Multi-tenant

Multi-tenant Laravel, part one: schema choices

Series opener. Schema-per-tenant vs row-per-tenant, the migration path, the queries that broke.

April 8, 202612 minby Ali Yosef

Part ofmulti-tenant-laravel

Loading essay…

Multi-tenant

Multi-tenant Laravel, part one: schema choices

Series opener. Schema-per-tenant vs row-per-tenant, the migration path, the queries that broke.

April 8, 202612 minby Ali Yosef

Part ofmulti-tenant-laravel

There are two ways to add multi-tenancy to a Laravel app, and the choice you make in week one shapes the next three years. This is part one of four on running multi-tenant Laravel honestly. We start at the schema because the schema decides almost everything else: how migrations work, how backups work, how performance scales, how leakage gets prevented.

The two schools#

The literature gives two answers, and most teams pick one without realising they have picked.

Schema-per-tenant (separate database or schema)#

Each tenant gets their own database (or, in Postgres, their own schema within a shared database). Models point at a connection chosen at request time based on the active tenant.

class TenantManager {
    public function bootForTenant(Tenant $tenant): void {
        config(['database.connections.tenant.database' => $tenant->database_name]);
        DB::purge('tenant');
        DB::reconnect('tenant');
    }
}

The result: a hard wall between tenant data. Any query in this request sees only that tenant's rows. Backups are per-tenant. Scaling is per-tenant. There is no tenant_id column on anything.

Row-per-tenant (shared database, scoped by column)#

All tenants share one database. Every table has a tenant_id column. Every query is scoped by it. Models have a global scope:

class TenantScope implements Scope {
    public function apply(Builder $builder, Model $model): void {
        $builder->where('tenant_id', auth()->user()->tenant_id);
    }
}

abstract class TenantModel extends Model {
    protected static function booted(): void {
        static::addGlobalScope(new TenantScope());
        static::creating(function ($model) {
            $model->tenant_id ??= auth()->user()->tenant_id;
        });
    }
}

The result: one database to manage, but the safety of the system depends on every query passing through that scope. One missed call to withoutGlobalScopes() and a tenant sees another tenant's data.

The honest trade-off#

Schema-per-tenant has a clean isolation story. It also has a problem nobody warns you about: operational cost compounds. If you have a hundred tenants, you have a hundred databases. Each one needs:

Its own migrations to be run (in lockstep, ideally)
Its own backups
Its own connection pool slot in your worker processes
Its own monitoring
Its own potential corruption to handle

When schema-per-tenant breaks, it tends to break for one tenant at a time, while everyone else keeps working. That is good. When migrations break, they tend to break for one tenant only, while you spend the next day reconciling. That is bad.

Row-per-tenant has no operational compounding. There is one database, one set of migrations, one backup policy. The problem is that every query is one missed scope away from a leak, and at scale tenant_id ends up in every index, every where clause, every join.

Why we picked row-per-tenant#

For an accounting product that ships to small-and-medium businesses, the tenant count is in the hundreds and grows. Per-tenant ops would have eaten a full-time engineer by year two. The leak risk is real but mechanical, and mechanical risks can be mitigated mechanically:

Every tenant-scoped model extends TenantModel, and TenantModel adds the global scope and the creating hook automatically.
CI runs a static-analysis pass that flags any model that does not extend TenantModel and uses a tenant-bound table. The pass is the only thing that consistently catches new models that forgot the parent class.
Every API test runs against a fixture with two tenants and asserts that tenant A cannot see tenant B's rows. No exceptions.

The static-analysis check is the most underrated of the three. Code review will miss this; tests will miss this if the test author also made the mistake. A linter does not.

What goes on every table#

Three columns, every time:

ALTER TABLE invoices
  ADD COLUMN tenant_id BIGINT UNSIGNED NOT NULL,
  ADD COLUMN created_by BIGINT UNSIGNED,
  ADD COLUMN updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP;

CREATE INDEX invoices_tenant_id_idx ON invoices (tenant_id);

-- Every "where this and that" index gets tenant_id as the leading column
CREATE INDEX invoices_tenant_status_idx ON invoices (tenant_id, status);
CREATE INDEX invoices_tenant_due_idx ON invoices (tenant_id, due_date);

The third index policy is the one that matters for performance. A status index that doesn't lead with tenant_id cannot be used by a tenant-scoped query without a sort step. Get this wrong on a hot table and your query plans collapse around month nine, when no individual tenant is enormous but the aggregate row count is.

The migrations that broke us#

Three patterns we kept hitting:

Adding NOT NULL without a backfill#

Adding a non-null column to a tenant-bound table on a shared database locks the table for the duration of the rewrite. With a hundred tenants, that lock is felt by all of them. The fix is the expand-then-contract dance: ship the column nullable, backfill in chunks, then add the constraint.

Renaming a column on a hot path#

A column rename on a shared table is a coordinated refactor across every tenant simultaneously. The pattern is to add the new column, double-write from the application for one release, backfill, then drop the old. Three deployments, no downtime.

Dropping a column "nobody uses"#

Nobody uses it on tenant 41 because tenant 41 is a small one. Tenant 7 has a dashboard query that touches it. The migration that drops it takes tenant 7 down. The lesson: do not "clean up unused columns" on shared schemas, ever, without searching every tenant's recent query log.

What's next#

Part two of this series is about queues. Specifically, what happens when one tenant has a million-row import and the other ninety-nine just want their daily sync to finish in under a second.

Series page tracks the rest.

Loading essay…