Skip to content
MemberHeroMemberHeroMemberHero Help

Membership & Billing

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

Section titled “Description”

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

Tables

Section titled “Tables”

Member core (split payload)

Section titled “Member core (split payload)”

members + profiles/billing/metrics — first review target

Name Columns Comment Type
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
public.member_billing 7 Member billing satellite — Not: commerce_entitlements / plan limits ledger BASE TABLE
public.member_metrics 12 Member metrics / engagement satellite — Not: Billing or entitlements BASE TABLE
public.member_profiles 22 Member profile satellite (name, contact fields, etc.) — Not: members identity hub; no org_id on satellite — RLS scopes via member BASE TABLE
public.members 17 Member identity hub row (org-scoped); satellites hold profile/billing/metrics — Not: Full member payload — write profiles/billing/metrics to their tables — See: docs/PLATFORM.md BASE TABLE

Membership structure

Section titled “Membership structure”

Tiers, units, applications

Name Columns Comment Type
membership_applications 20 BASE TABLE
membership_tiers_config 38 BASE TABLE
membership_types 6 BASE TABLE
membership_unit_members 6 BASE TABLE
membership_units 12 BASE TABLE
package_benefits 8 BASE TABLE
tier_benefits 8 BASE TABLE
tier_features 7 BASE TABLE

Commerce plan limits (commerce_*)

Section titled “Commerce plan limits (commerce_*)”

Sellable SKUs + commercial limits on a billing agreement.
commerce_entitlements = plan/agreement commercial limits — NOT member benefits.

Name Columns Comment Type
billable_offers 25 BASE TABLE
billing_agreements 27 BASE TABLE
billing_customers 15 BASE TABLE
billing_discounts 19 BASE TABLE
billing_subscriptions 17 BASE TABLE
billing_transactions 18 BASE TABLE
checkout_flows 13 BASE TABLE
public.commerce_entitlements 14 Plan / agreement commercial limits (seats, quota, access windows) — Not: Chamber member benefit allocations (see member_entitlements) — See: docs/decisions/2581-modules-vs-entitlements-vs-rls.md BASE TABLE
public.commerce_products 16 Sellable SKU catalog (platform / association / org-owned products) — Not: Chamber member benefit definitions; external product keys — See: docs/decisions/2581-modules-vs-entitlements-vs-rls.md BASE TABLE
invoices 27 BASE TABLE
payments 18 BASE TABLE
plan_price_variants 15 BASE TABLE
seat_allocations 12 BASE TABLE

Member benefits (not commerce)

Section titled “Member benefits (not commerce)”

Chamber benefit allocations — NOT plan SKUs / commercial limits.
member_entitlements anchors benefit_definitions.

Name Columns Comment Type
public.benefit_definitions 10 Catalog of chamber benefits that member_entitlements allocate — Not: commerce_products (sellable SKUs) BASE TABLE
public.member_benefit_usage 7 Draw-down ledger against member_entitlements (chamber member benefits) — Not: benefit_usage — that one draws against the legacy/sponsorship entitlements table BASE TABLE
public.member_entitlements 14 Chamber member benefit allocations (quantity + usage per benefit) — Not: Commerce plan limits (see commerce_entitlements); anchors benefit_definitions BASE TABLE

External product provisioning

Section titled “External product provisioning”

Downstream product access by product_key (e.g. Bullseye) —
NOT commerce_products and NOT member_entitlements.

Name Columns Comment Type
public.member_product_entitlements 11 External product provisioning (e.g. Bullseye access by product_key) — Not: Commerce SKUs; chamber benefit ledger BASE TABLE

Other entitlements table

Section titled “Other entitlements table”

Bare entitlements is a separate concept (sponsorship-fulfilment style);
ADR #2581 — not the platform commerce ledger.

Name Columns Comment Type
public.entitlements 14 Legacy / sponsorship-fulfilment entitlement rows (not the platform commerce ledger) — Not: commerce_entitlements (ADR 2581 explicitly distinguishes these) — See: docs/decisions/2581-modules-vs-entitlements-vs-rls.md BASE TABLE

Renewal risk

Section titled “Renewal risk”

Churn scoring and interventions

Name Columns Comment Type
recommended_interventions 16 BASE TABLE
renewal_risks 30 BASE TABLE
risk_scoring_runs 10 BASE TABLE

-

