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

This is part of my build-in-public series on running LLM workflows in n8n in production. Everything here is a failure I actually watched happen, not a hypothetical.

The batch job processed 300 rows a night. One night it failed on row 287 — a provider timeout, nothing exotic. 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. Customers got duplicates, the provider billed twice for the same work, and the execution log said success, because the second run did succeed. It just succeeded at doing everything twice.

The mistake was treating the batch as one unit of work. A batch workflow with no checkpoint has only two states: not started, and done. There is no "286 of 300 done" in that model, so a retry cannot resume — it can only restart. And a restart of a batch that already had side effects is not a retry. It is a second run of the first run.

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. The states are queued, claimed, done, and review — the same four states I use for deferred jobs, because a batch is really just 300 deferred jobs that happen to start together.

The workflow then works in a loop that never trusts its own memory: claim one row atomically, process it, and mark it done only after the provider confirms. If the run dies at row 287, the next run does not start at row 1. It starts by asking the table what is still queued, and row 287 is the first thing it picks up. The 286 done rows are never touched again, because their state says done and the claim query only returns queued rows.

Two details make this work, and both are easy to get wrong.

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. The claim has to be a single statement that updates and returns in one move, so only one branch ever gets the row. A row that is claimed but never marked done is not lost: a sweeper finds claims older than a timeout and returns them to queued, but only after checking the provider side, because a claimed row might have completed without the mark landing.

Second, mark-done must be tied to the provider's confirmation, not to the workflow reaching the last node. If the provider accepted the work and the workflow died before recording it, the row sits in claimed, the sweeper checks the provider by its reference, finds the effect, and marks it done without reprocessing. If the provider shows nothing, the row goes back to queued and is processed once, for real. The provider's record is the truth; the workflow's execution log is just a story about what it tried.

I also checkpoint in chunks, not just per row. Every 50 rows the workflow writes a summary — rows processed, rows quarantined, rows failed — so a human opening the run can see progress without counting executions. And at the end, the workflow reconciles: input count must equal done plus quarantined plus failed. If the numbers do not add up, the batch does not report success. A batch that silently drops a row is worse than a batch that fails loudly, because nobody goes looking for a success that never happened.

The rule I keep coming back to: a batch without a checkpoint is a bet that nothing will ever fail midway. That bet loses on row 287, and the cost of losing is not the rerun — it is reprocessing everything that already worked.

Free workflows from this series (MIT): https://github.com/zhp910318/n8n-ai-automation-lab

The production templates in my store: https://payhip.com/haopengailab