Schema ERD

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

GENERATED — DO NOT HAND-EDIT. Regenerated from the live staging schema by tbls. Hand edits will be overwritten on the next run. Exception for humans: edit labels in docs/db/erd-table-labels.yml, then regenerate.

How the system works (durable runbook): docs/runbooks/erd-schema-docs.md

Coverage (full schema — not a sample)

tbls always introspects the full public schema on live staging. What is committed to git is a git-friendly subset (GitHub rejects PR diffs over ~300 files):

In git	Contents
Domain viewpoints	8 × `viewpoint-*.md` with Mermaid ERs
Labeled table pages	Tables in `erd-table-labels.yml` (full column/FK docs + Description)
SCHEMA_INDEX.md	Complete list of every public table from the last run
This README	Naming traps + how to regen

Full local tree (all public.*.md pages): ERD_KEEP_ALL_TABLES=1 bun run docs:erd

Customer help (help/) and PLATFORM.md are separate systems — not generated here.

How to regenerate

bun scripts/generate-erd.ts
# or
bun run docs:erd

Requires:

tbls on PATH (brew install tbls)
Staging reader credentials (SELECT-only + BYPASSRLS):
- macOS Keychain item memberos-db-reader-staging, or
- env MEMBEROS_DB_READER_URL_STAGING
See docs/runbooks/db-reader-role.md for provisioning

Faithfulness / trust

Attribute	Value
Tier	Tier-1 deterministic — HIGH trust
Source	Live staging Postgres via direct reader connection
Tool	tbls (schema introspection → Markdown + Mermaid)
Environment	Staging only — never production
Link mode	Link-free — never `supabase link`; uses `resolveReaderUrl("staging")`
Role	`memberos_reader` — SELECT-only + BYPASSRLS
Labels	Curated in `docs/db/erd-table-labels.yml` (survives regen)

This is machine truth of the current staging schema, not aspirational design docs. If generated docs disagree with prose in docs/analysis/ or issue comments, the live schema wins for “what exists today.” Labels do not change the schema — they only disambiguate names for human readers.

Naming traps (durable labels)

Curated disambiguation for tables whose names look alike. Source of wording: docs/db/erd-table-labels.yml — edit that file, then re-run bun run docs:erd. Labels are injected into each table page as tbls comments and listed here.

`public.` is a schema name, not “world-readable”

The public. prefix on every table is the Postgres schema name (Supabase default for app data) — not “world-readable.” Isolation is RLS + role grants (anon has no SELECT on business tables). Almost every MemberOS business table is public.<name>.

Table labels

