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	27		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	16		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
public.email_events	7	First-party campaign engagement events (campaign_id, member_id) — Not: email_webhook_events — raw provider callbacks, not our own events	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_inbound	16	#828 / #4163 inbound mail captured from Resend receiving webhooks. Client role has SELECT only (org staff, via RLS) — writes are service-role via the inbound webhook, and the write privilege is now explicitly revoked from authenticated/anon/PUBLIC (#4586, see 20260811140000).	BASE TABLE
public.email_send_log	13	Per-recipient send ledger (status, block_reason, template) — the real send log — Not: email_send_state, which is dispatcher tuning and holds no per-send rows	BASE TABLE
public.email_send_state	8	SINGLETON dispatcher tuning/throttle state (batch size, delay, TTLs) — Not: A per-send or per-recipient record — despite the name it logs nothing	BASE TABLE
email_unsubscribe_tokens	7		BASE TABLE
email_unsubscribes	7		BASE TABLE
public.email_webhook_events	8	Raw inbound Resend webhook payloads (svix_id, resend_email_id) — Not: email_events — the first-party engagement table	BASE TABLE
inbound_email_routes	7	#828 / #4163 per-org subject-pattern → destination rules for inbound email routing. Org-admin CRUD.	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	21	#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	10		BASE TABLE
organizations	40		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_inbound" }o--o| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE SET NULL"
"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.inbound_email_routes" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE"
"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.journeys" }o--|| "public.journeys" : "FOREIGN KEY (parent_journey_id, org_id) REFERENCES journeys(id, org_id) ON DELETE SET NULL (parent_journey_id)"
"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.organizations" }o--o| "public.organizations" : "FOREIGN KEY (parent_org_id) REFERENCES organizations(id) ON DELETE SET NULL"
"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_inbound" {
  text org_id "Tenant key when known (from send log or member). ON DELETE SET NULL so org wipe does not erase audit rows."
}
"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.inbound_email_routes" {
  text org_id ""
}
"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 ""
  uuid parent_journey_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 ""
  text parent_org_id "#671 self-referencing hierarchy pointer. NULL = root org. Direct writes are blocked by trg_block_direct_parent_org_id_writes for every role except service_role/postgres/supabase_admin — the only write path for authenticated/interactive callers is public.reparent_organization(). Cycle-prevented by trg_prevent_org_hierarchy_cycle. #quot;Is association#quot; is derived (EXISTS a child), never stored."
}
"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