# Add Database Indexes to a Prisma Schema

Yes. An agent reads every query your routes make through Prisma, adds the @@index lines they need, generates the migration, proves it with EXPLAIN, and opens a PR.

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

## What the agent does

The agent reads the queries before the schema, because an index that no query uses costs writes and helps nothing. In an API laid out like the `orm/express` example in prisma/prisma-examples, `src/index.ts` is one Express 5 file with eight routes on a `PrismaClient` built with `@prisma/adapter-pg` over a `pg` pool. `GET /feed` filters `published: true`, optionally adds an `OR` of `title contains` and `content contains`, includes the author and orders by `updatedAt`; `GET /user/:id/drafts` filters `authorId` and `published: false`; `PUT /post/:id/views` increments `viewCount` by primary key; `/signup` creates a user with nested posts; `/post` connects an author by `email`. `prisma/schema.prisma` has two models: `User` with `email @unique`, and `Post` with `createdAt`, `updatedAt @updatedAt`, `title`, optional `content`, `published` defaulting to false, `viewCount` and an optional `authorId` relation to `User`. It declares no `@@index` at all. `package.json` pins `prisma` and `@prisma/client` at 7.0.0, the generator is `prisma-client` with `output = "./generated"`, and `prisma.config.ts` sets the schema path, the migrations directory, the `tsx prisma/seed.ts` seed command and the datasource URL from `DATABASE_URL`.

On PostgreSQL that schema means one thing in particular: `authorId` is a foreign key, and PostgreSQL does not index foreign key columns on its own, so `/user/:id/drafts` and the `include: { author: true }` join in `/feed` scan `Post` as the table grows, and so does the cascade check when a `User` row is deleted. The agent writes three indexes into `schema.prisma`: `@@index([authorId, published])` for the drafts route, `@@index([published, updatedAt])` so the feed's filter and sort are served by one index in either direction, and nothing for `title` or `content`, because `contains` becomes `LIKE '%…%'`, which no B-tree index can serve; the pull request names a trigram or full-text index as the separate change that would. It leaves `viewCount` and `createdAt` alone, since no route filters or orders by them.

It then runs `npx prisma migrate dev --name add-post-indexes --create-only` against a sandbox PostgreSQL so the SQL is written to `prisma/migrations/` without being applied blind, reads the generated `CREATE INDEX` statements and their generated names (`Post_authorId_published_idx`, `Post_published_updatedAt_idx`), and applies them with `npx prisma migrate deploy`. With the seed from `prisma/seed.ts` loaded and a few thousand extra posts generated in the sandbox, it runs `EXPLAIN (ANALYZE, BUFFERS)` through `prisma.$queryRaw` for the feed and drafts queries before and after, so the plan change from a sequential scan to an index scan is in the description with the row counts it was measured on.

Because the example has no test runner, the agent adds a small `prisma/indexes.test.ts` run with `node --test` and `tsx`: it queries `pg_indexes` for the `Post` table and asserts both index names exist, so a later migration that drops one fails in CI. It runs `npx prisma generate`, `npx prisma validate` and `npx tsc --noEmit` against `tsconfig.json`, and checks that `npx prisma migrate diff --from-migrations prisma/migrations --to-schema-datamodel prisma/schema.prisma` reports no drift, before opening the pull request.

## Start it with one API call

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

