Short answer: keep every fintech user reminder in a durable due-date table, let cron claim only reminders that are close enough to send, and treat the queue as short-lived transport rather than a seven-day calendar. The trade-off is an extra state machine in storage, but it gives operators one recoverable record when a delayed queue message never arrives and prevents a webhook retry from silently becoming a second delivery.
This distinction matters. A queue can carry work after it becomes eligible; it should not be the only record that the work must exist next month. If a queue's configured maximum delay is seven days, scheduling a notification for day eight isn't a retry problem. It is a representation problem.
The hard part is duplication.
How can operators recover user reminders when a delayed queue message never arrives?
Start by preserving identity, not by publishing a replacement message. Find the original logical reminder, freeze any ad hoc replay, and decide which evidence exists: the business due time, a queue acceptance record, a webhook attempt, or a terminal delivery result. If the only evidence is “cron ran,” the system cannot distinguish late work from lost work, and another enqueue may produce a duplicate rather than a recovery.
Use two clocks and one stable identity during that investigation. due_at is the business clock: the instant the user reminder becomes eligible. next_attempt_at is the recovery clock: the next time an unsuccessful outbound webhook may be attempted. A generated delivery_id and an idempotency key remain unchanged across queue redelivery, process restart, and manual replay.
Cron should run more frequently than the acceptable scheduling lag, select rows whose due time has passed, and claim them in a short database transaction. It must not hold a database lock while performing network I/O. After the claim commits, the dispatcher can enqueue each stable delivery ID. If that process stops between commit and enqueue, the lease expires and another dispatcher can claim the row; if it stops after enqueue but before recording progress, the same ID may be enqueued again. The consumer therefore has to recognize that ID.
That is deliberate. Trying to hide the crash boundary usually makes recovery less legible — a database commit and a queue publish are separate effects unless the system has explicitly introduced a transactional bridge between them. The storage record is the authority, while queue messages are replaceable invitations to advance it. During an incident, an operator reclaims an expired lease or republishes the existing delivery ID; creating a new logical reminder is the last resort because it discards the evidence needed to suppress duplicates.
The minimum state model is small:
| State | Meaning | Operator action |
|---|---|---|
scheduled |
Durable and not yet claimed for dispatch | Inspect due_at; no queue archaeology required |
dispatching |
Leased to a cron worker | Wait for lease_until, then reclaim if necessary |
queued |
Accepted by the short-horizon transport | Reconcile if it remains here beyond the local alert threshold |
delivering |
A webhook attempt owns a lease | Reclaim after lease expiry |
delivered |
The terminal success marker is stored | Never create a new delivery ID during replay |
dead |
Retry policy is exhausted | Review and explicitly requeue or cancel |
Don't overload scheduled to mean “probably somewhere in the broker.” A state must correspond to evidence an operator can inspect.
Build a queryable recovery ledger
The repair path dictates the storage model. An operator needs to ask which reminders are overdue, which worker owns a lease, which handoff was recorded, and which delivery is terminal without assembling the answer from expiring queue messages. The following schema is intentionally plain. It uses PostgreSQL as the coordination point and keeps the payload beside the scheduling metadata so that reconstructing a missing queue message doesn't depend on an application log. The uniqueness constraint makes the idempotency key part of the data model rather than a best-effort header added at the edge.
SCHEMA = """
CREATE TABLE reminder_delivery (
delivery_id uuid PRIMARY KEY,
user_id uuid NOT NULL,
due_at timestamptz NOT NULL,
next_attempt_at timestamptz NOT NULL,
destination text NOT NULL,
payload jsonb NOT NULL,
idempotency_key text NOT NULL UNIQUE,
state text NOT NULL CHECK (
state IN ('scheduled', 'dispatching', 'queued',
'delivering', 'delivered', 'dead')
),
attempt_count integer NOT NULL DEFAULT 0,
lease_owner text,
lease_until timestamptz,
last_error text,
created_at timestamptz NOT NULL DEFAULT now(),
delivered_at timestamptz
);
CREATE INDEX reminder_delivery_due_idx
ON reminder_delivery (next_attempt_at, delivery_id)
WHERE state IN ('scheduled', 'dispatching');
"""
def install_schema(connection):
with connection.transaction():
connection.execute(SCHEMA)
The index is aligned with the dispatcher's question, not with a dashboard's preferred sort order. Rows already marked delivered do not belong in the due-work index. For a large history, retention and archival need an explicit policy, because an idempotency record deleted too early can no longer protect a late replay. The correct retention period depends on how long messages and operator exports can reappear; there isn't a universal number in this design.
Only after overdue work is visible should cron claim it. Two cron processes can wake at the same instant, so “select, then update” as separate transactions is unsafe. PostgreSQL documents SKIP LOCKED as producing an inconsistent view that can be useful for multiple consumers accessing a queue-like table; that is exactly the narrow job here, provided the transaction is short and the durable rows remain available for later reconciliation.
from datetime import timedelta
CLAIM_DUE = """
WITH candidates AS (
SELECT delivery_id
FROM reminder_delivery
WHERE next_attempt_at <= now()
AND (
state = 'scheduled'
OR (state = 'dispatching' AND lease_until <= now())
)
ORDER BY next_attempt_at, delivery_id
FOR UPDATE SKIP LOCKED
LIMIT %(batch_size)s
)
UPDATE reminder_delivery AS reminder
SET state = 'dispatching',
lease_owner = %(worker_id)s,
lease_until = now() + %(lease_for)s
FROM candidates
WHERE reminder.delivery_id = candidates.delivery_id
RETURNING reminder.delivery_id, reminder.idempotency_key;
"""
def claim_due(connection, worker_id, batch_size=100):
parameters = {
"worker_id": worker_id,
"batch_size": batch_size,
"lease_for": timedelta(minutes=2),
}
with connection.transaction():
return connection.execute(CLAIM_DUE, parameters).fetchall()
The batch size of 100 and the two-minute lease are example operating values, not performance claims. Measure claim duration and queue publish latency, then set the lease comfortably above their normal range while keeping crash recovery tolerable. Your mileage may vary.
Implement duplicate containment before webhook replay
Enqueue only the delivery_id and idempotency key; the consumer should reload current state from storage before attempting the webhook. That prevents an old queue copy from overwriting a newer cancellation or terminal result. It also limits the amount of sensitive fintech data duplicated across broker storage, logs, and dead-letter paths.
Before sending, the consumer obtains its own expiring lease. If the row is already delivered, it acknowledges the queue message without another network call. If the row is cancelled under a wider application state model, it does the same. Otherwise it sends the webhook with the stable idempotency key, then writes the result. A receiving system that honors the key can suppress repeated effects; the sender still records every attempt because “the queue acknowledged it” and “the partner processed it” are different claims.
from datetime import datetime, timedelta, timezone
from urllib import request
import json
def send_webhook(destination, payload, idempotency_key, timeout_seconds=10):
body = json.dumps(payload).encode("utf-8")
outbound = request.Request(
destination,
data=body,
method="POST",
headers={
"Content-Type": "application/json",
"Idempotency-Key": idempotency_key,
},
)
with request.urlopen(outbound, timeout=timeout_seconds) as response:
return response.status
def retry_time(attempt_count, now=None):
now = now or datetime.now(timezone.utc)
delay_seconds = min(30 * (2 ** attempt_count), 3600)
return now + timedelta(seconds=delay_seconds)
Here, 10 seconds, 30 seconds, and one hour are example policy inputs. They must be tuned against the destination's behavior and the product's reminder deadline. A retry that occurs after the reminder has lost business value may be technically successful and operationally wrong, so store an expiry or cancellation rule if the product has one.
Now name the ambiguous outcome: the remote endpoint may process a request even when the sender does not retain a response. Retrying with a new key would turn uncertainty into a likely duplicate. Retrying with the same key preserves the receiver's chance to identify one logical delivery. If the receiver offers no idempotency contract, the sender cannot promise exactly one external effect; the honest options are accepting at-least-once delivery, adding a status lookup contract, or changing the integration boundary.
RabbitMQ dead-letter exchanges can route messages that are dead-lettered, but a dead-letter path is not the business schedule and should not become an invisible long-term timer. Its useful role here is quarantine and diagnosis after the application's retry policy says normal processing should stop. Keep the authoritative dead state and reason in the database, then correlate any dead-lettered message by delivery_id.
Rehearse every crash boundary
Migration begins with recovery drills, not a traffic percentage. Pause cron before claim, stop it after claim, interrupt it after queue publication, deliver the same queue message twice, and replay a message after the database row is terminal. Each experiment should converge on an inspectable state without allocating a new delivery ID. Also test clock skew at the application edge: the database should decide whether a row is due, while application instances merely request a batch.
No shortcuts.
Assign operational ownership
Track counts and ages by state. The oldest eligible scheduled row exposes scheduler lag; expired dispatching and delivering leases expose abandoned ownership; queued age exposes a broken handoff; attempt count and dead age expose endpoint trouble and unresolved operator work. Alerts should describe those state transitions. A generic “cron failed” alert doesn't tell an operator which reminders remain recoverable.
For each alert, name the team allowed to reclaim a lease, the evidence required before replay, and the person or process that can move dead work back into the active set. Retention belongs in the same ownership decision. Deleting terminal rows may control storage growth, but it also removes the idempotency evidence that protects against an old message or operator export returning later.
Migrate schedules without resetting delivery identity
| Approach | Recovery strength | Operational cost | Not suitable when |
|---|---|---|---|
| Long broker delay only | Few application states | Low until the configured delay ceiling or message inspection becomes a constraint | The due date exceeds the broker limit, or storage must answer what is pending |
| Durable due-date table plus short queue | Clear source of truth, leases, and replay | Requires schema ownership, cleanup, reconciliation, and idempotent consumers | The team cannot operate a database-backed state machine |
| Database workers without a queue | Direct claim and delivery path | Couples delivery throughput to database polling and worker behavior | The queue provides required isolation or downstream buffering |
The catch is real: a due-date table moves complexity into application-owned state. Stick with a broker's native delay when every reminder fits comfortably inside its documented and configured window, message inspection is adequate, and the team does not need database-level cancellation or audit queries. Use direct database workers when traffic is modest and another transport adds no useful isolation. The table-plus-queue pattern earns its keep when reminders outlive the queue delay, recovery must survive deployment boundaries, and operators need a precise answer to “what happens next?”
Roll out compactly. First dual-write new reminder schedules to the durable table while the existing path remains authoritative, and compare due counts without sending from the new dispatcher. Next enable dispatch for a narrow cohort, verify lease reclamation and duplicate suppression, then migrate the remaining future reminders with their original logical IDs. Finally disable long-delay publication and retain reconciliation until the longest old queue delay has elapsed. Do not migrate by generating fresh idempotency keys; that erases the relationship between an old scheduled notification and its replacement.
Top comments (0)