Short answer: keep the nightly trigger small, make it call an authenticated HTTP endpoint, and move a growing property-record cleanup into bounded queue jobs with a durable idempotency key. A direct delete is acceptable only when its worst-case runtime and retry effect are both bounded.
That is the least complex design that survives a missed response. The scheduler establishes intent; the endpoint creates work; the worker owns the delete.
What reliability boundary should a Node.js cron HTTP endpoint use for nightly data cleanup?
Consider a property-management database with leases, maintenance records, and tenant activity. Each night, a retention rule finds records older than a cutoff. The first version often looks harmless: a cron trigger calls an HTTP handler, the handler runs one indexed delete, and the request returns. Then the portfolio grows, one property has an unusually large history, or the retention policy changes. The same request now competes with daytime traffic and may be retried after the caller has lost its response.
I plan around that boundary before it becomes an incident. A scheduler can say “attempt this run,” but it cannot prove that the database mutation happened once. An HTTP client may retry a timeout. A queue may deliver a message again after a worker has committed. Those are normal failure modes, so the delete must be safe under repetition.
The invariant is simple: one logical slice of records has one stable operation ID, and recording that ID happens in the same database transaction as applying the slice. A second delivery sees the recorded operation and performs no delete. That is more useful than a dashboard that merely says the cron request returned 200.
Three words: intent, work, result.
How can a Go API make duplicate cleanup delivery idempotent?
Use one direct HTTP request only when the maximum candidate set is known, the query is indexed, and the handler has a comfortable deadline below its hosting limit. “Usually finishes quickly” is not capacity planning. The relevant number is the largest plausible property or portfolio slice during the retention window.
For a growing set, let the endpoint validate the schedule request, create a run record, partition the candidates, enqueue compact references, and return. It should not hold the web request open while deleting every old row. Each message can identify a property, cutoff, stable cursor, and limit. The worker fetches the current rows for that bounded range and applies the mutation transactionally.
At-least-once delivery changes the design. A message acknowledgement is not the same thing as a database commit, and a lost acknowledgement can cause another delivery. The worker therefore needs a unique operation record, a deterministic key, and a transaction that couples the claim with the delete. If the business operation also emits a notification or accounting event, that side effect needs its own outbox or idempotency contract; a database guard alone does not make an external email reversible.
The endpoint should authenticate the scheduler, reject an unexpected cutoff or tenant scope, and return a run identifier that operators can correlate with queue metrics. It should also enforce a concurrency policy: two schedules must not silently create overlapping ranges. A database lease or a unique run key is a better guard than hoping the scheduler never fires twice. The failure sequence is easy to miss in a review: the cron trigger starts at 02:00, the planner records a run, and the HTTP connection drops while the first batch is being enqueued. The scheduler retries; the second request sees the same schedule key and resumes or reports the existing run instead of creating a second set of ranges. Later, a worker deletes its batch, commits the idempotency row, and dies before acknowledging the queue message. The redelivery is expected to find that row and stop. If the worker instead generates a fresh UUID on every delivery, the queue has turned a transport retry into a second mutation. That is why the run key, batch cursor, and operation ID belong in durable state and in the design review, even though the endpoint itself should remain short.
The Go implementation behind a repeatable delete
The code below leaves the database transaction behind an interface, because the transaction boundary is the important part. ApplyCleanup should insert the operation ID into a table with a unique constraint and delete the bounded batch before committing. A duplicate operation returns applied == false.
package cleanup
import (
"context"
"crypto/sha256"
"encoding/hex"
"fmt"
"time"
)
type Batch struct {
PropertyID string
Cutoff time.Time
AfterID int64
Limit int
}
type Store interface {
ApplyCleanup(context.Context, string, Batch) (applied bool, deleted int64, err error)
}
func operationID(batch Batch) string {
value := fmt.Sprintf("retention:%s:%s:%d:%d", batch.PropertyID,
batch.Cutoff.UTC().Format(time.RFC3339), batch.AfterID, batch.Limit)
digest := sha256.Sum256([]byte(value))
return hex.EncodeToString(digest[:])
}
func Handle(ctx context.Context, store Store, batch Batch) (int64, error) {
if batch.PropertyID == "" || batch.Limit < 1 || batch.Limit > 1000 {
return 0, fmt.Errorf("invalid cleanup batch")
}
applied, deleted, err := store.ApplyCleanup(ctx, operationID(batch), batch)
if err != nil {
return 0, fmt.Errorf("apply cleanup: %w", err)
}
if !applied {
return 0, nil
}
return deleted, nil
}
The cursor must describe an ordering the query can reproduce. An offset is a poor choice when rows are being removed between attempts; a monotonic primary key or another stable range marker makes a retry refer to the same logical work. The limit is part of the operation ID here, so changing it creates a new operation deliberately rather than silently changing an in-flight batch.
Retry policy belongs at both edges. A worker should retry transient storage failures with a capped backoff and send repeatedly failing messages to a dead-letter queue after an explicit delivery limit. The scheduler or endpoint should treat HTTP 429 as a rate signal, honor Retry-After when present, and avoid a tight loop; the HTTP specification describes 429 as a response indicating that too many requests were made in a period. A dead-letter queue is for inspection and controlled recovery, not permission to delete the idempotency table and run everything again.
How do we measure cleanup capacity before choosing a queue?
The drain window is a requirement, not a hopeful graph line. Estimate the largest number of batches in one run, measure worker service time under database contention, and leave room for duplicate deliveries and daytime load. If a worker clears 12 batches per minute and the run can produce 1,000, the arithmetic already says that one worker cannot drain the set in an hour. The exact service rate will vary; measure it with production-shaped rows and indexes.
Track run creation, enqueue count, successful claims, duplicate claims, delete count, retry count, dead-letter count, oldest message age, and the age of the oldest eligible record. Alerting only on the cron invocation misses the useful question: will this cleanup finish before the next retention boundary? Record the cutoff and property ID in structured logs, while keeping sensitive tenant data out of log messages.
A useful SLO might be stated as “99% of eligible records are processed within the retention window,” with a separate latency objective for the trigger endpoint. The two objectives expose different failures. A fast endpoint can enqueue work faster than workers can consume it. A healthy queue can still be operating on a query plan that makes one property miss its deadline.
There is a practical deployment detail here. Ship the worker and schema change in a compatible order: create the idempotency table and indexes before sending the first batch, then deploy the producer and consumer. Test duplicate delivery, a worker crash after commit but before acknowledgement, a partial page, a throttled database, and a second trigger for the same cutoff. These tests are more valuable than a happy-path cron test because they exercise the actual failure boundary.
Which governance rules keep retention deletes reviewable?
| Design | Suitable when | Cost or limitation |
|---|---|---|
| Direct HTTP delete | The worst-case set is small, indexed, and harmless to repeat | Request deadlines and connection loss become the job boundary |
| HTTP planner plus queue workers | The set grows, work needs throttling, or properties vary widely in size | More state to operate: run records, idempotency, metrics, and recovery |
| A self-hosted scheduler and worker platform | The team already owns that control plane and needs private-network execution | On-call responsibility includes scheduler upgrades, queue capacity, and worker placement |
| A workflow engine | The cleanup has dependencies, approvals, joins, or backfills | The orchestration model is heavier than one bounded deletion pipeline |
The catch is that queued work is not automatically more reliable. It exchanges a long request for a distributed workflow, and the workflow needs durable state. Choose the direct request when the bound is real and tested. Choose the queue when the mutation needs independent retry, throttling, or per-property isolation. Keep a workflow engine for actual workflow semantics.
Your mileage may vary on batch size: database indexes, lock behavior, and tenant distribution determine the useful value. I'm not sure a single global batch size is defensible without measuring those variables. Start conservatively, record the result, and change the bound through an explicit versioned policy.
Further reading
- https://docs.aws.amazon.com/AWSSimpleQueueService/latest/SQSDeveloperGuide/sqs-dead-letter-queues.html
- https://developer.mozilla.org/en-US/docs/Web/HTTP/Reference/Status/429
Top comments (0)