mass_mail_engine/docs/SCHEMA.sql

-- PostgreSQL schema (MVP)
-- Note: This schema is optimized for low volume and long-term retention.
-- Future expansion: add delivery_detail / tracking_detail tables if needed.

-- Extensions
CREATE EXTENSION IF NOT EXISTS citext;

-- Tenants (optional; can be sourced from Member Center if not stored locally)
CREATE TABLE IF NOT EXISTS tenants (
  id UUID PRIMARY KEY,
  name TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Lists (per tenant)
CREATE TABLE IF NOT EXISTS lists (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_lists_tenant ON lists(tenant_id);

ALTER TABLE lists
  ADD CONSTRAINT fk_lists_tenant
  FOREIGN KEY (tenant_id) REFERENCES tenants(id);

-- List subscriptions (per list, keyed by email)
CREATE TABLE IF NOT EXISTS subscriptions (
  id UUID PRIMARY KEY,
  list_id UUID NOT NULL,
  email CITEXT NOT NULL,
  external_subscriber_id UUID, -- Member Center subscriber_id
  status TEXT NOT NULL, -- active/unsubscribed/bounced/complaint
  preferences JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (list_id, email)
);
CREATE INDEX IF NOT EXISTS idx_subscriptions_list ON subscriptions(list_id);
CREATE INDEX IF NOT EXISTS idx_subscriptions_external_subscriber ON subscriptions(external_subscriber_id);

ALTER TABLE subscriptions
  ADD CONSTRAINT fk_subscriptions_list
  FOREIGN KEY (list_id) REFERENCES lists(id);

-- Event inbox (append-only)
CREATE TABLE IF NOT EXISTS events_inbox (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  event_type TEXT NOT NULL,
  source TEXT NOT NULL, -- member_center / ses
  payload JSONB NOT NULL,
  received_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  processed_at TIMESTAMPTZ,
  status TEXT NOT NULL DEFAULT 'received', -- received/processed/invalid/failed
  error TEXT
);
CREATE INDEX IF NOT EXISTS idx_events_inbox_tenant ON events_inbox(tenant_id);
CREATE INDEX IF NOT EXISTS idx_events_inbox_type ON events_inbox(event_type);
CREATE INDEX IF NOT EXISTS idx_events_inbox_status ON events_inbox(status);

ALTER TABLE events_inbox
  ADD CONSTRAINT fk_events_inbox_tenant
  FOREIGN KEY (tenant_id) REFERENCES tenants(id);

-- Campaigns (content is provided by tenant sites)
CREATE TABLE IF NOT EXISTS campaigns (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  list_id UUID NOT NULL,
  name TEXT,
  subject TEXT,
  body_html TEXT,
  body_text TEXT,
  template JSONB, -- optional template payload
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_campaigns_tenant ON campaigns(tenant_id);

ALTER TABLE campaigns
  ADD CONSTRAINT fk_campaigns_tenant
  FOREIGN KEY (tenant_id) REFERENCES tenants(id);

ALTER TABLE campaigns
  ADD CONSTRAINT fk_campaigns_list
  FOREIGN KEY (list_id) REFERENCES lists(id);

-- Send jobs
CREATE TABLE IF NOT EXISTS send_jobs (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  list_id UUID NOT NULL,
  campaign_id UUID NOT NULL,
  scheduled_at TIMESTAMPTZ,
  window_start TIMESTAMPTZ,
  window_end TIMESTAMPTZ,
  status TEXT NOT NULL DEFAULT 'pending', -- pending/running/completed/failed/cancelled
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_send_jobs_tenant ON send_jobs(tenant_id);
CREATE INDEX IF NOT EXISTS idx_send_jobs_status ON send_jobs(status);

ALTER TABLE send_jobs
  ADD CONSTRAINT fk_send_jobs_tenant
  FOREIGN KEY (tenant_id) REFERENCES tenants(id);

ALTER TABLE send_jobs
  ADD CONSTRAINT fk_send_jobs_list
  FOREIGN KEY (list_id) REFERENCES lists(id);

ALTER TABLE send_jobs
  ADD CONSTRAINT fk_send_jobs_campaign
  FOREIGN KEY (campaign_id) REFERENCES campaigns(id);

-- Outbox (batches to send)
CREATE TABLE IF NOT EXISTS send_batches (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  send_job_id UUID NOT NULL,
  status TEXT NOT NULL DEFAULT 'queued', -- queued/sending/done/failed
  size INT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_send_batches_job ON send_batches(send_job_id);
CREATE INDEX IF NOT EXISTS idx_send_batches_status ON send_batches(status);

ALTER TABLE send_batches
  ADD CONSTRAINT fk_send_batches_tenant
  FOREIGN KEY (tenant_id) REFERENCES tenants(id);

ALTER TABLE send_batches
  ADD CONSTRAINT fk_send_batches_job
  FOREIGN KEY (send_job_id) REFERENCES send_jobs(id);

-- Delivery summary (no per-recipient details for MVP)
CREATE TABLE IF NOT EXISTS delivery_summary (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  send_job_id UUID NOT NULL,
  total INT NOT NULL DEFAULT 0,
  delivered INT NOT NULL DEFAULT 0,
  bounced INT NOT NULL DEFAULT 0,
  complained INT NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, send_job_id)
);
CREATE INDEX IF NOT EXISTS idx_delivery_summary_job ON delivery_summary(send_job_id);

ALTER TABLE delivery_summary
  ADD CONSTRAINT fk_delivery_summary_tenant
  FOREIGN KEY (tenant_id) REFERENCES tenants(id);

ALTER TABLE delivery_summary
  ADD CONSTRAINT fk_delivery_summary_job
  FOREIGN KEY (send_job_id) REFERENCES send_jobs(id);

-- Auth: trusted clients for this service (prevent abuse)
-- Member Center and tenant sites are registered here.
CREATE TABLE IF NOT EXISTS auth_clients (
  id UUID PRIMARY KEY,
  tenant_id UUID, -- NULL for Member Center or global services
  client_id TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  scopes TEXT[] NOT NULL,
  status TEXT NOT NULL DEFAULT 'active', -- active/disabled
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_auth_clients_tenant ON auth_clients(tenant_id);

-- API keys for HMAC or simple client auth (store hash only)
CREATE TABLE IF NOT EXISTS auth_client_keys (
  id UUID PRIMARY KEY,
  client_id UUID NOT NULL,
  key_hash TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  revoked_at TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS idx_auth_client_keys_client ON auth_client_keys(client_id);

ALTER TABLE auth_client_keys
  ADD CONSTRAINT fk_auth_client_keys_client
  FOREIGN KEY (client_id) REFERENCES auth_clients(id);

-- Replay protection for signed webhooks (optional)
CREATE TABLE IF NOT EXISTS webhook_nonces (
  id UUID PRIMARY KEY,
  client_id UUID NOT NULL,
  nonce TEXT NOT NULL,
  received_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (client_id, nonce)
);
CREATE INDEX IF NOT EXISTS idx_webhook_nonces_client ON webhook_nonces(client_id);

ALTER TABLE webhook_nonces
  ADD CONSTRAINT fk_webhook_nonces_client
  FOREIGN KEY (client_id) REFERENCES auth_clients(id);