Multi-Tenant Database Design Cheat Sheet

CREATE TABLE invoices (    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),    tenant_id UUID NOT NULL,    amount NUMERIC NOT NULL,    created_at TIMESTAMPTZ DEFAULT now());CREATE INDEX ON invoices (tenant_id);ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;CREATE POLICY tenant_isolation ON invoices    USING (tenant_id = current_setting('app.current_tenant')::UUID);-- Set per-connection/session before running app queriesSET app.current_tenant = '3f2504e0-4f89-11d3-9a0c-0305e82c3301';

-- Provisioning a new tenantCREATE SCHEMA tenant_acme;SET search_path TO tenant_acme;CREATE TABLE invoices (LIKE public.invoices_template INCLUDING ALL);-- Application connects and sets search_path per request-- e.g. in Node: await client.query('SET search_path TO tenant_acme');-- Migrations must be looped across all tenant schemas-- for schema in $(psql -tAc "SELECT nspname FROM pg_namespace WHERE nspname LIKE 'tenant_%'"); do#   psql -c "SET search_path TO $schema; \i migration.sql"# done

prisma.$use(async (params, next) => {  const tenantId = getCurrentTenantId(); // from request context / AsyncLocalStorage  if (params.model === 'Invoice') {    if (params.action === 'findMany' || params.action === 'findFirst') {      params.args.where = { ...params.args.where, tenantId };    }    if (params.action === 'create') {      params.args.data.tenantId = tenantId;    }  }  return next(params);});

CREATE OR REPLACE FUNCTION auth.tenant_id() RETURNS UUID AS $$  SELECT NULLIF(current_setting('request.jwt.claims', true)::json->>'tenant_id', '')::UUID;$$ LANGUAGE sql STABLE;CREATE POLICY tenant_isolation_select ON invoices  FOR SELECT USING (tenant_id = auth.tenant_id());CREATE POLICY tenant_isolation_insert ON invoices  FOR INSERT WITH CHECK (tenant_id = auth.tenant_id());-- Force RLS even for the table owner role (critical: owners bypass RLS by default)ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

async function withTenant<T>(tenantId: string, fn: (tx: PoolClient) => Promise<T>): Promise<T> {  const client = await pool.connect();  try {    await client.query('BEGIN');    // SET LOCAL, not SET — it auto-resets at COMMIT/ROLLBACK, safe to reuse the connection    await client.query('SET LOCAL app.current_tenant = $1', [tenantId]);    const result = await fn(client);    await client.query('COMMIT');    return result;  } catch (err) {    await client.query('ROLLBACK');    throw err;  } finally {    client.release();  }}

CREATE TABLE accounts (    id UUID DEFAULT gen_random_uuid(),    tenant_id UUID NOT NULL,    name TEXT NOT NULL,    PRIMARY KEY (tenant_id, id));CREATE TABLE invoices (    id UUID DEFAULT gen_random_uuid(),    tenant_id UUID NOT NULL,    account_id UUID NOT NULL,    amount NUMERIC NOT NULL,    PRIMARY KEY (tenant_id, id),    -- Composite FK forces account_id to belong to the SAME tenant_id    FOREIGN KEY (tenant_id, account_id) REFERENCES accounts (tenant_id, id));

import asyncioimport asyncpgasync def migrate_tenant(dsn: str, sql: str, sem: asyncio.Semaphore) -> tuple[str, Exception | None]:    async with sem:        conn = await asyncpg.connect(dsn)        try:            async with conn.transaction():                await conn.execute(sql)            return dsn, None        except Exception as e:            return dsn, e        finally:            await conn.close()async def migrate_all(tenant_dsns: list[str], sql: str, max_concurrency: int = 10):    sem = asyncio.Semaphore(max_concurrency)    results = await asyncio.gather(*(migrate_tenant(d, sql, sem) for d in tenant_dsns))    failed = [(dsn, err) for dsn, err in results if err]    if failed:        raise RuntimeError(f"{len(failed)} tenant migrations failed: {failed}")