Claude Code for Multi-Tenancy: Row-Level Security and Tenant Isolation

Multi-tenancy is where the gap between “it works in testing” and “it works in production” is most dangerous — a data isolation bug exposes one customer’s data to another. Claude Code implements multi-tenancy patterns with the correct trade-offs for your scale: shared schema with Row-Level Security for most SaaS applications, or separate schemas/databases for enterprise customers who require strict isolation.

This guide covers multi-tenancy with Claude Code: schema strategies, PostgreSQL RLS, middleware implementation, and testing isolation.

Schema Strategies

We're building a project management SaaS for teams.
What multi-tenant strategy should we use?

Claude Code’s analysis for a typical SaaS:

For most SaaS products: shared schema with PostgreSQL Row-Level Security — one database, tenant ID on each table, database enforces isolation.

Row-Level Security with PostgreSQL

Set up RLS so queries automatically filter to the current tenant.
No need to add WHERE tenant_id = $1 to every query.

-- Tables include tenant_id on all rows
CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  description TEXT,
  created_by UUID NOT NULL REFERENCES users(id),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index for tenant filtering (crucial for performance)
CREATE INDEX idx_projects_tenant ON projects(tenant_id);

-- Enable Row-Level Security
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Policy: users can only see projects in their tenant
CREATE POLICY projects_tenant_isolation ON projects
  USING (tenant_id = current_setting('app.tenant_id', true)::UUID);

-- Separate policy for INSERT — ensure data goes to correct tenant
CREATE POLICY projects_tenant_insert ON projects
  FOR INSERT
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::UUID);

-- Set the tenant context at the start of each request
-- This setting is scoped to the transaction
SET LOCAL app.tenant_id = '550e8400-e29b-41d4-a716-446655440000';

-- Now ALL queries on this connection are automatically filtered
SELECT * FROM projects;  -- Returns ONLY projects for the current tenant
INSERT INTO projects (name, tenant_id) VALUES ('New Project', current_setting('app.tenant_id')::UUID);
-- INSERT automatically validated against policy

Application Layer Implementation

Set up the middleware that establishes tenant context
from JWT claims before any database queries run.

// src/middleware/tenant.ts
import type { RequestHandler } from 'express';
import { db } from '../lib/db';

export const tenantMiddleware: RequestHandler = async (req, res, next) => {
  const userId = req.user?.id;
  if (!userId) return next(); // Auth middleware runs first
  
  // Get tenant from user's JWT claims (or from subdomain)
  const tenantId = req.user?.tenantId ?? extractTenantFromHost(req.hostname);
  
  if (!tenantId) {
    return res.status(400).json({ error: 'Tenant context required' });
  }
  
  // Verify user belongs to this tenant
  const membership = await db.query(
    'SELECT tenant_id FROM tenant_members WHERE user_id = $1 AND tenant_id = $2',
    [userId, tenantId],
  );
  
  if (membership.rows.length === 0) {
    return res.status(403).json({ error: 'Access denied to this tenant' });
  }
  
  req.tenantId = tenantId;
  next();
};

// src/lib/db.ts — tenant-aware database client
import { Pool } from 'pg';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

export class TenantAwareDB {
  async withTenant<T>(tenantId: string, fn: (client: PoolClient) => Promise<T>): Promise<T> {
    const client = await pool.connect();
    
    try {
      await client.query('BEGIN');
      
      // Set tenant context — RLS policies use this
      await client.query(
        'SELECT set_config($1, $2, true)',  // true = local to transaction
        ['app.tenant_id', tenantId],
      );
      
      const result = await fn(client);
      
      await client.query('COMMIT');
      return result;
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release();
    }
  }
}

export const tenantDb = new TenantAwareDB();

// Usage in a route handler
app.get('/api/projects', tenantMiddleware, async (req, res) => {
  const projects = await tenantDb.withTenant(req.tenantId!, async (client) => {
    // RLS automatically filters to req.tenantId
    // No WHERE clause needed
    const result = await client.query('SELECT * FROM projects ORDER BY created_at DESC');
    return result.rows;
  });
  
  res.json({ projects });
});

Tenant Provisioning

When a new customer signs up, provision their tenant:
create the tenant record, admin user, and initial data.

// src/services/tenant-provisioning.ts
import { db } from '../lib/db';

