# Review a Supabase Database Schema

Yes. An agent reads your Supabase schema and table stats through the connection and returns schema-review.md with index, bloat and type fixes as SQL you did not run.

[Run this on Rush](https://cloud.getrush.ai/run/database-schema-review/supabase/).

## What the agent does

The run needs no repository. The org admin connects Supabase once (`POST /me/connections` with the Supabase MCP at `https://mcp.supabase.com/mcp`, OAuth), then this request names that connection so the agent can run read-only SQL against the project. Against a seeded SaaS-shaped project the agent lists schemas and tables, then for each public table it reads column names and types, primary keys, foreign keys, unique constraints and check constraints the way the Supabase MCP exposes them: table lists, column definitions and ad-hoc `SELECT` against `pg_catalog` and `pg_stat_*`.

It builds the foreign-key index checklist first. For every foreign key it finds the referencing column (for example `orders.user_id` → `users.id`, `subscriptions.team_id` → `teams.id`) and asks whether an index already covers that column. A foreign key with no supporting index is listed with a suggested `CREATE INDEX CONCURRENTLY` statement the agent does not execute. It then walks index usage: indexes whose `idx_scan` in `pg_stat_user_indexes` is zero, or far below the table's sequential scan count, are marked as candidates to drop, with the exact index name and the create statement that produced them so a DBA can decide.

Table and bloat section comes from size and statistics, not guesswork. The agent reads `pg_total_relation_size`, `n_live_tup`, `n_dead_tup` and last vacuum/analyze times for the largest relations. A table with high dead-tuple ratio or a toast relation that dominates the size gets a note and a suggested `VACUUM (ANALYZE)` or `REINDEX` line, still unrun. Type review looks at columns whose declared type fights the data: a `text` column whose values are all short UUIDs, a `numeric` used as a money amount with no scale, a `timestamp without time zone` next to a `timestamptz` peer, or a `json` column that is always a flat object when `jsonb` would index better. Each finding cites the column and proposes an `ALTER TABLE … TYPE` or a check constraint as SQL comments in the report.

Nothing is written back to the database. The prompt is read-only: no migrations, no RLS changes, no `INSERT`/`UPDATE`/`DELETE`. The agent may create temporary views or run `EXPLAIN` only if the connection allows it; the deliverable is always `schema-review.md` in the sandbox, uploaded as a session artifact.

## Start it with one API call

`POST https://api.prix.dev/o/{org}/sessions`

```json
{
  "agent": "claude",
  "input": "Use the Supabase connection to read the schema, indexes and table and index statistics. Write schema-review.md listing foreign keys without an index, indexes never scanned, the largest tables and their bloat, and columns whose types look wrong for their data, each with a suggested fix as SQL you did not run. Read only.",
  "connections": [
    "supabase"
  ]
}
```

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.prix.dev/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 `schema-review.md` as an artifact on the session. The session read returns it with a download link that works for an hour. The file opens with the project ref and the schemas inspected, then four sections: foreign keys missing indexes, unused or cold indexes, largest tables with size and dead-tuple notes, and type or constraint suggestions. Every suggested statement is marked as not executed.

The session transcript keeps every SQL the agent ran through the connection and its row counts, so a reviewer can re-check a finding in the Supabase SQL editor. Before acting on it, a DBA confirms that a zero-scan index is not reserved for a rare report, that `CREATE INDEX CONCURRENTLY` fits the project's maintenance window, and that a type change will not break application code that still casts the old way.

## Run it on every weekly schema drift check

Schedule a cron on your server that sends this request every Monday with the same Supabase connection. Each run reads the live catalog, so a table added mid-week or an index someone dropped shows up in that week's report instead of waiting for a person to open the dashboard.

Set a webhook URL on the API key; Rush POSTs `run.completed` with the `session_url` when the turn finishes. Your server downloads `schema-review.md` and opens a tracking issue only when the unused-index or missing-FK-index sections are non-empty.

## Why run it on Rush?

A schema review on a laptop means pasting SQL into the Supabase SQL editor, copying result grids into a doc and hoping you did not miss a foreign key. In a Rush sandbox the agent issues the catalog queries itself, keeps the outputs in the transcript and writes one Markdown file you can hand to a reviewer.

Several projects can be reviewed at once, each with its own connection and its own artifact, up to the key's concurrent run limit. The database is never the agent's scratchpad: suggested SQL stays in the report until a person runs it.

## Frequently asked questions

### Does the agent change my database?

No. The prompt says read only and the suggested `CREATE INDEX`, `VACUUM` and `ALTER TABLE` statements are written into `schema-review.md` without being executed. You run what you accept, in your own maintenance window.

### How does the agent reach my Supabase project?

Connect Supabase under your Rush account once (OAuth to the Supabase MCP). Name that connection on the run as `connections: ["supabase"]` or by its id from `GET /me/connections`. The sample request above uses the name form.

### Will it see tables outside the public schema?

Yes, if the connection's role can. The agent lists the schemas it can read and says which ones it skipped. Auth and storage schemas are included when visible; say in the prompt if you want only `public`.

### What about Row Level Security?

This job reviews indexes, sizes and types, not RLS policy correctness. For policy review use a different prompt that asks for policies per table. Findings here stay useful either way because missing foreign-key indexes hurt whether RLS is on or off.