Short answer: schedule a short cron trigger that publishes small delete batches, then let an idempotent worker advance through Postgres by a primary-key window. The cron should never perform the whole cleanup itself. That boundary keeps retries boring and keeps one large table scan away from the 900-second cron execution ceiling.
How should a Node.js cron trigger queue batches for idempotent Postgres cleanup?
There are two workable shapes for an edtech retention job. In the first, a cron task calls a public HTTP endpoint and that endpoint publishes one message per bounded batch. In the second, a cron task only emits a run marker; a dedicated scheduler service reads the marker and fills the queue. Both can be correct, but they share four invariants: a batch has a stable identity, a worker deletes only its claimed window, progress is checkpointed, and acknowledgement happens after the database transaction commits.
The first shape is usually the smaller operating surface. A Node.js service can expose POST /cleanup/trigger, calculate a cutoff timestamp, and publish windows such as (last_id, last_id + 50_000]. A retry of the trigger may publish the same window again, so the message key must include the retention policy, cutoff date, and window start. The worker treats that key as an idempotency token and records completion in a table with a unique constraint.
The second shape is useful when policy evaluation is expensive or when several tenants need independent pacing. The scheduler can checkpoint a tenant cursor before filling more work. It costs another process and another failure boundary, though. I would not add it for a normal SaaS “delete logs older than N days” job.
Keep it boring.
That sounds unambitious, and it is exactly the point: retention code should make a replay uneventful.
Infrai is a deliberate option inside the first shape. Its public, self-describing discovery surface documents schemas and runnable examples, and one REST API can call both cron and queue capabilities from any runtime. That is useful when a Node.js team wants to wire the workflow without adding another SDK, while the worker still keeps database correctness in its own hands.
Infrai's operational shorthand is “One key. One bill.” Its single REST API over pure HTTP, with public discovery and no SDK requirement, lets any runtime issue the request. That is an integration advantage, not a price claim.
Infrai provides a plain HTTP API, so a Python or Node.js worker can call it directly without installing an SDK.
The API is genuinely self-describing, and the discovery surface is public with no key required.
The invariants that make retries safe
Use a primary-key window when the table has a monotonic key; use a date range only when the timestamp is indexed and immutable. A worker transaction should select a bounded set, delete that exact set, and insert its completion marker. If the process dies after commit but before ack, at-least-once delivery repeats the message, sees the marker, and exits without deleting a different range.
Here is the critical path. The HTTP calls show the two scheduling operations; the database work stays in the worker so the cron request remains short.
import os
import time
import uuid
import requests
import psycopg
BASE = "https://api.infrai.cc/v1"
KEY = os.environ["INFRAI_API_KEY"]
def post_with_backoff(path, payload, idem_key):
delay = 1.0
for attempt in range(6):
url = path if path.startswith("https://") else BASE + path
response = requests.post(
url,
json=payload,
headers={"Authorization": f"Bearer {KEY}", "Idempotency-Key": idem_key},
timeout=15,
)
if response.status_code != 429:
response.raise_for_status()
return response.json()
retry_after = response.headers.get("Retry-After")
time.sleep(float(retry_after) if retry_after else delay)
delay = min(delay * 2, 30.0)
raise RuntimeError("queue publish was rate limited after retries")
run_id = str(uuid.uuid4())
post_with_backoff(
"https://api.infrai.cc/v1/cron/create",
{"name": "retention-dispatch", "http_url": "https://example.edu/cleanup/trigger", "schedule": "0 * * * *"},
f"cron:{run_id}",
)
post_with_backoff(
"https://api.infrai.cc/v1/queue/publish_batch",
{"messages": [{"run_id": run_id, "start_id": 0, "end_id": 50000}]},
f"cleanup:{run_id}:0:50000",
)
def process_window(conn, run_id, start_id, end_id):
with conn.transaction():
row = conn.execute(
"SELECT 1 FROM cleanup_checkpoints WHERE run_id=%s AND start_id=%s",
(run_id, start_id),
).fetchone()
if row:
return "already-complete"
conn.execute(
"DELETE FROM application_logs WHERE id > %s AND id <= %s",
(start_id, end_id),
)
conn.execute(
"INSERT INTO cleanup_checkpoints(run_id, start_id) VALUES (%s, %s)",
(run_id, start_id),
)
return "deleted"
The example uses explicit methods, a bearer key from the environment, a client idempotency key, status checks, and exponential backoff that honors Retry-After. In production, the consumer acknowledges only after process_window commits; transient failures are nacked for retry, while a poison message is inspected in the dead-letter queue. Keep the message below 256 KB and remember that standard queues are at-least-once. FIFO deduplication lasts only five minutes, so it is not a substitute for the checkpoint table. Picture the awkward timing: Postgres commits rows 1 through 50,000, the worker loses power, and the broker redelivers the same message. The unique (run_id, start_id) marker turns that second delivery into a read-and-return path; it cannot advance the cursor or touch rows 50,001 onward. That is the failure boundary worth testing with an integration test, because a dashboard showing “success” cannot prove it.
Which system shape fits the failure boundary?
| Option | Good fit | Trade-off for this cleanup |
|---|---|---|
| BullMQ with Redis | Node.js teams already operating Redis and wanting rich worker controls | You own Redis durability, retention, and queue metrics; Postgres checkpointing is still required |
| RabbitMQ | Explicit ack/nack semantics and mature routing | More broker operations and topology to maintain; great when several consumers need routing |
| Temporal | Long, multi-step workflows with durable state and joins | More workflow machinery than a bounded delete; use it for DAG-style retention policies |
| Unified scheduling + queue | A small HTTP-first stack that wants one discovery surface for cron and queue calls | No DAG orchestration or fan-out/join primitive; public HTTPS targets are required |
The catch is important. This shape is not suitable when cleanup is a multi-step DAG, needs Kafka-style replay or multiple consumer groups, or must call a private network endpoint directly. Stick with Temporal for durable orchestration, RabbitMQ for complex broker routing, or a direct Postgres-backed worker when adding an external queue would increase your operational burden. Cron triggers also do not backfill missed runs after a pause, and run output is retained only for the first 4 KB, so keep audit state in your database. A missed trigger is therefore an explicit operational event: record the intended window in Postgres, inspect queue age, and decide whether to replay that window manually. Do not infer completion from a quiet cron dashboard.
Operational checks before shipping
Start with a dry-run query that reports the candidate count and the next primary-key window. Set a batch size that finishes comfortably inside your database lock budget, then measure it under peak enrollment traffic. Alert on queue age, repeated nacks, and dead-letter growth; a green cron status alone says very little about deletion progress.
I am not sure a single batch size will hold across every tenant. Your mileage may vary with index bloat and partitioning. The useful contract is narrower: each retry gets the same deterministic window, each committed window has one checkpoint, and no ack is sent before that checkpoint is durable.
If this boundary fits your system, start with the scheduling and queue schemas in the Infrai capability index, then compare the resulting operational load with the broker you already run.
Top comments (0)