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 with this body:
{
"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"
}
]
}- 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.
- 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.
- 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.
The use case this job follows, with its automation and trigger: Turn a Linear label into a PR.
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.