Daily SaaS Reservations: Cron and Message Queue Guarantees in Node.js

Use a cron trigger plus a durable message queue when a SaaS must expire stale reservations and send a daily report email without silently losing work. The cron job answers when to look; the queue and an idempotent database transition answer whether each item finishes. A scheduler alone is the simpler architecture only when an operator can safely rerun the entire report after a crash and duplicate sends are acceptable.

That recommendation came from treating delivery guarantees as the evaluation constraint, not from comparing feature lists. The tempting version is one scheduled Node.js process that queries expired holds, changes their status, builds the report, and sends email. It's wonderfully small in a notebook. In production, however, every side effect adds another crash boundary, and the process has no durable account of which reservation was handled before it stopped.

The useful experiment is a failure-injection test: terminate the process after selection, after the status update, after enqueueing, and after the email provider accepts a send. Then restart it. If the expected result is neither a missing expiration nor an unexplained duplicate report, a single cron callback won't carry enough state by itself.

How can cron and a message queue prevent a daily report email failure?

Use cron alone when the task is a replaceable batch calculation. A report that can be regenerated from authoritative rows, overwrites yesterday's artifact, and has no external side effect may fit that model. The scheduled process can recompute the whole day after an interruption.

Use a queue when each unit needs an explicit delivery lifecycle: ready, claimed, acknowledged, or eligible for redelivery. For reservation expiration, one unit should be one reservation ID, not one enormous daily batch. The worker can then retry a single failed unit without replaying unrelated work.

Use both when the business requirement combines a wall-clock boundary with recoverable execution. A cron expression is good at declaring the wall-clock boundary. The Linux crontab format has five time-and-date fields, and a command runs when the minute, hour, and month fields match while at least one of day-of-month or day-of-week matches. It doesn't describe a durable work item, an acknowledgement, or an idempotency rule. Those belong elsewhere.

Here is the compact decision table I use before adding infrastructure:

Requirement	Scheduler only	Scheduler plus durable queue
Recompute the full result safely	Good fit	Usually extra machinery
Recover one reservation independently	Requires custom bookkeeping	Natural work-item boundary
Tolerate duplicate execution	Application must make the whole batch safe	Consumer still must be idempotent
Observe pending versus completed work	Must be modeled separately	Queue state helps, but domain state remains authoritative
Send an external side effect	Risky without a durable send ledger	Better isolation; still record a stable send key

The catch is real: a queue adds a broker, consumer deployment, retry policy, retention decisions, and another dashboard. It's not a good fit when the report is purely derived, reruns are cheap, and a delayed run is operationally harmless. Stick with one scheduled process in that case, but write a run record with the intended report date so the simplicity is visible and testable rather than assumed.

Put reservation truth in data, not delivery timing

“Exactly once” is the wrong acceptance criterion for the whole pipeline. The practical target is at-least-once execution with idempotent effects. A broker can redeliver a message if a consumer disappears before acknowledgement; an acknowledgement tells the broker that delivery was processed, not that every external system shares a single transaction.

That distinction matters. Suppose a worker receives reservation r_1842, whose hold ended at 14:30:00Z. It changes the row to expired, emits the report fact, sends an email, and stops before acknowledging the message. Redelivery is correct from the broker's perspective, even though three useful effects already happened. Without a conditional database update and a stable email key, the second attempt can emit the fact and send the notice again; if another worker receives that duplicate concurrently, inspecting the status and updating it as separate operations still leaves a race. Reversing the order only changes the failure: acknowledging first creates a window in which the work disappears from the queue while the reservation remains held. The durable answer is a compare-and-change transaction for the reservation plus a uniquely keyed outbox record, followed by acknowledgement only after commit. Now each interruption point has a deterministic recovery path: work before commit may repeat, while work after commit observes the recorded outcome.

For this system, define the invariant in the database: a reservation moves from held to expired only when its stored expiration time is due. Make that update conditional. The first worker that changes the row records the transition; later deliveries see that no transition is needed. In the same database transaction, write an outbox event keyed by the reservation ID. A separate publisher may enqueue that event repeatedly, but consumers deduplicate on the event key.

Short and sharp: duplicates are expected.

The daily report is a second projection, not part of the expiration transaction. Build it from committed expiration records for a closed reporting interval, and assign a key such as (tenant_id, report_date). Before contacting the email provider, store a send attempt against that key; after acceptance, store the provider's reference if one is available. I'm not sure any universal retry interval is defensible here — provider behavior and the business tolerance for late mail decide it — so test the interval with your own failure and latency data.

A focused Python reference model

Even in a Node.js SaaS, I like to sketch the state machine in Python first because the test surface stays obvious. This is a reference model, not a broker client. The database methods must be implemented with transactions, and publish must target a durable queue in the production adapter.

from dataclasses import dataclass
from datetime import datetime, timezone
from typing import Protocol


