Scheduling jobs in Supabase with pg_cron

To schedule a job in Supabase, call cron.schedule() with a job name, a cron expression and the SQL to run, for example select cron.schedule('nightly-cleanup', '0 3 * * *', $$delete from events where created_at < now() - interval '30 days'$$);. This uses pg_cron, a Postgres extension that runs jobs inside the database itself, so you don't need an outside scheduler. This guide covers the syntax, practical examples, monitoring and the mistakes that catch people out.

What pg_cron is

pg_cron is an open-source Postgres extension that runs SQL on a schedule, using the same five-field syntax as Unix cron. Supabase uses it as the engine behind Supabase Cron.

Once it's installed, pg_cron adds a cron schema with:

• cron.job: one row per scheduled job.
• cron.job_run_details: one row per run, with its status and output.
• Functions such as cron.schedule, cron.unschedule and cron.alter_job.

On Supabase Cloud you turn it on from the dashboard (Integrations, then Cron), and you can create jobs there or in SQL. On SupaHost, pg_cron is built into every project, so you don't run create extension pg_cron. Jobs you create in SQL also appear in the dashboard's Cron tab (cron docs).

Cron syntax

A cron expression has five fields, separated by spaces:

* means "every", */n means "every n", and commas list values. Some common schedules:

The "N seconds" interval form

For jobs that need to run more often than once a minute, pg_cron accepts an interval string from '1 seconds' to '59 seconds' in place of a cron expression:

select cron.schedule('process-queue', '10 seconds', 'select process_pending_jobs()');

You can't mix seconds with other fields. On Supabase Cloud, the quickstart says this needs Postgres 15.1.1.61 or later.

The function signatures

From the pg_cron README:

cron.schedule(job_name text, schedule text, command text) returns bigint
cron.schedule(schedule text, command text) returns bigint
cron.unschedule(job_name text) returns boolean
cron.unschedule(job_id bigint) returns boolean
cron.alter_job(job_id bigint, schedule text default null, command text default null,
               database text default null, username text default null,
               active boolean default null) returns void

Always give jobs a name. Names are easier to read in cron.job and let you unschedule a job without looking up its ID.

Examples

Delete old rows

Dollar quoting ($$ ... $$) saves you from escaping single quotes inside the command:

select cron.schedule(
  'delete-old-events',
  '30 3 * * *',
  $$ delete from public.events where created_at < now() - interval '30 days' $$
);

If the table is large, add an index on created_at so the delete doesn't scan every row each night.

Refresh a materialized view

select cron.schedule(
  'refresh-daily-stats',
  '*/15 * * * *',
  $$ refresh materialized view concurrently public.daily_stats $$
);

CONCURRENTLY lets readers keep querying the view during the refresh. It needs a unique index on the view (plain columns, no WHERE clause), and the view must have been populated once already (PostgreSQL docs). Without CONCURRENTLY, the refresh can block reads until it finishes.

Call a database function

Put longer logic in a function and keep the job a one-liner. That makes it testable on its own:

select cron.schedule('expire-trials', '0 * * * *', 'select public.expire_trials()');

Call an HTTP endpoint with pg_net

pg_cron only runs SQL, so to call a webhook or API you combine it with the pg_net extension. Its POST function looks like this:

net.http_post(
    url text,
    body jsonb default '{}'::jsonb,
    params jsonb default '{}'::jsonb,
    headers jsonb default '{"Content-Type": "application/json"}'::jsonb,
    timeout_milliseconds int default 2000
) returns bigint

A job that pings an endpoint every hour:

select cron.schedule(
  'hourly-webhook',
  '0 * * * *',
  $$
  select net.http_post(
    url := 'https://example.com/hooks/hourly',
    body := jsonb_build_object('sent_at', now()),
    timeout_milliseconds := 5000
  );
  $$
);

pg_net is asynchronous. The function returns a request ID right away, and the request is sent after the transaction commits. Responses land in net._http_response, which keeps them for 6 hours by default.

Call an Edge Function

An Edge Function is an HTTP endpoint too, so the pattern is the same. Don't hard-code keys in the job text: anyone who can read cron.job can see them. Supabase's scheduling guide stores the project URL and key in Vault and reads them at run time:

select vault.create_secret('https://project-ref.supabase.co', 'project_url');
select vault.create_secret('YOUR_SECRET_KEY', 'secret_key');