interface ProvisionTenantInput {
  tenantName: string;
  adminEmail: string;
  adminName: string;
  plan: 'starter' | 'growth' | 'enterprise';
}

export async function provisionTenant(input: ProvisionTenantInput) {
  // All in a single transaction — either fully provisioned or not at all
  return db.transaction(async (trx) => {
    // 1. Create tenant
    const [tenant] = await trx('tenants').insert({
      name: input.tenantName,
      plan: input.plan,
      slug: slugify(input.tenantName),
    }).returning('*');
    
    // 2. Create admin user
    const passwordHash = await hashPassword(generateTemporaryPassword());
    const [user] = await trx('users').insert({
      email: input.adminEmail,
      name: input.adminName,
      password_hash: passwordHash,
    }).returning('*');
    
    // 3. Add user to tenant as owner
    await trx('tenant_members').insert({
      tenant_id: tenant.id,
      user_id: user.id,
      role: 'owner',
    });
    
    // 4. Create default workspace (scoped to this tenant)
    // Set tenant context for RLS
    await trx.raw('SELECT set_config(?, ?, true)', ['app.tenant_id', tenant.id]);
    
    await trx('workspaces').insert({
      tenant_id: tenant.id,
      name: `${input.tenantName}'s Workspace`,
      created_by: user.id,
    });
    
    // 5. Send welcome email with temp password
    await sendWelcomeEmail(user.email, user.name, tenant.slug);
    
    return { tenant, user };
  });
}

Testing Tenant Isolation

Write tests that verify tenant A cannot access tenant B's data.
This is a critical security requirement.

// tests/security/tenant-isolation.test.ts
describe('Tenant isolation', () => {
  let tenantA: Tenant;
  let tenantB: Tenant;
  let userA: User;
  let userB: User;
  let tokenA: string;
  let tokenB: string;
  
  beforeAll(async () => {
    // Provision two separate tenants
    ({ tenant: tenantA, user: userA } = await provisionTenant({
      tenantName: 'Company A',
      adminEmail: '[email protected]',
      adminName: 'Admin A',
      plan: 'starter',
    }));
    
    ({ tenant: tenantB, user: userB } = await provisionTenant({
      tenantName: 'Company B',
      adminEmail: '[email protected]',
      adminName: 'Admin B',
      plan: 'starter',
    }));
    
    tokenA = generateTestToken(userA.id, tenantA.id);
    tokenB = generateTestToken(userB.id, tenantB.id);
    
    // Create a project in tenant A
    await createProject(tenantA.id, { name: 'Secret Project A' });
  });
  
  it('tenant A can see their own projects', async () => {
    const response = await request(app)
      .get('/api/projects')
      .set('Authorization', `Bearer ${tokenA}`);
    
    expect(response.status).toBe(200);
    expect(response.body.projects).toHaveLength(1);
    expect(response.body.projects[0].name).toBe('Secret Project A');
  });
  
  it('tenant B CANNOT see tenant A projects', async () => {
    const response = await request(app)
      .get('/api/projects')
      .set('Authorization', `Bearer ${tokenB}`);
    
    expect(response.status).toBe(200);
    expect(response.body.projects).toHaveLength(0); // Sees nothing
  });
  
  it('tenant B cannot access tenant A project by ID', async () => {
    const projectA = await getFirstProject(tenantA.id);
    
    const response = await request(app)
      .get(`/api/projects/${projectA.id}`)
      .set('Authorization', `Bearer ${tokenB}`);
    
    // Should 404 (not 403 — don't reveal existence)
    expect(response.status).toBe(404);
  });
  
  it('tenant B cannot update tenant A project', async () => {
    const projectA = await getFirstProject(tenantA.id);
    
    const response = await request(app)
      .patch(`/api/projects/${projectA.id}`)
      .set('Authorization', `Bearer ${tokenB}`)
      .send({ name: 'Hacked' });
    
    expect(response.status).toBe(404);
    
    // Verify project wasn't modified
    const unchanged = await getProject(tenantA.id, projectA.id);
    expect(unchanged.name).toBe('Secret Project A');
  });
});

For the database migration strategy that adds tenant_id to existing tables without downtime, see the database migrations guide. For authentication that embeds tenant claims in JWT tokens, see the authentication guide. The Claude Skills 360 bundle includes multi-tenancy skill sets for SaaS data isolation. Start with the free tier to implement Row-Level Security for your application.