Below is a production-grade Billing & Subscription database schema, inspired by Stripe / Chargebee / Zuora, designed for multi-tenant SaaS systems.

I’ll explain:

1. All tables
2. Why they exist
3. Key relationships
4. Design decisions (immutability, lifecycle, auditability)

🧾 SaaS Billing & Subscription Database Schema (Complete Guide)

🧠 Core Design Principles

Before schema, understand the rules:

✅ Plans are immutable pricing units
✅ Subscriptions are stateful lifecycles
✅ Invoices are historical & never change
✅ Payments are append-only records
✅ Everything is tenant-isolated

🧱 High-Level Architecture

Tenant
 ├── Customers
 ├── Products
 │    └── Plans
 │         └── Subscriptions
 │              └── Invoices
 │                   └── Payments
 └── Usage Records (optional)

Tenant
 ├── Customers
 ├── Products
 │    └── Plans
 │         └── Subscriptions
 │              └── Invoices
 │                   └── Payments
 └── Usage Records (optional)

1️⃣ tenants

👉 Who owns the billing setup

tenants (
  id UUID PK,
  name TEXT,
  status TEXT,
  created_at TIMESTAMPTZ
)

tenants (
  id UUID PK,
  name TEXT,
  status TEXT,
  created_at TIMESTAMPTZ
)

Why?

• Enables multi-tenant SaaS
• Data isolation
• Tenant-specific pricing

2️⃣ customers

👉 Who pays the money

customers (
  id UUID PK,
  tenant_id UUID FK,
  email TEXT,
  name TEXT,
  external_ref TEXT, -- CRM / Auth ID
  created_at TIMESTAMPTZ
)

customers (
  id UUID PK,
  tenant_id UUID FK,
  email TEXT,
  name TEXT,
  external_ref TEXT, -- CRM / Auth ID
  created_at TIMESTAMPTZ
)

Why?

• A tenant can have many customers
• Decouples auth from billing
• Required for invoicing

3️⃣ products

👉 What is being sold (features)

products (
  id UUID PK,
  tenant_id UUID FK,
  name TEXT,
  description TEXT,
  active BOOLEAN
)

products (
  id UUID PK,
  tenant_id UUID FK,
  name TEXT,
  description TEXT,
  active BOOLEAN
)

Why?

• Logical grouping of plans
• Feature-based products
• One product → many plans

4️⃣ plans (IMMUTABLE)

👉 Pricing + billing rules

plans (
  id UUID PK,
  tenant_id UUID FK,
  product_id UUID FK,
  plan_key TEXT,
  billing_period INTERVAL,
  interval_count INT,
  amount_cents BIGINT,
  currency CHAR(3),
  trial_days INT,
  active BOOLEAN,
  created_at TIMESTAMPTZ,
  UNIQUE (tenant_id, plan_key)
)

plans (
  id UUID PK,
  tenant_id UUID FK,
  product_id UUID FK,
  plan_key TEXT,
  billing_period INTERVAL,
  interval_count INT,
  amount_cents BIGINT,
  currency CHAR(3),
  trial_days INT,
  active BOOLEAN,
  created_at TIMESTAMPTZ,
  UNIQUE (tenant_id, plan_key)
)

Why?

• Defines money
• Never update price or interval
• New price = new plan

🔒 Immutable after use

5️⃣ subscriptions (STATEFUL)

👉 Customer’s active contract

subscriptions (
  id UUID PK,
  tenant_id UUID FK,
  customer_id UUID FK,
  plan_id UUID FK,
  status TEXT,
  start_date TIMESTAMPTZ,
  current_period_start TIMESTAMPTZ,
  current_period_end TIMESTAMPTZ,
  cancel_at_period_end BOOLEAN,
  canceled_at TIMESTAMPTZ
)

subscriptions (
  id UUID PK,
  tenant_id UUID FK,
  customer_id UUID FK,
  plan_id UUID FK,
  status TEXT,
  start_date TIMESTAMPTZ,
  current_period_start TIMESTAMPTZ,
  current_period_end TIMESTAMPTZ,
  cancel_at_period_end BOOLEAN,
  canceled_at TIMESTAMPTZ
)

