# Add an Audit Log of Row Changes to a Supabase Project

Yes. An agent adds an audit_log table and trigger to your Supabase project recording every insert, update and delete with the acting user, under RLS, and opens a PR.

[Run this on Rush](https://cloud.getrush.ai/run/add-audit-log/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 migrations before it writes one, because what the trigger may record depends on how the tables already guard themselves. In a project like Razikus/supabase-nextjs-template, `supabase/migrations/` holds four files applied in order: an MFA migration that creates the `authenticative` schema and `authenticative.is_user_authenticated()`, a `security definer` SQL function that returns true only when the caller's JWT `aal` matches their verified factors; a storage migration that inserts a private `files` bucket; four `storage.objects` policies that scope each user to a folder named after `auth.uid()`; and the `todo_list` migration, which creates `public.todo_list` with `id` as an identity column, `created_at`, `title`, `urgent`, `description`, `done`, `done_at` and an `owner uuid` that references `auth.users(id)` with `on delete cascade`, enables row level security, grants the table to `anon`, `authenticated` and `service_role`, and adds one policy, `Owner can do everything`, `for all to authenticated` using `is_user_authenticated() and owner = auth.uid()`. `supabase/config.toml` sets `major_version = 15` for the database and `[db.seed]` with `./seed.sql`. The Next.js app under `nextjs/` reads and writes `todo_list` through `src/lib/supabase/unified.ts` with `@supabase/ssr` 0.5.2 and `@supabase/supabase-js` 2.47, and `src/app/app/table/page.tsx` is the page that does it.

The agent writes one new dated migration. It creates `public.audit_log` with `id bigint generated by default as identity`, `table_name text`, `row_id text`, `action text` constrained to `INSERT`, `UPDATE` or `DELETE`, `old_row jsonb`, `new_row jsonb`, `actor uuid` defaulting to `auth.uid()` and `changed_at timestamptz default now()`, with an index on `(table_name, row_id)`. It enables row level security on it, grants `select` to `authenticated` only, revokes everything from `anon`, and adds one policy: a user may read rows whose `old_row` or `new_row` carries their id in `owner`, written as `coalesce(new_row->>'owner', old_row->>'owner') = auth.uid()::text`. There is no insert, update or delete policy, so no client role can write to the log or edit it; only the trigger does. The trigger function `public.audit_row_change()` is `plpgsql`, `security definer` with `set search_path = public`, and inserts `TG_TABLE_NAME`, `TG_OP`, the row's `id` as text, `to_jsonb(old)` and `to_jsonb(new)` (null for the side an insert or delete does not have). An `after insert or update or delete` trigger `for each row` on `public.todo_list` calls it, and a comment at the top of the file says which tables are audited so the next one is a one-line addition.

Then it proves the behaviour against a real database. It starts the local stack with `supabase start` and runs `supabase db reset`, which applies all five migrations from an empty database and then `seed.sql`, and in `psql` on port 54322 it does what a signed-in user does: `set role authenticated` with `request.jwt.claims` set to a test user's `sub` and `aal`, inserts a todo, updates its `title`, marks it `done` and deletes it, then selects from `audit_log` to show four rows with the right `action`, the `owner` inside `new_row`, and `actor` equal to that user's id. It switches to a second user's claims and shows the same query returning nothing, and as `anon` shows `permission denied`. If the local stack cannot start in the sandbox, it applies the migrations to a PostgreSQL 15 with the Supabase `auth` helpers stubbed, runs the same session, and says so in the pull request.

The application is left as it is: `unified.ts` and `page.tsx` keep working because the trigger runs after their statements and under the function's own privileges, and `types.ts` gains nothing until the team decides to show history in the app. A pgTAP file under `supabase/tests/database/` with the same four assertions is added so `supabase test db` keeps checking it, in the shape the project already uses for tests when it has one. The existing `Owner can do everything` policy and the `authenticative` function are not changed.

## Start it with one API call

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

```json
{
  "agent": "claude",
  "input": "Add an audit log of row changes to this Supabase project and open a pull request. Read supabase/config.toml, every file in supabase/migrations/, nextjs/src/lib/supabase/unified.ts, nextjs/src/lib/types.ts and nextjs/src/app/app/table/page.tsx first. Write one new dated migration that creates public.audit_log (identity id, table_name, row_id text, action constrained to INSERT, UPDATE or DELETE, old_row jsonb, new_row jsonb, actor uuid default auth.uid(), changed_at timestamptz default now()) with an index on table_name and row_id, enables row level security, grants select to authenticated only, and adds a single select policy that lets a user read rows whose new_row or old_row owner equals auth.uid(); add no insert, update or delete policy. Define public.audit_row_change() as a plpgsql security definer trigger function with search_path set to public that records TG_TABLE_NAME, TG_OP, the row id and to_jsonb(old) and to_jsonb(new), and attach it as an after insert or update or delete trigger for each row on public.todo_list. Start the local stack, run supabase db reset, and in psql as the authenticated role with a test user's claims insert, update, complete and delete a todo and select the four audit rows; show that another user sees none and anon is refused. Add a pgTAP test under supabase/tests/database/ with the same checks. Do not change the existing policy, the authenticative function or the Next.js code. Paste the SQL session and the test output in the pull request description.",
  "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 two new files: the migration under `supabase/migrations/` and a pgTAP file under `supabase/tests/database/`. The description holds the `psql` session from the sandbox, the four audit rows with their `action`, `actor` and the `owner` inside the JSON, the empty result for the second user, the `permission denied` for `anon`, and the `supabase test db` output. The session keeps the `supabase start` and `db reset` logs, and `GET /o/{org}/p/{project}/sessions/{id}/changes` returns the diff.

A reviewer checks that the trigger function is `security definer` with a fixed `search_path`, since it is the one piece that writes to a table no role may write to, that the select policy is the only policy on `audit_log`, and that `to_jsonb(new)` is what they want stored, because it copies every column including `description`. Then they apply it with `npx supabase migrations up --linked` the way the README already describes for the other migrations.

## Run it on every new table in the schema

A new migration that creates a table is the moment to decide whether it is audited, so start one run per pull request that adds one: an automation on the Project with a `github` trigger on `pull_request.opened`, with `branch_patterns` when only some base branches matter, and a prompt that asks the agent to look for a new table under `supabase/migrations/` in the pull request, add it to the trigger list in the audit migration when it carries an `owner` column, and say why not when it does not. The trigger adds the pull request event after the prompt, and each run gets its own sandbox and local stack.

From your own server instead, a handler that receives GitHub's `pull_request` webhook calls `POST /o/{org}/sessions` with the pull request's URL in `resources`, which checks out its branch, and a `request_id` that is a UUID v5 of the delivery id so a redelivered webhook returns the run already started. Give that key a `webhook_url` and Rush POSTs `run.completed` when the turn ends, signed per Standard Webhooks.

## Why run it on Rush?

An audit table is only as good as its policies, and the mistakes are quiet ones: a trigger that fails under RLS because it runs as the invoker, a log the client can edit, or a `search_path` that a user-created function could hijack in a `security definer` body. The agent runs the whole thing against a real local stack with real JWT claims, as two different users and as `anon`, and the `psql` session is on the record, so the pull request shows who could read what rather than describing the intent.

The proof travels with the change: the migration, a pgTAP test that `supabase test db` will keep running, and the session's logs, in one pull request. The sandbox parks with the local stack's images pulled when the turn ends, so extending the audit to a second table or adding a retention job is a follow-up on the same session rather than a fresh setup.

## Frequently asked questions

### Why security definer on the trigger function?

Because `audit_log` has no insert policy for any client role, which is what keeps the log tamper-proof, and a trigger that runs with the caller's privileges would then fail on every write. `security definer` lets the function insert as its owner, and the fixed `search_path` closes the usual hole that comes with it.

### Can a user read other people's history?

No. The only policy on `audit_log` lets `authenticated` read rows whose `new_row` or `old_row` has their id in `owner`, which the sandbox session demonstrates with a second user seeing nothing. `service_role` bypasses RLS as it does on every table, so a server-side admin client still reads everything.

### Does it slow down writes to todo_list?

Each insert, update or delete now also inserts one row in the same transaction. For a table written by a person clicking in a page that is not measurable; the pull request notes that a bulk import should disable the trigger for its duration if it ever becomes one.

### What about tables added later?

The trigger function is generic and reads `TG_TABLE_NAME`, so auditing another table is one `create trigger` statement in a later migration. The select policy assumes an `owner` column in the JSON; a table scoped differently needs its own policy line, which the migration's comment says.

### Does the Next.js app change?

No. `unified.ts` and `page.tsx` are untouched, and the generated types in `types.ts` only need regenerating when the team wants to show history in the app. The audit happens in the database on every path that writes to the table, including the Supabase dashboard.

## More Supabase jobs

- [Schedule Database Jobs With pg_cron on Supabase](https://getrush.ai/run/add-cron-jobs/supabase/)
- [Add Missing Indexes to a Supabase Database](https://getrush.ai/run/add-database-indexes/supabase/)
- [Add a Postgres Trigger to a Supabase Project With a Migration](https://getrush.ai/run/add-database-triggers/supabase/)

## The same job on other stacks

- [Add an Audit Log to a Django REST Framework API from a Linear Issue](https://getrush.ai/run/add-audit-log/django-rest-framework/)
- [Add an Audit Log to a Fastify API](https://getrush.ai/run/add-audit-log/fastify/)
- [Add an Audit Log to a Filament Admin Panel](https://getrush.ai/run/add-audit-log/filament/)

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