`db/migrations/004_comms_suppression.sql`

65 lines
-- 004 · Communications-compliance tables (CAN-SPAM / §17529.5 / CCPA / TCPA)
--
-- Mirrors the schema documented in Steve's `comms-compliance` skill so any
-- send script can fail-closed if these tables are missing.
--
-- comms_suppression: any email/phone we must never contact again. Fed by:
--   - studio opt-outs from /unsubscribe?token=
--   - hard bounces from George
--   - CCPA delete requests
--   - manual ops additions
--
-- comms_send_audit: per-message log — recipient, channel, decision, reason.
--   Required by Steve's standing rule: "fail-closed if list missing or stale,
--   per-message audit to PG".

BEGIN;

CREATE TABLE IF NOT EXISTS comms_suppression (
  id              SERIAL PRIMARY KEY,
  channel         TEXT NOT NULL,                        -- 'email' | 'sms' | 'voice'
  identifier      TEXT NOT NULL,                        -- lowercased email or E.164 phone
  reason          TEXT NOT NULL,                        -- 'unsubscribe' | 'bounce' | 'ccpa_delete' | 'manual' | 'optout_form'
  source          TEXT,                                 -- where it came from (URL, scrape, ops note)
  installer_id    INTEGER REFERENCES installers(id) ON DELETE SET NULL,
  notes           TEXT,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (channel, identifier)
);

CREATE INDEX IF NOT EXISTS idx_comms_suppression_id ON comms_suppression (channel, identifier);

CREATE TABLE IF NOT EXISTS comms_send_audit (
  id              BIGSERIAL PRIMARY KEY,
  channel         TEXT NOT NULL,                        -- 'email' | 'sms' | 'voice'
  campaign        TEXT NOT NULL,                        -- 'claim_invite' | 'booking_confirmation' | etc.
  recipient       TEXT NOT NULL,                        -- lowercased email or E.164
  installer_id    INTEGER REFERENCES installers(id) ON DELETE SET NULL,
  decision        TEXT NOT NULL,                        -- 'sent' | 'blocked_suppression' | 'blocked_compliance_gate' | 'blocked_dnc' | 'failed'
  reason          TEXT,                                 -- detail for blocked/failed
  subject         TEXT,
  message_id      TEXT,                                 -- George/SMTP message-id when sent
  payload         JSONB,                                -- full template snapshot for audit replay
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_comms_send_audit_recipient ON comms_send_audit (recipient, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_comms_send_audit_campaign ON comms_send_audit (campaign, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_comms_send_audit_installer ON comms_send_audit (installer_id, created_at DESC);

-- Unsubscribe tokens — one-shot, signed; revoke by deleting the row.
CREATE TABLE IF NOT EXISTS unsubscribe_tokens (
  token           TEXT PRIMARY KEY,                     -- HMAC-derived, opaque
  channel         TEXT NOT NULL,
  identifier      TEXT NOT NULL,
  campaign        TEXT,
  installer_id    INTEGER REFERENCES installers(id) ON DELETE SET NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  used_at         TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_unsubscribe_tokens_identifier
  ON unsubscribe_tokens (channel, identifier);

COMMIT;