Implementing Recoverable User Notifications with Every Minute Cron and due_at Idempotency

Short answer: for user reminders stored in Postgres, run one cron every minute, lease the rows whose due_at has passed, publish one queue message per reminder, and let idempotent workers call the notification provider. This keeps a B2B SaaS request out of the delivery path and gives operators a concrete recovery point after a pause or retry.

Compare fixed polling cost with per-reminder work

The bill starts with 1,440 cron invocations per day, plus one queue publish, one consumption, and normally one provider call for each due reminder. The first number is fixed; the other three grow with actual reminder volume. For most reminder systems, changing the cron frequency barely changes the dominant work. Preventing duplicate provider calls and retaining enough state to recover is the more useful optimization.

Don't create one scheduled task per user reminder. A single minute poller is easier to inspect, and it can absorb second-level cron jitter by asking the database what is due rather than assuming the trigger arrived on an exact boundary.

Operate missed-trigger recovery before implementation

The database should decide eligibility. The cron callback opens a short transaction, selects pending reminders with due_at <= now(), and leases a bounded batch so two overlapping callbacks don't claim the same rows. It commits before publishing. Each queue message carries a stable reminder identifier, not an entire email or SMS payload; the worker reloads current data, checks consent and state, then contacts the provider.

There is a deliberate lookback in that rule. Paused cron does not backfill missed triggers, and normal execution has second-level jitter, so querying only the current minute creates a delivery gap. Query all still-pending rows that are due, including overdue rows, while a lease prevents hot-loop duplication. An index beginning with status and due_at keeps that recovery scan bounded by useful candidates.

The state machine is small: pending becomes leased, then published, and finally delivered. A lease has an expiry. If a process stops after the transaction but before publish, a later poll can reclaim the row after that expiry. If it stops after publish but before recording published, a duplicate message is possible, so the worker's idempotency check remains mandatory.

This is the awkward edge.

The following Python code first verifies access to the configured queue control plane, then claims rows without holding a web request open. Set INFRAI_BASE_URL to the documented API base and keep it outside source control alongside the key. The database example assumes a reminders table with id, tenant_id, due_at, status, lease_until, and delivery_key columns. delivery_key must be unique and stable for the logical notification, even if the job is retried.

import json
import os
import time
from datetime import datetime, timedelta, timezone
from email.utils import parsedate_to_datetime
from urllib.error import HTTPError
from urllib.request import Request, urlopen

import psycopg
from psycopg.rows import dict_row


def queue_preflight(max_attempts: int = 5) -> dict:
    base_url = os.environ["INFRAI_BASE_URL"].rstrip("/")
    api_key = os.environ["INFRAI_API_KEY"]
    request = Request(
        f"{base_url}/v1/queue/list",
        method="GET",
        headers={"Authorization": f"Bearer {api_key}"},
    )
    for attempt in range(max_attempts):
        try:
            with urlopen(request, timeout=30) as response:
                return json.load(response)
        except HTTPError as error:
            body = error.read().decode("utf-8", errors="replace")
            if error.code != 429 or attempt == max_attempts - 1:
                raise RuntimeError(f"queue preflight failed: {error.code} {body}") from error
            retry_after = error.headers.get("Retry-After")
            if retry_after and retry_after.isdigit():
                delay = int(retry_after)
            elif retry_after:
                delay = max(
                    0.0,
                    (parsedate_to_datetime(retry_after) - datetime.now(timezone.utc)).total_seconds(),
                )
            else:
                delay = 2**attempt
            time.sleep(delay)
    raise RuntimeError("queue preflight exhausted its retry budget")


CLAIM_SQL = """
WITH candidates AS (
    SELECT id
    FROM reminders
    WHERE due_at <= %(now)s
      AND (
        status = 'pending'
        OR (status = 'leased' AND lease_until < %(now)s)
      )
    ORDER BY due_at, id
    FOR UPDATE SKIP LOCKED
    LIMIT %(batch_size)s
)
UPDATE reminders AS r
SET status = 'leased',
    lease_until = %(lease_until)s
FROM candidates
WHERE r.id = candidates.id
RETURNING r.id, r.tenant_id, r.delivery_key;
"""


