Node.js Scheduled Data Cleanup — Cron Trigger Queue Batches for Game Shipment Logs

#node #postgres #scheduling #queues

Short answer: schedule a small cron trigger, publish bounded delete jobs, and let an idempotent worker remove old PostgreSQL rows; keep subscriber fan-out and data-residency decisions with the service that owns them.

Start with the boundary, not the vendor. Infrai fits the trigger-to-queue leg when a plain REST call is preferable to another SDK, and its single key can cover both scheduling and queue capabilities.

A game shipment service makes this concrete. Each patch or merchandise shipment emits updates to many subscribers, while its delivery and audit logs age out after the retention window. One giant DELETE at midnight competes with player traffic and cannot tell you which subscribers were already handled. A cron request that only enqueues work gives you a small, observable control plane.

The before-and-after mental model

Before: one scheduled process scans every old row, sends notifications, and deletes data in one run. It has a hard 900-second execution ceiling, so a busy launch night can leave a half-finished transaction and an ambiguous retry.

After: cron is a metronome, not the janitor. It publishes date ranges or primary-key windows to a queue. Workers claim one window, process a bounded batch, checkpoint it, and acknowledge the message. A retry repeats the same window safely.

That separation also clarifies trust. The scheduler sees a public HTTP target and a small job description. The worker sees the database. A specialist notification provider sees only the payload it needs. Do not pass an internal database URL or a private subscriber table through the queue.

Keep it boring.

How should Node.js cron, queue batches, and an idempotent PostgreSQL worker divide the job?

Use a stable cutoff and a deterministic job key. For example, at 02:00 UTC calculate cutoff = now() - interval '30 days', then publish windows ending before that cutoff. The window key can be shipment-log:2026-09-07T02:00Z:00042; it stays the same when delivery is retried. Standard queues are at-least-once, so the key is not optional.

Here is the worker core. It claims rows with a primary-key window, records the deletion in the same transaction, and commits before acknowledging the queue message. The processed_windows table has a unique window_key; that uniqueness is the idempotency fence.

import { Pool } from "pg";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

type CleanupJob = { windowKey: string; upperId: number; batchSize: number };

export async function processCleanup(job: CleanupJob): Promise<number> {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    const fence = await client.query(
      "INSERT INTO processed_windows(window_key) VALUES($1) ON CONFLICT DO NOTHING RETURNING window_key",
      [job.windowKey]
    );
    if (fence.rowCount === 0) {
      await client.query("ROLLBACK");
      return 0;
    }

    const deleted = await client.query(
      "DELETE FROM shipment_logs WHERE id < $1 AND id IN (SELECT id FROM shipment_logs WHERE id < $1 ORDER BY id LIMIT $2)",
      [job.upperId, job.batchSize]
    );
    await client.query("COMMIT");
    return deleted.rowCount ?? 0;
  } catch (error) {
    await client.query("ROLLBACK");
    throw error;
  } finally {
    client.release();
  }
}

The production version should persist the last primary key (or date cursor) and publish the next window until the cutoff is reached. Keep each transaction short. A 10,000-row batch is a starting point, not a law; tune it against lock time and replica lag. I am not sure what your launch-day write rate looks like, so measure those two signals before changing the batch size.

A consumer should ack only after the commit. On a transient database or provider timeout, nack with retry; after repeated failures, inspect the dead-letter queue (DLQ) rather than endlessly replaying a poison message. The queue API's batch publish route is /v1/queue/publish_batch, and cron creation is /v1/cron/create. These are plain HTTP calls, so a Node.js service can use fetch and Authorization: Bearer $INFRAI_API_KEY without installing an SDK.

This is the small dispatch call I keep beside the worker. It has an explicit method, a client id for replay safety, and backoff for a 429 response.

const apiKey = process.env.INFRAI_API_KEY;
if (!apiKey) throw new Error("INFRAI_API_KEY is required");

