API Pagination Is Lying to Your n8n Workflow — Build a Checkpointed Sync Engine

byHossein Hezami •
0

Here's a lie almost every paginated API tells, and most n8n workflows believe without question: "here are all the records from page 7."

They aren't. They're "here are the records that happened to be in slot 70 through 79 of whatever order the database felt like using at the instant you asked." For a dashboard, that's fine. For a sync that's supposed to produce a faithful copy of a dataset, it's a quiet disaster — rows that appear twice, rows that vanish entirely, and a workflow that stays green the whole time because every HTTP call returned 200 OK.

I want to walk through why pagination lies, which lies matter, and how to build what I'd call a checkpointed sync engine in n8n: a fetch loop that records its position durably, survives crashes mid-stream, resumes without re-reading everything, and proves at the end of every run that it didn't lose anything. If you've built a "HTTP Request node with pagination turned on, pipe into a database" workflow and had the uneasy feeling that nobody could tell you whether it was correct — this is that feeling, formalized.

TL;DR

  • Offset and page-based pagination are unstable under concurrent writes: rows shift between pages, causing duplicates and gaps. Cursor and keyset pagination are stable, but only if the API implemented them honestly.
  • Page count, total, and has_more fields are advisory, not contractual. Your termination condition must be derived from the data you actually receive.
  • n8n's built-in HTTP Request pagination handles the happy path well — but it's all-or-nothing per execution: no checkpoint, no resume, no mid-stream crash recovery.
  • The fix is a loop you own: fetch one batch → process it → write a durable checkpoint → repeat. At-least-once delivery plus idempotent writes equals a sync you can trust.
  • Every run ends with a watermark reconciliation: expected vs. received, gaps checked, drift alerted.

📋 Table of Contents

1. The Lie: What "Page 7" Actually Means

Scenario: You sync a ticketing system's GET /tickets?offset=70&limit=10. Page 7 gives you tickets 70–79. While you're fetching page 8, someone deletes ticket 71 upstream. Ticket 72 slides down into the 70-slot, your page 8 request starting at offset 80 now starts where ticket 79 slid to 78… you get a duplicate of ticket 79, and ticket 80 — which nobody deleted, nobody touched — simply never appears in your sync. No error. No warning. Just a missing ticket forever.

Why it matters: This is the classic offset instability problem, and it's fundamental to how offset pagination works: the API re-evaluates the query from scratch on every request. Between any two page requests, the dataset is free to move underneath you. The three failure shapes are:

  • Duplicates: a item slides back across the page boundary and appears on two consecutive pages.
  • Gaps: an item slides forward, skipping your read window entirely.
  • Nontermination: pages keep returning overlapping data, or a total count that grows as you read, forever.

The really nasty part is the failure mode's visibility. A crash is loud. This is silent. Your workflow has no way to know ticket 80 existed, because the API never told it — the lie is an omission, not a response you can inspect.

Solution — well, the beginning of one: the first question to ask about any API you're about to sync isn't "does it paginate?" It's "is its pagination stable under concurrent writes?" And the test is embarrassingly simple: query page 1 twice with a modification in between, and query the last page with a fresh insert in between. If ordering follows a mutable column (updated-at, sort order, "relevance"), or the API can't tell you what its sort key even is, assume it lies.

🧠 The important part: most people debug pagination as a network problem — retries, rate limits, timeouts. But the dominant failure class isn't transport; it's semantics. The API answered every request correctly. The answers just didn't compose into a correct dataset view.

2. Four Pagination Styles, Ranked by How Much They Lie

Not all pagination lies equally. It's worth being able to name what you're looking at, because the engine you need to build differs dramatically by style.

StyleLooks likeStable under writes?The specific lie it tells
Offset/limit?offset=70&limit=10❌ No"Slot 70–79" re-computed every request; rows slide between pages
Page numbers?page=7❌ NoSame instability, plus page-size changes shift every boundary
Opaque cursor?cursor=eyJ... or next_url⚠️ UsuallyHonest if the cursor encodes a snapshot or a keyset position; a lie if it's just an offset in a trench coat
Keyset / seek?updated_after=... or after_id=123✅ YesOnly honest if the sort key is monotonic and immutable (see below)

The important distinction is what the "position" actually references.

Offset pagination positions you at a slot in the current ordering. Move the ordering, move the answer.

