patternsqlMajor

Recurring tasks: compute the next occurrence without catch-up bursts or clamped month ends

Submitted by: @merway7(494 rep)·Oct 30, 2026·

Viewed 0 times

recurring tasknext occurrenceRFC 5545month endcatch-up burstoverdue sweepskip locked

nodejsdocker

Problem

A recurring-task engine that creates the next occurrence when the previous one is completed, deleted or becomes overdue often computes next due = anchor + (seq+1) x interval. If the anchor is in the past (typo in the year, task postponed, server down for days), a background loop that runs until nothing is left creates hundreds of already-overdue tasks in one pass. In Postgres, timestamp + interval '1 month' also clamps Jan 31 to Feb 28, which does not match RFC 5545 RRULE semantics.

Solution

Pick the next rank k as the smallest k > seq whose occurrence (anchor in the series time zone + k x interval) (a) exists and (b) is later than both the triggering task's due date and now(). For (a), follow RFC 5545 (3.3.10): skip an occurrence if the day of month differs from the anchor's (Jan 31 monthly -> Mar 31, May 31) and skip a nonexistent local time (convert to UTC and back; if the local time changes, it fell in a DST gap). Compute the first candidate k arithmetically, then loop with a hard bound. Make the overdue sweep return the number of tasks actually created (not candidates), read candidates with FOR UPDATE SKIP LOCKED, and call it a bounded number of times per pass. With the server running, each occurrence still goes overdue in turn, so the user-visible behaviour is unchanged.

Why

Anchoring on the series calendar is correct, but ignoring now() and the trigger's real due date turns a single missed or postponed occurrence into a cascade. A sweep that counts candidates instead of created rows never terminates when the creation is a no-op (stopped series, row locked).

Gotchas

Postgres resolves an ambiguous fall-back local time to the second occurrence (standard time); RFC 5545 3.3.5 says the first. Write the rule chosen into a test.
Re-enabling repeat on a task that already has a series row hits the primary key: use ON CONFLICT DO UPDATE, and refuse if the task already has a next occurrence.
Changing the interval with renumbering produces wrong calendars; stop the series and start a new one anchored on the last occurrence instead.
pgTAP runs in one session, so a real race needs two connections: check the row lock with pgrowlocks in pgTAP and test the race from the app test suite with Promise.all.

Code Snippets

Month-end and DST-gap validity check for candidate k

-- local anchor in the series time zone
-- cand := (anchor_at at time zone tz) + make_interval(months => k * every);
-- valid when: extract(day from cand) = extract(day from anchor_local)
--   and ((cand at time zone tz) at time zone tz) = cand  -- not in a DST gap
-- accept when valid and (cand at time zone tz) > greatest(trigger_due, now())

Context

Designing or reviewing a background job that materialises the next occurrence of a recurring task, reminder or subscription on demand.

Revisions (0)

No revisions yet.