broad-announce/migrations/011_sources_secrets.up.sql

-- 010_sources_secrets.up.sql
--
-- M13b W2: add secret + cert-lifecycle fields to public.sources
-- so the admin UI can issue HMAC + API keys for sources and
-- (later, in M14) manage mTLS client certs. Until this migration
-- landed, sources had no DB-side secret storage — the M0 pattern
-- was to keep secrets in BA_INGESTD_SOURCES env. The new columns
-- are nullable so rows created before this migration still load.
--
-- Storage policy:
--   hmac_secret_hash: bcrypt of the HMAC secret (cost 10 for
--     dev/smoke; cost 12 in prod — the secret length is 32 bytes
--     hex = 64 chars, which is well under bcrypt's 72-byte
--     limit so no truncation handling is needed).
--   api_key_hash: bcrypt of the API key. Same cost notes.
--   mtls_required: when TRUE, ingestd requires a client cert
--     signed by the company mTLS CA. M14 W2 will check this flag
--     on every ingest. W2 just stores it; the M14 backend is
--     the one that actually enforces it.
--   description: free-text label. Optional but the UI always
--     shows it on the detail page.
--
-- Plaintext is returned to the UI EXACTLY ONCE at create / rotate
-- time, in a "one-time secrets" payload. After that, only the
-- hashes are stored. The UI cannot re-fetch the plaintext.

ALTER TABLE sources
    ADD COLUMN IF NOT EXISTS hmac_secret_hash  TEXT,
    ADD COLUMN IF NOT EXISTS api_key_hash      TEXT,
    ADD COLUMN IF NOT EXISTS mtls_required     BOOLEAN NOT NULL DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS description       TEXT;