# Add a Postgres Trigger to a Supabase Project With a Migration

Yes. An agent writes a Supabase migration with a trigger function that keeps a column such as done_at in step with its row, tests it locally, and opens a PR.

[Run this on Rush](https://cloud.getrush.ai/run/add-database-triggers/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 schema and the code that writes to it. In a project shaped like Razikus/supabase-nextjs-template, `supabase/config.toml` names the project and pins `major_version = 15` for the database, and `supabase/migrations/` holds four dated SQL files. `20250130181641_todo_list.sql` creates `public.todo_list` with `done boolean not null default false` and `done_at timestamp with time zone`, enables row level security, grants the table to `anon`, `authenticated` and `service_role`, and adds an `Owner can do everything` policy that checks `authenticative.is_user_authenticated()` and `owner = auth.uid()`, with that function defined in `20250107210416_MFA.sql`. Nothing ever writes `done_at`: `nextjs/src/lib/supabase/unified.ts` has `updateAsDone(id)` that sends `update({done: true})` and nothing else, and `nextjs/src/app/app/table/page.tsx` calls it from `handleMarkAsDone`. The generated `Database` type in `nextjs/src/lib/types.ts` lists the column, so the schema promises a timestamp the app never fills.

It fixes that in the database, where every writer is covered. A new file under `supabase/migrations/` with the next timestamp defines `public.set_todo_done_at()` as a `plpgsql` trigger function with `security invoker` and an empty `search_path`, which the Supabase linter asks for, that sets `new.done_at = now()` when `done` flips from false to true, sets it back to `null` when `done` flips to false, and leaves it alone otherwise. A `before update of done on public.todo_list for each row` trigger calls it. Because it is `before update` on the `done` column only, a plain title edit never touches `done_at`, and because it runs inside the database, the Next.js app, the Expo app in `supabase-expo-template/` and anything using the service role all get the same behaviour without a code change. The migration backfills nothing, since no existing row has a known completion time, and says so in a comment.

The frontend gets one small change so the column is visible: in `page.tsx`, the completed list shows `done_at` beside each finished task, formatted the way `created_at` already is in the same component. `updateAsDone` in `unified.ts` stays as it is, which is the point, and `types.ts` is unchanged because the column already exists. The RLS policy is not touched, since the trigger runs as the invoking role and the update it modifies is already allowed by the policy.

The agent proves it against a fresh database. It starts the local stack, applies the migrations in order from an empty database so the new file is checked for ordering against the four before it, inserts a row as a test user, updates `done` to true and reads back a non-null `done_at`, updates it to false and reads `null`, and updates only `title` on a done row to show `done_at` holds. It adds a pgTAP test under `supabase/tests/database/` that does the same three checks so `supabase test db` guards the trigger from now on, then runs `next build` in `nextjs/` to show the component change type-checks.

## Start it with one API call

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

```json
{
  "agent": "claude",
  "input": "Add a trigger that maintains done_at on public.todo_list in 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 a new dated migration that defines public.set_todo_done_at() as a plpgsql trigger function with security invoker and an empty search_path, setting new.done_at to now() when done changes from false to true, to null when it changes to false, and leaving it alone otherwise, and a before update of done trigger on public.todo_list for each row that calls it. Backfill nothing and say so in a comment. Do not change the RLS policy or updateAsDone. In page.tsx show done_at beside completed tasks in the same format as created_at. Start the local stack, apply all migrations from an empty database, insert a row, flip done true and back and edit only the title, reading done_at after each, and add a pgTAP test under supabase/tests/database/ with those three checks runnable by supabase test db. Run next build in nextjs/. Paste the SQL session and the test and build output in the pull request.",
  "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 under `supabase/migrations/` holding the function and the trigger, one new pgTAP file under `supabase/tests/database/`, and a small change to `nextjs/src/app/app/table/page.tsx`. The description pastes the SQL session from the sandbox showing `done_at` becoming a timestamp, then `null`, then holding through a title edit, plus the `supabase test db` summary and the `next build` output.

The repository has no CI workflow, so the pasted session and the pgTAP test are the proof, and the test stays in the repository for the next change to the table. The agent pushes one commit for the migration and test and one for the component, so the schema change can be reviewed on its own.

A reviewer checks that the trigger is `before update of done` and not a plain `before update`, so unrelated edits never fire it, that the function uses `security invoker` and sets an empty `search_path`, that a flip back to false clears the column rather than leaving a stale time, that the migration filename sorts after the four existing ones, and that no backfill was invented. Then they deploy it with the same `supabase migrations up --linked` the README already uses.

## Run it on every column the app forgets to write

A schema review that finds a derived column nobody maintains, a `completed_at`, an `archived_at`, a `last_login_at`, becomes one run each with `POST /o/{org}/sessions`, this prompt with the table and column changed and the repository in `resources`. Each run reads that table's migration and the code that writes to it, and ends in its own pull request with its own pgTAP test. A `request_id` makes a retried call return the run it already started.

A team that tracks database work in Linear adds an automation to the Project with this prompt and a `linear` trigger on `linear.issue.labeled`; an issue such as "done_at is always null" labelled `agent` starts a run with the issue appended after the prompt, and the agent reads which column to cover from it, up to the automation's `max_runs_per_hour`.

## Why run it on Rush?

A trigger is a few lines of SQL whose mistakes are all about edge cases: firing on every update, missing the flip back to false, running with a search path an attacker can shadow. In a Rush sandbox the agent applies every migration from an empty database, drives the row through each transition with real SQL and reads the column back, and the pgTAP test it leaves behind runs the same checks for the next person.

Nothing on the reader's laptop has to run the Supabase stack or Postgres 15. The pull request carries the SQL session and the test output, the migration is deployed with the command the project already documents, and the same request works for any Supabase project with a column that should follow another.

## Frequently asked questions

### Why a trigger instead of setting done_at in updateAsDone?

Because `unified.ts` is one of several writers. The Expo app in `supabase-expo-template/` has its own client, and anyone with the service role can update the table directly. A `before update` trigger covers all of them, and a test on the database proves it without touching any client.

### Does it affect row level security?

No. The `Owner can do everything` policy still decides whether the update is allowed; the trigger only runs once it is. The function is `security invoker`, so it runs as the same role and cannot be used to reach rows the policy would refuse.

### What about rows that are already done?

They keep `done_at` as `null`. No timestamp in the table says when they were completed, so any backfill would be a guess, and the migration says so in a comment. If you want them set to `created_at` or to the migration time, ask for that in the prompt and the agent adds one explicit `update` statement.

### How do I run the pgTAP test?

With `supabase test db` against the local stack, which runs every file under `supabase/tests/database/`. The test creates a row, flips `done` and asserts `done_at` with `is()` and `ok()`, so a later change to the trigger that breaks one of the three transitions fails before it reaches a linked project.

## More Supabase jobs

- [Add a Database Webhook to a Supabase App](https://getrush.ai/run/add-database-webhooks/supabase/)
- [Add an Edge Function to a Supabase Project](https://getrush.ai/run/add-edge-function/supabase/)
- [Add Full-Text Search to a Supabase App](https://getrush.ai/run/add-full-text-search/supabase/)

## More jobs like this: Hand off a long task

- [Add dbt Project Evaluator to a dbt Project](https://getrush.ai/run/add-dbt-project-evaluator/dbt/)
- [Add Deep Links to an Expo App](https://getrush.ai/run/add-deep-links/expo/)
- [Add Deep Links to a Flutter App](https://getrush.ai/run/add-deep-links/flutter/)

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