broad-announce/migrations/012_telegram_bot_fields.up.sql

-- 012_telegram_bot_fields.up.sql
--
-- M13b W3: extend public.telegram_bots so the admin UI can
-- configure a per-bot welcome message and default source. Also
-- adds a bcrypt hash of the bot_token so the UI can render a
-- "configured / not configured" indicator without ever exposing
-- the plaintext.
--
-- Why both columns?
--   - bot_token            — TEXT, plaintext. telegramd reads
--                            this directly to authenticate with
--                            api.telegram.org. Keep it for now
--                            so telegramd continues to work.
--   - bot_token_hash       — TEXT, bcrypt. Added in W3 so the UI
--                            can determine "configured" status
--                            without exposing the plaintext. The
--                            M11 security milestone will replace
--                            bot_token entirely with an
--                            AES-256-GCM-encrypted column and
--                            add a sidecar to decrypt for
--                            telegramd.
--
-- Why nullable bot_token_hash?
--   Rows seeded by 004/seed_m3.sql have a plaintext bot_token
--   but no hash yet. The next W3 rotation (or operator save via
--   UI) will populate the hash. NULL = "unrotated since W3";
--   the UI surfaces this as "Configured (legacy token)" with
--   a one-click "Rotate to set hash" hint.
--
-- Other columns:
--   welcome_message       — sent in response to /start (M13c wires
--                            telegramd to use this; W3 just stores)
--   default_source_id     — optional pointer to public.sources.id.
--                            W3 sets it on the bot row; W4
--                            (Smoke) verifies the FK shape. We do
--                            NOT add a hard FK in this migration
--                            because sources can be deleted out
--                            from under the bot (admin flow); a
--                            soft reference + ON DELETE SET NULL
--                            would be correct, but we keep the
--                            bot row even if the source goes away
--                            (operator may want to point it at a
--                            new source). v1.1 adds the FK +
--                            a reconciliation job.
--   description           — free-text label. Same shape as
--                            sources.description.
--   last_rotated_at       — set every time the token is written
--                            (create OR rotate). UI shows it.
--   updated_at            — bumped on any PATCH. Audit-friendly.
--
-- The down migration drops the new columns. The original
-- bot_token plaintext column is preserved.

ALTER TABLE telegram_bots
    ADD COLUMN IF NOT EXISTS bot_token_hash     TEXT,
    ADD COLUMN IF NOT EXISTS welcome_message    TEXT,
    ADD COLUMN IF NOT EXISTS default_source_id  TEXT,
    ADD COLUMN IF NOT EXISTS description        TEXT,
    ADD COLUMN IF NOT EXISTS last_rotated_at     TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS updated_at         TIMESTAMPTZ NOT NULL DEFAULT now();

-- updated_at trigger so any UPDATE bumps it without the app
-- having to remember. Mirrors the auth.tenants.updated_at
-- pattern from migration 010.
CREATE OR REPLACE FUNCTION telegram_bots_touch_updated_at() RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_telegram_bots_touch_updated_at ON telegram_bots;
CREATE TRIGGER trg_telegram_bots_touch_updated_at
    BEFORE UPDATE ON telegram_bots
    FOR EACH ROW
    EXECUTE FUNCTION telegram_bots_touch_updated_at();

-- Index on default_source_id so M13c routing lookups
-- ("give me the bots that use this source") are fast even when
-- the table is large. Partial index — most rows will have NULL
-- default_source_id in v1.
CREATE INDEX IF NOT EXISTS idx_telegram_bots_default_source
    ON telegram_bots(default_source_id)
    WHERE default_source_id IS NOT NULL;