Implement Secure SaaS Multi-Tenant Architecture

Guides building tenant isolation for a shared-database SaaS application through an eight-step workflow: choosing a tenancy model (shared-schema with tenant_id as the default for most apps under 1000 tenants, schema-per-tenant only when justified, database-per-tenant only for regulatory data-residency needs), adding a NOT NULL tenant_id column to every tenant-scoped table, setting up PostgreSQL Row-Level Security policies as a database-level safety net, building tenant-aware middleware that sets app.current_tenant_id via SET LOCAL at the start of each request, scoping every ORM query by tenant through Prisma middleware or a Drizzle base query builder rather than relying on developers to remember, enforcing tenant_id on every new migration through a CI lint rule, building cross-tenant admin routes on a separate bypass role and authentication flow, and wrapping tenant provisioning in a transaction so partial signups never leave orphan records. It provides three full worked examples: a PostgreSQL RLS policy pair for SELECT and INSERT, an Express middleware that opens a transaction and sets the tenant session variable per request with commit/rollback cleanup on response finish, and a Prisma middleware that injects the tenant filter into every read, create, update, and delete call while exempting a named set of global tables. It lists six explicit "never do this" rules - never rely on ORM middleware alone for raw SQL, never skip RLS as the enforcement layer, never use sequential integer IDs for tenant-scoped resources, never let tenant sessions reach admin aggregation routes, never run migrations with RLS active on the migration connection, never reuse a pooled connection without resetting the tenant session variable - and five edge cases: tenant deletion via soft-delete plus batched background cleanup, GDPR data export requiring a maintained table registry, shared-resource tables using an owner_tenant_id pattern, tenant-aware background jobs that must set session context from the job payload, and connection-pool exhaustion under schema-per-tenant at scale.

Use it when building a SaaS application where multiple customers share a database, implementing tenant isolation or row-level security, scoping queries to a tenant without manual WHERE clauses, comparing shared-schema versus schema-per-tenant versus database-per-tenant, adding tenant_id to an existing single-tenant app, or building tenant-aware middleware in Express, Fastify, or Next.js API routes. Do not use it for a single-user application with no shared infrastructure, for authentication questions that don't involve tenant scoping, or for general database schema design with no multi-tenancy requirement.

Input is a description of the SaaS application's tenancy needs - scale, isolation requirements, existing schema. Output is tenant-isolation infrastructure - RLS policies, tenant-aware middleware, ORM scoping middleware, and migration linting - following the worked examples, for instance:

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON projects
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

CREATE POLICY tenant_insert ON projects
  FOR INSERT
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid);

Targets PostgreSQL Row-Level Security, and application layers including Express, Fastify, and Next.js API routes, plus ORM-level scoping for Prisma ($use middleware) and Drizzle (base query builder); recommends Redis or PgBouncer for tenant-lookup caching and connection pooling at scale.

Backend engineers building or retrofitting multi-tenant SaaS data architecture who need database-level tenant isolation, not just application-layer filtering that a single missed WHERE clause can defeat.