DOMAIN:DATABASE — MULTI-TENANT ARCHITECTURE

OWNER: boris, yoanna
UPDATED: 2026-03-24
SCOPE: tenant isolation strategy for all GE client projects

---

MULTITENANCY:OVERVIEW

GE builds SaaS applications for SME clients. Every application serves multiple end-customers (tenants).
Tenant isolation is non-negotiable — one tenant must NEVER see another tenant's data.
Failure here is a security incident, compliance violation, and business-ending event.

Three strategies exist. GE uses a decision tree to pick the right one per project.

---

MULTITENANCY:DECISION_TREE

EVALUATE IN ORDER

QUESTION_1: Does the client require physical data isolation (regulated industry, contractual requirement)?
YES → schema-per-tenant or database-per-tenant
NO → continue

QUESTION_2: Will the application exceed 100 tenants?
YES → application-level isolation or RLS (schema-per-tenant becomes unmanageable)
NO → continue

QUESTION_3: Does the client need per-tenant schema customization (custom fields, custom tables)?
YES → schema-per-tenant
NO → continue

QUESTION_4: Is the development team experienced with RLS?
YES → RLS (strongest isolation with shared tables)
NO → application-level isolation (simpler but higher risk)

GE DEFAULT RECOMMENDATION

DEFAULT: Row-Level Security (RLS) on shared tables
WHY: best balance of isolation strength, operational simplicity, and performance
ESCALATE_TO_SCHEMA_PER_TENANT: regulated industries, contractual physical isolation requirement
NEVER: database-per-tenant (operational nightmare at scale — connection pools, migrations, backups multiply)

---

MULTITENANCY:APPLICATION_LEVEL_ISOLATION

HOW IT WORKS

Every table has a tenant_id column.
Every query includes WHERE tenant_id = :current_tenant_id.
Application middleware sets current tenant from authentication context.
No database-level enforcement — isolation is purely in application code.

IMPLEMENTATION

// Drizzle schema
export const orders = pgTable('orders', {
  id: uuid('id').primaryKey().defaultRandom(),
  tenantId: uuid('tenant_id').notNull().references(() => tenants.id),
  // ... other columns
});

// Query — developer MUST include tenant filter
const results = await db
  .select()
  .from(orders)
  .where(eq(orders.tenantId, ctx.tenantId));

ADVANTAGES

SIMPLE: no PostgreSQL-specific features needed
PORTABLE: works on any database
EASY_TO_UNDERSTAND: every developer can read and debug

DISADVANTAGES

FRAGILE: one forgotten WHERE clause = data leak between tenants
UNAUDITABLE: no database-level proof of isolation for compliance
BUG_PRONE: LLM-generated code especially likely to forget tenant filter
NO_ENFORCEMENT: database will happily serve cross-tenant data if asked

MITIGATION IF CHOSEN

MUST: create a Drizzle helper that auto-injects tenant filter:

function tenantQuery<T>(table: T, tenantId: string) {
  return db.select().from(table).where(eq(table.tenantId, tenantId));
}

MUST: write integration tests that verify cross-tenant isolation
MUST: add Koen (linting agent) rules to flag queries missing tenant_id filter
NOTE: even with mitigations, this is the weakest isolation strategy

---

MULTITENANCY:SCHEMA_PER_TENANT

HOW IT WORKS

Each tenant gets a PostgreSQL schema (namespace): tenant_abc123.orders vs tenant_def456.orders.
Application sets search_path to the tenant's schema on each connection/transaction.
Shared data (lookup tables, system config) lives in public schema.

IMPLEMENTATION

-- Create tenant schema
CREATE SCHEMA tenant_abc123;

-- Create tables in tenant schema
CREATE TABLE tenant_abc123.orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  -- no tenant_id needed — schema IS the isolation
);

// Set search_path per request
await db.execute(sql`SET search_path TO ${sql.identifier(tenantSchema)}, public`);

ADVANTAGES

STRONG_ISOLATION: database-level boundary — even raw SQL queries are scoped
CUSTOMIZABLE: tenants can have custom tables/columns without affecting others
COMPLIANCE_FRIENDLY: can demonstrate physical separation for auditors
BACKUP_GRANULARITY: can dump/restore individual tenant schemas

DISADVANTAGES

MIGRATION_COMPLEXITY: every migration runs N times (once per tenant schema)
CONNECTION_POOL: search_path changes affect connection reuse — must reset per query
OPERATIONAL_OVERHEAD: >100 schemas becomes hard to manage
MONITORING: must aggregate metrics across schemas
SCALING_LIMIT: PostgreSQL catalog bloat with thousands of schemas — real limit around 500-1000

MIGRATION STRATEGY FOR SCHEMA-PER-TENANT

