Short answer: have a scheduled Node.js trigger admit one durable retention run through an authenticated HTTP handler, then let queue workers delete old records in bounded, idempotent batches.
The deciding constraint is not the clock. It is the ability to prove, after retries and a deployment, which retention policy authorized a deletion, which cutoff governed it, and which records were affected. A cron process that reports success after opening an HTTP connection cannot supply that proof for a long-running job.
This matters sharply in systems with payment or ledger-adjacent data. Retention may be required, but legal holds, reconciliation windows, and audit obligations can change the eligible set. The cleanup path therefore needs to treat deletion as a stateful business operation, even when its input is merely a nightly schedule.
How should a Node.js cron HTTP endpoint start scheduled data cleanup queue jobs?
Make time an input to admission, rather than an execution engine. The scheduler sends a request with a deterministic run key, such as retention:transactions:2026-08-07, and the handler creates that run exactly once. It resolves the retention cutoff once, persists the policy version and cutoff with the run, and writes an initial queue message through a durable outbox. A duplicate request with the same key returns the existing run instead of starting a competing cleanup.
That fixed cutoff is easy to underestimate. Recomputing "now minus 90 days" inside every worker moves the predicate while the job is in progress; a record that was initially outside the cleanup may become eligible halfway through it. A pagination cursor does not repair this semantic change: it orders the work but cannot establish which population was authorized when the run began. For a ledger-oriented system, an auditor should be able to reconstruct the predicate without guessing which worker clock was used, whether the scheduler retried, or whether a deploy changed the retention configuration between two batches. Store the run key, policy version, cutoff, requester, state transitions, batch cursor, attempted count, deleted count, and final disposition outside the rows that are being removed. If a request repeats the run key with a different cutoff or policy version, reject the changed parameters rather than silently attaching them to the existing run. This turns an accidental schedule change into an observable decision that can be reviewed before any extra records are removed. The audit record is not ornamental metadata; deletion removes much of the evidence that would otherwise explain the action.
Keep it small.
The HTTP handler owns authentication, parameter validation, idempotent admission, and a quick response after intent is durable. The worker owns enumeration, deletion, retry-safe progress, and continuation. The database owns the authoritative state transition. This separation is not an argument for a particular queue; it is a way to ensure that a transport retry never becomes a second logical retention decision.
For a queue, assume at-least-once delivery and build exactly-once effects. A worker first claims a batch state in a transaction. It deletes a page constrained by the persisted cutoff and cursor, marks the batch complete, and inserts an outbox record for the next cursor in that same transaction. A separate dispatcher may send that outbox record more than once, so the downstream batch identity should be unique on (run_id, cursor). The result is recoverable progress rather than a fragile promise of exactly-once delivery.
An admission limit deserves an explicit contract. If the endpoint cannot accept another run, HTTP 429 communicates that condition; the Retry-After response header can tell a scheduler when a retry is appropriate. Do not respond successfully just because the request was parsed. The response should mean that the run identity and its initial work item are durable.
Which execution boundary fits a nightly cleanup of old records?
The right design depends on the measured work unit and the evidence required after completion, not on a preference for cron syntax. The comparison below separates a short transaction from a job whose duration or volume can vary.
| Design | Completion boundary | Appropriate use | Main risk |
|---|---|---|---|
| Scheduler runs a database command | Database session | Controlled operations with transactional audit events | Credentials and partial progress are managed at the scheduler edge |
| Scheduler calls a synchronous handler | One request and one bounded transaction | A small, indexed delete that fits a conservative deadline | Growth turns a request into an unbounded job |
| Handler admits batches to workers | Run ledger, queue, worker, and database state | Variable-volume or long-running cleanup | The team must operate durable state, dispatch, and monitoring |
The queued option is not suitable when the table is demonstrably small, the delete is indexed and atomic, and the team cannot support an outbox plus worker operational model. Use the synchronous handler in that case, but retain the deterministic run key, immutable cutoff, affected-row cap, and audit result. Direct database scheduling can also be valid when database operations already provide credential isolation and transactional audit records.
The catch is that the long-running design has more parts, and every part has to be owned. A dead-letter queue is a quarantine boundary, not evidence that the retention run completed. AWS documents that dead-letter queues can affect ordering, so sequence-dependent cleanup needs an explicit cursor and state machine rather than an assumption that message movement preserves order. A run with quarantined work remains incomplete until an operator or a documented automated policy resolves it.
What does the critical idempotent worker path look like?
The following Go sketch is deliberately independent of a queue or HTTP framework. A Node.js service can follow the same contract: transport code asks the store to admit or process work; the durable transaction supplies the correctness boundary. Claim returns false for a duplicate delivery, making acknowledgement of a repeated message safe.
package retention
import (
"context"
"database/sql"
"time"
)
type Batch struct {
RunID string
Cursor string
}
type Store interface {
Claim(ctx context.Context, tx *sql.Tx, batch Batch) (bool, error)
DeletePage(ctx context.Context, tx *sql.Tx, runID, cursor string, cutoff time.Time) (next string, deleted int64, done bool, err error)
Complete(ctx context.Context, tx *sql.Tx, batch Batch, next string, deleted int64, done bool) error
AddOutbox(ctx context.Context, tx *sql.Tx, batch Batch) error
}
func ProcessBatch(ctx context.Context, db *sql.DB, store Store, batch Batch, cutoff time.Time) error {
tx, err := db.BeginTx(ctx, nil)
if err != nil {
return err
}
defer tx.Rollback()
claimed, err := store.Claim(ctx, tx, batch)
if err != nil || !claimed {
return err
}
next, deleted, done, err := store.DeletePage(ctx, tx, batch.RunID, batch.Cursor, cutoff)
if err != nil {
return err
}
if err := store.Complete(ctx, tx, batch, next, deleted, done); err != nil {
return err
}
if !done {
return store.AddOutbox(ctx, tx, Batch{RunID: batch.RunID, Cursor: next})
}
return tx.Commit()
}
The important detail is the outbox. Committing batch progress and sending a continuation as unrelated durable actions creates an ambiguous interval: progress can be committed without a next message, or a message can arrive before its state exists. Writing the continuation intent beside completion closes the business-level gap; a dispatcher can retry delivery until the queue accepts it. Database uniqueness makes repeated dispatch harmless.
Use keyset-style cursors when practical, not offsets that drift as rows disappear. The deletion predicate should include both the fixed cutoff and any hold or policy exclusion evaluated under the retention rules. A legal hold added during a run needs a defined precedence rule; compliance teams, counsel, and data owners must decide it before automation enforces it. I'm not sure a universal default exists, because retention law and record classification differ by jurisdiction. The implementation should preserve the policy decision and the evidence used to apply it.
What must be tested and observed before the schedule is trusted?
Test the ugly timelines. Submit the same admission request concurrently. Deliver one batch twice. Stop a worker after its transaction commits but before it acknowledges the message. Delay dispatch beyond the next scheduled interval. Add a hold before a later batch. Each test should leave a run ledger whose state is explainable without reading transient logs.
Observability should describe work and impact: run age, queue age, batches claimed, rows examined, rows deleted, retries, quarantined messages, database lock wait, statement duration, and database load. Alert on an overdue or incomplete run, not merely on a failed cron invocation. A failed invocation may be harmless if a later admission finds the same durable run; an overdue run is evidence that the retention outcome remains unresolved.
Deployment needs the same care. Give workers leases or a drain procedure, make schema changes readable by both old and new messages, and retain audit records long enough for reconciliation. Cost follows scans, writes, queue operations, and retained evidence, so load testing should include representative table cardinality and concurrent production queries. There is no substitute for testing the deletion predicate against held, disputed, and already-processed records.
For scheduled data cleanup, choose the smallest execution model that keeps these invariants true under the workload you measured. The goal is not a clever cron entry. It is a deletion process whose intent, effect, and remaining work can be independently reconciled.
Top comments (0)