All jobs

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

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

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

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

The same job on other stacks

Keep reading