Cursor pagination positions you at a point in the underlying data — usually a primary key value, a timestamp, or an opaque encoding of one. GitHub's Link: <...>; rel="next" headers, Stripe's starting_after, Slack's next_cursor — these all (in the well-implemented cases) encode a keyset position, which is why they're the recommended pattern in most modern API design guidance. The server can't slide a record "before" your cursor if your cursor says "everything with ID ≤ this point, in order."

Keyset pagination is the same idea without the opacity: you provide the boundary yourself (after_id=1042, updated_since=2026-10-22T09:00:00Z).

But here's the gotcha that bites even with well-intentioned APIs:

⚠️ Gotcha: keyset pagination is only stable if the sort key is immutable. Pagination on updated_at is stable for reads but creates a different problem: a row updated after your cursor position slides backward out of your unread window and you miss the new version. Pagination on a mutable sort_order column is the worst of both worlds. The gold standard is a strictly monotonic, append-only key (auto-increment ID, or a high-resolution created timestamp with an ID tiebreaker) — and very few APIs offer that publicly.

If you control the API, expose keyset pagination on an immutable key. If you don't, you build the checkpoint engine in the rest of this article, plus the dedup layer that makes the lies survivable.

One more nuance worth internalizing: cursors that encode an offset are common and undetectable from the outside. A cursor=eyJvZmZzZXQiOjcwfQ is just base64 for {"offset":70}. Decode the cursor before you trust it. If it decodes to an offset, you're in Section 1's world regardless of what the docs claim.

3. The Response Body Is a Contract, Not a Suggestion

Scenario: Your sync uses total_pages from the first response to decide how many pages to fetch. Two minutes into the run, the dataset grows by 40 rows. total_pages from page 1 said 500; the real page count is now 501. Your loop stops at 500 and the last 40 records silently don't sync. Alternatively: an API returns "has_more": true even when the last page is a duplicate of the previous one, and your loop runs until the rate limiter intervenes.

Why it matters: Developers treat response metadata — total, total_pages, has_more, count — as ground truth. They're hints. They're computed at a moment that is not your moment, often under different query semantics, and sometimes just wrong (count estimates are famously approximate in several large APIs, where total is a budget-conscious estimate rather than an exact count).

Solution: derive everything from what you actually receive.

  • Termination: stop when a page returns zero items, not when a counter says you're done. The one thing the API can't lie about (to a first approximation) is the array it just handed you.
  • Progress: track your own position from the data itself — the max ID seen, the latest timestamp, the opaque cursor string. Never trust that page n of the request corresponds to anything you expected.
  • Sanity: if a page returns fewer items than the limit but isn't empty, that's a soft signal of the end — but it's also what happens when a filter, permission boundary, or page-size cap silently truncated the response. Treat it as "probably last, verify next request returns zero," not as "definitely last."
  • Re-request the metadata never. Every page request re-derives its own view of the world. The first page's total describes the first page's moment, and by page 300 that moment is ancient history.

There's one more contract clause worth writing down explicitly: empty vs. exhausted. Some APIs return an empty data array with a valid next cursor (a quirk of their internal batching), and some return next=null while data still exists on a re-query. If you don't know which behavior you have, write both checks into your loop: continue when next is present AND stop when data is empty — with a hard cap and an alert if the two signals disagree more than once. Disagreement between the cursor and the data is a symptom of a lying API, and it's the kind of thing you want surfaced, not absorbed.

🔍 Why this matters: the sync engine's termination logic is where correctness lives or dies. Everything else in the workflow can be retried, checkpointed, or deduped; a termination condition that stops too early is a silent permanent loss of data.

4. What n8n's Built-In Pagination Gets Right — and Where It Stops

Before building anything custom, know what n8n gives you out of the box, because it's more than people expect — and the gap between it and "trustworthy sync" is smaller and more specific than you might think.

The HTTP Request node has first-class pagination under Options → Pagination. You pick how the loop advances — Response Contains Next URL (the node follows a next link from each response), Update a Parameter in Each Request (the node rewrites a query parameter per iteration, with expressions referencing $pageCount), or Update a Body Parameter in Each Request for POST-based pagination — and you define when the loop is complete (e.g., when the response is empty, or via a completion expression you write yourself). The node then fetches all pages internally and emits every item out of a single node execution.

