Scheduled jobs with pg_cron

Every SupaHost project can run scheduled jobs with pg_cron. A job is a name, a schedule and a piece of SQL. The SQL runs inside your project's database on schedule, without any server of yours staying awake. Typical jobs delete expired rows, refresh materialized views, roll up statistics, or call an HTTP endpoint such as one of your Edge Functions.

Cron is already set up in every project. Don't run create extension pg_cron: it isn't needed and fails inside a project database. See ERROR: can only create extension in database postgres.

How cron works on SupaHost

Postgres only allows pg_cron in one database per server, and on SupaHost each project is its own database on the instance's Postgres server. So the real scheduler lives in the instance's main database, and each project database has a small cron schema with the familiar functions. They forward to the scheduler and only ever see that project's jobs.

What that means for you:

• cron.schedule(), cron.unschedule(), cron.job and cron.job_run_details work as on Supabase Cloud.
• Jobs run as the postgres role inside your project's database.
• Schedules are in UTC.
• Only the postgres role can use the cron functions and views, so you manage jobs from the Cron tab, Studio's SQL editor or migrations, not through the Data API.
• cron.alter_job() and other pg_cron functions beyond these aren't available. To change a job, edit it in the Cron tab or schedule it again (see below).

Create a job in the dashboard

1. Go to Dashboard → your project → Cron tab.
2. Click New job.
3. Enter a Name (lowercase letters, numbers, - and _).
4. Pick a Schedule preset (Every minute, Every 5 minutes, Hourly, Daily at 03:00 UTC, Every 30 seconds) or type your own cron expression or "N seconds".
5. Write the SQL, or start from an example: Delete old rows, Refresh a view, Call an HTTP endpoint. Each run executes in its own transaction.
6. Save.

Each job in the list has an Active toggle to pause and resume it, plus Edit and Delete. Creating and changing jobs is owner only; team members can see the list. Jobs created with SQL appear here too, and jobs made here are visible in cron.job.

Create a job with SQL

Run this in Studio's SQL editor or put it in a migration. This job runs every night at 02:30 UTC and clears out abandoned bookings in a bookings app.

select cron.schedule(
  'expire-pending-bookings',   -- job name
  '30 2 * * *',                -- every day at 02:30 UTC
  $$
    update public.bookings
    set status = 'expired'
    where status = 'pending'
      and created_at < now() - interval '2 days';
  $$
);

cron.schedule returns the job id. The $$ ... $$ quoting lets you write SQL with single quotes inside.

Job names must match ^[a-z0-9][a-z0-9_-]{0,48}$: 1 to 49 characters, lowercase letters, numbers, - and _, starting with a letter or number. If you call cron.schedule(schedule, sql) without a name, the job is named job- plus 10 hex characters.

For schedules, see Cron syntax and examples. For jobs that run more often than once a minute, use a seconds schedule such as '30 seconds'.

Call an Edge Function or HTTP endpoint

pg_net is installed in every project, so a job can make HTTP requests. net.http_post queues the request and returns at once with a request id; the response arrives in net._http_response a moment later.

First, store the project URL and a key in Vault so they aren't written into the job's SQL:

select vault.create_secret('https://app-one.acme.supahost.space', 'project_url');
select vault.create_secret('<your service_role key>', 'service_role_key');

Then schedule a job that calls your send-reminders function every 15 minutes:

select cron.schedule(
  'send-booking-reminders',
  '*/15 * * * *',
  $$
  select net.http_post(
    url := (select decrypted_secret from vault.decrypted_secrets where name = 'project_url')
           || '/functions/v1/send-reminders',
    headers := jsonb_build_object(
      'Content-Type', 'application/json',
      'Authorization', 'Bearer ' || (select decrypted_secret from vault.decrypted_secrets where name = 'service_role_key')
    ),
    body := jsonb_build_object('triggered_at', now())
  ) as request_id;
  $$
);

The Authorization header passes the function's "Require JWT" check. Any valid project JWT passes that check, including the anon key, so if the function does something sensitive, also check inside the function that the caller used the service_role key or a shared secret. See Running functions on a schedule and Authentication and JWT verification.

The same pattern calls any external URL, for example a webhook in another service. See Database webhooks and HTTP requests with pg_net.

Change, pause or remove a job

-- Change the schedule or SQL: schedule again with the same name
select cron.schedule('expire-pending-bookings', '0 * * * *', $$ ... $$);

-- Remove a job by name or id
select cron.unschedule('expire-pending-bookings');
select cron.unschedule(42);

-- List your jobs
select jobid, jobname, schedule, active, command from cron.job order by jobname;

In pg_cron, scheduling an existing name replaces that job's schedule and command. Because alter_job isn't available, pause a job with the Active toggle in the Cron tab, or unschedule it and schedule it again later.

Things to know

• One run at a time per job. If a run is still going when the next one is due, pg_cron waits for it to finish before starting the next. Keep each run short, and process large backlogs in batches.
• Run history. cron.job_run_details shows the last 1,000 runs of your project's jobs, and runs older than 14 days are deleted daily. See Monitoring and debugging cron jobs.
• Branches and imports. Cron jobs aren't copied to branches, and an import from Supabase Cloud doesn't bring your pg_cron jobs across (the import lists them for you). Recreate them with SQL. For restored projects, see Stopping cron jobs on a restored project or branch.
• Instance load. Jobs run on the same server as everything else in the instance. A heavy job every minute takes CPU from your API. Schedule big jobs for quiet hours.

Related

• Cron syntax and examples
• Monitoring and debugging cron jobs
• Cron job not running
• How do I run scheduled (cron) tasks?