Scene 01 — Case · Client work · In production

Limon Akademi

Multi-school learning platform — school administration, course content and student project submission in one application

Many schools share one application, one database and one set of tables. Not one of the 142 endpoints is responsible for keeping them apart.

A multi-tenant system has exactly one rule it cannot afford to break: a school sees its own rows and nobody else’s. The usual home for that rule is the application — a filter on every query, a check in every handler, a review to keep it that way. Here the rule lives where the rows live, and the endpoints are left with nothing to forget.

PostgreSQL RLS
Multi-tenant
NestJS
Kysely
Argon2id
TOTP
pgTAP

Kind: Client work, in production
Surface: 142 REST endpoints across 28 tables
Roles: Four — super admin, school principal, teacher, student
Isolation: PostgreSQL Row-Level Security, per-transaction context
Tests: ~420 automated, across four layers

Figure 1 — the path of a request. Stations one to four are ordinary application work: authenticate, resolve the session, establish the role, issue the query. Station five is not a step the code takes, it is a line the code cannot cross — every statement in the transaction is filtered against the school context before any row is returned. The three row streams share the same tables; the only one that reaches the far side is the one this transaction has context for. Schematic, generated deterministically at build time; not a capture, and no data from the system appears in it.

Scene 02 — Problem

One rule, and 142 places to keep it

Every school on the platform shares the same tables. A row belonging to one school and a row belonging to another sit next to each other, in the same table, under the same index. Nothing in the storage layer separates them. The separation has to be applied when the rows are read — and the whole question is by whom.

In the ordinary design the answer is application code. Each query carries a filter for the current school; each handler checks it. That design is correct on the day it is written and stays correct for exactly as long as everyone adding the next endpoint remembers. With 142 endpoints over 28 tables, and more arriving with every feature, that is a great deal of remembering — and remembering is not an architectural property.

The failure mode is what makes it serious. A missing filter does not throw. It returns a larger result set: right shape, wrong rows. It looks like a working feature, and it keeps looking like one until somebody notices whose data they are reading.

So the problem was never how to write the filter carefully. It was where to put the rule so that writing it carefully stops being the thing the guarantee rests on.

What is not here

client work

This page describes scale and architecture, and stops there. The customer is not named. There is no screenshot, no schema, no table or column name, and no data from the running system.

Figures are schematic.

Every diagram below is generated at build time from a fixed seed, and row contents are drawn as neutral bars rather than invented values. Work I do for a client is describable without being exhibited.

Figure 2 — the shape of the problem. Tenancy is not a physical division. It is one attribute on a row, sitting in a table that every school shares, and the colour chips here stand in for it. Any read that does not account for that attribute is a read across all of them. Schematic, generated at build time; the row contents are deliberately blank.

Scene 03 — Approach

Put the boundary under the query, not around it

Every request runs inside a single transaction, and the first thing that transaction does is establish which school it belongs to. From then on every statement it issues — read or write, from any of the 142 endpoints — is filtered by Row-Level Security policies against that context. The endpoint adds no filter, because the filter is not the endpoint’s to add.

The important part is the default. If the context is not set, the policies do not fall back to permissive: they match nothing. A query with no school context returns zero rows. Forgetting to set the context therefore cannot leak the platform — it produces an empty page, which is the kind of defect that surfaces in the first minute rather than in the first breach.

The one surface that is supposed to see across schools — the super admin — does not get there by passing a different value into the same policies. It runs under a separate database role. Permission to cross the boundary is a property of the connection, not an argument to a function, and there is no value an ordinary request can carry that turns it into the other kind.

None of this is clever, and that is the point. It is one decision, taken once, that every endpoint written afterwards inherits without knowing it exists.

The path of a request

six steps

Request — authenticated call to one of 142 endpoints
Session — an opaque token resolved to an identity
Role — one of four
Query — issued inside one transaction, context already set
RLS policy — the boundary, in the database
Rows — this school’s, and no others

Steps one to four are written by hand and can be got wrong. Step five is not written per request at all, which is why step six holds.

BEGIN
  set the school context for this transaction   ← once, here
  --  every statement below is filtered by policy,
  --  whichever endpoint issued it
  SELECT  …
  INSERT  …
COMMIT

The shape of a request, not the project’s code. Statement text and setting names are deliberately replaced with prose: this is the mechanism, not a listing from the repository. What matters is the ordering — the context is established once, at the top, and everything after it is already inside the boundary.

Why a transaction

A context set on the connection would outlive the request that set it. Scoping it to a single transaction ties it to the unit of work instead: it is established and discarded with the statements it governs, and there is no window in which a connection carries one school’s context into another school’s request.

It also gives the rule one obvious place to be true. Anything that runs outside a transaction with context is not partially isolated — it sees nothing at all.

School context · SET

the same query, on a table under policy

n rows — this school’s, and no others.

School context · NOT SET

the same query, on a table under policy

