Multi-Tenant Isolation Models

Multi-Tenant Isolation Models

This page helps operators building B2B SaaS applications on ɳSelf pick the right data isolation model before writing their first schema. The wrong choice at Sprint 5 becomes an expensive migration at Sprint 50.

This is not the same as ɳSelf Cloud multi-tenancy. If you are an ɳSelf Cloud operator and want to understand how ɳSelf separates its own paying customers, read Multi-Tenancy Conventions instead.

---

The four models

Single-tenant (dedicated stack per customer)

Each customer gets a dedicated ɳSelf deployment. Separate Postgres, separate Hasura, separate VPS or container group.

Use this when:

• HIPAA, FedRAMP, or financial regulations require physical data separation
• You have fewer than 10 customers and each one pays enough to justify dedicated infra
• Data residency laws require data to stay in a specific region per customer

Cost: highest infra cost; lowest isolation risk.

Row-level isolation (ɳSelf default)

All customers share one Postgres database. Every table gets a tenant_id UUID column. A Hasura row permission filter and PostgreSQL RLS policy restrict each session to its own rows.

Use this when:

• 10-500 customers with under 5 GB of data per tenant
• Standard compliance (SOC 2 Type II, GDPR with proper RLS)
• You want fast onboarding without per-customer DDL

Cost: lowest infra cost; simplest to operate.

Schema-per-tenant

One Postgres database, one Hasura instance. Each customer gets their own Postgres schema: tenant_acme, tenant_beta, etc. The schema boundary replaces the row filter.

Use this when:

• 50-1000 customers
• 1-50 GB of data per tenant
• Customers need independently restorable point-in-time snapshots
• Auditors require demonstrable schema-level separation
• You need pg_dump --schema=tenant_acme per-customer exports

Cost: moderate setup complexity; manageable at N=1000 with the search_path approach.

Database-per-tenant

Each customer gets their own Postgres database or Postgres instance.

Use this when:

• 500+ customers with strict compliance requirements
• You already operate connection pooling middleware (PgBouncer)

Cost: highest operational overhead; N databases, N migration runs, N backup schedules.

---

Picking a model

How many paying customers at 12 months?
|
+-- Under 10 ------------------------------------------> Row-level (default)
|   (unless HIPAA/FedRAMP required -> Single-tenant)
|
+-- 10 to 500 ---------> Per-tenant data > 5 GB? -----> Schema-per-tenant
|                         or audit separation required?
|                         No -> Row-level (default)
|
+-- Over 500 -----------> Strict compliance? ----------> Database-per-tenant
                          No -> Schema-per-tenant (with PgBouncer)

For Ummeco Sprint 5: expected tenant count 100-1000, moderate per-tenant data, GDPR compliance. Recommended: schema-per-tenant. Row-level works to start; migrate using the cookbook below when you reach 50+ tenants or auditors require schema-level separation.

---

Hasura + schema-per-tenant

The key is to use one set of tracked tables in a template schema plus a session variable that sets search_path per request. Do not track each tenant schema as a separate Hasura data source, that approach bloats metadata and does not scale past around 50 tenants.

