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 public.chamber_events comms_sender_approval_policy companies contact_auth contact_invitations contact_relationships event_staff_assignments event_testimonial_consents feedback public.members notification_reads public.organization_users organizations prospects public.rbac_org_user_permission_sets public.rbac_org_user_roles tasks public.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, groups, 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.comms_sender_approval_policy" }o--|| "public.contacts" : "FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE"
"public.comms_sender_approval_policy" }o--o| "public.contacts" : "FOREIGN KEY (default_reviewer_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.event_staff_assignments" }o--o| "public.contacts" : "FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL"
"public.event_testimonial_consents" }o--o| "public.contacts" : "FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL"
"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.organizations" }o--o| "public.contacts" : "FOREIGN KEY (default_approval_reviewer_contact_id) REFERENCES contacts(id) ON DELETE SET NULL"
"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.prospects" }o--o| "public.contacts" : "FOREIGN KEY (referred_by_contact_id) REFERENCES contacts(id) ON DELETE SET NULL"
"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 feedback_survey_id FK ""
  uuid group_id FK "The group this event is a meeting of (#4961 Phase 3 / decision 6). NULL for ordinary chamber events. Same-org membership is enforced by trg_chamber_events_group_org_guard, not by the FK. ON DELETE SET NULL keeps the event as ordinary history if the group is removed — CASCADE would destroy attendance records, RESTRICT would make a group undeletable forever."
  uuid host_contact_id FK ""
  uuid id ""
  uuid location_id FK "FK to locations (#3997, epic #3987 T4). NULL means the event still uses the legacy free-text location/venue_name/address/city/region/postal_code/country fields as the render source. Attaching a location does not clear those columns — they remain a fallback and an audit trail."
  uuid location_room_id FK "Optional FK to location_rooms, only meaningful when location_id is set. Enforced same-location membership via trg_chamber_events_location_org_guard, not a DB constraint (no CHECK can join to another table)."
  text org_id FK ""
  uuid recurrence_series_id FK "#4366/#771 FK to chamber_event_series. ON DELETE SET NULL — never cascade-delete events with live registrations."
  uuid registration_form_id FK ""
  uuid registration_survey_id FK ""
  uuid reviewed_by FK ""
  uuid submitted_by_contact_id FK ""
}
"public.comms_sender_approval_policy" {
  uuid contact_id FK ""
  uuid default_reviewer_contact_id FK ""
  text org_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.event_staff_assignments" {
  uuid contact_id FK "public.contacts is not org-scoped by a single column (source_org_id / primary_org_id only), so a same-org FK guard is not well-defined here the way it is for member_id. No guard added, matching #5313's scope."
  uuid event_id FK ""
  uuid id ""
  uuid member_id FK "KNOWN RESIDUAL, stated not built: no trigger or RLS check confirms the referenced member.org_id matches this row's org_id. A cross-org guard could follow 20260818160000's groups.meeting_location_id precedent; out of scope for #5313 and rated material-not-blocking by the adversarial pass on the first draft."
}
"public.event_testimonial_consents" {
  uuid contact_id FK ""
  uuid event_id FK ""
  uuid feedback_id FK ""
  text org_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 ""
  uuid signature_template_id FK "#4311 staff-selected email_signature_templates row for this seat. Self-service via set_my_signature_selection only."
}
"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.prospects" {
  uuid assigned_to_contact_id FK ""
  uuid contact_id FK ""
  uuid converted_to_member_id FK ""
  text org_id FK ""
  uuid referred_by_contact_id FK "Contact who referred this prospect (member or prospect subject). #4232 reciprocal CRM link."
}
"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 ""
}

---

Generated by tbls