Multi-Tenant Architecture: Patterns and Pitfalls

When you're building a SaaS product, one of the first architectural decisions you'll face is: how do you keep each customer's data separate?

Building CROW, I evaluated three approaches and ultimately chose row-level security with a shared database. Here's why—and the pitfalls I encountered along the way.

The Three Approaches

1. Separate Databases Per Tenant

Each customer gets their own database instance.

Pros:

Complete isolation

Easy to reason about

Simple to backup/restore individual tenants

No risk of data leaks between tenants

Cons:

Expensive at scale (each DB costs money)

Operational nightmare (managing thousands of databases)

Schema migrations become complex

Connection pooling is harder

Best for: Enterprise SaaS with few, large customers who demand isolation.

2. Shared Database, Separate Schemas

One database, but each tenant gets their own schema (namespace).

Pros:

Better resource utilization than separate DBs

Still provides logical separation

Easier to manage than separate databases

Cons:

Schema migrations still complex

Connection pooling still tricky

Doesn't scale well past hundreds of tenants

Best for: Mid-market SaaS with dozens to hundreds of customers.

3. Shared Database, Shared Schema (Row-Level Security)

All tenants share the same tables, with a tenant_id column on every row.

Pros:

Most efficient resource usage

Simplest to operate

Schema migrations apply once

Scales to millions of tenants

Cons:

Requires discipline (every query must filter by tenant)

One bug can leak data between tenants

Noisy neighbor problems possible

Best for: B2B SaaS with many small-to-medium customers.

Why I Chose Row-Level Security

For CROW, the choice was clear. We expected many small customers (individual repair shops), not a few large enterprises. Row-level security with PostgreSQL gave us:

Operational simplicity — One database to manage

Cost efficiency — Important for a bootstrapped product

Fast onboarding — New tenants are just rows, not infrastructure

Implementation: PostgreSQL RLS

PostgreSQL's Row-Level Security feature is perfect for this. Here's how I set it up:

-- Enable RLS on the vehicles table
ALTER TABLE vehicles ENABLE ROW LEVEL SECURITY;
-- Create a policy that restricts access to the current tenant
CREATE POLICY tenant_isolation ON vehicles
  USING (tenant_id = current_setting('app.current_tenant')::uuid);
-- Force RLS even for table owners
ALTER TABLE vehicles FORCE ROW LEVEL SECURITY;

Now, every query against the vehicles table automatically filters by the current tenant—even if the developer forgets to add a WHERE clause.

Setting the Tenant Context

In the application layer, we set the tenant context at the start of each request:

// Middleware that runs on every request
async function tenantMiddleware(req: Request, res: Response, next: NextFunction) {
  const tenantId = extractTenantId(req);  // From JWT, subdomain, or header
  
  if (!tenantId) {
    return res.status(401).json({ error: 'Tenant not identified' });
  }
  
  // Set PostgreSQL session variable
  await db.raw(SET app.current_tenant = '${tenantId}');
  
  req.tenantId = tenantId;
  next();
}

Always validate and sanitize the tenant ID! SQL injection through session variables is still possible. Use parameterized queries or UUID validation.

The Pitfalls I Encountered

Pitfall 1: Forgetting RLS on New Tables

When you add a new table, you must remember to:

Add the tenant_id column

Enable RLS

Create the isolation policy

I built a migration helper to enforce this:

// Custom migration that enforces tenant columns
export async function createTenantTable(
  knex: Knex, 
  tableName: string, 
  builder: (table: Knex.CreateTableBuilder) => void
) {
  await knex.schema.createTable(tableName, (table) => {
    table.uuid('id').primary().defaultTo(knex.raw('gen_random_uuid()'));
    table.uuid('tenant_id').notNullable().index();
    table.timestamps(true, true);
    builder(table);
  });
  
  // Automatically enable RLS
  await knex.raw(ALTER TABLE ${tableName} ENABLE ROW LEVEL SECURITY);
  await knex.raw(ALTER TABLE ${tableName} FORCE ROW LEVEL SECURITY);
  await knex.raw(
    CREATE POLICY tenant_isolation ON ${tableName}
    USING (tenant_id = current_setting('app.current_tenant')::uuid)
  );
}

