public.member_entitlements

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

Chamber member benefit allocations (quantity + usage per benefit) — Not: Commerce plan limits (see commerce_entitlements); anchors benefit_definitions

Columns

Name	Type	Default	Nullable	Children	Parents
allocated_quantity	integer	0	false
benefit_definition_id	uuid		false		public.benefit_definitions
created_at	timestamp with time zone	now()	false
expires_at	date		true
id	uuid	gen_random_uuid()	false	public.member_benefit_usage
member_id	uuid		true		public.members
org_id	text	‘’::text	false		`organizations`
recommendation_priority	integer	0	true
source_id	uuid		true
source_type	text	‘tier’::text	false
start_date	date	CURRENT_DATE	false
status	text	‘active’::text	false
updated_at	timestamp with time zone	now()	false
used_quantity	integer	0	false

Viewpoints

Name Definition

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).

Name	Definition
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).

Constraints

Name	Type	Definition	Comment
fk_member_entitlements_org	FOREIGN KEY	FOREIGN KEY (org_id) REFERENCES organizations(id)
member_entitlements_benefit_definition_id_fkey	FOREIGN KEY	FOREIGN KEY (benefit_definition_id) REFERENCES benefit_definitions(id) ON DELETE RESTRICT	#5153 SPLIT-CATALOG TRAP: this FK still targets benefit_definitions (sponsorship catalog), while tier_benefits now targets membership_benefit_definitions. A tier->member grant path that copies tier_benefits.benefit_definition_id into this column will 23503. Remap the catalog (or this FK) first — see #2582.
member_entitlements_member_id_fkey	FOREIGN KEY	FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE SET NULL
member_entitlements_pkey	PRIMARY KEY	PRIMARY KEY (id)

Indexes

Name	Definition
member_entitlements_pkey	CREATE UNIQUE INDEX member_entitlements_pkey ON public.member_entitlements USING btree (id)

Triggers

Name	Definition
update_member_entitlements_updated_at	CREATE TRIGGER update_member_entitlements_updated_at BEFORE UPDATE ON public.member_entitlements FOR EACH ROW EXECUTE FUNCTION update_updated_at_column()

Relations

erDiagram

"public.member_entitlements" }o--|| "public.benefit_definitions" : "FOREIGN KEY (benefit_definition_id) REFERENCES benefit_definitions(id) ON DELETE RESTRICT"
"public.member_benefit_usage" }o--|| "public.member_entitlements" : "FOREIGN KEY (member_entitlement_id) REFERENCES member_entitlements(id) ON DELETE CASCADE"
"public.member_entitlements" }o--o| "public.members" : "FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE SET NULL"
"public.member_entitlements" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"

"public.member_entitlements" {
  uuid benefit_definition_id FK ""
  uuid id ""
  uuid member_id FK ""
  text org_id FK ""
}
"public.benefit_definitions" {
  uuid id ""
  text org_id FK ""
}
"public.member_benefit_usage" {
  uuid member_entitlement_id FK ""
  text org_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."
}

Generated by tbls