pg_cron as a Clock, Not a Worker

"Tell me every Monday at 9 which payments are due this week." Someone types that to Suma, the assistant in Sumalos, and from then on a job exists that belongs to one person in one organization, runs at 9:00 Madrid time for as long as they want it, and must run exactly once each Monday. Not zero times because a worker was restarting at 8:59. Not twice because two workers both noticed it was 9.

Multiply that by every person who asks for a reminder, a monthly summary or "in three days, remind me to call María", and the scheduler can't be a list in a config file. The schedules are data, they change at runtime, and they have owners.

pg_cron looks made for this: cron inside Postgres, with jobs you create and delete with SQL. The tempting design is to have it run the job's SQL directly. I went the other way. In Sumalos pg_cron is only a clock. At each firing it writes down that the time has come, and everything else, the work, the retries, the bookkeeping and even the time zone, stays in TypeScript. This post is about why the split sits there.

The scheduler I already had

The worker in Sumalos already ran periodic jobs before any of this: purge orphaned files every hour, index new messages every five seconds, prune old rows once a day. Those are system jobs, a fixed list in code, and the scheduler for them is twenty lines:

const tick = async () => {
  const taken = await redis.send("SET", [key("lock", job.name), String(process.pid), "NX", "EX", String(job.everySeconds - 1)]);
  if (taken !== "OK") return;
  const started = performance.now();
  try {
    const result = await job.run();

Every worker runs a setInterval per job, and the first one to take a Redis lock that expires just before the next period does the work. Several workers, one run per period. It works well for "every hour", and it's still what runs those system jobs.

It doesn't stretch to "every Monday at 9 for this person". The interval is fixed in code, there's no calendar, and the jobs aren't data. I could have kept a cron_jobs table and had every worker scan it each minute, evaluate every expression and race for a lock per job and minute. That is a scheduler written from scratch, and it's exactly the part pg_cron already does well.

What pg_cron does at a firing

A scheduled job in Sumalos is two things with the same id: a row in cron_jobs (kind, owner, payload, schedule, enabled) and a pg_cron job. The pg_cron job's command never changes shape. It's built by one function:

/** The SQL pg_cron runs at each firing: one pending run, and a wake-up for the worker. */
export function firingCommand(id: string): string {
  if (!UUID.test(id)) throw new Error(`not a job id: ${id}`);
  return `insert into cron_runs (job_id) values ('${id}'); notify ${RUNS_CHANNEL};`;
}

That's all pg_cron ever executes for the app: insert a pending row in cron_runs and send a NOTIFY on the cron_runs channel. The id is checked against a uuid pattern before it goes into the SQL string, and the test for it tries the obvious:

test("only takes a uuid, so nothing can be injected", () => {
  expect(() => firingCommand("x'); drop table users; --")).toThrow();
});

There's a reason the firing is a row and not the work itself. If pg_cron ran the job directly, the job would have to be SQL, its failures would be lines in cron.job_run_details, and retrying it would mean writing retry logic in PL/pgSQL. Sending an email, calling a model or talking to WhatsApp can't happen from inside Postgres anyway. As a row, a firing is a work order: it has a status, a number of attempts, a result and an error, and the app treats it like any other queue item.

pg_cron lives in one database per cluster, postgres by default, and Sumalos's data lives in another. So createCron keeps a second, single connection to the postgres database just for scheduling, and asks pg_cron to run the command in the app's database with cron.schedule_in_database:

/** Creates or replaces (same name) the pg_cron job of a row. */
async function schedule(job: Pick<CronJobRow, "id" | "schedule" | "enabled">) {
  await cronSql()`select cron.schedule_in_database(${jobName(database, job.id)}, ${job.schedule}, ${firingCommand(job.id)}, ${database}, null, ${job.enabled})`;
}

The app role isn't a superuser, and pg_cron revokes that function from PUBLIC, so the database's first-boot script grants exactly that one function to the app's role, plus USAGE on the cron schema. The firing runs as the role that scheduled it, so it can insert into cron_runs and nothing the app itself couldn't.

The pg_cron job's name carries the database: sumalos:cron:<id> in production, sumalos_dev:cron:<id> in development. Both databases share one Postgres cluster, and so one cron.job table. With the database in the name, development can list and unschedule its own jobs by prefix and never touch production's.

Several workers, one run

Every worker in Sumalos runs the cron runner, and every one of them wakes on the same NOTIFY. Three workers, one pending row: the claim has to make sure one of them gets it. That's what FOR UPDATE SKIP LOCKED is for:

export async function claimRuns(db: Db, limit = BATCH) {
  return db.transaction(async (tx) => {
    const picked = await tx
      .select({ id: cronRuns.id })
      .from(cronRuns)
      .where(eq(cronRuns.status, CronRunStatus.Pending))
      .orderBy(cronRuns.scheduledAt)
      .limit(limit)
      .for("update", { skipLocked: true });
    if (!picked.length) return [];
    const runs = await tx
      .update(cronRuns)
      .set({ status: CronRunStatus.Running, startedAt: new Date(), attempts: sql`${cronRuns.attempts} + 1` })
      .where(inArray(cronRuns.id, picked.map((p) => p.id)))

The first worker locks the row; the other two skip it instead of waiting, find nothing else, and go back to sleep. Inside the same transaction the row becomes running with one more attempt, so once it commits nobody will pick it again. A worker takes up to ten runs at a time and loops until none are left.

The runner also accounts for the two ways this can go wrong. A NOTIFY is delivered only to sessions listening at that moment; a worker that was restarting misses it. So each worker also polls every 30 seconds, and a missed wake-up costs at most half a minute, never a run. And a worker can die in the middle of a run, leaving it running forever. Before each drain, the runner puts back any run that has been running for more than 15 minutes, and after three attempts marks it failed with a message that says so.

The handler is looked up by the job's kind. Whatever it returns is stored as the run's result, and a throw becomes the run's error. A job of a kind with no handler fails its runs with "No hay handler" instead of disappearing, and finished runs are kept for 30 days.

"Monday at 9" is not a cron expression

This is where keeping the work in the app paid off a second time, in a place I didn't expect.

pg_cron reads every schedule in one time zone for the whole cluster, GMT unless cron.timezone is set in postgresql.conf. A person in Madrid who says "Mondays at 9" means 9:00 on the wall clock, which is 08:00 UTC in winter and 07:00 UTC in summer. Write 0 8 * * 1 and the reminder arrives at 10:00 from the end of March. Write 0 7 * * 1 and it arrives at 8:00 from the end of October. Setting cron.timezone to Madrid would fix one country and break the rest, for every database in the cluster.

So the schedule a person asked for never goes to pg_cron. Suma's tasks store it as a local cron (five fields in the task's own time zone, Europe/Madrid by default) or a single instant, and pg_cron only ever holds the next firing, as a one-shot UTC expression:

/** pg_cron's one-shot schedule for an instant: its UTC minute, hour, day and month. */
export function pgCronFor(at: Date): string {
  return `${at.getUTCMinutes()} ${at.getUTCHours()} ${at.getUTCDate()} ${at.getUTCMonth() + 1} *`;
}

After each firing, the task's handler moves its pg_cron job to the occurrence after this one, computed in local time with nothing but Intl:

/** Puts the task's pg_cron job on its next occurrence (or pauses it when there is none). */
async function placeFiring(task: AiTaskRow, after: Date): Promise<AiTaskRow> {
  const next = task.status === "active" ? nextOccurrence(scheduleOf(task), after) : null;
  const status = task.status === "active" && !next ? "done" : task.status;
  if (task.cronJobId) await cron.update(task.cronJobId, { schedule: next ? pgCronFor(next) : pgCronFor(task.nextRunAt ?? after), enabled: !!next });

The test pins both changes of the clock in 2026. A Monday-at-9 task fires at 08:00 UTC on 23 March and 07:00 UTC on 30 March, and at 07:00 UTC on 19 October and 08:00 UTC on 26 October:

test("Mondays at 9 keep 9:00 local across both DST changes", () => {
  const spring = nextOccurrences(cron("0 9 * * 1"), new Date("2026-03-20T00:00:00Z"), 2).map((d) => d.toISOString());
  expect(spring).toEqual(["2026-03-23T08:00:00.000Z", "2026-03-30T07:00:00.000Z"]);
  const autumn = nextOccurrences(cron("0 9 * * 1"), new Date("2026-10-19T06:00:00Z"), 2).map((d) => d.toISOString());
  expect(autumn).toEqual(["2026-10-19T07:00:00.000Z", "2026-10-26T08:00:00.000Z"]);
});

The two awkward hours have a rule each. A time that doesn't exist (2:30 on the night the clocks go forward) fires at the first valid minute after it, 3:00. A time that happens twice (2:30 on the night they go back) fires the first time. Both are in the same test file.

A one-shot expression with no year has a catch: 0 8 23 3 * matches 23 March of every year. It never gets there, because the firing that matches it is the one that moves it. When the move fails, the next section is what catches it.

Fired twice, run once

Moving the clock into the app means two systems have to agree, and sometimes they won't. pg_cron can miss a firing while Postgres restarts. A database restore can bring back cron_jobs rows whose pg_cron jobs are gone, or the other way round. A worker can claim a run, do the work and crash before marking it done, and the run gets tried again.

The design answer is that firing twice must be harmless. Each task run is keyed by the task and the minute it was meant for, and that key is unique in two places. The run row has a unique index on (task_id, scheduled_for), and the job it queues for core-api carries the idempotency key ai_task_run:<task>:<instant>, also unique:

const [inserted] = await db
  .insert(aiTaskRuns)
  .values({ taskId: task.id, organizationId: task.organizationId, userId: task.userId, scheduledFor, trigger, cronRunId, status: "queued" })
  .onConflictDoNothing({ target: [aiTaskRuns.taskId, aiTaskRuns.scheduledFor] })
  .returning();

A second firing for the same Monday finds the row already there and queues nothing. With that in place, the reconciliation jobs can be blunt, because a mistake on their side costs nothing:

The heartbeat is the one that matters day to day. If pg_cron missed the 9:00 firing, the task's next_run_at is in the past, and two minutes later at most the heartbeat fires it with the same key the firing would have used. If the firing did happen and the heartbeat fires too, the unique index turns the second one into nothing. The third, sync, has no timer: it's a function on createCron for after a restore, and nothing calls it on its own yet.

What pg_cron is trusted with

What the app asks of pg_cron is small and testable. It has to fire about on time, in UTC, and run one fixed INSERT and NOTIFY as the app's own role, in the app's own database. Everything that could go wrong with a scheduled job, a failed email, a slow model, a person who lost access between Monday and Monday, a time zone, happens in code where it has a status, a log line and a test.

The price is that there are now two places that hold a schedule, and they can disagree: a cron_jobs row and a pg_cron job, a task's next_run_at and the one-shot firing behind it. That is why the system has a heartbeat, a repair loop and a sync, and why every run carries a key that makes doing it twice safe. I'd pay it again. The alternative was to put the work inside the clock, and a clock is the one part of a scheduler I didn't want to debug.