The Batch That Reprocessed 300 Rows Because One Row Failed: Checkpointing Batch Workflows in n8n

Description and use

In n8n, a batch without checkpoints retried the entire batch after a single row failure, causing duplicates. The solution is to store each row’s state in a table and atomically claim rows so retries resume from the first queued row.

Problem

A batch workflow without checkpoints retried the entire batch after a single row failure, causing duplicate processing and billing.

Outcome

Implementing per-row state and atomic claims eliminated duplicate processing, ensured accurate billing, and provided clear checkpoints for monitoring.

Workflow

• Create progress table with row id, state, provider reference
• Set initial state to queued for all rows
• Loop: atomically claim one queued row
• Process the row with provider
• On provider confirmation, mark row as done and store reference
• If run dies, next run queries queued rows
• Sweeper reclaims stale claimed rows after timeout
• Checkpoint every 50 rows with summary
• Reconcile counts at end to ensure all rows accounted for

Evidence

The batch job processed 300 rows a night. One night it failed on row 287 — a provider timeout, nothing exotic.

Source

I fixed nothing, reran the workflow, and watched it process all 300 rows again. The 286 rows that had already succeeded were sent a second time.

Source

The fix is to give every row its own state, outside the workflow. Before the batch starts, each row gets a record in a small progress table: row id, state, and the provider reference when it completes.

Source

First, the claim must be atomic. "Read the next queued row, then update it to claimed" is two steps, and two concurrent runs — or a rerun overlapping the tail of the previous run — will both read the same row before either updates it.

Source