# Script to run migration across all tenant schemas
for schema in $(psql -t -c "SELECT schema_name FROM information_schema.schemata WHERE schema_name LIKE 'tenant_%'"); do
  psql -c "SET search_path TO $schema; \i migration.sql"
done

NOTE: this must be atomic — if migration fails for one tenant, investigate before continuing
NOTE: consider running migrations in parallel for many tenants (with connection limit awareness)

---

MULTITENANCY:ROW_LEVEL_SECURITY

HOW IT WORKS

Shared tables with tenant_id column — like application-level, but enforced BY THE DATABASE.
PostgreSQL automatically filters rows based on policies — no way to bypass from application code.
Even a SQL injection cannot access another tenant's data (if policies are correctly written).

SETUP

-- 1. Enable RLS on the table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- 2. Force RLS for table owner too (important for superuser safety)
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

-- 3. Create policy
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid);

APPLICATION INTEGRATION

// Middleware — runs before every database query
async function setTenantContext(tenantId: string) {
  await db.execute(sql`SET LOCAL app.current_tenant_id = ${tenantId}`);
}

// In request handler
await db.transaction(async (tx) => {
  await tx.execute(sql`SET LOCAL app.current_tenant_id = ${ctx.tenantId}`);
  // All subsequent queries in this transaction are automatically filtered
  const orders = await tx.select().from(ordersTable); // only returns this tenant's orders
});

NOTE: SET LOCAL scopes the setting to the current transaction — essential for connection pooling
NOTE: without a transaction, use SET (session-level) but reset after query
ALERT: if app.current_tenant_id is not set, the policy blocks ALL rows — fail-safe behavior

POLICY PATTERNS

SELECT ONLY (read isolation)

CREATE POLICY tenant_read ON orders
  FOR SELECT
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

INSERT ONLY (write isolation)

CREATE POLICY tenant_insert ON orders
  FOR INSERT
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid);

ALL OPERATIONS

CREATE POLICY tenant_all ON orders
  FOR ALL
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid);

ADMIN BYPASS (for system operations)

-- Create a role for admin operations
CREATE ROLE app_admin;

-- Policy: admin sees all, regular users see own tenant
CREATE POLICY tenant_or_admin ON orders
  USING (
    tenant_id = current_setting('app.current_tenant_id', true)::uuid
    OR current_user = 'app_admin'
  );

NOTE: current_setting('...', true) returns NULL if not set (instead of error) — needed for admin path
ALERT: admin bypass must be carefully restricted — audit all uses

SHARED DATA (lookup tables)

-- Public rows (tenant_id IS NULL) visible to everyone
-- Tenant-specific rows visible only to that tenant
CREATE POLICY shared_and_private ON categories
  USING (
    tenant_id IS NULL
    OR tenant_id = current_setting('app.current_tenant_id')::uuid
  );

---

MULTITENANCY:RLS_PERFORMANCE

INDEX REQUIREMENT

CRITICAL: tenant_id MUST be the first column in composite indexes for RLS tables
WHY: every query has an implicit WHERE tenant_id = X — without index, full table scan

-- GOOD: tenant_id first
CREATE INDEX idx_orders_tenant_created ON orders (tenant_id, created_at);

-- BAD: tenant_id not in index — RLS filter cannot use this index
CREATE INDEX idx_orders_created ON orders (created_at);

QUERY PLAN VERIFICATION

ALWAYS run EXPLAIN ANALYZE on key queries after enabling RLS:

SET app.current_tenant_id = 'test-tenant-uuid';
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC LIMIT 20;

CHECK: the Filter or Index Cond should show tenant_id = ...
CHECK: no Seq Scan on large tables — should use index with tenant_id

PERFORMANCE IMPACT

OVERHEAD: 1-5% for well-indexed tables — negligible
OVERHEAD: 10-50% if tenant_id is missing from indexes — fix indexes, not RLS
OVERHEAD: significant if RLS policy uses subqueries or function calls — keep policies simple
RULE: RLS policy should be a simple column comparison — no subqueries, no function calls
NOTE: PostgreSQL query planner inlines RLS policies — they are optimized with the query

---

MULTITENANCY:RLS_TESTING

UNIT TESTS

describe('RLS: orders tenant isolation', () => {
  const tenantA = 'tenant-a-uuid';
  const tenantB = 'tenant-b-uuid';

  beforeAll(async () => {
    // Create test data for both tenants
    await adminDb.insert(orders).values([
      { tenantId: tenantA, amount: 100 },
      { tenantId: tenantB, amount: 200 },
    ]);
  });

  it('tenant A cannot see tenant B orders', async () => {
    await db.transaction(async (tx) => {
      await tx.execute(sql`SET LOCAL app.current_tenant_id = ${tenantA}`);
      const results = await tx.select().from(orders);
      expect(results.every(r => r.tenantId === tenantA)).toBe(true);
      expect(results.some(r => r.tenantId === tenantB)).toBe(false);
    });
  });

  it('tenant A cannot insert into tenant B', async () => {
    await db.transaction(async (tx) => {
      await tx.execute(sql`SET LOCAL app.current_tenant_id = ${tenantA}`);
      await expect(
        tx.insert(orders).values({ tenantId: tenantB, amount: 300 })
      ).rejects.toThrow(); // WITH CHECK violation
    });
  });

  it('no tenant context returns zero rows', async () => {
    // app.current_tenant_id not set — policy should block all rows
    const results = await db.select().from(orders);
    expect(results.length).toBe(0);
  });
});

