← Back to case studies

Jul 1, 2026 · 2 min read

Building a Multi-tenant CRM

A single Postgres-backed CRM serving multiple isolated tenants — schema-per-tenant isolation, shared application code, and zero cross-tenant data leaks.

TL;DR

Architecture

Schema-per-tenant isolation on a shared Postgres instance, with a central tenants registry and a request-time resolver that sets the search path.

Request (Host / X-Tenant) → Tenant resolver → SET search_path
                                                       ↓
                                              Express handlers
                                                       ↓
                                  central DB (registry) ─┬─ tenant_a schema
                                                         └─ tenant_b schema

Interesting decisions

• Schemas over RLS / DB-per-tenant. RLS is elegant but one missed policy leaks; a DB-per-tenant is safest but operationally heavy. Schemas hit the sweet spot: strong isolation, easy per-tenant backup/restore, a single connection pool.
• SET search_path per request, not per query. Resolved once in middleware; the ORM stays tenant-agnostic. A guard asserts the path is set before any query runs — fail closed.
• Migrations fan out across all tenant schemas from one script, with a dry-run that lists affected schemas first.
• Per-tenant config (feature flags, branding) lives in each schema, so config travels with its data on export.

Lessons learned

• Test isolation with property tests. A Vitest suite that creates two tenants, writes data to A, and asserts B can never read it — run on every PR. This is the test that lets you sleep.
• Share the pool; switch search_path. Opening pools per tenant multiplies connections by tenant count. Share the pool, set the path per request.
• Eviction/export is a first-class feature. Build pg_dump --schema=tenant_x early — it doubles as your backup story.

Pros & cons

Pros: strong isolation, cheap per-tenant backup/restore, one codebase. Cons: migrations touch N schemas; a runaway tenant can still consume shared CPU/RAM.