# Add Database Indexes to GORM Models

Yes. The agent lists every query GORM sends, adds index tags for the columns they filter on, proves each with a query plan and tests the unique ones.

[Run this on Rush](https://cloud.getrush.ai/run/add-database-indexes/gorm/). 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, because an index nobody filters on only slows writes down. In a GORM app like realworld-apps/golang-gin-realworld-example-app (gorm.io/gorm v1.25.12 on `gorm.io/driver/sqlite`), the models live in `articles/models.go` and `users/models.go`, and `Migrate` in `hello.go` calls `AutoMigrate` for each of them at startup. The schema that produces has three unique indexes (`article_models.slug`, `tag_models.tag`, `user_models.email`) and a `deleted_at` index on the six tables that embed `gorm.Model`. No foreign key column has one: not `author_id` or `article_id`, not `favorite_id` or `favorite_by_id`, not `following_id`, `followed_by_id` or `user_model_id`.

Then it matches columns to callers. `GetArticleFeed` and the author filter in `FindManyArticle` select on `author_id`; `BatchGetFavoriteCounts` and `BatchGetFavoriteStatus` on `favorite_id` and `favorite_by_id`; `getComments` on `article_id`; `GetFollowings` on `followed_by_id`; and `GetArticleUserModel`, which the handlers and the article serializer call for the signed-in user, on `user_model_id`. The agent builds the app, lets `AutoMigrate` create a scratch database and asks SQLite for `EXPLAIN QUERY PLAN` on each statement. On the empty database every one of them is answered with `idx_..._deleted_at (deleted_at=?)`. That looks like an index hit and is not one: the soft-delete scope adds `deleted_at IS NULL` to each query, which matches every live row. So the agent seeds the tables and runs `ANALYZE`, and with statistics to go on the planner reports what is really happening: `SCAN article_models`, `SCAN favorite_models`, `SCAN comment_models` and the same for the other three. The `Preload("Tags")` lookup is a `SCAN article_tags` either way, because the join table's primary key starts with `tag_model_id` and the preload filters on `article_model_id`.

The change itself is struct tags: `gorm:"index"` on the single columns and a named composite, `index:idx_favorite_pair,priority:1` and `priority:2`, on the favorite pair. `AutoMigrate` creates an index that is missing the next time the app starts, on a database that already holds rows, so there is no migration file to write here. It does not rebuild an index whose name already exists, which is why the agent picks final names once. It also runs `gofmt -w`: adding a tag to some fields of a struct changes the alignment of the others, `gofmt -l .` then lists `articles/models.go`, and the repository's CI fails on that through `scripts/gofmt.sh`.

The unique index is where this job goes wrong. A favorite should be unique per article and user, and a plain `uniqueIndex` on the pair passes the whole existing suite. It also breaks the product. `unFavoriteBy` soft-deletes the row, `favoriteBy` uses `FirstOrCreate`, which cannot see a soft-deleted row and inserts a new one, and the insert now fails with `duplicated key not allowed`. No test in `articles/unit_test.go` favorites, unfavorites and favorites again, so nothing fails until a user does it. The agent writes that test first, then declares the index partial with `where:deleted_at IS NULL`, which keeps one live row per pair and lets deleted ones pile up beside it. The same reasoning is applied to `follow_models`.

## Start it with one API call

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

```json
{
  "agent": "claude",
  "input": "Add the missing database indexes to the GORM models in this repository. Read go.mod, every model file, every function that builds a query (Where, Joins, Preload, Association, Order, Group), the place AutoMigrate is called, the test setup and the CI workflow first. Run the tests and the format check before changing anything and record the result. Start the app once against a scratch database so AutoMigrate creates the schema, list the indexes it has, seed enough rows to make plans meaningful, run ANALYZE, and capture EXPLAIN QUERY PLAN for every query the code sends. Add index tags only for columns a query filters, joins or orders on, and say which function each one serves. Treat a plan that uses only the deleted_at index as a scan. For any unique index on a model that embeds gorm.Model, first write a test that deletes a row and creates the same pair again, then make the index partial on deleted_at IS NULL so the test passes; do not switch soft delete to hard delete. Do not rename or drop existing indexes. Run gofmt -w on changed files. Start the app against a copy of the scratch database that already has rows and confirm the new indexes are created. Capture the query plans again. Open a pull request with the before and after plan for each query, the list of indexes added with their callers, and the test and format output.",
  "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 that touches the model files and the tests and nothing else. Its description is a table with one row per query: the function that sends it, the plan before, the index added and the plan after. Queries that needed no index are listed too, with the reason, for example the tag filter in `FindManyArticle`, which already searches the join table's primary key by `tag_model_id`.

Below the table come the indexes that were considered and left out, the new tests for the unique pairs, and the output of `go test ./...` and `scripts/gofmt.sh` from before and after. Any plan that did not change is stated as such. On an empty database with no statistics SQLite can go on choosing the `deleted_at` index for a query after a better one exists, which is why the plans in the description come from the seeded, analyzed database and say how many rows it held.

A reviewer checks two things before merging. First, that every new unique index is partial and has the delete-then-recreate test beside it. Second, how the indexes reach production: with `AutoMigrate` they are built when the new version starts, so on a large table the first start takes as long as the index build, and on a database with existing duplicates a unique index fails to build at all. The description gives the query that finds such duplicates.

## Run it on every Linear issue labelled slow-query

Create an automation in the Project that holds the repository, with this prompt and a `linear` trigger on `linear.issue.labeled` for the label your team uses. The trigger puts the issue after the prompt as data and fills in no placeholder, so write the prompt to say that the issue names the slow endpoint or query and that the agent should index for that one first. The trigger needs a Linear connection for the member the automation runs as, made in the console under Settings → Connections.

The automation's own cap, max runs per hour, decides how many of these start; an issue past the cap waits as a queued run. Two runs that add tags to the same struct will conflict, so merge one before the next is reviewed. To start a run from a script instead, send this body to `POST /o/{org}/sessions` with a `request_id` that is a UUID v5 of the issue id, so a retried call returns the run it already started.

## Why run it on Rush?

An index is a claim about a query plan, and the claim is only worth something when the plan was actually read. In its own sandbox the agent builds the app, creates the database the way production does, seeds it and runs `EXPLAIN QUERY PLAN` before and after, and each command with its output stays in the transcript at `GET /o/{org}/sessions/{id}/messages`. A reviewer who doubts a row in the table can find the statement that produced it.

It also needs a database nobody is using. The run works on a scratch file inside its sandbox, so nothing touches a developer's local `data/gorm.db` or a shared staging database, and several issues can be worked at once, each in a separate checkout with its own data.

## Frequently asked questions

### Does AutoMigrate really create indexes on a table that already has data?

Yes. It compares the model with the database and creates what is missing, indexes included, without touching existing rows. It does not drop an index you removed from the model or rebuild one whose definition changed under the same name, so those two cases need a manual statement or a migration tool.

### Why not a plain unique index on the favorite pair?

Because the models soft-delete. An unfavorite leaves the row in the table with `deleted_at` set, and the next favorite inserts a second row with the same pair, which a plain unique index refuses. A partial index on `deleted_at IS NULL` enforces uniqueness among live rows only.

### Does the partial index work on MySQL?

No. SQLite and PostgreSQL support a `WHERE` clause on an index and GORM passes the `where` tag option through to them. MySQL has no partial indexes, so there the agent reports the options instead: hard-delete the join rows with `Unscoped()`, or restore the soft-deleted row in place of inserting a new one.

## More GORM jobs

- [Add Database Migrations to a GORM App](https://getrush.ai/run/add-database-migrations/gorm/)
- [Add OpenTelemetry Tracing to GORM Queries in a Go Service](https://getrush.ai/run/add-opentelemetry/gorm/)
- [Add Optimistic Locking to a GORM Model in a Gin API](https://getrush.ai/run/add-optimistic-locking/gorm/)

## The same job on other stacks

- [Add Database Indexes to a Mongoose App](https://getrush.ai/run/add-database-indexes/mongoose/)
- [Add Database Indexes to a Prisma Schema](https://getrush.ai/run/add-database-indexes/prisma/)
- [Add Database Indexes to a Sequelize App](https://getrush.ai/run/add-database-indexes/sequelize/)

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