Admin
9f1ac9c25b
The client acks a frame as soon as it arrives — that is how an online device stops being re-told — and the service recorded that ack in acked_at, the column the drain-to-empty rule counts. Measured on an Android 15 emulator: the owner's trigger reached the subscriber at .562 and the alert was destroyed 27 ms later, mid-siren, so the owner's disarm answered ERR_ALERT_NOT_FOUND. alarm_deliveries now carries two timestamps. delivered_at is the receipt (the WS ack frame and the offline check-in): it retires re-delivery and nothing else. acked_at is the human dismissal (ackAlarm): only that ends the alert, once every subscription's row is dismissed. Migration 011 adds the column and replaces the pending index with an undelivered one. ackAlarm also runs the drain check on every explicit dismissal, not only when this call cleared a row: a receipted row still has to be dismissed. Also fixed, found by the same probe run: db.Close left the closed pool as the process singleton (a second probe inherited it), and the lifecycle probe's three subscriptions shared one device id, which migration 010 made illegal. Verified: both probes pass against the live dev database, including the new receipt-vs-dismissal case; migration 011 applied to the dev DB.
56 lines
2.6 KiB
SQL
56 lines
2.6 KiB
SQL
-- Copyright (C) 2026 Dcntrlized
|
|
--
|
|
-- This program is free software: you can redistribute it and/or modify
|
|
-- it under the terms of the GNU Affero General Public License as published
|
|
-- by the Free Software Foundation, either version 3 of the License, or
|
|
-- (at your option) any later version.
|
|
--
|
|
-- This program is distributed in the hope that it will be useful,
|
|
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
|
|
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
|
|
-- GNU Affero General Public License for more details.
|
|
--
|
|
-- You should have received a copy of the GNU Affero General Public License
|
|
-- along with this program. If not, see <https://www.gnu.org/licenses/>.
|
|
|
|
-- 011_delivery_receipts.sql — "received" is not "dismissed".
|
|
--
|
|
-- The delivery ledger had one timestamp (`acked_at`) for two different facts,
|
|
-- and the clients ack a frame the moment they receive it (that is how a live
|
|
-- device stops being told about the same alarm on every poll). Because the
|
|
-- drain-to-empty rule destroys the alert when every row is acked, a single-device
|
|
-- alert was destroyed 27 ms after the owner triggered it — measured on an
|
|
-- Android 15 emulator: `triggered` then `destroyed/delivered` in the event log,
|
|
-- while the phone was still ringing. The owner then could not disarm
|
|
-- (`ERR_ALERT_NOT_FOUND`), the subscription went with the alert, and its device
|
|
-- token stopped resolving mid-alarm (401).
|
|
--
|
|
-- So the ledger now records both facts:
|
|
-- * `delivered_at` — the device has the trigger (a live frame, a replay, or a
|
|
-- check-in). It stops re-delivery. It is NOT a dismissal.
|
|
-- * `acked_at` — the person holding the device dismissed it. Only this drives
|
|
-- the drain, so the alert ends when everyone has dismissed it, never because
|
|
-- everyone happened to be online.
|
|
--
|
|
-- Additive and idempotent. Existing rows: a row with `acked_at` set keeps that
|
|
-- fact (it was a dismissal under the old contract too); a pending row simply has
|
|
-- no receipt yet, which is the correct reading of "we never recorded a delivery".
|
|
|
|
\c dcntrlized
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_namespace WHERE nspname = 'alarm') THEN
|
|
RETURN;
|
|
END IF;
|
|
|
|
ALTER TABLE alarm.alarm_deliveries ADD COLUMN IF NOT EXISTS delivered_at TIMESTAMPTZ;
|
|
|
|
-- "Who still owes an alarm on this device" is the check-in query; the
|
|
-- partial index keeps it to the rows that can still answer yes.
|
|
DROP INDEX IF EXISTS alarm.alarm_deliveries_pending_idx;
|
|
CREATE INDEX IF NOT EXISTS alarm_deliveries_undelivered_idx
|
|
ON alarm.alarm_deliveries (alert_id, subscription_id)
|
|
WHERE acked_at IS NULL AND delivered_at IS NULL;
|
|
END $$;
|