public.contacts

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

Person identity (auth-linked via contact_auth); used across CRM and membership — Not: A member by itself — membership is members + org seat

Columns

Name	Type	Default	Nullable	Children	Parents	Comment
ai_guidance_mode	text	‘standard’::text	false
archived_at	timestamp with time zone		true
avatar_url	text		true
bio	text		true
created_at	timestamp with time zone	now()	false
email	citext		true
first_name	text		false
id	uuid	gen_random_uuid()	false	association_users author_accounts authors board_positions chamber_event_invitations chamber_event_registrations chamber_events companies contact_auth contact_invitations contact_relationships feedback public.members notification_reads organization_users prospects rbac_org_user_permission_sets rbac_org_user_roles tasks user_module_overlays
job_title	text		true
last_name	text		false
location	text		true
phone	text		true
primary_org_id	text		true		organizations
sms_deliverable	boolean	true	false
source_org_id	text		true		organizations
timezone	text		true
updated_at	timestamp with time zone	now()	false

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_*.
Membership & Billing	Members split-payload (members + member_profiles + member_billing + member_metrics), tiers, commerce/billable offers, billing agreements/subscriptions/transactions. Catalog §2.1–2.2, 2.4. Strong first-review cluster for Nathan/Craig. NAMING TRAP — “entitlement” is three different concepts (see docs/db/erd-table-labels.yml): commerce_entitlements = plan/agreement commercial limits; member_entitlements = chamber member benefit allocations; member_product_entitlements = external product provisioning (e.g. Bullseye). The bare entitlements table is NOT the commerce ledger (ADR #2581).
CRM & Tasks	Companies, prospects, unified tasks/feedback, committees, custom fields. Catalog §2.5–2.6. Note mid-migration: legacy crm_* may coexist with tasks/feedback.

Constraints

Name	Type	Definition
contacts_pkey	PRIMARY KEY	PRIMARY KEY (id)
contacts_primary_org_id_fkey	FOREIGN KEY	FOREIGN KEY (primary_org_id) REFERENCES organizations(id) ON DELETE SET NULL
contacts_source_org_id_fkey	FOREIGN KEY	FOREIGN KEY (source_org_id) REFERENCES organizations(id) ON DELETE SET NULL

Indexes

Name	Definition
contacts_pkey	CREATE UNIQUE INDEX contacts_pkey ON public.contacts USING btree (id)
idx_contacts_email_unique	CREATE UNIQUE INDEX idx_contacts_email_unique ON public.contacts USING btree (email) WHERE ((email IS NOT NULL) AND (archived_at IS NULL))

Triggers

Name	Definition
trg_enqueue_simpletexting_sync_contact	CREATE TRIGGER trg_enqueue_simpletexting_sync_contact AFTER UPDATE OF phone, first_name, last_name ON public.contacts FOR EACH ROW EXECUTE FUNCTION enqueue_simpletexting_sync_contact()
trg_guard_contacts_org_keys	CREATE TRIGGER trg_guard_contacts_org_keys BEFORE UPDATE OF source_org_id, primary_org_id ON public.contacts FOR EACH ROW EXECUTE FUNCTION guard_contacts_org_keys()

Relations

erDiagram

"public.association_users" }o--|| "public.contacts" : "FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE"
"public.author_accounts" }o--o| "public.contacts" : "FOREIGN KEY (owner_contact_id) REFERENCES contacts(id) ON DELETE SET NULL"
"public.authors" }o--o| "public.contacts" : "FOREIGN KEY (approval_owner_contact_id) REFERENCES contacts(id) ON DELETE SET NULL"
"public.authors" }o--o| "public.contacts" : "FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL"
"public.board_positions" }o--|| "public.contacts" : "FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE"
"public.chamber_event_invitations" }o--|| "public.contacts" : "FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE"
"public.chamber_event_invitations" }o--o| "public.contacts" : "FOREIGN KEY (invited_by) REFERENCES contacts(id) ON DELETE SET NULL"
"public.chamber_event_registrations" }o--o| "public.contacts" : "FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL"
"public.chamber_events" }o--o| "public.contacts" : "FOREIGN KEY (host_contact_id) REFERENCES contacts(id) ON DELETE SET NULL"
"public.chamber_events" }o--o| "public.contacts" : "FOREIGN KEY (reviewed_by) REFERENCES contacts(id) ON DELETE SET NULL"
"public.chamber_events" }o--o| "public.contacts" : "FOREIGN KEY (submitted_by_contact_id) REFERENCES contacts(id) ON DELETE SET NULL"
"public.companies" }o--o| "public.contacts" : "FOREIGN KEY (primary_contact_id) REFERENCES contacts(id) ON DELETE SET NULL"
"public.contact_auth" |o--|| "public.contacts" : "FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE"
"public.contact_invitations" }o--|| "public.contacts" : "FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE"
"public.contact_relationships" }o--|| "public.contacts" : "FOREIGN KEY (contact_a_id) REFERENCES contacts(id) ON DELETE CASCADE"
"public.contact_relationships" }o--|| "public.contacts" : "FOREIGN KEY (contact_b_id) REFERENCES contacts(id) ON DELETE CASCADE"
"public.feedback" }o--o| "public.contacts" : "FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE RESTRICT"
"public.members" }o--|| "public.contacts" : "FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE"
"public.notification_reads" }o--|| "public.contacts" : "FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE"
"public.organization_users" }o--|| "public.contacts" : "FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE"
"public.prospects" }o--o| "public.contacts" : "FOREIGN KEY (assigned_to_contact_id) REFERENCES contacts(id) ON DELETE SET NULL"
"public.prospects" }o--|| "public.contacts" : "FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE"
"public.rbac_org_user_permission_sets" }o--|| "public.contacts" : "FOREIGN KEY (contact_id) REFERENCES contacts(id)"
"public.rbac_org_user_permission_sets" }o--o| "public.contacts" : "FOREIGN KEY (granted_by) REFERENCES contacts(id) ON DELETE SET NULL"
"public.rbac_org_user_roles" }o--o| "public.contacts" : "FOREIGN KEY (assigned_by) REFERENCES contacts(id) ON DELETE SET NULL"
"public.rbac_org_user_roles" }o--|| "public.contacts" : "FOREIGN KEY (contact_id) REFERENCES contacts(id)"
"public.tasks" }o--o| "public.contacts" : "FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE RESTRICT"
"public.user_module_overlays" }o--|| "public.contacts" : "FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE"
"public.user_module_overlays" }o--o| "public.contacts" : "FOREIGN KEY (granted_by) REFERENCES contacts(id) ON DELETE SET NULL"
"public.contacts" }o--o| "public.organizations" : "FOREIGN KEY (primary_org_id) REFERENCES organizations(id) ON DELETE SET NULL"
"public.contacts" }o--o| "public.organizations" : "FOREIGN KEY (source_org_id) REFERENCES organizations(id) ON DELETE SET NULL"

