All jobs

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

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

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

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

The same job on other stacks

Keep reading