-- Postgres function: called at the start of each GraphQL request
CREATE OR REPLACE FUNCTION set_tenant_schema() RETURNS void AS $$
BEGIN
  PERFORM set_config(
    'search_path',
    'tenant_' || current_setting('hasura.user.x-hasura-tenant-id', true) || ', public',
    false
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Your auth webhook must return the tenant schema name as a session variable:

{
  "X-Hasura-Role": "user",
  "X-Hasura-User-Id": "<uuid>",
  "X-Hasura-Tenant-Schema": "tenant_acme_corp"
}

This approach keeps Hasura metadata at O(tables x roles) regardless of how many tenants you have, because the schema boundary is enforced at the Postgres level.

---

Performance at scale

These are expected outcomes based on PostgreSQL catalog behavior at CX23 scale (2 vCPU, 4 GB RAM). Run the benchmark script in the internal architecture doc to get actual numbers for your workload.

N schemas	p50 query	p99 query	VACUUM ANALYZE	pg_dump (all)
10	1.2 ms	3.5 ms	< 1 sec	< 5 sec
100	1.3 ms	4.1 ms	2-5 sec	30-60 sec
1000	1.8 ms	8.2 ms	30-90 sec	5-15 min

Query latency stays nearly flat because search_path resolution is a catalog lookup. The p99 increase at N=1000 comes from planner statistics pressure (~20,000 additional pg_class rows). For OLTP workloads with sub-100ms SLOs, this overhead is not noticeable. For sub-millisecond SLOs, benchmark on your actual data first.

Per-customer pg_dump --schema=tenant_acme is fast regardless of total schema count. Connection count is also unaffected: schemas share the connection pool.

---

Drizzle migration runner

ɳSelf’s default migration runner targets the public schema. Schema-per-tenant requires running migrations per tenant on signup and on each schema update.

import { drizzle } from 'drizzle-orm/node-postgres';
import { migrate } from 'drizzle-orm/node-postgres/migrator';
import { Pool } from 'pg';

async function provisionTenantSchema(tenantId: string, pool: Pool): Promise<void> {
  const schemaName = `tenant_${tenantId.replace(/-/g, '_')}`;

  // Create schema (idempotent)
  await pool.query(`CREATE SCHEMA IF NOT EXISTS ${schemaName}`);

  // Create a schema-scoped connection pool
  const tenantPool = new Pool({ ...pool.options });
  tenantPool.on('connect', (client) => {
    client.query(`SET search_path TO ${schemaName}, public`);
  });

  // Run all Drizzle migrations in this tenant's schema
  const db = drizzle(tenantPool);
  await migrate(db, { migrationsFolder: './drizzle' });

  await tenantPool.end();
}

When you add a new migration, run it against all existing tenant schemas in batches:

async function runMigrationAllTenants(tenants: string[], pool: Pool): Promise<void> {
  const BATCH_SIZE = 10;
  for (let i = 0; i < tenants.length; i += BATCH_SIZE) {
    const batch = tenants.slice(i, i + BATCH_SIZE);
    await Promise.all(batch.map((id) => provisionTenantSchema(id, pool)));
  }
}

Track migration state per tenant in a shared public.tenant_migrations table so you can resume after failures.

---

RLS vs schema-per-tenant comparison

Dimension	Row-Level (RLS)	Schema-per-Tenant
Setup complexity	Low	Medium
Drizzle changes	None	Required
Hasura metadata changes	None	Moderate
Per-customer data export	Complex (filtered dump)	Simple (pg_dump --schema)
GDPR erasure	Filtered DELETE cascade	DROP SCHEMA tenant CASCADE
Cross-tenant query risk	RLS bug can expose data	Schema boundary enforced by Postgres
Planner statistics	Shared across tenants	Isolated per schema
Autovacuum pressure	Lower	Higher at N=1000
Max practical tenants	10,000+	1,000-2,000
Auditor preference	Acceptable	Preferred

The two models compose well: schema-per-tenant for the data plane with RLS within each schema as a defense-in-depth layer.

---

Recommendation matrix

Tenants	Per-tenant data	Compliance	Recommended
< 10	Any	Low-medium	Row-level (default)
< 10	Any	HIPAA / FedRAMP	Single-tenant
10-50	< 1 GB	Low	Row-level
10-100	1-5 GB	Medium	Row-level or Schema
50-500	1-10 GB	Medium	Schema-per-tenant
100-500	Any	SOC 2 Type II	Schema-per-tenant
500-1000	> 5 GB	Medium-high	Schema-per-tenant + PgBouncer
1000+	> 10 GB	High	Database-per-tenant
1000+	< 1 GB	Low-medium	Row-level

---

Migration cookbook: row-level to schema-per-tenant

Zero-downtime path, 10 steps.

1. Create schemas (no downtime): CREATE SCHEMA IF NOT EXISTS tenant_<id> for each tenant.
2. Provision tables: run Drizzle migrations in each tenant schema using the provisioning function above.
3. Copy data: INSERT INTO tenant_acme.records SELECT ... FROM public.records WHERE tenant_id = X.
4. Dual-write: write to both the old row-level path and the new tenant schema. Read from the tenant schema. This allows rollback.
5. Update Hasura: deploy the auth webhook change that sets X-Hasura-Tenant-Schema. Configure the set_tenant_schema() function.
6. Validate parity: SELECT count(*) from both locations must match for each tenant.
7. Cut over reads: all reads now target tenant schemas. Monitor for 24-48 hours.
8. Stop dual-write: remove the old row-level write path.
9. Archive shared rows: copy to an archive table, then delete from the shared table.
10. Drop tenant_id: once all tenants are migrated, remove the column from shared tables.

Estimated time: under 50 tenants and 100 GB total data runs in 2-4 hours. 100-500 tenants with 100 GB-1 TB takes 1-3 days with batched copy and validation.

---

Hasura metadata samples

Row-level model

table:
  name: records
  schema: public
select_permissions:
  - role: user
    permission:
      columns: "*"
      filter:
        tenant_id:
          _eq: X-Hasura-Tenant-Id
      allow_aggregations: false
insert_permissions:
  - role: user
    permission:
      columns: [user_id, payload]
      check:
        tenant_id:
          _eq: X-Hasura-Tenant-Id
      set:
        tenant_id: X-Hasura-Tenant-Id

Schema-per-tenant model

# Track the template schema; search_path resolves to the actual tenant schema at request time
table:
  name: records
  schema: tenant_template
select_permissions:
  - role: user
    permission:
      columns: "*"
      filter: {}   # Schema boundary replaces row filter
      allow_aggregations: false
insert_permissions:
  - role: user
    permission:
      columns: [user_id, payload]
      check: {}

Single-tenant model

Single-tenant means one organization per deploy. There is no tenant column. Hasura uses role-based permissions with no cross-tenant filter.

table:
  name: users
  schema: public
select_permissions:
  - role: user
    permission:
      filter:
        id: { _eq: X-Hasura-User-Id }
      columns: [id, email, created_at]
      allow_aggregations: false
  - role: admin
    permission:
      filter: {}
      columns: [id, email, created_at, role, last_login]
      allow_aggregations: true
insert_permissions:
  - role: admin
    permission:
      check: {}
      columns: [email, role]
update_permissions:
  - role: user
    permission:
      filter:
        id: { _eq: X-Hasura-User-Id }
      columns: [email]
  - role: admin
    permission:
      filter: {}
      columns: [email, role, last_login]

No tenant_id column, no search_path switching. Backup, restore, and query planning all use defaults. Operationally the simplest model. Suitable for any deploy serving one organization.

Database-per-tenant model

Each tenant gets its own Postgres database. Hasura connects to N databases as separate sources. Table metadata is identical across sources; the connection info differs per tenant.

# Per-tenant table metadata (same structure for every source)
table:
  name: users
  schema: public
select_permissions:
  - role: user
    permission:
      filter:
        id: { _eq: X-Hasura-User-Id }
      columns: [id, email, created_at]
      allow_aggregations: false
  - role: admin
    permission:
      filter: {}
      columns: [id, email, created_at, role, last_login]
      allow_aggregations: true

Source connections in metadata/databases/databases.yaml:

- name: tenant_acme
  kind: postgres
  configuration:
    connection_info:
      database_url: { from_env: TENANT_ACME_DATABASE_URL }
      isolation_level: read-committed
      pool_settings: { max_connections: 25, idle_timeout: 180, retries: 1 }
  customization: { naming_convention: hasura-default }
- name: tenant_globex
  kind: postgres
  configuration:
    connection_info:
      database_url: { from_env: TENANT_GLOBEX_DATABASE_URL }
      isolation_level: read-committed
      pool_settings: { max_connections: 25, idle_timeout: 180, retries: 1 }
  customization: { naming_convention: hasura-default }

The auth webhook must return the source name so Hasura routes each request to the right database:

{
  "X-Hasura-Role": "user",
  "X-Hasura-User-Id": "<uuid>",
  "X-Hasura-Source": "tenant_acme"
}

Connection-pool overhead grows linearly with N tenants. Use this model only when compliance or data sovereignty requires full database-level isolation.

---

See also

• Multi-Tenancy Conventions: how ɳSelf separates its own Cloud customers (tenant_id and source_account_id)
• ɳSelf CLI reference: nself db migrate, �P1� plugin install
• Plugin development guide: how plugins declare multi-app support