What this gets right, and I want to be generous here because it's genuinely good engineering:

  • One node, one retry policy, one auth context. Retries and backoff on the HTTP node cover the whole traversal, not page-by-page manual plumbing.
  • Rate limiting is handled through the node's built-in batching delay options, rather than you hand-rolling sleep logic between pages.
  • Termination is expressive. The completion expression lets you encode "stop when the data array is empty" or "stop when this field says done" without code.

Where it stops — and this is the entire rest of the article:

  1. It's atomic per execution. The node fetches all pages or the execution fails. There's no "resume from page 214 of last night's run." A 40-minute traversal that dies at 80% restarts at 0%.
  2. There's no checkpoint. The node holds the traversal state in memory for the duration of the execution. Kill the instance, redeploy the workflow, hit the timeout — the position is gone.
  3. There's no cross-run dedup. If the traversal gets partially re-run (manually, by an error workflow, by an overlapping schedule), every page is re-fetched as if it were new.
  4. Memory and execution history take the full hit. A 200k-record traversal produces a 200k-item execution, which lands in your execution history in full unless you've planned for pruning.

So the honest framing isn't "the built-in pagination is bad." It's: the built-in pagination is a great fetcher and a nonexistent engine. It has no opinions about durability, resumability, or verification — which is fine for "get me all the search results for this dashboard" and inadequate for "keep this table in sync faithfully, forever."

The line I'd draw: if your API returns data in under a few hundred thousand items, tolerates an occasional full re-fetch, and your downstream writes are idempotent — use the built-in pagination and spend your effort on Section 7's dedup layer. The moment a full re-fetch is expensive (rate limits, cost, hours), or partial runs would be wasteful, or you need a provable watermark, you need the loop you own.

5. The Checkpoint Contract: What You Must Write Down Between Batches

Scenario: Your nightly sync dies at 3:40 a.m. on page 214 of 480 — the upstream API had a bad ten minutes. The on-call engineer re-executes the workflow. It starts from page 1. By the time it reaches page 214 again, the window of records the API was having trouble with has passed, and it completes — having spent four hours re-reading 213 pages it already processed correctly. Or worse, it doesn't get re-executed at all until someone notices at noon.

