Skip to main content

Multi-tenancy

The architecture has two hard requirements, and they turn out to be the same mechanism: any number of institutions can be added without new code or schema changes, and each institution's data stays isolated. Isolation is enforced inside Postgres, not in application code, so an application bug cannot leak cross-tenant rows.

The model: shared schema + row-level security​

Every tenant-scoped table carries tenant_id and has one RLS policy of the same shape:

tenant_id = nullif(current_setting('app.current_tenant_id', true), '')::uuid

That policy is generated once for all 19 tenant-scoped tables from a single array in packages/db/src/schema/index.ts:

export const TENANT_SCOPED_TABLES = [
  "module_entitlements", "custom_field_definitions", "departments", "programmes",
  "branches", "batches", "staff_categories", "class_sections", "subjects", "persons",
  "person_addresses", "person_sensitive_identifiers", "student_enrollments",
  "staff_employment", "faculty_subject_assignments", "attendance_delegations",
  "attendance_records", "staff_attendance_records", "audit_log",
] as const;

Deliberately not in that list: tenants itself, module_definitions (platform-seeded, not tenant-writable), the six global reference tables, and all seven Better Auth tables (Better Auth manages its own multi-tenancy through organization/member). Adding a table here is the only way to get isolation, which is why extending the schema always means editing this array.

The nullif(..., '') is not defensive noise. A pooled connection that has had app.current_tenant_id set at least once reverts to an empty string — not NULL — once that transaction ends, which is a documented Postgres quirk for custom GUCs on reused sessions. A plain current_setting(..., true)::uuid cast then throws invalid uuid syntax instead of cleanly evaluating false-for-every-row, which on a pooled connection reused across requests could surface as a 500 rather than a clean deny. NULLIF makes both the never-set and set-then-reverted cases resolve to the same safe deny.

Setting the tenant: withTenant()​

Every tenant-scoped request runs inside withTenant() (packages/db/src/client.ts), which opens a transaction, sets the session variable with SET LOCAL, and then runs the callback:

export async function withTenant<T>(tenantId: string, fn: (tx: Db) => Promise<T>): Promise<T> {
  return db.transaction(async (tx) => {
    await tx.execute(sql`select set_config('app.current_tenant_id', ${tenantId}, true)`);
    return fn(tx as unknown as Db);
  });
}

SET LOCAL is scoped to the transaction, so it never leaks across pooled connections. The raw db object is exported but documented as off-limits for tenant-scoped tables.

The full enforcement chain​

Four independent layers, each of which would need to fail for a cross-tenant read to happen:

  1. tenantMiddleware (apps/api/src/middleware/tenant.ts) — resolves the Better Auth session, reads its activeOrganizationId, maps that to a tenants row via tenants.betterAuthOrgId, and puts the resolved tenant id on the request context. It does not grant data access; it only resolves which tenant and who.
  2. requirePermission({ ... }) (apps/api/src/middleware/require-permission.ts) — calls Better Auth's own auth.api.hasPermission against the roles and statements declared in apps/api/src/auth.ts, so the permission model lives in exactly one place. Returns 403 on failure.
  3. withTenant() — sets the GUC. A route that forgets this gets zero rows rather than all rows.
  4. RLS policies in Postgres — the actual guarantee. Independent of the application entirely.

Handlers must still pass the tenant id into withTenant() themselves; layers 1 and 2 do not implicitly scope the query.

The bug that made RLS a no-op​

This is the most important thing to understand about this codebase's history, and it is documented in full in docs/M0_REMAINING_TASKS.md and in the comments on schema/index.ts.

The only Postgres role configured for the app was campus — the POSTGRES_USER of the official Postgres image, which is a superuser. Postgres superusers bypass RLS unconditionally, and FORCE ROW LEVEL SECURITY does not override that (it only affects a table's owner for non-superuser roles). So every policy above was silently inert for every real request the system had ever served.

It was caught while writing rls.test.ts, and fixed by:

  1. Adding a genuinely restricted runtime role, campus_app, with NOSUPERUSER NOBYPASSRLS, created idempotently by migrate.ts calling appRoleSetupSql().
  2. Switching packages/db/src/client.ts — and therefore every app route — to connect as it via a new RUNTIME_DATABASE_URL.
  3. Reserving DATABASE_URL (the owning role) for migrate / generate / seed / import scripts only.
  4. Hardening the policy expression with the NULLIF above.

The regression test is designed to fail against the old superuser role and pass against campus_app, so this specific mistake cannot return silently.

Pre-filtering: the part that is easy to get wrong​

For graph and vector queries there is a second, subtler leak surface. Tenant filtering must be a pre-filter — applied before traversal or similarity search runs — never a post-filter on results. Post-filtering lets tenant A's query score against tenant B's vectors or graph before the results are discarded, which is a real leak surface even though nothing is returned.

This is enforced structurally rather than by convention: there are exactly two sanctioned entry points, and both take tenantId as a required parameter.

// packages/db/src/graph-query-service.ts — the ONLY sanctioned way to run AGE Cypher
runTenantScopedCypher(tx, { tenantId, cypher, cypherParams })

// packages/db/src/vector-query-service.ts — the ONLY sanctioned way to run pgvector search
tenantScopedVectorSearch(tx, { tenantId, table, embeddingColumn, queryEmbedding, limit })

In runTenantScopedCypher, tenantId is injected as a mandatory bound parameter and the example query matches on it inside the pattern:

MATCH (s:Person {tenantId: $tenantId, personType: 'student'})
MATCH (s)-[:HAS_ATTENDANCE_FLAG]->(:AttendanceRisk {tenantId: $tenantId})
...

In tenantScopedVectorSearch, tenant_id is part of the WHERE clause that runs before the <=> ANN operator does its scan.

Both services also run inside a withTenant() transaction, so Postgres RLS is a second, independent enforcement layer on the underlying AGE and pgvector storage tables.

Two caveats worth carrying forward:

  • The abstraction is load-bearing, and it is a convention. Any code path that calls cypher() or queries an embeddings table directly reintroduces exactly the risk the research warned about. The technical architecture doc suggests an automated lint or review rule for this; none exists yet.
  • tenantScopedVectorSearch currently has no callers and there is no embeddings table. The pre-filter guarantee is only as meaningful as what actually goes through it — today, that is the graph service only.

Onboarding a new institution​

By design, this is a data operation rather than an engineering one:

  1. One row in tenants (slug, name, regulatoryFramework)
  2. One Better Auth organization row, with tenants.betterAuthOrgId pointing at it
  3. One module_entitlements row listing which of the 20 modules are licensed
  4. An admin user in that organization

No schema change, no new deployment, no new code path. RLS policies and the shared query services apply to the new tenant from its first write.

The graduation path​

If a specific institution ever needs physical isolation — rare, but real for some enterprise contracts — the same schema can be deployed into a dedicated Postgres instance for that tenant. Shared-schema is the default, not the only option. The rls.test.ts and docs/TECHNICAL_ARCHITECTURE.md §3 verification steps describe the two-layer test to run on any second tenant: confirm RLS blocks a cross-tenant read, and confirm a pre-filtered graph/vector query never touches the other tenant's rows. They are different enforcement layers, so both need checking.