How to Choose a Cron Setup for Periodic Database Cleanup in a Node.js Backend

The best cron setup for periodic database cleanup in a Node.js backend is a replayable nightly trigger backed by a database lease and an idempotency key. The important trade-off is not whether the scheduler can fire at 02:00. It is whether a retry can repeat the work without creating a second financial effect. I use a durable run record, a provider transaction key, and a database uniqueness constraint to make that decision explicit.

The scheduler is only the alarm clock.

This distinction matters in a marketplace. The job compares the platform's order and payout ledger with a payment provider's records, marks discrepancies for review, and then cleans up temporary reconciliation rows after they are settled. A process restart, a provider timeout, or two application instances waking up together must leave the same ledger state as one successful attempt.

The failure sequence is easy to miss because each individual step looks reasonable. Worker A asks for the provider's transactions, receives a page, and inserts the first observation. The database commit succeeds. Then the worker loses its connection before it marks the run complete. The next nightly trigger sees an unfinished run, or a recovery process sees the same window, and asks for that page again. Worker B now has the same provider transaction in hand. A preflight SELECT followed by an INSERT is unsafe here because A and B can both perform the read before either insert commits. A uniqueness constraint makes the second insert a known duplicate, while the run record preserves the fact that recovery happened. This is why the retry boundary belongs around the read and the idempotent observation, not around an unguarded financial mutation.

The duplicate is expected.

The ambiguity is the thing to design away.

Should a Node.js cron setup run periodic database cleanup nightly?

For a Node.js backend, keep the cron expression small and the actual work in an ordinary application function. Acquire a database-backed lease, create a run id, fetch a bounded time window, and write every externally observed transaction behind an idempotency key. Retry network reads and transient database errors with a limit; do not retry a non-idempotent write unless the write is guarded by a unique key.

A useful starting schedule is one nightly trigger with a lookback window larger than one calendar day. The exact window depends on provider settlement and clock behavior, so it belongs in configuration and tests, not in an unexplained magic number. A run should be safe to repeat for the same date range. That property is more valuable than shaving a few minutes from the schedule.

The following small example shows the decision boundary. It uses Python because the core rule is independent of a JavaScript framework: a provider transaction may be seen many times, but it may be applied once.

from dataclasses import dataclass from datetime import datetime, timezone from typing import Protocol @dataclass(frozen=True) class ProviderTransaction: provider_id: str order_id: str amount_cents: int observed_at: datetime class ReconciliationStore(Protocol): def record_observation(self, transaction: ProviderTransaction, run_id: str) -> bool: """Return True only when this provider id was new to the ledger.""" ... def mark_run_complete(self, run_id: str, finished_at: datetime) -> None: ... class Provider(Protocol): def list_transactions(self, start: datetime, end: datetime) -> list[ProviderTransaction]: ... def reconcile_window( provider: Provider, store: ReconciliationStore, run_id: str, start: datetime, end: datetime, ) -> int: applied = 0 for transaction in provider.list_transactions(start, end): if store.record_observation(transaction, run_id): applied += 1 store.mark_run_complete(run_id, datetime.now(timezone.utc)) return applied

The store implementation must enforce uniqueness on provider_id in the same transaction that records the observation. Checking first and inserting later is a race: two workers can both observe the missing row. In PostgreSQL, an equivalent constraint is UNIQUE (provider_id); in another database, use its transactional uniqueness primitive. The application can treat a duplicate-key result as a successful no-op, while a malformed amount or an unknown order remains a visible exception.

What should happen when the nightly run is interrupted?

An interrupted run should be boring to resume. Persist run_id, the requested start and end timestamps, an attempt count, a status, and the last recoverable error. Do not infer completion from the process exit code. A container can disappear after the provider accepted a request and before the application received its response.

The run lease needs an expiry. A forever-held lock turns one incident into a permanent outage; a lock with no ownership token lets an old worker release a newer worker's lease. Store a random lease token, renew it while processing, and require that token when completing or releasing the lease. The token is a capability, not a user-facing secret.

A practical state flow is queued -> running -> succeeded, with running -> retryable for a timeout and running -> needs_review for data that the code cannot safely interpret. A retryable run gets another attempt after a bounded delay. A needs-review run does not get an automatic loop. That is the line between recovery and repeated damage.

Here is a compact retry policy. It retries only errors the caller has classified as temporary, and it adds jitter so several workers do not wake together. The policy does not decide whether a financial mutation is safe; the idempotency constraint does.

