Multitenancy Patterns: How to Isolate Customer Data at Scale

#database #architecture #saas #backend

Multitenancy Patterns: How to Isolate Customer Data at Scale

Every B2B SaaS needs multitenancy — multiple customers sharing infrastructure, with their data isolated from each other.

The Three Approaches

1. Shared Schema (Row-Level Isolation)

All tenants in the same tables, separated by tenant_id:

CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Every query MUST include tenant_id
SELECT * FROM projects WHERE tenant_id = $1 AND id = $2;

Pros: simplest, cheapest, easy to scale
Cons: risk of data leak if you forget the WHERE clause

2. Separate Schemas

Each tenant gets their own Postgres schema:

CREATE SCHEMA tenant_abc123;
CREATE TABLE tenant_abc123.projects ( id UUID PRIMARY KEY, ... );

-- Query within tenant's schema
SET search_path = tenant_abc123;
SELECT * FROM projects;

Pros: stronger isolation, easier per-tenant migrations
Cons: schema management complexity, harder to query across tenants

3. Separate Databases

Each tenant gets their own database instance.

Pros: maximum isolation, easy compliance (GDPR deletion is drop database)
Cons: expensive, operational overhead, N connection pools

Recommended: Shared Schema With RLS

PostgreSQL Row Level Security enforces tenant isolation at the database level:

-- Enable RLS on the table
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Policy: users can only see their tenant's data
CREATE POLICY tenant_isolation ON projects
  USING (tenant_id = current_setting('app.tenant_id')::UUID);

// Set tenant context before every query
async function withTenant<T>(tenantId: string, fn: () => Promise<T>): Promise<T> {
  return prisma.$transaction(async (tx) => {
    await tx.$executeRaw`SELECT set_config('app.tenant_id', ${tenantId}, true)`;
    return fn();
  });
}

// Usage
const projects = await withTenant(user.tenantId, () =>
  prisma.projects.findMany() // RLS handles filtering automatically
);

Middleware to Inject Tenant

// Every authenticated request has tenant context
app.use(async (req, res, next) => {
  const session = await getSession(req);
  if (!session) return res.status(401).json({ error: 'Unauthorized' });

  req.tenantId = session.user.tenantId;
  req.userId = session.user.id;
  next();
});