# 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 Rush](https://cloud.getrush.ai/run/add-database-indexes/sqlmodel/). Opens 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`

```json
{
  "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.

## 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

- [Add Email Verification to a FastAPI App on SQLModel](https://getrush.ai/run/add-email-verification/sqlmodel/)
- [Add Full-Text Search to a SQLModel and FastAPI App](https://getrush.ai/run/add-full-text-search/sqlmodel/)
- [Add Optimistic Locking to SQLModel Updates](https://getrush.ai/run/add-optimistic-locking/sqlmodel/)

## The same job on other stacks

- [Add Database Indexes to a sqlx Project](https://getrush.ai/run/add-database-indexes/sqlx/)
- [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/)

## 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/)
