All jobs

Add Database Indexes with an Alembic Migration

Yes. An agent finds the queries with no supporting index, adds each index to the model and an Alembic migration, measures both plans and opens a pull request.

Run this on RushOpens the console with this prompt ready to send, in a Project that has your repository.

What the agent does

An index belongs to a query, so the agent starts from the queries and not from the schema. In a project like fastapi/full-stack-fastapi-template it reads backend/app/models.py and every statement under backend/app/api/routes. Item.owner_id is declared with foreign_key="user.id" and ondelete="CASCADE" and no index=True; PostgreSQL requires an index on the referenced side of a foreign key and creates none on the referencing column. read_items in routes/items.py then uses that column twice for every ordinary user: a count() filtered on owner_id, and a page of rows filtered on owner_id, ordered by created_at descending with offset and limit. Deleting a user cascades to their items through the same column. By contrast User.email already has unique=True, index=True. The agent lists each query with the index that would serve it and the ones that need nothing.

It reads how migrations run here before writing one. backend/alembic.ini points at app/alembic; env.py takes its metadata from SQLModel.metadata with compare_type=True and runs every revision inside context.begin_transaction(); there are five revisions ending at fe56fa70289e; and scripts/prestart.sh runs alembic upgrade head before the app starts, so a migration that takes a long lock holds up a deploy. The agent declares the index on the model first, so the metadata and the database agree, then runs alembic revision --autogenerate and edits the result by hand.

The hand edit is the part autogenerate cannot do. A plain CREATE INDEX blocks writes to the table until it finishes, so the agent builds the index with postgresql_concurrently=True, and because PostgreSQL refuses a concurrent build inside a transaction, wraps the operation in op.get_context().autocommit_block(). A concurrent build that is interrupted leaves an invalid index behind, so the migration is written to be rerun safely and the description says how to spot and drop a leftover. The downgrade drops the index the same way. For the list query it weighs a single-column index on owner_id against a composite on owner_id and created_at descending that also serves the ordering, and keeps whichever the measured plans justify.

To measure, it needs the real database engine. The compose file runs postgres:18 and the sandbox has no Docker, so the agent installs PostgreSQL with sudo apt-get install -y postgresql, notes the version it got, points DATABASE_URL at it and runs alembic upgrade head. It loads enough users and items for the planner to have a choice, runs EXPLAIN (ANALYZE, BUFFERS) on both statements from read_items before and after the migration, then runs alembic downgrade -1, alembic upgrade head and alembic check to prove the revision reverses and the models show no drift. Last it runs the backend tests with bash scripts/tests-start.sh, the same script the test-backend.yml workflow calls.

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 backend is missing and open a pull request. Read backend/app/models.py, every query in backend/app/api/routes and backend/app/crud.py, backend/alembic.ini, backend/app/alembic/env.py, the existing revisions, backend/scripts and .github/workflows/test-backend.yml first. List every filter, join, order and cascade the code relies on, and for each one say which existing index serves it or that none does. For each missing index: declare it on the SQLModel model, generate a revision with alembic revision --autogenerate, then edit it so the index is created with postgresql_concurrently=True inside an autocommit block, is safe to rerun after an interrupted build, and is dropped concurrently in downgrade. Do not add an index no query in the code uses. Install PostgreSQL in the sandbox, set DATABASE_URL, run alembic upgrade head, load enough rows that the planner has a real choice, and capture EXPLAIN (ANALYZE, BUFFERS) for each affected query before and after. Run alembic downgrade -1, alembic upgrade head and alembic check, then the backend tests with scripts/tests-start.sh. In the pull request description give the query list, both plans for each query, the PostgreSQL version used, how many rows were loaded, and what to do if a concurrent build is interrupted in production.",
  "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

A pull request with two changed files per index: the model, where the index is declared, and one new revision under backend/app/alembic/versions whose down_revision is the current head. The description opens with the query list, each line naming the route, the statement and the index that serves it. Under it are the plans, before and after, for the statements the new index is for, with the PostgreSQL version and the number of rows loaded stated so nobody mistakes sandbox timings for production ones.

A reviewer reads the revision itself, since it is short: that the create runs outside a transaction, that the downgrade mirrors it, and that the index name matches what the model generates, or the next autogenerate will propose dropping and recreating it. They check that alembic check passed after the upgrade. And they judge the one thing the sandbox cannot: the plans show the planner chooses the index on synthetic rows, while the size and skew of the real table decide how much it helps and how long the concurrent build runs.

The description ends with operating notes: the migration runs from prestart.sh at deploy, a concurrent build waits for transactions already open on the table, and the query to find an invalid index if the build was cut short.

Run it on every Linear issue labelled slow-query

Slow endpoints usually surface as a ticket. In the console, add an automation to the Project with this prompt and a linear trigger on linear.issue.labeled for the label slow-query. The trigger needs a Linear connection for whoever the automation runs as, made under Settings → Connections, and it fires only when the person adding the label is in your Rush org. The issue is placed after the prompt as context and fills in no placeholder, so the prompt should tell the run to start from the endpoint the issue names.

Each labelled issue then gets its own run and its own pull request with measured plans. The automation's max runs per hour applies across all of them; an issue labelled past the cap waits as a queued run.

The use case this job follows, with its automation and trigger: Hand off a long task.

Why run it on Rush?

Deciding on an index properly takes a database of the right engine with enough rows in it, the migration applied and reverted, and the query plans captured at each step. On a laptop that means disturbing a development database someone is using, or building a second one. The agent has a sandbox to itself: it installs PostgreSQL, loads and discards data freely, and every alembic and EXPLAIN command it ran is kept with its output in the run's record.

What comes back is an argument, not just a migration. The pull request ties each index to a line of code that needs it and shows the plan change, and the transcript behind it, from GET /o/{org}/sessions/{id}/messages, lets a sceptical reviewer see the raw planner output.

Frequently asked questions

Why does CREATE INDEX CONCURRENTLY fail in an Alembic migration?

Because Alembic runs each revision in a transaction and PostgreSQL does not allow a concurrent index build inside one. Wrapping the operation in the migration context's autocommit_block() runs that statement outside the transaction. The agent writes it that way and verifies it by applying the revision.

Does PostgreSQL index foreign key columns automatically?

No. It requires an index on the referenced column, which a primary key or unique constraint provides, and creates nothing on the referencing column. Filters on that column, joins through it and cascading deletes all scan the table until you add one.

Will autogenerate pick up an index I add to a SQLModel field?

Yes, when env.py uses the models' metadata as its target, as it does in this layout. It emits a plain create_index, though, with no concurrency and no rerun safety, which is why the agent edits the generated revision instead of committing it as is.

Does it work with MySQL or SQLite?

The survey of queries and the model change carry over. The concurrent build is PostgreSQL syntax, so on another engine the agent writes that engine's equivalent or a plain create, and says what locking to expect. It measures on the engine the project deploys to, not a substitute.

More Alembic jobs

The same job on other stacks

Keep reading