public.member_role_assignments

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

Assigns a role_definitions service role to a member (term dates, scope) — Not: A permission grant — privilege is rbac_org_user_roles

Columns

Name	Type	Default	Nullable	Children	Parents	Comment
created_at	timestamp with time zone	now()	false
end_date	date		true
id	uuid	gen_random_uuid()	false
member_id	uuid		false		public.members
notes	text		true
org_id	text	‘’::text	false		organizations
role_definition_id	uuid		false		public.role_definitions
scope_id	text		true
scope_type	role_scope_type	‘organization’::role_scope_type	false
start_date	date		true
status	role_assignment_status	‘active’::role_assignment_status	false
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_*.

Constraints

Name	Type	Definition
fk_member_role_assignments_org	FOREIGN KEY	FOREIGN KEY (org_id) REFERENCES organizations(id)
member_role_assignments_member_id_fkey	FOREIGN KEY	FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE
member_role_assignments_pkey	PRIMARY KEY	PRIMARY KEY (id)
member_role_assignments_role_definition_id_fkey	FOREIGN KEY	FOREIGN KEY (role_definition_id) REFERENCES role_definitions(id) ON DELETE CASCADE

Indexes

Name	Definition
idx_mra_member	CREATE INDEX idx_mra_member ON public.member_role_assignments USING btree (member_id)
idx_mra_role	CREATE INDEX idx_mra_role ON public.member_role_assignments USING btree (role_definition_id)
idx_mra_status	CREATE INDEX idx_mra_status ON public.member_role_assignments USING btree (status)
member_role_assignments_pkey	CREATE UNIQUE INDEX member_role_assignments_pkey ON public.member_role_assignments USING btree (id)

Triggers

Name	Definition
trg_enqueue_simpletexting_role_sync_del	CREATE TRIGGER trg_enqueue_simpletexting_role_sync_del AFTER DELETE ON public.member_role_assignments REFERENCING OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION enqueue_simpletexting_role_sync()
trg_enqueue_simpletexting_role_sync_ins	CREATE TRIGGER trg_enqueue_simpletexting_role_sync_ins AFTER INSERT ON public.member_role_assignments REFERENCING NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE FUNCTION enqueue_simpletexting_role_sync()
trg_enqueue_simpletexting_role_sync_upd	CREATE TRIGGER trg_enqueue_simpletexting_role_sync_upd AFTER UPDATE ON public.member_role_assignments REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE FUNCTION enqueue_simpletexting_role_sync()
update_member_role_assignments_updated_at	CREATE TRIGGER update_member_role_assignments_updated_at BEFORE UPDATE ON public.member_role_assignments FOR EACH ROW EXECUTE FUNCTION update_updated_at_column()

Relations

erDiagram

"public.member_role_assignments" }o--|| "public.members" : "FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE"
"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.member_role_assignments" {
  uuid member_id FK ""
  text org_id FK ""
  uuid role_definition_id FK ""
}
"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."
}
"public.role_definitions" {
  uuid id ""
  text org_id FK ""
}

---

Generated by tbls