Implement Secure SaaS Multi-Tenant Architecture

This skill designs and implements multi-tenant SaaS architectures in PostgreSQL and TypeScript: row-level security, tenant-scoped queries, shared-schema isolation, and safe cross-tenant admin patterns. Its eight-step core workflow: pick a tenancy model (shared-schema with a tenant_id column is the default for under roughly 1000 tenants; schema-per-tenant and database-per-tenant are reserved for operational or regulatory reasons); add a NOT NULL tenant_id to every tenant-scoped table and its composite indexes; set up PostgreSQL Row-Level Security policies filtered by current_setting('app.current_tenant_id') as a database-level safety net; build tenant-aware middleware that extracts tenant_id from the session or JWT and sets it with SET LOCAL app.current_tenant_id inside a transaction; scope every ORM query by tenant automatically, using a Prisma global middleware or a Drizzle base query builder, rather than relying on developers to add filters by hand; enforce tenant_id on every migration via a CI lint rule, with an explicit exception list for global tables like plans; build cross-tenant admin routes separately, bypassing RLS via a dedicated role and a separate admin auth flow; and provision new tenants inside a single transaction so partial signups never leave orphan records.

Use it when building a SaaS app where multiple customers share a database, when the user asks about tenant isolation, row-level security, or data-leak prevention, when scoping every query to a tenant without manual WHERE clauses, when comparing shared-schema vs. schema-per-tenant vs. database-per-tenant, when building tenant-aware Express, Fastify, or Next.js middleware, or when adding tenant_id to an existing single-tenant app. Do not use it for a single-user application with no shared infrastructure, for authentication-only questions with no tenant scoping, or for general schema design with no multi-tenancy requirement.

It ships a working PostgreSQL RLS policy pair:

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);

plus an Express middleware that sets app.current_tenant_id via set_config inside a transaction and commits or rolls back on response finish, and a Prisma $use middleware that injects a tenantId filter into findMany, findFirst, count, aggregate, create, createMany, update, and delete calls while skipping an explicit global-tables set. It names six absolute prohibitions, including never querying a tenant table without a tenant_id filter even with ORM middleware in place, never relying on application-layer filtering alone without RLS, never using sequential integer IDs for tenant-scoped resources, never letting tenant users reach cross-tenant admin endpoints, never running migrations with RLS active on the migration connection, and never sharing pooled connections across tenants without resetting app.current_tenant_id. It also covers five edge cases (tenant deletion via soft-delete plus batched background cleanup, GDPR data export needing a maintained table registry, shared-resource tables using owner_tenant_id instead of tenant_id, background jobs carrying tenant_id in their payload, and connection-pool exhaustion with schema-per-tenant) and six best practices (a canonical tenants table, tenant_id as the first column in composite indexes, cached subdomain or path-based tenant routing, an explicit global-vs-tenant-scoped table list enforced in CI, testing with at least 3 seeded tenants, and per-tenant rather than global rate limiting).

Targets PostgreSQL (Row-Level Security, current_setting, composite indexes) and TypeScript backends using Prisma, Drizzle, Express, Fastify, or Next.js API routes, with Redis suggested for cached tenant-routing lookups and per-tenant rate limiting.

Backend engineers building or hardening a shared-database SaaS product who need concrete, defense-in-depth tenant isolation, RLS as enforcement rather than just application-layer filtering, plus safe patterns for cross-tenant admin access, tenant provisioning, and GDPR-style data export.