Happy Tracker holds a few hundred organisations in one MySQL database. Every project, every time entry, every screenshot and every leave request belongs to exactly one of them, and the separation is a single column called organization_id. There is no separate database per customer, no schema switching, no connection juggling.

That decision gets argued about a lot, usually by people who have never had to run the alternative on a small team. This is why we chose it, what it costs, and — more importantly — the specific patterns that stop a shared-column design from leaking one customer’s data into another customer’s screen.

The three ways, honestly compared

A database per tenant gives you the strongest isolation available. A query written against the wrong connection cannot return another customer’s rows, because those rows are not there. It is genuinely safer, and it is the right answer if you sell to banks.

The bill arrives in operations. Every migration runs once per tenant, which means a deploy that takes four seconds at ten customers takes six minutes at three hundred, and it can fail halfway. Backups multiply. Connection pools multiply. A cross-tenant report — how many organisations used feature X this month — becomes a loop rather than a query. Adding a customer becomes a provisioning job with its own failure modes.

A schema per tenant on PostgreSQL is the same trade with fewer databases to back up. The migration fan-out is identical, which is the part that actually hurts.

A shared table with an organisation column makes all of that go away. One migration, one backup, one connection, one deploy. Cross-tenant analytics are ordinary SQL. Onboarding a customer is an INSERT.

And in exchange you accept one thing: isolation is now a property of your code rather than a property of your infrastructure. Every query that forgets the filter is a data breach with no error message.

For a team of four running a few hundred tenants, the shared column is the right call — but only if you make the isolation structural rather than remembered. The rest of this article is about that word, structural.

Why a where clause is not a strategy

The obvious implementation is to add the filter everywhere:

$projects = Project::where('organization_id', auth()->user()->organization_id)
    ->orderBy('name')
    ->get();

This is correct. It is correct the first time, and the fiftieth. The problem is the two hundredth, written at seven on a Friday by somebody adding a small feature, and the line that goes to production is:

$projects = Project::orderBy('name')->get();   // every tenant, silently

Nothing throws. Nothing is logged. The page renders, a bit slower than usual, with another company’s project names in a dropdown. You find out when a customer emails to ask who Sundaram Textiles are.

Security that depends on remembering is not security. It is a habit, and habits fail under deadline pressure, during onboarding, and in code written by whoever joins next year.

It is worth being precise about why this particular bug class is so unpleasant. Most mistakes announce themselves: a wrong column name throws, a missing relationship throws, a bad type throws. A missing tenant filter does none of that. The query is valid, the result set is bigger, and bigger result sets look like success. There is no exception to catch, no log line to grep for, and no monitoring signal that distinguishes “returned 400 projects because the customer has 400” from “returned 400 projects because it returned everybody’s”.

The only thing that reliably prevents an invisible bug is making the bug impossible to write.

The global scope

Eloquent lets you attach a constraint to every query a model ever makes. This is the single highest-value piece of code in a shared-database multi-tenant application:

namespace App\Models\Scopes;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Scope;

class OrganizationScope implements Scope
{
    public function apply(Builder $builder, Model $model): void
    {
        if (! app()->bound('tenant')) {
            return;                       // console, migrations, health checks
        }

        $builder->where(
            $model->qualifyColumn('organization_id'),
            app('tenant')->id
        );
    }
}

Two details in that small class matter more than they look.

qualifyColumn() prefixes the table name. Without it, the moment a query joins two tenant tables you get “column organization_id is ambiguous” and somebody fixes it by removing the scope.

The app()->bound(’tenant’) guard is the escape hatch for console commands, scheduled jobs and migrations, which have no logged-in user. It is also the most dangerous line in the file, because a bug that leaves the tenant unbound during a web request disables isolation everywhere at once. Make the binding explicit and loud:

class IdentifyTenant
{
    public function handle($request, Closure $next)
    {
        $user = $request->user();

        if ($user && $user->organization_id) {
            app()->instance('tenant', $user->organization);
        }

        return $next($request);
    }
}

Register that middleware on the API and web groups, and add a test that hits an authenticated route and asserts the container has a tenant bound. It takes four lines and it protects the assumption everything else rests on.

Attaching it once

Put the scope and the relationship in a trait, and use the trait on every tenant-owned model:

trait BelongsToOrganization
{
    protected static function bootBelongsToOrganization(): void
    {
        static::addGlobalScope(new OrganizationScope);

        static::creating(function ($model) {
            if (! $model->organization_id && app()->bound('tenant')) {
                $model->organization_id = app('tenant')->id;
            }
        });
    }

    public function organization(): BelongsTo
    {
        return $this->belongsTo(Organization::class);
    }
}

The creating hook matters as much as the scope. Reading is only half the problem; a create that forgets to set the organisation produces an orphan row that belongs to nobody and shows up in nobody’s list, which is a genuinely miserable bug to diagnose six weeks later.

Put the column on every table, even the deep ones

