broad-announce/migrations/001_init.up.sql

-- 001_init.up.sql
-- M1 minimum-viable schema. Three tables, all tenant-scoped.
-- Future migrations add: groups, group_members, sources,
-- subscriptions, routing_rules, telegram_bots.
--
-- All tables include company_id and the per-tenant queries MUST
-- filter on it. We do NOT enable RLS in v1; isolation is enforced
-- in the app layer. See SPEC §4 + §22.

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS companies (
    id            TEXT PRIMARY KEY,
    name          TEXT NOT NULL,
    status        TEXT NOT NULL DEFAULT 'active',   -- active | suspended
    rate_limit_per_sec INTEGER NOT NULL DEFAULT 10000,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS individuals (
    id            TEXT PRIMARY KEY,                 -- individuals are global IDs, not scoped
    company_id    TEXT NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    full_name     TEXT NOT NULL,
    email         TEXT,
    phone_e164    TEXT,
    locale        TEXT DEFAULT 'en',
    tz            TEXT DEFAULT 'UTC',
    status        TEXT NOT NULL DEFAULT 'active',   -- active | suspended
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_individuals_company ON individuals(company_id) WHERE status = 'active';

CREATE TABLE IF NOT EXISTS fcm_tokens (
    id            BIGSERIAL PRIMARY KEY,
    individual_id TEXT NOT NULL REFERENCES individuals(id) ON DELETE CASCADE,
    token         TEXT NOT NULL UNIQUE,
    device_id     TEXT,
    platform      TEXT NOT NULL DEFAULT 'android',
    locale        TEXT,
    app_version   TEXT,
    last_seen     TIMESTAMPTZ,
    status        TEXT NOT NULL DEFAULT 'active',   -- active | unregistered
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_fcm_tokens_individual ON fcm_tokens(individual_id) WHERE status = 'active';