SaaS billing database schema
This is a normalized billing schema for a B2B SaaS that integrates with Stripe. It tracks customers, subscription tiers, invoices with line items, payment methods, and webhook events - enough to support proration, dunning, and seat-based pricing.
Copy this prompt
Paste this into Raltey's AI agent to generate the same diagram, then tweak it on the canvas.
Design a PostgreSQL database schema for: SaaS billing database schema. This is a normalized billing schema for a B2B SaaS that integrates with Stripe. It tracks customers, subscription tiers, invoices with line items, payment methods, and webhook events - enough to support proration, dunning, and seat-based pricing. Include these tables: customer, subscription, plan, invoice, invoice_line, payment_method, webhook_event. Use proper primary keys, foreign keys, and indexes. Export SQL DDL for PostgreSQL.
Context
- - Optimised for a B2B SaaS on Stripe.
- - Supports seat-based and metered billing on the same schema.
- - Stripe IDs stored separately from internal IDs (never use Stripe ID as PK).
- - Webhook events are persisted for idempotency and replay.
Patterns
- - External-ID column for every Stripe-mapped table.
- - Idempotent webhook ingestion via unique stripe_id on webhook_event.
- - Amounts in cents (integer), never decimals.
- - Optional dunning state managed in subscription.status.
The schema, table by table
customer
Maps an internal account to a Stripe customer.
Table customer {
id uuid pk
workspace_id uuid [ref: > workspace.id, not null]
stripe_id text unique
email text not null
created_at timestamptz default `now()`
}subscription
A single active subscription per customer.
Table subscription {
id uuid pk
customer_id uuid [ref: > customer.id, not null]
plan_id uuid [ref: > plan.id, not null]
status text not null // active, past_due, canceled
seat_count int not null default 1
current_period_start timestamptz not null
current_period_end timestamptz not null
cancel_at_period_end boolean default false
}plan
Pricing plan reference, mirrors Stripe Price.
Table plan {
id uuid pk
stripe_price_id text unique
name text not null
interval text not null // month, year
unit_amount int not null // in cents
currency text default `'usd'`
}invoice
One row per Stripe invoice, including its line items.
Table invoice {
id uuid pk
customer_id uuid [ref: > customer.id, not null]
subscription_id uuid [ref: > subscription.id]
stripe_id text unique
status text not null // open, paid, void
amount_due int not null
amount_paid int default 0
issued_at timestamptz not null
paid_at timestamptz
}invoice_line
Table invoice_line {
id uuid pk
invoice_id uuid [ref: > invoice.id, not null]
description text not null
quantity int default 1
unit_amount int not null
amount int not null
}payment_method
Table payment_method {
id uuid pk
customer_id uuid [ref: > customer.id, not null]
stripe_id text unique
brand text // visa, mastercard
last4 text
is_default boolean default false
}webhook_event
Idempotency log for Stripe webhooks.
Table webhook_event {
id uuid pk
stripe_id text unique not null
type text not null
payload jsonb not null
processed_at timestamptz
}Relationships
| From | To | Type |
|---|---|---|
| workspace | customer | 1:1 |
| customer | subscription | 1:N |
| customer | invoice | 1:N |
| subscription | invoice | 1:N |
| invoice | invoice_line | 1:N |
| customer | payment_method | 1:N |
Notes from the field
- Use a database-level UNIQUE constraint on stripe_id to prevent duplicate webhook ingestion.
- Index (customer_id, issued_at DESC) on invoice for fast billing-history queries.
- Keep webhook_event.payload as the full event JSON - it pays off on the day Stripe ships a new field.
Frequently asked
Should I use Stripe IDs as primary keys?
No. Use your own uuid or bigserial primary keys and keep stripe_id as a unique external reference. This decouples your data model from Stripe and makes future migrations safer.
How do I handle proration in this schema?
Proration shows up as additional invoice_line rows on the next invoice. Stripe computes the amounts; your job is to persist them faithfully via the invoice_line table.
How do I support both seat-based and usage-based billing?
Keep subscription.seat_count for seat-based plans and add a usage_event table that aggregates into invoice_line on each billing period.
More templates
Open this template in Raltey
Edit it on the canvas, ask the AI agent to extend it, then share or export.
Start for free