broad-announce/migrations/003_subscriptions_groups.up.sql

-- 003_subscriptions_groups.up.sql
-- M2 recipient resolution surface. See SPEC §4 (entities),
-- SPEC §6 (recipient resolution algorithm).
--
-- What lands in M2:
--   sources        — the on-tenant source address book (M1 had it
--                    only as an env var for HMAC auth)
--   groups         — named groups of individuals (e.g. "sre",
--                    "noc", "on-call-rotation-A")
--   group_members  — many-to-many individuals ↔ groups
--   subscriptions  — per-individual opt-in to a source, with
--                    severity gate, channel mask, quiet hours
--   routing_rules  — per-company override: if alert matches
--                    match_expr, route to target_type+target_id
--                    instead of (or in addition to) source defaults
--
-- What stays out of M2:
--   per-source HMAC secret in DB. Ingestd still reads
--   BA_INGESTD_SOURCES env. The `sources` table here holds
--   addressing metadata only.
--   Routing rule match_expr is intentionally simple: keys are
--   category, severity, data.<key>=<value>, all=true. Full
--   JSONPath/expression language is M6 or later.

-- ── sources ───────────────────────────────────────────────────────
-- One row per (company, source_id). Pairs with ingestd's
-- BA_INGESTD_SOURCES env (auth) — M2 just needs the row to exist
-- for the resolver to know about it.
CREATE TABLE IF NOT EXISTS sources (
    id                   TEXT NOT NULL,                 -- sources are scoped to a company
    company_id           TEXT NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    name                 TEXT NOT NULL,
    type                 TEXT NOT NULL DEFAULT 'http',  -- http | mqtt | ws | grpc
    rate_limit_per_sec   INTEGER NOT NULL DEFAULT 100,
    allowed_targets      JSONB NOT NULL DEFAULT '[]'::jsonb,  -- e.g. [{"type":"group","id":"sre"}, {"type":"individual","id":"ind-..."}]
    match_expr           JSONB NOT NULL DEFAULT '{}'::jsonb,  -- default match (category, severity, data keys)
    status               TEXT NOT NULL DEFAULT 'active',      -- active | suspended
    created_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (company_id, id)
);
CREATE INDEX IF NOT EXISTS idx_sources_company ON sources(company_id) WHERE status = 'active';

-- ── groups ────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS groups (
    id            TEXT NOT NULL,
    company_id    TEXT NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    name          TEXT NOT NULL,
    description   TEXT,
    status        TEXT NOT NULL DEFAULT 'active',  -- active | archived
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (company_id, id)
);
CREATE INDEX IF NOT EXISTS idx_groups_company ON groups(company_id) WHERE status = 'active';

-- ── group_members ─────────────────────────────────────────────────
-- Junction: which individuals belong to which groups. M2 only adds
-- rows; M3+ lets the admin UI manage them.
CREATE TABLE IF NOT EXISTS group_members (
    group_id      TEXT NOT NULL,
    company_id    TEXT NOT NULL,
    individual_id TEXT NOT NULL REFERENCES individuals(id) ON DELETE CASCADE,
    added_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    added_by      TEXT,
    PRIMARY KEY (company_id, group_id, individual_id),
    FOREIGN KEY (company_id, group_id) REFERENCES groups(company_id, id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_group_members_individual ON group_members(individual_id);

-- ── subscriptions ─────────────────────────────────────────────────
-- Per-(individual, source) opt-in with severity gate, channel mask,
-- and per-subscriber quiet hours. See SPEC §6.
--
-- channel_mask is a JSON array of channel names, e.g.
--   ["fcm", "telegram"]
-- M2 ships with only 'fcm' actually delivered (M3 brings telegram).
-- The resolver will include 'telegram' in the result, but no worker
-- consumes it yet; the alert gets dropped at the broker level with
-- a "no worker" log. See SPEC §6 / M2 honest flag in PROMPT.md.
--
-- quiet_hours_start / quiet_hours_end are times of day in the
-- subscriber's tz (HH:MM). Wrap-around (e.g. 22:00–06:00) is
-- supported by the resolver.
--
-- min_severity is the lowest severity that gets through. Order is
-- info < warning < critical < inminent_colapse. NULL = no filter
-- (any severity).
CREATE TABLE IF NOT EXISTS subscriptions (
    id                   BIGSERIAL PRIMARY KEY,
    individual_id        TEXT NOT NULL REFERENCES individuals(id) ON DELETE CASCADE,
    company_id           TEXT NOT NULL,                  -- denormalized for indexing
    source_id            TEXT NOT NULL,
    min_severity         TEXT,                            -- info|warning|critical|inminent_colapse; NULL = any
    channel_mask         JSONB NOT NULL DEFAULT '["fcm"]'::jsonb,
    quiet_hours_start    TIME,                            -- in `tz`; NULL = no quiet hours
    quiet_hours_end      TIME,
    tz                   TEXT NOT NULL DEFAULT 'UTC',
    status               TEXT NOT NULL DEFAULT 'active',  -- active | paused
    created_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (individual_id, source_id),
    FOREIGN KEY (company_id, source_id) REFERENCES sources(company_id, id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_subscriptions_source ON subscriptions(company_id, source_id) WHERE status = 'active';
CREATE INDEX IF NOT EXISTS idx_subscriptions_individual ON subscriptions(individual_id) WHERE status = 'active';

-- ── routing_rules ─────────────────────────────────────────────────
-- Optional per-company overrides. A rule with priority=N is
-- evaluated in priority order; first match wins, unless
-- `continue` is true. The M2 resolver evaluates them in a single
-- SQL pass (window function) and unions the targets.
--
-- match_expr shape (M2):
--   {
--     "category": "storage",             // optional, exact match
--     "severity": "critical",            // optional, exact match
--     "data": {"host": "db-prod-03"},    // optional, all keys must match
--     "all": true                        // if true, match every alert for this company
--   }
--
-- target shape:
--   { "type": "group",       "id": "sre"   }
--   { "type": "individual",  "id": "ind-…" }
--   { "type": "broadcast",   "id": null    }  -- all active individuals in company
--   { "type": "drop",        "id": null    }  -- explicitly drop the alert for this company
--
-- enabled=false → skipped. Priorities are 0..N, lower is higher
-- priority. Rules within the same priority are unioned.
CREATE TABLE IF NOT EXISTS routing_rules (
    id            BIGSERIAL PRIMARY KEY,
    company_id    TEXT NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    name          TEXT NOT NULL,
    priority      INTEGER NOT NULL DEFAULT 100,
    match_expr    JSONB NOT NULL DEFAULT '{}'::jsonb,
    target        JSONB NOT NULL,                       -- {type, id} as above
    enabled       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (company_id, name)
);
CREATE INDEX IF NOT EXISTS idx_routing_rules_company_priority
    ON routing_rules(company_id, priority) WHERE enabled = TRUE;