Sometimes you can watch yourself get better inside a single file. In an old CRM I built, managers wanted dashboards — claims per day, revenue per team, that sort of thing — over a table heading toward millions of rows. I built those charts over a few weeks, and by pure luck the good version and the bad version ended up as adjacent methods in the same class. Opening it back up is like reading two different engineers who happen to share a name.

Here’s the one I’d still write today. “How many claims per day, for this user, in this window”:

$query = Claim::select([
    DB::raw("DATE(created_at) as day"),
    DB::raw('count(id) as count'),
])->whereBetween('created_at', [$from, $to]);

if ($user->hasRole("Agent")) {
    $query->where("user_id", $user->id);
}
if ($user->hasRole("Manager")) {
    $query->where("team_id", $user->team->id);
}

$claims = $query->groupBy('day')->orderBy('day')->pluck('count', 'day');

The database does the counting. It filters by an indexed date range, groups by day, and hands back a tiny result — one row per day, a couple of integers each — regardless of whether there are ten thousand claims or ten million behind it. The permission rules (an agent sees only their own, a manager sees their team) are just more where clauses on the same query, so authorization and aggregation happen in one pass. The result set is bounded by the number of days in the window, not the number of rows in the table. This scales fine forever.

There’s a nice variant right next to it that queries the audit trail’s JSON column to chart status changes — where("new_values->status_id", $status) — which is a small thing I’m still a little proud of: reaching into a JSON field in SQL instead of loading audits into PHP to inspect them.

And then, a few methods down

Same file. “Revenue per team.” Here’s what past-me wrote:

$teams = Team::whereHas("campaign", fn ($q) => $q->where("activity_id", 2))
    ->get()
    ->load(["claims.claimable"]);          // load EVERY team's EVERY claim into memory

$result = collect([]);
$teams->map(function ($team) use ($result) {
    $result->put($team->name, $team->claims->sum(function ($claim) {
        return $claim->claimable->plan->price;   // ...then reach through two more relations per claim
    }));
});

Read what this does. It loads every team, and for each team eager-loads every claim, and for each claim walks claimable → plan → price and sums it in a PHP closure. So to show a handful of numbers — one total per team — it pulls every claim in those teams into memory, hydrates them into objects, and does the arithmetic in Ruby-, sorry, PHP-land. The exact work the database is built to do in one SUM ... GROUP BY, done the slowest possible way. On a young table it returns instantly. On the real one it’s the query that makes the dashboard spin.

The whole thing should have been what the method above it already knew how to do:

$result = Claim::join('plans', /* ...via claimable... */)
    ->select('teams.name', DB::raw('SUM(plans.price) as revenue'))
    ->whereBetween('claims.created_at', [$from, $to])
    ->groupBy('teams.name')
    ->pluck('revenue', 'name');

Same answer, one query, bounded result. The gap between the two methods isn’t knowledge of SQL — I clearly knew how, one screen up. It’s that ->sum(fn ($x) => ...) on a collection reads so naturally in Eloquent that it doesn’t feel like a mistake. Collections make loading-then-computing frictionless, and frictionless is exactly how an O(rows) operation sneaks into a dashboard.

The schema was in on it

The in-memory version hurt more than it had to because the table wasn’t indexed for the questions the dashboards asked. The claims migration declared exactly one explicit index — on the reference column — which was already unique, so it was redundant, adding nothing. Meanwhile the columns every dashboard actually filtered on — team_id, user_id, status_id, created_at — had no composite index at all. So even the SQL version was doing more work than it needed, and the in-memory version had nothing to lean on. Across the whole app, only a small fraction of the migrations declared any index; the rest trusted that “it’s fast now” would keep being true.

The fix for the dashboards is a couple of composite indexes matching the access patterns — (team_id, created_at), (user_id, created_at), (status_id, created_at) — leading with the equality filter and ending with the range/sort column. Index for the query you actually run, not the column that happened to be unique.

What it taught me

Two habits I’ve kept. First: if you’re calling ->sum, ->map, ->filter, or ->groupBy on a collection of database records in a request path, stop and ask whether the database should be doing it. Ninety percent of the time the answer is yes, and the rewrite is a SELECT ... GROUP BY that returns a fraction of the data. Loading rows to compute a number over them is the most natural-feeling performance bug there is. Second: indexes aren’t a thing you add when it’s slow — they’re the shape of the questions you’re going to ask, decided up front. A table with a redundant index on a unique column and none on its hot filters is a table whose author (me, here) indexed by reflex instead of by access pattern.

I don’t wince at the good method. I wince a little at the one below it — but honestly, having both in one file is the most useful thing in the repo, because it’s the exact moment the lesson landed, preserved in amber.

I spend a lot of my time now making dashboards and reports fast — usually by moving the work back into the database and indexing for it. If yours are the slow part of the app, let’s talk. Related: migrating this CRM’s data off its legacy system.