Subscription Billing

Design the schema for a SaaS billing system: customers subscribe to plans, get billed on a cycle, and may upgrade / downgrade with proration mid-cycle. Failed payments retry on a schedule. Every monetary movement must be auditable.

Clarifying questions

Single currency or multi-currency? Multi-currency requires currency on every monetary column.
Are plans fully versioned (a grandfathered $9/mo plan still exists) or is there one current price?
Do we issue real invoices for tax / accounting, or just charge cards?
Is proration cash-basis (credit on next invoice) or accrual (immediate refund)?
How long do failed payments retry, and do we dunning-email out of band?

Core requirements

Subscribe a customer to a plan with a billing cycle anchor.
Generate invoices at cycle boundaries and on plan changes.
Apply proration when a customer changes plans mid-cycle.
Record payments and refunds with provider references.
Retry failed payments on a backoff schedule until success or cancel.
Reconstruct any customer's billing history exactly — never lose a money movement.

Canonical schema (PostgreSQL)

CREATE TABLE customers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email CITEXT NOT NULL,
  default_currency CHAR(3) NOT NULL DEFAULT 'USD',
  tax_id TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE plans (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  code TEXT NOT NULL,
  version INTEGER NOT NULL,
  amount_minor BIGINT NOT NULL,
  currency CHAR(3) NOT NULL,
  interval TEXT NOT NULL CHECK (interval IN ('month', 'year')),
  active BOOLEAN NOT NULL DEFAULT true,
  UNIQUE (code, version)
);

CREATE TABLE subscriptions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id UUID NOT NULL REFERENCES customers(id),
  plan_id UUID NOT NULL REFERENCES plans(id),
  status TEXT NOT NULL CHECK (status IN ('trialing', 'active', 'past_due', 'cancelled')),
  current_period_start TIMESTAMPTZ NOT NULL,
  current_period_end TIMESTAMPTZ NOT NULL,
  cancel_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE invoices (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id UUID NOT NULL REFERENCES customers(id),
  subscription_id UUID REFERENCES subscriptions(id),
  number TEXT UNIQUE NOT NULL,
  currency CHAR(3) NOT NULL,
  subtotal_minor BIGINT NOT NULL,
  tax_minor BIGINT NOT NULL DEFAULT 0,
  total_minor BIGINT NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('draft', 'open', 'paid', 'void', 'uncollectible')),
  issued_at TIMESTAMPTZ,
  due_at TIMESTAMPTZ,
  paid_at TIMESTAMPTZ
);

CREATE TABLE invoice_lines (
  id BIGSERIAL PRIMARY KEY,
  invoice_id UUID NOT NULL REFERENCES invoices(id) ON DELETE CASCADE,
  description TEXT NOT NULL,
  quantity NUMERIC(12, 4) NOT NULL DEFAULT 1,
  unit_amount_minor BIGINT NOT NULL,
  amount_minor BIGINT NOT NULL,
  period_start TIMESTAMPTZ,
  period_end TIMESTAMPTZ,
  proration BOOLEAN NOT NULL DEFAULT false
);

CREATE TABLE payments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  invoice_id UUID REFERENCES invoices(id),
  customer_id UUID NOT NULL REFERENCES customers(id),
  amount_minor BIGINT NOT NULL,
  currency CHAR(3) NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('pending', 'succeeded', 'failed', 'refunded')),
  provider TEXT NOT NULL,
  provider_charge_id TEXT,
  attempt INTEGER NOT NULL DEFAULT 1,
  next_retry_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Key indexes

CREATE INDEX idx_subscriptions_customer ON subscriptions(customer_id, status)

'Active subscriptions for this customer' is the dominant API (Application Programming Interface) query.

CREATE INDEX idx_subscriptions_renew ON subscriptions(current_period_end) WHERE status IN ('active', 'trialing')

The renewal cron scans only subscriptions about to roll — partial index makes the job O(due) not O(total).

CREATE INDEX idx_payments_retry ON payments(next_retry_at) WHERE status = 'failed' AND next_retry_at IS NOT NULL

Dunning worker pulls only retryable failures, ignoring permanently failed or succeeded rows.

CREATE UNIQUE INDEX idx_payments_provider_charge ON payments(provider, provider_charge_id) WHERE provider_charge_id IS NOT NULL

Idempotency on webhook replay — same charge id never inserts twice.

Key decisions to defend

Store money as amount_minor BIGINT plus currency CHAR(3) — never NUMERIC for amounts and never separate currency tables; rounding bugs and FX confusion both vanish.
Plans are versioned (code, version) rather than mutated — grandfathered customers stay on plan v3 while new ones sign up for v5, with no historical rewrite.
Proration produces invoice_lines with proration=true and explicit period_start/end — the invoice is the audit trail, no separate proration ledger needed.
Invoices and payments are separate concepts: an invoice can have multiple payment attempts, partial payments, and refunds. Conflating them loses the retry history.
Idempotency via UNIQUE (provider, provider_charge_id) lets webhook handlers be safely re-run — payment providers fire duplicate events constantly.

Likely follow-ups

· Add usage-based billing (metered API calls) on top — how do lines accumulate?
· A customer disputes a charge from 14 months ago — what's the read path?
· Move from monthly to anniversary billing for a customer — what changes?
· Tax jurisdiction changes mid-cycle — does the in-flight invoice update?

Dive deeper

Billing schemas are about respecting the difference between contracts (subscription), intents (invoice), and outcomes (payment). Junior schemas collapse these into one 'subscription' table that mutates on every event and loses history. Senior schemas treat invoices and payments as immutable financial records and version plans so historical pricing is recoverable. The idempotency constraint on provider_charge_id is a tell that you've actually integrated with Stripe.

All database design prompts