SaaS Architecture Patterns: Multi-Tenant System Design

CREATE TABLE users (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  email VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_tenant (tenant_id)
);
-- CRITICAL: Always filter by tenant_id
SELECT * FROM users WHERE tenant_id = ? AND email = ?;

ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON users
  USING (tenant_id = current_setting('app.current_tenant')::uuid);

-- Create schema per tenant
CREATE SCHEMA tenant_abc;
CREATE TABLE tenant_abc.users (...);
-- tenant_xyz gets separate schema
CREATE SCHEMA tenant_xyz;
CREATE TABLE tenant_xyz.users (...);

┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐
│  tenant_abc_db  │  │  tenant_xyz_db  │  │  tenant_123_db  │
│                 │  │                 │  │                 │
│  users          │  │  users          │  │  users          │
│  orders         │  │  orders         │  │  orders         │
│  products       │  │  products       │  │  products       │
└─────────────────┘  └─────────────────┘  └─────────────────┘

import { AsyncLocalStorage } from 'async_hooks';
interface Tenant {
  id: string;
  slug: string;
  plan: 'free' | 'pro' | 'enterprise';
  databaseUrl?: string;
}
class TenantContext {
  private static storage = new AsyncLocalStorage();
  static getCurrentTenant(): Tenant {
    const tenant = this.storage.getStore();
    if (!tenant) {
      throw new Error('No tenant context - middleware not configured');
    }
    return tenant;
  }
  static run(tenant: Tenant, fn: () => T): T {
    return this.storage.run(tenant, fn);
  }
}
// Express middleware
const tenantMiddleware = async (req, res, next) => {
  const tenantSlug = req.subdomains[0] || 'default';
  const tenant = await resolveTenant(tenantSlug);
  if (!tenant) {
    return res.status(404).json({ error: 'Tenant not found' });
  }
  TenantContext.run(tenant, () => next());
};

abstract class TenantScopedRepository {
  protected abstract tableName: string;
  private get tenantId(): string {
    return TenantContext.getCurrentTenant().id;
  }
  async findAll(conditions?: Partial): Promise {
    // Tenant filter is ALWAYS applied automatically
    return this.db.query(
      SELECT * FROM ${this.tableName} WHERE tenant_id = $1,
      [this.tenantId]
    );
  }
  async create(data: Omit): Promise {
    // Tenant ID is ALWAYS injected automatically
    return this.db.query(
      INSERT INTO ${this.tableName} (tenant_id, ...) VALUES ($1, ...),
      [this.tenantId, ...]
    );
  }
}

┌─────────────────┐
                    │  Load Balancer  │
                    └────────┬────────┘
                             │
        ┌────────────────────┼────────────────────┐
        │                    │                    │
        ▼                    ▼                    ▼
┌───────────────┐  ┌───────────────┐  ┌───────────────┐
│   App Pod 1   │  │   App Pod 2   │  │   App Pod N   │
│  (Stateless)  │  │  (Stateless)  │  │  (Stateless)  │
└───────┬───────┘  └───────┬───────┘  └───────┬───────┘
        │                  │                  │
        └──────────────────┴──────────────────┘
                           │
                    ┌──────┴──────┐
                    ▼             ▼
             ┌──────────┐  ┌───────────┐
             │  Primary │  │  Replica  │
             │    DB    │──│    DB     │
             └──────────┘  └───────────┘

interface ShardConfig {
  id: number;
  connectionUrl: string;
  tenantRange: [number, number];
}
function getShardForTenant(tenantId: string): ShardConfig {
  const hash = consistentHash(tenantId);
  const shardId = hash % totalShards;
  return shardConfigs.find(s => s.id === shardId);
}
// Route tenant to correct shard
const tenant = await TenantContext.getCurrentTenant();
const shard = getShardForTenant(tenant.id);
const db = getConnection(shard.connectionUrl);