Pitfall 2: Aggregate Queries Across Tenants

Sometimes you need admin queries that span all tenants (for analytics, billing, etc.). RLS blocks these by default.

Solution: Use a separate database role for admin queries:

-- Create an admin role that bypasses RLS
CREATE ROLE admin_role BYPASSRLS;
-- Grant it to your admin connection
GRANT admin_role TO admin_user;

In the app, admin endpoints use a separate connection pool:

const tenantDb = knex({ /<em> regular connection </em>/ });
const adminDb = knex({ /<em> admin connection with BYPASSRLS role </em>/ });
// Regular endpoints use tenantDb
app.get('/api/vehicles', async (req, res) => {
  const vehicles = await tenantDb('vehicles').select('*');
  // RLS automatically filters by current tenant
});
// Admin endpoints use adminDb
app.get('/admin/all-vehicles', adminAuth, async (req, res) => {
  const vehicles = await adminDb('vehicles').select('*');
  // No RLS filtering - sees all tenants
});

Pitfall 3: Foreign Key Constraints

If you have foreign keys between tenant tables, you need to ensure they can only reference rows within the same tenant:

-- Bad: Could reference another tenant's vehicle!
ALTER TABLE service_records
  ADD CONSTRAINT fk_vehicle
  FOREIGN KEY (vehicle_id) REFERENCES vehicles(id);
-- Good: Compound foreign key includes tenant_id
ALTER TABLE service_records
  ADD CONSTRAINT fk_vehicle
  FOREIGN KEY (tenant_id, vehicle_id) 
  REFERENCES vehicles(tenant_id, id);

Pitfall 4: Testing Is Hard

Unit tests need to simulate multi-tenant scenarios:

describe('Vehicle Service', () => {
  const tenant1 = uuid();
  const tenant2 = uuid();
  
  beforeEach(async () => {
    // Create test vehicles for both tenants
    await createVehicle({ tenantId: tenant1, make: 'Honda' });
    await createVehicle({ tenantId: tenant2, make: 'Toyota' });
  });
  
  it('should only return vehicles for current tenant', async () => {
    await setCurrentTenant(tenant1);
    
    const vehicles = await vehicleService.list();
    
    expect(vehicles).toHaveLength(1);
    expect(vehicles[0].make).toBe('Honda');
    // Should NOT see tenant2's Toyota
  });
});

Performance Considerations

With millions of rows across thousands of tenants, you need proper indexing:

-- Always include tenant_id in your indexes
CREATE INDEX idx_vehicles_tenant_make ON vehicles(tenant_id, make);
CREATE INDEX idx_service_records_tenant_date ON service_records(tenant_id, service_date);
-- Partial indexes for tenant-specific hot paths
CREATE INDEX idx_active_vehicles ON vehicles(tenant_id, id) 
  WHERE status = 'active';

And consider partitioning for very large tables:

-- Partition by tenant_id hash for even distribution
CREATE TABLE service_records (
  id uuid,
  tenant_id uuid,
  -- ... other columns
) PARTITION BY HASH (tenant_id);
CREATE TABLE service_records_p0 PARTITION OF service_records
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);
-- ... create partitions 1-3

Conclusion

Row-level security is powerful but requires discipline. The pattern works beautifully when implemented correctly, but a single mistake can expose data across tenants.

My recommendations:

Automate RLS setup — Don't rely on developers remembering

Test tenant isolation explicitly — Every test should verify data boundaries

Use separate roles for admin queries — Don't disable RLS globally

Index with tenant_id first — All tenant queries need this

For CROW, this architecture handles hundreds of repair shops efficiently on a single PostgreSQL instance. When we outgrow it, we can shard by tenant—but that's a problem for future me.

---

Building a multi-tenant SaaS? Let's chat about architecture decisions.