Fix N+1 Queries in a Phoenix App

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.

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.

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.

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.

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

Will it preload everything to be safe?

Why a lateral join instead of a preload?

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