Multi-tenant SaaS on Postgres: lessons from a school ERP

Our School Management ERP serves many schools from one database. Every school is a tenant, and the most important promise the system makes is that no school ever sees another school's data — not a student's name, not a fee receipt, not an exam mark.

This post covers the decisions that kept that promise, and the bugs that taught us the most.

Three ways to isolate tenants

There are three common designs for a multi-tenant database:

Design	How it works	Good for
Database per tenant	Each school gets its own database	Few, large tenants with strict contracts
Schema per tenant	One database, one Postgres schema per school	Tens of tenants, some customisation
Shared tables + tenant column	All schools in the same tables, each row tagged	Hundreds of tenants, one product

We chose shared tables with a tenant column. It keeps migrations simple — one migration runs once, not once per school — and reporting across the platform stays fast. The cost is discipline: every query must filter by tenant, every time.

A tenant column on everything

Every tenant-owned table carries a school_id, with an index that starts with it. A middleware resolves the current school from the logged-in user and attaches it to the request. Every service-layer query filters on it.

Two rules made this safe in practice:

1. The tenant never comes from the request body. A client can't send school_id and switch schools; it always comes from the authenticated session.
2. Unique constraints include the tenant. An admission number is unique within a school, so the constraint is (school_id, admission_no), not admission_no alone.

Postgres row-level security (RLS) can enforce the same rule inside the database. It is a strong second line of defence for teams that want it, at the cost of more complex connection handling.

One permission matrix

Roles — principal, manager, teacher, student, parent and staff — are checked through a single matrix of resource → action → roles. Adding a feature means adding one line to that matrix, not sprinkling role checks across controllers.

Because the matrix is plain data, it is easy to test: a single test can walk every resource and action and confirm that, for example, a parent can never update a mark.

Return 404, not 403

When a student asks for a course outside their class, the API answers not found, not forbidden. A 403 would confirm that the resource exists in another class — or another school — which is itself a small data leak. Treat "not yours" exactly like "doesn't exist".

Plans, limits and feature flags

Schools buy different plans, so limits — number of students, storage, modules — live on the tenant record. Every limit is checked on the server, never only hidden in the user interface. A missing limit must mean no access, not unlimited. That rule comes from the bug below.

The bug worth remembering

We once added a column through a migration but forgot to declare it on the ORM model. The ORM silently ignored the field, a seat limit read back as undefined, and the code treated it as "no limit". A before-and-after API test caught it before release.

The lessons:

• After every migration, update the model in the same commit.
• Test the behaviour, not just the schema: "a school on the basic plan cannot add a 101st student".
• Make the safe value the default. Missing data should lock a feature, not unlock it.

Checklist for your own multi-tenant SaaS

• Tenant id on every tenant-owned table, first column in composite indexes.
• Tenant taken from the session, never from user input.
• Unique constraints scoped by tenant.
• One permission matrix, tested as data.
• 404 for anything outside the tenant's scope.
• Limits enforced on the server, with "deny" as the default.
• An automated test that logs in as two tenants and proves neither can read the other's records.

Building a SaaS product? Talk to us about architecture before the first migration — it is the cheapest time to get it right.