Monitor Supabase Cron Jobs

Supabase Cron records each run in the cron.job_run_details table. To get an alert when a run fails or the job stops running, ping a Chronos monitor from the same SQL job with the pg_net extension.

Add the ping to the job

1. Create a monitor with the same cron expression as the Supabase Cron job, and set its timezone to UTC. Supabase Cron runs in UTC, and a monitor’s timezone defaults to your account’s. If you changed cron.timezone, use that timezone instead.
2. Enable the pg_net extension in your Supabase project.
3. Schedule the job with the work and the pings in one block:

select cron.schedule(
  'nightly-cleanup',
  '0 3 * * *',
  $$
  do $job$
  begin
    delete from events where created_at < now() - interval '30 days';

    perform net.http_get(
      url := 'https://ping.chronos.sh/k3Jx9Qm2TnV7bRw4YcZ8dHfL',
      timeout_milliseconds := 10000
    );
  exception when others then
    perform net.http_post(
      url := 'https://ping.chronos.sh/k3Jx9Qm2TnV7bRw4YcZ8dHfL/fail',
      body := jsonb_build_object('error', SQLERRM),
      timeout_milliseconds := 10000
    );
  end
  $job$;
  $$
);

pg_net sends requests only after the transaction commits, so the success ping goes out once the work is saved. When the work raises an error, the exception branch rolls the work back and queues the fail ping with the error message, which shows up in the down alert.

Caution

Because the exception branch handles the error, Supabase records the run as succeeded in cron.job_run_details. Chronos still alerts. To mark the run failed there too, re-raise the error. The re-raise rolls back the transaction, so the fail ping never goes out and the monitor goes down after the grace period.

timeout_milliseconds gives each ping 10 seconds. A ping that takes longer shows as a timed-out row in net._http_response, and the monitor goes down after the grace period.

Check it works

Run the block between $$ and $$ once in the SQL editor. The ping goes out when the statement commits, and the monitor moves from new to up. To test the alert, add raise exception 'test failure'; before the perform net.http_get line and run it again.