Why it matters: "Retry from the top" isn't just slow, it's wasteful in the way that compounds: with rate limits, a full re-read can take longer than the window between scheduled runs, which means your next run starts mid-traversal of the previous one. (We'll handle concurrency too, but the cheapest fix is not needing to re-run.)

Solution: define a checkpoint — and make it the smallest unit of resumable truth.

A checkpoint isn't "page 214." A page number is a transport detail tied to offset semantics — exactly the thing we established doesn't compose. A checkpoint needs to reference a position in the data, not a position in the fetch:

{
  "syncRunId": "orders-sync-2026-10-23T02:00",
  "cursor": "eyJpZCI6MTA0Mn0",
  "lastSeenKey": 1042,
  "lastSeenTimestamp": "2026-10-20T14:22:03Z",
  "fetchedCount": 8740,
  "lastSuccessfulPageCommit": "2026-10-23T03:31:12Z",
  "status": "in_progress"
}

What each field buys you:

  • cursor / lastSeenKey — the resumable position. On restart, you don't ask for page 214; you ask for "everything after key 1042." With a keyset position, the answer is stable regardless of what moved upstream since.
  • lastSeenTimestamp — your watermark for incremental runs. Once the full backfill completes, subsequent nightly runs may not need to traverse history at all — they fetch only what's newer than this timestamp. The full-traversal engine is a bootstrap tool; the watermark is the steady-state tool.
  • fetchedCount — your drift-detection baseline (Section 9). "I have fetched 8,740 distinct keys through position 1042" is a claim you can audit later against the upstream count.
  • status — because a checkpoint that says in_progress and one that says complete lead the next run to completely different behavior. The number of production incidents caused by conflating those two states is not small.

Where does the checkpoint live? The same rule as any durable state in n8n: outside the execution. Workflow static data ($getWorkflowStaticData('global')) persists between executions and is tempting, but it's tied to the workflow's identity, limited in size, and — the trap — reset by import/export migrations or restored instances. The right home is a real datastore: a Postgres table, Redis, even a file you control. One row per sync job. Read it at the top of every run, update it after every batch.

The checkpoint write must happen after the batch's data has been durably processed (written downstream, deduped), and only then. If you write the checkpoint before committing the data, a crash in between permanently skips a batch. If you commit data but crash before the checkpoint, you re-fetch that batch — which is fine, because re-fetch is cheap and duplicates are handled by the dedup layer. Optimize for never-skipping, tolerate re-reading. That asymmetry is the whole design.

6. The Loop You Own: One Batch, One Checkpoint, Repeat

Now the actual build. The structure in n8n:

Schedule Trigger
  → Load Checkpoint (Code/DB node)
  → Loop Over Items (drives one page fetch per pass)
      → Fetch One Batch (HTTP Request, cursor from checkpoint)
      → Stop Check (Code: empty page? → route out of loop)
      → Process Batch (normalize → dedup → write downstream)
      → Commit Checkpoint (write cursor/count/status)
      → back to Loop
  → Finalize (mark complete, reconcile, report)

The trick is that the Loop Over Items node (formerly "Split In Batches") is being used somewhat against its stated purpose: instead of splitting a data set, you feed it a single dummy item and let it drive iterations of a side-effect loop, with each iteration's fetch parameters coming from the checkpoint you just wrote. This is the standard n8n idiom for "do something N times where N is discovered as you go."

Key pieces, in Code nodes:

// Node: "Fetch One Batch" parameters (HTTP Request node)
// URL:   https://api.example.com/orders?limit=100&after_id={{ $('Checkpoint').item.json.lastSeenKey }}
// The after_id comes from the checkpoint committed last iteration —
// NOT from $pageCount, which would reintroduce offset semantics.

// Node: "Stop Check" — Run Once for All Items
const records = $input.all().filter(i => i.json.id !== undefined);
if (records.length === 0) {
  // Signal loop exit: n8n's Loop Over Items stops when the loop branch
  // produces no items / a "done" route — route to Finalize here.
  return [];
}
return $input.all();

// Node: "Commit Checkpoint" — Run Once for All Items
const batch = $input.all();
const maxKey = Math.max(...batch.map(i => i.json.id));

return [{
  json: {
    ...$('Checkpoint').first().json,
    lastSeenKey: maxKey,
    fetchedCount: $('Checkpoint').first().json.fetchedCount + batch.length,
    lastSuccessfulPageCommit: new Date().toISOString(),
  },
}];

Followed by whatever datastore write persists it (Postgres node, Redis, etc.), then the branch loops back to the Loop Over Items node.

Why this works: every iteration is a self-contained, independently-correct unit. Fetch from a data position → process → durably record the new position. Any iteration can be the last one executed before a crash, and the state left behind is always coherent: "everything before key N is done; N onward is not." The loop doesn't even need to know whether it's resuming a half-finished run or starting fresh — the checkpoint determines the starting cursor either way. The same code path handles backfill and steady-state incremental runs; the only difference is what's in the checkpoint when the loop begins.

⚠️ Gotcha on ordering: the Math.max(...ids) assumes the API returns the batch in ascending key order — which you should assert, not assume. If the API returns unordered pages, sort the batch before taking the max, or better, add a check that fires an alert when min(batch) <= lastSeenKey, because that's the signature of a lying cursor and it means your checkpoint would be moving backward.

One deliberate design choice worth defending: I'm not batching the writes downstream per-page beyond what the loop naturally does. The loop's granularity is your write granularity. If your downstream API rate-limits, add a small Loop Over Items inside the process step — but keep the outer loop's unit "one fetch batch," because mixing fetch and write batching in one loop makes the checkpoint semantics mushy.

Practical note on the sub-workflow option: when this loop gets complex — and it will — the Process Batch step is a good candidate for an Execute Workflow node calling a sub-workflow. The sub-workflow gets one batch, does normalization/dedup/write, and returns a summary. That isolates the messy API-specific logic from the engine, and the engine stays generic enough to copy-paste across sync jobs. It also makes the processing independently testable, which is worth a lot when the workflow is 40 nodes deep.

7. Deduplication on Ingest: Because At-Least-Once Is the Best You Get

The checkpoint engine guarantees at-least-once delivery of every batch: crashes re-read, overlapping runs re-read, and lying cursors re-read. The system is only correct if re-reads are harmless. That's the ingest layer's job, and it has exactly one design rule: identity is a property of the data, not of the fetch.

Scenario: Your crash-resume re-fetches a batch of 100 orders. Two-hundred rows arrive in the target table instead of 100. Now finance has a duplicate-invoice problem that originated in an n8n execution retry nobody ever heard about.

Solution, three layers, in escalating cost:

Layer 1 — a seen-set within the run. During a single execution, track every key you've processed:

// Node: "Dedup Within Run" — Run Once for All Items
// Pull the seen-set from static data scoped to this run, or thread it
// through items; for large runs, use a Set built from a ledger query.
const seen = new Set($('Seen So Far').isExecuted
  ? $('Seen So Far').first().json.seenKeys
  : []);

const fresh = [];
for (const item of $input.all()) {
  if (!item.json.id || seen.has(item.json.id)) continue;
  seen.add(item.json.id);
  fresh.push(item);
}

return [{ json: { seenKeys: [...seen], items: fresh } }];

Use a Set, not array.includes() — a linear scan inside a loop over 100k items is an O(n²) cliff that turns a fast sync into a slow one for no reason.

Layer 2 — downstream idempotency. The seen-set only protects within one execution. The durable guarantee has to be at the write target: a unique constraint on the natural key with ON CONFLICT DO NOTHING/DO UPDATE (Postgres), an upsert-by-key API, or a composite key derived from source + external_id. If the target is a table you own, this is a one-line DDL change and it's the single highest-leverage thing in this entire article. If the target is a third-party API with no upsert semantics and no idempotency keys, you must keep a ledger of already-written keys and check before writing — and accept the residual race window (next section).

Layer 3 — the duplicate as a signal. Here's the opinionated part: don't silence duplicates — log them. If a key arrives twice within a run, that's expected behavior (a re-read overlapping the previous run's tail). But if duplicates arrive at a rate far beyond what resume semantics explain — say, every page is 30% repeats — that's your smoking gun for offset-instability or a lying cursor. Route duplicates to a counter that feeds the report (Section 9), and put a threshold on it. The dedup layer isn't just plumbing; it's your instrumentation for catching the API in the act.

