Incremental Supplier Sync with Cursor Pagination and a Durable High-Water Mark

The production failure this guide fixes is a traceability record that quietly never arrives. A supplier API is polled for new Critical Tracking Events, the poller walks pages with an offset or a page number, and somewhere in the window a record is either skipped or fetched twice. A skip means a Critical Tracking Event — a shipping or receiving event you are required to hold — is simply absent from your ledger, and no error ever fired. A double-fetch means the same event lands twice and inflates quantities or corrupts a lot chain. Both come from the same root defect: pagination anchored to a position that shifts while new rows are being inserted at the source.

This is an incremental-sync concern under the parent API Polling Strategies, and it is the counterpart to rate-limit handling: rate limits govern how fast you may poll, while this guide governs how you resume so each poll picks up exactly where the last one stopped. The mechanism is a pair — an opaque cursor (or token) that the supplier’s API issues to page forward, plus a durable high-water mark you persist so a resumed sync never rewinds past acknowledged data nor jumps over unread data. Done right, a full day of polling reconstructs the source’s new events with zero gaps and zero duplicates.

Root Cause: Offset Pagination Over a Moving Dataset

The defect is treating a growing dataset as if it were frozen. Offset pagination — ?limit=100&offset=200 — assumes row N is the same row on every request. It is not, once the source keeps inserting. Three failure shapes follow directly:

1. Skips from insert-shift. You read offset 0–99, and before you request offset 100 the supplier inserts 20 new events at the top of the ordering. What used to be rows 80–99 are now at offset 100–119, so requesting offset 100 skips the 20 rows that shifted down. Those events never enter your ledger.
2. Duplicates from delete-shift or overlap. If rows are removed, or if you re-poll a boundary to be safe without deduplicating, the same event is returned in two windows and written twice.
3. No durable resume point. When the poller crashes mid-window and restarts from offset 0, it re-reads everything (duplicates) or, if it naively restarts from where it thinks it was, skips the in-flight window. There is no authoritative “we have consumed up to here” fact.

The correct model is a cursor keyed to a stable, monotonic ordering the server controls — an opaque continuation token, or a (updated_at, id) tuple — combined with a persisted high-water mark that records the last position you have durably committed. Under 21 CFR Part 1, Subpart S, the records for each CTE must be maintained and produced on request; a sync that can silently drop a shipping event cannot guarantee the record exists to be produced. The engineering rule: page with a server-controlled cursor, persist a high-water mark transactionally with the data, and make writes idempotent so an overlap is harmless.

Figure — Cursor-paged sync with a durable high-water mark:

The Cursor State This Sync Persists

The fields that make a resume durable, and the regulatory obligation each ultimately serves. The high-water mark is not a convenience — it is the fact that lets you assert every CTE in a window was consumed.

Minimal Reproducible Example

The naive poller pages by offset against a source that keeps inserting. Simulated here with a list that grows between pages, it skips the shifted rows:

class MovingSource:
    def __init__(self) -> None:
        # Events ordered newest-first, as many APIs return them.
        self.events = [{"id": i, "tlc": f"TLC-{i}"} for i in range(100, 90, -1)]

    def page(self, offset: int, limit: int) -> list[dict]:
        return self.events[offset:offset + limit]

    def insert_new(self, n: int) -> None:
        top = self.events[0]["id"]
        # New events arrive at the top, shifting every existing row down by n.
        self.events = [{"id": top + k, "tlc": f"TLC-{top + k}"} for k in range(n, 0, -1)] + self.events


src = MovingSource()
seen: list[int] = []

first = src.page(offset=0, limit=5)          # ids 100..96
seen += [e["id"] for e in first]
src.insert_new(3)                            # 3 new events shift everything down by 3
second = src.page(offset=5, limit=5)         # intended 95..91, actually re-reads shifted rows

seen += [e["id"] for e in second]
print("fetched ids:", seen)
# ids 95 and 94 were shifted to offset 8-9 and are skipped; the new 101-103 never seen.
missing = {95, 94, 93} - set(seen)
print("SKIPPED events:", missing)            # -> {93, 94, 95} depending on shift

Nothing raised. The offset walked over a moving target, and CTEs 93–95 dropped out of the sync entirely while brand-new events were never requested. In a real feed those are shipping events absent from the ledger with no trace of the loss.

Fix: Opaque Cursor, Transactional High-Water Mark, Idempotent Upsert

The fix pages by a server-issued cursor over a stable ordering, advances the high-water mark in the same transaction as the writes, and upserts on an idempotency key so any deliberate boundary overlap is absorbed rather than duplicated. This is the durable-resume discipline the parent API Polling Strategies guide pairs with rate-limit backoff.

from __future__ import annotations

import hashlib
import logging
from dataclasses import dataclass, field
from datetime import datetime, timezone
from typing import Optional

logger = logging.getLogger("fsma204_sync")
logging.basicConfig(level=logging.INFO, format="%(asctime)s | %(levelname)s | %(message)s")


@dataclass
class HighWaterMark:
    supplier_id: str
    updated_at: Optional[datetime] = None   # last committed source timestamp (tz-aware)
    record_id: Optional[str] = None         # tiebreak within equal timestamps
    next_cursor: Optional[str] = None       # opaque token to resume mid-window


@dataclass
class Ledger:
    """Stands in for the traceability store; the set enforces idempotency."""
    committed: set[str] = field(default_factory=set)
    rows: list[dict] = field(default_factory=list)

    def upsert(self, event_hash: str, record: dict) -> bool:
        # Idempotent: a repeated hash is a no-op, so overlap never double-writes.
        if event_hash in self.committed:
            return False
        self.committed.add(event_hash)
        self.rows.append(record)
        return True