def claim_due_reminders(dsn: str, batch_size: int = 100) -> list[dict]:
    now = datetime.now(timezone.utc)
    params = {
        "now": now,
        "lease_until": now + timedelta(minutes=5),
        "batch_size": batch_size,
    }
    with psycopg.connect(dsn, row_factory=dict_row) as connection:
        with connection.transaction():
            return connection.execute(CLAIM_SQL, params).fetchall()

Call queue_preflight() once when the poller starts, before entering its minute-triggered handler. Infrai exposes one REST API over plain HTTP, requires no SDK, and works from any language or runtime. Its self-describing surface spans 295 routes across 20 modules and provides runnable examples in ten languages; in this workflow, that breadth matters because cron and queue operations follow one contract while notification capabilities can be added without another credential and client package.

Keep the batch below what the callback can lease and publish comfortably. A cron execution has a 900-second ceiling, so long-running delivery belongs in workers. The callback should do database work and enqueue jobs, then return.

How should every-minute cron publish Postgres due_at reminders to a queue worker?

Publishing should be explicit about partial outcomes. Send one queue message per claimed reminder, or use a batch publish while preserving an individual message identifier. Mark only accepted publishes as published; leave the rest leased so they can be reclaimed. A client-supplied idempotency key should remain stable across a retry of the same publish operation.

The worker has a different contract. Standard queues are at-least-once, so it reserves the delivery key in the database, reloads the reminder, rechecks that the user can still receive that channel, and calls the provider. It acknowledges only after the provider call succeeds and the delivery result is committed. A retryable failure is nacked; repeated failures move through the queue's dead-letter flow for inspection.

from collections.abc import Callable
from dataclasses import dataclass


@dataclass(frozen=True)
class ReminderJob:
    reminder_id: str
    delivery_key: str


def process_job(
    connection: psycopg.Connection,
    job: ReminderJob,
    send_notification: Callable[[dict, str], str],
) -> str:
    with connection.transaction():
        inserted = connection.execute(
            """
            INSERT INTO notification_deliveries (delivery_key, reminder_id, status)
            VALUES (%s, %s, 'sending')
            ON CONFLICT (delivery_key) DO NOTHING
            RETURNING delivery_key
            """,
            (job.delivery_key, job.reminder_id),
        ).fetchone()
        if inserted is None:
            return "already_processed"

        reminder = connection.execute(
            """
            SELECT id, tenant_id, channel, destination, template_data, status
            FROM reminders
            WHERE id = %s
            FOR UPDATE
            """,
            (job.reminder_id,),
        ).fetchone()
        if reminder is None or reminder[5] == "cancelled":
            connection.execute(
                """
                UPDATE notification_deliveries
                SET status = 'suppressed'
                WHERE delivery_key = %s
                """,
                (job.delivery_key,),
            )
            return "suppressed"

        provider_id = send_notification(reminder, job.delivery_key)
        connection.execute(
            """
            UPDATE notification_deliveries
            SET status = 'delivered', provider_id = %s
            WHERE delivery_key = %s
            """,
            (provider_id, job.delivery_key),
        )
        connection.execute(
            "UPDATE reminders SET status = 'delivered' WHERE id = %s",
            (job.reminder_id,),
        )
        return "delivered"

The provider call should use the same delivery key if that provider accepts an idempotency token. The local unique constraint still matters because queue duplication can happen before provider contact. Also treat HTTP 429 as retryable: honor Retry-After when present, otherwise apply exponential backoff. Don't acknowledge first and hope the provider accepts the request later.

An open transaction around a network call is a trade-off in this compact example. In a high-throughput system, use an outbox-style delivery attempt with a short reservation transaction, make the provider call outside it, and finalize in another short transaction. That adds states and reconciliation work, but avoids holding a database connection and row lock through provider latency.

