Multi-Tenancy Patterns: Shared vs Isolated Databases in SaaS

TL;DR

Shared database with row-level security works for 95% of SaaS products — simplest to implement, easiest to maintain, scales to millions of tenants. Schema-per-tenant for regulated industries (healthcare, finance) where tenant data isolation is a compliance requirement. Database-per-tenant only for enterprise clients paying $10k+/month who require contractual data isolation. Start with RLS.

The Three Patterns

Pattern 1: Shared Database (Row-Level Security)
  One database → One schema → Many tenants
  Tenant rows separated by organization_id
  Isolation enforced by RLS policies

Pattern 2: Schema-Per-Tenant
  One database → Many schemas (one per tenant)
  public, tenant_abc, tenant_xyz
  Tables duplicated per schema

Pattern 3: Database-Per-Tenant
  Many databases (one per tenant)
  Complete isolation at the infrastructure level

---

Pattern 1: Shared Database with RLS (Recommended)

Row-Level Security is a PostgreSQL feature that enforces access control at the database level. Even if application code has a bug and passes the wrong organization_id, the database enforces the policy.

Schema Design

// prisma/schema.prisma
model Organization {
  id        String   @id @default(cuid())
  name      String
  plan      String   @default("free")
  members   Member[]
  projects  Project[]
  createdAt DateTime @default(now())
}

model Member {
  id             String       @id @default(cuid())
  userId         String
  organizationId String
  role           MemberRole   @default(MEMBER)
  organization   Organization @relation(fields: [organizationId], references: [id])

  @@unique([userId, organizationId])
  @@index([organizationId])
}

model Project {
  id             String       @id @default(cuid())
  name           String
  organizationId String
  organization   Organization @relation(fields: [organizationId], references: [id])
  tasks          Task[]

  @@index([organizationId])  // Essential for performance
}

RLS Policies in PostgreSQL

-- Enable RLS on tenant tables
ALTER TABLE "Project" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "Task" ENABLE ROW LEVEL SECURITY;

-- Policy: users can only see their organization's data
-- current_setting() reads the org ID set by the application
CREATE POLICY "tenant_isolation" ON "Project"
  USING (
    "organizationId" IN (
      SELECT "organizationId"
      FROM "Member"
      WHERE "userId" = current_setting('app.current_user_id')
    )
  );

// lib/db.ts — set tenant context before every query
import { prisma } from './prisma';

export async function withTenant<T>(
  userId: string,
  organizationId: string,
  callback: () => Promise<T>
): Promise<T> {
  return prisma.$transaction(async (tx) => {
    // Set the current user/org context for RLS
    await tx.$executeRaw`SELECT set_config('app.current_user_id', ${userId}, true)`;
    await tx.$executeRaw`SELECT set_config('app.current_org_id', ${organizationId}, true)`;
    return callback();
  });
}

// Usage in API routes — tenant context is automatic
export async function GET(req: Request) {
  const { userId, organizationId } = await getAuthContext(req);

  return withTenant(userId, organizationId, async () => {
    // RLS ensures this only returns this org's projects
    const projects = await prisma.project.findMany();
    return Response.json(projects);
  });
}

Supabase RLS (The Easy Way)

Supabase makes RLS configuration through their dashboard:

-- Supabase RLS policy using auth.uid()
CREATE POLICY "Users can view org projects"
ON projects FOR SELECT
USING (
  organization_id IN (
    SELECT organization_id FROM members
    WHERE user_id = auth.uid()
  )
);

CREATE POLICY "Users can insert org projects"
ON projects FOR INSERT
WITH CHECK (
  organization_id IN (
    SELECT organization_id FROM members
    WHERE user_id = auth.uid() AND role IN ('owner', 'admin', 'member')
  )
);

---

Pattern 2: Schema-Per-Tenant

Each tenant gets their own PostgreSQL schema:

database: myapp
├── schema: public (shared: auth, billing)
├── schema: org_abc123 (tenant A)
│   ├── projects
│   ├── tasks
│   └── settings
└── schema: org_xyz789 (tenant B)
    ├── projects
    ├── tasks
    └── settings

// Create tenant schema on org creation
async function provisionTenant(organizationId: string) {
  const schemaName = `org_${organizationId}`;

  await prisma.$executeRaw`CREATE SCHEMA IF NOT EXISTS ${schemaName}`;

  // Run migrations in the new schema
  await prisma.$executeRaw`
    CREATE TABLE IF NOT EXISTS ${schemaName}.projects (
      id TEXT PRIMARY KEY DEFAULT gen_random_uuid(),
      name TEXT NOT NULL,
      created_at TIMESTAMPTZ DEFAULT now()
    )
  `;
}

// Query tenant schema
async function getProjects(organizationId: string) {
  const schema = `org_${organizationId}`;
  return prisma.$queryRaw`
    SELECT * FROM ${schema}.projects ORDER BY created_at DESC
  `;
}

When to use: Regulatory compliance (ISO 27001, SOC 2 Type II), healthcare (HIPAA), or financial products where tenants need contractual schema isolation.

Trade-offs: Schema migrations must run per-tenant (automated, but operationally complex at hundreds of tenants).

---

Pattern 3: Database-Per-Tenant

Each tenant gets their own database:

// Tenant registry
const tenantDatabases = new Map<string, PrismaClient>();

async function getTenantDb(organizationId: string): Promise<PrismaClient> {
  if (tenantDatabases.has(organizationId)) {
    return tenantDatabases.get(organizationId)!;
  }

  const tenant = await getTenantConfig(organizationId);
  const db = new PrismaClient({
    datasources: { db: { url: tenant.databaseUrl } }
  });

  tenantDatabases.set(organizationId, db);
  return db;
}

// Usage
export async function GET(req: Request) {
  const { organizationId } = await getOrgContext(req);
  const db = await getTenantDb(organizationId);

  const projects = await db.project.findMany();
  return Response.json(projects);
}

When to use: Enterprise clients ($10k+/month) who require contractual data isolation, data residency in specific regions, or custom backup schedules.

Trade-offs: Expensive, complex to operate. Neon's branching can help — each tenant gets a Neon branch/project.

---

Boilerplate Multi-Tenancy Support

Boilerplate	Multi-Tenancy	Pattern
Supastarter	✅	Supabase RLS
Makerkit	✅	Supabase RLS + Organizations
Volca	✅	Prisma + organizationId
Bedrock	✅	Enterprise patterns
T3 Stack	❌ (add yourself)	—
ShipFast	❌	—

---

Starting Multi-Tenant from Day One

If you know your SaaS will be multi-tenant, design it in from the start:

// The minimal multi-tenant schema
// Add organizationId to every user-owned resource
model Project {
  id             String   @id @default(cuid())
  // ...other fields...
  organizationId String   // Foreign key to Organization
  organization   Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)

  @@index([organizationId])  // Always index this for performance
}

Adding multi-tenancy to a single-tenant app later is painful. If there's any chance of it, add organizationId now.

---

Find multi-tenant SaaS boilerplates on StarterPick.