Skip to content

Multitenancy

1. Overview

SIS uses a single shared schema with a tenant_id UUID column on every tenant-scoped table. Tenant isolation is enforced in two coexisting layers:

  1. PostgreSQL Row-Level Security (Wave-4, landed 2026-07-02) — FORCE RLS policies on the 8 high-value PII tables make cross-tenant access fail structurally at the database, including $queryRaw, independent of service discipline (§4).
  2. Service-layer tenantId filtering — every service method still filters by tenantId in its Prisma where clause (defense-in-depth, and the only layer for tables outside the RLS set).

2. Current Status

Current status: RLS is ACTIVE on the 8 PII tables (students, referents, guardians, teachers, staff, files, attendance_records, student_curriculum_selections) with the app connecting as the non-superuser app_user role (APP_DATABASE_URL). The tenant GUC is delivered by one request-scoped interactive transaction (§4). On top of that, per-service where: { tenantId } and the base CRUD getScopedWhere weld (§4.2) remain as app-layer defense-in-depth. The Wave-2a observe guard was deleted — RLS supersedes it (its STRICTLY_TENANTED_MODELS registry survives at src/prisma/tenanted-models.ts as the drift oracle and the worklist for extending RLS).

3. Tenant Resolution

Tenant identity is established during login using a password-first two-step approach that eliminates Host header dependency.

Step 1 — Credential verification:

POST /api/v1/auth/login { email, password }
  1. Find all active User records matching email across all active tenants
  2. Verify password against each with argon2.verify (in parallel)
  3. Collect matches — user + tenant pairs where the password is valid
Matches Response
0 401 — "Invalid credentials" (generic, no enumeration)
1 Normal login: cookies set, return { user }
2+ 200 with { requiresTenantSelection, tenants[], selectionToken }

Step 2 — Tenant selection (multi-match only):

The frontend shows a tenant picker and completes login:

POST /api/v1/auth/login/select-tenant { selectionToken, tenantId }
→ cookies set, return { user }

The selectionToken is a short-lived (10 min) JWT containing { sub: "tenant-selection", matchedUserIds: [...] }. The window covers the interactive chooser step; staleness is caught by a fresh availability re-check on consume, not by a tight TTL. The tenant list is only returned after password verification — it does not leak email-to-tenant mappings.

After authentication, tenantId is embedded in the signed JWT. All subsequent requests use the JWT payload, not the Host header. Tenant status (ACTIVE/TRIAL) is validated at login and re-validated on every token refresh.

Full auth details: chapter 03

4. RLS Architecture (ACTIVE — full coverage)

Landed 2026-07-02 in two steps: the 8 PII tables (migration 20260702082621_enable_rls_high_value_pii) and then every remaining tenant-bearing table (migration 20260702130000_extend_rls_full_coverage) — 58 of 66 tables carry ENABLE + FORCE row security and a tenant_isolation_<table> policy.

The single source of truth is src/prisma/rls-coverage.ts — it classifies all 66 models into four classes, the migration was generated from it, 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.

Class Tables Predicate (USING + WITH CHECK)
S — simple 31 (required tenant_id) tenant_id = (SELECT NULLIF(current_setting('app.current_tenant_id', true), ''))::uuid
M — mixed catalogs 4 (roles, room_types, subject_levels, evaluation_scales — nullable tenant_id, platform presets ∪ 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 23 (no tenant_id; e.g. refresh_tokens, grade_criterion_marks, homeroom_assignments, the curriculum grid children) 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 8 (tenants, the 5 RBAC catalog tables, curriculum_preset, processed_webhook_events) 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:

  1. 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).
  2. 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 tx is handed over via CLS, not an injected TransactionHost: the forwardRef circular injection into PrismaService resolves a second, unwired TransactionHost whose isTransactionActive() is always false — the library route silently misroutes every query to the GUC-less base connection (found when RLS flipped on; this is the spec's Q1 fallback made primary).
  3. Single connection ⇒ serialized queries. In-request DB concurrency is deliberately serialized (Promise.all over queries → sequential await); non-DB concurrency (argon2, S3) stays parallel.
  4. Exempt routes (@NoTenantTx() — login/refresh/logout, invitation verify/accept, health, resend webhook, platform-admin preset routes) run on the base client with no GUC, so covered tables read as empty there (Class M still serves global rows). The full auth bypass surface, flow by flow:
Flow Client Why
Login step 1 credential match (users ∪ tenant/school/logo) AdminPrismaService email is the only key — inherently cross-tenant
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 (replay branches) AdminPrismaService must COMMIT even though the flow throws right after — a GUC tx would roll it back, the GUC-less RLS client would match 0 rows
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
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. 5. 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: fetchUserRoles, fetchParametricAssignments, 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 and the e2e global setup). Hosted envs: run prisma/sql/provision-app-role.sql once per database, set a strong password, and set APP_DATABASE_URL — the deploy fails at boot until this is done (see the boot guards below; per-env rollout order in chapter 10).

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

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

  1. 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.
  2. 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 (the unwired-TransactionHost incident, §4.0) — 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):

  1. Required tenantId? → Class S.
  2. Nullable tenantId (platform presets ∪ tenant rows)? → Class M.
  3. 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).
  4. Genuinely global/infra? → Class X, with a written reason.

