# Add Soft Delete to a Supabase Table

Yes. An agent adds a deleted_at column and a restore path to your Supabase table, rewrites the RLS policies to hide deleted rows, tests with pgTAP and opens a PR.

[Run this on Rush](https://cloud.getrush.ai/run/add-soft-delete/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 migration and the client code that touches the table before it changes either, because soft delete has to agree in three places at once: the schema, the row-level security policy and every query the app sends. In a project like Razikus/supabase-nextjs-template, `supabase/migrations/20250130181641_todo_list.sql` creates `public.todo_list` with `id`, `created_at`, `title`, `urgent`, `description`, `done`, `done_at` and an `owner` that references `auth.users`, enables row level security, grants every command to `anon`, `authenticated` and `service_role`, and adds one permissive policy, `Owner can do everything`, for all commands to `authenticated` with `authenticative.is_user_authenticated() AND owner = auth.uid()`, where that function comes from the MFA migration and requires `aal2` for users who have enrolled a factor. On the Next.js side, `nextjs/src/lib/supabase/unified.ts` wraps the client in a `SassClient` whose `getMyTodoList` selects `*` with a range and an optional `done` filter, `removeTask` runs a hard `delete().eq('id', id)`, and `updateAsDone` sets `done: true`; `nextjs/src/app/app/table/page.tsx` calls those three and renders a trash icon that deletes a task for good, with no confirmation and no way back. `nextjs/src/lib/types.ts` holds the generated `Database` type with the `todo_list` row shape.

The agent writes one new migration under `supabase/migrations` that adds a nullable `deleted_at timestamp with time zone` to `todo_list`, creates a partial index on `owner` where `deleted_at is null` for the list query, and replaces the single policy with four: `select` limited to the owner's rows where `deleted_at is null`, `insert` and `update` with the same owner check, and no `delete` policy for `authenticated` at all, so a hard delete from the browser is refused by RLS. A `security definer` function `restore_task(task_id bigint)` sets `deleted_at` back to null for a row the caller owns, because the select policy would otherwise hide the row the update needs to find. Because `postgres` 15 is pinned in `supabase/config.toml`, the SQL uses nothing newer.

On the client, `removeTask` in `unified.ts` becomes an `update({ deleted_at: new Date().toISOString() })`, `getMyTodoList` keeps selecting `*` because the policy already hides deleted rows, and a `getMyDeletedTasks` plus a `restoreTask` that calls the new function through `rpc` are added. `page.tsx` gains a fourth filter button, `Deleted`, that lists trashed tasks with a restore icon instead of the check and trash icons, and the trash action gets a short undo notice that calls `restoreTask`. `types.ts` is regenerated with `supabase gen types typescript --local` so the new column and function are typed and `yarn build` catches any call site that was missed.

Tests are pgTAP, which the Supabase CLI runs with `supabase test db` from `supabase/tests`. The agent installs Docker in the sandbox first, since `supabase start` needs it, applies the migrations, and writes a test that creates two users, inserts a task as the first, soft-deletes it, and asserts the first user no longer sees it in a select, that a direct `delete` as that user affects no rows, that `restore_task` brings it back for its owner and does nothing for the second user, and that the `anon` role sees nothing. It then runs `yarn lint` and `yarn build` in `nextjs` with the local project's URL and anon key in `.env.local`, which is what the README's local setup produces.

## Start it with one API call

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

```json
{
  "agent": "claude",
  "input": "Add soft delete to the todo_list table 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/supabase/client.ts, nextjs/src/lib/types.ts, nextjs/src/app/app/table/page.tsx and README.md first. Write one new migration that adds a nullable deleted_at timestamptz, a partial index on owner where deleted_at is null, replaces the single all-commands policy with owner-scoped select, insert and update policies that hide rows where deleted_at is set and no delete policy for authenticated, and a security definer restore_task(bigint) function that clears deleted_at for the caller's own row. Change removeTask to set deleted_at, add getMyDeletedTasks and restoreTask to the client wrapper, add a Deleted filter with a restore action and an undo notice to the table page, and regenerate nextjs/src/lib/types.ts with supabase gen types. Install Docker in the sandbox, run supabase start and the migrations, write a pgTAP test under supabase/tests covering hidden rows after soft delete, a refused hard delete, restore by the owner, no restore by another user and nothing visible to anon, run supabase test db, then run yarn lint and yarn build in nextjs, and paste the outputs in the pull request. Do not change the storage or MFA migrations or any auth page.",
  "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 migration and one new pgTAP file under `supabase/`, edits to `unified.ts`, `page.tsx` and the regenerated `types.ts` under `nextjs/`, and nothing else. The description shows the `supabase test db` output with each assertion, the `yarn lint` and `yarn build` results, and the rendered policies from `select * from pg_policies where tablename = 'todo_list'` on the local database so a reviewer can read the final rules without applying the migration.

A reviewer checks that no `delete` policy remains for `authenticated`, that the select policy filters `deleted_at`, that `restore_task` checks `owner = auth.uid()` inside the function rather than relying on the caller's policies, that the `aal2` requirement from the MFA helper is kept in every new policy, and that the undo notice calls the same function the Deleted view uses. Then they push the migration to their linked project with `supabase migrations up --linked` as the README describes and deploy the Next.js change.

## Run it on every table that needs soft delete

A team with several Supabase projects starts one run per repository with `POST /o/{org}/sessions`, this prompt with the table name changed and the repository in `resources`. The agent reads each project's own policies and client code, so a table that already uses a `select` policy gets it amended rather than replaced, and each run opens its own pull request.

The key's `webhook_url` receives `run.completed` for each run it starts, with a `session_url` to read the pull request link. A `request_id`, a UUID, in each body makes a retried start return the run already started.

## Why run it on Rush?

Soft delete on a table behind row-level security is easy to get wrong in a way no unit test in the app catches: the column is added, the client stops deleting, and the old all-commands policy still lets any session hard-delete or read trashed rows through the REST API. In a Rush sandbox the agent starts the real Supabase stack, applies the migration, runs pgTAP against the policies as two different users and as `anon`, and keeps the output in the transcript, so the pull request shows the rules behaving rather than describing them.

It is also a job to hand off from the terminal: start it, and come back to a pull request with the policies, the client changes and the test results in one place. The same request runs across every Supabase project the team owns, each in its own sandbox.

## Frequently asked questions

### Why remove the delete policy instead of filtering it?

Because the browser client talks to PostgREST directly, and a `delete` policy that still allows the owner means a hard delete remains one request away even after the app stops sending it. Leaving no `delete` policy for `authenticated` makes RLS refuse it; the pgTAP test asserts a direct delete affects no rows.

### Why is restore a function rather than an update?

Once the select policy hides rows where `deleted_at` is set, an `update` from the client cannot find the row to restore. A `security definer` function that checks `owner = auth.uid()` itself can, and it keeps the rule in one place. The test covers restore by the owner and a no-op for another user.

### Does this keep the MFA requirement?

Yes. Every new policy keeps `authenticative.is_user_authenticated()` from the MFA migration alongside the owner check, so a user with an enrolled factor still needs an `aal2` session to read or change tasks, including the Deleted view.

### How does the agent run Supabase in the sandbox?

A Rush sandbox has no Docker, so the agent installs the `docker.io` package and starts `dockerd`, then runs `supabase start`, which brings up Postgres 15 as `config.toml` pins it, applies the migrations and runs `supabase test db` for the pgTAP file. The Next.js build runs against that local project's URL and anon key.

## More Supabase jobs

- [Add Vector Search to a Supabase App](https://getrush.ai/run/add-vector-search/supabase/)
- [Review a Supabase Database Schema](https://getrush.ai/run/database-schema-review/supabase/)
- [Keep Supabase TypeScript Types in Sync With Your Migrations](https://getrush.ai/run/generate-database-types/supabase/)

## The same job on other stacks

- [Add Soft Delete to a tRPC and Prisma App](https://getrush.ai/run/add-soft-delete/trpc/)
- [Add Soft Delete to TypeORM Entities](https://getrush.ai/run/add-soft-delete/typeorm/)
- [Add Soft Delete to a Vapor App](https://getrush.ai/run/add-soft-delete/vapor/)

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