Nightly payment reconciliation is a scheduling problem with a recovery requirement. A reminder that misses its first delivery window must still be explainable, deduplicated, and runnable after a worker has been offline. Short answer: keep the durable schedule in your database, use a queue for near-term work, and let a cron-driven reconciler reclaim anything beyond the queue's delay limit.
That split is less glamorous than putting a seven-day delay on one queue message. It is much easier to operate. The payment provider remains the source of truth; the notification service is a projection that can be rebuilt.
A reversible rollout for nightly reconciliation
Ship the intent table and a dry-run reconciler first. Compare its proposed notifications with the current system for several nightly runs. Then enable enqueueing for one account cohort, keeping the old sender as a read-only observer. Record the idempotency key in both paths so a rollback does not double-send.
Finally, test the uncomfortable cases: the worker dies after claiming, the provider settles a payment during a retry, the webhook arrives twice, and the clock jumps across daylight-saving changes. Inject a seven-day-old intent and verify that cron finds it, the worker handles it once, and the audit record explains the result.
Nightly reconciliation starts with an evidence clock
Queue systems optimize for work that is ready soon. They also have visibility and retry semantics that are easy to confuse with a calendar. A delayed message can become visible later, but the provider's documented maximums and retention rules still apply. AWS SQS, for example, documents visibility timeout as a lease on an in-flight message, not a durable appointment. Treating that lease as a seven-day schedule makes recovery depend on one opaque timer.
For a B2B SaaS reconciliation run, I model an intent as a row with account_id, payment_id, notify_at, channel, and an idempotency key. The row is durable before any queue publish. A dispatcher selects due rows in small batches, writes an attempt record, and publishes work with a short delay. A cron job scans for due rows whose attempt is absent or stale. The two paths converge on the same worker, so recovery is a normal state transition rather than a special script.
The important boundary is seven days. If a user asks for a reminder farther out than the queue supports, store the timestamp and stop pretending the queue owns the calendar. Your mileage may vary because queue limits differ, but the design does not need to.
No magic timer.
How should per-user scheduled notifications survive queue delay limits and cron recovery?
Start with a state machine. scheduled means the intent exists; enqueued means a worker lease was created; sent means the provider accepted the request; expired means policy says it is too late. A failed attempt returns to scheduled with next_attempt_at, while a duplicate delivery is rejected by a unique constraint on (account_id, idempotency_key).
Consider one account whose payment provider settles at 02:00 UTC and whose reminder is due seven days later. The nightly job first advances its provider cursor and records the settlement. The schedule row is then visible to the dispatcher only when notify_at is due. If the queue is unavailable at that minute, the row remains due; the next cron scan claims it. If the worker dies after the claim, a lease timeout makes the row eligible again. If the webhook arrives twice while the worker is retrying, the persisted event ID and idempotency key collapse both races into one outcome. Each recovery path leaves an audit record that an operator can inspect without reconstructing queue internals from logs.
The nightly reconciliation job supplies the evidence for those transitions. It fetches the provider's settled payments, compares them with the local ledger, and emits a notification intent only for a changed balance or status. That ordering matters: scheduling from a stale local flag can send a “payment missing” reminder after the provider has already settled it.
Here is the core loop in Python. It deliberately has no vendor SDK; the queue and webhook adapters are ordinary interfaces that can be tested with fakes.
from datetime import datetime, timezone
def dispatch_due(store, queue, now: datetime, batch_size: int = 100) -> int:
due = store.claim_due(now=now, limit=batch_size)
published = 0
for item in due:
key = f"{item.account_id}:{item.payment_id}:{item.notify_at.isoformat()}"
if store.already_attempted(key):
continue
store.record_attempt(key=key, state="enqueued", at=now)
queue.publish({"intent_id": item.intent_id, "idempotency_key": key})
published += 1
return published
def reconcile_and_schedule(provider, ledger, store, now: datetime) -> None:
for payment in provider.settled_since(ledger.last_cursor()):
ledger.upsert(payment)
if payment.needs_reminder:
store.put_intent(
account_id=payment.account_id,
payment_id=payment.id,
notify_at=now,
channel="email",
idempotency_key=f"settled:{payment.id}:{payment.status}",
)
ledger.advance_cursor(now)
if __name__ == "__main__":
dispatch_due(store, queue, datetime.now(timezone.utc))
The production version needs a transaction around the claim and attempt insert. Without it, two cron runners can observe the same row. A queue's “exactly once” marketing language cannot repair a race in your database.
Measure transitions, not just HTTP success. I want a count of intents by state, age of the oldest scheduled row, time from notify_at to enqueue, provider acceptance rate, and duplicate suppression count. Alert on a growing age percentile and on a reconciliation cursor that has stopped advancing. A 200 response from a webhook endpoint proves almost nothing if the handler acknowledged before persisting the event.
Public HTTPS webhooks need their own small protocol. Require TLS, verify the provider signature against the raw request bytes, reject timestamps outside a narrow replay window, persist the event ID, and return a 2xx only after the event is durable. If the provider retries, the event ID turns a repeated callback into a no-op. Do not put payment amounts in logs; use the account and event identifiers needed for an audit trail.
Email and SMS add deliverability constraints that scheduling alone cannot solve. Suppression lists, opt-outs, regional quiet hours, and provider rate limits belong in the worker policy. A reminder can be technically due and still correctly move to expired because the account disabled that channel. That is a business decision, not queue failure.
The architecture boundary between a queue, a scheduler, and cron
The practical comparison is about ownership of time and evidence:
| Component | Good at | Boundary to document |
|---|---|---|
| Queue (such as SQS) | Fast fan-out, leases, retry pressure | Delay and retention limits; visibility is not a calendar |
| Managed scheduler (such as Inngest) | Durable event-to-function timing | Inspect its replay, concurrency, and history model before making it the ledger |
| Task service (such as Google Cloud Tasks) | HTTP delivery with per-task retry policy | Public endpoint auth and task lifetime remain your responsibility |
| Cron plus database | Rebuilding due work and auditing state | Polling cadence and claim contention need explicit limits |
There is no universal winner. A queue-only design is unsuitable when reminders can be scheduled beyond its documented delay or when an audit must show why a reminder was skipped. A managed scheduler may fit a small team that values hosted history, while a database-plus-cron design is a better match when reconciliation and notification state must be queried together. Stick with the simplest boundary your on-call team can restore from a database backup.
That is the design I trust for reminders around money: a queue moves ready work, cron repairs gaps, and the database tells the truth.
Top comments (0)