# Add Database Indexes to a sqlx Project

Yes. An agent reads every query! macro against the migrations, adds only the indexes the WHERE clauses need, shows the plans before and after, and opens a PR.

[Run this on Rush](https://cloud.getrush.ai/run/add-database-indexes/sqlx/). Opens the console with this prompt ready to send, in a Project that has your repository.

## What the agent does

The agent starts from the queries, not from the tables, because an index that no query uses is a write cost with no reader. In a project laid out like LukeMathWalker/zero-to-production, every query is a `sqlx::query!` macro checked at compile time, and the schema is thirteen SQL migrations in `migrations/`. `subscriptions` has a primary key on `id` and a `UNIQUE` on `email`; `subscription_tokens` keys on `subscription_token` and references `subscriptions (id)` through `subscriber_id` with no index; `issue_delivery_queue` keys on `(newsletter_issue_id, subscriber_email)`; `idempotency` keys on `(user_id, idempotency_key)`; `users` has a unique `username`. The queries that read them are in `src/routes/admin/newsletter/post.rs` (`SELECT $1, email FROM subscriptions WHERE status = 'confirmed'` into the queue), `src/issue_delivery_worker.rs` (`SELECT … FROM issue_delivery_queue FOR UPDATE SKIP LOCKED LIMIT 1`, then a delete by both key columns), `src/routes/subscriptions_confirm.rs` (a token lookup by primary key and an update by `id`), `src/authentication/password.rs` (by `username`) and `src/idempotency/persistence.rs` (by both key columns). `.sqlx/` holds 18 offline query files, CI runs `cargo sqlx prepare --workspace --check -- --all-targets`, and the Dockerfile builds with `SQLX_OFFLINE=true`.

Matching each `WHERE` to an index leaves two gaps and one judgement. The gap that grows with the subscriber list is `subscriptions.status`: publishing an issue scans the whole table for `'confirmed'` rows, so the migration adds a partial index, `CREATE INDEX subscriptions_confirmed_idx ON subscriptions (id) WHERE status = 'confirmed'`, which is small because unconfirmed rows are left out. The second is the foreign key `subscription_tokens.subscriber_id`, which Postgres does not index on its own; nothing queries it today, but a future delete of a subscription has to check it, so the agent adds a plain index and says it is for the constraint, not a query. The judgement is the delivery queue: `FOR UPDATE SKIP LOCKED LIMIT 1` with no `WHERE` reads the first unlocked row, and the primary key already serves the delete, so no index is added there and the description says why.

The migration is a new file in `migrations/` named by `sqlx migrate add add_indexes`, using `CREATE INDEX IF NOT EXISTS` wrapped in a `BEGIN`/`COMMIT` the way `20210307184428_make_status_not_null_in_subscriptions.sql` does, because sqlx does not wrap a migration in a transaction by itself. No query text changes, so the `.sqlx/` files are unchanged in content, but the agent runs `cargo sqlx prepare --workspace -- --all-targets` after migrating and shows `git status` clean under `.sqlx/`, so CI's `--check` step will pass.

In its sandbox the agent starts Postgres with `SKIP_DOCKER=true ./scripts/init_db.sh` against a local server, runs `cargo test` to see the 25 integration tests green, each of which creates its own database and runs the migrations through `sqlx::migrate!`, so the new file is exercised 25 times. It then loads 200,000 subscription rows with a tenth confirmed into a scratch database, runs `EXPLAIN (ANALYZE, BUFFERS)` on the newsletter insert-select before and after the migration, and quotes both plans: a sequential scan on the whole table, then an index scan on the partial index with a fraction of the buffers. It drops the scratch database and quotes `cargo test`, `cargo clippy -- -D warnings` under `SQLX_OFFLINE=true` and `cargo fmt --check`, the three CI jobs.

## Start it with one API call

`POST https://api.phoenix.so/o/{org}/sessions`

```json
{
  "agent": "claude",
  "input": "Add the database indexes this sqlx project needs. Read every migration in migrations/, every sqlx::query! in src/, the .sqlx directory, scripts/init_db.sh and .github/workflows/general.yml first. Match each WHERE clause to an existing key or index and list the gaps. Write one new migration with sqlx migrate add that creates a partial index on subscriptions for status = 'confirmed' and an index on subscription_tokens.subscriber_id, inside BEGIN and COMMIT, using IF NOT EXISTS; explain why the delivery queue needs none. Start Postgres with SKIP_DOCKER=true ./scripts/init_db.sh, run cargo test, then load 200000 subscriptions with ten percent confirmed into a scratch database and quote EXPLAIN ANALYZE for the newsletter insert-select before and after the migration. Run cargo sqlx prepare --workspace -- --all-targets and show .sqlx is unchanged, then cargo clippy -- -D warnings with SQLX_OFFLINE=true and cargo fmt --check. Open a pull request quoting the plans and the three checks.",
  "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.phoenix.so/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 with one new file, the migration, and nothing else: no Rust changes, because no query text changed, and no `.sqlx/` changes, which the description proves with a clean `git status` after `cargo sqlx prepare`. The description holds a table of every `WHERE` clause in `src/`, the key or index that serves it, and the two gaps the migration fills.

The two `EXPLAIN ANALYZE` plans are quoted in full: the sequential scan with its row count and buffers, then the index scan on `subscriptions_confirmed_idx` with its numbers, followed by `cargo test` with all 25 tests passing, and `clippy` and `fmt` clean.

Before merging, a reviewer decides whether the index on `subscription_tokens.subscriber_id` is wanted now or when a delete path exists, since the agent added it for the foreign key check rather than for a query, and whether the partial index should also cover the `subscribed_at` column for a future export ordered by date.

## Run it on every Linear issue labelled agent

Slow queries get filed as issues once a table is large enough to notice, so a team routes them by label. An automation in the service's Project with the prompt above and a `linear` trigger on `linear.issue.labeled` for the label `agent` starts a run when someone files "publishing a newsletter takes a minute" and labels it. The trigger adds the issue after the prompt, so the run reads which path the issue names and plans that query first.

Each run opens its own pull request on its own branch and links the issue. A table that is already indexed for the query gets a description quoting the plan and no migration, because the run reads `EXPLAIN` before it writes `CREATE INDEX`.

## Why run it on Rush?

Indexes are cheap to add and expensive to add wrong: one per column is a write tax, and one chosen from the table rather than from the queries is a guess. The work is reading every `WHERE` against every key, and in a sqlx project the queries are in the Rust source as macros, which makes that reading tractable. In a Rush sandbox the agent reads them all, writes only the two the queries need, and shows the plans on a table big enough to matter.

The record of the run keeps both plans and the three CI checks, and the pull request quotes them, so the reviewer sees a sequential scan become an index scan rather than trusting a migration by its name. Across every service a team keeps on sqlx, the same request runs in parallel, one sandbox and one pull request each.

## Frequently asked questions

### Why a partial index on status?

The only query on status asks for 'confirmed' rows, and those are the minority while a list is growing. An index with WHERE status = 'confirmed' holds only those rows, so it is smaller to scan and cheaper to maintain than a full index on the column. The EXPLAIN plans in the pull request show the difference.

### Does the offline query data change?

No. The .sqlx files describe each query's text, parameters and result columns, and no query text changed. The agent runs cargo sqlx prepare after migrating and shows git status clean under .sqlx, which is what CI's prepare --check step verifies.

### Why no index for the delivery queue worker?

Its dequeue has no WHERE clause: FOR UPDATE SKIP LOCKED LIMIT 1 takes the first unlocked row by table order, and the delete that follows uses both primary key columns, which the primary key index already serves. An index there would cost on every enqueue and be read by nothing.

### Why wrap the migration in BEGIN and COMMIT?

sqlx runs a migration file as it is written and does not open a transaction around it, which the repository's own migration 20210307184428 notes in a comment. Two CREATE INDEX statements in one transaction either both land or neither does, so a failure part-way leaves no half-applied migration.

## More sqlx jobs

- [Set Up SQLx Offline Mode So a Rust App Builds Without a Database](https://getrush.ai/run/add-offline-mode/sqlx/)
- [Add an SQLx Offline Query Check to CI](https://getrush.ai/run/add-offline-query-check-to-ci/sqlx/)
- [Add Slow Query Logging to an SQLx + Axum API](https://getrush.ai/run/add-slow-query-logging/sqlx/)

## The same job on other stacks

- [Add Missing Indexes to a Supabase Database](https://getrush.ai/run/add-database-indexes/supabase/)
- [Add Database Indexes to TypeORM Entities](https://getrush.ai/run/add-database-indexes/typeorm/)
- [Add Database Indexes with an Alembic Migration](https://getrush.ai/run/add-database-indexes/alembic/)

## Keep reading

- [How a Rush run works on your repository, from request to pull request](https://getrush.ai/cloud/)
- [Compare cloud agents, agent sandboxes and AI code review](https://getrush.ai/compare/)
- [Rush pricing: Free, Pro and Max](https://getrush.ai/pricing/)
