Node.js Cron + Postgres: Idempotent Reminder Notifications with due_at
The hard part of scheduling user reminder notifications is recovery, not waking up once a minute. For a media app that fans out a shipment update to many subscribers, the practical choice is a small cron poller that claims due rows in Postgres, hands short jobs to a queue worker, and makes delivery idempotent. The poller should be disposable; the database row and the delivery key should carry the state.
Keep it boring.
Short answer: store the schedule in Postgres with a due_at timestamp, let a Node.js cron process claim a bounded batch every minute, and let queue workers retry delivery with an idempotency key. A queue by itself does not prevent duplicate notifications, and a cron process by itself does not provide useful recovery.
This is an experiment note, not a vendor bake-off. The simple approach is “run the notification code from cron.” It is easy to ship and difficult to recover: a process restart can interrupt a batch, a slow provider can occupy the next tick, and a timeout leaves you unsure whether the provider accepted the message. The chosen approach separates discovery from delivery. Before copying it, measure claim latency, queue age, retry count, provider response classes, and the number of duplicate delivery attempts.
What should a Node.js cron worker do with Postgres due_at reminders?
The cron job has one narrow responsibility: find reminders whose due_at is in the past, claim them briefly, and enqueue work. It should not send email, push, or SMS itself. That separation gives the worker a bounded transaction and gives delivery its own timeout and retry policy.
For a shipment update, one logical notification might target hundreds of subscribers. Keep the logical event separate from its recipients. A shipment_updates record can identify the event, while a reminders or notification_deliveries record contains the recipient, channel, due_at, status, and a stable deduplication key such as shipment-update:{eventId}:{subscriberId}. The exact table names are less important than making one delivery attempt addressable.
The claim must be atomic. Multiple cron processes may overlap during deployment, and two application instances may run the same schedule. A row lock plus a short lease lets one process claim a row without making the whole scan a global lock. The lease also makes a crashed claim recoverable after a defined interval.
Don't treat the lease as proof that delivery happened. It only says that one poller temporarily owns the next action.
type Reminder = {
id: string;
recipientId: string;
dueAt: Date;
idempotencyKey: string;
};
async function claimDueReminders(limit: number, leaseUntil: Date): Promise<Reminder[]> {
return db.transaction(async (tx) => {
const rows = await tx.query<Reminder>(
`
UPDATE reminders
SET status = 'queued', lease_until = $1
WHERE id IN (
SELECT id
FROM reminders
WHERE due_at <= now()
AND (status = 'pending' OR lease_until < now())
ORDER BY due_at, id
FOR UPDATE SKIP LOCKED
LIMIT $2
)
RETURNING id, recipient_id AS "recipientId", due_at AS "dueAt",
idempotency_key AS "idempotencyKey"
`,
[leaseUntil, limit],
);
return rows;
});
}
async function poll(): Promise<void> {
const leaseUntil = new Date(Date.now() + 2 * 60_000);
const reminders = await claimDueReminders(100, leaseUntil);
await Promise.all(reminders.map((reminder) => queue.publish({
type: "send-reminder",
reminderId: reminder.id,
idempotencyKey: reminder.idempotencyKey,
})));
}
The 100 is a starting bound, not a universal tuning value. It should fit inside the poll interval and the database connection budget. If the queue publish step fails after the transaction commits, the lease expires and the reminder becomes claimable again. That is why delivery must tolerate a second attempt.
Consider the shipment fan-out at the boundary between these components. The poller claims the subscriber rows, the queue stores one job per recipient, and the worker checks current subscription state immediately before sending. If subscriber 417 has opted out while subscriber 418 has not, a replay can suppress only 417; it does not need to discard the whole shipment event. That small choice keeps recovery local, avoids rebuilding a large fan-out, and gives operations a useful unit to inspect. It also means the idempotency key must include the recipient, not merely the shipment ID. A shipment-level key would incorrectly make the first successful recipient suppress every other recipient.
How do queue workers make fan-out recoverable?
A worker consumes one recipient-level job, sends it through the selected channel, and records the result. It should classify failures before deciding what “retry” means. A temporary provider timeout, a rate limit, a malformed address, and a revoked subscription do not have the same recovery path.
For HTTP delivery, 429 Too Many Requests means the recipient service is asking the client to slow down. Honor Retry-After when it is present, then apply a bounded backoff with jitter. Do not turn every non-2xx response into an immediate retry; that creates a second outage made by your own worker.
The idempotency boundary belongs at the delivery operation. Before sending, the worker creates or reads a record keyed by the stable idempotency key. After a successful send, it marks that record complete. If the process dies after the provider accepts the request but before the database update, the next attempt presents the same key. The provider or your own delivery gateway must define what that key means; a local database check alone cannot prove that an external side effect did not happen.
async function sendReminder(job: {
reminderId: string;
idempotencyKey: string;
}): Promise<void> {
const existing = await db.oneOrNone(
"SELECT status FROM notification_deliveries WHERE idempotency_key = $1",
[job.idempotencyKey],
);
if (existing?.status === "sent") return;
const response = await notificationGateway.send({
reminderId: job.reminderId,
idempotencyKey: job.idempotencyKey,
});
if (response.status === 429) {
throw new RetryableDeliveryError("rate limited", response.retryAfterMs);
}
if (!response.ok) {
throw new PermanentDeliveryError(`delivery status ${response.status}`);
}
await db.query(
`
INSERT INTO notification_deliveries (idempotency_key, status, sent_at)
VALUES ($1, 'sent', now())
ON CONFLICT (idempotency_key)
DO UPDATE SET status = 'sent', sent_at = now()
`,
[job.idempotencyKey],
);
}
There is an uncomfortable edge here. If the gateway has no idempotency contract, “at least once” processing can still produce a duplicate message. The honest decision is to expose that risk in the product behavior: use a stable event identifier in the notification, deduplicate at the recipient when possible, and track attempted versus confirmed delivery separately.
What should retry, dead-letter, and recovery state look like?
Retries need a stopping rule. Store the attempt count, last error class, next attempt time, and lease information with the delivery. A worker can retry temporary failures with exponential backoff, but a permanent failure should move to a terminal state without consuming the queue forever.
A dead-letter queue is the place for messages that cannot be processed successfully after the configured attempts. AWS describes dead-letter queues as a way to isolate messages that failed processing so they can be inspected, re-driven, or discarded according to policy. The implementation can be different, but the operational question is the same: who reviews the dead letters, how are they replayed, and how is replay prevented from ignoring a user unsubscribe?
For a shipment update, replay should re-check current subscription state and the shipment event version. A subscriber who opted out after the original schedule should not receive a notification merely because an old job was rescued. Recovery is a business rule as much as a queue rule.
Do not hide recovery in an unbounded in-memory array. A restart should leave enough durable state to answer three questions: what was due, what was claimed, and what was confirmed. Those answers make an incident manageable even when delivery timing is imperfect.
Where does this design stop being the best fit?
This design is a good fit for minute-level reminders and workloads where Postgres is already the source of truth. It is not suitable when notifications must fire with sub-second precision, when a single schedule can create millions of recipient jobs at once without rate controls, or when the database cannot sustain the claim queries. In those cases, use a scheduler built for the required precision or partition the workload before adding more workers.
It is also the wrong choice if the product needs a full workflow engine with long pauses, human approvals, or visual retry policy. A queue worker can implement those states, but the operational surface grows quickly. Stick with a simpler database-backed poller when the requirement is just “send this reminder after due_at,” and choose a workflow system when the state machine itself is the product.
Cost matters, but it is a secondary constraint. The useful optimization is bounded work: index the due-time query, claim only what the worker pool can process, and avoid creating a job for an unsubscribed recipient. A cheaper queue does not compensate for duplicate sends or an unreviewed dead-letter backlog.
What should be measured before shipping it?
Start with a failure-oriented test matrix. Advance a clock past due_at; run two pollers; kill a worker after the gateway call; return 429 with and without Retry-After; reject a recipient permanently; expire a lease; and replay a dead-letter job after an unsubscribe. The test is successful when the state transitions are explainable, not merely when the happy path sends a message.
Instrument these dimensions separately:
- schedule lag:
claimed_at - due_at; - queue age and worker execution time;
- attempts per idempotency key;
- confirmed sends, permanent failures, and dead letters;
- 429 counts and the delay before the next attempt;
- duplicate attempts versus duplicate notifications.
A one-minute cron interval is not a delivery guarantee. It is a discovery cadence. The user-facing promise should match the measured lag and the provider's behavior, with a clear policy for missed reminders.
The smallest design that survives a restart is usually the right starting point: durable due_at state, bounded atomic claims, queue-based delivery, explicit retry classes, and an idempotency key that follows the notification all the way to its side effect. Ship that path, then tune the boundaries using the measurements above.
Top comments (0)