Keysproperty operations Corbelstone Residential

Water alarm with no ticket

A water leak alarm still active after ticket_minutes with no ticket for it: none linked to the alarm, and no leak ticket for the same home (or, for a plant-room sensor, the same building's common areas) opened since.

Definition
water_alarm_no_ticket

Params: ticket_minutes = 10 · evaluated at Mon 20:00

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