PENETRATION TESTING CHECKLIST

• [ ] Direct query without tenant context returns zero rows
• [ ] Setting tenant context to non-existent tenant returns zero rows
• [ ] Cannot UPDATE rows belonging to another tenant
• [ ] Cannot DELETE rows belonging to another tenant
• [ ] Cannot INSERT with another tenant's tenant_id
• [ ] Aggregate queries (COUNT, SUM) only reflect current tenant
• [ ] JOIN queries respect RLS on all joined tables
• [ ] Subqueries respect RLS
• [ ] CTE (WITH) queries respect RLS
• [ ] COPY command respects RLS (it does since PG 16)

---

MULTITENANCY:RLS_PITFALLS

PITFALL: PGBOUNCER TRANSACTION MODE

PROBLEM: SET LOCAL only works in transactions. PgBouncer transaction mode releases connection at transaction end.
SOLUTION: always wrap database calls in explicit transactions when using RLS + PgBouncer
ALERT: a bare SELECT without explicit transaction may execute without tenant context

PITFALL: DRIZZLE MIGRATION QUERIES

PROBLEM: Drizzle migration runner may not set tenant context — migration queries bypass RLS if run as table owner
SOLUTION: migrations should run as a superuser or the table owner — RLS does not apply to table owners UNLESS FORCE ROW LEVEL SECURITY is set
CHECK: verify ALTER TABLE ... FORCE ROW LEVEL SECURITY is set on all tenant tables

PITFALL: BACKUP AND RESTORE

PROBLEM: pg_dump runs as superuser — dumps all tenants' data
SOLUTION: this is correct for backups. For tenant-specific export, use COPY with WHERE clause
NOTE: restoring a backup restores all tenants — cannot selectively restore one tenant with RLS alone

PITFALL: POSTGRES SUPERUSER BYPASSES RLS

PROBLEM: superuser and table owner bypass RLS by default
SOLUTION: FORCE ROW LEVEL SECURITY makes policies apply to table owner too
SOLUTION: application should connect as a non-superuser role
ALERT: NEVER use the superuser role for application connections

PITFALL: FOREIGN KEY REFERENCES ACROSS TENANTS

PROBLEM: FK constraint does not enforce tenant isolation — tenant A's order could reference tenant B's user
SOLUTION: include tenant_id in FK: FOREIGN KEY (tenant_id, user_id) REFERENCES users (tenant_id, id)
REQUIRES: composite unique constraint on referenced table: UNIQUE (tenant_id, id)

PITFALL: MISSING RLS ON NEW TABLES

PROBLEM: new table added without RLS — data accessible to all tenants
SOLUTION: DBA review checklist includes RLS verification for every new table with tenant_id
AUTOMATION: CI check that every table with a tenant_id column has RLS enabled

---

MULTITENANCY:GE_IMPLEMENTATION_GUIDE

STANDARD SETUP FOR NEW CLIENT PROJECT

-- 1. Create application role (non-superuser)
CREATE ROLE app_user LOGIN PASSWORD '...';
GRANT USAGE ON SCHEMA public TO app_user;

-- 2. For each table with tenant_id:
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
  FOR ALL
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid);

GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app_user;

-- 3. Create admin role for migrations and system operations
CREATE ROLE app_admin LOGIN PASSWORD '...';
GRANT ALL ON ALL TABLES IN SCHEMA public TO app_admin;

DRIZZLE MIDDLEWARE PATTERN

// src/db/middleware/tenant.ts
import { sql } from 'drizzle-orm';

export function withTenant<T>(
  db: DrizzleClient,
  tenantId: string,
  callback: (tx: Transaction) => Promise<T>
): Promise<T> {
  return db.transaction(async (tx) => {
    await tx.execute(sql`SET LOCAL app.current_tenant_id = ${tenantId}`);
    return callback(tx);
  });
}

// Usage
const orders = await withTenant(db, ctx.tenantId, async (tx) => {
  return tx.select().from(ordersTable).where(eq(ordersTable.status, 'pending'));
});

NOTE: this pattern ensures tenant context is ALWAYS set before any query
NOTE: if a developer forgets to use withTenant, RLS blocks all rows — fail-safe