SaaS Database Architecture: Patterns That Scale to 1M Users (Real Benchmarks)

TL;DR: 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:

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 active when trial ends and payment succeeds
• active → happy path. Dunning starts if payment fails
• past_due → payment failed. Grace period (3-14 days), then canceled or expired
• 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

Invoices represent billing events and are the financial record of what a customer owes. Each invoice links to an organization and optionally to a subscription, tracking the full lifecycle from draft through open, paid, void, or uncollectible:

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);

Amounts are stored as integers in cents to avoid floating-point rounding errors. The stripe_invoice_id field creates an audit trail between your database and Stripe's event system, which is essential for reconciliation.

6. Invoice Line Items

Line items break down what each invoice covers — subscriptions, add-ons, credits, taxes, or adjustments. Storing individual line items is critical for prorated refunds, tax reporting, and usage-based billing breakdowns:

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);

The type column categorizes each line item so your accounting system can distinguish between recurring subscription charges, one-time add-ons, and tax adjustments. The unit_amount and total_amount fields both use integer cents for precision.

Billing and Invoicing Design

Flat-Rate Subscription

Charge a fixed amount per period. Simple, predictable.

Per-Seat (Per-User)

Per-seat pricing charges based on the number of active members in an organization. This query counts members excluding billing-only users who should not incur a seat cost:

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

Usage-based billing tracks consumption events and bills based on volume. The usage events table captures each metered action with its quantity, and the billing system aggregates these at the end of each billing period:

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);

The compound index on (organization_id, metric_name, recorded_at) ensures efficient aggregation queries when calculating monthly usage totals for billing.

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. For a deep dive into running this schema on the edge, see the Cloudflare D1 Production Guide. If you need help setting up your pricing tiers around this database structure, our SaaS Pricing Strategy Playbook covers tier design and value metrics. And the TanStack Start End-to-End Tutorial walks through building a complete application on top of this schema.