Section titled “-”
Name Columns Comment Type
member_comm_preferences 8 Per-recipient comms preferences (#830): per-channel master switches, per-category x per-channel matrix (prefs jsonb), pause window, weekly frequency cap. Granular layer beneath email_unsubscribes one-shot suppression. BASE TABLE
member_preferences 17 BASE TABLE
organizations 40 BASE TABLE

Relations

Section titled “Relations”
erDiagram

"public.benefit_definitions" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.billing_agreements" }o--|| "public.commerce_products" : "FOREIGN KEY (product_id) REFERENCES commerce_products(id) ON DELETE RESTRICT"
"public.billing_subscriptions" }o--o| "public.members" : "FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE RESTRICT"
"public.billing_subscriptions" }o--o| "public.membership_tiers_config" : "FOREIGN KEY (tier_id) REFERENCES membership_tiers_config(id) ON DELETE SET NULL"
"public.billing_subscriptions" }o--o| "public.membership_units" : "FOREIGN KEY (membership_unit_id) REFERENCES membership_units(id) ON DELETE SET NULL"
"public.billing_subscriptions" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.billing_transactions" }o--o| "public.billable_offers" : "FOREIGN KEY (offer_id) REFERENCES billable_offers(id) ON DELETE SET NULL"
"public.billing_transactions" }o--o| "public.billing_customers" : "FOREIGN KEY (customer_id) REFERENCES billing_customers(id) ON DELETE SET NULL"
"public.checkout_flows" }o--o| "public.billable_offers" : "FOREIGN KEY (offer_id) REFERENCES billable_offers(id) ON DELETE SET NULL"
"public.commerce_entitlements" }o--|| "public.billing_agreements" : "FOREIGN KEY (billing_agreement_id) REFERENCES billing_agreements(id) ON DELETE CASCADE"
"public.commerce_entitlements" }o--|| "public.commerce_products" : "FOREIGN KEY (product_id) REFERENCES commerce_products(id) ON DELETE RESTRICT"
"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.entitlements" }o--|| "public.benefit_definitions" : "FOREIGN KEY (benefit_definition_id) REFERENCES benefit_definitions(id) ON DELETE RESTRICT"
"public.entitlements" }o--o| "public.membership_units" : "FOREIGN KEY (membership_unit_id) REFERENCES membership_units(id) ON DELETE SET NULL"
"public.entitlements" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.invoices" }o--o| "public.billing_agreements" : "FOREIGN KEY (billing_agreement_id) REFERENCES billing_agreements(id) ON DELETE SET NULL"
"public.invoices" }o--o| "public.members" : "FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE SET NULL"
"public.invoices" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.member_benefit_usage" }o--|| "public.member_entitlements" : "FOREIGN KEY (member_entitlement_id) REFERENCES member_entitlements(id) ON DELETE CASCADE"
"public.member_benefit_usage" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.member_billing" |o--|| "public.members" : "FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE"
"public.member_comm_preferences" |o--|| "public.members" : "FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE"
"public.member_entitlements" }o--|| "public.benefit_definitions" : "FOREIGN KEY (benefit_definition_id) REFERENCES benefit_definitions(id) ON DELETE RESTRICT"
"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_metrics" |o--|| "public.members" : "FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE"
"public.member_preferences" |o--|| "public.members" : "FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE"
"public.member_product_entitlements" }o--|| "public.members" : "FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE"
"public.member_profiles" |o--|| "public.members" : "FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE"
"public.members" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.members" }o--|| "public.contacts" : "FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE"
"public.membership_applications" }o--o| "public.members" : "FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE SET NULL"
"public.membership_applications" }o--o| "public.membership_tiers_config" : "FOREIGN KEY (tier_id) REFERENCES membership_tiers_config(id) ON DELETE SET NULL"
"public.membership_applications" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.membership_tiers_config" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.membership_types" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.membership_unit_members" }o--|| "public.members" : "FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE"
"public.membership_unit_members" }o--|| "public.membership_units" : "FOREIGN KEY (membership_unit_id) REFERENCES membership_units(id) ON DELETE CASCADE"
"public.membership_units" }o--o| "public.members" : "FOREIGN KEY (owner_member_id) REFERENCES members(id) ON DELETE SET NULL"
"public.membership_units" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"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.package_benefits" }o--|| "public.benefit_definitions" : "FOREIGN KEY (benefit_definition_id) REFERENCES benefit_definitions(id) ON DELETE CASCADE"
"public.package_benefits" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.payments" }o--o| "public.billing_agreements" : "FOREIGN KEY (billing_agreement_id) REFERENCES billing_agreements(id) ON DELETE SET NULL"
"public.payments" }o--o| "public.invoices" : "FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE SET NULL"
"public.payments" }o--o| "public.members" : "FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE SET NULL"
"public.payments" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.plan_price_variants" }o--|| "public.commerce_products" : "FOREIGN KEY (product_id) REFERENCES commerce_products(id) ON DELETE CASCADE"
"public.recommended_interventions" }o--o| "public.members" : "FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE SET NULL"
"public.recommended_interventions" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.recommended_interventions" }o--o| "public.renewal_risks" : "FOREIGN KEY (renewal_risk_id) REFERENCES renewal_risks(id) ON DELETE CASCADE"
"public.renewal_risks" }o--o| "public.members" : "FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE SET NULL"
"public.renewal_risks" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.risk_scoring_runs" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.seat_allocations" }o--|| "public.billing_agreements" : "FOREIGN KEY (billing_agreement_id) REFERENCES billing_agreements(id) ON DELETE CASCADE"
"public.seat_allocations" }o--|| "public.commerce_products" : "FOREIGN KEY (product_id) REFERENCES commerce_products(id) ON DELETE RESTRICT"
"public.tier_benefits" }o--|| "public.membership_tiers_config" : "FOREIGN KEY (tier_id) REFERENCES membership_tiers_config(id) ON DELETE CASCADE"
"public.tier_benefits" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.tier_features" }o--o| "public.membership_tiers_config" : "FOREIGN KEY (tier_id) REFERENCES membership_tiers_config(id) ON DELETE CASCADE"

