DEV Community

sweet
sweet

Posted on

Designing SaaS Database Architecture: Users, Organizations, Subscriptions, and Billing

#saas #sql #postgres #ai

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
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

Subscription State Machine 

  trialing ──→ active ──→ past_due ──→ canceled/expired
                  ↑
                  └─── (renewal fails)
Enter fullscreen mode Exit fullscreen mode
  • 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 

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);
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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.

Related Resources

Top comments (0)