Running scheduled jobs that call an API, with Supabase pg_cron and edge functions

You have work that has to happen on a clock and reach outside the database: drain a queue every few seconds, re-fetch overnight whatever a provider forgot to tell you about, expire a batch of signed URLs. Supabase pg_cron ships with the platform and schedules that in one SQL statement, so it looks like the answer.

Then you write the job and find that pg_cron runs SQL and only SQL. freeCodeCamp's write-up puts the limit plainly: the extension "can't send an HTTP request, push to a queue, or send an email" on its own, and it says to move that work into your application or a workflow engine.

You don't have to leave Postgres to get there. Give it pg_net for the outbound call and Vault for the credentials, and the cron job reaches an edge function on any schedule you choose. Supabase publishes that wiring in its own guide. We spend our time on what it leaves open: sub-minute intervals and what they cost, plus a claim size that keeps each run inside its interval. Then how you stop a job, and the three queries that separate one which never fired from one that fired and failed downstream.

Can Supabase pg_cron call an edge function?

Not on its own, but with pg_net beside it, yes. pg_cron holds the schedule and runs SQL, pg_net turns a SQL statement into an outbound HTTP request, and Supabase Vault holds the credentials that request needs.

Keeping the schedule inside Postgres is what makes this survive the things that kill external schedulers. The job backs up and restores with the database, so there is no scheduler host to keep alive and no second place where a job can quietly stop existing.

1. Enable both extensions. Run this in the SQL editor, or toggle them under Database and then Extensions.

create extension if not exists pg_cron;
create extension if not exists pg_net;

2. Put the function URL and the service-role key in Vault. Two secrets, created once, read back by the job at run time.

select vault.create_secret(
  'http://host.docker.internal:54321/functions/v1', 'fastpix_functions_url');
select vault.create_secret('<SERVICE_ROLE_KEY>', 'fastpix_service_role_key');

3. Schedule the job. The second argument is the schedule and the third is the SQL to run.

select cron.schedule(
  'fastpix-drain-worker',
  '10 seconds',
  $$
  select net.http_post(
    url := (select decrypted_secret from vault.decrypted_secrets
            where name = 'fastpix_functions_url') || '/fastpix-worker',
    headers := jsonb_build_object(
      'Content-Type', 'application/json',
      'Authorization', 'Bearer ' || (select decrypted_secret
                                     from vault.decrypted_secrets
                                     where name = 'fastpix_service_role_key')
    ),
    body := '{}'::jsonb,
    timeout_milliseconds := 5000
  );
  $$
);

One detail decides everything after this. net.http_post returns a request id immediately and sends the request in the background, so your cron job finishes in milliseconds whether the edge function takes half a second or times out. A green run in the cron history therefore tells you the request was queued, and nothing more.

Where do the credentials live?

In Supabase Vault, read back through vault.decrypted_secrets inside the scheduled statement. A URL and a service-role key are both tempting to paste into the migration that creates the job, and both then sit in version control, in every clone of the repository, and in the diff of whoever opens the pull request.

Use host.docker.internal rather than localhost for local development, because the URL has to be reachable from inside the Postgres container where localhost means the container itself. In production the same secret holds https://<ref>.supabase.co/functions/v1, and vault.update_secret replaces a value that already exists.

Missing secrets produce the worst kind of failure, which is a quiet one. Events arrive, rows land in the queue, and nothing drains them, because the job that would have raised an error never got as far as making a request. We put the Vault step ahead of the webhook setup in our own docs for that reason.

Supabase cron jobs every 10 seconds

Pass an interval string in place of a five-field cron expression, and the schedule drops below the one-minute floor that standard cron syntax bottoms out at.

-- once a day at 03:00, classic five-field syntax
select cron.schedule('fastpix-nightly-reconcile', '0 3 * * *', $$ ... $$);

-- six times a minute, interval syntax
select cron.schedule('fastpix-drain-worker', '10 seconds', $$ ... $$);

Every reference documents the interval form as a feature, and what decides whether you reach for it is the shape of the work rather than the syntax. A sub-minute schedule earns its place when three things are true. The work has to arrive continuously rather than in a nightly batch, so that a minute of waiting is a minute of stale data. Each run has to be cheap, because you're about to run it six times as often. And the work has to be idempotent, because two workers will eventually be in flight together.

Webhook processing fits all three. A video finishes encoding, the provider fires an event, and something has to pick it up, so ten seconds becomes the ceiling on how long that event waits. A nightly report fits none of the three and belongs on a five-field expression.

What if a run overruns?

pg_cron won't start a second copy of the same job. The pg_cron README states that only one instance of each job runs at a time, and a second run triggered before the first one finishes is queued and starts as soon as the first one completes.

