# Schedule Database Jobs With pg_cron on Supabase

Yes. An agent writes a migration that enables pg_cron, adds a purge function for finished tasks, schedules it nightly, tests it on the local stack, and opens a PR.

[Run this on Rush](https://cloud.getrush.ai/run/add-cron-jobs/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 project config before it schedules anything. In a project shaped like Razikus/supabase-nextjs-template, `supabase/config.toml` names the project `sasstemplate`, pins `major_version = 15`, puts the API on `54321` and the database on `54322`, and `supabase/migrations/` holds four dated SQL files. `20250130181641_todo_list.sql` creates `public.todo_list` with `id`, `created_at`, `title`, `urgent`, `description`, `done`, `done_at` and an `owner` foreign key to `auth.users`, enables row level security, grants the table to `anon`, `authenticated` and `service_role`, and adds one policy, `Owner can do everything`, for `authenticated` rows where `owner = auth.uid()` and `authenticative.is_user_authenticated()` holds. `20250107210416_MFA.sql` defines that function as `security definer` SQL checking the JWT's `aal` against verified MFA factors. The Next.js app in `nextjs/` reads the table through the generated `Database` types in `src/lib/types.ts`, whose `Functions` block is empty today, and `src/app/app/table/page.tsx` creates and lists tasks through `SassClient`. Nothing in the project runs on a schedule.

It writes one new migration, `supabase/migrations/<timestamp>_purge_done_todos.sql`, in three parts. First `create extension if not exists pg_cron;`, the statement Supabase's own guides use, which creates the `cron` schema with `cron.job` and `cron.job_run_details`. Second a function, `public.purge_done_todos(retention interval default '30 days')`, declared `security definer` with an empty `search_path`, that deletes rows from `public.todo_list` where `done` is true, `done_at` is not null and `done_at` is older than `now() - retention`, and returns how many rows it removed; `execute` is revoked from `anon` and `authenticated` so a client cannot call it through the API, and only the owner role and the scheduler can. Third the schedule: `select cron.schedule('purge-done-todos', '0 3 * * *', $$select public.purge_done_todos()$$);`, which is safe to rerun because `cron.schedule` with a job name updates the job of that name rather than adding a second one.

Rows marked done with no `done_at` are left alone on purpose, and the pull request says so: the app sets `done` from the UI and `done_at` is not guaranteed, so keying the purge on `done_at` means nothing is deleted on a guess. The agent adds a pgTAP file, `supabase/tests/purge_done_todos.test.sql`, that asserts the extension is installed, that a job named `purge-done-todos` exists in `cron.job` with the nightly schedule, that calling `public.purge_done_todos('0 seconds')` after inserting one old finished row and one open row removes exactly the finished one, and that `authenticated` has no `execute` on the function. Then it regenerates the client types with `supabase gen types typescript --local > nextjs/src/lib/types.ts`, so `Functions` now lists `purge_done_todos` and the frontend build still type-checks.

Before pushing, the agent runs `supabase start`, `supabase db reset` to apply every migration in order, `supabase test db` for the pgTAP file, and a `psql` query on `127.0.0.1:54322` for `select jobname, schedule, command from cron.job`, and pastes that row and the test output into the pull request. It also notes the one step the API cannot do for a hosted project: `pg_cron` runs in the database the migration is applied to, so `npx supabase migrations up --linked`, the command the README already uses, is what turns the schedule on in production.

## Start it with one API call

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

```json
{
  "agent": "claude",
  "input": "Add a scheduled cleanup job with pg_cron to this Supabase project and open a pull request. Read supabase/config.toml, every file in supabase/migrations, nextjs/src/lib/types.ts, nextjs/src/app/app/table/page.tsx and README.md first. Write one new migration that enables pg_cron with create extension if not exists, adds public.purge_done_todos(retention interval default '30 days') as a security definer function with an empty search_path that deletes todo_list rows where done is true and done_at is older than the retention and returns the count, revokes execute from anon and authenticated, and schedules it with cron.schedule('purge-done-todos', '0 3 * * *', ...). Leave rows with a null done_at alone and say why in the pull request. Add supabase/tests/purge_done_todos.test.sql with pgTAP assertions for the extension, the job row, a run with a zero retention that removes only an old finished row, and the revoked grant. Regenerate nextjs/src/lib/types.ts with supabase gen types typescript --local. Run supabase start, supabase db reset, supabase test db and a psql query of cron.job, and paste the job row and the test output in the pull request. Do not change existing migrations or the Next.js UI.",
  "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`, one under `supabase/tests`, and a regenerated `nextjs/src/lib/types.ts` whose `Functions` block gains `purge_done_todos`. The description shows the `cron.job` row with its name, `0 3 * * *` schedule and command, and the `supabase test db` result for the pgTAP assertions.

Applying the pull request to a hosted project is the same `npx supabase migrations up --linked` the README already prescribes; the job appears in `cron.job` and each nightly run is recorded in `cron.job_run_details` with its status, which is where a reviewer looks after the first night.

A reviewer checks the retention default, that the function is `security definer` with an empty `search_path`, that `execute` is revoked from the API roles, that rows with a null `done_at` are untouched, and that the schedule's hour is what they want in UTC. Then they merge and apply.

## Run it on every table that needs retention

A team with several Supabase projects sends one `POST /o/{org}/sessions` per repository with a prompt that names the table, the condition and the retention, and that repository in `resources`. Each run reads its own migrations and generated types, writes a migration and a pgTAP test for that table, and opens its own pull request.

Give the API key a `webhook_url` and Rush POSTs `run.completed` when each turn ends, with the `session_url` to read the pull request and the pasted `cron.job` row from.

## Why run it on Rush?

A scheduled delete is the one migration nobody wants to test in production first. In a Rush sandbox the agent runs the full local Supabase stack, applies every migration from scratch with `db reset`, runs the purge with a zero retention against rows it inserted, and keeps the `psql` and `supabase test db` output in the run's record, so the pull request shows the job registered and the function deleting exactly what it should.

The function's grants and `search_path` are the details that make a `security definer` routine safe, and they are asserted in the pgTAP file, so a later migration that loosens them fails the test rather than passing silently. The same request works on any Supabase project with a `supabase/` directory.

## Frequently asked questions

### Does pg_cron run on the local stack?

Yes. The migration's `create extension if not exists pg_cron` runs during `supabase db reset`, and the pgTAP test reads the job back from `cron.job`. Scheduled runs happen while the local database is up; the test calls the function directly instead of waiting for the hour.

### Why not delete rows with done set but done_at empty?

Because `done_at` is the only timestamp that says when a task was finished. A row with `done` true and no `done_at` would have to be judged by `created_at`, which deletes a task finished yesterday if it was created long ago. The pull request leaves those rows and says so.

### Can a user call the purge from the browser?

No. `execute` on `public.purge_done_todos` is revoked from `anon` and `authenticated`, so the PostgREST API refuses it. The scheduler runs as the database owner, and the pgTAP file asserts the missing grant.

### How do I change the schedule later?

A new migration with `cron.schedule('purge-done-todos', '<new cron>', ...)` updates the job of that name, and `cron.unschedule('purge-done-todos')` removes it. Past runs stay visible in `cron.job_run_details`.

## More Supabase jobs

- [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/)
- [Add a Database Webhook to a Supabase App](https://getrush.ai/run/add-database-webhooks/supabase/)

## The same job on other stacks

- [Add a Scheduled Job to a Vapor App](https://getrush.ai/run/add-cron-jobs/vapor/)

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

- [Add a Cron Trigger to a Cloudflare Worker](https://getrush.ai/run/add-cron-trigger/cloudflare-workers/)
- [Add a Cron Trigger to a Hono Worker](https://getrush.ai/run/add-cron-trigger/hono/)

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