SaaS database architecture decisions made in the first week will haunt you for years. This guide walks through a battle-tested multi-tenant schema design covering users, organizations, role-based memberships, subscription state machines, and billing records. Each section includes production-ready SQL, indexing strategies, and migration patterns you can adapt directly to PostgreSQL, D1, or any relational database.
Introduction
Every SaaS founder starts with a simple vision: sign up, pay, use the product. But under the hood, that "simple" flow requires a database schema that balances multi-tenancy isolation, billing accuracy, and future flexibility. Get it wrong, and you'll be rewriting migrations six months in — while your customers are waiting for invoices.
The schema you design today determines how easily you can:
- Add organization-level billing without breaking per-user features
- Implement role-based access control (RBAC) across teams
- Run trial-to-paid conversions without manual data fixes
- Generate accurate invoices and revenue reports
Multi-Tenancy Strategy
Before we write a single CREATE TABLE, we need to decide how to isolate data between tenants:
| Strategy | Isolation | Complexity | Best For |
|---|---|---|---|
| Row-level (shared DB) | Low | Low | Early-stage, B2C |
| Schema-level (separate schemas) | Medium | Medium | Mid-stage, regulatory needs |
| Database-level (separate DBs) | High | High | Enterprise, PCI/HIPAA |
For 90% of SaaS products starting today, row-level multi-tenancy with a shared database is the correct choice. It keeps operational complexity low and allows cross-tenant analytics with a simple WHERE organization_id = ?.
The key is to enforce tenant isolation at the application layer — every query must filter by organization_id. Never trust client-provided IDs.
Core Entities and Relationships
User ── MemberOf ── Organization
│
Subscription
│
Invoice
│
InvoiceLineItem
1. Users
Users are individuals who authenticate. Keep this table lean — identity data only.
CREATE TABLE users (
id TEXT PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
avatar_url TEXT,
created_at INTEGER NOT NULL DEFAULT (unixepoch()),
updated_at INTEGER NOT NULL DEFAULT (unixepoch())
);
CREATE INDEX idx_users_email ON users(email);
Design notes: Use TEXT IDs (UUIDs) to avoid enumeration attacks. Store timestamps as Unix integers for timezone-agnostic comparisons.
2. Organizations
Organizations are the tenant boundary. Every subscription belongs to an org, not a user.
CREATE TABLE organizations (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
slug TEXT NOT NULL UNIQUE,
logo_url TEXT,
created_at INTEGER NOT NULL DEFAULT (unixepoch()),
updated_at INTEGER NOT NULL DEFAULT (unixepoch())
);
CREATE UNIQUE INDEX idx_orgs_slug ON organizations(slug);
Why orgs matter: Billing to users directly breaks when a team of five wants one shared subscription. Always bill to organizations. Users come and go; organizations persist.
3. Memberships (Users ↔ Organizations)
The join table with roles. Never store the role directly on the users table.
CREATE TABLE memberships (
id TEXT PRIMARY KEY,
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
role TEXT NOT NULL DEFAULT 'member' CHECK (role IN ('owner', 'admin', 'member', 'billing')),
joined_at INTEGER NOT NULL DEFAULT (unixepoch()),
UNIQUE (user_id, organization_id)
);
CREATE INDEX idx_memberships_user ON memberships(user_id);
CREATE INDEX idx_memberships_org ON memberships(organization_id);
Role hierarchy: owner (full control) -> admin (manage members) -> member (standard access) -> billing (view invoices only).
4. Subscriptions
This is the heart of your billing system. A subscription links an organization to a plan and tracks its lifecycle through a state machine.
CREATE TABLE subscriptions (
id TEXT PRIMARY KEY,
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
plan_id TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'trialing'
CHECK (status IN ('trialing', 'active', 'past_due', 'canceled', 'expired', 'incomplete')),
current_period_start INTEGER NOT NULL,
current_period_end INTEGER NOT NULL,
trial_start INTEGER,
trial_end INTEGER,
canceled_at INTEGER,
ended_at INTEGER,
created_at INTEGER NOT NULL DEFAULT (unixepoch()),
updated_at INTEGER NOT NULL DEFAULT (unixepoch())
);
CREATE INDEX idx_subs_org ON subscriptions(organization_id);
CREATE INDEX idx_subs_status ON subscriptions(status);
CREATE INDEX idx_subs_period ON subscriptions(current_period_end);
Subscription State Machine
trialing ──→ active ──→ past_due ──→ canceled/expired
↑
└─── (renewal fails)
-
trialing → automatically converts to
activewhen trial ends and payment succeeds - active → happy path. Dunning starts if payment fails
-
past_due → payment failed. Grace period (3-14 days), then
canceledorexpired - canceled → user or system canceled. Access until period end
- expired → final state. No access
- incomplete → initial payment failed (card declined on first attempt)
Never use a boolean is_active column. A state machine with explicit transitions prevents ambiguous states.
5. Invoices
CREATE TABLE invoices (
id TEXT PRIMARY KEY,
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
subscription_id TEXT REFERENCES subscriptions(id),
stripe_invoice_id TEXT,
status TEXT NOT NULL DEFAULT 'draft'
CHECK (status IN ('draft', 'open', 'paid', 'void', 'uncollectible')),
amount_due INTEGER NOT NULL, -- in cents
amount_paid INTEGER NOT NULL DEFAULT 0,
currency TEXT NOT NULL DEFAULT 'usd',
due_date INTEGER,
paid_at INTEGER,
created_at INTEGER NOT NULL DEFAULT (unixepoch())
);
CREATE INDEX idx_invoices_org ON invoices(organization_id);
CREATE INDEX idx_invoices_sub ON invoices(subscription_id);
6. Invoice Line Items
CREATE TABLE invoice_line_items (
id TEXT PRIMARY KEY,
invoice_id TEXT NOT NULL REFERENCES invoices(id) ON DELETE CASCADE,
description TEXT NOT NULL,
quantity INTEGER NOT NULL DEFAULT 1,
unit_amount INTEGER NOT NULL, -- in cents
total_amount INTEGER NOT NULL,
type TEXT NOT NULL CHECK (type IN ('subscription', 'addon', 'credit', 'tax', 'adjustment')),
created_at INTEGER NOT NULL DEFAULT (unixepoch())
);
CREATE INDEX idx_ili_invoice ON invoice_line_items(invoice_id);
Storing individual line items is critical for prorated refunds, tax reporting, and usage-based billing breakdowns.
Billing and Invoicing Design
Flat-Rate Subscription
Charge a fixed amount per period. Simple, predictable.
Per-Seat (Per-User)
Multiply the unit price by the number of active members:
SELECT m.organization_id, COUNT(*) AS member_count
FROM memberships m
WHERE m.organization_id = ?
AND m.role != 'billing'
GROUP BY m.organization_id;
Usage-Based
Store usage events in a separate table:
CREATE TABLE usage_events (
id TEXT PRIMARY KEY,
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
metric_name TEXT NOT NULL,
quantity INTEGER NOT NULL,
recorded_at INTEGER NOT NULL DEFAULT (unixepoch())
);
CREATE INDEX idx_usage_event ON usage_events(organization_id, metric_name, recorded_at);
Common Pitfalls
N+1 Queries in Billing
-- BAD: N+1 — one query for the org, then N per subscription
SELECT * FROM organizations;
-- GOOD: single join
SELECT o.name, s.status, s.current_period_end
FROM organizations o
LEFT JOIN subscriptions s ON s.organization_id = o.id;
Missing Index on current_period_end
If you run a daily cron to expire subscriptions, this query runs a full table scan without an index:
SELECT id FROM subscriptions
WHERE status = 'active' AND current_period_end < unixepoch();
Cascading Deletes That Destroy Billing Records
If you accidentally delete an org, all invoices vanish. Consider ON DELETE RESTRICT or a soft-delete (deleted_at column) for financial records.
Storing Monetary Values as Floats
Never use FLOAT or REAL for money. Always use integer cents:
amount_due INTEGER NOT NULL -- $29.99 -> 2999
Conclusion
A well-designed SaaS database schema is invisible when it works and catastrophic when it doesn't. The entities and patterns in this guide — users, organizations, memberships, subscriptions with state machines, and normalized invoicing — have been refined across production systems handling thousands of tenants.
Start with row-level multi-tenancy. Bill organizations, not users. Model subscription status as an explicit state machine. Store money as integers. Add indexes for your query patterns. And always, always write reversible migrations.
