Skip to content

Wave 4 — Postgres RLS via a request-scoped transaction (audit #1b, iteration 2)

✅ STATUS (2026-07-02): LANDED — all 3 phases in tree, RLS ON. Phase 2 (non-superuser app_user + APP_DATABASE_URL, FORCE RLS policies on the 8 PII tables — migration 20260702082621_enable_rls_high_value_pii, isolation proof test/rls-isolation.e2e-spec.ts + db-constraints policy assertions, AdminPrismaService) and Phase 3 (Wave-2a observe guard DELETED; registry kept at src/prisma/tenanted-models.ts) are implemented; every e2e suite ran green with RLS enforced as app_user. One material mechanism change vs. this spec's primary Q1 route: the library TransactionHost injected into PrismaService via forwardRef resolves a second, UNWIRED instance (isTransactionActive() always false → every query silently misrouted to the GUC-less base connection; invisible with RLS off, fatal with it on). The Q1 hand-rolled fallback is now primary: the interceptor stores the open tx in CLS (TENANT_TX_CLS_KEY) and the PrismaService proxy reads CLS. Additional landed deltas: withTenantGuc for pre-auth single-tenant paths (login profile resolution, invitation verify/accept, guard-stack fetchTeacherDepartmentIds); AdminPrismaService for the cross-tenant login credential match; e2e fixture access moved to a superuser getAdminPrisma() helper. Current state: docs/02-multitenancy.md §4.

What this supersedes. The prior per-op RLS attempt (built 2026-06-25, reverted 2026-06-26; its tombstone spec 2026-06-25-wave4-rls-backstop was removed 2026-06-30 with its facts folded here) proved that RLS policies + a non-superuser role deliver hard isolation, but its GUC-delivery mechanism (a per-op $transaction wrapper) was implemented, found non-viable, and reverted — wrapping every standalone op in its own transaction collides with the app's concurrent (Promise.all) and batch ($transaction([findMany, count])) query patterns. This iteration keeps everything that worked and replaces only the delivery mechanism with option A — one request-scoped interactive transaction per authenticated request (GUC set once, all the request's queries on that pinned connection), and deliberately gives up in-request DB concurrency (serialize) rather than preserve it. Inputs: the updated data-layer guide (2026-06-26), the 55-site Promise.all concurrency audit (this chat), and the prior spec's proven constraints.

Carried forward as proven (from the reverted per-op attempt), not re-litigated: RLS policies on the 8 PII tables work; NULLIF(current_setting(…), '')::uuid is required (a tx-local set_config reverts a custom GUC to '' on a pooled connection, and bare ''::uuid errors); the sis_app non-superuser role works (full app ran 61/61 e2e as it with RLS off → grants complete); cross-tenant isolation is provable via $queryRaw + WITH CHECK. The open problem was only delivery — that is what this spec resolves.