"public.benefit_definitions" {
  uuid id ""
  text org_id ""
}
"public.billable_offers" {
  uuid id ""
}
"public.billing_agreements" {
  uuid id ""
  uuid product_id ""
}
"public.billing_customers" {
  uuid id ""
}
"public.billing_discounts" {
}
"public.billing_subscriptions" {
  uuid member_id ""
  uuid membership_unit_id ""
  text org_id ""
  uuid tier_id ""
}
"public.billing_transactions" {
  uuid customer_id ""
  uuid offer_id ""
}
"public.checkout_flows" {
  uuid offer_id ""
}
"public.commerce_entitlements" {
  uuid billing_agreement_id ""
  uuid product_id ""
}
"public.commerce_products" {
  uuid id ""
}
"public.contacts" {
  uuid id ""
  text primary_org_id ""
  text source_org_id ""
}
"public.entitlements" {
  uuid benefit_definition_id ""
  uuid membership_unit_id ""
  text org_id ""
}
"public.invoices" {
  uuid billing_agreement_id ""
  uuid id ""
  uuid member_id ""
  text org_id ""
}
"public.member_benefit_usage" {
  uuid member_entitlement_id ""
  text org_id ""
}
"public.member_billing" {
  uuid member_id ""
}
"public.member_comm_preferences" {
  uuid member_id ""
}
"public.member_entitlements" {
  uuid benefit_definition_id ""
  uuid id ""
  uuid member_id ""
  text org_id ""
}
"public.member_metrics" {
  uuid member_id ""
}
"public.member_preferences" {
  uuid member_id ""
}
"public.member_product_entitlements" {
  uuid member_id ""
}
"public.member_profiles" {
  uuid member_id ""
}
"public.members" {
  uuid contact_id ""
  uuid id ""
  text org_id ""
}
"public.membership_applications" {
  uuid member_id ""
  text org_id ""
  uuid tier_id ""
}
"public.membership_tiers_config" {
  uuid id ""
  text org_id ""
}
"public.membership_types" {
  text org_id ""
}
"public.membership_unit_members" {
  uuid member_id ""
  uuid membership_unit_id ""
}
"public.membership_units" {
  uuid id ""
  text org_id ""
  uuid owner_member_id ""
}
"public.organizations" {
  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.package_benefits" {
  uuid benefit_definition_id ""
  text org_id ""
}
"public.payments" {
  uuid billing_agreement_id ""
  uuid invoice_id ""
  uuid member_id ""
  text org_id ""
}
"public.plan_price_variants" {
  uuid product_id ""
}
"public.recommended_interventions" {
  uuid member_id ""
  text org_id ""
  uuid renewal_risk_id ""
}
"public.renewal_risks" {
  uuid id ""
  uuid member_id ""
  text org_id ""
}
"public.risk_scoring_runs" {
  text org_id ""
}
"public.seat_allocations" {
  uuid billing_agreement_id ""
  uuid product_id ""
}
"public.tier_benefits" {
  text org_id ""
  uuid tier_id ""
}
"public.tier_features" {
  uuid tier_id ""
}

Generated by tbls