🧠 The important part: at-least-once plus idempotent writes gives you exactly-once as an emergent property. You can't build exactly-once delivery into a workflow that talks to a distributed API — no one can. You build idempotent consumption and let the retries happen freely. This is the same design bet that every serious event pipeline makes, and it's the correct one.

8. Crash, Resume, and the End-of-Page Audit

The checkpoint loop makes recovery cheap — but only if you actually resume rather than restart. And there's one more lying-pagination failure class that checkpoints alone don't cover, so let's handle both.

Crash-resume mechanics. The run starts at Load Checkpoint. The logic there is short but has to be exact:

// Node: "Load Checkpoint" — Run Once for All Items
const cp = $input.first().json; // from your state store

if (cp.status === 'in_progress') {
  // There is an unfinished run. Decision: resume it or abandon it.
  // Resume if the checkpoint is fresh enough that its cursor is still valid.
  // Abandon (restart from zero / from watermark) if the cursor type has
  // a TTL — some opaque cursors expire after minutes to hours.
  const ageMinutes = (Date.now() - new Date(cp.lastSuccessfulPageCommit)) / 60000;
  if (cp.cursorType === 'opaque' && ageMinutes > cp.cursorTtlMinutes) {
    return [{ json: { ...cp, mode: 'restart_from_watermark', resume: false } }];
  }
  return [{ json: { ...cp, mode: 'resume', resume: true } }];
}

// A completed run means: do an incremental pass from the watermark only.
return [{ json: { ...cp, mode: 'incremental', resume: false } }];

The branch nobody plans for is the expired cursor. Opaque cursors frequently encode a query snapshot or session server-side with a TTL — Stripe's list cursors, for instance, are meant to be followed promptly, not resumed the next day. A resume against an expired cursor usually returns an error (good — loud) but occasionally returns different data from an empty snapshot (bad — silent). That's why the checkpoint stores lastSeenKey in addition to the opaque cursor: the opaque cursor is the fast path for a live run; the keyset position is the durable fallback for a resume after any meaningful time gap. Two representations of position, deliberately.

