Incremental Sync State Management for LMS Pipelines

A full re-pull of every gradebook, roster, and attendance record on each run is the default that quietly kills institutional pipelines. It is expensive in vendor cost budget, it saturates the rate ceilings that make handling Canvas API rate limits a discipline in the first place, and it turns a five-minute delta into a multi-hour scrape that collides with the morning reporting SLA. The alternative — pulling only what changed since the last successful run — sounds trivial and is not. Incremental sync is only as correct as the state it depends on: a durable record of exactly how far each source has been read, advanced under transactional rules strict enough that a crash never loses an edit and never silently skips one. Get the state model wrong and incremental sync is worse than a full reload, because a full reload is at least self-correcting while a drifting watermark corrupts the warehouse indefinitely.

This page treats sync state as the load-bearing entity it is. It covers the checkpoint model that persists high-water marks, cursors, and updated_since watermarks; the per-platform endpoints and parameters that make delta reads possible; the normalization logic — overlap windows, clock-skew margins, watermark selection — that keeps a delta both complete and idempotent; the FERPA constraint on what the state layer may store; a reference checkpoint-driven pull; and the failure modes that only appear when a fleet runs this against production for a full term.

The invariant the diagram encodes is the one everything else defends: the watermark is advanced last, in the same logical unit of work as the commit or immediately after it, never before. Reverse that ordering and a crash between “advance watermark” and “commit data” leaves a permanent hole in the record. Keep it and the worst a crash can cost you is re-reading a small overlap window, which idempotent upserts render harmless.

The Sync-State Entity Model

Incremental sync state is a first-class table, not a scattered set of files or an environment variable. Model it as a checkpoint entity keyed by the granularity at which you actually resume — almost always (source, entity, scope) — with one row per independently advancing stream. A single institution running Canvas gradebook, Moodle attendance, and Blackboard rosters needs at least three rows, and in practice many more once you shard by term or course-section batch.

Three design choices distinguish a resilient checkpoint table from a brittle one. First, the watermark and the cursor are different things: the watermark is a semantic boundary in the data’s own updated_at domain, while the cursor is a transport-level position inside a single paginated pull. A pull that spans many pages advances the cursor but must not advance the watermark until the entire window has committed — otherwise a mid-pagination crash strands half a window. This is precisely why the pagination strategies for bulk exports contract and the checkpoint contract are separate concerns that meet only at commit time.

Second, status is a lease. Before a worker reads a checkpoint it claims the row (UPDATE ... SET status='running', last_run_started=now() WHERE status != 'running'), so a second worker cannot double-pull the same stream and race the watermark. Without a lease, two schedulers firing a minute apart both read the old watermark and both advance it, and whichever commits second wins — quietly discarding the other’s progress.

Third, the watermark is monotonic by construction. The advance step computes new_watermark = max(old_watermark, max(record.updated_at for record in batch)) and never regresses. A source that returns a record with an updated_at earlier than the current watermark (a late-arriving edit, a clock-skewed node) is still captured — because the overlap window re-read it — but it can never drag the boundary backward.

Delta Endpoints and Request Patterns per Platform

The mechanics of “give me only what changed” differ enough across vendors that a single delta strategy is a fiction. What follows is the behavior that shapes the checkpoint’s watermark and cursor semantics on each platform.

Canvas. Many Canvas endpoints accept a server-side time filter — submissions expose submitted_since and graded_since, and several list endpoints honor an updated_since style parameter — combined with RFC 5988 Link header cursors (rel="next"). The pattern is: send updated_since=<watermark − overlap>, walk Link cursors to exhaustion for that window, then advance. Canvas time filters are inclusive at the boundary, so an overlap of even a few seconds guarantees no edit on the exact boundary second is missed. Because cost accrues per request, a tight delta is also what keeps a stream under the budget described in the rate-limit reference.

Moodle. Moodle’s core_* web-service functions expose timemodified on most records but frequently offer no server-side “modified since” filter, so the delta is computed client-side: fetch the candidate set, keep rows where timemodified > watermark − overlap, discard the rest. This makes Moodle deltas heavier — you pay to transfer rows you then drop — and pushes Moodle schedules toward off-peak windows. The checkpoint still stores a timemodified-domain watermark; only the filtering location moves from server to client.

Blackboard. Blackboard Learn’s REST resources support a modified query parameter on selected endpoints (for example the Gradebook and Users APIs) alongside a paging block that carries offset/cursor state. Send modified=<watermark − overlap> where supported, page through the paging block, and advance on completion. Blackboard’s envelopes are consistent, which makes the updated_at extraction (its modified field) reliably available for the max-watermark computation.

The unifying abstraction is a DeltaSource interface with two methods: pull(since, cursor) -> (records, next_cursor) and updated_at(record) -> datetime. Each platform implements the transport differently, but the checkpoint loop above is written once against the interface. Where a pull is a long server-side job rather than a synchronous read, the loop hands off to async polling for grade syncs and resumes the watermark only when the export job reports complete.

Normalization: Watermarks, Overlap, and Clock Skew

