Sponsorships

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

Sponsorship offerings and bookings, event sponsor slots/fulfillment,
sponsor impressions/placements, package benefits. Catalog §2.3.
(Table names on staging: sponsorships / sponsorship_offerings — not
sponsorship_inventory from older prose.)

Tables

Inventory & bookings

Sellable sponsorship inventory and sold bookings

Name	Columns	Comment	Type
public.benefit_definitions	10	Catalog of chamber benefits that member_entitlements allocate — Not: commerce_products (sellable SKUs)	BASE TABLE
public.benefit_reservations	10	Holds placed against entitlements before fulfilment (expiring reservations) — Not: benefit_usage — a reservation is not yet consumption	BASE TABLE
public.benefit_usage	11	Draw-down ledger against entitlements (legacy / sponsorship fulfilment) — Not: member_benefit_usage — the chamber member benefit ledger	BASE TABLE
package_benefits	8		BASE TABLE
sponsorship_offerings	24		BASE TABLE
sponsorships	26		BASE TABLE

Event linkage

Event-scoped sponsor slots

Name	Columns	Comment	Type
event_sponsor_slots	9		BASE TABLE

Measurement

Impressions and placements

Name	Columns	Comment	Type
sponsor_impressions	9		BASE TABLE
sponsor_placements	14		BASE TABLE

Commerce bridge

Checkout/billing when sponsorship is sold as a billable offer

Name	Columns	Comment	Type
billable_offers	25		BASE TABLE
billing_agreements	27		BASE TABLE

-

Name	Columns	Comment	Type
companies	20		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
organizations	40		BASE TABLE
sponsorship_credit_balances	8	#3773 — current Sponsorship Credit balance per party (member or company), one row per party per org. A CACHE derived from sponsorship_credit_ledger, not a source of truth — written only by adjust_sponsorship_credits(). Never client-writable: see the file header for why this is a table and not a view.	BASE TABLE
sponsorship_credit_ledger	16	#3773 — append-only Sponsorship Credit audit log. Every grant, deduction, purchase, refund and admin adjustment. Client-writable NEVER — see adjust_sponsorship_credits().	BASE TABLE
sponsorship_newsletter_issues	5	#3774 — attaches a Newsletter sponsorship to one or more email_campaigns rows (“issues”). One sponsorship can sponsor a series (multiple rows).	BASE TABLE
sponsorship_website_months	5	#3774 — whole-month bookings of the org’s single website banner slot. UNIQUE(org_id, month_start) is the real one-sponsor-per-month enforcement.	BASE TABLE

Relations

erDiagram

"public.benefit_definitions" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.benefit_reservations" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.benefit_usage" }o--o| "public.benefit_reservations" : "FOREIGN KEY (reservation_id) REFERENCES benefit_reservations(id) ON DELETE SET NULL"
"public.benefit_usage" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.companies" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.event_sponsor_slots" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.members" }o--o| "public.companies" : "FOREIGN KEY (company_id) REFERENCES companies(id)"
"public.members" }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.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.package_benefits" }o--|| "public.sponsorship_offerings" : "FOREIGN KEY (sponsor_package_id) REFERENCES sponsorship_offerings(id) ON DELETE CASCADE"
"public.sponsor_impressions" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.sponsor_impressions" }o--|| "public.sponsorships" : "FOREIGN KEY (subscription_id) REFERENCES sponsorships(id) ON DELETE CASCADE"
"public.sponsor_placements" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.sponsorship_credit_balances" }o--o| "public.companies" : "FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE"
"public.sponsorship_credit_balances" }o--o| "public.members" : "FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE"
"public.sponsorship_credit_balances" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE"
"public.sponsorship_credit_ledger" }o--o| "public.companies" : "FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL"
"public.sponsorship_credit_ledger" }o--o| "public.members" : "FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE SET NULL"
"public.sponsorship_credit_ledger" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE"
"public.sponsorship_credit_ledger" }o--o| "public.sponsorships" : "FOREIGN KEY (sponsorship_id) REFERENCES sponsorships(id) ON DELETE SET NULL"
"public.sponsorship_newsletter_issues" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE"
"public.sponsorship_newsletter_issues" }o--|| "public.sponsorships" : "FOREIGN KEY (sponsorship_id) REFERENCES sponsorships(id) ON DELETE CASCADE"
"public.sponsorship_offerings" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.sponsorship_website_months" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE"
"public.sponsorship_website_months" }o--|| "public.sponsorships" : "FOREIGN KEY (sponsorship_id) REFERENCES sponsorships(id) ON DELETE CASCADE"
"public.sponsorships" }o--o| "public.companies" : "FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL"
"public.sponsorships" }o--o| "public.members" : "FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE SET NULL"
"public.sponsorships" }o--|| "public.organizations" : "FOREIGN KEY (org_id) REFERENCES organizations(id)"
"public.sponsorships" }o--o| "public.sponsorships" : "FOREIGN KEY (subscription_id) REFERENCES sponsorships(id) ON DELETE SET NULL"
"public.sponsorships" }o--|| "public.sponsorship_offerings" : "FOREIGN KEY (offering_id) REFERENCES sponsorship_offerings(id)"

"public.benefit_definitions" {
  uuid id ""
  text org_id ""
}
"public.benefit_reservations" {
  uuid id ""
  text org_id ""
}
"public.benefit_usage" {
  text org_id ""
  uuid reservation_id ""
}
"public.billable_offers" {
}
"public.billing_agreements" {
}
"public.companies" {
  uuid id ""
  text org_id ""
}
"public.event_sponsor_slots" {
  text org_id ""
}
"public.members" {
  uuid company_id ""
  uuid id ""
  text org_id ""
}
"public.organizations" {
  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 ""
  uuid sponsor_package_id ""
}
"public.sponsor_impressions" {
  text org_id ""
  uuid subscription_id ""
}
"public.sponsor_placements" {
  text org_id ""
}
"public.sponsorship_credit_balances" {
  uuid company_id ""
  uuid member_id ""
  text org_id ""
}
"public.sponsorship_credit_ledger" {
  uuid company_id ""
  uuid member_id ""
  text org_id ""
  uuid sponsorship_id ""
}
"public.sponsorship_newsletter_issues" {
  text org_id ""
  uuid sponsorship_id ""
}
"public.sponsorship_offerings" {
  uuid id ""
  text org_id ""
}
"public.sponsorship_website_months" {
  text org_id ""
  uuid sponsorship_id ""
}
"public.sponsorships" {
  uuid company_id ""
  uuid id ""
  uuid member_id ""
  uuid offering_id "#3542: FK to sponsorship_offerings (renamed from tier_id)."
  text org_id ""
  uuid subscription_id ""
}

---

Generated by tbls