maintenance · v1 · Head of Maintenance
4
alarms at Mon 20:00
lift_fault raised at least fault_minutes before now and not cleared before now; every other lift in the building is either inside a planned maintenance window at now or has its own active fault. The evidence says whether a ticket and a residents' notice exist yet.
maintenance · changed 2026-11-01: First version.
Tests
- count_between min 0.0 max 20.0
- unique on alarm_id
- detector_shape
SQL
WITH faults AS (
SELECT a.* FROM alarms a
WHERE a.kind = 'lift_fault' AND a.raised_at <= {now} AND (a.cleared_at IS NULL OR a.cleared_at > {now})
),
lifts AS (
SELECT s.building_id, s.id AS asset_id,
(s.maintenance_from IS NOT NULL AND s.maintenance_from <= {now} AND s.maintenance_to > {now}) AS in_works,
EXISTS (SELECT 1 FROM faults f WHERE f.asset_id = s.id) AS faulted
FROM assets s WHERE s.kind = 'lift'
),
per_building AS (
SELECT building_id, count(*) AS lifts, count(*) FILTER (WHERE in_works) AS in_works,
count(*) FILTER (WHERE NOT in_works AND NOT faulted) AS working
FROM lifts GROUP BY building_id
)
SELECT 'alarm' AS subject_type, f.id AS subject_id, f.id AS alarm_id, f.asset_id, f.building_id,
f.raised_at + INTERVAL ({fault_minutes}) MINUTE AS started_at, 'critical' AS severity,
tk.id AS ticket_id,
json_object('raised_at', f.raised_at, 'minutes_out', date_diff('minute', f.raised_at, {now}),
'lifts', p.lifts, 'lifts_in_planned_works', p.in_works, 'working_lifts', p.working,
'has_ticket', tk.id IS NOT NULL,
'residents_notified', EXISTS (SELECT 1 FROM tenant_notices n WHERE n.building_id = f.building_id
AND n.kind = 'building_notice' AND n.sent_at >= f.raised_at
AND n.sent_at <= {now} AND n.status <> 'cancelled'),
'homes_above_ground', (SELECT count(*) FROM units u WHERE u.building_id = f.building_id
AND u.floor > 0),
'floors', b.floors, 'building', b.name, 'manager_id', b.manager_id) AS evidence
FROM faults f
JOIN per_building p ON p.building_id = f.building_id
JOIN buildings b ON b.id = f.building_id
LEFT JOIN (SELECT alarm_id, min(id) AS id FROM tickets WHERE opened_at <= {now} AND alarm_id IS NOT NULL
GROUP BY alarm_id) tk ON tk.alarm_id = f.id
WHERE f.raised_at <= {now} - INTERVAL ({fault_minutes}) MINUTE AND p.working = 0
ORDER BY started_at, f.id