All jobs

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 RushOpens 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 with this body:

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

The use case this job follows, with its automation and trigger: Turn a Linear label into a PR.

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

The same job on other stacks

Keep reading