import random import time from collections.abc import Callable class TemporaryProviderError(Exception): pass def with_retries(operation: Callable[[], object], attempts: int = 4) -> object: for attempt in range(attempts): try: return operation() except TemporaryProviderError: if attempt == attempts - 1: raise delay_seconds = min(60, 2 ** attempt) + random.uniform(0, 0.5) time.sleep(delay_seconds) raise RuntimeError("unreachable")

Four attempts is an example policy, not a universal truth. Set the limit from the provider's timeout behavior, the reconciliation window, and the time available before the next run. A retry that continues past the next scheduled run is not automatically wrong, but it must contend through the same lease and run records.

I started with a shorter retry limit because it made the dashboard look tidy. That was the wrong optimization: a tidy dashboard is less useful than a run that records why it stopped. The durable choice is a little more state and a little less hidden behavior. Four attempts, a 60-second cap on the exponential delay, and a 24-hour window are policy inputs to test, not promises to copy unchanged.

How do cleanup and reconciliation share one database safely?

Separate the financial fact from its temporary processing state. Keep the provider transaction and the internal ledger observation as durable records. Put transient pages, cursors, and raw response fragments in a table with a retention policy. Cleanup can delete an expired cursor only after the run that owns it is terminal. It should never delete the evidence needed to explain a mismatch.

For example, a cleanup query can select rows by expires_at, but the deletion must be bounded and repeatable. Process a few hundred rows per transaction, commit, then continue. A single giant delete competes with reconciliation for locks and makes recovery harder. The batch size is an operational knob, not a correctness guarantee.

Do not use database time and provider time interchangeably. Normalize stored timestamps to UTC, record the provider's reported timestamp, and define the window as half-open: [start, end). That avoids counting a transaction exactly at midnight twice. The job may still need a lookback because settlement data can arrive late; duplicate observations are acceptable when the unique key turns them into no-ops.

I initially treated cleanup as housekeeping that could run independently of the reconciliation code. The awkward result was that a failed run lost the cursor needed to resume. The correction was to make retention depend on run state, then add a repair path that can rebuild a cursor from the bounded source window. More rows remain for a little longer. That is a good trade for an auditable ledger.

Keep the evidence.

A small evaluation harness catches the expensive mistakes

Before connecting a scheduler, test the function as if time were hostile. Run the same window twice. Deliver the same provider page twice. Fail after the observation is accepted but before the run is marked complete. Start two workers with the same schedule. Move a transaction onto the exclusive end boundary. The expected ledger count should stay stable.

def test_replay_does_not_duplicate_observations(fake_provider, store): start = datetime(2026, 10, 24, tzinfo=timezone.utc) end = datetime(2026, 10, 25, tzinfo=timezone.utc) first = reconcile_window(fake_provider, store, "run-a", start, end) second = reconcile_window(fake_provider, store, "run-b", start, end) assert first == 3 assert second == 0 assert store.observation_count() == 3

The useful assertion is not only that the second call returns zero. Check the durable state: one observation per provider transaction, one settled amount per order, and a run history that explains both attempts. This is where an eval-driven workflow pays off. A scheduler test that only asserts a callback was invoked can pass while the money path is still unsafe.

Track a few signals: scheduled time versus start time, run duration, rows observed, rows newly applied, duplicate observations, retry count, cleanup batch count, and unresolved discrepancies. Alert on a run that is late or stuck, not merely on an empty result. A quiet marketplace may legitimately produce zero new transactions.

The same measurements also keep token and compute costs under control if an AI agent summarizes discrepancies. Send the agent structured, redacted mismatch records, not an entire payment response. Cap the number of records per summary and preserve the source record id so a human can inspect the original data. The model can classify or draft a note; it should not decide to retry a charge.

Choosing the scheduler boundary

If the backend has one durable instance and a database lease, an in-process cron trigger can be adequate for a nightly reconciliation. In a horizontally scaled service, every instance may wake up, so the lease becomes mandatory. A managed scheduler or a platform timer can reduce process-local timing concerns, but it still cannot provide idempotency for a provider mutation. The boundary changes; the data contract does not.

Use a queue when one run can produce work that must be distributed, independently retried, or rate-limited. Use a database lease when the work is a bounded batch and the database is already the system of record. Avoid choosing a scheduler by its expression syntax. Ask where the trigger history lives, how ownership expires, how a delayed run is recovered, and how duplicate delivery is made harmless.

The operational checklist is short enough to keep beside the runbook: every run has a durable id and bounded window; ownership has an expiring token; observations have a provider-level uniqueness key; retries are limited to classified temporary failures; cleanup is batched and retention-aware; timestamps use UTC and a half-open interval; dashboards distinguish duplicates from new records; and a replay test runs in CI. If any answer is vague, the cron expression is premature.