Multi-Tenancy in One Database Without Leaks: Global Scopes, 404s and Isolation Tests

The bug that made me rewrite tenancy in Opero was not dramatic. Nothing had leaked yet. Before we onboarded a second excursion operator, I did an audit. Twenty-two tables that held one operator's data had no provider_id at all. Tax codes and catalogue slugs were unique across the whole instance. Route-model binding meant /bookings/{booking} would resolve any booking by id. The only thing keeping operators apart was every controller remembering to add a where. One operator, that's fine. With two, every forgotten where would have shown one operator's data to the other.

This is how I made isolation a property of the data layer, not a habit of the person writing the controller, and how I test it.

Why one database

Opero is a POS and operations platform for excursion operators: counter sales, multi-currency payments, QR wristbands, handheld scanners. One operator writes maybe tens to a few hundred rows a day. A database per tenant would have meant separate migrations, backups and connection config for every operator. On top of that, the platform's own screens (list every tenant, provision one, change a tenant's base currency) would have to go across databases.

So I wrote down the decision as an ADR: one database, every tenant-owned row carries an indexed provider_id, and a global scope enforces it. The alternatives were "filter in controllers by convention" (the bug class I was trying to get rid of) and "database per tenant" (the operational cost above). The ADR lists both as rejected.

The scope is the boundary, not the controller

Every tenant-owned model uses one trait. It applies a global scope, and on create it stamps the current tenant onto the row:

trait BelongsToTenant
{
    public static function bootBelongsToTenant(): void
    {
        static::addGlobalScope(new TenantScope);

        static::creating(function ($model) {
            if ($model->provider_id === null) {
                $model->provider_id = static::resolveTenantForCreate();
            }
        });
    }
}

The tenant itself lives in a TenantContext singleton. Middleware fills it in from the authenticated user. Outside a request (seeders, console commands, queued jobs) nothing is implicit. You either call runAs($providerId, fn () => ...) or, for genuinely cross-tenant work, runWithoutScope(...). Both restore the previous state in a finally. That matters because one job that leaves the scope switched off would quietly un-scope everything that runs after it in the same worker.

The migration backfilled provider_id on those 22 tables in one go. After that, adding a tenant table follows a rule that is easy to check in review: the table gets provider_id, the model gets the trait, the isolation test gets a case.

Middleware order is a security property

This is the part I nearly got wrong. In Laravel, SubstituteBindings is the middleware that turns {booking} into a Booking model. If it runs before the tenant context exists, the lookup runs unscoped, and a guessed id loads another operator's booking before your code even starts.

So the middleware priority list pins the order: authenticate, then SetTenantContext, then SubstituteBindings. As a second line of defence, the scope falls back to the authenticated user's provider whenever no explicit context is set. If someone reorders the middleware one day, binding still resolves through the right tenant.

404, never 403

When a user asks for another tenant's record, they get 404. A 403 says "this exists, but you can't have it", which confirms the id is real. Under a global scope the row simply isn't there for them, and the response should say so. You don't need special code for this, either. Scoped route-model binding can't find the row, so it 404s by itself.

Fail closed for principals that aren't staff

Later Opero got a second kind of principal: travel agents. An agent is a global identity linked to operators through contracts, not a staff user of any one tenant. That opened a dangerous gap. With an agent logged in and no explicit tenant context, "fall back to the user's provider" finds nothing. A scope that does nothing when it has no tenant would then return every tenant's rows.

So the scope treats an authenticated non-staff principal as "no tenant at all" and matches nothing:

if ($tenantId === self::NO_TENANT) {
    $builder->whereRaw('1 = 0');
    return;
}

Agent routes bind the tenant explicitly, from an active operator link. If someone forgets that binding, the agent sees an empty list instead of everyone's data. When you can't work out the tenant, the default has to be "nothing".

Uniqueness is per tenant too

A global scope filters reads. It does nothing for unique indexes. Two operators can both sell a "sunset-cruise", so the slug index became composite (provider_id, slug), and validation matches it:

Rule::unique('services', 'slug')->where('provider_id', $providerId)

One version of this bit us before the fix. Booking references were numbered per date but unique across the whole instance. The second operator's first booking on any given day failed with a duplicate key. There's now a test named exactly that: two tenants can book the same date.

The validation side has one more trap. Laravel's bare exists: rule queries the table directly and skips the Eloquent scope. A foreign key coming from user input has to be validated with an explicit provider_id condition. Otherwise someone can attach another tenant's id to their own record. The ADR records this as a standing audit item, so it doesn't get forgotten.

The places the scope can't see

A global scope only covers Eloquent. I keep a short list of the places it can't reach:

• •Raw SQL. The daily summary report uses the query builder for speed and scopes every table by hand. It has its own isolation test.
• •Child rows without the trait. Booking items, for example, are reached only through their parent booking, so the parent's scope protects them. If one is ever queried directly, it gets the trait.
• •Instance-wide tables. Currencies, denominations and exchange rates started out global, the one documented exception. When platform admins needed to give each operator its own base currency, the exception had to go.

That currency migration was the most delicate change in the project. currencies.code stopped being unique instance-wide and became unique per (provider_id, code). MySQL refuses a foreign key that points at a non-unique column, so six foreign keys that referenced currencies.code had to be dropped. The codes stay plain strings, matched through the tenant scope. The first operator kept the existing rows, and every other operator got a copy, because they had all been trading on that shared set. I ran the migration up, down and up again against a seeded scratch database before it went anywhere near production.

Isolation tests are the contract

The reference test builds two tenants, Acme and Beta, each with an admin. Then it tries to break the wall:

• •each admin's list shows only their own services
• •reading Beta's service by id as Acme answers 404
• •created records get stamped with the caller's tenant
• •both tenants can use the same slug, but one tenant can't use it twice
• •the hand-written report counts one booking per tenant, not two
• •schedules, slots, overrides and meals bound directly in routes refuse cross-tenant PATCH/DELETE with 404, and the row is unchanged afterwards

That last assertion is important. A 404 from the response alone doesn't prove the write was blocked. I check fresh() on the record to confirm nothing moved.

Trade-offs I accept

• •The scope is invisible. A query looks unfiltered until you remember the trait. I accept that in exchange for not relying on memory everywhere else, and I keep withoutGlobalScopes() rare and easy to grep for.
• •Tests run on SQLite, production on MySQL. The isolation tests prove the logic, not MySQL behaviour. That's why the currency migration was rehearsed on a real MySQL database.
• •It is Eloquent-shaped. Anything that goes around the ORM needs its own scoping and its own test.

Takeaway

Multi-tenancy in one database is safe when isolation is enforced in one place and attacked in tests. Put the boundary in a global scope. Make sure it's bound before route-model binding. Answer cross-tenant reads with 404. Fail closed when you can't work out the tenant. Make every unique index composite. Then keep a short, written list of what the scope can't see (raw SQL, bare exists: rules, global tables) and give each item its own test. The wall shouldn't depend on anyone's memory.