Table	Label	Not to be confused with	See
`commerce_products`	Sellable SKU catalog (platform / association / org-owned products)	Chamber member benefit definitions; external product keys	docs/decisions/2581-modules-vs-entitlements-vs-rls.md
`commerce_entitlements`	Plan / agreement commercial limits (seats, quota, access windows)	Chamber member benefit allocations (see member_entitlements)	docs/decisions/2581-modules-vs-entitlements-vs-rls.md
`member_entitlements`	Chamber member benefit allocations (quantity + usage per benefit)	Commerce plan limits (see commerce_entitlements); anchors benefit_definitions	—
`member_product_entitlements`	External product provisioning (e.g. Bullseye access by product_key)	Commerce SKUs; chamber benefit ledger	—
`entitlements`	Legacy / sponsorship-fulfilment entitlement rows (not the platform commerce ledger)	commerce_entitlements (ADR 2581 explicitly distinguishes these)	docs/decisions/2581-modules-vs-entitlements-vs-rls.md
`benefit_definitions`	Catalog of chamber benefits that member_entitlements allocate	commerce_products (sellable SKUs)	—
`members`	Member identity hub row (org-scoped); satellites hold profile/billing/metrics	Full member payload — write profiles/billing/metrics to their tables	docs/PLATFORM.md
`member_profiles`	Member profile satellite (name, contact fields, etc.)	members identity hub; no org_id on satellite — RLS scopes via member	—
`member_billing`	Member billing satellite	commerce_entitlements / plan limits ledger	—
`member_metrics`	Member metrics / engagement satellite	Billing or entitlements	—
`contacts`	Person identity (auth-linked via contact_auth); used across CRM and membership	A member by itself — membership is members + org seat	—
`user_roles`	PLATFORM-wide role grants (master_admin) — what is_master_admin() reads	Org privilege — that is rbac_org_user_roles. This table is not org-scoped at all	docs/PLATFORM.md
`rbac_org_user_roles`	Org privilege grant — (contact_id x org_id) to an RBAC role; what is_org_admin() / is_org_staff() read	organization_users (a seat, no privilege); user_roles (platform-wide)	docs/PLATFORM.md
`rbac_role_definitions`	Catalog of PRIVILEGE-bearing RBAC roles (role_key, default permission + module keys)	role_definitions — that is the member service-role catalog and grants nothing	docs/PLATFORM.md
`role_definitions`	Catalog of member SERVICE roles (board seat, group post) — descriptive only	rbac_role_definitions — this one confers NO permission of any kind	—
`member_role_assignments`	Assigns a role_definitions service role to a member (term dates, scope)	A permission grant — privilege is rbac_org_user_roles	—
`organization_users`	The org seat — presence in an org plus display/email overrides	A privilege record; role_type was DROPPED (#3178) and a seat grants nothing	docs/PLATFORM.md
`rbac_permission_set_definitions`	Catalog of composable permission sets — the sanctioned way to add a job-function capability	A role; new capabilities are permission sets, never new baseline roles	docs/PLATFORM.md
`rbac_org_user_permission_sets`	Assigns permission sets to a contact within an org (composes on top of their role)	rbac_org_user_roles (the base role grant)	—
`member_benefit_usage`	Draw-down ledger against member_entitlements (chamber member benefits)	benefit_usage — that one draws against the legacy/sponsorship entitlements table	—
`benefit_usage`	Draw-down ledger against entitlements (legacy / sponsorship fulfilment)	member_benefit_usage — the chamber member benefit ledger	—
`benefit_reservations`	Holds placed against entitlements before fulfilment (expiring reservations)	benefit_usage — a reservation is not yet consumption	—
`platform_enabled_modules`	Platform-wide module enablement (the default every org inherits)	org_enabled_modules — the per-org override layer	—
`org_enabled_modules`	Per-org module enablement, overriding the platform default	platform_enabled_modules; also not org_enabled_capabilities	—
`platform_enabled_capabilities`	Platform-wide capability enablement (default inherited by every org)	platform_enabled_modules — capabilities are the finer-grained axis	—
`org_enabled_capabilities`	Per-org capability enablement, overriding the platform default	org_enabled_modules — a module is the coarser grouping	—
`user_module_overlays`	Per-user module grant/deny overlay on top of the org’s enabled set	org_enabled_modules (org-wide); this is one contact’s exception	—
`email_send_log`	Per-recipient send ledger (status, block_reason, template) — the real send log	email_send_state, which is dispatcher tuning and holds no per-send rows	—
`email_send_state`	SINGLETON dispatcher tuning/throttle state (batch size, delay, TTLs)	A per-send or per-recipient record — despite the name it logs nothing	—
`email_events`	First-party campaign engagement events (campaign_id, member_id)	email_webhook_events — raw provider callbacks, not our own events	—
`email_webhook_events`	Raw inbound Resend webhook payloads (svix_id, resend_email_id)	email_events — the first-party engagement table	—
`chamber_events`	THE event row — everything on the calendar, public or member-only	signature_events (a planning workspace) or event_templates (a blueprint)	—
`signature_events`	Flagship-event PLANNING workspace (budget, stage, owner); links to chamber_events	An event on the calendar — the real event row is chamber_events	—
`event_templates`	Reusable event blueprint used to seed a new chamber_events row	event_sequence_templates (email sequences); not an event itself	—
`org_partnerships`	Peer org<->org partnership — invite/accept/pause/revoke lifecycle; one LIVE row per pair (pending/active/paused), revoked is terminal, expired can still be revoked	The association/hierarchy subsystem; this is a direct, independent relationship between two orgs	—
`partnership_grants`	One direction’s scope of an org_partnerships row (channels, object types, caps) — asymmetric, at most one row per (partnership, granting org)	org_partnerships itself; a grant never implies the reverse direction is also scoped	—

“Entitlement” — three different concepts

Table	Means
`commerce_entitlements`	Plan / agreement commercial limits (seats, quota, access)
`member_entitlements`	Chamber member benefit allocations (via benefit_definitions)
`member_product_entitlements`	External product provisioning (e.g. Bullseye by product_key)
`entitlements` (bare)	Separate legacy/sponsorship-style ledger — not the commerce one (ADR #2581)

Domain viewpoints

Human-oriented slices aligned to docs/analysis/SYSTEMS-CATALOG.md. Each viewpoint has its own Mermaid ER and table list (see files named viewpoint-*.md).

Viewpoint	Focus
Identity, Tenancy & RBAC	orgs, contacts, contact_auth, organization_users, rbac_*
Membership & Billing	members split-payload, tiers, commerce, billing_*, entitlement naming traps
CRM & Tasks	companies, prospects, tasks, feedback, groups
Events & Registration	chamber_events, registrations, tickets, signature_events
Communications & Journeys	email_*, campaigns, journeys, playbooks
Website, Funnels & Public	website_, funnels, exchange_, hot_deals
Sponsorships	sponsorships, offerings, sponsor placements
Platform Catalog & Modules	permission_catalog_*, modules, autonomy lanes

First review target (Nathan / Craig)

Start with the members / member_profiles / member_billing / member_metrics / contacts cluster:

Viewpoint: Membership & Billing → group “Member core (split payload)”
Individual tables: members, member_profiles, member_billing, member_metrics, contacts

Then the entitlement naming trap groups on the same viewpoint: commerce plan limits vs member benefits vs external product provisioning.

Constraints (do not violate)

Never run supabase link in the repo cwd for this pipeline.
Never point this at production credentials.
Never commit connection strings, passwords, or resolved DSNs.
Output stays under docs/generated/erd/ only.
Table lists in .tbls.yml viewpoints must match real public tables (verified against types.ts / staging).
Edit labels in docs/db/erd-table-labels.yml, not under docs/generated/.

Doc-vs-reality questions

If something here looks wrong, incomplete, or surprises you:

→ Ask Carson (maintainer lane). Do not “fix” the schema from these docs; open a DB Request or staging investigation instead.

Config source

tbls config: .tbls.yml at repo root
Labels: docs/db/erd-table-labels.yml
Generator: scripts/generate-erd.ts
Full table list: SCHEMA_INDEX.md