@dataclass(frozen=True)
class ExpireReservation:
    reservation_id: str
    expires_at: datetime


class ReservationStore(Protocol):
    def find_due(self, now: datetime, limit: int) -> list[ExpireReservation]: ...

    def expire_and_record_event(
        self, reservation_id: str, expected_expiry: datetime
    ) -> bool: ...


class DurableQueue(Protocol):
    def publish(self, key: str, payload: dict[str, str]) -> None: ...


def schedule_due_expirations(
    store: ReservationStore, queue: DurableQueue, batch_size: int = 200
) -> int:
    now = datetime.now(timezone.utc)
    due = store.find_due(now=now, limit=batch_size)
    for item in due:
        queue.publish(
            key=f"expire:{item.reservation_id}",
            payload={
                "reservation_id": item.reservation_id,
                "expected_expiry": item.expires_at.isoformat(),
            },
        )
    return len(due)


def handle_expiration(message: dict[str, str], store: ReservationStore) -> None:
    expected = datetime.fromisoformat(message["expected_expiry"])
    store.expire_and_record_event(
        reservation_id=message["reservation_id"],
        expected_expiry=expected,
    )
    # Acknowledge through the queue adapter only after this returns successfully.

The expire_and_record_event operation needs one precise contract: atomically change the row only if its status is still held and its expiration timestamp equals expected_expiry, then insert an outbox row with a unique event key. Equality protects a renewed hold. If the customer extended the reservation between discovery and handling, the stale message becomes a harmless no-op rather than expiring the new hold.

There is a subtle split here — worth keeping. The scheduling query may see the same due row on several ticks before the worker commits, so publication can repeat. That is fine because correctness sits in the conditional transition. Trying to make the scanner perfectly unique with only in-memory flags moves correctness into the least durable part of the design.

In the Node.js implementation, keep the same boundaries: one scheduler adapter, one queue adapter, one transactional repository, and a tiny handler. The language changes; the invariant doesn't. Don't let a broker-specific callback leak into the domain function, because that makes failure injection and notebook-to-prod parity much harder to maintain.

Test crash boundaries, not the happy-path timestamp

A test that asserts “the callback ran at midnight” proves very little. The higher-value eval matrix crosses a failure point with a restart and then checks durable state. Freeze time, seed reservations on both sides of the hold deadline, and run these cases:

1. Stop after the due-row query. On restart, every due reservation must still be discoverable.
2. Publish the same expiration event twice. Exactly one held row should transition, while both handler calls may complete successfully.
3. Renew a hold after discovery but before handling. The old event must not expire the renewed reservation.
4. Stop after the database commit but before queue acknowledgement. Redelivery must observe the completed transition and make no second domain change.
5. Generate the same tenant report twice. The stable (tenant_id, report_date) key must prevent a second accepted send.

That fourth case is the one people skip. RabbitMQ's acknowledgement documentation distinguishes consumer acknowledgements from publisher confirms: they solve different reliability problems and are orthogonal. A consumer acknowledgement covers delivery from broker to consumer; a publisher confirm covers the publisher-to-broker side. If a chosen queue exposes equivalent concepts, test both edges rather than treating “queued” as one indivisible event.

Prompt cost belongs in this eval too if the daily report contains an LLM-written summary. Store the deterministic report facts first, then generate prose from that snapshot. Record input tokens, output tokens, model identifier, prompt version, and report key beside the attempt. A retry can then reuse accepted text or be compared against the same facts. Otherwise a transport retry quietly becomes another model call, changes the wording, and obscures both cost and evaluation.

No guessing required.

Count the operational bill before adopting the queue

Measure the guarantee your users can observe: expiration lag from expires_at to committed status, oldest unprocessed outbox age, redelivery count, conditional no-op count, and report-send outcomes by stable key. Queue depth alone can rise because consumers are slow, because publishers produced a burst, or because poison work keeps returning; it isn't a diagnosis.

Also record scheduler starts, finishes, and the intended logical interval. Wall clocks have awkward behavior — including daylight-saving transitions called out by the crontab manual — so define reporting intervals in an explicit time zone and store their UTC boundaries. Reservation expiry itself should compare timestamps, not rely on a particular local clock label occurring exactly once.

Choose the scheduler-only version when a full rerun is safe and the team values a smaller operational surface over per-item recovery. Choose the combined version when each reservation must eventually leave held, retries must be isolated, or an external email side effect needs an auditable key. For stricter multi-step workflows with compensation, long waits, or human approval, this small pattern may fall short; a durable workflow engine can make those states explicit, at the cost of a larger programming and operations model.

Before shipping, run the crash matrix and inspect the resulting rows. The architecture earns its extra moving parts only if recovery is visible, idempotency holds, and the measured expiration lag meets the product's hold-window promise.

References

• https://man7.org/linux/man-pages/man5/crontab.5.html
• https://www.rabbitmq.com/docs/confirms