Concurrency guard. A resume shouldn't happen while the previous run is still limping along. Before entering the loop, take a lock — SET sync:orders:lock <runId> NX EX 3600 in Redis, or a Postgres advisory lock, or a running row with a timestamp your Load Checkpoint step treats as stale after a threshold. If the lock is held and fresh, exit immediately with a "skipped: previous run active" report. Cheap, and it removes an entire class of duplicate-heavy failure modes.

The end-of-page audit. When the loop terminates — empty page received — the run isn't done until it verifies. Two checks, both cheap:

  1. Gap check: for keyset syncs on integer IDs, verify that keys are contiguous-ish or at least that your fetchedCount of distinct keys matches what you'd expect from lastSeenKey − startingKey within tolerance (there can be legitimate holes from filtered-out or deleted records, so this is a tolerance, not an equality).
  2. Count reconciliation: compare your checkpoint's fetchedCount against any independent count the API offers (GET /orders/count, a dashboard total). You're not looking for exact equality — counts drift by construction — you're looking for gross disagreement. 2% off is Tuesday; 40% off means your loop lied to you somewhere.

Then, and only then, flip status: complete and stamp the watermark. A watermark that gets written before the audit passes is a watermark that can permanently skip the very records your failed audit was worried about.

🚨 Production warning: the single most common checkpointing bug I'd look for in someone else's workflow is checkpoint-commit-then-process instead of process-then-commit. It feels natural to write state "before the risky step." It is exactly backwards. If you take one thing from this section: commit the checkpoint after the batch is durably downstream, never before.

9. Drift Detection: When Green Executions Are the Scariest Ones

Scenario: The sync has run nightly for three months. Every execution green. Then a stakeholder asks why the analytics dashboard is missing everything from the last week of September. Investigation reveals the API changed its default sort order in a release — silently — and your loop, which trusted "give me whatever's next," spent a week walking in circles over the same 200 records while believing it was progressing.

Why it matters: Checkpointed sync engines fail forward. Unlike a crash, which stops and yells, a semantic drift keeps executing and reporting success. The engine's health therefore can't be measured by "did the executions succeed" — it has to be measured by whether the numbers are self-consistent.

Solution: every run ends with a derived report, and drift alerts fire on the numbers, not the status. Assemble it from Count nodes and a Code node, and send it somewhere humans look (Slack, email) — plus an Error-workflow for hard failures:

// Node: "Build Run Report" — Run Once for All Items
const cp = $('Load Checkpoint').first().json;
const stats = $('Batch Stats').first().json;

const dupRate = stats.received ? stats.duplicates / stats.received : 0;

const alerts = [];
if (dupRate > 0.10) alerts.push(`Duplicate rate ${(dupRate * 100).toFixed(1)}% — cursor instability suspected`);
if (stats.received === 0 && cp.mode === 'backfill') alerts.push('Backfill received zero records — check API sort semantics');
if (stats.gapsFound > 50) alerts.push(`${stats.gapsFound} key gaps — possible skipped records`);

return [{
  json: {
    text: [
      `📦 Sync run ${$execution.id} (${cp.syncRunId})`,
      `Mode: ${cp.mode} | Fetched: ${stats.received} | New: ${stats.new} | Dup: ${stats.duplicates}`,
      `Watermark: ${stats.newWatermark}`,
      alerts.length ? '⚠️ ' + alerts.join(' · ') : '✅ Consistent',
    ].join('\n'),
  },
}];

The thresholds are the interesting engineering. They should be calibrated from normal, not from theory: run the sync for a week, observe the natural duplicate rate your resume semantics produce, and alert at 2–3× that baseline. Alerting on "any duplicate" makes the alert useless within a week; alerting on 40% makes you blind for months.

A few drift signals worth instrumenting specifically, because each one names a distinct failure:

  • Duplicate rate spike → cursor/offset instability (Sections 1–2).
  • Zero-progress runs (fetched > 0, new = 0, watermark unmoved) → the walking-in-circles failure above.
  • Sudden page-count collapse → API silently changed page size or default sort; your run finishes suspiciously fast.
  • Watermark stalls → an upstream process stopped writing the field you watermark on (the classic updated_at-never-updates-on-some-path trap).

That last one deserves a sentence of its own because it's brutal when it bites: if your incremental watermark is updated_at and any write path in the source system updates a record without touching that field (raw SQL updates, some bulk tools, status changes via a queue), those changes are invisible to your sync forever, and everything looks healthy. If you can, watermark on something the source guarantees monotonic — an event log, a changelog feed, an append-only audit table — or accept periodic full audits as the backstop.

