broad-announce/migrations/004_telegram.up.sql

-- 004_telegram.up.sql
-- M3 Telegram delivery + bot commands. See SPEC §8.
--
-- What lands in M3:
--   telegram_bots    — one row per (company, bot). For M3 we
--                      support one bot per company; the table
--                      keys on (company_id, bot_id) so adding
--                      more later is a one-line change.
--   individuals:     — adds telegram_chat_id, telegram_user_id,
--                      telegram_invite_code (set on individual
--                      creation by the admin; admin hands the
--                      code to the user out-of-band), and
--                      mute_until (per-individual global mute
--                      for the /mute command).
--
-- What stays out of M3:
--   Bot token encryption at rest. M3 stores the token in
--   plaintext in telegram_bots.bot_token with a dev-only
--   annotation. AES-256-GCM is a security milestone (§11).
--   Real Telegram API. M3 ships faketgmd and a long-poll
--   bot loop. Webhook mode is M5/M9.
--   Retry + DLQ for failed sends. The single-attempt pattern
--   from M1's fcm path is repeated. M9 adds the retry chain.
--
-- Naming: snake_case to match the rest of the schema.

-- ── telegram_bots ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS telegram_bots (
    bot_id            TEXT NOT NULL,                       -- short id; e.g. "primary"
    company_id        TEXT NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    name              TEXT NOT NULL,                       -- human label; e.g. "Acme Ops"
    bot_token         TEXT NOT NULL,                       -- DEV ONLY; encrypt in M11 (security milestone)
    status            TEXT NOT NULL DEFAULT 'active',      -- active | paused
    last_seen_at      TIMESTAMPTZ,                         -- last getUpdates / webhook hit
    created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (company_id, bot_id)
);
CREATE INDEX IF NOT EXISTS idx_telegram_bots_company ON telegram_bots(company_id) WHERE status = 'active';

-- ── extend individuals ───────────────────────────────────────────
-- All four columns are nullable: an individual that has not yet
-- linked their Telegram account has no chat_id / user_id. The
-- invite_code is set when the admin creates the individual.
ALTER TABLE individuals
    ADD COLUMN IF NOT EXISTS telegram_chat_id     TEXT,
    ADD COLUMN IF NOT EXISTS telegram_user_id     BIGINT,
    ADD COLUMN IF NOT EXISTS telegram_invite_code TEXT,
    ADD COLUMN IF NOT EXISTS mute_until           TIMESTAMPTZ;

-- Unique index for fast "/start <code>" lookup. Partial index
-- so historical / legacy individuals without a code don't bloat
-- the index.
CREATE UNIQUE INDEX IF NOT EXISTS idx_individuals_invite_code
    ON individuals(telegram_invite_code)
    WHERE telegram_invite_code IS NOT NULL;

-- Unique index for fast "is this telegram_user_id already linked?"
-- check. Partial: most individuals won't be linked.
CREATE UNIQUE INDEX IF NOT EXISTS idx_individuals_telegram_user
    ON individuals(company_id, telegram_user_id)
    WHERE telegram_user_id IS NOT NULL;