```json
{
  "agent": "claude",
  "input": "Add the database indexes this Prisma schema needs. Read package.json, prisma.config.ts, prisma/schema.prisma, prisma/seed.ts, prisma/migrations and every route in src/index.ts first, and list for each route the columns it filters and orders by. Add @@index attributes to schema.prisma only for columns a route filters or sorts on, including the authorId foreign key, and explain in the pull request why the contains searches get no B-tree index. Create the migration with prisma migrate dev --create-only, read the SQL, apply it with prisma migrate deploy to a PostgreSQL in your sandbox, seed it, add enough posts to make the plans meaningful, and record EXPLAIN ANALYZE output for the feed and drafts queries before and after. Add a test that asserts the index names exist in pg_indexes, run prisma validate, prisma generate, tsc --noEmit and prisma migrate diff to confirm no drift, and open a pull request. Do not change any model, field or route.",
  "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

The pull request changes `prisma/schema.prisma`, adds a timestamped `prisma/migrations/…_add_post_indexes/migration.sql` with two `CREATE INDEX` statements, and adds `prisma/indexes.test.ts` with a `test` script in `package.json`. Its description is a table of routes to columns to index, the migration SQL, the before and after `EXPLAIN ANALYZE` plans with their row counts, and the `prisma migrate diff` output showing the schema and migrations agree.

A reviewer checks that the index column order matches how the routes query (equality columns first, the sort column last), decides whether `title` and `content` search deserves a `pg_trgm` index or a `tsvector` column as a follow-up, and confirms the migration can run on the production table size without `CONCURRENTLY`, which Prisma's migration runner does not add on its own.

## Run it on every new query in a pull request

Indexes drift the moment a route changes, so the run worth repeating is the review of each new query. An automation on the Project with a `github` trigger on `pull_request.opened` and the prompt "if the pull request adds or changes a Prisma query, list the columns it filters and orders by, check schema.prisma for a matching index, and comment with the @@index line to add or the reason none is needed" starts one run per pull request, with the event appended after the prompt. Only an owner, member or collaborator fires it.

A team with several Prisma services sends this body once per repository from its own server, each a `POST /o/{org}/sessions` with that repository in `resources` and a `request_id` that is a UUID v5 of the repository's full name, so a retried call returns the run already started. Each run reads its own routes: a service on MySQL gets no foreign key index because that database creates one itself, and one with a `prisma.config.ts` pointing at a different migrations directory gets the migration written there.

## Why run it on Rush?

Index changes are the kind of pull request that reads fine and is wrong: an index on the wrong column order, a `LIKE` search that was supposed to speed up and did not, a migration that was never applied to a real database. The agent applies the migration to a PostgreSQL in its sandbox, loads enough rows for the planner to choose, and puts the plans in the pull request rather than a claim about them.

Every command and its output is kept with the run, from `migrate dev --create-only` to the `EXPLAIN ANALYZE` text, so a reviewer who doubts a plan can read it as the database printed it. The same request runs across every Prisma service a team owns at once.

## Frequently asked questions

### Why does the foreign key need an index?

PostgreSQL creates an index for a primary key and a unique constraint but not for a foreign key column. `authorId` is a plain column with a constraint, so any query by author, the `include: { author: true }` join and the referential check on user deletion scan `Post`. MySQL indexes foreign keys itself, which is why the pull request says the line is PostgreSQL-specific.

### Why no index for the title and content search?

Those filters use `contains`, which Prisma turns into `LIKE '%term%'`, and a leading wildcard defeats a B-tree index. A `pg_trgm` GIN index or a generated `tsvector` column with a full-text query would serve it, but that is a schema and query change the prompt leaves out; the pull request names it as the next step.

### Will the migration lock the table in production?

`CREATE INDEX` without `CONCURRENTLY` takes a lock that blocks writes to `Post` while it builds, and Prisma Migrate writes the plain form. On a small table that is a moment; on a large one the reviewer can edit the generated `migration.sql` to add `CONCURRENTLY`, which cannot run inside a transaction, and the pull request explains both.

### Does it change the generated client or the routes?

No. `@@index` affects only the database; `prisma generate` produces the same client types, `src/index.ts` is untouched, and `tsc --noEmit` passes unchanged. The only runtime difference is the query plan.

## More Prisma jobs

- [Add Full-Text Search to a Prisma App](https://getrush.ai/run/add-full-text-search/prisma/)
- [Add a Prisma Migration Drift Check to CI](https://getrush.ai/run/add-migration-drift-check/prisma/)
- [Add Optimistic Locking to Prisma Models](https://getrush.ai/run/add-optimistic-locking/prisma/)

## The same job on other stacks

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

- [What an agent can do in Prisma itself, once you connect it](https://getrush.ai/integrations/prisma/)
- [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/)
