All jobs

Add Database Indexes to a SQLModel App With Alembic

Yes. An agent finds the unindexed filters and sorts in a SQLModel app, adds the indexes to the models and an Alembic migration, 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

The agent works from the queries the code runs, not from a list of columns that look important. In a project like fastapi/full-stack-fastapi-template, backend/app/api/routes/items.py has the pattern worth checking: for an ordinary user, read_items selects from Item where owner_id equals the caller, orders by created_at descending, and applies offset and limit, and it runs a select(func.count()) with the same filter on every call. backend/app/models.py declares owner_id with foreign_key="user.id" and ondelete="CASCADE" and no index=True. PostgreSQL indexes the referenced side of a foreign key, the primary key of user, and never the referencing column, so the item table has its primary key index and nothing else. The same column is what delete_user filters on, and what the cascade has to search each time a user row is removed.

It confirms that against a database before proposing anything. The sandbox has no Docker, so in place of the db service in compose.yml the agent installs PostgreSQL from the system packages, creates the app database, sets DATABASE_URL, and runs uv run bash scripts/prestart.sh to apply the five existing Alembic revisions. It seeds enough rows for the planner to have a choice and runs EXPLAIN on the statements SQLAlchemy emits. On a table seeded with 50,000 items across 500 owners while this page was written, the owner's list was a Seq Scan on item followed by a sort, and the count was a second Seq Scan.

The index goes on the model, so the schema has one source. SQLModel's Field(index=True) covers a single column; an index on two columns with a direction is declared through SQLAlchemy, as __table_args__ = (Index("ix_item_owner_id_created_at", "owner_id", desc("created_at")),) on Item. From backend/, alembic revision --autogenerate detects it, because app/alembic/env.py uses SQLModel.metadata as its target, and writes an op.create_index call whose second column is sa.literal_column('created_at DESC'). The agent then edits the migration for production use: a plain CREATE INDEX blocks writes to the table while it builds, so the upgrade runs inside op.get_context().autocommit_block() with postgresql_concurrently=True, which Alembic needs because PostgreSQL refuses to build an index concurrently inside a transaction. The downgrade drops it the same way.

With the migration applied, the agent repeats the EXPLAIN runs; in the seeded table above both statements switched to scanning the new index. It runs alembic check, which should report no pending operations once model and database match, then the repository's own checks: uv run bash scripts/tests-start.sh for pytest under coverage, and scripts/lint.sh for mypy in strict mode, ty and ruff, which accept __table_args__ on a SQLModel class. It does not add an index for the superuser branch of read_items, which sorts every item by created_at with no filter, unless the plans show that query matters; the pull request says which statements were examined and which were left.

Start it with one API call

POST https://api.phoenix.so/o/{org}/sessions with this body:

{
  "agent": "claude",
  "input": "Find and add the missing database indexes in this FastAPI and SQLModel backend. Read backend/app/models.py, every query in backend/app/api/routes/ and backend/app/crud.py, backend/app/alembic/env.py and its versions, backend/scripts/ and .github/workflows/test-backend.yml first. List each query with its WHERE and ORDER BY columns and which existing index, if any, serves it. There is no Docker here: install PostgreSQL from the system packages, create the app database, set DATABASE_URL and run uv run bash scripts/prestart.sh. Seed enough rows for realistic plans (tens of thousands of items across hundreds of owners), capture EXPLAIN for each candidate query, and delete the seed data afterwards. Declare each index on the model: Field(index=True) for one column, __table_args__ with sqlalchemy Index for composites, with the column order and direction the query needs. Generate the migration with alembic revision --autogenerate from backend/, then change it to build with postgresql_concurrently=True inside op.get_context().autocommit_block(), and drop the same way on downgrade. Apply it, capture EXPLAIN again, run alembic check, uv run bash scripts/tests-start.sh and scripts/lint.sh. Add only indexes the plans justify. Open a pull request with the query list and the before and after plans.",
  "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 a few lines in backend/app/models.py and one new revision under backend/app/alembic/versions/. The description is where the work shows: a table of every query in the routes with its filter and sort columns, the index that serves it or the note that none does, and for each index added the EXPLAIN output before and after, copied from the sandbox. It states how much data the plans were measured on, because a plan from a seeded table is evidence about shape, not a promise about production timings, and it carries the pytest and lint output.

A reviewer checks the column order first: owner_id leads because the query filters on it with equality, and created_at follows in the direction of the sort. They read the migration for the concurrent build and consider their deploy. The deploy workflows run backend/scripts/prestart.sh, which calls alembic upgrade head, as a step of each release, so on a large table the first deploy with this revision waits for the build to finish; the description says so and suggests running the upgrade ahead of the release if that wait matters. They also weigh the write cost: every insert and delete on item now maintains one more index, which is the price of the faster read.

Run it on every slow-query alert

If slow statements already raise an alert, that alert can start the run. Add an automation in the console on the Project that holds the backend, with a webhook trigger and this prompt narrowed to one instruction: find the query named in the alert, check its plan and add the index it needs. A Grafana contact point can call the trigger's URL directly, since it can send the Authorization: Bearer header carrying the trigger's secret; the alert body, up to 64 KB, is added after the prompt as the event. A monitoring tool that cannot set that header posts to a small handler on your server, which forwards the body with the secret.

Alerts repeat while a problem lasts, so set the automation's max runs per hour low. Events past the cap wait as queued runs for the next hour, and beyond one more hour's worth they are refused, which keeps a flapping alert from opening a pull request per firing.

The use case this job follows, with its automation and trigger: Fix a bug from an alert.

Why run it on Rush?

An index is a claim about a query plan, and the only honest way to make it is to look at the plan. That takes a PostgreSQL with the real schema and enough rows, which most people do not want to build and tear down on their own machine for one migration. The agent has a sandbox for it: it installs the database, applies the repository's migrations, seeds, measures and cleans up, and the run keeps each command with its output. The plans in the pull request are the ones that database printed.

It also makes the dull half routine. The agent reads every query, not just the one someone complained about, so the pull request can say which statements are already served and which were left alone on purpose. A reviewer gets the inventory along with the fix, and the next slow-query alert starts from a list and not from a guess.

Frequently asked questions

Doesn't a foreign key already create an index?

Not on the side that needs one here. PostgreSQL requires a unique index on the referenced column, which the primary key provides, and creates none on the referencing column. Filtering item by owner_id, and cascading a user's deletion to their items, both scan the table until an index on owner_id exists.

Why declare the index in the model and not only in a migration?

Because alembic revision --autogenerate compares the models with the database. An index that exists only in a migration looks like drift on the next autogenerate, which would propose dropping it. Declared in __table_args__, it is part of the metadata and alembic check stays clean.

Will creating the index lock my table?

A plain CREATE INDEX blocks inserts, updates and deletes on the table until it finishes. The migration in the pull request builds concurrently, which allows writes during the build and takes longer. If a concurrent build is interrupted it can leave an invalid index behind, which has to be dropped before the migration is run again.

More SQLModel jobs

The same job on other stacks

Keep reading