`db/comms_compliance.sql`

45 lines
-- comms_compliance.sql — applied 2026-05-03 by comms-compliance officer.
--
-- Two append-only tables that gate every outbound message:
--   comms_suppression — anyone who unsubscribed, hard-bounced, complained,
--                       or was added to a regulator DNC. Pre-flight check.
--   comms_send_audit  — one row per send decision (sent OR blocked), with
--                       SHA-256 of the recipient identifier (no PII). Forensic
--                       proof of compliance per FTC consent-decree guidance.
--
-- Wire-up note: the drops mailer (src/lib/email.ts + src/app/api/drops/send)
-- does NOT yet call into these tables. Roadmap item — when we wire the drops
-- send path through isSendAllowed() / logDecision(), this is where it logs.
-- Schema definitions match the canonical layout in
-- ~/.claude/agents/comms-compliance.md so future projects sharing PG can join.

CREATE TABLE IF NOT EXISTS comms_suppression (
  id              BIGSERIAL PRIMARY KEY,
  channel         TEXT NOT NULL CHECK (channel IN ('email','sms','voice')),
  identifier      TEXT NOT NULL,                  -- email or E.164 phone (raw, scrubbed if needed)
  identifier_hash TEXT NOT NULL,                  -- SHA-256(identifier) for joins without exposing PII
  reason          TEXT NOT NULL CHECK (reason IN ('unsubscribe','dnc','hard_bounce','complaint','manual','tcpa_revoke','ccpa_delete')),
  source          TEXT,                           -- federal_dnc / utah_cpr / fbl_gmail / one_click / etc.
  added_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  campaign_id     TEXT,                           -- if added during a specific campaign
  notes           TEXT,
  UNIQUE (channel, identifier)
);
CREATE INDEX IF NOT EXISTS idx_comms_suppression_hash ON comms_suppression(identifier_hash);

CREATE TABLE IF NOT EXISTS comms_send_audit (
  id                  BIGSERIAL PRIMARY KEY,
  channel             TEXT NOT NULL CHECK (channel IN ('email','sms','voice')),
  campaign_id         TEXT NOT NULL,
  identifier_hash     TEXT NOT NULL,              -- SHA-256, never raw recipient
  decision            TEXT NOT NULL CHECK (decision IN ('sent','blocked_dnc','blocked_suppression','blocked_no_consent','blocked_quiet_hours','blocked_other','failed_send')),
  block_source        TEXT,                       -- which list flagged it
  scrubbed_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  scrub_version       TEXT NOT NULL,              -- date-stamp of the DNC snapshot used
  consent_log_id      BIGINT,
  message_template_id TEXT,
  notes               TEXT
);
CREATE INDEX IF NOT EXISTS idx_comms_send_audit_campaign ON comms_send_audit(campaign_id);
CREATE INDEX IF NOT EXISTS idx_comms_send_audit_decision ON comms_send_audit(decision, scrubbed_at);