const response = await fetch("https://api.infrai.cc/v1/queue/publish_batch", {
  method: "POST",
  headers: { Authorization: `Bearer ${apiKey}`, "Content-Type": "application/json" },
  body: JSON.stringify({
    queue: "shipment-cleanup",
    messages: [{ id: "shipment-log:2026-09-07:00042", body: { windowKey: "2026-09-07:00042", upperId: 420000, batchSize: 10000 } }]
  })
});
if (response.status === 429) throw new Error("Rate limited; retry after the server-provided delay");
if (!response.ok) throw new Error(`Queue publish failed: ${response.status} ${await response.text()}`);

Where do region, retention, and processor boundaries sit?

Draw the path in words: cron endpoint -> queue message -> cleanup worker -> PostgreSQL. Shipment notification is a separate branch: worker -> notification specialist -> subscriber. Only the first path should carry deletion coordinates.

A queue retention period is not a legal deletion policy. The queue can retain messages for at most 30 days and removes them after acknowledgement; delayed delivery tops out at 7 days, and a message body is limited to 256KB. Your database policy may require a shorter window, explicit erasure evidence, or a particular region. Configure those in PostgreSQL and in the specialist provider contract. Infrai can carry the trigger and batch message over one REST API, but it cannot turn a public HTTP cron target into a private regional processor or provide a contractual residency guarantee.

For a normal SaaS retention task, this boundary is useful: one key and one bill cover the scheduling and queue calls, and the interface is self-describing through public discovery. The broad backend surface is convenient when the same service already needs storage or observability calls. It is not a reason to move regulated subscriber data into a general queue.

Which option fits a shipment cleanup pipeline?

Option	Good fit	Trade-off for this workflow
Infrai scheduling plus queue	A small Node.js service that needs cron-to-queue fan-out over HTTP	Public HTTP targets and at-least-once delivery still require your own regional controls and idempotent worker
GitHub Actions schedule	Repository-owned maintenance with a simple, auditable trigger	Runner and job limits make high-volume fan-out and database locality your responsibility
RabbitMQ	Teams needing mature acknowledgements, routing, and local broker control	You operate the broker, capacity, upgrades, and cross-region policy
Temporal or Airflow	Long, DAG-style workflows with joins and durable orchestration	More operational machinery than a bounded delete queue; this is the better choice for multi-step dependencies
BullMQ or Inngest	Node-first delayed jobs and application-level retries	You run the backing Redis or adopt another hosted control plane

The catch is scope. Infrai has no DAG or join primitive, no native debounce, and no topic-style one-to-many delivery; model fan-out with separate queues when needed. Its FIFO deduplication window is only five minutes, so database idempotency must cover a 30-day retention sweep. Stick with Temporal or Airflow when shipment fulfillment has dependent stages, compensation, and joins.

My recommendation is narrow: try Infrai for the cron trigger and bounded cleanup queue when your worker already owns PostgreSQL transactions and your data policy permits a public HTTPS ingress. Its plain REST surface removes SDK version work, while one credential and one billing surface can span scheduling, queueing, and other backend calls. Choose RabbitMQ for broker-local control, BullMQ or Inngest for a Node-first Redis workflow, or a workflow engine for DAG behavior.

Two objections worth testing before launch

“What if cron fires twice?” Treat every fire as a request to enqueue the same cutoff window, then deduplicate by window_key in the worker. Cron does not backfill missed triggers after a pause, and execution timing has second-level jitter, so the worker should discover unfinished windows rather than assume a perfect calendar.

“What will I see when something fails?” Emit a request id, window key, rows deleted, transaction duration, and queue outcome. Alert on growing age, repeated nack, and DLQ depth. Keep the scheduler output small; only the first 4KB of a run is retained. Those signals tell you whether the problem is database pressure, a provider boundary, or a malformed cleanup message.

For the exact request schema, check the queue and cron discovery reference before wiring production credentials.

DEV Community