Multitenancy¶

1. Overview¶

SIS uses a shared schema. Tenant ownership is direct through tenant_id or inherited through a classified parent relation; mixed catalogues also admit explicitly global rows. Tenant isolation is enforced in two coexisting layers:

PostgreSQL Row-Level Security — FORCE RLS policies make cross-tenant access fail structurally at the database, including $queryRaw, independent of service discipline (§4).
Service-layer tenantId filtering — tenant-scoped service queries retain tenant ownership in their Prisma predicates (defense-in-depth, and the only layer for tables outside the RLS set).

2. Coverage registries¶

src/prisma/rls-coverage.ts classifies every model and is the authority for policy coverage. src/prisma/tenanted-models.ts mirrors models with required tenantId for strict-tenancy drift checks and the runtime misroute tripwire. Update both when adding a required-tenant model. Do not duplicate model counts or table inventories in this chapter.

3. Tenant Resolution¶

Tenant context is chosen after one global credential proof. Canonical email maps to one auth-private LoginIdentity (password and credential version). Each school keeps a distinct User with its own immutable established profileType and same-type person/history rows. Different schools may use different types for that identity; ordinary contact emails are independent. LoginIdentity.profileType is deprecated storage only. See the authentication chapter.

POST /api/v1/auth/login verifies one password before discovering active, invitation-enabled Users in ACTIVE/TRIAL tenants. Zero eligible memberships yields generic 401, one proceeds to role-view selection/session issuance, and multiple memberships return the school chooser. Both chooser JWTs carry private identity/version snapshots; consumption rechecks those and school eligibility under withTenantGuc. Administrative role views do not create extra person types.

GET /auth/tenants lists currently eligible schools. POST /auth/switch-tenant uses the current access proof and source refresh cookie/body alternative; a real switch consumes the source exactly once before target issuance. The final view-selection step owns that consumption when a chooser is needed. School reset/archive/delete operates only on its local User/session/access state; it cannot change shared credentials or cancel global recovery.

LoginIdentity and LoginPasswordResetRequest are private Class-X tables, inaccessible to app_user/PUBLIC. Both app-role provisioning implementations revoke their grants inside the grant transaction. Privileged identity operations expose named auth functions, not a generic cross-tenant write client. Database person/User binding guards are fixed-search-path SECURITY DEFINER constraint functions with no public/app_user EXECUTE privilege.

Full auth details: chapter 03

4. RLS architecture¶

The single source of truth is src/prisma/rls-coverage.ts — it classifies every model into four classes, migrations must implement its classifications, test/db-constraints.e2e-spec.ts asserts policy + FORCE for every covered table against it, and rls-coverage.drift.spec.ts fails CI when a new model isn't classified. Do not maintain a table list anywhere else. The required-tenant model registry must be updated alongside it.

Class	Tables	Predicate (USING + WITH CHECK)
S — simple	Required `tenant_id`	`tenant_id = (SELECT NULLIF(current_setting('app.current_tenant_id', true), ''))::uuid`
M — mixed catalogs	Nullable `tenant_id`; platform presets and tenant rows	`tenant_id IS NULL OR tenant_id = <GUC>` — GUC-less platform-admin routes see exactly the global rows; tenant requests see global ∪ own
C — children	No direct `tenant_id`; ownership through a parent	`EXISTS (SELECT 1 FROM <anchor> a WHERE a.id = <fk> AND a.tenant_id = <GUC>)` — explicit join to the tenant-bearing ancestor; two hops for the 3 grandchildren of `curriculum_subjects`; the M-shape arm when the anchor is mixed
X — excluded	Global or private infrastructure; reason recorded in the registry	none — genuinely tenant-less, each with a written reason in the registry

Predicate details (load-bearing): NULLIF(…, '') because a tx-local set_config reverts a custom GUC to '' (not NULL) on a reused pooled connection and bare ''::uuid errors; the scalar-subquery wrap makes the planner evaluate it once (InitPlan). No-GUC ⇒ Class S/C match nothing ⇒ fail closed (reads empty, writes rejected); Class M serves only the global rows — which is precisely what the backoffice preset routes ride on.
FORCE keeps the table owner subject to the policies. Superusers still bypass by role attribute — which is exactly how migrations, seeds and AdminPrismaService work.
RI bypass (by Postgres design): FK referential actions (ON DELETE CASCADE/SET NULL) and unique-constraint checks are not policy-filtered — parent deletes still cascade into policy-guarded children, and a cross-tenant unique collision can still surface as a P2002.
RLS is isolation, not authorization: nothing here gates which user within a tenant (or which platform admin) may write — that stays the RBAC layer's job.