That protection is real, and it does nothing for you here. The scheduled statement is a net.http_post that returns in milliseconds, so the cron job never runs long, never queues and never engages the serialisation. The overlap moves into the edge function instead, where two invocations of the same worker can be running together.

Overlap is safe only when the queue hands out work exclusively. A pgmq read makes each claimed message invisible for a visibility window, so a second worker starting mid-run takes different messages rather than the same ones twice. That property, not the schedule, is what makes the drain correct.

The other half is bounding what a run claims, and it is worth being exact about which knob does that. Our worker reads a fixed ten messages per run; that number is in the code and no environment variable moves it. FASTPIX_MAX_READ_CT is a retry cap rather than a batch size: it archives a message once it has been handed out seven times, so a poison event stops consuming runs instead of blocking the queue forever.

When arrivals outpace the drain you cannot raise the claim size, because ten per run is fixed, so the interval in 0003_fastpix_setup_cron_job.sql is the only lever you own. Shortening it adds a connection per run, and on a small instance that cost bites before throughput does.

How do I stop a job?

cron.unschedule takes the job name, or the job id from cron.job when a name has been reused, and removes the schedule outright.

select cron.unschedule('fastpix-drain-worker');

-- when the name is ambiguous, unschedule by id
select cron.unschedule(jobid) from cron.job where jobname = 'fastpix-drain-worker';

Unscheduling is the bluntest lever and rarely the one you want mid-incident. Setting active to false on the cron.job row pauses the job while keeping its definition, which is what you reach for while debugging the worker behind it, and scheduling the same name again replaces that definition rather than adding a second job. Pause before you dig, because a job firing six times a minute against a broken worker fills cron.job_run_details faster than you can read it.

Did the job fire or fail?

Read three tables in order, because a job that never fired and a worker that failed produce the same symptom, which is a table that stopped filling.

select j.jobname,
       j.active,
       max(r.start_time)                            as last_run,
       count(*) filter (where r.status = 'failed')  as failed_runs
from cron.job j
left join cron.job_run_details r
       on r.jobid = j.jobid
      and r.start_time > now() - interval '1 hour'
group by j.jobname, j.active;

No recent last_run means the schedule is not running, so check active and that the job was created in the right database. A succeeded status is where people stop and it isn't an answer: it means the SQL ran, and that SQL was a net.http_post which queued a request. For the response, read pg_net's own table.

select id, status_code, created
from net._http_response
order by created desc
limit 10;

Then check whether the work landed. Our schema keeps a raw event log for this, and the troubleshooting page maps each symptom below to its cause. The triage query is three columns wide:

select "type", "processStatus", "lastError"
from fastpix.webhook_events
order by "receivedAt" desc
limit 10;

Column names match the FastPix API, so they are camelCase and need double quotes: select mediaId fails where select "mediaId" works.

Read the three results together and the diagnosis falls out. No cron rows means the schedule never fired. Cron rows plus a non-2xx in net._http_response means the function is being called and is failing. Cron rows, a 200 response, and every event still at received means the worker is running but not finishing its claim.

Supabase pg_cron in production

Our Supabase integration ships two Supabase Cron jobs, and between them they cover both failure directions: a drain runs every ten seconds, and a reconcile runs nightly.

The drain is the path in. A FastPix webhook hits the fastpix-webhook edge function, which verifies the signature and pushes the event onto a pgmq queue, and the cron job posts to fastpix-worker, which claims its bounded batch, re-fetches each resource from our API rather than trusting the payload, and writes fastpix.media, fastpix.live_streams or fastpix.uploads. We re-fetch because a webhook body is a snapshot of the moment the event fired, and the delivery side of that contract is covered in automating video uploads with webhook notifications.

The nightly reconcile answers silence, which is the harder failure. Building it yourself means a second scheduled job that walks the provider's API and diffs it against your tables, and here it takes a window in hours:

npx @fastpix/supabase reconcile      # last 24 hours
npx @fastpix/supabase reconcile 48   # last 48 hours, CLI only

One operational caveat comes with the ten-second drain, and it's the one we had to design around. Edge functions open a direct, non-pooled Postgres connection at that rate, so on a small instance you'll approach the connection limit, and the symptom is connection errors in the function logs rather than anything in the cron history. Point the functions at Supavisor, or lengthen the interval in 0003_fastpix_setup_cron_job.sql.

Run the drain on your project

Set this up locally before you reason about production, because every failure mode above is visible on a laptop:

npx @fastpix/supabase init

That writes the three migrations, creates the four edge functions, and sets verify_jwt to false for the webhook endpoint. Add the two Vault secrets, restart Supabase, upload a video, and watch a row appear in fastpix.media within seconds. The integration guide has the full sequence, the troubleshooting section maps each symptom to its cause, and pointing a scheduled job at your own edge function needs only the free plan, ten videos and no card.