
About
Design and implement multi-tenant SaaS architectures with row-level security, tenant-scoped queries, shared-schema isolation, and safe cross-tenant admin patterns in PostgreSQL and TypeScript.
name: saas-multi-tenant description: "Design and implement multi-tenant SaaS architectures with row-level security, tenant-scoped queries, shared-schema isolation, and safe cross-tenant admin patterns in PostgreSQL and TypeScript." risk: safe source: community date_added: "2026-03-28" tags: [multi-tenancy, saas, row-level-security, postgresql, tenant-isolation] tools: [claude, cursor, gemini]
SaaS Multi-Tenant Architecture
When to Use This Skill
- The user is building a SaaS application where multiple customers share the same database
- The user asks about tenant isolation, row-level security, or data leakage prevention
- The user needs to scope every database query to a specific tenant without manual WHERE clauses
- The user asks about shared-schema vs schema-per-tenant vs database-per-tenant tradeoffs
- The user is implementing admin endpoints that must access data across tenants
- The user needs to add
tenant_idcolumns to an existing single-tenant application - The user asks about PostgreSQL RLS policies for tenant isolation
- The user is building tenant-aware middleware in Express, Fastify, or Next.js API routes
Do NOT use this skill when:
- The user is building a single-user application with no shared infrastructure
- The user asks about authentication only without tenant scoping (use an auth skill instead)
- The user needs general database schema design without multi-tenancy requirements
Core Workflow
-
Determine the tenancy model. Ask the user about their scale expectations and isolation requirements. For most SaaS apps under 1000 tenants, shared-schema with a
tenant_idcolumn on every table is the correct default. Schema-per-tenant adds operational overhead (migrations run N times). Database-per-tenant is only justified when tenants have regulatory data residency requirements. -
Add
tenant_idto every tenant-scoped table. The column must beNOT NULL, typeUUIDorTEXT, and included in every composite index. Never allow a tenant-scoped table to exist without this column — a missingtenant_idis a data leak waiting to happen. -
Set up PostgreSQL Row-Level Security (RLS). Create a policy on each tenant-scoped table that filters rows by
current_setting('app.current_tenant_id'). This acts as a database-level safety net — even if application code forgets a WHERE clause, RLS blocks cross-tenant reads. -
Build tenant-aware middleware. At the start of every request, extract the
tenant_idfrom the authenticated session or JWT claims. Set it on the database connection usingSET LOCAL app.current_tenant_id = '...'inside a transaction. Every subsequent query in that request inherits the tenant scope automatically. -
Scope all ORM queries by tenant. If using Prisma, apply a global middleware that injects
where: { tenantId }into everyfindMany,findFirst,update, anddeletecall. If using Drizzle, create a base query builder that includes the tenant filter. Never rely on developers remembering to add the filter manually. -
Handle tenant-aware migrations. Every new table migration must include
tenant_idas a column. Write a linting rule or CI check that rejects any migration creating a table withouttenant_idunless the table is explicitly marked as global (e.g.,plans,feature_flags). -
Build cross-tenant admin routes separately. Admin endpoints that aggregate data across tenants must bypass RLS explicitly using
SET LOCAL role = 'admin_bypass'or a dedicated database role. These routes must be protected by a separate admin authentication flow — never reuse tenant user sessions for admin access. -
Implement tenant provisioning. When a new customer signs up, create their tenant record, seed default data (roles, settings, onboarding state), and assign the founding user. Wrap this in a database transaction so partial provisioning never leaves orphan records.
Examples
Example 1: PostgreSQL RLS Policy for Tenant Isolation
-- Enable RLS on the table
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects FORCE ROW LEVEL SECURITY;
-- Policy: users can only see rows where tenant_id matches the session variable
CREATE POLICY tenant_isolation ON projects
USING (tenant_id = current_setting('app.current_tenant_id')::uuid);
-- Policy for INSERT: new rows must match the current tenant
CREATE POLICY tenant_insert ON projects
FOR INSERT
WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid);
Example 2: Express Middleware That Sets Tenant Context per Request
import { Pool } from "pg";
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
async function tenantMiddleware(req, res, next) {
const tenantId = req.auth?.tenantId; // extracted from JWT during auth
if (!tenantId) return res.status(403).json({ error: "No tenant context" });
const client = await pool.connect();
try {
await client.query("BEGIN");
// Use set_config — SET LOCAL does not