💡 Practical note: n8n's execution history is genuinely good forensics — per-node item inspection, execution filtering by status, the works. Treat it as your post-mortem tool and treat the report as your monitoring. If the only way anyone knows the sync is broken is by opening n8n and looking, the sync has no monitoring — it has an in-tray.

10. When This Whole Engine Is Overkill

After nine sections of "here's how to build the engine," the honest caveat: a checkpointed sync engine inside n8n is a specific tool for a specific situation, and there are jobs it's the wrong tool for.

SignalBuild the n8n engineUse something else
VolumeThousands to low hundreds of thousands of recordsMillions of rows, or continuous streams
SourceREST API you don't control, no bulk exportDatabase → database (use CDC: Debezium, Airbyte, Fivetran)
DownstreamA target with upsert semantics or a table you ownA target with no idempotency and no API for it (reconsider the integration)
CadenceNightly or hourly batchesReal-time (use webhooks/events, not traversal)
TeamIntegration/ops engineers own n8n alreadyA data platform team with Airflow/dbt already running
Change scopeFull-table copies of modest datasetsHigh-churn datasets where the diff is the workload

The something-else column is real: if both ends are databases, change data capture doesn't paginate at all — it streams the log — and that eliminates every lie in this article by construction. If a SaaS source offers webhooks for changes, subscribe and process events; traversal becomes a weekly audit rather than the primary mechanism. If a vendor offers a bulk export or snapshot endpoint, prefer it over paginating a mutable list — snapshots can't lie about ordering because they stop time.

And within n8n itself, there's a lighter path worth naming: many native app nodes (Postgres, Airtable, Google Sheets, and the HTTP node's built-in pagination) already handle stable traversal for well-behaved sources. If your source paginates by immutable key, honors cursors, and your runs fit comfortably in one execution — use the built-in machinery, add the dedup layer and the watermark, and skip the loop entirely. The engine exists for the gap between "the API is well-behaved and the dataset is small" and "I need a real data pipeline." Know which side of that gap you're on before you build.

What the n8n version does buy you, even against the heavier alternatives: the execution history makes every run replayable and inspectable at the item level; non-engineers can read the workflow; state lives in one small table you fully understand; and the whole thing is portable enough to copy for the next lying API. For a team whose primary tool is already n8n, the marginal cost of owning this engine is a few evenings, versus standing up and operating an entire data platform for what is fundamentally one sync job.

The Pre-Flight Checklist Before You Trust a Sync

Before I'd let any checkpointed sync engine take responsibility for a dataset, these all need to be true:

  1. You've classified the pagination. Offset, page, cursor, keyset — and if it's a cursor, you've decoded one to check it isn't an offset in disguise.
  2. Termination is derived from received data, not from total, total_pages, or has_more.
  3. Position is a data position (key/timestamp), not a page number; for opaque cursors, you also store the keyset equivalent as a resume fallback.
  4. Checkpoints commit after the batch is durably downstream — never before.
  5. Checkpoints live in an external datastore, not workflow static data.
  6. The target enforces idempotency — a unique constraint, an upsert, or an idempotency key — so at-least-once is safe.
  7. A run lock prevents resume-while-running, with a staleness threshold.
  8. The watermark is only advanced after the end-of-page audit passes, and you've verified the watermark field actually updates on every write path in the source.
  9. Every run reports derived numbers, with drift thresholds calibrated to observed baselines, and an Error workflow catches hard failures.
  10. You've deliberately decided this isn't a CDC/bulk-export problem — because if a snapshot or changelog exists, the honest fix is upstream, not engine-grade resilience downstream.

The unifying idea behind all ten: don't trust the fetch to compose correctly — make each batch independently verifiable, and make the evidence readable. APIs lie about pagination for reasons that are mostly innocent — unstable sort orders, approximate counts, cursors with TTLs, well-meaning metadata computed a moment too early. None of them are malicious. All of them are fatal to a sync that treats "the workflow finished green" as "the data is complete." Checkpoints, idempotent writes, and drift numbers are how you turn that gap into something you can see, resume, and defend — one batch at a time.

Tags:

Post a Comment

Previous Post Next Post