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 with this body:
{
"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"
}
]
}- 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.
- 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.
- 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.
The use case this job follows, with its automation and trigger: Hand off a long task.
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
The same job on other stacks
More jobs like this: Hand off a long task
- Add a Cron Trigger to a Cloudflare Worker (Cloudflare Workers)
- Add a Cron Trigger to a Hono Worker (Hono)