public.email_send_log

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

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

Columns

Name	Type	Default	Nullable	Parents	Comment
block_reason	text		true		Reason a send was blocked by canSend() or by an admin action. Set only when status=blocked. Taxonomy: platform_off \| platform_test_not_allowlisted \| org_off \| org_test_not_allowlisted \| manual_purge.
created_at	timestamp with time zone	now()	false
error_message	text		true
id	uuid	gen_random_uuid()	false
member_id	uuid		true	public.members	Member binding when the recipient is a known member. Used for per-member email pref enforcement (#673) and analytics.
message_id	text		true
message_type	text		true		Taxonomy: transactional \| auth \| marketing \| digest \| reminder \| campaign \| test \| invite. Set by dispatchEmail(). Mirrors the MessageType TS union in supabase/functions/_shared/email-dispatch.ts. No DB CHECK by design — the taxonomy is owned by the TS type and can evolve without a migration.
metadata	jsonb		true
org_id	text		true	`organizations`	Org context for the email. NULL for platform-level emails (signup before user is org-bound). Drives RLS + per-org analytics (#679).
recipient_email	text		false
status	text		false
template_name	text		false
webhook_id	text		true

Viewpoints

Name	Definition
Communications & Journeys	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.

Constraints

Name	Type	Definition
email_send_log_member_id_fkey	FOREIGN KEY	FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE SET NULL
email_send_log_org_id_fkey	FOREIGN KEY	FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE SET NULL
email_send_log_pkey	PRIMARY KEY	PRIMARY KEY (id)
email_send_log_status_check	CHECK	CHECK ((status = ANY (ARRAY[‘pending’::text, ‘sent’::text, ‘suppressed’::text, ‘failed’::text, ‘bounced’::text, ‘complained’::text, ‘dlq’::text, ‘dry_run’::text, ‘allowlist_skipped’::text, ‘dispatch_disabled’::text, ‘rate_limited’::text, ‘demo_redirected_batch’::text, ‘demo_redirected_immediate’::text, ‘skipped_member_pref’::text, ‘blocked’::text, ‘skipped_hygiene’::text, ‘requeued’::text, ‘skipped_quiet_hours’::text, ‘skipped_frequency_cap’::text])))

Indexes

Name	Definition
email_send_log_pkey	CREATE UNIQUE INDEX email_send_log_pkey ON public.email_send_log USING btree (id)
idx_email_send_log_block_reason	CREATE INDEX idx_email_send_log_block_reason ON public.email_send_log USING btree (block_reason) WHERE (block_reason IS NOT NULL)
idx_email_send_log_created	CREATE INDEX idx_email_send_log_created ON public.email_send_log USING btree (created_at DESC)
idx_email_send_log_member	CREATE INDEX idx_email_send_log_member ON public.email_send_log USING btree (member_id) WHERE (member_id IS NOT NULL)
idx_email_send_log_message	CREATE INDEX idx_email_send_log_message ON public.email_send_log USING btree (message_id)
idx_email_send_log_message_sent_unique	CREATE UNIQUE INDEX idx_email_send_log_message_sent_unique ON public.email_send_log USING btree (message_id) WHERE (status = ‘sent’::text)
idx_email_send_log_message_type	CREATE INDEX idx_email_send_log_message_type ON public.email_send_log USING btree (message_type) WHERE (message_type IS NOT NULL)
idx_email_send_log_org_created	CREATE INDEX idx_email_send_log_org_created ON public.email_send_log USING btree (org_id, created_at DESC) WHERE (org_id IS NOT NULL)
idx_email_send_log_org_recipient_lower	CREATE INDEX idx_email_send_log_org_recipient_lower ON public.email_send_log USING btree (org_id, lower(recipient_email))
idx_email_send_log_recipient	CREATE INDEX idx_email_send_log_recipient ON public.email_send_log USING btree (recipient_email)
idx_email_send_log_webhook_id_unique	CREATE UNIQUE INDEX idx_email_send_log_webhook_id_unique ON public.email_send_log USING btree (webhook_id) WHERE (webhook_id IS NOT NULL)

Relations

erDiagram

"public.email_send_log" }o--o| "public.members" : "FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE SET NULL"
"public.email_send_log" }o--o| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE SET NULL"

"public.email_send_log" {
  uuid member_id FK "Member binding when the recipient is a known member. Used for per-member email pref enforcement (#673) and analytics."
  text org_id FK "Org context for the email. NULL for platform-level emails (signup before user is org-bound). Drives RLS + per-org analytics (#679)."
}
"public.members" {
  uuid company_id FK ""
  uuid contact_id FK ""
  uuid id ""
  text org_id FK ""
}
"public.organizations" {
  text association_id FK ""
  uuid default_approval_reviewer_contact_id FK "Org-level fallback reviewer for two-step send (#5698). Routing chain: the sender's own reviewer, then this, then all org admins. NULL means fall through to the admins, which is the pre-#5698 behaviour."
  text id ""
  text parent_org_id FK "#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."
}

Generated by tbls