All jobs

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

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

The use case this job follows, with its automation and trigger: Hand off a long task.

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

The same job on other stacks

Keep reading