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);