Background Jobs

Schedule and manage recurring tasks with pg_cron.

Overview

Background jobs are powered by pg_cron, a PostgreSQL extension that runs scheduled tasks directly in the database. This means zero additional infrastructure: no Redis, no separate worker process, no external job queue.

Built-in Jobs

Your app comes with three pre-configured jobs:

Job	Schedule	Description
`cleanup-login-attempts`	Every 6 hours	Removes failed login attempts older than 24 hours
`cleanup-expired-notifications`	Daily at 3 AM	Deletes notifications past their expiry date
`cleanup-job-history`	Weekly (Sunday 4 AM)	Prunes job execution history older than 30 days

Admin Panel

Super admins can view and manage jobs from Admin > Jobs:

View all scheduled jobs and their last execution status
See execution history with timestamps and results
Manually trigger any job with the "Run Now" button

Adding Custom Jobs

To add a new scheduled job, create a new SQL migration:

-- supabase/migrations/XXXXXX_my_custom_job.sql

SELECT cron.schedule(
  'my-job-name',           -- unique job name
  '*/30 * * * *',          -- cron expression (every 30 minutes)
  $$
  DO $$
  DECLARE
    affected INTEGER;
  BEGIN
    -- Your SQL logic here
    DELETE FROM some_table WHERE expired_at < now();
    GET DIAGNOSTICS affected = ROW_COUNT;

    -- Log the result
    PERFORM public.log_cron_job('my-job-name', 'succeeded',
      'Processed ' || affected || ' rows');
  EXCEPTION WHEN OTHERS THEN
    PERFORM public.log_cron_job('my-job-name', 'failed', NULL, SQLERRM);
  END $$;
  $$
);

Run npm run db:migrate to apply the migration.

Cron Expression Reference

┌───────────── minute (0-59)
│ ┌───────────── hour (0-23)
│ │ ┌───────────── day of month (1-31)
│ │ │ ┌───────────── month (1-12)
│ │ │ │ ┌───────────── day of week (0-6, Sunday=0)
│ │ │ │ │
* * * * *

Common patterns:

Expression	Meaning
`/5 * * *`	Every 5 minutes
`0 /6 * *`	Every 6 hours
`0 3 * * *`	Daily at 3:00 AM
`0 0 * * 0`	Weekly on Sunday at midnight
`0 0 1 * *`	Monthly on the 1st at midnight

Managing Jobs

Remove a Job

SELECT cron.unschedule('job-name');

List All Jobs

SELECT * FROM cron.job;

View Execution Log

SELECT * FROM cron.job_run_details
ORDER BY start_time DESC
LIMIT 20;

Job History

All job executions are logged in the cron_job_history table. Use public.log_cron_job() in your custom jobs to record results:

-- Log success
PERFORM public.log_cron_job('my-job', 'succeeded', 'Processed 42 rows');

-- Log failure
PERFORM public.log_cron_job('my-job', 'failed', NULL, 'Connection timeout');

History is automatically cleaned up after 30 days by the cleanup-job-history job.