Checklists and Checks

Views for checklists, individual checks with measurements, signatures, labels, and links to issues.

These views cover the checklist and planned work system in Maecos. A checklist is an instance of a checklist definition (template) that groups multiple checks. Each check represents a single measurement or verification task that an operator performs. Checks can have measurements (values), compliance indicators, and can trigger issues when problems are found.

---

reporting.checklists

Checklist instances — each row is one execution of a checklist definition.

Field	Type	Description
checklist_id	uuid	Unique identifier of the checklist instance
checklist_name	text	Name of the checklist definition
location_id	uuid	Location within the checklist definition
location_name	text	Human-readable location name
signature_required	boolean	Whether the checklist requires a signature
is_signed	boolean	Whether the checklist has been signed
closed_at	timestamp	When the checklist was closed
closed_by	uuid	User who closed the checklist
created_at	timestamp	When the checklist was created
updated_at	timestamp	Most recent of created_at and closed_at

Joins: Join with checklists_checks and checks using checklist_id. Join with checklists_labels for filtering by label. Join with checklist_signatures for signature details.

---

reporting.checklists_checks

Maps checks to their parent checklist.

Field	Type	Description
check_id	uuid	The individual check
checklist_id	uuid	The parent checklist instance
checklist_name	text	Name of the checklist definition
created_at	timestamp	When the checklist was created

Joins: Join with checks on check_id for measurement details.

---

reporting.checks

Individual check items with their latest measurement. This is the most detailed view for analyzing check results.

Each check row contains only the most recent measurement. Historical measurements are not available in this view. If a check is filled in multiple times, only the last value is shown.

Field	Type	Description
check_id	uuid	Unique identifier of the check
check_name	text	Name of the procedure (check type)
procedure_number	integer	Procedure sequence number
checklist_id	uuid	Parent checklist instance
equipment_id	uuid	Equipment this check was performed on
workstation_id	uuid	Workstation where the check was executed
earliest_start_at	timestamp	Earliest allowed start time
due_at	timestamp	When the check is due
value_is_compliant	boolean	Whether the measured value is within spec
value_string	text	The measured value as text
value_decimal	numeric	The measured value as a number (if applicable)
value_created_at	timestamp	When the measurement was recorded
value_created_by	uuid	User who recorded the measurement
value_is_late	boolean	Whether the measurement was recorded after the due time
check_created_at	timestamp	When the check was created
marked_as_not_executable_at	timestamp	If the check was marked as not executable
marked_as_not_executable_by	uuid	User who marked it as not executable
updated_at	timestamp	Last update timestamp
check_title	text	Title of the check (from the slide)
check_description	text	Description of the check (from the slide)

Joins: Join with checklists on checklist_id. Join with equipment on equipment_id. Join with workstations on workstation_id. Join with checks_labels on check_id. Join with check_issues on check_id for linked issues.

---

reporting.checklists_labels

Labels attached to checklist definitions, inherited by all instances.

Field	Type	Description
checklist_instance_id	uuid	The checklist instance
label_category	text	Label category
label_value	text	Label value

---

reporting.checks_labels

Labels attached to check procedures.

Field	Type	Description
check_id	uuid	The check
label_category	text	Label category
label_value	text	Label value

---

reporting.checklist_signatures

Signature records for checklists that require sign-off.

Field	Type	Description
checklist_id	uuid	The checklist instance
signer_id	uuid	User who signed
is_deleted	boolean	Whether the signature was revoked
updated_at	timestamp	Last update timestamp

Joins: Join with checklists on checklist_id. Join signer_id with users.user_id.

---

reporting.check_issues

Links checks to issues created from non-compliant measurements or observations.

Field	Type	Description
check_id	uuid	The check that triggered the issue
issue_id	uuid	The resulting issue
linked_at	timestamp	When the link was created
linked_by	uuid	User who created the link

Joins: Join with checks on check_id. Join with issues on issue_id.

---

Example queries

Checklist completion rate by checklist name (last 30 days)

SELECT
    checklist_name,
    COUNT(*) AS total,
    COUNT(closed_at) AS completed,
    ROUND(100.0 * COUNT(closed_at) / COUNT(*), 1) AS completion_pct
FROM reporting.checklists
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY checklist_name
ORDER BY completion_pct ASC;

Non-compliant checks with equipment and operator

SELECT
    c.check_name,
    c.value_string,
    c.value_decimal,
    e.name AS equipment_name,
    u.first_name || ' ' || u.last_name AS operator
FROM reporting.checks c
LEFT JOIN reporting.equipment e USING (equipment_id)
LEFT JOIN reporting.users u ON c.value_created_by = u.user_id
WHERE c.value_is_compliant = false
    AND c.value_created_at >= NOW() - INTERVAL '7 days'
ORDER BY c.value_created_at DESC;