"public.contacts" {
  uuid id ""
  text primary_org_id FK ""
  text source_org_id FK ""
}
"public.association_users" {
  text association_id FK ""
  uuid contact_id FK "#3179/#3186 — canonical seat identity key (contacts.id). NOT NULL: every association seat is keyed on (association_id, contact_id); user_id is the optional auth link."
}
"public.author_accounts" {
  uuid author_id FK ""
  text org_id FK ""
  uuid owner_contact_id FK ""
}
"public.authors" {
  uuid approval_owner_contact_id FK ""
  uuid contact_id FK ""
  uuid id ""
  text org_id FK ""
  uuid voice_profile_id FK ""
}
"public.board_positions" {
  uuid contact_id FK ""
  text org_id FK ""
}
"public.chamber_event_invitations" {
  uuid contact_id FK ""
  uuid event_id FK ""
  uuid invited_by FK ""
}
"public.chamber_event_registrations" {
  uuid contact_id FK "Identity root for a prospect registration. NULL on legacy / member rows. When member_id IS NULL AND contact_id IS NOT NULL, the row counts against org.comp_event_limit. Issue #1244."
  uuid event_id FK ""
  uuid id ""
  uuid member_id FK ""
  uuid ticket_type_id FK ""
}
"public.chamber_events" {
  uuid host_contact_id FK ""
  uuid id ""
  text org_id FK ""
  uuid registration_form_id FK ""
  uuid reviewed_by FK ""
  uuid submitted_by_contact_id FK ""
}
"public.companies" {
  uuid id ""
  text org_id FK ""
  uuid primary_contact_id FK ""
}
"public.contact_auth" {
  uuid contact_id FK ""
}
"public.contact_invitations" {
  uuid contact_id FK "The person being invited (canonical identity). Required."
  uuid member_id FK "Optional link to a members row when the invitee is already a member."
  text org_id FK ""
}
"public.contact_relationships" {
  uuid contact_a_id FK ""
  uuid contact_b_id FK ""
}
"public.feedback" {
  uuid company_id FK ""
  uuid contact_id FK ""
  text org_id FK ""
}
"public.members" {
  uuid company_id FK ""
  uuid contact_id FK ""
  uuid id ""
  text org_id FK ""
}
"public.notification_reads" {
  uuid contact_id FK ""
  uuid notification_id FK ""
}
"public.organization_users" {
  uuid contact_id FK ""
  text organization_id FK ""
}
"public.prospects" {
  uuid assigned_to_contact_id FK ""
  uuid contact_id FK ""
  uuid converted_to_member_id FK ""
  text org_id FK ""
}
"public.rbac_org_user_permission_sets" {
  uuid contact_id FK ""
  uuid granted_by FK ""
  text org_id FK ""
  uuid permission_set_id FK ""
}
"public.rbac_org_user_roles" {
  uuid assigned_by FK ""
  uuid contact_id FK ""
  text org_id FK ""
  uuid role_id FK ""
}
"public.tasks" {
  uuid company_id FK ""
  uuid contact_id FK ""
  text org_id FK ""
}
"public.user_module_overlays" {
  uuid contact_id FK ""
  uuid granted_by FK ""
  text module_key FK ""
  text org_id FK ""
}
"public.organizations" {
  text association_id FK ""
  text id ""
}

---

Generated by tbls