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