arch-atlas
SaaS Atlas·Pillar 02 / 12

Multi-Tenancy

Shared schema · Postgres RLS · Hybrid for enterprise

Multi-tenancy is the bet you can't afford to get wrong. Shared-schema with RLS gives you the unit economics of cents-per-tenant for the long tail; tenant-per-schema or tenant-per-DB gives enterprise the compliance story they need. The pragmatic 2026 default is hybrid: ship shared-schema first, design every table with org_id and an RLS policy, and have the migration path to isolation ready before your first enterprise customer asks.

7Nodes2Vendors2Decisions12Checklist2Flows

Primary stack

The default picks this pillar assumes

  • Postgres + RLS
  • Drizzle ORM

Alternates worth knowing

Swap in when scale, cost or constraints change

None listed yet.

01
StrategyShared / hybrid / isolated

Isolation Strategy

Three families: shared schema with RLS (cheapest, hardest to mess up safely); shared DB with separate schemas (medium); database-per-tenant (most isolated, most ops). The mature SaaS answer is hybrid: shared-schema default, enterprise opts up into separate schemas or DBs.

How it flows

step by step
  1. 01

    Decide DEFAULT tier (shared) and ESCALATION tiers (schema-per-tenant, DB-per-tenant).

    step
  2. 02

    Mark every customer record with its tier (`tenancy_mode: 'shared' | 'schema' | 'database'`).

    step
  3. 03

    App reads tenancy_mode and routes the connection accordingly.

    step
  4. 04

    Migration path: a job copies a tenant from shared → isolated when they upgrade.

    step

Cases & edge cases

watch for these

Shared-schema with no RLS is a data leak waiting to happen — don't ship without policies.

Database-per-tenant means N copies of every migration; you need orchestration before you have 10 tenants.

'Hybrid' adds complexity. Don't add the second tier until a real customer asks (with money).

Vendors & tools

what implements this

Postgres + RLS

primary

Database-enforced tenant isolation — the policy is the perimeter.

Pros

  • No way to forget WHERE org_id =
  • Battle-tested at scale (Supabase, Linear, Vercel run on this)

Cons

  • Policy debugging is its own learning curve

Decisions

pick once, live with it

  • Decision 01

    Which strategy first?

    pickShared schema + RLS.

    whyCheapest unit economics, hardest-to-misconfigure with the right policies, scales to thousands of tenants. Re-architecting to isolation later is a migration, not a rewrite.

    altsSchema-per-tenant (if early customers are healthcare/finance and need 'physical' separation)

Checklist

ship readiness

  • Strategy document committed to the repo

    must

  • tenancy_mode column on customer table

    should

  • Tenant migration runbook drafted (shared → isolated)

    should
02
DataPostgres row-level security

RLS Policies

Every tenant-scoped table has org_id and a policy `USING (org_id = current_setting('app.org_id')::uuid)`. The app sets app.org_id at the start of every request; the DB enforces the WHERE.

How it flows

step by step
  1. 01

    Request enters → middleware reads auth().orgId.

    step
  2. 02

    Connection pool acquires a session, runs `SET LOCAL app.org_id = '<uuid>'`.

    step
  3. 03

    Subsequent queries on that connection only see rows matching the policy.

    step
  4. 04

    Connection returns to the pool — `SET LOCAL` is scoped to the transaction.

    step

Cases & edge cases

watch for these

Forgetting `LOCAL` leaks the org_id across pool checkouts. Use a wrapper that always uses transactions.

Migrations run as superuser → RLS is bypassed. Don't run app code as superuser.

RLS adds ~5-15% query overhead. Profile and tune indexes — they should still be on (org_id, ...).

Vendors & tools

what implements this

Drizzle ORM

primary

SQL-first ORM with predefined Supabase roles for RLS-aware queries.

Pros

  • Doesn't fight RLS; native @supabase import
  • Type-safe queries respect policies

Cons

  • RLS policy generation is not yet automated — write as manual migrations

Checklist

ship readiness

  • Policy on every tenant-scoped table

    must

  • Migration test that asserts SELECT cross-tenant returns 0 rows

    must

  • Composite index on (org_id, ...) for hot queries

    should

Where it lives

code references
References
  • drizzle/policies/ // policy migrations (raw SQL)
  • lib/db/with-tenant.ts // SET LOCAL wrapper

Notes

margin

Treat policies as code. Review them like routes; test them like routes. A policy bug is a P0 data incident.

03
RuntimeRequest-scoped propagation

Tenant Context

How the org_id travels from JWT → middleware → DB session → query. Get this wrong once and you've leaked. The 'with-tenant' wrapper is the only allowed door into the DB.

How it flows

step by step
  1. 01

    Middleware: auth().orgId → request.context.orgId.

    step
  2. 02

    Route handler / server action: withTenant(orgId, async (db) => { ... }).

    step
  3. 03

    withTenant: BEGIN → SET LOCAL app.org_id → run callback → COMMIT (or ROLLBACK).

    step
  4. 04

    Background jobs receive orgId in their payload and use the same wrapper.

    step

Cases & edge cases

watch for these

Background jobs without orgId in payload are the #1 source of cross-tenant bugs.

Caching keys MUST include orgId — Redis keys like `feature:flags` instead of `feature:flags:<orgId>` leak.

Admin / cross-org tooling has its own DB connection, not RLS-enabled — separate code path.

