Supabase Cron Jobs: pg_cron, and Three Jobs This Backend Didn't Need

Supabase Cron is pg_cron with a dashboard on top: you schedule recurring jobs in cron syntax inside your own Postgres, and each job runs SQL, calls a database function, invokes an Edge Function or hits a webhook. Jobs live in the cron.job table and every run is recorded in cron.job_run_details, so the schedule is schema you can migrate and the history is a table you can query.

That is genuinely useful, and it is also the easiest thing in a Postgres backend to reach for too early. This storefront's backend is eight migrations, 271 lines of SQL, six tables and four Postgres functions — with zero scheduled jobs, and three of the places you would most expect a nightly job are the interesting part of that number.

How you schedule one

-- Enable the extension once (Supabase: Database → Extensions, or in a migration)
create extension if not exists pg_cron;

-- Every night at 03:00 UTC: delete audit rows older than 90 days
select cron.schedule(
  'purge-old-download-events',
  '0 3 * * *',
  $$ delete from public.download_events where created_at < now() - interval '90 days' $$
);

-- Inspect and remove
select jobid, jobname, schedule, active from cron.job;
select * from cron.job_run_details order by start_time desc limit 20;
select cron.unschedule('purge-old-download-events');

Two operational numbers are worth having before you design around it: Supabase's own guidance is no more than 8 jobs running concurrently, and no single job running longer than 10 minutes. Sub-minute schedules (every 1–59 seconds) are supported, which is a strong hint about the kind of work it is for — short, frequent, idempotent.

The three jobs this schema didn't need

1. A cleanup job for the rate-limit window

Download limits here are hourly and counted from a table, not from memory:

-- supabase/migrations/0001_commerce_init.sql
create or replace function public.downloads_in_last_hour(uid uuid)
returns int language sql stable security invoker
set search_path = public, pg_temp as $$
  select count(*)::int from public.download_events
  where user_id = uid and created_at > now() - interval '1 hour';
$$;

A fixed-window counter table would need a job to reset it. A rolling window computed from timestamps needs nothing: the old rows simply stop matching created_at > now() - interval '1 hour', and the (user_id, created_at) index keeps the count cheap as the table grows. The scheduled reset disappears because the window is derived rather than stored — the same move as computing a balance from a ledger instead of maintaining a balance column someone has to recompute.

The defect on this path is worth the detour, because it is also not a cron problem and was once mistaken for one. The original code read the count and then inserted the audit row as two separate statements:

-- Root cause (CWE-367 TOCTOU): read downloads_in_last_hour(), then a
-- SEPARATE insert. Under concurrency, N requests could all read the same
-- count, all pass the `< limit` check, and all insert.

Migration 0007_atomic_download_rate_limit.sql (67 lines) collapses it into one RPC serialized per user:

perform pg_advisory_xact_lock(hashtextextended(p_user_id::text, 0));

select count(*)::int into recent
from public.download_events
where user_id = p_user_id
  and created_at > now() - interval '1 hour';

if recent >= p_limit then
  return -1;  -- over limit — record nothing
end if;

insert into public.download_events (…) values (…);
return recent + 1;

