# Add Missing Indexes to a Supabase Database

Yes. An agent reads your Supabase migrations and the queries your app runs, writes a migration with the indexes they need, checks the plans locally and opens a PR.

[Run this on Rush](https://cloud.getrush.ai/run/add-database-indexes/supabase/). 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 and the queries together, because an index is only right for a query. In a project like Razikus/supabase-nextjs-template, `supabase/config.toml` sets Postgres `major_version = 15` and four files sit in `supabase/migrations`. `20250130181641_todo_list.sql` creates `public.todo_list` with `id`, `created_at`, `title`, `urgent`, `description`, `done`, `done_at` and `owner uuid not null`, turns on row level security, adds the primary key, a foreign key from `owner` to `auth.users(id)` with `on delete cascade`, and one policy, `Owner can do everything`, whose `using` clause is `authenticative.is_user_authenticated() AND owner = auth.uid()`. The only index on the table is the primary key. Postgres does not index a foreign key column by itself, so `owner` has none.

The app's side is in `nextjs/src/lib/supabase/unified.ts`. `getMyTodoList(page, pageSize, order, done)` runs `.from('todo_list').select('*').range(...).order(order)` with a default order of `created_at` and, unless `done` is `null`, `.eq('done', done)`; `createTask`, `removeTask` and `updateAsDone` insert, delete and update by `id`. Under the policy every one of those statements also filters on `owner = auth.uid()`. So the shape of the hot query is: rows for one owner, with a given `done` value, ordered by `created_at`, limited by the page. The agent writes one composite index, `(owner, done, created_at)`, which serves that query from a single range scan and also serves plain lookups by `owner`, including the cascade when a user is deleted.

The change is a migration, not a console edit. The agent runs `supabase migration new add_todo_list_indexes`, which creates a timestamped file in `supabase/migrations`, and writes `create index` there with a name that says what it covers. It does not use `concurrently`, because the Supabase CLI applies each migration inside a transaction and `create index concurrently` cannot run in one; the pull request says when that matters and what to do instead on a large live table. Two things it reads and deliberately leaves alone are noted for a follow-up: the storage policies in `20250130181110_storage_policies.sql` match `name ~ '^<uid>/'` with a regular expression, which no btree index helps, and `is_user_authenticated()` is a `security definer` function evaluated per row by the policy.

Then it proves the plan. In the sandbox the agent runs `supabase start`, which brings up the local Postgres from `config.toml`, applies the migrations with `supabase db reset`, inserts enough rows for a planner to care, and runs `explain analyze` on the SQL that PostgREST generates for `getMyTodoList`: `select * from todo_list where owner = $1 and done = false order by created_at limit 100`. Before the migration the plan is a sequential scan with a sort; after it, an index scan on the new index with no sort node. Both plans go into the pull request, and `supabase db lint` runs so the migration is checked the way the project's own tooling would.

## Start it with one API call

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

```json
{
  "agent": "claude",
  "input": "Add the missing indexes to this Supabase project and open a pull request. Read supabase/config.toml, every file in supabase/migrations and nextjs/src/lib/supabase/unified.ts first, and list each query the app runs against public.todo_list together with the filter the row level security policy adds. Create a migration with supabase migration new add_todo_list_indexes and write a composite index on todo_list (owner, done, created_at) that serves getMyTodoList and lookups by owner, with a name that says what it covers; do not use concurrently and explain in the pull request why and when a large live table would need it applied by hand instead. Note, without changing them, that the storage policies match name with a regular expression that no index serves, and that the policy calls is_user_authenticated() per row. Run supabase start and supabase db reset, insert a few thousand rows for several owners, and paste explain analyze for the list query before and after the migration, then run supabase db lint. Do not change the policies, the Next.js code or the generated types.",
  "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 with one new file in `supabase/migrations`, a few lines long, and a description with three parts: the list of queries from `unified.ts` with the policy's `owner = auth.uid()` filter written beside each, the `explain analyze` output before and after the index showing the sequential scan with a sort become an index scan, and the `supabase db lint` result. Nothing in `nextjs/` changes, because an index changes no column and therefore none of the generated types in `nextjs/src/lib/types.ts`.

The repository has no CI, so the pasted plans are the evidence. The migration applies to a linked project with the commands the README already documents, `npx supabase link` and `npx supabase migrations up --linked`, or through `supabase db push` in whatever pipeline the team runs.

A reviewer checks that the column order `(owner, done, created_at)` matches the filters and the sort, that the table is small enough on the live project for a plain `create index` to hold its lock briefly, and reads the two notes about the storage regex and the per-row function to decide whether a follow-up is worth it.

## Run it on every new table in a migration

A team that merges schema changes often sends `POST /o/{org}/sessions` with this prompt whenever a migration adding a table or a foreign key lands, with the repository in `resources`. Each run reads the new table, finds the queries the app runs against it and the filters its policies add, and opens a pull request with the indexes they need, or none, if the primary key already covers them.

An automation with a `github` trigger on `pull_request.closed.merged` and `branch_patterns` set to `main` starts the run when a pull request merges and appends the event after the prompt, so the agent reads which pull request arrived and whether it touched `supabase/migrations/`. An automation run has its own `max_runs_per_hour` cap and is not tied to an API key.

## Why run it on Rush?

Index advice is cheap and usually wrong in one of two ways: an index on `owner` alone that still sorts every page, or an index on a column no query filters on. The only way to know is to run the real query against the real schema and read the plan. In a Rush sandbox the agent starts the local Supabase stack, applies your migrations, loads rows and runs `explain analyze` before and after, and every command and both plans are kept in the run's record.

The agent also reads the parts of the system that make Supabase different: the row level security policy that adds a filter to every query, the `security definer` function it calls, and the storage policies that cannot be indexed. Those notes are in the pull request, and the same request produces them for the next table.

## Frequently asked questions

### Why (owner, done, created_at) and not just owner?

Because `getMyTodoList` filters on `owner` through the policy and on `done` through `.eq`, then orders by `created_at` for the page. With all three in that order Postgres reads the matching rows already sorted and stops at the limit; an index on `owner` alone would still fetch every row for the user and sort them.

### Why not create index concurrently?

The Supabase CLI runs each migration file in a transaction, and `concurrently` cannot run inside one. On a small table a plain `create index` holds its lock for a moment. For a large table in production, the pull request says to run the `concurrently` form by hand first, then let the migration's `if not exists` pass.

### Does row level security use the index?

Yes. The policy's `owner = auth.uid()` becomes part of the query's `where` clause, and the planner treats it like any other filter. The `explain analyze` in the pull request is run as the authenticated role so the policy is in the plan.

### Will the generated types change?

No. `nextjs/src/lib/types.ts` describes columns and relationships, and an index adds neither. Re-running type generation produces the same file, which is why the pull request touches nothing under `nextjs/`.

### What about the storage policies?

They match `name ~ '^<uid>/'`, a regular expression on `storage.objects`, which a btree index cannot serve. The agent notes it and leaves it alone; changing how files are keyed is an application decision, not an index.

## More Supabase jobs

- [Add a Postgres Trigger to a Supabase Project With a Migration](https://getrush.ai/run/add-database-triggers/supabase/)
- [Add a Database Webhook to a Supabase App](https://getrush.ai/run/add-database-webhooks/supabase/)
- [Add an Edge Function to a Supabase Project](https://getrush.ai/run/add-edge-function/supabase/)

## The same job on other stacks

- [Add Database Indexes to TypeORM Entities](https://getrush.ai/run/add-database-indexes/typeorm/)
- [Add Database Indexes with an Alembic Migration](https://getrush.ai/run/add-database-indexes/alembic/)
- [Add Database Indexes to a Drizzle Schema from a Linear Issue](https://getrush.ai/run/add-database-indexes/drizzle/)

## Keep reading

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