Cleaning up cron run history

On a standard Supabase project, pg_cron writes one row to cron.job_run_details for every run and never deletes them. A job that runs every minute adds about 1,440 rows a day, so people schedule a cleanup job to keep the table small.

On SupaHost you don't need to. History is pruned for you, and the view in your project only shows recent runs.

How history works on SupaHost

• The real run history lives in the instance's postgres database, next to pg_cron itself.
• A built-in job, sh-prune-history, deletes runs older than 14 days. It runs daily at 03:15 UTC.
• In your project, cron.job_run_details is a view that returns the newest 1,000 runs for that project, across all of its jobs.

The 1,000-run window matters if you have frequent jobs. Two jobs every 30 seconds produce about 5,760 runs a day, so the view covers only the last four hours or so. Older runs still exist for up to 14 days, but you can't reach them from your project.

What you see if you try to delete

Supabase's usual cleanup query fails on SupaHost:

delete from cron.job_run_details
where end_time < now() - interval '7 days';

ERROR:  cannot delete from view "job_run_details"

That's expected. The view is read-only, and the pruning job already handles retention. If you copied a cleanup job from a Supabase project, unschedule it:

select cron.unschedule('delete-job-run-details');

Keeping a longer history

If you need more than 14 days, or more than 1,000 runs, copy runs into your own table on a schedule. Here's a small audit table for an invoicing app:

create table public.cron_history (
  runid bigint primary key,
  jobname text,
  status text,
  return_message text,
  start_time timestamptz,
  end_time timestamptz
);

alter table public.cron_history enable row level security;
-- No policies: only postgres and service_role can read it.

select cron.schedule(
  'archive-cron-history',
  '*/15 * * * *',
  $$
  insert into public.cron_history (runid, jobname, status, return_message, start_time, end_time)
  select r.runid, j.jobname, r.status, r.return_message, r.start_time, r.end_time
  from cron.job_run_details r
  left join cron.job j using (jobid)
  where r.end_time is not null
  on conflict (runid) do nothing
  $$
);

Run it more often than your 1,000-run window turns over, or you'll miss runs. Then trim your own table however you like:

select cron.schedule(
  'trim-cron-history',
  '0 4 * * *',
  $$ delete from public.cron_history where start_time < now() - interval '90 days' $$
);

To keep the copy small, you could filter on status = 'failed' and store only the failures.

On SupaHost

• Dashboard → your project → Cron tab → Recent runs shows the last 50 runs.
• In SQL, join cron.job_run_details to cron.job on jobid to see each run's job name, status and return_message. That's the fastest place to look when something breaks.

Related

• Monitoring and debugging cron jobs
• Cron job not running
• Scheduled jobs with pg_cron