maintenance · v1 · Head of Maintenance
0 alarms
kind water_leak, raised at least ticket_minutes before now, not cleared before now; no ticket opened before now with this alarm_id, and no leak ticket for the same home or building opened between 30 minutes before the alarm and now.
From water_alarm_no_ticket (maintenance) · tables alarms, assets, buildings, tickets, a
SQL and the first rows of 0
SELECT 'alarm' AS subject_type, a.id AS subject_id, a.id AS alarm_id, a.asset_id, a.building_id, a.unit_id,
a.raised_at + INTERVAL (10) MINUTE AS started_at, 'critical' AS severity,
json_object('raised_at', a.raised_at, 'minutes_active', date_diff('minute', a.raised_at, TIMESTAMP '2026-11-02 20:00:00'),
'sensor', s.label, 'building', b.name, 'kind', a.kind) AS evidence
FROM alarms a
JOIN assets s ON s.id = a.asset_id
JOIN buildings b ON b.id = a.building_id
WHERE a.kind = 'water_leak'
AND a.raised_at <= TIMESTAMP '2026-11-02 20:00:00' - INTERVAL (10) MINUTE
AND (a.cleared_at IS NULL OR a.cleared_at > TIMESTAMP '2026-11-02 20:00:00')
AND NOT EXISTS (
SELECT 1 FROM tickets t
WHERE t.opened_at <= TIMESTAMP '2026-11-02 20:00:00'
AND (t.alarm_id = a.id
OR (t.category = 'leak' AND t.building_id = a.building_id
AND t.unit_id IS NOT DISTINCT FROM a.unit_id
AND t.opened_at >= a.raised_at - INTERVAL 30 MINUTE)))
ORDER BY started_at, a.id
[]