A delta is correct only if it is both complete (no changed record missed) and convergent (re-runs do not multiply rows). Three normalization rules make that true.

Watermark selection. The new watermark is the maximum source updated_at actually committed in the batch, not the wall-clock time the job ran and not the requested since value. Using wall-clock time assumes the source’s clock equals yours and that every record modified before “now” was returned before “now” — both false under replication lag. Anchoring the watermark to observed record timestamps ties the boundary to the data you truly have.

Overlap window. Every pull requests updated_since = watermark − overlap for a small overlap (typically 30–300 seconds). This deliberately re-fetches a sliver of already-seen data so that records written at the previous boundary — or committed to the source’s read replica slightly after the boundary passed — are never stranded on the wrong side of the cut. Overlap trades a little redundant transfer for the elimination of an entire class of silent-gap bug. It is safe precisely because upserts are idempotent: re-reading a row you already have is a no-op.

Clock-skew safety margin. LMS backends are distributed; the node that timestamps a record and the node that answers your query may differ by seconds. A record can therefore carry an updated_at slightly in the future relative to your reader, or become queryable only after a lag. The overlap window absorbs both, but the margin must exceed your observed worst-case skew plus replication lag. The relationship is simple:

$\text{overlap} \;\geq\; \text{skew}_{\max} + \text{lag}_{\text{replica}} + \text{poll\_interval\_jitter}$

Set the overlap from measured behavior — the p99 gap between a record’s updated_at and the time it first appears in a delta pull — not from a guess. Too small and you leak edits; too large and you needlessly re-transfer, though correctness is preserved either way.

Idempotent upsert grain. Convergence comes from writing on a deterministic key. For grade records the natural key is a composite of (source, course_id, assignment_id, canonical_student); the upsert replaces the existing row only when the incoming updated_at (or a version counter) is newer. Handling the duplicates that overlap windows and retries inevitably produce is enough of a topic on its own that it has its own guide: deduplicating incrementally synced grade records. The watermark-persistence half — writing the timestamp durably and in the right order — is covered in tracking last sync timestamps for incremental LMS pulls.

FERPA Constraints on the State Layer

The checkpoint table is metadata, and keeping it metadata is a compliance requirement, not merely good hygiene. Sync state describes how far a stream has been read; it must never become a shadow copy of what was read. Two rules hold the line.

First, state carries tokenized keys only. When a checkpoint is sharded per student or per section, the shard identifier must be a tokenized surrogate — stu_… from a salted hash — never a raw sis_user_id or login_id. The same deterministic tokenization used everywhere else in the pipeline applies here so that a state shard still joins to canonical data through the cross-LMS student id mapping surrogate key:

import hashlib

# Salt loaded from a secrets manager and rotated on schedule — never committed.
def tokenize(raw_id: str, salt: bytes) -> str:
    return "stu_" + hashlib.sha256(salt + raw_id.encode("utf-8")).hexdigest()[:24]

Second, no educational-record payload in the checkpoint. A watermark is a timestamp and a cursor is an opaque transport token; neither is a grade, a name, or an attendance mark. The temptation to cache “the last score we saw” inside the state row to detect changes is exactly the anti-pattern to refuse — it pulls a direct_identifier-adjacent educational_record into a table that lives outside the tokenization boundary and outside the audit columns that govern the canonical warehouse. Change detection belongs in the upsert’s updated_at comparison, not in the checkpoint.

The practical test mirrors the pipeline-wide one: could someone reconstruct a student’s record from the sync-state table alone? If the answer is anything but “no — it holds only timestamps, cursors, and tokenized shard keys,” the state model has overreached.

Reference Implementation: A Checkpoint-Driven Pull

The function below is the loop from the diagram, written against the DeltaSource interface and a transactional checkpoint store. It reads the watermark, subtracts the overlap, pulls the delta, upserts idempotently, and advances the watermark only after the data commit — inside one transaction so a crash rolls back both together. Python 3.10+.

import hashlib
from dataclasses import dataclass
from datetime import datetime, timedelta, timezone
from typing import Protocol

UTC = timezone.utc


@dataclass
class Checkpoint:
    source: str
    entity: str
    scope: str
    watermark: datetime
    overlap: timedelta


class DeltaSource(Protocol):
    def pull(self, since: datetime) -> list[dict]: ...
    def updated_at(self, record: dict) -> datetime: ...


def natural_key(source: str, rec: dict) -> str:
    raw = f"{source}:{rec['course_id']}:{rec['assignment_id']}:{rec['student_token']}"
    return hashlib.sha256(raw.encode()).hexdigest()