Why?

• Tracks lifecycle
• Allows upgrades/downgrades
• Does NOT store pricing

Subscription States

INCOMPLETE
ACTIVE
PAST_DUE
CANCELED
EXPIRED

INCOMPLETE
ACTIVE
PAST_DUE
CANCELED
EXPIRED

6️⃣ invoices (HISTORICAL)

👉 Bill generated for a period

invoices (
  id UUID PK,
  tenant_id UUID FK,
  subscription_id UUID FK,
  invoice_number TEXT,
  status TEXT,
  subtotal_cents BIGINT,
  tax_cents BIGINT,
  total_cents BIGINT,
  currency CHAR(3),
  issued_at TIMESTAMPTZ,
  due_at TIMESTAMPTZ
)

invoices (
  id UUID PK,
  tenant_id UUID FK,
  subscription_id UUID FK,
  invoice_number TEXT,
  status TEXT,
  subtotal_cents BIGINT,
  tax_cents BIGINT,
  total_cents BIGINT,
  currency CHAR(3),
  issued_at TIMESTAMPTZ,
  due_at TIMESTAMPTZ
)

Why?

• Legal & financial record
• Never edited after issuance
• Basis for payments & refunds

7️⃣ invoice_items

👉 Line-level billing breakdown

invoice_items (
  id UUID PK,
  invoice_id UUID FK,
  description TEXT,
  amount_cents BIGINT,
  quantity INT
)

invoice_items (
  id UUID PK,
  invoice_id UUID FK,
  description TEXT,
  amount_cents BIGINT,
  quantity INT
)

Why?

• Supports proration
• Discounts
• Add-ons
• Usage charges

8️⃣ payments

👉 Money movement

payments (
  id UUID PK,
  tenant_id UUID FK,
  invoice_id UUID FK,
  provider TEXT,
  provider_payment_id TEXT,
  status TEXT,
  amount_cents BIGINT,
  currency CHAR(3),
  paid_at TIMESTAMPTZ
)

payments (
  id UUID PK,
  tenant_id UUID FK,
  invoice_id UUID FK,
  provider TEXT,
  provider_payment_id TEXT,
  status TEXT,
  amount_cents BIGINT,
  currency CHAR(3),
  paid_at TIMESTAMPTZ
)

Why?

• Append-only ledger
• Multiple attempts per invoice
• Gateway-agnostic

9️⃣ payment_methods

👉 Stored payment instruments

payment_methods (
  id UUID PK,
  customer_id UUID FK,
  provider TEXT,
  token TEXT,
  is_default BOOLEAN
)

payment_methods (
  id UUID PK,
  customer_id UUID FK,
  provider TEXT,
  token TEXT,
  is_default BOOLEAN
)

Why?

• PCI compliance
• Reusable cards
• Auto-billing support

🔟 usage_records (Optional – Metered Billing)

👉 Pay-as-you-go usage

usage_records (
  id UUID PK,
  subscription_id UUID FK,
  metric TEXT,
  quantity BIGINT,
  recorded_at TIMESTAMPTZ
)

usage_records (
  id UUID PK,
  subscription_id UUID FK,
  metric TEXT,
  quantity BIGINT,
  recorded_at TIMESTAMPTZ
)

Why?

• API calls
• Storage usage
• Seat-based billing

🔁 Relationships Summary

🔐 Critical Design Rules (Do NOT Break)

❌ Never update plans.amount
❌ Never delete invoices
❌ Never modify paid invoices
❌ Never store money in subscriptions

🧠 Mental Model (Golden Rule)

Plans define money
Subscriptions define time
Invoices define history
Payments define truth

🏁 Final Thoughts

This schema:
✔ Scales to millions of subscriptions
✔ Supports upgrades, proration, usage billing
✔ Is audit-safe & finance-friendly
✔ Matches Stripe-level design

Read other awesome articles in Medium.com or in akcoding’s posts.

OR

Join us on YouTube Channel

• YouTube

OR Scan the QR Code to Directly open the Channel 👉