Where it lives

code references
References
  • lib/db/with-tenant.ts
  • lib/jobs/with-tenant-job.ts

Notes

margin

One function, one rule: NO raw db.query() in app code. Everything goes through withTenant — caught by lint.

04
DataDefault tier

Shared Schema

Every customer is a row in `organizations`. Every other tenant-scoped table has org_id NOT NULL. One set of migrations, one connection pool, lowest cost.

How it flows

step by step
  1. 01

    All tables: org_id uuid NOT NULL REFERENCES organizations(id) ON DELETE CASCADE.

    step
  2. 02

    Indexes: (org_id, id), (org_id, created_at), (org_id, status) — whatever the hot filters are.

    step
  3. 03

    Backups: one nightly dump; PITR via Postgres WAL.

    step
  4. 04

    Hot path: 99% of customers live here, 99% of revenue ratios get hit here.

    step

Cases & edge cases

watch for these

Schema migrations are zero-risk per-tenant — they hit everyone at once.

Large tenants (>1% of rows) can cause query planner skew. Add partial indexes if needed.

Don't denormalize org_id into JSON columns — Postgres can't enforce policy on JSONB paths cleanly.

Checklist

ship readiness

  • org_id NOT NULL FK on every tenant table

    must

  • Cascade behaviour decided per table (most should cascade)

    must

  • Composite indexes documented in schema.md

    should
05
DataSchema or DB per tenant

Enterprise Tier

An escape hatch for the 1-5% of customers who pay 10x for isolation: their own schema (cheaper to operate) or their own database (true physical isolation, audit-friendly).

How it flows

step by step
  1. 01

    Customer signs enterprise contract → ops triggers `provision-isolated-tenant` job.

    step
  2. 02

    Job creates a new schema (or DB), runs migrations against it, copies the customer's existing rows from shared.

    step
  3. 03

    Flips `tenancy_mode = 'schema' | 'database'` on the org row.

    step
  4. 04

    Next request resolves to the new connection via a tenant→DSN map (Redis-backed).

    step
  5. 05

    Old rows in shared schema are nulled-out and archived after a verification window.

    step

Cases & edge cases

watch for these

Migrations now have to run N+1 times — parallelize cautiously, never fully concurrently (DDL locks).

Connection pooling per-tenant explodes file descriptors — use a proxy (PgBouncer, Supavisor) at scale.

Backups per-tenant means restore is per-tenant — write the runbook, test it quarterly.

Decisions

pick once, live with it

  • Decision 01

    Schema-per-tenant or DB-per-tenant for enterprise?

    pickSchema-per-tenant first; DB-per-tenant only when contractually required.

    whySchemas share connection pool, vacuum, monitoring. DBs are isolated but multiply ops cost. Most 'we need isolation' asks are satisfied by separate schemas + a compliance letter.

06
OpsAcross N tenants

Migrations

Shared-schema = trivial. Schema-per-tenant = orchestrated. The tooling that survives both is a migration runner that knows the per-tenant strategy and a CI job that validates against representative tenants.

How it flows

step by step
  1. 01

    Engineer writes `drizzle/migrations/2026XXXX_<name>.sql`.

    step
  2. 02

    CI runs it against a 'representative tenant set' staging DB — shared + 1 isolated.

    step
  3. 03

    Deploy → migration runs against shared once, then enqueues per-isolated jobs.

    step
  4. 04

    Per-tenant jobs run sequentially, with a 5-min wait on failure, alerting on >2 failures.

    step

Cases & edge cases

watch for these

Destructive migrations (DROP COLUMN) need a feature-flag toggle — phase out reads, then drop in a second deploy.

Schema-changes-with-data-backfill should split: ship empty column, backfill in batches, then NOT NULL.

Lock timeout: `SET lock_timeout = '5s'` so a long DDL on a hot table fails fast instead of stalling all writes.

Checklist

ship readiness

  • Lock timeout configured for all DDL

    must

  • Backfill-as-a-job pattern, not inline migration

    must

  • Per-tenant migration log persisted in the DB

    should

Where it lives

code references
References
  • scripts/migrate-all-tenants.ts
  • drizzle/migrations/
07
OpsPer-tenant limits

Noisy Neighbours

One customer running a 50M-row export shouldn't slow everyone else down. Per-tenant rate limits, query timeouts, statement budgets, and a 'jail' for runaway workloads.

How it flows

step by step
  1. 01

    Edge: per-org rate limit on writes (e.g. 100 RPS sustained, 500 burst) via Upstash Ratelimit.

    step
  2. 02

    DB: `statement_timeout = 30s` at the role level; long jobs run on a separate role with higher limits.

    step
  3. 03

    Job queue: per-org concurrency cap so one tenant can't starve workers.

    step
  4. 04

    Observability: per-tenant latency dashboard; alert when p99 for one tenant > 10x median.

    step

Cases & edge cases

watch for these

Rate limits should return 429 with Retry-After — don't 500 a customer who hits the cap.

Audit which tenants trigger the timeout repeatedly — they're either abusing or need an enterprise tier.

Background jobs are where bills explode. Cap before billing realizes it.

Notes

margin

Limits aren't punishment — they're the contract that lets cheap tenants exist without dragging premium tenants down.

Annotate0 strokes

Apple Pencil draws with pressure. Fingers still scroll. Saved per pillar.