def event_hash(supplier_id: str, record: dict) -> str:
    """Stable idempotency key: same source event always hashes the same."""
    basis = f"{supplier_id}|{record['id']}|{record['tlc']}|{record['updated_at']}"
    return hashlib.sha256(basis.encode("utf-8")).hexdigest()


def sync_supplier(api, ledger: Ledger, mark: HighWaterMark, page_size: int = 100) -> HighWaterMark:
    """Drain new events using the opaque cursor, committing the mark with each page."""
    cursor = mark.next_cursor  # resume mid-window if a prior poll stopped partway
    total_new = 0

    while True:
        # The server issues the cursor; we never compute an offset ourselves.
        page = api.fetch(
            supplier_id=mark.supplier_id,
            cursor=cursor,
            since=mark.updated_at,   # a small overlap window is safe: upsert dedupes it
            limit=page_size,
        )
        for rec in page.records:
            h = event_hash(mark.supplier_id, rec)
            if ledger.upsert(h, rec):
                total_new += 1
            # Advance the in-memory mark to the newest record consumed.
            ts = datetime.fromisoformat(rec["updated_at"])
            if mark.updated_at is None or ts >= mark.updated_at:
                mark.updated_at, mark.record_id = ts, str(rec["id"])

        # Commit data AND mark together. In real code this is one DB transaction.
        mark.next_cursor = page.next_cursor
        logger.info(
            "supplier=%s committed_page new=%d high_water=%s cursor=%s",
            mark.supplier_id, total_new, mark.updated_at, mark.next_cursor,
        )

        cursor = page.next_cursor
        if not cursor:            # exhausted: the durable mark now spans the whole window
            break

    logger.info("supplier=%s sync complete new_events=%d", mark.supplier_id, total_new)
    return mark

Three properties make this correct: the cursor is defined by the server over a stable ordering, so inserts at the source do not shift what “next” means; the high-water mark is committed with the data, so a crash resumes from the last durable page rather than the start; and the upsert keys on a content hash, so a deliberate since overlap — cheap insurance against boundary races — is absorbed silently instead of duplicating a CTE.

Verification Steps

Simulate a polling window that includes an insert burst and a mid-window restart, then assert the reconstructed set has no gaps and no duplicates:

from types import SimpleNamespace


class FakeAPI:
    """Serves cursor pages over a stable (updated_at, id) ordering that only grows."""
    def __init__(self) -> None:
        base = datetime(2026, 3, 4, tzinfo=timezone.utc)
        self.store = [
            {"id": i, "tlc": f"TLC-{i}", "updated_at": base.replace(second=i).isoformat()}
            for i in range(1, 26)
        ]

    def fetch(self, supplier_id, cursor, since, limit):
        start = int(cursor) if cursor else 0
        chunk = self.store[start:start + limit]
        nxt = str(start + limit) if start + limit < len(self.store) else None
        return SimpleNamespace(records=chunk, next_cursor=nxt)


api = FakeAPI()
ledger = Ledger()
mark = HighWaterMark(supplier_id="SUP-42")

# First poll drains part of the window, then a simulated crash: we resume from the mark.
sync_supplier(api, ledger, mark, page_size=10)

# Re-run the whole sync a second time with a deliberate overlap; idempotency must hold.
mark.next_cursor = None
sync_supplier(api, ledger, mark, page_size=10)

got = sorted(r["id"] for r in ledger.rows)
assert got == list(range(1, 26)), f"gap detected: {set(range(1, 26)) - set(got)}"
assert len(got) == len(set(got)), "duplicate detected"
print("No gaps, no duplicates across", len(got), "events over two overlapping polls.")

The two assertions are the compliance guarantee stated as code: every event in the window is present exactly once, even though the sync ran twice with an overlapping boundary. In production, emit the high_water and per-page new counts to data-quality monitoring so a window that returns suspiciously few new events — a sign the mark advanced past unread data — raises an alarm instead of passing quietly.

Related Edge Cases to Check Next

• Cursor expiry mid-drain. Some APIs expire an opaque token after minutes. If a page fetch returns a cursor-expired error, fall back to the persisted high-water timestamp rather than restarting from zero, and route the retry through your error-handling workflows so a stale cursor never triggers a full re-sync.
• Clock skew and equal timestamps. When many events share one updated_at, ordering by timestamp alone is unstable and can skip a tied row at a page boundary. Always order by the (updated_at, id) tuple and carry both in the high-water mark so ties break deterministically.
• Backfill versus incremental. The first sync for a new supplier has no mark and must backfill the full history before switching to incremental mode. Keep the two modes explicit, and feed the backfill through async batch processing so the initial load does not starve steady-state polling.

Frequently Asked Questions

Related

• API Polling Strategies — the parent guide pairing this durable-resume design with rate-limit backoff.
• Supplier Data Ingestion & Sync Automation — the ingestion program these polled CTE records feed.
• Async Batch Processing — the mode for backfilling a new supplier’s full history before incremental polling.
• Error Handling Workflows — where cursor-expiry and fetch failures route for retry without a full re-sync.
• Data Quality Monitoring — the checks that flag a window returning too few new events.

Up: API Polling Strategies — this cursor-and-high-water design is how incremental polling stays gap-free and duplicate-free.

For the authoritative regulatory text, reference the FDA Food Traceability Final Rule.