The N+1 that was in the authorisation layer

The orders list had eager loading in the controller, a query-count test that passed, and two hundred queries per request in production. The eager loading was correct and the test was measuring the wrong thing, because the extra queries were coming from the authorisation check that ran after the controller had finished.

The symptom

$ curl -s -o /dev/null -w '%{time_total}n' '/orders?per_page=50'
1.412

# the query log, grouped:
#     1  select * from orders ... limit 50
#     1  select * from customers where id in (...)
#     1  select * from shipments where order_id in (...)
#    50  select * from customers where id = ?      ← ?
#    50  select * from teams where id = ?
#    50  select exists(select 1 from team_user ...)

# 50 rows, three queries each, from a layer the eager
# loader has never heard of.

The controller eager-loads the customer and the policy loads it again, because the policy receives the model and calls a relation on it — and the relation is already loaded, but the policy is resolving a different chain. Nothing about the controller suggests any of this is happening.

Why it happens

Authorisation runs outside the query, per object, and is invisible to the query planner, to the eager loader and to every N+1 detector that watches the ORM from inside a controller. A policy is a function of a user and a model, and calling it fifty times is fifty independent evaluations by design.

It is also invisible in review, because the controller is correct, the policy is correct, and the interaction is the problem. The only place it shows up is a query log with the request as a whole in view.

The fix

Seeing it at all

Model::preventLazyLoading(! app()->isProduction());

Model::handleLazyLoadingViolationUsing(function (Model $m, string $r): void {
    Log::warning('orm.lazy_load', [
        'model'  => $m::class,
        'relation' => $r,
        'route'  => request()->route()?->uri(),
        'origin' => firstAppFrame(),        // ← the useful bit
    ]);
});

Recording the first application frame in the stack is what distinguishes a lazy load in a controller from one in a policy, and without it the warning names a model and a relation and leaves the location as an exercise. That one field turned a two-hour investigation into a grep.

The policy that was doing the work

public function view(User $user, Order $order): bool
{
    // three queries, per order, every time
    return $user->id === $order->customer->user_id
        || $user->teams->contains($order->customer->team_id);
}

// $order->customer  — a relation, loaded per call
// $user->teams      — a relation, loaded per call, on the
//                     same user object, fifty times

The $user->teams load is the one that should not happen at all: it is the same user object on every call, so the relation is loaded once and cached on the model — except that the user is resolved fresh from the container in some code paths, which produces fifty distinct instances and fifty loads.

Batch authorisation

/** @return Collection<int, int> the ids the user may see */
public function viewableIds(User $user, Collection $orders): Collection
{
    $teamIds = $user->teams()->pluck('teams.id');     // one query

    return DB::table('orders')
        ->join('customers', 'customers.id', '=', 'orders.customer_id')
        ->whereIn('orders.id', $orders->pluck('id'))
        ->where(fn ($q) => $q
            ->where('customers.user_id', $user->id)
            ->orWhereIn('customers.team_id', $teamIds))
        ->pluck('orders.id');                          // one query
}

Two queries instead of a hundred and fifty, and the authorisation rule is now expressed twice — once per object and once in SQL. That duplication is the real cost of this approach and it is a correctness risk: the two can diverge, and the divergence is a permission bug rather than a performance one.

// so the two are tested against each other, exhaustively
public function testBatchAndSingleAgree(): void
{
    $user   = User::factory()->withTeams(2)->create();
    $orders = Order::factory()->count(40)->assorted()->create();
    $batch  = $this->policy->viewableIds($user, $orders);

    foreach ($orders as $order) {
        $this->assertSame($this->policy->view($user, $order),
            $batch->contains($order->id), "order {$order->id}");
    }
}

This test is the thing that makes the duplication survivable, and it has to cover the assorted cases rather than the happy one — an order owned by the user, one owned by their team, one owned by neither, and one with a null customer. It caught a genuine divergence within a week, where the SQL treated a null team as a match and the object form did not.

Caching a decision within a request

// simpler, and covers the repeated-USER case rather than
// the repeated-model one. bound with scoped().
public function allows(string $ability, Model $model): bool
{
    $key = $ability . ':' . $model::class . ':' . $model->getKey();

    return $this->decisions[$key] ??= $this->gate->allows($ability, $model);
}

A request-scoped cache is the cheaper fix and does nothing for a list of fifty distinct models, since every key is different. It is the right answer for the case where the same object is checked from several places in one request — a page rendering a resource in a header, a body and a sidebar — which is a different problem that looks identical in a query log.

The rule about which to use

  a single resource       the per-object policy
  a collection, rendered  the batch policy
  a collection, filtered  a scope on the query, and no
                          policy call at all

the third is best and is not always available. filtering
in the query means the unauthorised rows are never loaded
and never counted in a paginator — which is the difference
between a page saying "50 results" and one saying "50
results, 12 of which you cannot see".

The pagination consequence is the argument for pushing this into the query wherever possible: filtering after the fact produces a page of eleven items where the paginator says fifty, which is a visible bug rather than a performance one. A global scope on the model is the mechanism and it has its own hazard — a scope that silently filters is a scope somebody will forget when writing a report.

Verifying it worked

$ curl -s -o /dev/null -w '%{time_total}n' '/orders?per_page=50'
0.142        # was 1.412

$ vendor/bin/phpunit --filter 'QueryCount|BatchAndSingle'
Tests: 7 passed

# a week of production, with the warning left on
$ grep -c 'orm.lazy_load' /var/log/app/*.log
0

# 153 queries → 6

public function testOrderIndexDoesNotScaleWithRowCount(): void
{
    Order::factory()->count(50)->create();

    DB::enableQueryLog();
    $this->actingAs($this->user)->get('/orders?per_page=50')->assertOk();

    // the assertion that would have caught this: it counts
    // the whole request, including the response render
    $this->assertLessThan(10, count(DB::getQueryLog()));
}

The original query-count test asserted inside the controller and therefore missed everything after it, which is why it passed. Counting for the whole request — including the response rendering, where the policy actually runs — is the change that makes the test meaningful, and it is one line.

What this costs

The authorisation rule written twice, in two languages, with a test holding them together. That is a genuine correctness risk and the reason to reach for the query scope first — a rule expressed once as a WHERE clause has none of this problem and is not always expressible, particularly when the rule involves anything outside the database.

It also adds a second way to write a check, which means a rule about which to use and a review that enforces it. The failure mode of getting it wrong is not a broken page: it is a page that is fast and shows the wrong rows, which is worse than the problem being solved and is the reason the agreement test is not optional.