broad-announce/migrations/seed_m2.sql

-- seed_m2.sql
-- M2 seed. Adds 2 more individuals (Bob, Carol), one group (sre:
-- Alice+Bob), the M2 source row, and four subscriptions covering
-- the four M2 verification scenarios.
--
-- Idempotent (ON CONFLICT DO NOTHING + careful ordering). Safe
-- to re-run on top of seed.sql.

-- ── individuals ───────────────────────────────────────────────────
INSERT INTO individuals (id, company_id, full_name, email, locale, tz) VALUES
    ('ind-acme-002', 'acme-001', 'Bob SRE',       'bob@acme.example',   'en', 'UTC'),
    ('ind-acme-003', 'acme-001', 'Carol NOC',     'carol@acme.example', 'en', 'UTC')
ON CONFLICT (id) DO NOTHING;

-- ── tokens ────────────────────────────────────────────────────────
INSERT INTO fcm_tokens (individual_id, token, device_id, locale, app_version) VALUES
    ('ind-acme-002', 'fake-fcm-token-acme-bob-001',   'pixel-7-bob',   'en', '1.0.0'),
    ('ind-acme-003', 'fake-fcm-token-acme-carol-001', 'pixel-7-carol', 'en', '1.0.0')
ON CONFLICT (token) DO NOTHING;

-- ── source row ────────────────────────────────────────────────────
-- Pairs with BA_INGESTD_SOURCES env (HMAC auth). The env
-- registers the secret; this row gives the resolver addressing
-- metadata (allowed_targets + match_expr).
--
-- For M2 we leave allowed_targets EMPTY and rely on the
-- broadcast fallback. Wait — per your Q2 answer, we hard-fail.
-- So we populate allowed_targets to a real target so the
-- resolver has somewhere to route.
INSERT INTO sources (id, company_id, name, type, rate_limit_per_sec, allowed_targets, match_expr) VALUES
    ('prom-prod', 'acme-001', 'Prometheus (prod)', 'http', 100,
     '[
        {"type":"group",      "id":"sre"},
        {"type":"individual", "id":"ind-acme-003"}
     ]'::jsonb,
     '{}'::jsonb
    )
ON CONFLICT (company_id, id) DO NOTHING;

-- ── groups ────────────────────────────────────────────────────────
INSERT INTO groups (id, company_id, name, description) VALUES
    ('sre', 'acme-001', 'SRE Team', 'Site Reliability Engineering on-call')
ON CONFLICT (company_id, id) DO NOTHING;

-- ── group members ─────────────────────────────────────────────────
-- sre = Alice (ind-acme-001) + Bob (ind-acme-002)
INSERT INTO group_members (company_id, group_id, individual_id) VALUES
    ('acme-001', 'sre', 'ind-acme-001'),
    ('acme-001', 'sre', 'ind-acme-002')
ON CONFLICT (company_id, group_id, individual_id) DO NOTHING;

-- ── subscriptions ─────────────────────────────────────────────────
-- Scenarios for M2_VERIFICATION.md:
--
-- (a) ind-acme-001 (Alice, in sre): plain fcm subscription, no
--     severity filter, no quiet hours. Gets every alert.
--
-- (b) ind-acme-002 (Bob, in sre): fcm subscription, min_severity
--     = 'critical'. Only critical / inminent_colapse alerts.
--
-- (c) ind-acme-003 (Carol, NOT in sre): fcm subscription with
--     quiet_hours 00:00–23:59 in UTC. Skips everything except
--     inminent_colapse (which bypasses quiet hours).
--
-- (d) ind-acme-002 (Bob) also subscribes via a routing_rule to
--     demonstrate rule-driven targeting: any storage alert with
--     host=db-prod-03 goes to him.
INSERT INTO subscriptions (individual_id, company_id, source_id, min_severity, channel_mask, quiet_hours_start, quiet_hours_end, tz) VALUES
    -- (a) Alice: everything
    ('ind-acme-001', 'acme-001', 'prom-prod', NULL,    '["fcm"]'::jsonb, NULL,    NULL,    'UTC'),
    -- (b) Bob: critical and above
    ('ind-acme-002', 'acme-001', 'prom-prod', 'critical', '["fcm"]'::jsonb, NULL, NULL,    'UTC'),
    -- (c) Carol: quiet hours cover the whole day
    ('ind-acme-003', 'acme-001', 'prom-prod', NULL,    '["fcm"]'::jsonb, '00:00', '23:59', 'UTC')
ON CONFLICT (individual_id, source_id) DO NOTHING;

-- ── routing rules ─────────────────────────────────────────────────
-- Rule (d): any storage alert with data.host = db-prod-03 goes
-- to Bob (ind-acme-002). Note: Bob's subscription has
-- min_severity=critical, so this rule only fires for critical
-- storage alerts about db-prod-03 (which is the realistic case
-- for a "run this migration now" pager). The rule's match_expr
-- intentionally does NOT filter on severity so we can show that
-- the resolver still applies subscriptions on top of rules.
INSERT INTO routing_rules (company_id, name, priority, match_expr, target) VALUES
    ('acme-001', 'DB prod-03 storage page', 10,
     '{"category":"storage","data":{"host":"db-prod-03"}}'::jsonb,
     '{"type":"individual","id":"ind-acme-002"}'::jsonb
    )
ON CONFLICT (company_id, name) DO NOTHING;