# Add Missing Foreign Key Indexes With an Ecto Migration

Yes. An agent finds Ecto references with no index, writes a concurrent index migration, tests it up and down against Postgres and opens a pull request.

[Run this on Rush](https://cloud.getrush.ai/run/add-database-indexes/ecto/). Opens the console with this prompt ready to send, in a Project that has your repository.

## What the agent does

The agent reads the schema as the migrations built it. In a Phoenix app like thechangelog/changelog.com there is no `structure.sql`, only 129 files in `priv/repo/migrations`, so it walks them in order and records every `add ... references(...)` and every `create index` or `create unique_index`, including later `alter table` changes and the raw `execute` statements that add GIN indexes. A foreign key is covered when some index starts with its column. Four are not: `news_queue.item_id`, `news_sponsorships.sponsor_id`, `feeds.owner_id`, and `sponsor_reps.person_id`, where the unique index on `[:sponsor_id, :person_id]` covers `sponsor_id` but cannot serve a lookup by `person_id` alone.

Then it checks that each missing index matters. `lib/changelog/schema/news/news_queue.ex` looks entries up with `Repo.get_by(NewsQueue, item_id: item.id)` in three places. `Sponsor` counts news sponsorships with `where: q.sponsor_id == ^sponsor.id`. `Person` declares `has_many :feeds, Feed, foreign_key: :owner_id`. Deletes matter too: `20180510145419_adjust_foreign_key_constraints.exs` makes `news_queue.item_id` and `news_sponsorships.sponsor_id` cascade, `20240319161447_create_feeds.exs` gives `owner_id` `on_delete: :delete_all`, and `20241024142033_move_delete_alls_to_database.exs` does the same for both `sponsor_reps` columns. So every `Repo.delete!(person)` in the admin person controller, and every person `PersonBouncer` removes, has Postgres scan `feeds` and `sponsor_reps` for rows to delete.

The agent reads how migrations reach production before writing one. Each `fly.toml` under `fly.io/` sets `release_command = "on.deploy"` with a `bluegreen` strategy, and `docker/scripts/on.deploy` runs `mix ecto.migrate`, so the migration runs while the current release is serving. It writes one migration with `@disable_ddl_transaction true` and `@disable_migration_lock true` and four `create index(..., concurrently: true)` lines, because `CREATE INDEX CONCURRENTLY` cannot run inside a transaction and does not block writes the way a plain `CREATE INDEX` does. Each index has an explicit name so the down migration drops exactly what the up created.

To prove it, the agent installs PostgreSQL and the Erlang and Elixir versions in `mise.toml`, creates `changelog_test` with the credentials in `config/test.exs`, and runs `MIX_ENV=test mix ecto.migrate`, `mix ecto.rollback --step 1` and `mix ecto.migrate` again so both directions work. It inserts a few rows, runs `EXPLAIN` on each lookup with `SET enable_seqscan = off` to show the planner can use the new index, and runs `mix test`, which migrates through its alias in `mix.exs`.

## Start it with one API call

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

```json
{
  "agent": "claude",
  "input": "Find foreign keys with no index and add them. Read every migration in priv/repo/migrations in order and list each references() column with whether an index starts with it. For each one missing, find the queries and on_delete cascades that use it in lib/. Read fly.io/*/fly.toml and docker/scripts/on.deploy to see how migrations run. Write one migration that creates the missing indexes concurrently with @disable_ddl_transaction and @disable_migration_lock and named indexes. Install Postgres, migrate, roll back and migrate again, show EXPLAIN for each lookup, run mix test, and open a pull request with the table and the 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

What comes back is a pull request that adds one file to `priv/repo/migrations` and changes nothing else. The description opens with a table of every foreign key in the schema: the table and column, the migration that created it, the index that covers it or `none`, and for the four uncovered ones, the query or cascade that reads them with its file and line.

Below it are the migrate, rollback and migrate output from the sandbox, the `EXPLAIN` plan for each lookup showing an index scan on the new index, and the `mix test` result. The pull request says what it cannot know from the repository: how many rows each table holds in production and how long the concurrent builds will take there. It gives the query that answers it, `SELECT relname, n_live_tup, seq_scan FROM pg_stat_user_tables`, for someone with production access.

A reviewer checks that the deploy path really runs migrations outside a transaction, since a concurrent index that fails part-way leaves an `INVALID` index behind that must be dropped by hand, and decides whether to ship the four indexes together or one at a time.

## Run it on every Monday morning

Missing indexes come back whenever someone adds a reference and forgets the index. An automation in the Project with a `schedule` trigger, `cron` `0 8 * * 1` in your `timezone`, can run a narrower prompt every week: read the migrations added since last Monday, and open a pull request only if one of them adds a `references` column with no index. A week with nothing new ends with a one-line answer and no branch.

From a CI job, the same check is one `POST /o/{org}/sessions` with this body and the repository in `resources`, started when a pull request touches `priv/repo/migrations`. The key's webhook URL receives `run.completed` with the `session_url`, so the job can post the answer back to the pull request.

## Why run it on Rush?

Finding missing foreign key indexes from 129 migrations is a bookkeeping job: it is easy to miss a reference added in an `alter table` years later, or a composite index whose first column is not the one you need. The agent reads them all in order, and the run's record keeps the table it built, so a reviewer can check any row against the migration it cites.

A concurrent index migration has to be run to be trusted, because the module attributes and the transaction rules are easy to get wrong. The sandbox has its own Postgres, so the migration runs up, down and up again, and `EXPLAIN` shows the planner using each index, before anyone points it at production.

## Frequently asked questions

### Why does Ecto need both @disable_ddl_transaction and @disable_migration_lock?

`CREATE INDEX CONCURRENTLY` cannot run inside a transaction, so `@disable_ddl_transaction` stops Ecto from wrapping the migration in one. Ecto's migration lock also opens a transaction by default, so `@disable_migration_lock` is needed too, which is what Ecto's documentation recommends for concurrent indexes.

### Doesn't Postgres index foreign keys automatically?

No. Postgres indexes the referenced side, the primary key, but not the referencing column. A lookup or a cascading delete on the referencing column scans the table unless you create the index yourself.

### Why not use a unique index on sponsor_reps.person_id?

One person can represent several sponsors, so `person_id` alone is not unique. The existing unique index on `[:sponsor_id, :person_id]` stays, and the new one is a plain index on `person_id`.

### Will the migration lock the tables during deploy?

A concurrent build takes a lock that allows reads and writes to continue, so the app keeps serving while `mix ecto.migrate` runs in the release command. It does wait for transactions already running on the table to finish before it starts.

## The same job on other stacks

- [Add Database Indexes to GORM Models](https://getrush.ai/run/add-database-indexes/gorm/)
- [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/)
- [Add Database Indexes to a SQLModel App With Alembic](https://getrush.ai/run/add-database-indexes/sqlmodel/)
- [Add Database Indexes to a sqlx Project](https://getrush.ai/run/add-database-indexes/sqlx/)

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