pg-cron-scheduled-jobs
Skill10 starsChanged 6 months agoMITView on GitHub
Use when scheduling Postgres jobs with pg_cron — installing the extension, writing cron expressions, scheduling SQL or function calls, monitoring runs, unscheduling, and avoiding overlap. Companion to background-jobs-and-cron; this is the deep dive.
In this repository56 other files it gives its agents.
- accessibility-passSkill
- analytics-eventsSkill
- api-error-handlingSkill
- audit-logging-backendSkill
- background-jobs-and-cronSkill
- dark-mode-and-themingSkill
- dashboard-charts-rechartsSkill
- edge-functions-and-webhooksSkill
- email-and-notificationsSkill
- error-states-and-empty-uiSkill
- feature-flags-and-togglesSkill
- file-upload-storageSkill
- i18n-multi-languageSkill
- lovable-prompting-playbookSkill
- lovable-ship-checklistSkill
- migration-playbookSkill
- multi-tenant-backendSkill
- onboarding-copySkill
- optimistic-updatesSkill
- payment-failed-and-recoverySkill
- payment-webhook-idempotencySkill
- payment-webhook-testingSkill
- performance-budgetSkill
- pg-cron-recipesSkill
- pg-cron-with-pg-net-edgeSkill
- postgres-full-text-searchSkill
- postgres-triggers-and-functionsSkill
- pricing-and-billingSkill
- programmatic-seo-content-qualitySkill
- programmatic-seo-pagesSkill
- rate-limiting-edgeSkill
- realtime-and-subscriptionsSkill
- refactor-safe-diffSkill
- release-notesSkill
- seo-core-web-vitalsSkill
- seo-landing-pageSkill
- seo-meta-tags-spaSkill
- seo-sitemap-generationSkill
- seo-ssr-and-prerenderingSkill
- seo-structured-dataSkill
- server-input-validationSkill
- shadcn-patternsSkill
- soft-delete-and-archivingSkill
- stripe-one-time-payment-webhooksSkill
- stripe-payment-webhooksSkill
- supabase-auth-flowsSkill
- supabase-database-designSkill
- supabase-rls-and-authSkill
- support-replySkill
- tanstack-infinite-queriesSkill
- tanstack-mutations-and-invalidationSkill
- tanstack-prefetch-and-hydrationSkill
- tanstack-query-alternativeSkill
- tanstack-router-data-loadersSkill
- tanstack-start-server-routesSkill
- typed-api-hooks-formsSkill
The file
We split this skill into the rules it gives an agent
1 rule. About 0 are common, 0 are distinctive, and 1 is very specific to this project.
≈0
Common · estimate
Many files like this have a rule of this kind.
≈0
Distinctive · estimate
This author's own choices. Worth a look.
1
Very specific
Tied to their project's names, files or tools. Probably skip.
- Common
- Distinctive
- Very specific
- Very specific: line 178
Put cron.schedule calls in a versioned SQL migration so jobs are reproducible in every environment.
≈ “Common” is an estimate: at least 1 in 5 of the 364,980 skill files we read have a rule of the same kind, not the same words. “Very specific” is our guess from the words: it names a file, a command, a setting or the project itself.
Files
pg_cron deep dive: scheduled jobs inside Postgres
pg_cron runs SQL on a cron schedule from inside Postgres. It is enabled on Supabase and most managed Postgres. Use it for recurring database maintenance, aggregations, soft-delete sweeps, and triggering edge functions via pg_net.
Companion skills: background-jobs-and-cron (overview), pg-cron-with-pg-net-edge (calling HTTPS endpoints), pg-cron-recipes (cookbook), postgres-triggers-and-functions.
Enable
create extension if not exists pg_cron;
-- Supabase: also enable in Dashboard → Database → ExtensionsOn Supabase, jobs run inside the postgres database. You can only schedule from a role with cron.schedule privileges (usually postgres / service role via SQL editor).
Cron syntax (5 fields)
┌──────── minute (0-59)
│ ┌────── hour (0-23, UTC)
│ │ ┌──── day of month (1-31)
│ │ │ ┌── month (1-12)
│ │ │ │ ┌ day of week (0-6, Sun=0)
│ │ │ │ │
* * * * *Common patterns:
| Schedule | Meaning |
* * * * * | every minute |
*/5 * * * * | every 5 minutes |
0 * * * * | top of every hour |
0 9 * * * | 09:00 UTC daily |
0 9 * * 1 | 09:00 UTC Mondays |
15 3 1 * * | 03:15 UTC on the 1st of each month |
All times are UTC. Convert from your business timezone before writing the schedule.
Schedule a job
select cron.schedule(
job_name => 'cleanup-soft-deleted',
schedule => '0 3 * * *',
command => $$ delete from posts where deleted_at < now() - interval '30 days' $$
);Return value is the jobid. Re-running cron.schedule with the same job_name replaces the existing job — safe for idempotent migrations.
Run a function (preferred)
Inline SQL is hard to test. Wrap logic in a security definer function and schedule a one-liner:
create or replace function public.cleanup_soft_deleted()
returns void
language plpgsql
security definer
set search_path = public
as $$
begin
delete from posts where deleted_at < now() - interval '30 days';
delete from comments where deleted_at < now() - interval '30 days';
end;
$$;
revoke all on function public.cleanup_soft_deleted() from public, anon, authenticated;
select cron.schedule('cleanup-soft-deleted', '0 3 * * *',
$$ select public.cleanup_soft_deleted() $$);Now you can unit-test the function directly with select public.cleanup_soft_deleted();.
List and inspect jobs
-- definitions
select jobid, schedule, command, jobname, active from cron.job;
-- last 50 runs
select jobid, runid, job_pid, status, return_message, start_time, end_time
from cron.job_run_details
order by start_time desc
limit 50;
-- failed runs in the last day
select * from cron.job_run_details
where status = 'failed' and start_time > now() - interval '1 day'
order by start_time desc;cron.job_run_details grows forever — schedule its own cleanup (see recipes).
Update or remove
-- Re-schedule (replace existing by name)
select cron.schedule('cleanup-soft-deleted', '0 4 * * *',
$$ select public.cleanup_soft_deleted() $$);
-- Pause / resume
update cron.job set active = false where jobname = 'cleanup-soft-deleted';
update cron.job set active = true where jobname = 'cleanup-soft-deleted';
-- Delete
select cron.unschedule('cleanup-soft-deleted');Concurrency and overlapping runs
pg_cron does not prevent overlapping runs of the same job. If a job sometimes takes longer than its interval, add a lock:
create or replace function public.refresh_dashboard_stats()
returns void
language plpgsql
security definer
as $$
begin
-- advisory lock; skip this run if another is in progress
if not pg_try_advisory_lock(hashtext('refresh_dashboard_stats')) then
raise notice 'previous run still in progress, skipping';
return;
end if;
perform refresh_materialized_view_concurrently('public.dashboard_stats');
perform pg_advisory_unlock(hashtext('refresh_dashboard_stats'));
exception when others then
perform pg_advisory_unlock(hashtext('refresh_dashboard_stats'));
raise;
end;
$$;Idempotency
Cron jobs will run twice eventually (restarts, manual triggers, race conditions). Make every job idempotent:
delete ... where deleted_at < ...— safe, just deletes less the second time.update ... where status = 'pending'— safe, transitions only once.- Sending an email? Use a
sent_atcolumn andwhere sent_at is nullfilter.
update notifications
set sent_at = now()
where scheduled_for <= now() and sent_at is null
returning id;
-- then enqueue email send for returning idsJob naming convention
{verb}-{noun}-{cadence} or {noun}-{verb}:
cleanup-soft-deletedrefresh-dashboard-statsdigest-weeklyexpire-trials
Stable names = idempotent re-runs of your migration.
Migration discipline
Put cron.schedule calls in a versioned SQL migration so jobs are reproducible in every environment. Don't rely on the Supabase dashboard UI for the source of truth.
-- migrations/20260520_schedule_cleanup.sql
select cron.schedule('cleanup-soft-deleted', '0 3 * * *',
$$ select public.cleanup_soft_deleted() $$);Time zones
- pg_cron schedules are UTC.
- Inside SQL you can still use
at time zone 'America/Los_Angeles'for business logic. - For "every Monday 9am Pacific" use the UTC equivalent (
0 17 * * 1standard /0 16 * * 1daylight saving) — or use a daily 5‑min job that checks local time.
What pg_cron is NOT good for
- Sub-minute schedules (minimum granularity is 1 minute).
- Long-running tasks (> a few minutes) — extract to an edge function and trigger via
pg-cron-with-pg-net-edge. - Workloads that need retries, backoff, or DLQs — use a real queue (pgmq, Inngest, Trigger.dev).
Avoid
- Inline multi-statement SQL in
cron.schedule— wrap in a function. - Running heavy aggregations during peak traffic windows; schedule off-peak UTC.
- Querying
cron.job_run_detailswithout a time filter (table grows unbounded). - Forgetting to lock when a job can overlap itself.
- Storing the schedule only in the dashboard with no migration.
Checklist
pg_cronextension enabled.- Each job calls a function, not inline SQL.
- Function is
security definerwith locked-downsearch_pathand revoked public execute. - Job is idempotent (safe to run twice).
- Long/overlapping jobs use
pg_try_advisory_lock. - Schedules are checked into migrations.
cron.job_run_detailshas its own cleanup job (see recipes).
Does it work?
Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.
No reviews yet
People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.
- It worked
- Partly
- It didn't
Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.
Your trail
Files you open will collect here, so you can find them again.
Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.
Now do this for your own.
Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.

