# Fix N+1 Queries in a Phoenix App

Yes. An agent counts the Ecto queries behind a slow Phoenix route, fixes the N+1 with a preload or a lateral join, and pins the count in a test before opening a PR.

[Run this on Rush](https://cloud.getrush.ai/run/fix-n-plus-one-queries/phoenix/), in a Project that has your repository.

## What the agent does

The run starts with the alert text after the prompt and the repository cloned into its own cloud sandbox. In a Phoenix app like hexpm/hexpm the agent reads `mix.exs` first: Elixir `~> 1.20`, `ecto` and `ecto_sql` `~> 3.0`, `ecto_psql_extras`, and a `test` alias that runs `ecto.create --quiet`, `ecto.load --skip-if-loaded`, `ecto.migrate` and then `test`. `config/test.exs` points `Hexpm.RepoBase` at a `hexpm_test` database through `Ecto.Adapters.SQL.Sandbox` with a pool of 20, and `.github/workflows/main.yml` runs the suite on OTP 29.1 and Elixir 1.20.4 against a `postgres:18` service, with `mix compile --warnings-as-errors` and `mix format --check-formatted` as gates. The sandbox starts Postgres, runs `mix setup`, and the suite is available.

Then it looks for how the project already measures queries, because the fix needs a number. `test/support/test_helpers.ex` has `capture_queries/1`: it attaches a `:telemetry` handler to `[:hexpm, :repo_base, :query]`, runs the function, and returns the SQL of every query the calling process issued. `package_controller_test.exs` already uses it to assert that `GET /packages/:name` issues exactly one query against `package_dependants`, and `organization_controller_test.exs` uses it to prove the profile tab never touches `audit_logs`. The agent writes the same shape of test for the route in the alert: `capture_queries(fn -> get(build_conn(), path) end)`, then counts the queries by table. A route that issues one `SELECT` per row of a list is the N+1, and the count is the failing assertion.

With the count in hand it reads the code path. In this codebase the data layer is explicit about loading: `Releases.all/1` is `assoc(package, :releases)` through `Repo.all`, `Releases.preload/2` maps each key through `preload_field/2`, `Packages.preload/1` loads `[:downloads, :releases]`, and `Packages.attach_latest_releases/1` uses an `inner_lateral_join` on a subquery so a page of packages gets its latest release in one query rather than one per package. An N+1 here is usually a template or view that reaches into an association the controller never preloaded, or an `Enum.map` over a list that calls `Repo.get` or `Repo.preload` inside the loop. The fix follows the pattern already in the file: preload the association on the list in the controller, move the per-row lookup into a `join` with `preload`, or add a lateral join like the one `attach_latest_releases/1` uses when only the newest related row is needed.

It runs the new test until the count is what the page needs, then the whole suite with `mix test`, `mix format --check-formatted` and `mix compile --warnings-as-errors`, because the CI gates both. The pull request keeps the test, so the count cannot climb back without a failing build, and its description quotes the before and after query lists from `capture_queries/1` for the route.

## Start it with one API call

`POST https://api.prix.dev/o/{org}/sessions`

```json
{
  "agent": "claude",
  "input": "An alert that follows this prompt names a slow route in this Phoenix app. Reproduce it with a test that uses capture_queries/1 from test/support/test_helpers.ex around a request to the route and counts the queries per table. Find the N+1 in the controller, context or template, and fix it with a preload, a join with preload, or a lateral join in the style of Packages.attach_latest_releases/1, whichever fits. Keep the test with the corrected count asserted. Run mix test, mix format --check-formatted and mix compile --warnings-as-errors, and open a pull request whose description quotes the query list before and after.",
  "resources": [
    {
      "url": "https://github.com/your-org/your-app"
    }
  ]
}
```

1. **Get an API key.** In the console, open Settings, then API keys, and create a key for your org. Send it in the Authorization header as a Bearer token.
2. **Send the request.** POST the body in request.json to https://api.prix.dev/o/{org}/sessions, with your repository as the resources URL. The run starts in its own cloud sandbox.
3. **Read what came back.** Follow the run on its live stream or wait for the run.completed webhook, then read the result and its trajectory.

## What comes back

What comes back is a pull request on its own branch with two kinds of change: the fix, in the controller or context module that loads the list, and a test in the matching `test/hexpm_web/controllers` file that wraps the request in `capture_queries/1` and asserts how many queries hit the table that was being queried per row. The description names the route from the alert, quotes the captured query list before the fix, with the repeated `SELECT` visible, and after it, and explains which loading strategy it chose and why.

A reviewer checks that the assertion counts queries by table rather than in total, so an unrelated query added later does not break it for the wrong reason, and that the preload does not load more than the page renders, since this codebase has tests that fail when a page queries data it never shows. The session's transcript keeps the failing test run, the passing one, the full suite and the format and compile checks in order.

If the route's query count is already what the page needs, the agent says so in its final answer with the captured list, rather than inventing a fix. The alert may be about something other than queries, and the transcript shows what was ruled out.

## Run it on every p95 latency alert

Add an automation to the Project with a `webhook` trigger and this prompt. Grafana contact points can send a custom `Authorization` header, so a Grafana alert on the route's p95 latency can call the trigger's URL directly with `Bearer <its secret>`; the alert body, with the route label, lands after the prompt as the event. Each firing starts one run, which opens one pull request. A redelivery that carries the same `Idempotency-Key` starts nothing new, and the automation's `max_runs_per_hour` keeps a flapping alert from starting a run a minute.

An alerting service that cannot set the header, like Sentry's performance alerts, posts to a handler on your server instead, which forwards the body to the trigger URL with the header. Either way the agent reads the alert as data after your prompt, never as instructions.

## Why run it on Rush?

An N+1 is cheap to fix and expensive to find, and the finding needs a database full of rows, a request against the real route and a way to count what Ecto sent. The agent does that in its own sandbox with Postgres running, using the query-capture helper the project already has, and leaves a test behind so the count stays fixed. Nobody has to keep a local database in the right shape for an afternoon.

The pull request carries its own evidence: the captured query list before and after, the test that asserts the count, and the suite, format and compile output in the session. Several alerts can each start their own run, each on its own branch, and a reviewer reads a diff with a number attached instead of a hunch.

## Frequently asked questions

### The alert is about latency. How does the agent know it is an N+1?

It does not assume. The first step is `capture_queries/1` around a request to the route, which returns every SQL statement the request issued. A list with the same `SELECT` repeated once per row is an N+1; a list with a few queries means the time is elsewhere, and the agent says so with the list instead of changing the loading code.

### Will it preload everything to be safe?

No, and this codebase would catch it: `package_controller_test.exs` asserts that the package page never queries `audit_logs` and counts `package_dependants` queries exactly. The fix loads what the template renders and nothing more, and the new test asserts a count, not an upper bound, so over-loading fails too.

### Why a lateral join instead of a preload?

A preload loads every related row; when the page needs only the newest one per parent, that is a second large query. `Packages.attach_latest_releases/1` already solves this with an `inner_lateral_join` on a `latest_release` subquery, and the agent reuses that shape when the alerting route has the same need.

### Won't a query-count test break whenever we add a query?

Only if the new query hits the counted table, because the assertion filters the captured list by `FROM "table"` the way the existing tests do. Adding an unrelated query leaves the count alone; adding one more per row fails it, which is the point.