# Write pgTAP Tests for a Supabase Database

Yes. An agent writes pgTAP tests for your Supabase migrations, row level security and vector search function, runs supabase test db and opens a pull request.

[Run this on Rush](https://cloud.getrush.ai/run/write-pgtap-tests/supabase/), in a Project that has your repository.

## What the agent does

The agent clones the project into its own cloud sandbox and reads the database it has to test. In supabase-community/nextjs-openai-doc-search there is one migration, `supabase/migrations/20230406025118_init.sql`, and `supabase/config.toml` sets `major_version = 15` with the database on port `54322`. The migration enables `vector`, creates `nods_page` and `nods_page_section` with an `embedding vector(1536)` column, turns on row level security for both with `enable row level security`, and defines two functions: `match_page_sections(embedding, match_threshold, match_count, min_content_length)` in plpgsql and `get_page_parents(page_id)`, a recursive SQL query that walks `parent_page_id` up to the root.

The migration creates no policies, and that is the first thing the tests pin down. With row level security on and no policy, the `anon` and `authenticated` roles can neither read nor write either table, and the app works only because `pages/api/vector-search.ts` and `lib/generate-embeddings.ts` connect with `SUPABASE_SERVICE_ROLE_KEY`. The agent writes `supabase/tests/rls.test.sql` to assert that: `has_table`, `col_type_is` for `embedding`, an `is` check that `pg_policies` holds no row for either table, then `set role anon` and `throws_ok` on an insert into `nods_page`, and `is_empty` on a select. Neither function is `security definer`, so the same file checks that `anon` calling `match_page_sections` gets no rows even when matching sections exist.

The function tests go in `supabase/tests/match_page_sections.test.sql`. They insert a page and a few sections inside the test's transaction, with embeddings built so their dot products are known: unit vectors made with `array_fill` and a few changed components. The function scores by `(embedding <#> query) * -1`, so the tests assert that results come back highest similarity first, that a section under `match_threshold` is left out, that `match_count` limits the rows, and that a section shorter than `min_content_length` is skipped. The values the API route sends, `0.78`, `10` and `50`, are used in one case so a test fails if that call's behavior changes. A third file checks that `get_page_parents` returns the whole chain for a nested page.

Then the agent starts the local stack with `supabase start` and runs `supabase test db`, which runs every file in `supabase/tests` with pgTAP inside a transaction that is rolled back. The local stack runs in containers; if they cannot start in the sandbox, the agent runs the same files with `pg_prove` against a Postgres 15 with `pgvector` and `pgtap` installed and the migration applied, and the pull request says which way they ran.

## Start it with one API call

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

```json
{
  "agent": "claude",
  "input": "Write pgTAP tests under supabase/tests for this project's migrations. Cover the tables and their columns, the row level security on each table (which policies exist, and that the anon role cannot insert, update or select), the vector match function's ordering, threshold, count limit and minimum content length, using embeddings whose similarity you can compute, and any other function the migrations define. Use the parameters the app's API route passes in one case. Start Supabase locally with supabase start and run supabase test db; if the local stack cannot start in the sandbox, run the same files with pg_prove against a Postgres with pgvector and pgtap and the migrations applied, and say so. Open a pull request listing each test file, what it asserts, and any security gap the tests document.",
  "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.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 a pull request with `supabase/tests/rls.test.sql`, `supabase/tests/match_page_sections.test.sql` and `supabase/tests/get_page_parents.test.sql`, each starting with `begin; select plan(n);` and ending with `select * from finish(); rollback;`. The description lists what each file asserts and states plainly that both tables have row level security with no policies, so only the service role reaches them, and whether that is what the app intends.

The session's transcript keeps the `supabase test db` or `pg_prove` output with each test's `ok` or `not ok` line. If the project has no workflow that runs database tests, the pull request says so and suggests one, without adding it unasked.

Before merging, a reviewer checks that the documented security behavior is the intended one: a doc search might want `anon` to read sections through a policy or a `security definer` function instead of the service key. They also read the vector fixtures, because a test whose embeddings are not normalized can pass for the wrong reason.

## Run it on every new migration

A repository webhook on your server can send this request when a pull request adds a file under `supabase/migrations`, asking for tests of the new tables, policies and functions, with the branch in `resources` as `https://github.com/your-org/your-app/tree/<branch>`. The tests then arrive with the schema change they cover.

With a webhook URL on the API key, Rush posts `run.completed` when the turn ends. Your handler can comment on the migration's pull request, which is where a reviewer reads a policy change anyway.

## Why run it on Rush?

Database tests are only worth having when they run against a real Postgres with the real extensions. In a Rush sandbox the agent applies the migrations to a fresh database of its own, with `pgvector` and `pgtap` installed, so the tests prove what the schema does and no shared or hosted Supabase project is touched.

The proof is the output: every `supabase test db` or `pg_prove` run is kept in the transcript with its per-test lines. Row level security is the part of a Supabase app that fails quietly, and a pull request that states what each role can and cannot do, backed by passing tests, is something a reviewer can check in minutes.

## Frequently asked questions

### Does it connect to my hosted Supabase project?

No. The tests run against a local database in the sandbox built from the migrations in the repository. No project URL or service key is needed.

### Why test that a role cannot do something?

Because row level security fails open when a policy is too broad, and nothing else in the app notices. A test that `anon` gets `throws_ok` on an insert fails the day someone adds a permissive policy by mistake.

### How can tests check vector search without OpenAI?

The tests insert sections with hand-built embeddings whose dot products are known, so the expected order and threshold results are exact. No embedding model is called.

### Will it add the missing policies?

Not in this job. The tests document what the schema does today and the pull request flags the gap. Adding policies is a separate change you can ask for in a follow-up.