Short answer: Use a small cron trigger to enqueue bounded reservation ID batches, then let an idempotent PostgreSQL worker conditionally delete only holds that remain expired; a duplicate queue delivery should be a harmless no-op.
That is the whole rule.
The incident lesson: delivery is a hint, not proof
I've been paged for both sides of scheduling failure: missed jobs and duplicate deliveries. They look opposed, but they expose the same bad assumption. A team treated one cron invocation and one queue message as proof that one unit of work would happen exactly once. The runbook then had nowhere useful to go. Replaying the trigger risked deleting twice, while refusing to replay left stale state behind.
For an edtech reservation system, the boundary is precise. A learner gets a temporary seat hold. Once its fixed hold window ends, cleanup may expire that reservation, but it must not touch a renewed or confirmed reservation. The invariant belongs in the write condition: delete only the row with the expected reservation ID, expiry time, and still-expirable state. The scheduler's clock tells the system when to look. It does not grant permission to delete.
This distinction matters because cron, a queue, and a worker have different responsibilities. The trigger creates a run identity and a cutoff. The producer finds candidates and divides them into bounded batches. The queue carries those batches and may redeliver them. The worker checks current database state, commits the conditional delete, and only then acknowledges delivery. A retry repeats the check rather than repeating an unconditional side effect.
No exceptions.
I use a simple postmortem question here: if this exact message arrives again after the process loses its connection, what observable state changes? "Nothing" is the desired answer once the first transaction committed. RabbitMQ's acknowledgement documentation is useful background for the delivery side of that question: consumer acknowledgements and publisher confirms solve different problems, and neither replaces an idempotent database operation.
How should a Node.js cron trigger queue batches for idempotent PostgreSQL cleanup?
Keep the Node.js scheduled task thin. It should acquire whatever single-run coordination your deployment already trusts, assign an opaque run ID, freeze a cutoff timestamp, scan eligible reservation IDs, and publish small batches. It should not fetch a row and later issue an unconditional delete based on that stale read. The race is obvious once written down: cleanup reads "expired," the learner renews the hold, and cleanup deletes a reservation that is now valid.
Put enough evidence in each message for the worker to make a fresh decision. A practical contract has a version, run ID, batch ID, fixed cutoff, and items containing reservation ID plus the expiry value observed by the producer. Do not put an instruction such as delete=true in the message and treat it as authorization. The database row may have changed while the message waited.
State wins.
For example, a batch could represent ten or a hundred candidates; the correct size is workload-specific. I'm not sure there is a universal number, because row width, index shape, lock duration, queue limits, and the acceptable replay cost all matter. Measure transaction time and redelivery behavior in your own system, then set a ceiling. The invariant doesn't depend on that tuning.
The cron trigger also needs a catch-up rule. Record run state separately from reservation state, and make an operator replay use the same cutoff or an explicitly chosen new cutoff. A missed run should increase backlog, not change correctness. A duplicate run should increase no-op work, not change outcomes.
| Boundary | Owns | Must not assume |
|---|---|---|
| Scheduler | run ID, cutoff, launch policy | one launch means completion |
| Producer | candidate scan, batch boundaries | candidates remain eligible |
| Queue | durable handoff and redelivery policy | delivery is exactly once |
| Worker | conditional mutation, result classification | first attempt reached commit |
| Runbook | replay and backlog decisions | an absent acknowledgement means no commit |
Make the mutation carry the idempotency rule
An idempotency table can be useful when a worker performs several side effects, but it is not automatically required for this cleanup. If the only effect is deleting an eligible reservation row, the delete predicate can be the idempotency mechanism. Match the immutable ID, the producer-observed expiry timestamp, the fixed cutoff, and the state that permits expiry. A renewed hold changes at least one of those facts, so the old message no longer matches.
There are three normal outcomes, not two. One affected row means this delivery expired the reservation. Zero affected rows means the desired state already exists or the reservation is no longer eligible; acknowledge it as a successful no-op. A database or transport error means the result is unknown, so do not acknowledge and let the delivery policy retry. Keep those outcomes separate in logs and metrics. Otherwise harmless replays look like failures, and actual retry storms disappear inside a generic "processed" counter.
Unknown means retry.
Use a stable idempotency key such as expire-reservation:<reservation-id>:<observed-expiry>. It is diagnostic evidence even if the conditional delete is sufficient. Carry the run and batch IDs through structured logs. The useful counters are candidates found, batches published, deliveries attempted, rows deleted, no-op replays, retryable failures, and oldest eligible reservation age. The last one catches the quiet failure mode: a green cron dashboard with a growing cleanup backlog.
Be careful with time. Create the cutoff once at the trigger, serialize it with an explicit timezone, and compare database timestamps against that value. Don't let every worker call its local clock and silently create a moving boundary. A fixed cutoff makes replay understandable: the repeated run asks the same question about the same window.
Put the preventative check in the worker
The following Go worker path shows the database boundary even when the producer happens to be a Node.js service. The message contract is language-neutral, and keeping the decisive SQL beside the transaction makes the review point hard to miss.
package cleanup
import (
"context"
"database/sql"
"errors"
"fmt"
"time"
)
type ExpireItem struct {
ReservationID string
ObservedExpiry time.Time
}
type Batch struct {
Version int
RunID string
BatchID string
Cutoff time.Time
Items []ExpireItem
}
type Result struct {
Deleted int
NoOp int
}
func ExpireBatch(ctx context.Context, db *sql.DB, batch Batch) (Result, error) {
if batch.Version != 1 {
return Result{}, fmt.Errorf("unsupported message version %d", batch.Version)
}
tx, err := db.BeginTx(ctx, nil)
if err != nil {
return Result{}, err
}
defer tx.Rollback()
result := Result{}
for _, item := range batch.Items {
execResult, err := tx.ExecContext(ctx, `
DELETE FROM seat_reservations
WHERE reservation_id = $1
AND status = 'held'
AND expires_at = $2
AND expires_at <= $3`,
item.ReservationID,
item.ObservedExpiry,
batch.Cutoff,
)
if err != nil {
return Result{}, err
}
rows, err := execResult.RowsAffected()
if err != nil {
return Result{}, err
}
switch rows {
case 0:
result.NoOp++
case 1:
result.Deleted++
default:
return Result{}, errors.New("reservation ID affected multiple rows")
}
}
if err := tx.Commit(); err != nil {
return Result{}, err
}
return result, nil
}
The consumer acknowledges the message only after ExpireBatch returns success. If the connection disappears around commit, the caller cannot safely infer whether the transaction committed. Retry anyway. The equality check on expires_at protects a renewed row, while a previously deleted row produces zero affected rows. This is why the retry path can be blunt and operationally useful.
Keep the batch transaction bounded. A large single delete may be attractive because it has less queue overhead, but it also couples discovery, mutation, and retry into one long operation. Smaller batches isolate failures and make lag visible at useful granularity. They do add messages, acknowledgements, and bookkeeping. Your mileage may vary — tune after observing the database, not from a generic batch-size slogan.
Test the replay before choosing the scheduler
The first acceptance test should deliver the same batch twice and assert that the second delivery deletes zero rows. Then renew one hold after candidate discovery but before worker execution; the old batch must leave it intact. Also test a worker stop immediately before commit, immediately after commit, and before acknowledgement. These are state-machine tests, not timing tricks: after every retry, a reservation is either still eligible, safely expired, renewed, or confirmed.
Deployment deserves the same discipline. Introduce the message version before changing its shape, let old and new consumers overlap only when both understand messages already in flight, and cap concurrency against observed database pressure. Alert on backlog age and repeated retry attempts, not merely on whether cron fired. A scheduler can report a successful trigger while every downstream batch waits untouched.
The scheduler choice comes last. An application cron process is reasonable when the service already has dependable leader coordination and catch-up behavior. A hosted workflow scheduler can be adequate for coarse, non-critical launches; GitHub documents a schedule workflow event, for example, but coupling production expiry to a source-control workflow adds an external operational boundary that the runbook must own. Stick with a database-native or dedicated scheduler when strict launch control, private-network execution, or an existing operations model makes that boundary simpler. The catch is that changing schedulers never repairs a non-idempotent worker.
This pattern is not suitable when expiry must synchronously release several resources across systems, emit an externally visible event exactly once, or preserve every transition for audit. In that case, a conditional delete alone is too small a consistency boundary. Keep an explicit state transition, transactional outbox, and deduplication record, then let a separate publisher drive downstream effects. For modest reservation cleanup where deletion is the sole effect, those extra tables may be needless operational weight.
Ship only after the replay test passes.
Top comments (0)