Enforcing Multi-Tenant Isolation Structurally, Not by Convention

Bicitra EMS is a multi-tenant education management system: one shared platform, any number of schools, each school's data supposed to be fully invisible to every other school. Tenant isolation is the one property a multi-tenant SaaS cannot get partially right — a single leaked row is a data breach, not a bug ticket.

The convention that almost worked

Every tenant-scoped table in the system carries a schoolId column. The straightforward way to enforce isolation is convention: every query that touches a tenant table includes a WHERE schoolId = ... clause, and every engineer remembers to add it.

That convention doesn't scale past the first missed line. So instead of trusting each call site, isolation is applied by a Prisma Client Extension that intercepts queries and injects the tenant scope automatically — a caller doesn't get the chance to forget the clause, because the clause isn't theirs to write.

Where the convention still had a gap

Automated testing is what actually found the problem: 225 API tests running against a real Postgres instance, as part of a 338-test suite that also covered browser and mobile flows. Tenant isolation is exactly the kind of property a mock can't validate — a mocked database can't tell you whether your real query actually stayed inside its tenant boundary.

The tests found a defect in code that had already been marked done: a nested Prisma include on a relation is evaluated as part of its parent query and never gets routed back through the extension that owns tenant scoping. A fee invoice, an attendance record, or a salary row referencing another school's student ID would pull that other school's guardian details and QR gate-scan credential straight back out — reachable through seven different endpoints, none of which looked wrong in isolation.

Fixing the class of bug, not the seven instances

Patching each of the seven endpoints individually would have left the same gap open for the eighth endpoint someone writes next month. Instead, the fix is structural: every inbound tenant-scoped foreign key is declared in one central registry and validated at write time, inside the same extension that already owns tenant scoping. A new endpoint gets the protection automatically, because the registry — not the endpoint author's memory — is what enforces it.

Why this matters beyond one bug

"We enforce tenant isolation with an ORM extension" sounds airtight until you remember that ORMs have edge cases — relation includes, raw queries, batch operations — that don't always route through the same code path as a normal query. The lesson isn't "use a query extension." It's narrower and more useful than that: whatever mechanism you use to enforce a security invariant, assume it has at least one code path that bypasses it, and go looking for that path with real tests before an attacker — or another tenant — finds it for you.

The same test pass caught two other real defects: a double-payment race condition from two simultaneous "pay now" taps, and a gate console accidentally exposing password hashes to the lowest-privilege role in the building. None of the three were found by code review. All three were found by tests that exercised real concurrency and real data, not mocks.