public.organization_users

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

The org seat — presence in an org plus display/email overrides — Not: A privilege record; role_type was DROPPED (#3178) and a seat grants nothing — See: docs/PLATFORM.md

Columns

Name	Type	Default	Nullable	Children	Parents	Comment
association_labels	text[]	‘{}’::text[]	false
contact_id	uuid		false		public.contacts
created_at	timestamp with time zone	now()	true
department	text		true
display_name_override	text		true
email_local_part	text		true			Local part for own-address mode (e.g. elaine -> elaine@org-domain). Null when shared. Format + reserved-word denylist enforced by organization_users_email_local_part_format_check (#4432); unique per org by organization_users_org_email_local_part_uniq.
email_override	citext		true
email_send_mode	text	‘shared’::text	false			Default outbound From: shared org address vs staff local-part under org domain (#4309).
id	uuid	gen_random_uuid()	false
invited_at	timestamp with time zone		true
invited_by_user_id	uuid		true
is_primary_org	boolean	false	true
job_title_override	text		true
joined_at	timestamp with time zone	now()	true
last_active_at	timestamp with time zone		true
organization_id	text		false		organizations
signature_personalization	jsonb	‘{}’::jsonb	false			#4311 {custom_text: string} for the template custom_field_label slot. Self-service via set_my_signature_selection only.
signature_template_id	uuid		true		email_signature_templates	#4311 staff-selected email_signature_templates row for this seat. Self-service via set_my_signature_selection only.
status	text	‘active’::text	false
updated_at	timestamp with time zone	now()	true
user_id	uuid		true

Viewpoints

Name	Definition
Identity, Tenancy & RBAC	Org tenancy, contacts/auth linking, seats (organization_users), and M-7 RBAC. Catalog §1. Seat holds presence only — privilege is rbac_org_user_roles, not organization_users.role_type (dropped). First review: contacts ↔ contact_auth ↔ organization_users ↔ rbac_*.

Constraints

Name	Type	Definition
organization_users_contact_id_fkey	FOREIGN KEY	FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE
organization_users_email_local_part_format_check	CHECK	CHECK (((email_local_part IS NULL) OR ((email_local_part ~ ‘^a-z0-9?$’::text) AND (email_local_part <> ALL (ARRAY[‘noreply’::text, ‘no-reply’::text, ‘postmaster’::text, ‘admin’::text, ‘administrator’::text, ‘abuse’::text, ‘support’::text, ‘info’::text, ‘webmaster’::text, ‘root’::text, ‘mailer-daemon’::text, ‘hostmaster’::text, ‘billing’::text, ‘help’::text, ‘contact’::text, ‘sales’::text, ‘security’::text, ‘privacy’::text])))))
organization_users_email_send_mode_check	CHECK	CHECK ((email_send_mode = ANY (ARRAY[‘shared’::text, ‘own’::text])))
organization_users_org_contact_unique	UNIQUE	UNIQUE (organization_id, contact_id)
organization_users_organization_id_fkey	FOREIGN KEY	FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE
organization_users_organization_id_user_id_key	UNIQUE	UNIQUE (organization_id, user_id)
organization_users_pkey	PRIMARY KEY	PRIMARY KEY (id)
organization_users_signature_template_id_fkey	FOREIGN KEY	FOREIGN KEY (signature_template_id) REFERENCES email_signature_templates(id) ON DELETE SET NULL
organization_users_user_id_fkey	FOREIGN KEY	FOREIGN KEY (user_id) REFERENCES auth.users(id) ON DELETE SET NULL

Indexes

Name	Definition
idx_organization_users_contact_id	CREATE INDEX idx_organization_users_contact_id ON public.organization_users USING btree (contact_id)
idx_organization_users_org_id	CREATE INDEX idx_organization_users_org_id ON public.organization_users USING btree (organization_id)
idx_organization_users_signature_template	CREATE INDEX idx_organization_users_signature_template ON public.organization_users USING btree (signature_template_id) WHERE (signature_template_id IS NOT NULL)
idx_organization_users_user_id	CREATE INDEX idx_organization_users_user_id ON public.organization_users USING btree (user_id)
organization_users_org_contact_unique	CREATE UNIQUE INDEX organization_users_org_contact_unique ON public.organization_users USING btree (organization_id, contact_id)
organization_users_org_email_local_part_uniq	CREATE UNIQUE INDEX organization_users_org_email_local_part_uniq ON public.organization_users USING btree (organization_id, email_local_part) WHERE (email_local_part IS NOT NULL)
organization_users_organization_id_user_id_key	CREATE UNIQUE INDEX organization_users_organization_id_user_id_key ON public.organization_users USING btree (organization_id, user_id)
organization_users_pkey	CREATE UNIQUE INDEX organization_users_pkey ON public.organization_users USING btree (id)

Triggers

Name	Definition	Comment
trg_grant_default_rbac_role	CREATE TRIGGER trg_grant_default_rbac_role AFTER INSERT OR UPDATE ON public.organization_users FOR EACH ROW EXECUTE FUNCTION grant_default_rbac_role_on_join()
trg_guard_organization_users_identity	CREATE TRIGGER trg_guard_organization_users_identity BEFORE INSERT OR UPDATE ON public.organization_users FOR EACH ROW EXECUTE FUNCTION guard_organization_users_identity()	#3173/#3186 — seat identity guard: organization_users’ two identity keys (contact_id, user_id) cannot disagree.
update_organization_users_updated_at	CREATE TRIGGER update_organization_users_updated_at BEFORE UPDATE ON public.organization_users FOR EACH ROW EXECUTE FUNCTION update_updated_at_column()

Relations

erDiagram

"public.organization_users" }o--|| "public.contacts" : "FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE"
"public.organization_users" }o--|| "public.organizations" : "FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE"
"public.organization_users" }o--o| "public.email_signature_templates" : "FOREIGN KEY (signature_template_id) REFERENCES email_signature_templates(id) ON DELETE SET NULL"

"public.organization_users" {
  uuid contact_id FK ""
  text organization_id FK ""
  uuid signature_template_id FK "#4311 staff-selected email_signature_templates row for this seat. Self-service via set_my_signature_selection only."
}
"public.contacts" {
  uuid id ""
  text primary_org_id FK ""
  text source_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."
}
"public.email_signature_templates" {
  uuid id ""
  text org_id FK ""
}

---

Generated by tbls