Building Multi-Tenant SaaS: Data Isolation Strategies in PostgreSQL

Multi-tenancy is one of the first architectural decisions in a SaaS app.
Get it wrong and you're refactoring everything at scale. Here are the three patterns.

The Three Approaches

1. Shared schema: All tenants in the same tables, tenant_id column
2. Separate schemas: Each tenant gets their own PostgreSQL schema
3. Separate databases: Each tenant gets their own database

Approach 1: Shared Schema (Most Common)

CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL
);

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID REFERENCES organizations(id),
  email TEXT NOT NULL,
  UNIQUE(org_id, email)
);

CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID REFERENCES organizations(id) NOT NULL,
  name TEXT NOT NULL
);

-- Index every org_id column
CREATE INDEX users_org_idx ON users(org_id);
CREATE INDEX projects_org_idx ON projects(org_id);

The dangerous part: every query must include org_id. Forget it once and you have a data leak.

Row Level Security (PostgreSQL RLS)

RLS enforces tenant isolation at the database level — the application can't accidentally bypass it:

-- Enable RLS on all tables
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Policy: only see your org's data
CREATE POLICY org_isolation ON projects
  USING (org_id = current_setting('app.current_org_id')::uuid);

// Set org context before every query
async function withOrgContext<T>(
  orgId: string,
  fn: (db: typeof prisma) => Promise<T>
): Promise<T> {
  return prisma.$transaction(async (tx) => {
    await tx.$executeRaw`SELECT set_config('app.current_org_id', ${orgId}, true)`
    return fn(tx as unknown as typeof prisma)
  })
}

// Usage — RLS automatically filters to this org
const projects = await withOrgContext(orgId, (db) =>
  db.project.findMany()  // no WHERE clause needed
)

Middleware Pattern for Next.js

// middleware.ts
import { getServerSession } from 'next-auth'

export async function middleware(request: NextRequest) {
  const session = await getServerSession()
  if (!session) return NextResponse.redirect('/login')

  // Extract org from subdomain or path
  const hostname = request.headers.get('host')!
  const orgSlug = hostname.split('.')[0]  // acme.myapp.com

  const headers = new Headers(request.headers)
  headers.set('x-org-slug', orgSlug)

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

Approach 2: Separate Schemas

-- Each tenant gets their own schema
CREATE SCHEMA tenant_acme;
CREATE SCHEMA tenant_beta;

-- Each schema has identical tables
CREATE TABLE tenant_acme.projects ( ... );
CREATE TABLE tenant_beta.projects ( ... );

// Switch schema per request
const tenantDb = prisma.$extends({
  query: {
    $allOperations({ args, query }) {
      return prisma.$transaction(async (tx) => {
        await tx.$executeRaw`SET search_path TO ${Prisma.raw(schemaName)}`
        return query(args)
      })
    }
  }
})

Pros: Better isolation, easier per-tenant backups.
Cons: Migrations must run N times, harder to query across tenants.

Which to Choose

	Shared Schema	Separate Schemas	Separate DBs
Scale	Millions of tenants	Thousands	Hundreds
Isolation	App-enforced	Schema-level	Full
Cost	Low	Medium	High
Migration	Easy	Complex	Very complex

Default choice: Shared schema + RLS. Move to separate schemas only if compliance requires it.

---

The AI SaaS Starter Kit includes multi-tenancy foundations: organization model, RLS policies, and middleware for org context. $99 one-time.