A tempting shortcut is to leave the organisation column off tables that are already reachable through a parent. A task belongs to a project, a project belongs to an organisation, so why store it twice?

Because every query for tasks then needs a join to establish the tenant, and because a denormalised copy is what makes the global scope possible at all. Carry organization_id down to the leaves — tasks, comments, attachments, activity blocks, screenshots. It is four bytes per row, and it buys a filter that works without a join on your busiest tables.

It also gives you a consistency check that costs nothing to run:

SELECT t.id
FROM tasks t
JOIN projects p ON p.id = t.project_id
WHERE t.organization_id <> p.organization_id;

That query should return zero rows forever. Run it nightly across each parent-child pair, alert if it ever does not, and you have a direct detector for the one class of bug the denormalised column introduces.

Query through the relationship

The scope protects you from forgetting. The next habit protects you from a subtler failure: accepting an id from the request and trusting it.

Consider a controller that adds a task to a project, where the project id arrives in the body:

// Looks fine. Is a hole if the scope is ever bypassed.
$project = Project::find($request->project_id);

// Cannot be a hole. The tenant is in the SQL by construction.
$project = $organization->projects()->findOrFail($request->project_id);

Both lines are safe while the global scope is attached. The difference is what happens the day somebody adds withoutGlobalScopes() to fix a report, or writes a query builder statement instead of Eloquent, or introduces a model that was copied from a file predating the trait.

Always start from the tenant. $organization->projects() puts the constraint in the generated SQL directly, independent of any scope. It reads better too: the code says what it means, which is “this organisation’s projects”, rather than “all projects, hopefully filtered”.

The same rule applies one level down. A request that carries both a project id and a task id should resolve the task through the project, not separately:

$project = $organization->projects()->findOrFail($projectId);
$task    = $project->tasks()->findOrFail($taskId);

Resolve each level through its parent and a mismatched pair fails at the level where it stops making sense, rather than succeeding and quietly writing a task belonging to project 12 onto project 91.

Route model binding, and why the answer is 404

Laravel resolves Route::get(’/projects/{project}’, ...) by looking the model up for you. With a global scope attached this is already tenant-aware — the lookup fails for another organisation’s id, the binding throws ModelNotFoundException, and the framework renders 404.

That is the correct status code, and it is worth being deliberate about, because the instinct of most developers is to write an authorisation check that returns 403.

A 403 is an admission. It tells the caller that project 91 exists and belongs to somebody else. Walk the ids from 1 to 5,000 and the pattern of 403s and 404s maps out how many projects every other customer on the platform has. That is not a catastrophic leak, but it is free intelligence handed to a competitor who signed up for your free plan.

404 gives nothing. Non-existent and not-yours are indistinguishable, which is exactly what you want. The pleasant part is that the global scope produces this behaviour by default: you have to work to get it wrong.

Where you use scoped bindings, define the nesting in the route so the framework enforces the parent relationship:

Route::get('/projects/{project}/tasks/{task}', [TaskController::class, 'show'])
    ->scopeBindings();

With scopeBindings(), Laravel resolves the task through the project’s relationship rather than as a top-level lookup. A task id from a different project returns 404 without a line of controller code.

The pivot table, and where the role lives

A person can belong to more than one organisation — a contractor working for two clients, an accountant with access to several books. The moment that is true, users.organization_id stops being adequate.

The membership becomes its own row, and the role belongs on it:

Schema::create('organization_user', function (Blueprint $table) {
    $table->id();
    $table->foreignId('organization_id')->constrained()->cascadeOnDelete();
    $table->foreignId('user_id')->constrained()->cascadeOnDelete();
    $table->string('role');                 // owner, admin, manager, member
    $table->timestamp('joined_at');
    $table->timestamp('removed_at')->nullable();
    $table->unique(['organization_id', 'user_id']);
});

The role is a property of the membership, not of the user. The same person can be an owner in one organisation and a plain member in another, and any design that puts users.role in the users table cannot express that without lying.

Read it through the pivot, with the tenant already in scope:

public function roleIn(Organization $org): ?string
{
    return $this->organizations()
        ->wherePivotNull('removed_at')
        ->find($org->id)?->pivot->role;
}

Note removed_at rather than deleting the pivot row. Somebody who left in March still appears as the author of time entries and the approver of leave requests from February, and a hard delete turns every one of those into an unresolvable reference. Keep the membership, mark it ended, and exclude it from anything that asks “who is on this team now”.

Support access, and the one legitimate way to cross the line

Sooner or later somebody in support needs to see what a customer sees. The wrong way is a super-admin flag that quietly disables the global scope, because from that moment the strongest guarantee in the system has a bypass that a bug can reach.

The pattern that works is to switch the tenant rather than remove it. A support session rebinds the container to the customer’s organisation, keeps the scope fully active, and records who did it:

public function impersonate(Organization $org, User $target)
{
    abort_unless($this->user()->isPlatformAdmin(), 404);

    Audit::record('support.impersonated', $target, [
        'organization_id' => $org->id,
    ]);

    return $this->issueScopedToken($target, impersonatedBy: $this->user());
}