4.0 GUC delivery — the request-scoped transaction¶

set_config(…, is_local = true) is transaction-scoped, and Prisma 7 + @prisma/adapter-pg have no per-request connection hook — so the only reliable delivery is one interactive transaction per tenant-scoped request:

TenantTransactionInterceptor (global APP_INTERCEPTOR, src/common/interceptors/tenant-transaction.interceptor.ts) opens txHost.withTransaction(...) for every non-exempt HTTP route, issues set_config('app.current_tenant_id', <cls tenantId>, true) as the first statement, stores the open tx in CLS under TENANT_TX_CLS_KEY, and runs the handler inside it. A non-exempt route with no CLS tenant fails closed (500 TENANT_GUARD_VIOLATION). Post-commit side effects: the interceptor also brackets the handler in PostCommitCoordinator.runScoped(...) (src/post-commit/) — request-path services register(job) deferred effects (S3 blob deletes, invitation emails) and the interceptor drains them only after the real outer commit, discarding on rollback. A service transaction joins the ambient request transaction; returning from it does not mean the database has committed. Scope ownership follows the real commit boundary: the OUTERMOST runScoped is the sole drain owner — a nested runScoped joins the enclosing scope (same queue, no early drain), mirroring how a nested $transaction joins the ambient tx. @NoTenantTx routes get no scope (there is no handler-wide commit to anchor a drain to; a service tx there commits mid-handler, so draining at handler-resolve could discard effects whose DB state already committed) — register() on an exempt route fails loudly, and an exempt flow that needs deferred effects brackets its own explicit transaction with postCommit.runScoped(...), which becomes the outermost owner there and joins harmlessly under tenant routes. Background sweepers own their own real tx and keep their local AfterCommitQueue. A post-commit job that must touch the DB opens its own withTenantGuc tx (the request tx and its GUC are gone by drain time — e.g. InvitationsService.onMailerSendFailed). Route-sized budgets: a route may declare @TenantTxBudget('<key>'); the interceptor resolves that named key from the single TX_BUDGETS registry in src/common/constants/tx-budgets.ts before opening the transaction. A nested $transaction carrying timeout or maxWait while joining the ambient transaction throws because an open transaction cannot be resized. An isolationLevel-only hint retains the documented-ignore behavior used by the expected-attendance resolver.
PrismaService's Proxy reads TENANT_TX_CLS_KEY on every property access: with a tx open, model delegates / $queryRaw / $executeRaw resolve to the tx client (the GUC connection) and $transaction(cb) joins it (calls cb(tx), no nesting). With no tx, everything serves from the base client. The proxy receives the transaction through CLS; keep this handoff aligned with the interceptor.
Single connection ⇒ serialized queries. In-request DB concurrency is deliberately serialized (Promise.all over queries → sequential await); non-DB concurrency (argon2, S3) stays parallel.
Exempt routes (@NoTenantTx() — login/refresh/logout, invitation verify/accept, health, resend webhook, platform-admin preset routes, and timetable generation) run on the base client with no ambient GUC, so covered tables read as empty unless the flow opens its own scoped transaction (Class M still serves global rows). The full bypass surface, flow by flow:

Flow	Client	Why
Global credential proof and eligible school discovery	LoginIdentityService + AdminPrismaService	Verify one private hash, then discover Users for that identity
Login steps 2–3 (`select-tenant`/`select-profile` user checks), `login()` (lastLoginAt + token issuance), profile resolution	`withTenantGuc`	the chosen/derived tenant id is in hand
Refresh / logout token-hash lookups	`AdminPrismaService`	the hash IS the credential; tenant unknown until the row is read — rotation then runs under `withTenantGuc`
Token-reuse family revocation	withTenantGuc on the source school	Own transaction commits before the error is thrown; profile/view switch routes are NoTenantTx
Invitation `verifyToken`/`acceptToken` by-token lookup	`AdminPrismaService` pre-read → `withTenantGuc` re-read	token IS the credential; the GUC tx re-reads the row under RLS (fail-closed) and does all the work
Timetable generation solver call	`withTenantGuc` ×2	short read-snapshot transaction → solver with no transaction open → revalidate-and-persist transaction; a structural race answers 409 before writing
Svix webhook delivery ingestion	`AdminPrismaService`	inbound provider callback, inherently tenant-less
Platform-preset usage guards (`rooms`, `subject-levels`, `evaluation-scales` services)	`AdminPrismaService`	"is this global preset referenced by ANY tenant?" — cross-tenant by definition; a GUC-less count on the RLS client is silently 0 and would defeat the in-use guard