The lock is transaction-scoped, so it releases on commit with nothing to clean up, and it is keyed on the user-id hash so different users never contend. No job, no lease table, no stale-lock sweeper. (It does depend on READ COMMITTED, Postgres's default — under REPEATABLE READ the second caller's snapshot could freeze before the first caller's insert, and the comment in the migration says so.)

2. A retention job for the audit trail

The download audit has a retention rule: it is kept, anonymised, when a buyer deletes their account, because the abuse trail has to survive a self-delete. The obvious implementation is a nightly job that finds orphaned rows and nulls their user_id. The actual implementation is a foreign key:

-- supabase/migrations/0005_retain_anonymized_download_audit.sql
alter table public.download_events alter column user_id drop not null;
alter table public.download_events drop constraint download_events_user_id_fkey;
alter table public.download_events add constraint download_events_user_id_fkey
  foreign key (user_id) references auth.users(id) on delete set null;

Three lines, and the anonymisation happens inside the same transaction as the account deletion. A nightly job would have left a window — hours long — in which deleted users' rows still carried their id, which for a privacy commitment is the whole thing you were trying to guarantee. When correctness depends on timing, a schedule is the wrong tool: on delete set null, on delete cascade and a trigger all run at the moment the change happens.

3. An expiry job for the refund window

Refunds here are available for 14 days and blocked once a buyer has downloaded. A job marking orders "expired" every night would be a reasonable-looking design. Instead the whole question is answered at read time, by a pure function:

// src/lib/refundEligibility.ts
export const REFUND_WINDOW_DAYS = 14;

const windowMs = REFUND_WINDOW_DAYS * 24 * 60 * 60 * 1000;
if (input.now.getTime() - input.createdAt.getTime() > windowMs) {
  return { eligible: false, reason: "window_expired" };
}
return { eligible: true };

The function takes now as an argument, which is what makes it testable without freezing a clock, and both the dashboard button's disabled state and the server action's enforcement call it — so they cannot disagree. A nightly expiry job introduces exactly that disagreement: between 00:00 and whenever the job runs, the database says eligible and the rule says not.

The one scheduled-looking thing this schema does have is not scheduled either. When a refund webhook lands, refund_order() revokes the slots, marks the order, and resolves any pending request — all in one transaction:

update public.entitlement_slots set status = 'revoked' where order_id = v_order_id and status <> 'revoked';
update public.orders set status = 'refunded' where id = v_order_id;
update public.refund_requests set status = 'resolved', resolved_at = now()
  where order_id = v_order_id and status = 'pending';

Event-driven, not time-driven. Which is the actual rule underneath all three cases.

The rule, and where it stops applying

A cron job is for work that must happen when nobody is asking for it. If a user request, a webhook, or a foreign key can carry the work instead, it will be more correct, because it happens at the moment the state changes rather than up to 24 hours later.

That leaves a real set of jobs it cannot cover, and they are the ones worth pg_cron:

And four places to run them, which is the decision people actually mean by "Supabase cron jobs":

The last row is not a scheduler, and it is the one this backend uses three times.

Troubleshooting

Frequently asked questions

Is Supabase Cron the same as pg_cron? Yes — Supabase Cron is a dashboard and a natural-language schedule picker over the pg_cron extension running in your project's database. Anything you can do with cron.schedule() in SQL you can do from the UI, and jobs created either way appear in the same cron.job table.

Can a cron job call a Supabase Edge Function? Yes, and it is the normal pattern for work that needs network access or a third-party secret: the job makes an HTTP request to the function (via pg_net), and the function does the work. For large batches, Supabase's own guidance is to pair it with Queues so one invocation does not have to finish everything inside the 10-minute budget.

Do I need pg_cron to clean up old rows? Only if the deadline is not already derivable from data you store. Counting inside a rolling time window needs no cleanup at all; anonymising on account deletion is a foreign-key action. Reach for a job when a row must change state at a time nothing else observes — a hard delete 90 days after a soft delete is the clean example.

Does pg_cron work on the free tier, and does it work locally? The extension is available to enable on Supabase projects, and it is part of the Postgres image, so it also runs in the local Docker stack. Be deliberate about which database a job is scheduled on, though — it is per-project state, like your auth redirect settings, not something your migration files carry between projects unless you write the cron.schedule() call into one.

Templates that keep the backend simple

Every decision above happened in SQL and one pure TypeScript function — not in the UI. The Next.js landing page templates and Tailwind landing page templates render statically and leave the data layer to you, which is why adding a Postgres backend later is a scoped job rather than a rewrite.

Templates in this post

ASoc Nexus, ASoc Nimbus and ASoc Nova are Next.js + Tailwind landing page templates with no scheduled work of their own, so the first cron job in your project is one you chose rather than one you inherited.