Isolation is never off; it is pointed somewhere else. The session carries an obvious banner so nobody forgets which account they are in, every action it takes is attributed to both people, and the guard is an abort_unless(..., 404) rather than a 403, for the same reason as before.

Where the shared column still bites

Being fair about the weaknesses is more useful than defending the choice.

• Raw SQL and query builder bypass the scope entirely. A DB::table(’time_entries’) aggregate for a report has no global scope and no safety net. Every raw query in a multi-tenant app needs the organisation in its where clause, written by hand, and reviewed by a human.
• Your indexes need the column first. An index on (user_id, started_at) is close to useless when every query also filters by organisation. Lead with organization_id in nearly every composite index — it is the highest-selectivity filter you have, and getting this wrong is the usual cause of a tenant app that feels fine at fifty customers and crawls at three hundred. The column order in a composite index decides whether it is used at all.
• One noisy tenant affects everyone. A customer running a 40,000-row export at 11am competes with every other customer for the same database. Per-tenant rate limits are not optional at any scale worth having.
• “Delete my data” is a query, not a DROP. Under GDPR or a contractual exit, you are writing a careful cascade across thirty tables instead of dropping a database. Write that job early, while there are only ten tables.
• A restore is all-or-nothing. Recovering one tenant from last Tuesday means restoring the whole backup to a scratch server and copying rows out. Rehearse it once, before you need it.

The tests that must exist

Here is the part people skip, and it is the part that lets you sleep. The global scope, the relationship habit and the 404 behaviour are all invisible when they work. The only way to know they still work after six months of changes is a test suite that deliberately tries to cross the boundary.

The foundation is a seeder that always creates two organisations with data in both. A test suite with one tenant in it cannot detect a leak, because there is nothing to leak.

public function test_cannot_read_another_organizations_project(): void
{
    [$orgA, $orgB] = Organization::factory()->count(2)->create();

    $userA   = User::factory()->for($orgA)->create();
    $project = Project::factory()->for($orgB)->create();

    $this->actingAs($userA)
        ->getJson("/api/projects/{$project->id}")
        ->assertNotFound();                 // 404, deliberately not 403
}

Then the index test, which catches the forgotten-where-clause bug directly:

public function test_index_returns_only_own_projects(): void
{
    [$orgA, $orgB] = Organization::factory()->count(2)->create();

    Project::factory()->count(3)->for($orgA)->create();
    Project::factory()->count(5)->for($orgB)->create();

    $this->actingAs(User::factory()->for($orgA)->create())
        ->getJson('/api/projects')
        ->assertJsonCount(3, 'data');
}

And the one that people never write, which is worth more than the other two combined — a reflection test asserting that every model which has an organization_id column actually has the trait:

public function test_every_tenant_model_has_the_scope(): void
{
    foreach ($this->tenantModels() as $class) {
        $model = new $class;

        $this->assertArrayHasKey(
            OrganizationScope::class,
            $model->getGlobalScopes(),
            "{$class} is missing the organisation scope"
        );
    }
}

That test fails the day somebody adds a model by copying an older file. It is the only one of the three that protects you from a person who has not read this article, which is the failure mode that actually occurs.

Also worth asserting

1. A create request carrying another organisation’s project_id is rejected by validation, not silently accepted.
2. An update request that tries to change organization_id has no effect — that column belongs in $guarded.
3. Report aggregates do not move when the other organisation gets more data. This catches raw SQL that the scope never touched.
4. A user removed from an organisation loses access immediately, including any token they still hold.

What to do on Monday morning

If you have a shared-database multi-tenant application in production, three hours of work will tell you where you stand.

1. List every table with an organization_id column and check each corresponding model has the trait. Do it by hand once; then write the reflection test so it is checked forever.
2. Grep for withoutGlobalScope, DB::table, DB::select, whereRaw and selectRaw. Every hit is a place the scope does not apply. Confirm each one filters by organisation explicitly.
3. Grep for ::find( and ::findOrFail( in controllers. Rewrite each to start from the tenant relationship. The scope makes these safe today; the relationship makes them safe regardless.
4. Seed a second organisation into your test suite and write the three tests above. If any of them passes without the scope attached, the test is wrong, so delete the scope temporarily and confirm they all go red.
5. Check your composite indexes lead with organization_id. Run EXPLAIN on your two slowest list endpoints and look at which index was chosen.

The shared column is a good design. It is simple to operate, cheap to run and easy to reason about, and it will hold several hundred tenants on hardware that costs less per month than a single developer’s lunch budget. But it puts the entire isolation guarantee inside your application code, and the only honest way to carry that responsibility is to make the guarantee structural — on the model, in the relationship, in the foreign key — and then to prove it with tests that actively try to break through.

Get that right and you never think about tenancy again. Get it wrong and you find out from a customer.