Keysproperty operations Corbelstone Residential

Inspection overdue

A statutory building check (fire alarm, emergency lighting, legionella, lift certificate, gas safety) past its due date, not done and with nothing booked.

Definition
inspection_overdue

Params: none · evaluated at Mon 20:00

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