broad-announce/migrations/002_deliveries.up.sql

-- 002_deliveries.up.sql
-- Per-attempt delivery row. In M1 this is written by deliverd-fcm
-- after each fake-fcm call. In M3+ it gets the full DLQ + retry
-- semantics from SPEC §9.
--
-- For M1 the schema is intentionally minimal:
--   - alert_id, individual_id, channel, target, status, attempts
--   - the payload (jsonb) for replay (M8)
-- Timescale conversion lands in M7.

CREATE TABLE IF NOT EXISTS deliveries (
    id            BIGSERIAL PRIMARY KEY,
    alert_id      TEXT NOT NULL,
    company_id    TEXT NOT NULL,
    individual_id TEXT NOT NULL,
    channel       TEXT NOT NULL,                    -- fcm | telegram | sms | email | slack | teams | webhook
    target        TEXT NOT NULL,                    -- the fcm_token, telegram_chat_id, phone_e164, …
    status        TEXT NOT NULL DEFAULT 'pending',  -- pending | sent | failed | dlq
    attempts      INT NOT NULL DEFAULT 0,
    last_error    TEXT,
    payload       JSONB,                            -- snapshot at enqueue time, for M8 replay
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    sent_at       TIMESTAMPTZ,
    next_attempt_at TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS idx_deliveries_company_alert ON deliveries(company_id, alert_id);
CREATE INDEX IF NOT EXISTS idx_deliveries_status ON deliveries(status) WHERE status IN ('pending','failed');