Cron Jobs Registry

Shipped: 2026-04-29 Migration: infrastructure/medbase/migrations/036_cron_jobs_registry.sql Admin UI: /super-admin/cron-jobs (CronJobsPage.tsx)

Problem

Before this, recurring schedules were set with bare cron.schedule(...) calls inside individual migrations. Once a job was scheduled, no one could see, audit, edit, or disable it without ssh + psql and direct edits to cron.job — exactly the kind of “rogue or strayaway cron” we don’t want.

Pattern

The cron_jobs table is the source of truth for every pg_cron schedule the platform owns.

admin UI / migration ──INSERT/UPDATE/DELETE──▶ cron_jobs row
                                                    │
                                          AFTER ROW trigger
                                                    │
                                  cron.schedule / cron.unschedule
                                                    ▼
                                              cron.job (pg_cron)

A row’s enabled = true means the schedule is applied to pg_cron. enabled = false means the row is preserved (audit trail) but unscheduled. active = false is a soft-delete that also unschedules.

The trigger swallows pg_cron errors so a missing pg_cron extension or invalid schedule doesn’t block the row write — the failure is logged via RAISE WARNING and the row stays in a “drift-not-scheduled” state, visible in the admin UI.

Schema

Column	Notes
jobname (UNIQUE)	The pg_cron job name. The unschedule key.
description	Free text shown in the admin UI.
category	ack | orchestrator | rcm | reporting | gold-layer | maintenance | general — for filtering.
schedule	5-field cron expression (UTC).
command	The SQL pg_cron runs. Typically SELECT net.http_post(...) to invoke an edge function.
enabled	Toggleable from the admin UI without losing the row.
last_run_at/last_run_status/last_run_message	Telemetry (filled by the runtime — see “Last-run telemetry” below).
managed_by	migration (seeded by SQL), admin (created via UI), system (bot-managed).
active	Soft-delete flag.

Drift detection

cron_jobs_status view joins each row to cron.job and computes sync_status:

Status	Meaning
in-sync	Row matches pg_cron entry.
archived	Row’s active=false; not expected to be scheduled.
drift-not-scheduled	Row is enabled but no pg_cron entry. (pg_cron not installed, schedule rejected, or someone unscheduled it.)
drift-still-scheduled	Row is disabled but pg_cron still has it. (Trigger error, or someone scheduled it directly.)
drift-schedule	Row schedule differs from pg_cron schedule.
drift-paused-in-pgcron	pg_cron has active=false for the job but the row says enabled.

The admin UI flags drift rows with a warning chip and surfaces the actual pg_cron schedule alongside the desired one.

Reconcile

cron_jobs_reconcile() RPC fixes everything in one shot:

1. Unschedule any pg_cron entry whose jobname has no matching active cron_jobs row.
2. Re-apply every active row by bumping updated_at (which fires the sync trigger).

Returns a list of {jobname, action} pairs. The admin UI exposes this as a “Reconcile” button.

Adding a new scheduled job

From a migration (preferred for system-owned jobs):

INSERT INTO cron_jobs (
  jobname, description, category, schedule, command, enabled, managed_by
) VALUES (
  'my-new-tick',
  'Does the thing every 5 minutes',
  'orchestrator',
  '*/5 * * * *',
  $cmd$ SELECT net.http_post(url := '...', body := '{}'::JSONB) $cmd$,
  FALSE,             -- start disabled; admin enables when ready
  'migration'
)
ON CONFLICT (jobname) DO NOTHING;

From the admin UI: open /super-admin/cron-jobs → “New job” → fill the form. The managed_by is set to admin automatically.

RLS

Read + write restricted to super-admins via the cron_jobs_is_super_admin() helper, which inspects the JWT roles claim for super_admin / super-admin / superadmin / admin (case-insensitive). The Supabase service-role key bypasses RLS as usual, so backend services / edge functions can still read/write.

Setup checklist for a fresh region

1. Run migration 036 in Studio.
2. Set the GUCs once per project (only if you actually want the secrets that the seeded ack-escalator-tick uses):

   ALTER DATABASE postgres SET app.settings.edge_functions_url
     = 'https://<project>.functions.supabase.co';
   ALTER DATABASE postgres SET app.settings.service_role_key
     = '<service-role-jwt>';
   

3. Open /super-admin/cron-jobs as a super-admin and toggle the rows you want enabled.
4. Click Reconcile to apply.
5. Watch the “Sync” column — should all flip to In sync within seconds (the trigger fires on every write + the realtime subscription auto-refreshes the page).

Last-run telemetry (TODO)

Currently last_run_at/last_run_status are not auto-populated. pg_cron writes detailed run history to cron.job_run_details — a follow-up should add a periodic update that copies the latest entry per jobname into our row. For now, admins can inspect cron.job_run_details directly via Studio.