compliance · v1 · Head of Compliance
0
inspections at Mon 20:00
due_at before now; not done before now; no booking made before now. The evidence names the inspection firm on the schedule and the other active firms in the building's city.
compliance · changed 2026-11-01: First version.
Tests
- count_between min 0.0 max 60.0
- unique on inspection_id
- detector_shape
SQL
SELECT 'inspection' AS subject_type, i.id AS subject_id, i.id AS inspection_id, i.building_id, i.vendor_id,
i.due_at AS started_at,
CASE WHEN i.kind IN ('fire_alarm', 'lift_certificate', 'gas_safety') THEN 'high' ELSE 'medium' END AS severity,
json_object('kind', i.kind, 'due_at', i.due_at, 'days_overdue', date_diff('day', i.due_at, {now}),
'building', b.name, 'firm', v.name) AS evidence
FROM inspections i
JOIN buildings b ON b.id = i.building_id
LEFT JOIN vendors v ON v.id = i.vendor_id
WHERE i.due_at <= {now}
AND (i.done_at IS NULL OR i.done_at > {now})
AND (i.booked_at IS NULL OR i.booked_at > {now})
ORDER BY i.due_at, i.id