# Add Database Indexes to a Drizzle Schema from a Linear Issue

Yes. Label the Linear issue and an agent reads every Drizzle query, adds the indexes those queries need to the schema, generates the migration and opens a PR.

[Run this on Rush](https://cloud.getrush.ai/run/add-database-indexes/drizzle/). 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 Linear issue and the repository in its own sandbox, and builds the index list from the queries rather than from the tables. In an app laid out like nextjs/saas-starter, `lib/db/schema.ts` defines `users`, `teams`, `team_members`, `activity_logs` and `invitations` with `pgTable` from `drizzle-orm/pg-core`, and the only indexes today are the ones constraints imply: the serial primary keys, `users.email` unique and `teams.stripe_customer_id` and `stripe_subscription_id` unique. Postgres creates no index for a foreign key, so `team_members.user_id`, `team_members.team_id`, `activity_logs.team_id`, `activity_logs.user_id`, `invitations.team_id` and `invitations.invited_by` are all unindexed `references()` columns. `drizzle.config.ts` writes migrations to `lib/db/migrations` for the `postgresql` dialect, and the one migration there, `0000_soft_the_anarchist.sql`, matches that picture.

Then it reads every query. `lib/db/queries.ts` has `getTeamForUser`, which runs `db.query.teamMembers.findFirst` on `teamMembers.userId` and loads the team's members and each member's user through the relations; `getUserWithTeam` and `signIn` in `app/(login)/actions.ts`, which left-join `teamMembers` on `userId`; and `getActivityLogs`, which filters `activityLogs.userId` and orders by `timestamp` descending with a limit of 10. `actions.ts` adds `removeTeamMember` and `deleteAccount`, which filter `teamMembers` by `teamId` with `id` or `userId`; `inviteTeamMember`, which looks for a pending invitation by `email`, `teamId` and `status`; and `signUp`, which reads an invitation by `id`, `email` and `status`. `getTeamByStripeCustomerId` in `queries.ts` is already covered by the unique constraint.

From that, the agent adds five indexes in the third argument to `pgTable`, the array form Drizzle documents: `team_members_user_id_idx` on `userId` and `team_members_team_id_idx` on `teamId`, because the two filters run in different directions; `activity_logs_user_id_timestamp_idx` on `(userId, timestamp.desc())`, so the dashboard's ten newest rows come straight off the index; `activity_logs_team_id_idx` on `teamId` for the relation; and `invitations_team_id_email_status_idx` on `(teamId, email, status)`, which serves both invitation lookups because the `signUp` read also filters on `id`, the primary key. It leaves `invitations.invited_by` alone and says why in the pull request: no query filters on it, and an index nothing reads only slows inserts. Then `pnpm db:generate --name add_query_indexes` writes `0001_add_query_indexes.sql` with five `CREATE INDEX IF NOT EXISTS` statements, a new snapshot and a journal entry, and the agent commits all three.

To prove the plans change, the agent installs Postgres with `apt-get` in its sandbox, points `POSTGRES_URL` at it, runs `pnpm db:migrate` and `pnpm db:seed` for the one user and team the seed creates, and runs `EXPLAIN` for the activity log query and the `teamMembers` lookup before and after the new migration, pasting both plans. It runs `pnpm build` to show the schema change compiles, and keeps a note in the description that `getTeamForUser`'s relational query issues its own `SELECT`s per level, so the member list is served by `team_members_team_id_idx` and the user rows by the primary key.

## Start it with one API call

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

```json
{
  "agent": "claude",
  "input": "Add the database indexes this Next.js app's Drizzle queries need. Read package.json, drizzle.config.ts, lib/db/schema.ts, lib/db/queries.ts, lib/db/seed.ts, lib/db/migrations/0000_soft_the_anarchist.sql and meta/_journal.json, app/(login)/actions.ts, app/api/team/route.ts and app/(dashboard)/dashboard/activity/page.tsx first. List every filter, join and order by column, then add only the indexes a query reads, using the array form of pgTable's third argument: team_members on user_id and separately on team_id, activity_logs on (user_id, timestamp desc) and on team_id, and invitations on (team_id, email, status). Leave invitations.invited_by unindexed and say why. Run pnpm db:generate --name add_query_indexes and commit the SQL, snapshot and journal it writes. Install Postgres in your sandbox, set POSTGRES_URL, run pnpm db:migrate and pnpm db:seed, run EXPLAIN for the activity log query and the team member lookup before and after the migration, and paste both plans into the pull request with the output of pnpm build. Change no query, no column and no application code.",
  "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 links the Linear issue, changes `lib/db/schema.ts`, and adds `lib/db/migrations/0001_add_query_indexes.sql`, `meta/0001_snapshot.json` and the journal entry. Its description has a table of each index, the query that reads it and the file and function the query lives in, followed by the two `EXPLAIN` plans before and after, where the activity log query moves from a sequential scan with a sort to an index scan on `activity_logs_user_id_timestamp_idx`, and the `pnpm build` output.

No query changes and no response changes; the migration is additive and `IF NOT EXISTS` makes it safe to re-run. The description notes the one operational point: Drizzle's generated `CREATE INDEX` is not `CONCURRENTLY`, so on a large production table the statement takes a lock for the duration, and the `.concurrently()` option on the index is the one-word change to make if that matters.

Before merging, a reviewer confirms the index column order for `invitations` matches how they expect to query it, decides whether `activity_logs` by `team_id` is read anywhere outside the relation and is worth keeping, and chooses whether to apply the migration with `pnpm db:migrate` in a deploy step or by hand.

## Run it on every Linear issue labelled agent

Add an automation to the Project with this prompt and a `linear` trigger on `linear.issue.labeled` with the label you reserve for the agent in its `labels`. Each labelled issue starts one run with the issue as its context, on its own branch; the trigger adds the issue after the prompt and fills in no placeholder, and a change made by an app integration never fires it, so the agent's own comment on the issue starts nothing.

The automation's `max_runs_per_hour` holds a burst of labels to that many runs an hour: a fire past it is created `queued` and starts when the hour has room, and one past a second hour's worth is skipped as `rate_limited` until the issue is labelled again. From your own server, the same run starts with this request and the issue URL in `resources`, with a `request_id` that is a UUID v5 of the issue id so a retried start returns the run already begun.

## Why run it on Rush?

An index list made from the tables is a guess; one made from the queries is a fact, and the only proof that an index helps is the plan before and after. The agent reads every query in the app, adds nothing a query does not use, and runs `EXPLAIN` against a real Postgres in its own sandbox with the migration applied, so the pull request shows the scan that went away rather than asserting it.

The transcript keeps every command and its output, so the reviewer reads the generated SQL, the migration run and both plans without checking the branch out. Several Drizzle services can be reviewed at once, each run in its own sandbox with its own database, and nothing merges until a person reads the diff.

## Frequently asked questions

### Why two single-column indexes on team_members instead of one composite?

The queries filter in both directions: `getTeamForUser` and the sign-in join look up by `user_id`, while `removeTeamMember`, `deleteAccount` and the team's member list look up by `team_id`. A composite on `(user_id, team_id)` serves the first and not the second. Two indexes cover both, and the table is small enough that the write cost is negligible.

### Why is timestamp in the activity log index descending?

`getActivityLogs` orders by `timestamp` descending and takes 10. With `(user_id, timestamp desc)` Postgres reads the ten newest rows for the user straight off the index and skips the sort. Drizzle writes the direction into the migration from `timestamp.desc()` in the index definition.

### Does the migration lock the table?

Drizzle generates a plain `CREATE INDEX IF NOT EXISTS`, which takes a share lock for the build. On the tables this starter ships, that is milliseconds. For a large table, add `.concurrently()` to the index in the schema and regenerate, and note that a concurrent build cannot run inside a transaction, so the migrator runs it on its own.

### Why not index invitations.invited_by?

No query filters or joins on it; it is written on insert and read back as a column. An index Postgres never consults still has to be updated on every insert, so the agent leaves it out and lists it in the pull request so a reviewer who plans an "invitations I sent" screen can add it then.

### Will the migration drift check pass?

Yes, if the repository has one: the agent commits the SQL, the new snapshot and the journal entry that `pnpm db:generate` wrote together, so a later `generate` run finds nothing to add. The pull request shows the three files side by side with the schema change so a reviewer can see they belong together.

## More Drizzle jobs

- [Add Database Migrations to a Drizzle App](https://getrush.ai/run/add-database-migrations/drizzle/)
- [Add Full-Text Search to a Drizzle and PostgreSQL App](https://getrush.ai/run/add-full-text-search/drizzle/)
- [Add a Drizzle Migration Drift Check to CI](https://getrush.ai/run/add-migration-check-to-ci/drizzle/)

## The same job on other stacks

- [Add Missing Foreign Key Indexes With an Ecto Migration](https://getrush.ai/run/add-database-indexes/ecto/)
- [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/)

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