S3: Persist initial-sync cursor position to SQLite to enable resumable pull after app termination

[ankitDhwani]

Problem

The SDK maintains a Cursor object (in cursor.dart) to track per-DocType pull progress during a sync session. However, this cursor is held in-memory only and is not persisted to SQLite. If the app is terminated mid-pull (OS kill, crash, or user force-quit), all cursor progress is lost.

On the next launch, the SDK has no record of how far the initial sync progressed and must restart the entire pull from page 0.

Impact

• Catastrophic for Initial Sync: The initial sync for a large deployment (e.g., 10,000 master records across 15 DocTypes) can take several minutes. If killed on page 47 of 60, the user must restart from scratch.
• • Repeated Data Re-download: Resuming from page 0 means all previously written rows must be re-fetched, wasting bandwidth in low-connectivity field environments.
• • • No User Feedback: There is no way to tell the user their progress percentage or offer a "resume" option because there is no persisted progress marker.
• • • • Battery & Data drain: Multiple failed/restarted initial syncs on low-end devices with intermittent connectivity is a leading cause of user abandonment.

Root Cause

The Cursor and DoctypeSyncState objects are constructed fresh in memory at the start of each pullSync call. There is no write path that flushes cursor state (last page offset, last OK modified timestamp, hasMore flag) to a durable SQLite table between pages.

Proposed Solution

Durable Sync Checkpoint Table

Add a sync_checkpoint table to the SQLite schema:

CREATE TABLE sync_checkpoint (
  doctype TEXT PRIMARY KEY,
  last_page_offset INTEGER NOT NULL DEFAULT 0,
  last_ok_cursor TEXT,
  has_more INTEGER NOT NULL DEFAULT 1,
  started_at TEXT NOT NULL,
  checkpoint_at TEXT NOT NULL
);

Write Checkpoint After Every Successful Page

After each page of pulled records is successfully written to SQLite, atomically upsert the cursor state for that DocType into sync_checkpoint.

Resume on Next Launch

When sync is initiated, check sync_checkpoint first. If a checkpoint exists with has_more = 1, resume from the persisted cursor rather than page 0.

Checkpoint Cleanup

Once a DocType pull completes (has_more = 0), delete its checkpoint row. A fully clean sync leaves the table empty.

Category

Sync / Resilience / Offline-First