Communications & Journeys

Synced from docs/generated/erd/ — do not hand-edit.
Regenerate with bun run docs:erd (needs staging reader), then this script runs automatically.
Labels: docs/db/erd-table-labels.yml · Runbook: docs/runbooks/erd-schema-docs.md

Description

Email campaigns, segments, templates, send/hygiene state,
and journey engine (journeys/nodes/edges/runs). Catalog §4.1, §4.4.

email_automations + automation_executions dropped #2213 (P3 UNIT 2) —

journeys.* is the live automation surface; do not re-list those tables here.

Tables

Campaigns & templates

Bulk and templated email

Name	Columns	Comment	Type
ab_test_variants	14		BASE TABLE
campaigns	12	Content Studio A1 (#3394): channel-agnostic planning container. NOT the email send artifact — that stays email_campaigns with its claim state machine.	BASE TABLE
communication_templates	11		BASE TABLE
email_campaigns	25		BASE TABLE
email_segments	9	Transitional back-compat shim for the email_segments->segments rename (#1418, P7). security_invoker=true so base-table (public.segments) RLS is enforced as the caller. DROP in P8 once all callers read/write public.segments and both send edge functions are redeployed.	VIEW
email_templates	15		BASE TABLE

Delivery & hygiene

Send state, unsubscribes, domain health

Name	Columns	Comment	Type
email_domain_checks	4	Domain-level MX cache for #2315 email hygiene. Positive TTL 24-48h, negative/failed TTL ~1h.	BASE TABLE
email_events	7		BASE TABLE
email_hygiene_rate_limits	3	Fixed-window per-user request counter for the email-hygiene edge function (#2315 abuse-control). Rows older than a window or two are harmless dead weight; not worth a cron sweep at this volume.	BASE TABLE
email_send_log	13		BASE TABLE
email_send_state	8		BASE TABLE
email_unsubscribe_tokens	7		BASE TABLE
email_unsubscribes	7		BASE TABLE
email_webhook_events	8	Resend webhook event sink (the §2 proposal “email_events” — renamed; public.email_events is a pre-existing live campaign-analytics table). svix_id UNIQUE for idempotency; service-role writes only; org-scoped SELECT. 24h deliverability windows roll from COUNT(*) here (#2426).	BASE TABLE
member_comm_preferences	8	Per-recipient comms preferences (#830): per-channel master switches, per-category x per-channel matrix (prefs jsonb), pause window, weekly frequency cap. Granular layer beneath email_unsubscribes one-shot suppression.	BASE TABLE
org_email_config	17	Per-org email sending config (one row/org): sender domain identity, BYOK state, rolling 24h deliverability counters. Writes only via SECURITY DEFINER RPCs (no authed write policy). NO mode column by design — organizations.email_mode is authoritative (#2426).	BASE TABLE
platform_email_settings	5		BASE TABLE
suppressed_emails	5		BASE TABLE

Journeys & playbooks

Multi-step process engines

Name	Columns	Comment	Type
automation_templates	12		BASE TABLE
journey_edges	7	#2213 directed edge — mirrors ProcessEdge in processGraph.ts (from/to node + optional branch/gate condition label). No org_id: scope inherited from parent journey.	BASE TABLE
journey_nodes	17	#2213 journey node — mirrors ProcessNode in src/lib/journeys/processGraph.ts (node_type ∈ trigger/step/wait/branch/gate/handoff/exit; lane; owner_role; sla_hours; relationship_critical; handoff_target_flow_id). Mapped to ProcessGraph via journeyAdapter.ts so the PL-2 validator runs on real rows. No org_id: scope inherited from parent journey via RLS.	BASE TABLE
journey_run_events	8	#2213 per-step run event log (entered/step_completed/sla_breached/exited/completed). org_id + journey_id denormalized for analytics filtering. Replaces the Math.random() mock in JourneyAnalyticsDashboard.tsx and feeds the PL-5 stall monitor.	BASE TABLE
journey_runs	12	#2213 a subject’s traversal of a journey. #3688 adds status=failed + error_message/failed_at for terminal hard failures (dispatch unretryable, etc.). Partial-unique uq_journey_runs_active_subject = idempotent enroll: one active run per subject/journey.	BASE TABLE
journeys	19	#2213 canonical journey engine: a published journey definition (draft/published/archived, versioned). PL-6 immutable snapshots are a follow-on. Coexists with legacy event_email_sequences (not migrated).	BASE TABLE
playbook_launches	11		BASE TABLE
playbooks	22		BASE TABLE

-

Name	Columns	Comment	Type
email_admin_audit_log	9	Audit trail for admin email actions (suppress/unsuppress/unsubscribe-restore). org_id NULL = platform-level suppression. snapshot preserves the pre-action row so a restore is itself reversible.	BASE TABLE
email_mode_history	8	Append-only audit log of organizations.email_mode transitions. Rows written exclusively by public.set_org_email_mode(). No INSERT/UPDATE/DELETE policy is present by design — direct writes from authenticated roles must fail.	BASE TABLE
notification_reads	3	Per-user read state for the org-wide notifications broadcast (#3299). notifications.is_read is shared across the org and is retired by this table.	BASE TABLE
notifications	9		BASE TABLE
organizations	35		BASE TABLE
platform_email_domain_status	9	Platform-wide (not per-org) SPF/DKIM/DMARC verification status for the shared sending domains (memberos.co, notify.memberos.co). Seeded from the manual #2200 verification (2026-06-26). Not a live DNS checker — a follow-up periodic re-verification job is a tracked future enhancement. Platform-admin read only (RLS); writes via service-role only.	BASE TABLE
simpletexting_outbox	11	Queue of TCC member changes that need to be pushed to SimpleTexting. Drained by the sync-simpletexting-outbox edge function. Intentionally service-role-only — no GRANT to authenticated.	BASE TABLE

Relations

erDiagram

"public.ab_test_variants" }o--|| "public.email_campaigns" : "FOREIGN KEY (campaign_id) REFERENCES email_campaigns(id) ON DELETE CASCADE"
"public.ab_test_variants" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.campaigns" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE"
"public.email_admin_audit_log" }o--o| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE"
"public.email_campaigns" }o--o| "public.email_templates" : "FOREIGN KEY (template_id) REFERENCES email_templates(id) ON DELETE SET NULL"
"public.email_campaigns" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.email_events" }o--o| "public.email_campaigns" : "FOREIGN KEY (campaign_id) REFERENCES email_campaigns(id) ON DELETE CASCADE"
"public.email_events" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.email_mode_history" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE"
"public.email_send_log" }o--o| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE SET NULL"
"public.email_templates" }o--o| "public.email_templates" : "FOREIGN KEY (parent_template_id) REFERENCES email_templates(id) ON DELETE SET NULL"
"public.email_templates" }o--o| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.email_unsubscribe_tokens" }o--o| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE"
"public.email_unsubscribes" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.email_webhook_events" }o--o| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE SET NULL"
"public.journey_edges" }o--|| "public.journeys" : "FOREIGN KEY (journey_id) REFERENCES journeys(id) ON DELETE CASCADE"
"public.journey_edges" }o--|| "public.journey_nodes" : "FOREIGN KEY (from_node_id) REFERENCES journey_nodes(id) ON DELETE CASCADE"
"public.journey_edges" }o--|| "public.journey_nodes" : "FOREIGN KEY (to_node_id) REFERENCES journey_nodes(id) ON DELETE CASCADE"
"public.journey_nodes" }o--|| "public.journeys" : "FOREIGN KEY (journey_id) REFERENCES journeys(id) ON DELETE CASCADE"
"public.journey_run_events" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE"
"public.journey_run_events" }o--|| "public.journeys" : "FOREIGN KEY (journey_id) REFERENCES journeys(id) ON DELETE CASCADE"
"public.journey_run_events" }o--|| "public.journeys" : "FOREIGN KEY (journey_id, org_id) REFERENCES journeys(id, org_id) ON DELETE CASCADE"
"public.journey_run_events" }o--o| "public.journey_nodes" : "FOREIGN KEY (node_id) REFERENCES journey_nodes(id) ON DELETE SET NULL"
"public.journey_run_events" }o--|| "public.journey_runs" : "FOREIGN KEY (run_id) REFERENCES journey_runs(id) ON DELETE CASCADE"
"public.journey_runs" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE"
"public.journey_runs" }o--|| "public.journeys" : "FOREIGN KEY (journey_id) REFERENCES journeys(id) ON DELETE CASCADE"
"public.journey_runs" }o--|| "public.journeys" : "FOREIGN KEY (journey_id, org_id) REFERENCES journeys(id, org_id) ON DELETE CASCADE"
"public.journey_runs" }o--o| "public.journey_nodes" : "FOREIGN KEY (current_node_id) REFERENCES journey_nodes(id) ON DELETE SET NULL"
"public.journeys" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE"
"public.notification_reads" }o--|| "public.notifications" : "FOREIGN KEY (notification_id) REFERENCES notifications(id) ON DELETE CASCADE"
"public.notifications" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.org_email_config" |o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE"
"public.playbook_launches" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE"
"public.playbook_launches" }o--|| "public.playbooks" : "FOREIGN KEY (playbook_id) REFERENCES playbooks(id) ON DELETE CASCADE"
"public.playbooks" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE"

