Shared Schema

Introduction

Multi-Tenant Shared Schema gives all tenants identical table structures within a database — the most common SaaS pattern. Stripe's connected accounts, Shopify's shops, and Slack's workspaces typically share one schema definition with tenant_id discrimination. Schema migrations affect all tenants atomically — powerful and dangerous.

Real production story

Stripe's Dashboard team stored all connected account settings in a shared PostgreSQL schema: accounts(stripe_account_id, ...) with row-level tenant scoping via authenticated account context. A platform engineer added a compliance column with NOT NULL and no default — migration locked the table for 23 minutes during US peak. Every connected account's dashboard froze — not one tenant, all of them.

The remediation introduced expand-contract migrations: add nullable column → backfill per shard → enforce NOT NULL in separate deploy. Plus per-tenant migration progress tracking for backfills exceeding 100M rows. Shared schema stayed — the migration discipline changed.

Business problem

Business pressure: Stripe serves millions of connected accounts with uniform data model — shared schema enables one codebase, one migration path, and consistent API behavior across all accounts.

• Revenue at risk: Schema migration outage affects every connected account simultaneously — blast radius is platform-wide.
• Engineering velocity: One feature = one migration = all tenants get it — no per-tenant schema drift.
• Compliance / trust: PCI requires consistent controls across accounts — shared schema simplifies audit scope.

Architecture overview

Shared Schema means identical tables and columns for every tenant in a database. Differentiation is purely data (tenant_id values), not structure. Contrast with separate schema where each tenant gets own namespace with potentially different tables.

• Definition: One set of tables; all tenants' rows coexist with tenant discriminator.
• When to adopt: Standardized SaaS product, millions of similar tenants, strong migration discipline.
• When to defer: Tenants need custom columns/tables, regulatory physical separation, or widely varying data volume per tenant.
• Operability: Expand-contract migrations; online schema change tools (gh-ost, pg-osc); per-tenant backfill jobs.

Architecture motivation

Why architects care: Shared schema is the default multi-tenant model — simplicity until migrations, noisy neighbors, or enterprise isolation requirements force evolution to separate schema or database tiers.

• Force: Uniform tenant data model; high tenant count; single product surface.
• Constraint: Schema customization per tenant is rare — product is standardized.
• Outcome: tenant_id on all rows, expand-contract migrations, per-tenant backfill tracking.

Internal architecture

Stripe shared schema model:

Database: stripe_dashboard
Schema: public (shared by all connected accounts)

accounts(stripe_account_id PK, business_name, …)
settings(stripe_account_id FK, payout_schedule, …)
webhooks(stripe_account_id FK, endpoint_url, …)

Every query: WHERE stripe_account_id = @authenticated_account
Migration: affects ALL rows in table simultaneously

Data flow

Migration path: expand-contract pattern prevents table locks from blocking all tenants during schema evolution.

• Write path: dual-write to old + new column during backfill window.
• Read path: read new column with fallback to old until backfill complete.
• Async path: backfill worker processes stripe_account_id ranges in batches with checkpoint.

-- Expand-contract migration (PostgreSQL)
-- Step 1: Expand (online, no lock)
ALTER TABLE settings ADD COLUMN compliance_tier TEXT NULL;

-- Step 2: Backfill (async job, batched by account_id range)
UPDATE settings SET compliance_tier = 'standard'
WHERE stripe_account_id BETWEEN @start AND @end AND compliance_tier IS NULL;

-- Step 3: Contract (after 100% backfill verified)
ALTER TABLE settings ALTER COLUMN compliance_tier SET NOT NULL;

System design diagram

Two diagrams show the Shared Schema topology and the primary request/event path used in production at scale.

Production code example

Backfill job with checkpoint for shared schema column migration:

async function backfillComplianceTier(): Promise<void> {
  let cursor = await redis.get("backfill:compliance_tier:cursor") ?? "acct_0";
  const BATCH = 5000;
  while (true) {
    const rows = await db.query(
      `SELECT stripe_account_id FROM settings
       WHERE stripe_account_id > @cursor AND compliance_tier IS NULL
       ORDER BY stripe_account_id LIMIT @batch`,
      { cursor, batch: BATCH },
    );
    if (rows.length === 0) break;
    await db.query(
      `UPDATE settings SET compliance_tier = 'standard'
       WHERE stripe_account_id = ANY(@ids) AND compliance_tier IS NULL`,
      { ids: rows.map((r) => r.stripe_account_id) },
    );
    cursor = rows[rows.length - 1].stripe_account_id;
    await redis.set("backfill:compliance_tier:cursor", cursor);
    metrics.gauge("backfill.progress", await percentComplete());
  }
}

Enterprise case study

Stripe Dashboard — shared schema migration discipline: After 23-minute outage, mandatory expand-contract, backfill progress dashboard, and migration freeze windows during US peak.

• Before: NOT NULL migration locked all connected accounts 23 minutes.
• Decision: Expand-contract + online DDL tools + migration SLO (zero table lock >5s).
• After: 40+ schema migrations in 12 months with zero platform-wide lockouts.

Trade-offs

• Uniformity vs flexibility: All tenants get same columns — no per-tenant schema forks without EAV pain.
• Migration blast radius: One bad migration hits everyone — expand-contract and online DDL mandatory.
• Simplicity vs enterprise isolation: Largest accounts may demand separate schema tier — offer as premium.

Security considerations

Security is architectural: Shared schema means one SQL bug exposes all accounts — same defense in depth as shared database.

• Identity: stripe_account_id from authenticated session — Dashboard API validates account scope on every endpoint.
• Data: Row-level security optional second layer; encrypt bank account fields at application level.
• Supply chain: Migration scripts reviewed in PR with estimated lock time and backfill duration.

Scalability analysis

Scale dimensions: Stripe's settings table exceeds 100M rows — migrations and backfills must be sharded by account_id range with progress dashboards.

• Horizontal scale: PostgreSQL read replicas for dashboard reads; Citus or sharding when single instance saturates.
• Hot spots: Top 1000 accounts generate disproportionate webhook rows — partition by account_id hash.
• Cost: Shared schema minimizes storage overhead vs per-tenant schema metadata duplication.

Failure scenarios

What breaks: Blocking DDL during peak; backfill job without checkpoint restarts from zero; missing tenant filter same as shared database.

• Blocking DDL: Use pg-ost or expand-contract — never NOT NULL without backfill on 100M row table.
• Backfill stall: Checkpoint per account_id range; alert if progress flatlines 1 hour.
• Schema drift in prod: Single migration pipeline — no manual per-tenant ALTER.

Staff engineer insights

• Shared schema migrations are fleet-wide events — treat them like production deploys with rollback plans.
• Expand-contract is non-negotiable above 10M rows — the NOT NULL outage is the textbook case.
• Offer separate schema/database tier for enterprise before they demand it in RFP — productize isolation.

Interview questions

Architecture review questions

• Are quality attributes (latency, availability, consistency) explicit with SLOs for Multi-Tenant Shared Schema?
• Is the failure/degraded mode documented — including what happens when dependencies are down?
• Are boundaries and ownership clear on an architecture diagram a new engineer understands in 10 minutes?
• Is there an ADR capturing alternatives considered and why they were rejected?
• Can this design scale 10× on traffic and 3× on engineering headcount without a rewrite?
• Security: authn/authz, encryption, and blast radius reviewed at every external interface?

Summary

Multi-Tenant Shared Schema at Stripe scale means one table definition for millions of accounts — with migration discipline that respects fleet-wide blast radius. Master expand-contract backfills and isolation guardrails before chasing per-tenant customization.