The two clients: withTenantGuc(prisma, tenantId, fn) (src/common/utils/tenant-guc.ts) opens (or joins) a transaction and sets the GUC — the query stays under RLS; use it whenever ANY tenant id is derivable first. AdminPrismaService (superuser DATABASE_URL, RLS-bypassing) is reserved for credential-shaped lookups and cross-tenant-by-definition reads — its consumer allowlist is fenced by src/prisma/admin-prisma.usage.drift.spec.ts (a reference outside the allowlist fails CI) and documented per-consumer in admin-prisma.service.ts.

Guard-stack reads (before the interceptor opens the tx) that touch covered tables need their own GUC transaction. All of the permission-compilation reads are wrapped: fetchActiveAssignments (the caller's one assignment load per request, shared by guard compile, parameters, SEN checks and qualified slices — src/permissions/request-assignments.ts), fetchUserRoles (session surfaces), fetchTeacherDepartmentIds (src/permissions/permissions.queries.ts) and getActiveRoleKeys (permissions.service.ts) — user_roles/roles/role_permissions/user_role_parameters are all covered. withTenantGuc joins the ambient tx when one exists, so the same functions serve both guard-stack and in-request callers.

4.0.1 Connection roles¶

Connection	Env var	Role	RLS
App runtime (`PrismaService`)	`APP_DATABASE_URL` (required on hosted envs; falls back to `DATABASE_URL` only on `local`/`test`)	`app_user` — LOGIN, full DML, no BYPASSRLS, not owner	enforced
Migrations / seed / tooling / `AdminPrismaService`	`DATABASE_URL`	superuser (local `sis_dev`, hosted `postgres`)	bypassed

app_user is provisioned idempotently by npm run db:provision-app-role (prisma/sql/provision-app-role.cjs; hooked into docker:reset / db:setup, the e2e global setup, CI and the Railway pre-deploy). The script sets the role password from APP_DB_PASSWORD or the password in APP_DATABASE_URL; its built-in default password applies only when NODE_ENV is local or test, and anywhere else a missing source fails the run. Hosted envs therefore need APP_DATABASE_URL with a strong password before the first deploy — pre-deploy fails without it, and boot fails too (see the boot guards below; connection setup in chapter 10).

4.0.1a Boot guards — the fallback is loud, not silent¶

Two layers turn "silently unprotected" into "loudly unprovisioned":

Env validation (src/config/env.validation.ts): APP_DATABASE_URL is @ValidateIf(isProductionLikeEnv) required — a hosted boot without it throws before the app serves a request. local/test keep the superuser fallback so a fresh checkout boots before provisioning.
Runtime role probe (PrismaService.onModuleInit, assertRoleCannotBypassRls): on production-like envs, queries pg_roles for the connected role's rolsuper/rolbypassrls and throws if it can bypass RLS. Layer 1 can only see that a URL is set — this catches an APP_DATABASE_URL that resolves to a superuser.

4.0.1b Misroute tripwire (log-only)¶

The PrismaService proxy warns (event: tenant_tx.model_access_outside_tx) when a strictly-tenanted model delegate is accessed while a CLS tenant is present but no ambient tenant tx is open (shouldTripwireWarn, src/prisma/prisma.service.ts). Healthy operation produces zero hits — handler queries run in the interceptor's tx, guard-stack reads go through withTenantGuc (a $transaction access, not a delegate), and AdminPrismaService isn't proxied. A hit means a guard-stack read missing its withTenantGuc or a proxy misroute — both otherwise fail silently-closed as RLS-empty results; the tripwire makes them loud. Detection only: the query still proceeds and RLS remains the enforcement.

4.0.1c Adding a new model — classification recipe¶

Every new Prisma model MUST be classified in src/prisma/rls-coverage.ts (the drift spec fails CI until it is):

Required tenantId? → Class S, and add the Prisma model name to STRICTLY_TENANTED_MODELS in src/prisma/tenanted-models.ts (tenanted-models.drift.spec.ts fails until you do).
Nullable tenantId (platform presets ∪ tenant rows)? → Class M.
No tenantId, but rows belong to a tenant through a parent? → Class C with anchor/fk (+ via for a two-hop ancestor, mixedAnchor when the anchor is Class M).
Genuinely global/infra? → Class X, with a written reason.

Then add ENABLE ROW LEVEL SECURITY, FORCE ROW LEVEL SECURITY and the matching policy in a migration. Use the class shapes in the registry and existing migration SQL; include both USING and WITH CHECK. db-constraints.e2e-spec.ts checks the database policy, not just registry membership.

4.0.2 E2E convention¶

The suite runs with RLS enforced: the app-under-test connects as app_user (test/setup-e2e-env.ts builds the e2e APP_DATABASE_URL; test/global-setup-e2e.cjs provisions the role). Spec code runs outside any request (no GUC), so direct DB fixture access uses getAdminPrisma() (test/helpers/admin-prisma.helper.ts, superuser) — against the app client, covered-table reads/cleanup silently no-op and fixture creates are rejected by WITH CHECK. Proofs: test/rls-isolation.e2e-spec.ts (cross-tenant reads/writes blocked incl. $queryRaw, plus one live proof per policy class — S/M/C/grandchild) and test/db-constraints.e2e-spec.ts (policy + FORCE for every covered table, list imported from the coverage registry).

4.0.3 P2002 detail redaction under RLS¶

On an RLS-enabled table, Postgres redacts the DETAIL: Key (...) line of unique-violation (23505) errors for roles subject to the policy — the conflicting row's values could leak data the role can't SELECT. The pg driver adapter derives meta.driverAdapterError.cause.constraint.fields from that line, so as app_user a P2002 arrives with no field list at all (only originalMessage naming the constraint). mapP2002 (src/common/utils/prisma-p2002.ts) compensates by falling back to the constraint name parsed from originalMessage; handler regexes must therefore match index-name tokens, not just column-list tokens (e.g. the subject-groups dispatcher matches the truncated …_curriculum_subject_key). When writing a new mapP2002 handler, test it against the redacted shape — a column-only regex silently degrades every typed 409 into the filter's generic CONFLICT.

4.2 Base-service tenant predicate¶

BaseTenantedCrudService.resolveScopedWhere awaits resolveBaseWhere and then welds the authenticated tenantId onto the result. Base list/detail reads use this asynchronous path; a subclass cannot remove the tenant boundary by omitting or mis-setting it in its policy predicate. getScopedWhere remains a synchronous wrapper for pure callers. Follow the resolved path for policies with query dependencies.

5. Implementation rules¶

Classify every model by actual tenant ownership. Direct tenant columns, nullable preset ownership and child-through-parent ownership need different policy shapes.
Keep tenant-scoped predicates in services and query helpers. RLS is a backstop, not a substitute for domain filtering.
Controllers obtain the authenticated tenant through @TenantId(); never trust a caller-supplied tenant identifier as authorization.
Platform administrators bypass permission checks where explicitly allowed, but ordinary tenant requests still use the selected tenant's GUC. Platform-wide operations require an explicit administrative path.
User is a school membership with one established person type. A LoginIdentity may have memberships of different types in different schools; it does not permit mixing person types within one school membership.
Use withTenantGuc when the tenant is known. Privileged cross-tenant access belongs only to the named administrative/authentication consumers.

5.1 Named Exceptions¶

The "every query filters by tenantId" rule has several approved exceptions, all rooted in platform-owned rows (tenantId IS NULL). Their read and provisioning contracts differ:

Read-merge — global rows are unioned into tenant reads via a named visible…Where helper ({ OR: [{ tenantId }, { tenantId: null }] }), writes stay strict, presets are immutable on tenant routes. Applies to evaluation scales and subject levels.
Shared-reference — global rows are referenced directly by tenant data (FK) and read-merged, but writes against globals live only on a platform-admin backoffice route. Applies to room types.
Clone-and-backoffice-read — global rows are not unioned into tenant reads; tenant catalogues are seeded by cloning presets and read strictly by tenantId, while the raw global rows are read only on a platform-admin backoffice route. Applies to role presets.

Each is enumerated below.

Evaluation scales (read-merge)¶

Why: the grading-scale catalog ships three platform-provided presets (NUMERIC / LETTER / DESCRIPTIVE) that every tenant must see and reference, without being able to mutate them. Seeding a copy per tenant would freeze preset definitions; punching a hole in the read filter keeps preset evolution centralised.

How: the read filter is encoded in one helper on src/evaluation-scales/evaluation-scales.queries.ts:

export function visibleScaleWhere(
  tenantId: string,
): Prisma.EvaluationScaleWhereInput {
  return { OR: [{ tenantId }, { tenantId: null }] };
}

Every read path on EvaluationScalesService composes this; the FK validation helper assertScaleVisible(tenantId, scaleId) reuses it so cross-module consumers (e.g. CurriculumService validating a gradingScaleId payload) see the same visibility.

Boundary: writes never use visibleScaleWhere. Create / update / delete paths use the stricter tenantId = :tenantId filter, and the explicit EVALUATION_SCALE_PRESET_READONLY (403) check fires before any mutation against a platform row would even be reached. Two-layer defense: the WHERE clause cannot match a preset, and the explicit guard produces a clean error code.

Tests: evaluation-scales.service.spec.ts covers the boundary on both sides — tenant A cannot mutate tenant B's row, neither tenant can mutate a platform row, both can read platform rows.

Subject levels (read-merge)¶

Mirrors evaluation scales exactly. The IB HL/SL subject-level catalog ships platform-owned presets every tenant must see and reference but cannot mutate. The read filter is the named helper visibleLevelWhere(tenantId) on src/subject-levels/subject-levels.queries.ts (returns { OR: [{ tenantId }, { tenantId: null }] }, self-described as "the named exception to docs/02-multitenancy.md"). Write paths MUST NOT use it — they filter { id, tenantId } so presets stay immutable on tenant routes, with the SUBJECT_LEVEL preset-readonly guard firing before any mutation against a platform row.

Platform-global room types (shared-reference)¶

RoomType.tenantId is nullable. Reads union global defaults (tenantId IS NULL) with the tenant's own custom rows via listRoomTypesForTenant / findRoomTypeForTenant on src/rooms/rooms.queries.ts (where: { OR: [{ tenantId: null }, { tenantId }] }). Rooms reference the shared global rows directly, as curriculum rows can reference shared scales and levels. Writes against globals are restricted to the platform-admin backoffice /room-types/presets route (@PlatformAdminOnly, where: { tenantId: null }), with PROTECTED_ROOM_TYPES (CANTEEN) frozen via ROOM_TYPE_LOCKED.

Role presets (clone-and-backoffice-read)¶

Global preset roles live with tenantId IS NULL and use a clone model rather than read-merge. Tenant role catalogues are seeded by cloning presets and read strictly by tenantId (fetchTenantRoles, where: { tenantId } on src/permissions/permissions.queries.ts); the raw global rows are read only on the @PlatformAdminOnly /roles/presets backoffice route via fetchGlobalPresetRoles (where: { tenantId: null }). The global rows are never unioned into tenant reads, so this shape is distinct from the visibleScaleWhere / visibleLevelWhere read-merge.

Extending shared catalogues: evaluation scales, subject levels, room types, and role presets use named module helpers. A new mixed global/tenant catalogue must define its read and mutation ownership rules and register the appropriate RLS class.

6. Trust Proxy¶

Configured in main.ts via app.set('trust proxy', 1). Express uses this trusted-hop setting to derive req.ip, which feeds rate limiting and security logging. Keep the trusted-hop count aligned with the actual reverse-proxy topology.

7. Isolation boundaries¶

A shared schema requires complete policy coverage and deliberate administrative access. RLS does not replace authorization, input validation or tenant-aware foreign-key checks. Every tenant-owned model, including child tables and non-PII data, needs the appropriate policy. Cross-tenant operations require the documented privileged client boundary; routine tenant code must remain under RLS.