Keysproperty operations Corbelstone Residential

Ask

Plain-English questions, answered only through the definitions. Where a term has two meanings, both answers are shown with their owners; where no definition fits, there is no number.

Sheet K-01
answers at Mon 20:00

How many homes are vacant?Which tenants are in arrears?How many open tickets?Tickets in SLA last weekLeases in the renewal windowWater alarms with no ticket

0 alarms according to the Head of Maintenance (maintenance v1).

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
[]