maintenance · v1 · Head of Maintenance
0
alarms at Mon 20:00
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.
maintenance · changed 2026-11-01: First version.
Tests
- count_between min 0.0 max 30.0
- unique on alarm_id
- detector_shape
SQL
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 ({ticket_minutes}) MINUTE AS started_at, 'critical' AS severity,
json_object('raised_at', a.raised_at, 'minutes_active', date_diff('minute', a.raised_at, {now}),
'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 <= {now} - INTERVAL ({ticket_minutes}) MINUTE
AND (a.cleared_at IS NULL OR a.cleared_at > {now})
AND NOT EXISTS (
SELECT 1 FROM tickets t
WHERE t.opened_at <= {now}
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