One database, nineteen worlds

The shape of the problem

Our tenants are not customers — they are whole vertical products. A charter operation, a restaurant back-office and a sports club manager do not share a data model; they share a platform: one identity, one tenancy record per provider, one push system, one job runner. And they are rebuilt in parallel lanes, which adds a constraint most multi-tenancy write-ups skip: two lanes must be able to verify at the same time, each seeding and truncating test data, without touching each other.

Three ways to slice a database

What is genuinely shared lives in a dedicated platform schema: identity, tenancy, the ticketing core, push subscriptions, job heartbeats. Everything vertical lives in that OS's own schema. Isolation where people collide; oneness where the product demands it — a provider signs in once, not nineteen times.

Selection you cannot forget

The REST layer over Postgres selects a schema per request by header. The dangerous version of that sentence is “every query passes its schema” — because forgetting on one query out of four hundred is how an OS half-writes into another's tables, and the failure mode is quiet. So no individual query is allowed to remember. An OS module declares its schema once, at the top, and takes pre-bound calls from the seam:

// the shape: bind once, never per query
const db = forSchema('restaurantos');
// every db.get / db.rest call now carries the right
// read-header or write-header automatically

Row-level security, from the first table

Schemas separate operating systems from each other. Inside one OS, providers still share tables — and that boundary is enforced by the database, not by application code remembering a WHERE clause. Every table ships with row-level policies in the same migration that creates it. Retrofitting RLS onto a live table means archaeology on every existing query; writing it on day one costs a paragraph. The generic shape:

alter table bookings enable row level security;

create policy provider_owns_rows on bookings
  using (provider_id = current_provider_id());

Belt and braces: the application layer also scopes every read and write by provider as its first argument — not because we distrust the database, but because two independent layers have to fail together before data leaks, and one of them is checked by a guard.

The two boring rules that make it hold

Schema-per-OS fails in practice through migrations, not queries. Two rules, each written after an actual incident, both enforced by a CI guard:

• Every migration file lives under a namespace, and only touches tables its namespace owns. A file loose in the migrations directory has no declared owner — which is exactly how an early OS migration came to create two platform tables it had no business creating. Ownership is written down, and the guard reads it.
• Numbers never repeat inside a namespace — and repeat across namespaces on purpose. Our applied ledger once carried the same number twice, from a single file applied as two versions; the guard makes that impossible now. Across namespaces, both the platform and an OS can own a migration with the same number, and replay order breaks the tie: platform first, always.

And the rule that makes both rules testable: CI replays the entire migration history from zero on every build. Today that is — platform migrations and — for the first rebuilt OS. A migration that only works because of the state it happened to run against is a migration that will fail on the day you least want it to — the day you stand up a fresh region.