This scheduled database cleanup for a large healthtech dataset is primarily an operational recovery problem. The difficult question is not how to make a cron trigger fire; it is how to resume after a Node.js queue worker dies halfway through a tenant's data without broadening the deletion scope or losing the audit evidence.
Short answer: let a cron trigger create a bounded cleanup run, then let idempotent queue workers drain deterministic chunks. Keep the clock, the work planner, and the database mutation behind separate failure boundaries. A rate-limited worker pool should be able to stop, resume, and reconcile its work without asking the scheduler to remember database state.
That answer has a compliance qualifier. Retention policy, legal hold, minimum-necessary access, and the applicable health-data rules must authorize the deletion before any message is published. Queue mechanics cannot turn an unauthorized purge into an authorized one.
What should Node.js teams do when Postgres cleanup meets a cron trigger and queue worker?
The Node.js trigger should create a run record with an immutable cutoff and policy version, partition the eligible scope into bounded work references, and enqueue those references. It should return after planning and publication are durable. It should not scan the whole table while an HTTP invocation is still open.
The worker then loads one reference, checks the policy and tenant scope, deletes a bounded batch, records the result, and acknowledges the message only after the database transaction has committed. A reference can identify a tenant, relation, key interval, cutoff, and run ID. It should not contain a giant list of patient rows; the database remains the authority for eligibility.
Small changes matter. A retry must use the original cutoff, not now() in the consumer. A second scheduler invocation must either join the existing run or create a separately identifiable run under an explicit overlap rule. Acknowledging before the commit creates silent loss; committing and then losing the acknowledgement creates duplicate delivery. The latter is acceptable when the effect is idempotent.
Three words: recovery beats speed.
I would measure chunk duration, lock waits, rows affected, retry count, and oldest pending work, then tune the planner against the actual Postgres workload. I am not sure a row count can predict a safe batch size for an unfamiliar schema: indexes, foreign keys, vacuum behavior, and tenant skew all change the cost. Your mileage may vary, but the worker contract should stay stable while that number changes.
Decision record: invariants and failure boundaries
The design has a small set of invariants. The cutoff is fixed before publication. The chunk identity is deterministic. The authorized scope is stored with the run. The mutation and its audit record commit together. Acknowledgement follows the committed outcome. These rules give operators something better than a green scheduler status: they provide a path from policy to run to chunk to database result.
At-least-once delivery is the assumption to design for. A queue can redeliver after a timeout, process restart, or lost acknowledgement. Exactly-once execution is not a credible premise for this pipeline. Exactly-once effect is the useful target. If the same chunk key appears again, the worker should observe the prior accepted result or repeat the same bounded mutation without deleting outside its original scope.
The scheduler is responsible for time and invocation intent. The planner is responsible for a finite, inspectable work set. The queue is responsible for delivery attempts. The worker is responsible for database state. The audit store is responsible for evidence. Do not use a scheduler's invocation log as the deletion ledger, and do not infer completion merely because the queue has become quiet.
Consider a tenant partition that contains several million eligible rows. The planner records a cutoff at 02:00 UTC, emits ranges in stable key order, and the worker pool has processed the first 40 ranges when a database connection disappears. Some transactions committed; one message may be invisible temporarily; another may be redelivered. Recovery is correct only if the next attempt can distinguish those states using durable chunk records, retry the uncertain range without changing its cutoff, and continue with the remaining ranges without treating a delivery attempt as a second business event. A dashboard showing "job failed" is not enough, because it does not identify the committed boundary or prove that a legal hold was excluded. The run record, chunk records, database audit entries, and final reconciliation must answer those questions together.
That is the failure to design for.
For a regulated dataset, the audit record should carry the run ID, chunk ID, policy version, cutoff, tenant scope, attempt number, actor or service identity, rows selected, rows affected, and terminal state. Retain the evidence according to the organization's policy. A count that cannot be reconciled to a stable scope is an operational metric, not proof of a compliant purge.
Comparing recovery boundaries
| Arrangement | Recovery unit | Good fit | The catch |
|---|---|---|---|
| One cron handler scans and deletes | The complete invocation | A small, bounded, repeatable cleanup | A timeout or process exit makes the whole operation the retry unit |
| Cron plus a queue and workers | One deterministic chunk | Large datasets, tenant isolation, and per-chunk reconciliation | More state must be observed and reconciled |
| A workflow orchestrator | A task or workflow step | Long dependency chains, joins, and explicit human gates | More orchestration state than a simple retention run needs |
| A database-native scheduler | A database job or transaction boundary | Teams that want scheduling close to the data | Application-level rate limits and external retry policy still need owners |
The table is a decision aid, not a ranking. Choose the smallest boundary that can recover the real failure. A queue is justified when a failed tenant partition should be retried independently, when the rate-limited pool must drain gradually, or when operators need to pause new work while already-committed chunks remain auditable.
It is not suitable when the cleanup is tiny, its runtime is demonstrably bounded, and there is no independent retry or reconciliation requirement. In that case, a direct database job can be easier to operate. Stick with the simpler boundary when adding a queue would create more credentials, dashboards, and state than the team can reliably own.
The rejection criterion is also concrete: if nobody can answer which run owns a chunk, which cutoff authorized it, and whether its database transaction committed, the architecture is not recoverable enough for a healthtech purge. A 429 from a downstream dependency or a process exit with no final status is not a reason to widen the next attempt; it is a reason to preserve the same work identity and retry under the rate limit.
Critical path in Go
The following code shows the contract rather than a framework integration. The Node.js trigger and worker can implement the same state machine. The store must enforce the idempotency rule in durable state; an in-memory set is not an audit trail.
package cleanup
import (
"context"
"crypto/sha256"
"encoding/hex"
"fmt"
"time"
)
type Chunk struct {
RunID string
TenantID string
Table string
FromID int64
ToID int64
Cutoff time.Time
}
func (c Chunk) ID() string {
value := fmt.Sprintf("%s|%s|%s|%d|%d|%s",
c.RunID, c.TenantID, c.Table, c.FromID, c.ToID,
c.Cutoff.UTC().Format(time.RFC3339Nano))
digest := sha256.Sum256([]byte(value))
return hex.EncodeToString(digest[:])
}
type Store interface {
// Apply must commit the deletion and audit result atomically.
// Repeating the same ID and scope must preserve the accepted state.
Apply(ctx context.Context, id string, chunk Chunk) error
}
type Queue interface {
Ack(ctx context.Context, id string) error
Retry(ctx context.Context, id string) error
}
func Process(ctx context.Context, store Store, queue Queue, chunk Chunk) error {
id := chunk.ID()
if err := store.Apply(ctx, id, chunk); err != nil {
_ = queue.Retry(ctx, id)
return fmt.Errorf("apply chunk %s: %w", id, err)
}
if err := queue.Ack(ctx, id); err != nil {
return fmt.Errorf("acknowledge chunk %s: %w", id, err)
}
return nil
}
The dangerous case is not a clean failure. It is an ambiguous one: the transaction may have committed, and the worker may have lost its connection before acknowledgement. The queue will deliver the message again. Apply must use the chunk ID and scope to make that second attempt a no-op or the same accepted result, while the audit record remains attributable to the original run.
The worker should also honor concurrency limits deliberately. A pool that is rate-limited by the database or an upstream service needs backoff, visibility-timeout sizing, and a dead-letter policy for messages that cannot reach a terminal state. A dead-letter queue is a quarantine, not a success state; its contents need an owner, an alert, and a replay procedure that preserves the original chunk identity. The public queue documentation describes dead-letter queues as a way to isolate messages that cannot be successfully processed, which is the right operational model here.
Testing should exercise the failure boundaries, not just the happy path. Kill a worker after the database commit and before acknowledgement. Deliver the same chunk twice concurrently. Restart the planner after publishing half its references. Pause the schedule while workers finish an existing run. Assert that the resulting rows, audit entries, and run counts remain reconcilable. A unit test for the hash is useful; it is not a substitute for a database transaction test.
The rejected design and its valid use
The rejected design is a cron handler that scans and deletes the entire large dataset before returning. It couples trigger availability, query duration, retry behavior, progress, and audit completion to one invocation. When that process exits at row 8,000,001, the next attempt has no safe reason to assume that rows before that point were committed unless the application recorded that boundary durably.
That design still works for a small cleanup whose runtime is bounded, whose repeated effect is idempotent, and whose operators do not need independent chunk recovery. Do not add a queue to make a diagram look modern. Add one when the failure unit, rate limit, tenant isolation, or evidence requirement demands it.
For the stated healthtech workload, cron should remain the clock, not the execution engine. A bounded planner, an idempotent worker pool, and a durable audit trail make recovery explicit; the trade-off is additional operational state, which must be monitored rather than hidden.