def run_incremental_pull(cp: Checkpoint, src: DeltaSource, conn) -> datetime:
    """One checkpoint-driven cycle. Returns the advanced watermark.

    Correctness rests on two invariants:
      * pull from (watermark - overlap) so boundary edits are never stranded;
      * advance the watermark in the SAME transaction as the upsert, last.
    """
    since = cp.watermark - cp.overlap
    records = src.pull(since)                       # delta only, not a full scrape

    if not records:
        return cp.watermark                          # nothing changed; leave state untouched

    # Watermark is the max OBSERVED updated_at, never wall-clock now().
    batch_max = max(src.updated_at(r) for r in records)
    new_watermark = max(cp.watermark, batch_max)     # monotonic: never regress

    with conn:                                        # BEGIN … COMMIT / ROLLBACK
        cur = conn.cursor()
        for rec in records:
            key = natural_key(cp.source, rec)
            # Idempotent upsert: newer updated_at wins, dupes collapse to one row.
            cur.execute(
                """
                INSERT INTO grade_facts (nk, source, course_id, assignment_id,
                                         student_token, score, updated_at)
                VALUES (%s, %s, %s, %s, %s, %s, %s)
                ON CONFLICT (nk) DO UPDATE SET
                    score = EXCLUDED.score,
                    updated_at = EXCLUDED.updated_at
                WHERE EXCLUDED.updated_at > grade_facts.updated_at
                """,
                (key, cp.source, rec["course_id"], rec["assignment_id"],
                 rec["student_token"], rec["score"], src.updated_at(rec)),
            )
        # Advance the watermark LAST, in the same transaction. A crash here
        # rolls back the data too, so the next run safely re-reads the window.
        cur.execute(
            """
            UPDATE sync_checkpoint
               SET watermark = %s, last_run_committed = %s, status = 'idle'
             WHERE source = %s AND entity = %s AND scope = %s
            """,
            (new_watermark, datetime.now(UTC), cp.source, cp.entity, cp.scope),
        )
    return new_watermark

The single most important line is the ON CONFLICT ... WHERE EXCLUDED.updated_at > grade_facts.updated_at clause paired with advancing the watermark inside the same with conn: block. Together they make the whole cycle atomic and convergent: overlap re-reads and retries collapse, and a partial failure self-heals on the next run instead of leaving a gap. When the pull itself is a long export, wrap it in the retry policy from error retry logic for sync jobs so transient failures re-enter the loop rather than aborting the term’s sync.

Failure Modes and Edge Cases

State bugs are the cruelest class because they do not fail loudly — they succeed while quietly losing or duplicating data, and the damage is only visible in a reconciliation weeks later.

Watermark advanced before commit. The canonical data-loss bug: the process persists a new watermark, then crashes before the data lands. The next run reads from the advanced boundary and the un-committed window is skipped forever. The fix is structural, not defensive — advance the watermark in the same transaction as the write, last, as the reference does. Never split them across two connections or two statements outside a transaction.

Clock skew and non-monotonic updated_at. A source node whose clock runs fast stamps a record with a future updated_at; a naive reader advances its watermark past that record and then misses everything the skewed node writes in the interim. Anchor the watermark to observed timestamps, keep it monotonic, and size the overlap above measured worst-case skew. Never trust that source timestamps increase monotonically in the order you receive them.

Missed deletes and tombstones. An updated_since delta returns changed rows; a deleted row simply stops appearing and is invisible to the filter. A student dropped from a section, an assignment removed, a grade retracted — none carry an updated_at you can query. Handle deletes explicitly: either consume the vendor’s tombstone/soft-delete feed where one exists (Canvas surfaces some deletions via event streams), or run a periodic full-key reconciliation that diffs canonical keys against a lightweight roster pull and marks absentees. A delta-only pipeline that never reconciles keys accumulates zombie records indefinitely.

Lost updates from concurrent runs. Two schedulers fire, both read the same watermark, both pull, both advance — and the second commit clobbers the first, discarding a window. The status lease prevents it: claim the checkpoint row before reading it, and refuse to run a stream already running. Add a stale-lease timeout so a worker that died mid-run does not lock the stream permanently.

Overlap too small under replication lag. If the source answers reads from a replica that lags the primary by more than the overlap, records committed just before the boundary are not yet visible when you pull and are stranded once the watermark advances past them. Measure the p99 lag and set overlap above it; when in doubt, widen the overlap — the only cost is redundant, idempotent re-reads.

Cursor and watermark conflated. Advancing the watermark on each page of a multi-page window rather than once at window completion strands the remaining pages if pagination breaks mid-stream. Keep the cursor as in-progress transport state and advance the semantic watermark exactly once, at commit.

The thread through all six is the same as everywhere else in ingestion: make the unsafe ordering impossible by construction, size safety margins from measured behavior, and emit structured state-transition logs — the pattern in logging failed grade syncs with structured JSON — so a stranded watermark is a queryable event, not a silent gap discovered a month later.

Related

• Tracking last sync timestamps for incremental LMS pulls — persisting the watermark durably and in the correct commit order, with a runnable checkpoint store.
• Deduplicating incrementally synced grade records — collapsing the duplicates that overlap windows and retries produce, keeping the newest by version.
• Handling Canvas API rate limits — why a tight delta is also what keeps a stream inside the per-token cost budget.
• Pagination strategies for bulk exports — the cursor contract that meets the checkpoint contract at commit time.
• Error retry logic for sync jobs — the backoff-and-jitter policy that re-enters the loop on transient failure instead of aborting.

Part of: API Ingestion & Sync Workflows