select cron.schedule(
  'invoke-report-function',
  '0 6 * * *',
  $$
  select net.http_post(
    url := (select decrypted_secret from vault.decrypted_secrets where name = 'project_url')
           || '/functions/v1/daily-report',
    headers := jsonb_build_object(
      'Content-Type', 'application/json',
      'apikey', (select decrypted_secret from vault.decrypted_secrets where name = 'secret_key')
    ),
    body := jsonb_build_object('time', now())
  );
  $$
);

Use a secret key here, never the public one, because anyone with the public key could call a function that accepts it. Supabase's guide also notes that the decrypted key sits in net.http_request_queue until the request goes out, so any role that can log in to the database could read it during that time.

Changing and removing jobs

Remove a job by name or by ID:

select cron.unschedule('delete-old-events');
select cron.unschedule(42);

Change the schedule, or pause a job without deleting it, with cron.alter_job:

select cron.alter_job(
  job_id := (select jobid from cron.job where jobname = 'refresh-daily-stats'),
  schedule := '0 * * * *'
);

select cron.alter_job(
  job_id := (select jobid from cron.job where jobname = 'refresh-daily-stats'),
  active := false
);

Reading cron.job and cron.job_run_details

List your jobs:

select jobid, jobname, schedule, command, active
from cron.job
order by jobid;

See the last runs of one job:

select status, return_message, start_time, end_time
from cron.job_run_details
where jobid = (select jobid from cron.job where jobname = 'delete-old-events')
order by start_time desc
limit 10;

Find recent failures across all jobs:

select j.jobname, d.return_message, d.start_time
from cron.job_run_details d
join cron.job j using (jobid)
where d.status = 'failed'
  and d.start_time > now() - interval '1 day'
order by d.start_time desc;

Keeping run history small

pg_cron writes a row to cron.job_run_details for every run and never cleans it up. A job running every 10 seconds adds 8,640 rows a day. Schedule a cleanup job:

select cron.schedule(
  'purge-cron-history',
  '0 0 * * *',
  $$ delete from cron.job_run_details where end_time < now() - interval '7 days' $$
);

Common pitfalls

Times are GMT/UTC, not your local time. pg_cron uses GMT unless the server's cron.timezone setting is changed. 0 9 * * * means 09:00 UTC. Convert before you schedule, and remember that daylight saving shifts your local time against UTC twice a year.

Long jobs queue up. pg_cron never runs a job at the same time as itself. If a run is still going when the next one is due, the next run waits and starts after it. A job that takes longer than its interval falls further and further behind. Supabase recommends keeping each job under 10 minutes and running at most 8 jobs at once. Process work in batches with a limit if a job might run long.

"Succeeded" doesn't mean the HTTP call worked. For pg_net jobs, the cron run succeeds as soon as the request is queued. Check net._http_response for the actual status code. The default timeout is 2 seconds, so raise timeout_milliseconds for slow endpoints.

Permissions and identity. A job runs as the role that scheduled it, usually postgres if you used the SQL editor. There's no signed-in user, so auth.uid() returns null inside a job. Don't write job SQL that depends on it. Users can only see and change their own jobs unless they're superusers or have bypassrls.

Jobs don't always move with your data. When you migrate a project, check cron.job on the new side before you assume your schedules came along. SupaHost's one-click import from Supabase Cloud doesn't copy pg_cron jobs either. It lists them so you can recreate them (migration guide). Keep your cron.schedule calls in a migration file so they're easy to reapply.

FAQ

How do I create a cron job in Supabase?

Run select cron.schedule('job-name', '*/5 * * * *', $$ your sql $$); in the SQL editor, or use the Cron page in the dashboard. The job is stored in cron.job and starts on the next matching minute.

Can pg_cron run a job every few seconds?

Yes. Use an interval such as '30 seconds' (from 1 to 59 seconds) in place of the cron expression. On Supabase Cloud this needs Postgres 15.1.1.61 or newer.

What time zone does Supabase cron use?

GMT/UTC by default. Convert your local times to UTC when writing the schedule.

How do I stop or delete a Supabase cron job?

Call cron.unschedule('job-name') to delete it, or cron.alter_job(job_id := ..., active := false) to pause it and keep its definition.

How do I see if a cron job failed?

Query cron.job_run_details for rows where status = 'failed'. The return_message column holds the error. For HTTP jobs, also check net._http_response.

Do I need to enable pg_cron on SupaHost?

No. pg_cron is built into every SupaHost project, so you can call cron.schedule straight away without create extension.