The short version
If you sync Stripe into your own database (or are building Stripe-like billing), you need 9 tables: customers, products, prices, subscriptions, subscription_items, invoices, invoice_line_items, payment_methods, and payment_intents. Customers own everything; subscriptions compose prices; invoices snapshot what was billed; payment intents move the money. This is the full Stripe billing model as PostgreSQL.
The 9 tables and how they connect
| Table | What it holds | Key FK |
|---|---|---|
customers | Who pays (email, name, balance) | — (core) |
products | What you sell (name, active) | — |
prices | How much + how often (unit_amount, interval) | product_id → products.id |
subscriptions | A customer's recurring purchase | customer_id → customers.id |
subscription_items | Each price in a subscription (quantity) | subscription_id, price_id |
invoices | A bill for a period | customer_id, subscription_id |
invoice_line_items | Line on an invoice | invoice_id, price_id |
payment_methods | Card / bank on file | customer_id |
payment_intents | An attempt to charge | customer_id, invoice_id, payment_method_id |
The billing flow as joins
-- What is this customer subscribed to, at what price?
select c.email, pr.name as product, p.unit_amount, si.quantity
from customers c
join subscriptions s on s.customer_id = c.id
join subscription_items si on si.subscription_id = s.id
join prices p on p.id = si.price_id
join products pr on pr.id = p.product_id
where s.status = 'active';
-- Unpaid invoices with their payment attempt
select i.id, i.amount_due, pi.status as payment_status
from invoices i
left join payment_intents pi on pi.invoice_id = i.id
where i.status != 'paid';Design details worth copying
- Prices, not products, are what you bill. One product ("Pro Plan") can have many prices ($10/mo, $100/yr). Subscriptions reference prices, not products directly.
- Subscription items allow multi-product subscriptions. One subscription can contain multiple prices (base + add-on) with independent quantities.
- Invoices are snapshots.
invoice_line_itemscopies price and amount at invoice time — even if the price later changes, the invoice stays truthful. - Payment intents are the audit trail. Every charge attempt is a row, even failed ones. Join to invoices to see what was paid and what wasn't.
See it visually: the Stripe billing schema diagram renders all 9 tables with foreign keys as navigable relationships — the same picture you'd get introspecting a live Stripe-synced database.
FAQ
What tables does a Stripe billing schema need?
9 tables: customers, products, prices, subscriptions, subscription_items, invoices, invoice_line_items, payment_methods, and payment_intents.
Should subscriptions reference products or prices?
Prices. A product is a catalog entry; a price is a purchasable variant (amount + interval). Subscriptions are composed of subscription_items that each reference a price.
How does Stripe handle multiple products in one subscription?
Through subscription_items — one row per price in the subscription, each with its own quantity. One subscription, many items.
What's the difference between an invoice and a payment intent?
An invoice is a bill (what is owed). A payment intent is an attempt to collect it (what was charged, status, method). One invoice can have multiple payment intents (retries).