Identity, Tenancy & RBAC

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

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_*.

Tables

Tenancy

Tenant root and association hierarchy

Name	Columns	Comment	Type
association_users	13		BASE TABLE
associations	14		BASE TABLE
public.organization_users	21	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	BASE TABLE
organizations	40		BASE TABLE

Identity

Contacts and auth linking

Name	Columns	Comment	Type
access_requests	11		BASE TABLE
canonical_identities	10		BASE TABLE
contact_auth	9		BASE TABLE
contact_invitations	20	Invitations to join an org (member or staff), keyed on the canonical contact_id. member_id is an optional link when the invitee is already a chamber member. Renamed from member_invitations (#1990) to converge on the contact identity.	BASE TABLE
contact_relationships	9		BASE TABLE
public.contacts	17	Person identity (auth-linked via contact_auth); used across CRM and membership — Not: A member by itself — membership is members + org seat	BASE TABLE

RBAC

Privilege grants (M-7) + descriptive directory roles

Name	Columns	Comment	Type
board_positions	9		BASE TABLE
public.member_role_assignments	12	Assigns a role_definitions service role to a member (term dates, scope) — Not: A permission grant — privilege is rbac_org_user_roles	BASE TABLE
permission_catalog_entries	7		BASE TABLE
permission_catalog_groups	7		BASE TABLE
rbac_audience_class_definitions	8		BASE TABLE
public.rbac_org_user_permission_sets	9	Assigns permission sets to a contact within an org (composes on top of their role) — Not: rbac_org_user_roles (the base role grant)	BASE TABLE
public.rbac_org_user_roles	6	Org privilege grant — (contact_id x org_id) to an RBAC role; what is_org_admin() / is_org_staff() read — Not: organization_users (a seat, no privilege); user_roles (platform-wide) — See: docs/PLATFORM.md	BASE TABLE
public.rbac_permission_set_definitions	11	Catalog of composable permission sets — the sanctioned way to add a job-function capability — Not: A role; new capabilities are permission sets, never new baseline roles — See: docs/PLATFORM.md	BASE TABLE
public.rbac_role_definitions	12	Catalog of PRIVILEGE-bearing RBAC roles (role_key, default permission + module keys) — Not: role_definitions — that is the member service-role catalog and grants nothing — See: docs/PLATFORM.md	BASE TABLE
public.role_definitions	8	Catalog of member SERVICE roles (board seat, group post) — descriptive only — Not: rbac_role_definitions — this one confers NO permission of any kind	BASE TABLE
public.user_roles	4	PLATFORM-wide role grants (master_admin) — what is_master_admin() reads — Not: Org privilege — that is rbac_org_user_roles. This table is not org-scoped at all — See: docs/PLATFORM.md	BASE TABLE

Autonomy lanes

Per-capability Manual/Assistive/Automated trust dial (data foundation)

Name	Columns	Comment	Type
autonomy_lane_catalog	12	AL-1 (#2207): global catalog of autonomy lanes (per-capability manual/assistive/automated axis). Support seeds: support.answering / support.triage / community.answering (#4271).	BASE TABLE
autonomy_promotions	12	AL-1 (#2207): trust ledger of lane promotion recommendations + decisions.	BASE TABLE
org_autonomy_lanes	13	AL-1 (#2207): per-org autonomy lane config (level, policy, kill switch, dry-run).	BASE TABLE

Relations

erDiagram

"public.association_users" }o--|| "public.associations" : "FOREIGN KEY (association_id) REFERENCES associations(id) ON DELETE CASCADE"
"public.association_users" }o--|| "public.contacts" : "FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE"
"public.board_positions" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE"
"public.board_positions" }o--|| "public.contacts" : "FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE"
"public.canonical_identities" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.contact_auth" |o--|| "public.contacts" : "FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE"
"public.contact_invitations" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"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.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.member_role_assignments" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.member_role_assignments" }o--|| "public.role_definitions" : "FOREIGN KEY (role_definition_id) REFERENCES role_definitions(id) ON DELETE CASCADE"
"public.org_autonomy_lanes" }o--|| "public.autonomy_lane_catalog" : "FOREIGN KEY (lane_key) REFERENCES autonomy_lane_catalog(lane_key) ON DELETE CASCADE"
"public.organization_users" }o--|| "public.organizations" : "FOREIGN KEY (organization_id) REFERENCES organizations(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.associations" : "FOREIGN KEY (association_id) REFERENCES associations(id) ON DELETE SET NULL"
"public.organizations" }o--o| "public.organizations" : "FOREIGN KEY (parent_org_id) REFERENCES organizations(id) ON DELETE SET NULL"
"public.organizations" }o--o| "public.contacts" : "FOREIGN KEY (default_approval_reviewer_contact_id) REFERENCES contacts(id) ON DELETE SET NULL"
"public.permission_catalog_entries" }o--|| "public.permission_catalog_groups" : "FOREIGN KEY (group_key) REFERENCES permission_catalog_groups(group_key)"
"public.rbac_audience_class_definitions" }o--o| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE"
"public.rbac_org_user_permission_sets" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"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_permission_sets" }o--|| "public.rbac_permission_set_definitions" : "FOREIGN KEY (permission_set_id) REFERENCES rbac_permission_set_definitions(id)"
"public.rbac_org_user_roles" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"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.rbac_org_user_roles" }o--|| "public.rbac_role_definitions" : "FOREIGN KEY (role_id) REFERENCES rbac_role_definitions(id)"
"public.rbac_permission_set_definitions" }o--o| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.rbac_role_definitions" }o--o| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.role_definitions" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"

"public.access_requests" {
}
"public.association_users" {
  text association_id ""
  uuid contact_id "#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.associations" {
  text id ""
}
"public.autonomy_lane_catalog" {
  text lane_key ""
}
"public.autonomy_promotions" {
}
"public.board_positions" {
  uuid contact_id ""
  text org_id ""
}
"public.canonical_identities" {
  text org_id ""
}
"public.contact_auth" {
  uuid contact_id ""
}
"public.contact_invitations" {
  uuid contact_id "The person being invited (canonical identity). Required."
  text org_id ""
}
"public.contact_relationships" {
  uuid contact_a_id ""
  uuid contact_b_id ""
}
"public.contacts" {
  uuid id ""
  text primary_org_id ""
  text source_org_id ""
}
"public.member_role_assignments" {
  text org_id ""
  uuid role_definition_id ""
}
"public.org_autonomy_lanes" {
  text lane_key ""
}
"public.organization_users" {
  uuid contact_id ""
  text organization_id ""
}
"public.organizations" {
  text association_id ""
  uuid default_approval_reviewer_contact_id "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 "#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.permission_catalog_entries" {
  text group_key ""
}
"public.permission_catalog_groups" {
  text group_key ""
}
"public.rbac_audience_class_definitions" {
  text org_id ""
}
"public.rbac_org_user_permission_sets" {
  uuid contact_id ""
  uuid granted_by ""
  text org_id ""
  uuid permission_set_id ""
}
"public.rbac_org_user_roles" {
  uuid assigned_by ""
  uuid contact_id ""
  text org_id ""
  uuid role_id ""
}
"public.rbac_permission_set_definitions" {
  uuid id ""
  text org_id ""
}
"public.rbac_role_definitions" {
  uuid id ""
  text org_id ""
}
"public.role_definitions" {
  uuid id ""
  text org_id ""
}
"public.user_roles" {
}

---

Generated by tbls