1. Problem distillation

  • The cross-school boundary is a hand-repeated where: { tenantId } with no structural backstop (audit #1). Wave 2a added an app-layer $extends observe guard that only logs unsafe queries and cannot see $queryRaw. RLS is the only DB-level layer that makes the unsafe query fail and also covers raw SQL + survives an ORM swap.
  • RLS needs the tenant GUC (app.current_tenant_id) set on the same connection each query uses. With Prisma 7 + @prisma/adapter-pg there is no per-request connection hook (prior §13), and set_config(…, true) is transaction-scoped, so the only reliable delivery is: run all of a request's queries inside one interactive transaction and set the GUC once at the top.
  • Doing that means a request's queries share one connection → they must run serially. This codebase leans on in-request concurrency (55 Promise.all DB sites), so the cost of this mechanism is losing in-request DB parallelism. The concurrency audit established that none of it is structurally required and the latency cost is bounded/invisible at K-12 scale — so we accept the loss rather than engineer around it.
  • The guide is written greenfield (repository layer, no tenantId/tx params, fan-out-via-separate-transactions). This spec adopts its mechanism (@nestjs-cls/transactional + GUC + RLS + roles) and rejects its architecture mandates, mapping the mechanism onto the existing PrismaService-injected, tenantId-threaded service layer via a transparent client proxy.

Success criteria (observable behavior that proves this works): - FORCE ROW LEVEL SECURITY on the 8 PII tables; with the GUC set to tenant A, a query (incl. $queryRaw) cannot read/write tenant B's rows even if the app-layer where omits tenantId; an INSERT/UPDATE with a mismatched tenantId is rejected by WITH CHECK. - Every tenant-scoped request opens one interactive transaction, sets app.current_tenant_id exactly once, and runs all its DB work on that connection. - The full existing e2e suite passes with RLS enforced and the app connecting as the non-superuser app_user role — i.e. the serialized + ambient-tx mechanism survives the concurrent (Promise.all) and batch ($transaction([…])) paths that broke the per-op attempt. - Pre-auth / no-tenant / global paths (login, refresh, health, platform-admin/backoffice, setup tenant-create) keep working — via the base/admin client outside the tenant transaction, not by accident. - The Wave-2a observe guard is deleted; getScopedWhere (R3a) + per-service where: { tenantId } stay as cheap defense-in-depth.

Non-goals (in-scope-shaped things this iteration is explicitly not doing): - RLS on platform-global tables (tenantId IS NULL: catalog, room-type/role/scale/level presets) or on users (login is pre-tenant). Deferred to a follow-up. - Removing the app-layer where: { tenantId } filters or tenantId service-method params (defense-in-depth; see §7). - The guide's repository layer and "no schoolId/tx parameters" mandates — explicitly rejected (§7/§8). - Preserving in-request DB parallelism — deliberately surrendered (§7); the guide's separate-Tx.run-per-read + p-limit fan-out model is rejected. - Per-tenant database/schema isolation (a different architecture).

2. Patterns survey

Analogous module/spec What we'd borrow What doesn't fit
The prior per-op RLS attempt (reverted 2026-06-26; tombstone spec removed 2026-06-30 — facts in the §0 status banner) The entire DB-side, proven: 8-table policy set, FORCE, NULLIF(current_setting('app.current_tenant_id', true), '')::uuid, the sis_app non-superuser role + APP_DATABASE_URL, the cross-tenant isolation e2e, the db-constraints policy assertion. Only the delivery mechanism (per-op $transaction wrapper) — replaced by the request-scoped tx here.
src/prisma/tenant-guard/* + src/prisma/prisma.service.ts (Wave-2a Proxy) STRICTLY_TENANTED_MODELS (drift-guarded registry) defines the protected-table universe; the PrismaService Proxy is the exact precedent for transparently substituting the client every this.prisma.* call resolves to (here: substitute the ambient tx). The guard is observe-only/app-layer and is deleted by this spec; the Proxy is extended, not the guard.
docs/superpowers/specs/2026-06-26-transaction-hygiene-remediation-design.md The same $transaction sites it audits (~54) are the ones that must join the request tx (propagation) instead of nesting; its tx-collapse reduces the count first. That spec lifts side-effects out of txs; this one folds reads/writes into one request tx — complementary, must be sequenced (§10-Q6).
docs/02-multitenancy.md §4 (RLS SQL shape) + docs/12-migrations.md + Wave-2b test/db-constraints.e2e-spec.ts The canonical policy SQL; the raw-SQL-constraint registry row + positive existence assertion idiom (extended to pg_policies / relforcerowsecurity). Doc shows the policy, not GUC delivery under pooling; db-constraints covered uniques/CHECKs — we add a policy assertion.
app.module.ts:51 (ClsModule.forRoot({ middleware: { mount: true } })) + jwt.strategy.ts:38 (cls.set('tenantId', …)) The CLS context is already mounted per request and already carries tenantId — the GUC reads from it; no call-site threading. Base nestjs-cls only; @nestjs-cls/transactional + the Prisma adapter are the new deps.
src/common/utils/import-code-generator.ts (pg_advisory_xact_lock via $executeRaw inside a tx) Precedent that we already issue parameterized raw SQL inside a tx for a cross-cutting effect — the exact shape of the set_config call. Advisory lock is per-call; the GUC must blanket every query in the request.
The 55-site Promise.all concurrency audit (this chat) The serialization work-list: ~5 patterns, dominated by base-tenanted-crud.service.ts:~544 (Promise.all([findMany, count, getDefinitionsByScope])) which covers most list traffic; 3 NON-DB sites (argon2/S3) stay parallel. n/a — it is the authoritative blast-radius input.

3. Architecture mapping

Primitive Apply? How Justify
Tenant scope yes — this IS the structural enforcement RLS policy USING/WITH CHECK (tenant_id = (SELECT NULLIF(current_setting('app.current_tenant_id', true), ''))::uuid) on each of the 8 PII tables; GUC set once per request from cls.get('tenantId'). The whole point of the wave.
Academic-year scope no n/a — RLS keys on tenant only. AY isolation stays app-layer.
RBAC entity key reuse / none Operates on tables, not entities. RLS is orthogonal to RBAC.
Scopes none n/a.
Actions none n/a.
Service base touch (central) PrismaService Proxy is extended so this.prisma.* resolves to the request's ambient tx (txHost.tx) when one is active, else the base client; a TenantTransactionInterceptor opens the request tx + sets the GUC for tenant-scoped routes; the Wave-2a guard subsystem is deleted (registry + R3a kept). base-tenanted-crud.service.ts findAll/import-preview Promise.all sites are serialized. Single chokepoint; reuses the existing Proxy slot; no per-call-site rewrite.
queries.ts shape touch (serialize) The bespoke *.queries.ts and service fan-outs in the audit's MUST-SERIALIZE list switch Promise.all([dbA, dbB]) → sequential await. Single-connection request tx.
Error codes reuse Repurpose the existing TENANT_GUARD_VIOLATION enum value as the "tenant tx expected but no CLS tenant" internal signal (the guard is gone, the greppable code is a ready signal). No new code. Avoid silent-empty confusion; no catalogue churn.
DTO conventions n/a No request/response shape changes. Below the controller layer.
File-backed sub-resources n/a (indirect) files is a protected table; S3 presign/put/delete stay outside the tx (NON-DB; aligns with transaction-hygiene). Keep the tx short; don't hold a connection across S3 I/O.
Custom fields yes (indirect) The JSONB custom-field $executeRaw is a raw site RLS now protects; customFieldDefinition reads serialize where fanned out. Raw SQL coverage is RLS's headline win.
Profile completeness no n/a.

New (off-axis) elements: the request-scoped tx boundary (@nestjs-cls/transactional + TenantTransactionInterceptor); the PrismaService proxy-to-ambient-tx; the RLS policy migration (raw SQL → ch12 registry + db-constraints assertion); the non-superuser app_user runtime role + a separate app_admin/superuser client for migrate/seed/jobs/cross-tenant; the serialization of 55 Promise.all sites; deletion of the Wave-2a guard.

4. Data model plan

Schema deltas

  • No Prisma model change. RLS is raw SQL Prisma can't express. (All 8 tables already carry a denormalized tenant_id + index — verified in the Wave-2a registry.)

Migration shape

  • Additive (DDL), raw SQL. Per protected table <t> ∈ {students, referents, guardians, teachers, staff, files, attendance_records, student_curriculum_selections}: 
    ALTER TABLE "<t>" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "<t>" FORCE  ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_<t> ON "<t>"
  USING      (tenant_id = (SELECT NULLIF(current_setting('app.current_tenant_id', true), ''))::uuid)
  WITH CHECK (tenant_id = (SELECT NULLIF(current_setting('app.current_tenant_id', true), ''))::uuid);
  • Scalar-subquery wrap is mandatory (evaluated once as InitPlan, not per row).
  • NULLIF is mandatory (proven in prior §13 — ''::uuid errors on a reused pooled connection).
  • Flat equality only — no subqueries/joins/EXISTS in the policy.
  • Hazards (ch12 + RLS-specific):
  • Owner bypass → FORCE on every table, so even a future non-superuser owner is covered; migrate/seed run on the superuser/app_admin (auto/explicit bypass).
  • ENABLE RLS takes a brief ACCESS EXCLUSIVE lock — fine at these sizes; staged.
  • Fail-closed blast radius → strict deploy ordering (see below). Enabling RLS before the mechanism ships breaks every read.
  • Rollback: DROP POLICY tenant_isolation_<t> ON "<t>"; ALTER TABLE "<t>" DISABLE ROW LEVEL SECURITY; per table (kept for the runbook).
  • Deploy ordering (three steps, do not collapse): (1) ship + verify the request-tx mechanism and the serialization changes (app still on the superuser/bypass connection — no behavior change, just plumbing); (2) ship the RLS migration and flip DATABASE_URL to the app_user role; (3) delete the Wave-2a guard. Each step is independently reversible.

Indexes and uniqueness

  • All 8 tables already have a tenant_id index; confirm during impl no policy forces a seq-scan on a hot path (EXPLAIN, acceptance check 5).
  • ch12 registry: one row per tenant_isolation_<t> policy; db-constraints.e2e-spec.ts asserts each policy exists in pg_policies and relforcerowsecurity = true on each table.

5. API surface

n/a — no HTTP endpoint change. The new behavior is a cross-cutting TenantTransactionInterceptor (opens the request tx + sets the GUC) below the controller layer; no new routes, DTOs, or response shapes.

Swagger considerations

  • n/a.

6. RBAC seed plan

Seed file Delta
All (rbac-catalogue.ts, roles.ts, scope-fields.ts) none — RLS is orthogonal to the RBAC catalogue.

Infra, not seed-content: the runtime connection uses the non-superuser app_user role (APP_DATABASE_URL); migrate/seed/jobs/cross-tenant use the superuser/app_admin connection. Provision app_user + the URL per env (local Docker, e2e setup, CI, Railway). (Role provisioning SQL + the sis_app precedent are proven — prior §13.)

7. Divergence ledger

Pattern We diverge by Reason Tradeoff accepted
Queries run outside an explicit $transaction (auto-commit) One request-scoped interactive tx per tenant route; GUC set once; PrismaService proxied so this.prisma.* resolves to the ambient tx set_config(…, true) is tx-scoped; pooling defeats session SET; adapter has no per-request connection hook (prior §13) A pooled connection is held for the whole request; longer-lived txs; reads now run inside a tx. Mitigated by keeping non-DB work out of the tx + idle_in_transaction_session_timeout + pool sizing.
In-request DB concurrency (Promise.all over queries) Serialized — 55 sites → sequential await (NON-DB Promise.all stays parallel) A single request connection executes one query at a time Bounded per-request latency (audited: tens of ms on admin/dashboard endpoints, invisible at K-12 scale).
Guide: independent reads stay parallel via separate Tx.run + p-limit Rejected — single request tx, serialize instead The fan-out model reintroduces multiple connections per request (the pool amplification we want to kill) + p-limit tuning, for latency we don't need Give up parallel reads; gain 1-connection-per-request, read-your-writes, simplicity.
Guide: repository layer; never inject Prisma outside repos Rejected — keep this.prisma in services; transparent Proxy to the ambient tx No repo layer exists; ~30 modules + BaseTenantedCrudService are built on this.prisma; the rule buys zero isolation (RLS is the boundary regardless) Service-level Prisma access stays; isolation comes from RLS, not from layering.
Guide: schoolId/tx never parameters Rejected — keep tenantId (and tx-typed helper) params tenantId is threaded through every signature, feeds defense-in-depth where, and is needed by global/admin paths Slightly redundant filters (intended defense-in-depth).
~54 this.prisma.$transaction(...) write blocks Migrate to ambient-tx propagation (join the request tx via Propagation.Required) rather than nest Nested interactive tx is forbidden; a fresh $transaction opens a 2nd connection with no GUC → RLS denies Touch those sites — coordinated with transaction-hygiene so it's done once (§10-Q6).
Wave-2a $extends observe guard is the current backstop Deleted — RLS supersedes it The guard can never flip to enforce on its own (Wave-2a §14); RLS is the real enforcing layer Keep the STRICTLY_TENANTED_MODELS registry + R3a weld; drop the guard extension.

8. Pushback log

Guide/source says Conflicts with Proposed instead Status
"NEVER inject PrismaService/PrismaClient outside repositories; only repositories touch txHost.tx" No repository layer exists; the whole service layer + base CRUD use this.prisma Transparent PrismaService Proxy resolving to the ambient txHost.tx; services unchanged Resolved (§7)
"schoolId and tx are NEVER method parameters in business logic" tenantId threaded through every method; load-bearing for defense-in-depth + global/admin Keep params; RLS is the boundary, params are belt-and-suspenders Resolved (§7)
"Independent reads → separate Tx.run + Promise.all those + p-limit" Reintroduces multi-connection-per-request pool amplification + tuning we're trying to eliminate One request tx; serialize independent reads Resolved (§7; concurrency audit)
Policy as current_setting('…', true)::uuid (no NULLIF) Errors invalid input syntax for type uuid: "" on a reused pooled connection (proven prior §13) NULLIF(current_setting('…', true), '')::uuid Resolved (§4)
TenantContextInterceptor throws UnauthorizedException whenever req.user.schoolId is absent Breaks login/refresh/health/platform-admin/backoffice/setup (legitimately tenant-less) Selective interceptor — only tenant-scoped routes open the tx + set the GUC; tenant-less routes run on the base/admin client outside it; read tenant from existing CLS, not req.user Resolved (§9)
Guide names tables "Student" / schoolId Repo uses snake_case tables + tenant_id / tenantId Adapt names; key on tenant_id Resolved (cosmetic)

9. Deferrals

  • RLS beyond the 8 PII tables — deferred. The remaining STRICTLY_TENANTED_MODELS stay app-layer-guarded; extending RLS to them (and giving the auth path a bypass so users can join) is a follow-up — follow-up: future spec.
  • Platform-admin / backoffice cross-tenant reads — they run on the app_admin/superuser client (or set the GUC per target tenant) outside the request tx — follow-up only when a real cross-tenant surface appears.
  • Propagation.Nested (SAVEPOINTs) — adopt only where a sub-unit genuinely needs independent rollback; default is Required (join). Most of the ~54 sites just join the request tx — revisit per-site during impl.
  • Background jobs / afterCommitQueue / cron — these have no HTTP CLS context. In scope: audit existing post-commit consumers (today only invitation mail) and any cron; each must either establish CLS + tenant + the tx manually or use the app_admin client. Wiring a generic job-context helper is a follow-up if the surface grows.
  • Removing the app-layer where: { tenantId } — never (defense-in-depth) — not a deferral, a permanent non-goal.
  • The selective-interceptor exemption list — the v1 exemptions (login/refresh/health/platform-admin/setup-tenant-create) are enumerated during impl from the existing public/@PlatformAdminOnly route set; a missed exemption fails closed (clear error, not a leak), so this is low-risk to iterate.

10. Open questions

Resolved decisions (repo convention: [x] with the resolution inline). The one residual implementation risk is called out under Q1 with its fallback — it is a risk accepted at sign-off (no spike, per user), not an unresolved blocker.

  • Q1 — Delivery mechanism + the transparent-proxy risk. Primary: @nestjs-cls/transactional + TransactionalAdapterPrisma, with PrismaService proxied so this.prisma.* resolves to txHost.tx when a request tx is active. Residual risk (accepted): the library expects call sites to use txHost.tx; making the existing this.prisma.* calls route there transparently requires the Proxy to reach TransactionHost (possible lazy/circular-DI friction with our existing Wave-2a Proxy). Mitigation/fallback (no spike): if the transparent proxy fights the library, fall back to a hand-rolled request tx — interceptor opens prisma.$transaction, stores tx in CLS, the existing Proxy (which already reads CLS) resolves model access to it, and PrismaService.$transaction is overridden to join an active ambient tx instead of nesting. Both routes are documented; the fallback uses only primitives already in the tree. The first impl task is to stand up the mechanism on one read + one write path and run those modules' e2e before touching the other 53 sites — fail fast on this risk inside the build, not in prod.
  • Q2 — Tx boundary granularity. One request-scoped tx opened in a TenantTransactionInterceptor wrapping the handler (GUC set once). Rejected the per-service-method boundary (more call sites, marginal tx-length benefit) and the guide's per-operation boundary (the reverted dead-end).
  • Q3 — Concurrency. Serialize (one request tx). The 55 Promise.all DB sites → sequential; NON-DB Promise.all (argon2 auth.service.ts:109, S3 presign/delete) stays parallel. Per the concurrency audit; fan-out-via-separate-tx rejected (§7).
  • Q4 — Table scope. 8 PII tables (carried from prior §10-Q3). users excluded (pre-auth login lookup) and global tenantId IS NULL tables excluded (flat-equality RLS would hide them). Both are documented follow-ups.
  • Q5 — DB roles. Runtime = non-superuser app_user (no BYPASSRLS); migrate/seed/jobs/cross-tenant = superuser/app_admin (bypass). Proven (prior §13: sis_app ran the full app).
  • Q6 — Ordering vs. transaction-hygiene. Run transaction-hygiene first (it collapses ~11 needless txs + lifts S3/argon2 out → fewer, cleaner tx sites), then convert the remaining $transaction blocks to ambient-tx propagation. Avoids touching the same sites twice and keeps non-DB work out of the request tx (which the held-connection cost requires anyway).
  • Q7 — Policy GUC name + NULLIF. app.current_tenant_id, NULLIF(current_setting(…, true), '')::uuid, scalar-subquery wrapped, FORCE. Proven (prior §13).

No unresolved blockers. Sign-off is the go-ahead given the Q1 risk is accepted as a build-time fail-fast (no spike).

11. Verification plan

  • Unit specs:
  • tenant-transaction.interceptor.spec.ts — asserts: a tenant route opens the tx and issues set_config('app.current_tenant_id', <cls tenant>, true) as the first statement exactly once; a route on the exemption list does not open the tx; a tenant route with no CLS tenant surfaces the TENANT_GUARD_VIOLATION signal (not a silent empty).
  • prisma.service.proxy.spec.ts — this.prisma.<model> resolves to the ambient tx when active, to the base client otherwise; $transaction joins an active ambient tx rather than nesting.
  • The rewritten serialized sites are behavior-covered by their existing service specs (base CRUD findAll, students/teachers/rooms/command-center, etc.) — assert no regression (same returned shapes), which is the point of serializing rather than restructuring.
  • E2E specs (the validation that replaces a spike):
  • Run the full existing e2e suite with RLS FORCE on the 8 tables and the app connected as app_user. Green proves the request-tx + serialization mechanism survives the concurrent (Promise.all) + batch ($transaction([…])) + long-interactive (bulkSync/attendance/rollover) paths that broke the per-op attempt. This is the headline gate.
  • test/rls-isolation.e2e-spec.ts (recovered from prior attempt) — GUC=A: $queryRaw SELECT * FROM students returns only A; a known B-id returns nothing; updateMany without tenantId in where cannot touch B; INSERT/UPDATE with mismatched tenantId rejected by WITH CHECK.
  • Extend test/db-constraints.e2e-spec.ts — each tenant_isolation_<t> policy exists (pg_policies) and relforcerowsecurity = true on each of the 8 tables.
  • A no-tenant path (login/refresh/health) still works as app_user/app_admin outside the request tx.
  • Manual verification: with RLS on and the GUC unset, confirm a tenanted read fails closed (clear error / empty) rather than leaking; EXPLAIN a representative list query shows an index scan on tenant_id and the setting as a single InitPlan (acceptance check 5).

Patterns: chapter 02 (multitenancy), chapter 12 (migrations), chapter 09 (testing), feedback_e2e_isolation_patterns.md, the Wave-2a guard spec, prior Wave-4 §13.

12. Sign-off

  • Approved by: Fabio Barbieri
  • Date: 2026-06-26
  • Chat reference: approved by Fabio in chat 2026-06-26 after walkthrough — supersedes the per-op mechanism (reverted, prior §13) with a request-scoped tx (option A) + deliberate serialization; mechanism via @nestjs-cls/transactional + transparent PrismaService proxy (hand-rolled fallback documented); 8-table scope; app_user/app_admin role split; guide's repository-layer + no-tenantId-param mandates rejected. Q1's transparent-proxy risk accepted as a build-time fail-fast (no spike). Q6 prerequisite met: transaction-hygiene remediation is implemented + committed (2ca345a), so the $transaction-migration tasks run against the post-hygiene (collapsed, side-effect-free) tx sites; RLS is no longer blocked behind it.

Approved — implementation may proceed per the plan (docs/superpowers/plans/2026-06-26-wave4-rls-request-scoped-tx.md).