Building a Multi-Tenant SaaS in 2026: Architecture, Pitfalls, and Production Patterns

Multi-tenancy is one of those problems that looks solved — until you're actually running 200 customers on the same database and one of them starts asking why they can occasionally see another company's invoice number in their URL. Data isolation at scale is genuinely hard, and the architectural decision you make on day one will define your operational complexity for years.

The Three Models (and When to Use Each)

There is no universally correct multi-tenancy model. The right choice depends on your pricing, compliance requirements, and team's PostgreSQL fluency. Here's how we evaluate it with every new SaaS client.

• Shared schema with Row-Level Security (RLS): Single database, single schema. Every row has a tenant_id column. PostgreSQL RLS policies enforce isolation at the database driver level. Best for early-stage products with 100–10,000 tenants and no compliance requirements beyond SOC 2.
• Separate schema per tenant: Single database instance, separate schema per tenant (company_a.orders, company_b.orders). Easier migrations per-tenant, no RLS complexity. Best for mid-market products where tenants need schema-level customisation.
• Separate database per tenant: Full physical isolation. Each tenant gets their own RDS instance or Aurora cluster. Most expensive, hardest to operate, but required for HIPAA, FedRAMP, or large enterprise contracts that mandate it.

PostgreSQL Row-Level Security in Practice

RLS is the most cost-effective isolation model for most SaaS products. The pattern is straightforward: set a runtime configuration variable on every database connection, then enforce it in a policy. Here's the actual SQL we use:

-- 1. Add tenant context to every session
ALTER DATABASE myapp SET app.current_tenant_id = '';

-- 2. Enable RLS on the table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

-- 3. Policy — tenants can only see their own rows
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- 4. Set the context in your connection pool (e.g. Prisma middleware)
-- prisma.$use(async (params, next) => {
--   await prisma.$executeRaw`SET app.current_tenant_id = ${tenantId}`;
--   return next(params);
-- });

Tenant Resolution in Next.js Middleware

Before any database query runs, you need to know which tenant is making the request. In Next.js App Router, the right place to do this is middleware. We resolve tenants from subdomain (company.yoursaas.com) or from a custom header set by the edge network.

// middleware.ts
import { NextRequest, NextResponse } from 'next/server';

export function middleware(request: NextRequest) {
  const host = request.headers.get('host') ?? '';
  const subdomain = host.split('.')[0];

  // Inject as a header so server components can read it
  const requestHeaders = new Headers(request.headers);
  requestHeaders.set('x-tenant-id', subdomain);

  return NextResponse.next({ request: { headers: requestHeaders } });
}

Lessons from Production

• Index on tenant_id first: Every query in a shared-schema setup must filter by tenant_id. Add a composite index (tenant_id, created_at) on every high-volume table from day one — retrofitting this at 10M rows is painful.
• Your pricing model shapes your architecture: If you offer a 'dedicated instance' tier, design the tenant resolver to support it from the start. Switching from shared to dedicated mid-flight is a migration project, not a feature.
• Audit logs are non-negotiable: Maintain a separate audit_logs table outside of RLS scope. When a customer claims they didn't delete that record, you'll want immutable evidence.
• Connection pooling and RLS don't always mix well: PgBouncer in transaction mode resets session variables between transactions. Use session mode or set the tenant context on every query explicitly.