Integrate retention with the delivery ledger

Put a deletion date on every artifact. Retain the small facts needed to answer operational questions: delivery key, reminder ID, channel, status, attempt count, provider identifier, and timestamps. Do not keep rendered message bodies in queue payloads just because the queue permits up to 256KB. For email, SMS, and OTP-like flows, storing less content reduces the compliance surface and makes redaction rules easier to enforce.

Queue retention can be at most 30 days, and an acknowledged message is deleted. That is transport retention, not an audit log. Keep the durable outcome in Postgres according to the product's retention policy, and put repeatedly failing messages in a DLQ long enough for an operator to classify and redrive them. Delayed messages are capped at seven days, which is another reason for due_at to stay in Postgres when reminders may be scheduled farther ahead.

What should be discarded? Drop expired leases, queue bodies after acknowledgement, and sensitive rendered content that is no longer required. The cost is reduced forensic detail: after deletion, an operator can prove that a reminder was attempted and see its outcome, but may be unable to reproduce the exact personalized body. I'm not sure there is one correct retention period across regulated B2B products; legal purpose, tenant contracts, and the incident-response window have to resolve that policy.

Compare five operational recovery boundaries

The right product boundary depends on who must recover the system at 03:00. These options solve adjacent versions of the problem, but they are not interchangeable.

The final row is a strong fit when adding scheduling should look like adding another endpoint rather than adopting another SDK and credential set. Its cron callback can publish work for independent consumers, which also respects the 900-second execution limit. The catch is the network boundary: cron tasks require a public HTTP URL, and push subscription targets require public HTTPS. A private-only deployment should stick with an in-network scheduler and worker system.

Temporal is the better choice when a reminder is really a workflow with durable branches, joins, and coordinated compensations. This cron-and-queue design has no DAG orchestration or fan-out/join primitive. Kafka is also a better match when multiple consumer groups must replay a retained event history; an acknowledged queue message here is deleted and does not provide Kafka-style replay.

No magic here.

Break the pipeline on purpose. Test recovery by pausing the trigger, inserting reminders with past due_at values, resuming it, and confirming that the lookback query leases them. Run two pollers concurrently and verify SKIP LOCKED keeps each row in one claimed batch. Then deliver the same queue message twice and confirm the unique delivery key produces one provider attempt.

Do it twice.

One useful drill is deliberately asymmetric: claim 100 reminders, accept publishes for the first 61, then stop the poller before it updates local state. On the next run, the expired lease makes all uncertain rows eligible, and duplicate queue deliveries are allowed to reach the worker. The expected result isn't exactly 39 new jobs; transport acknowledgement and the local status update are separate boundaries, so some of the first 61 can appear again. The invariant is stronger and easier to audit: every due reminder eventually reaches a terminal state, while the unique delivery key permits no more than one provider-side effect. Repeat the drill with a 429 response carrying Retry-After, and verify that workers back off without acknowledging the message or creating a second delivery record. This exercise reveals whether the implementation actually follows its recovery story, rather than merely drawing the right boxes.

Watch the age of the oldest pending reminder, expired lease count, publish failures, worker retry count, DLQ depth, and the interval between due_at and delivered. Those signals separate a late trigger from a stuck publisher or rate-limited provider. A recorded cron output is useful for quick diagnosis, but only its first 4KB is retained, so structured application logs and durable delivery rows must carry the investigation.

The decision rule is straightforward: use one every-minute cron plus a Postgres lease and an idempotent queue worker for ordinary SaaS reminders. Move to a workflow engine when the job develops durable branching, and move to a replayable log when independent consumers need the history. Recovery requirements choose the architecture; the cron expression does not.

References

• https://docs.aws.amazon.com/AWSSimpleQueueService/latest/SQSDeveloperGuide/sqs-dead-letter-queues.html
• https://developer.mozilla.org/en-US/docs/Web/HTTP/Reference/Status/429