Then add the matching CREATE POLICY in a new migration — copy the class's predicate shape from 20260702130000_extend_rls_full_coverage/migration.sql (its header documents each shape's rationale). db-constraints.e2e-spec.ts fails until the policy actually exists in the database.

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 all 58 tables, 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.

Full design rationale: Wave-4 RLS spec (mechanism + the first 8 tables) and RLS full-coverage spec (the other 50, boot guards, tripwire, bypass fence). The Wave-2a observe guard ($extends query monitor, TENANT_GUARD_MODE, runWithoutTenantGuard) was deleted when RLS landed — RLS is the enforcing layer it could never become.

4.2 Base-service tenant weld (R3a)

BaseTenantedCrudService.getScopedWhere(tenantId, ctx) returns { ...getBaseWhere(tenantId, ctx), tenantId } — tenantId spread last. The base findAll/findOne funnel reads through getScopedWhere, not getBaseWhere directly, so a subclass that overrides getBaseWhere and omits (or mis-sets) tenantId cannot drop the tenant boundary from the base read path. This is the structural complement to the guard.

5. Implementation Rules

  1. Every tenant-scoped table has tenant_id UUID NOT NULL + FK to tenants.
  2. Child tables (Department, Grade, Period) carry their own tenantId even though it is derivable via FK chain — defense-in-depth: every query path is independently tenant-safe, future RLS policies stay simple, and future CRUD APIs have a direct tenantId filter as a safety net.
  3. Services MUST filter by tenantId in every where clause — app-layer correctness first (right rows, right errors, index-friendly plans); RLS is the structural backstop underneath it on every tenant-bearing table, not a substitute for the filter.
  4. Access the tenant identity in controllers via the @TenantId() tenantId: string decorator, which extracts tenantId from req.user.tenantId (populated by JwtAuthGuard).
  5. Platform admin (is_platform_admin flag on User): bypasses tenant scope and all permission guards (ScopeGuard, ActionGuard, FieldWriteGuard, FieldFilterInterceptor).
  6. Users are per-tenant (user.tenantId). A single user can link to multiple domain tables simultaneously (e.g., both Teacher and Staff). Capabilities are defined by roles, not by which domain tables the user links to.
  7. No cross-tenant queries in application code — tenant isolation is the service layer's responsibility. Cross-tenant analytics (e.g., admin reporting) require an explicit bypass handled by an admin-only reporting path.

5.1 Named Exceptions

The "every query filters by tenantId" rule has several approved exceptions, all rooted in platform-owned rows (tenantId IS NULL). They fall into two distinct shapes:

  • 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 }] }). Unlike the read-merge catalogues, rooms FK directly to the shared global rows — globals are referenced, not cloned per tenant. 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 break the strict per-tenant assumption of rule 6 — but via 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.

Future extensions: the catalogue pattern has already been applied to evaluation scales, subject levels, room types, and role presets — each documented above. Any future addition requires an entry in this section and a named helper on its module.

6. Trust Proxy

Configured in main.ts via app.set('trust proxy', 1). This causes req.ip to return the client's real IP address when the server is behind Railway's load balancer and Cloudflare's proxy. Required for accurate rate limiting (throttler uses req.ip as the key) and for meaningful IP logging in security events.

7. Trade-offs

Three trade-offs were accepted in choosing shared-schema multitenancy:

  • Weaker isolation than schema-per-tenant — acceptable for an EdTech SaaS where tenants are schools, not competing businesses with adversarial threat models. RLS on the PII tables (§4) closes the gap at the database level.
  • Discipline required for new tables — every new table must include tenant_id; PII-bearing tables should also get an RLS policy (add to the §4 set + ch12 registry + db-constraints assertions). This is a code-review checklist item (see chapter 11).
  • Cross-tenant analytics require an RLS bypass — queries that span tenants (e.g., platform-level reporting) go through AdminPrismaService (superuser connection). Acceptable because this is handled by dedicated admin-only paths.