# Find Slow Database Queries in a Rails App

Yes. An agent seeds your Rails app with realistic data, exercises its pages, logs queries over 50 ms, and writes a report with each slow query, its code and the fix.

[Run this on Rush](https://cloud.getrush.ai/run/investigate-slow-queries/rails/), in a Project that has your repository.

## What the agent does

The agent first reads how the app is put together instead of assuming a stack. In a Rails app like lobsters/lobsters it opens Gemfile.lock and finds rails 8.1.3.1 running on sqlite3 with rspec-rails and rack-mini-profiler, then config/database.yml.sample, which defines four sqlite databases (primary, cache, queue and rack_attack). It copies that sample to config/database.yml, runs bin/setup to install dependencies and create the databases, and checks the Makefile to see that the test suite is bundle exec rspec.

Next it builds the dataset the diagnosis needs. db/seeds.rb only creates a handful of users, so the agent runs bin/rails fake_data, the task in lib/tasks/fake_data.rake that uses Faker to generate users, stories, comments and votes at a realistic volume with BCrypt at minimum cost. With data in place it starts the app the way Procfile.dev does, with bundle exec rails server, and exercises the main routes: / served by home#index, which paginates Story.hottest ordered by the hotness column, plus /hottest, /page/:page, /comments and /search.

While it works through the pages, an ActiveSupport::Notifications subscriber it installs logs every query slower than 50 ms with the SQL and the call site, and config/environments/development.rb already sets config.active_record.verbose_query_logs to attribute each query to a line. For each slow query the agent opens the database with bin/sqlite3, because CONTRIBUTING.md warns that the system sqlite3 gives wrong EXPLAIN QUERY PLAN output when the app compiles its own SQLite with flags in .bundle/config. It traces the statement back to the code that issues it, such as the not_hidden_by and filter_tags scopes in app/models/story.rb, and to db/schema.rb, where the stories table carries a hotness decimal and indexes like hotness_idx.

For each finding it tries the fix in the sandbox: a candidate index or a rewritten scope, then the same page request again with the timer running, so the report can show the milliseconds before and after. Because the prompt says not to open a pull request, nothing is pushed; the migration-ready SQL and any scope rewrite live only in the report.

## Start it with one API call

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

```json
{
  "agent": "claude",
  "input": "Load this app in the sandbox with a realistic amount of seed data, exercise its main pages, log every query over 50 ms, and write slow-queries.md: each query, the code that issues it, its EXPLAIN ANALYZE, and the index or rewrite with the time after. Do not open a pull request.",
  "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 slow-queries.md artifact on the finished session. Each query gets a section with the SQL exactly as Active Record issued it, the file and line of the code that issues it, the EXPLAIN QUERY PLAN output, and the proposed fix: the migration-ready index or the rewritten scope, with the measured time before and the time after the fix.

The session's transcript, at GET /o/{org}/sessions/{id}/messages, keeps every step with its command output: the seed run, each page request, every captured slow query and each explain. That record is the proof behind the report and stays readable after the sandbox is gone.

The repository itself is untouched, since the run was asked for a report and no pull request. Download slow-queries.md from the artifact url on the session; the link needs no key and works for an hour, and reading the session again returns a fresh one.

## Run it on every slow-query alert

Point your alerting at your server: when your APM or your database flags a slow query, your server POSTs this same request with the repository in resources and the alert text in input, and a new run investigates each alert in its own sandbox with its own freshly seeded database. A repository named in resources is cloned through the Rush GitHub App, so the App has to be installed on it first.

Set a webhook URL on the API key and Rush POSTs run.completed or run.failed when each run's turn finishes, with the session URL, so your server can hand the on-call engineer the report link. A key runs at most max_concurrent sessions at once (4 by default) and answers 429 past that, so a burst of alerts should be queued rather than fired in parallel.

## Why run it on Rush?

On a laptop this job pollutes your development database: the fake_data task asks for confirmation before adding to a populated one, the generated volume competes with your own data, and afterwards you have to restore or recreate the database. On Rush the seeded database lives in the run's sandbox and is discarded with it, so your checkout and your dev database are never touched, and the run keeps working when your laptop closes.

The report is checkable rather than claimed: the transcript keeps every command and its output, including each explain plan and the before-and-after timings, so you can replay the agent's reasoning query by query. And because each run is its own sandbox, the same request can check several apps or several services at the same time, one report each.

## Frequently asked questions

### Our app runs on Postgres, and the example app uses sqlite. Does the method carry over?

Yes. The method is the database's explain plan plus measured timings. On Postgres the agent runs EXPLAIN (ANALYZE, BUFFERS) instead of the sqlite plan and reads actual time and buffers. The report keeps the same shape either way: the query, the code that issues it, the plan, the fix and the time after.

### Will it modify my repository?

No. The prompt asks for a report and says not to open a pull request, so no branch or commit is created and nothing is pushed. The index and scope changes appear only as text inside slow-queries.md, ready for you to turn into a migration when you agree with them.

### Where does the realistic seed data come from?

db/seeds.rb gives a minimal admin user, a category and a tag, so the agent runs bin/rails fake_data, the task in lib/tasks/fake_data.rake that generates users, stories, comments and votes with Faker. The task refuses non-development environments and asks before adding to a populated database; in the sandbox it runs against a fresh one every time.

### How do I know a suggested index actually helps?

The report does not list an index without measuring it. The agent applies the candidate index or rewrite in the sandbox, reruns the page or query that produced the slow statement, and records the time after next to the time before, alongside the explain plan that shows the index in use.

### Can I scope the run to one page or endpoint?

Yes, put the route in the prompt. Routes are plain Rails mappings (config/routes.rb sends / to home#index, for example), so asking for only /comments and /search narrows the run to those controllers and the queries they issue.