Keysproperty operations Corbelstone Residential

Lift out with no other lift

A lift fault lasting fault_minutes in a building where no other lift is in service: the only lift, or the remaining one while the other is out for planned work or its own fault. Residents on upper floors are stuck.

Definition
lift_outage_single

Params: fault_minutes = 5 · evaluated at Mon 20:00

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