We give each school its own database rather than a shared table keyed by a tenant column, because isolation should be guaranteed by the datastore, not left to the discipline of remembering a filter on every query.
The decision that shapes everything else
When you build software that serves many organisations from one codebase, one decision quietly shapes everything that follows: where does one customer's data end and the next one's begin? For a school ERP the stakes are unusually high. The data is children's records, family finances and examination results. Get the boundary wrong and you do not just have a bug; you have one school looking at another school's students.
There are two broad answers. The first is a shared schema: one set of tables, with a column on every row naming the school it belongs to. The second is a database per tenant, where each school gets its own physically separate database. The shared model is cheaper and simpler to start with, and it is the default the industry reaches for. In building multi-tenant SaaS architecture for schools, we deliberately did not take it.
The shared-table trap
A tenant column looks harmless. The problem is that it turns isolation into a discipline rather than a guarantee. Every query, every join, every report and every background job must remember to filter by school. Miss it once, in a hand-written report, a new developer's first feature or a cache key that forgets the school, and the failure is not a crash you will notice. It is one school silently seeing, or worse editing, another's data. The safety of the whole system comes to rest on a rule that has to hold across every query anyone will ever write.
The operational costs compound from there. Backups become all-or-nothing: you cannot hand one school its own data, or restore one school to yesterday, without touching everyone. A heavy month-end run at one large school competes for the same tables as everyone else, the classic noisy-neighbour problem. And a schema change locks a table that all schools share, so routine maintenance becomes a whole-estate event. The safest cross-tenant query is the one that is impossible to write.
Why we chose a database per tenant
So we gave each school its own PostgreSQL database, provisioned automatically the moment the school is created. Alongside them sits a separate control-plane database that knows only about schools themselves: the registry of tenants, their plans, which modules each has switched on, and provisioning history. A school's students, staff, fees, marks and messages live only in that school's own database. Isolation is enforced by the datastore, not by a condition you hope everyone remembered.
That single change turns a list of risks into a list of properties. Tenants are separated at the strongest level the database offers. Each school can be backed up, snapshotted before a risky change, restored, or exported entirely on its own. One school's load lives on its own database and does not drag down the rest. A school can be suspended or resumed without affecting a neighbour. And the blast radius of almost any failure shrinks from the whole platform to one school.
The two tiers do different jobs, and keeping them separate is the point. The control plane is the operator's map of the estate: which schools exist, what they are entitled to, how they are branded. It never holds a school's operational data. Each tenant database is a school's entire world and knows nothing of any other. Provisioning a new school spins up a fresh database, seeds its roles, and applies its plan and branding, after which that school is a sealed environment.
The cost we took on: migrations at N
None of this is free, and it would be dishonest to pretend otherwise. The price of a database per tenant is that a schema change is no longer one migration against one database. It is the same change applied across every school's database, and every newly provisioned school has to come up already at the latest shape. That is genuinely more machinery than a single alteration against a shared schema.
We treat it as a solved, owned problem rather than a recurring risk. The tenant schema is a single source of truth; provisioning applies it to new databases so they are born current, and a controlled rollout applies changes across existing ones. The work is real, but it lives in our tooling, once, not in every school's exposure to a forgotten filter, every day. We would rather pay the migration tax in our own tooling than make every school share the risk of a query that forgot which school it was for.
What it means if you are the school
Strip away the engineering and the promise is simple: your data is physically yours. It sits in its own database. It can be snapshotted before a big change and restored on its own. It can be handed to you when you ask. And it is never one bug away from mingling with another school's records.
Isolation is not a clause in a contract or a claim on a slide. It is the shape of the system. For a platform trusted with children's records and a school's finances, that felt like the boundary worth building the hard way.