"public.ab_test_variants" {
  uuid campaign_id ""
  text org_id ""
}
"public.automation_templates" {
}
"public.campaigns" {
  text org_id ""
}
"public.communication_templates" {
}
"public.email_admin_audit_log" {
  text org_id ""
}
"public.email_campaigns" {
  uuid id ""
  text org_id ""
  uuid template_id ""
}
"public.email_domain_checks" {
}
"public.email_events" {
  uuid campaign_id ""
  text org_id ""
}
"public.email_hygiene_rate_limits" {
}
"public.email_mode_history" {
  text org_id ""
}
"public.email_segments" {
}
"public.email_send_log" {
  text org_id "Org context for the email. NULL for platform-level emails (signup before user is org-bound). Drives RLS + per-org analytics (#679)."
}
"public.email_send_state" {
}
"public.email_templates" {
  uuid id ""
  text org_id ""
  uuid parent_template_id ""
}
"public.email_unsubscribe_tokens" {
  text org_id "Org the token was minted for. NULL for legacy/platform tokens (no org context). On unsubscribe, drives the org-scoped email_unsubscribes write (#2424)."
}
"public.email_unsubscribes" {
  text org_id ""
}
"public.email_webhook_events" {
  text org_id "Attributed org. NULL when attribution is unknown at ingest (log-not-yet-committed race) or for BYOK orgs whose events fire on their own account."
}
"public.journey_edges" {
  uuid from_node_id ""
  uuid journey_id ""
  uuid to_node_id ""
}
"public.journey_nodes" {
  uuid id ""
  uuid journey_id ""
}
"public.journey_run_events" {
  uuid journey_id ""
  uuid node_id ""
  text org_id ""
  uuid run_id ""
}
"public.journey_runs" {
  uuid current_node_id ""
  uuid id ""
  uuid journey_id ""
  text org_id ""
}
"public.journeys" {
  uuid id ""
  text org_id ""
}
"public.member_comm_preferences" {
}
"public.notification_reads" {
  uuid notification_id ""
}
"public.notifications" {
  uuid id ""
  text org_id ""
}
"public.org_email_config" {
  text org_id ""
}
"public.organizations" {
  text id ""
}
"public.platform_email_domain_status" {
}
"public.platform_email_settings" {
}
"public.playbook_launches" {
  text org_id ""
  uuid playbook_id ""
}
"public.playbooks" {
  uuid id ""
  text org_id ""
}
"public.simpletexting_outbox" {
}
"public.suppressed_emails" {
}

---

Generated by tbls