0 rows — not an error, not a 403. There is nothing to return.

Figure 3 — the same query, twice. Same connection, same tables, same statement. The only difference is whether the transaction carries a school context. This is what makes the guarantee structural rather than procedural: the permissive case does not exist, so there is no state in which forgetting something produces more rows instead of fewer. Schematic; row contents are drawn as neutral bars because no data from the system appears on this page, and “n” is written as “n” because a real row count was never measured for it.

The four user roles and the scope each one reaches
Role	Reaches	Bounded by
Super admin	The whole platform	Runs under a separate database role
School principal	Their own school	Bounded by the context set on the transaction
Teacher	The courses they are assigned	Inside their school
Student	Their own enrolment	Inside their school

Four roles, one of which is different in kind rather than in degree. Super admin is not “school principal with a wider filter” — it is the only surface running on a separate database role, so the difference between seeing one school and seeing all of them is a difference in who is connected, not in what was passed.

Scene 04 — Obstacle

A guarantee you cannot check is not a guarantee

Moving the rule into the database is only worth something if you can show it holds. The application-layer version cannot be shown to hold. It can only be reviewed, endpoint by endpoint, and then reviewed again next month.

That is the real asymmetry between the two designs, and it has nothing to do with performance or elegance. With the rule in application code, “no endpoint leaks” is a claim about 142 separate pieces of code, and every endpoint added afterwards reopens it. With the rule in the database it is a claim about one boundary, in one place, that the endpoints cannot reach past — and a claim in one place is a claim you can test.

The stack reflects that. pgTAP is a test framework that runs inside PostgreSQL, which is where a policy test belongs: the guarantee is checked in the same engine that enforces it, not one layer above it and hoping the layers agree. Around it sit roughly 420 automated tests across four layers.

Figure 4 — 142 things to keep right, or one. The cells are drawn one per endpoint, not as an illustration of “many”. Schematic, generated at build time.

Scene 05 — Result

What shipped

The platform runs in production for multiple schools. What it does for them is theirs; what it is made of is below.

142

REST endpoints. None of them carries the isolation rule.

Tables, shared by every school on the platform.

User roles — super admin, school principal, teacher, student.

~420

Automated tests, across four layers.

Sessions

who is asking

Sessions are opaque tokens: the token carries no claims of its own, so nothing about a session can be read out of it or asserted by holding it — it is a handle the server resolves. Passwords are hashed with Argon2id, a second factor is available as TOTP, and refresh rotates the token rather than extending it.

The identity established here is what the transaction turns into a school context. It is the only input the boundary takes.

Untrusted content

what students submit

Student project submission means untrusted markup and untrusted images arrive by design, not by accident. Both are sanitised, and both sanitisers are dependency-free — the code that decides what untrusted content is allowed to be has no third-party surface of its own to inherit a problem from.

The same instinct as the boundary: the fewer places a decision can be made, the fewer places it can be made wrongly.

Operations

how it runs

The whole system is a Docker Compose deployment behind Caddy, which obtains and renews its certificates automatically. Inside PostgreSQL, pgaudit provides audit logging in the server itself and pg_cron runs scheduled work there too.

Fewer moving parts to operate is the same argument as fewer places to enforce a rule, applied to the machine instead of the code.

Stack

as deployed

Client: React 18 + Vite
Server: NestJS 10 + Kysely
Database: PostgreSQL 18 — RLS, pgTAP, pgaudit, pg_cron
Edge: Caddy, with automatic Let’s Encrypt certificates
Runtime: Docker Compose

On the query builder

Kysely is a query builder, not an ORM: queries are written as SQL and checked by the type system rather than assembled behind one. On a system whose central guarantee is a database feature, that matters — the thing that reaches the boundary is legible SQL, and there is no generated layer in between whose behaviour has to be reasoned about separately.

Everything on this page is architecture rather than product for a reason. The product belongs to the client. The decisions are what I can show.

Scene 06 — Close

What this case is evidence of

The safest place for a rule is the layer that cannot be skipped.Ali Batuhan Özsürmeli

The technical content is Row-Level Security and transaction-scoped context. The transferable part is older and smaller than that: when a rule has to hold everywhere, put it somewhere it cannot be routed around, and let the rest of the code stop being responsible for it.

Every endpoint written after that decision inherits the guarantee without doing anything to earn it. That is what makes it an architecture decision rather than a coding standard — a coding standard has to be upheld, and this one cannot be violated by writing ordinary code carelessly.

It is also the version of this work I can publish. Client work, described the way a client is comfortable with: how it is built, not what is inside it.

Techniques

applied

PostgreSQL RLS
Multi-tenant architecture
NestJS 10
Kysely
React 18
Argon2id
TOTP MFA
pgTAP
Caddy
Docker Compose

Status: In production
Scope: 142 endpoints · 28 tables · 4 roles
Verification: ~420 tests across four layers