Offline-First Sync Strategies for SpatiaLite & GeoPackage

A field crew edits a GeoPackage on a tablet with no signal for six hours, then walks back into coverage — and the entire correctness of your dataset now depends on how deliberately you designed the moment those edits merge into the central store. This guide, part of the CLI Automation & Offline Sync Pipelines section, lays out the architecture for moving edits between disconnected SpatiaLite and GeoPackage files and a shared authoritative copy without losing, duplicating, or silently overwriting a single feature.

Offline-first sync is not one algorithm but a small stack of independent decisions: how you capture what changed, how you transport it, how you reconcile competing edits, and how you guarantee that a retried or half-finished run is harmless. Get the boundaries between those layers right and the individual pieces become simple; blur them and no amount of clever conflict code will save you.

Prerequisites #

Before wiring up a sync loop, confirm the foundation is in place. Skipping any of these is the usual root cause of a sync that “works in testing” and loses data in the field.

• SQLite 3.35+ / SpatiaLite 5+ — needed for RETURNING, generated columns, and reliable ON CONFLICT upsert semantics
• GDAL/OGR 3.4+ with GPKG and SQLite drivers for validation and re-indexing after apply
• Python 3.9+ with the standard-library sqlite3 module — the reference scripts here use no third-party driver
• WAL mode enabled on the field database so a background export never blocks live data collection
• A stable primary key strategy — a device-unique feature identifier that survives round-tripping, not a bare autoincrement rowid
• Familiarity with connection retry mechanics for handling database is locked under concurrent access
• An understanding of the R-tree spatial index so you can rebuild it correctly after a bulk apply

Confirm the field file is in WAL mode and carries a place to record its sync watermark before you begin:

# Field database — verify WAL mode and initialise a sync-state table
import sqlite3

def prepare_field_db(path: str) -> None:
    """Ensure WAL mode and a one-row sync-state table exist on the field file."""
    conn = sqlite3.connect(path)
    try:
        mode = conn.execute("PRAGMA journal_mode=WAL;").fetchone()[0]
        if mode.lower() != "wal":
            raise RuntimeError(f"Expected WAL journal mode, got {mode!r}")
        conn.execute(
            "CREATE TABLE IF NOT EXISTS _sync_state ("
            "  id INTEGER PRIMARY KEY CHECK (id = 1),"
            "  last_pushed_version INTEGER NOT NULL DEFAULT 0"
            ")"
        )
        conn.execute(
            "INSERT OR IGNORE INTO _sync_state (id, last_pushed_version) VALUES (1, 0)"
        )
        conn.commit()
    finally:
        conn.close()

prepare_field_db("field.gpkg")

Concept & Specification Reference #

Offline sync introduces a handful of terms that must mean exactly one thing across every script in the pipeline. Pin these definitions before writing code.

Core Vocabulary #

The Two Sync Shapes #

There are only two transport shapes, and the choice between them is the first architectural decision:

Full-file sync is the right default for read-mostly reference layers pushed out to devices. Delta sync is mandatory the moment devices edit and those edits must merge — which is the case this guide addresses.

Conflict Resolution Strategies #

When two devices touch the same feature between syncs, the reconcile step must pick a winner deterministically. Three strategies cover almost every field workflow:

Whichever you adopt, encode it in the script. A reconcile rule that lives only in an operator’s habits is unauditable and will drift.

Step-by-Step Implementation #

This walkthrough builds the capture side on the field device, exports a delta, and applies it to the central store. Each step is runnable in isolation.

1. Install Change-Tracking Triggers #

Every syncable table needs three AFTER triggers that write to the shared change-log. The log stamps a monotonic version from a single sequence table so ordering is global across every tracked table.

-- GeoPackage context — global change-log and version sequence
CREATE TABLE IF NOT EXISTS _sync_seq (
    id      INTEGER PRIMARY KEY CHECK (id = 1),
    version INTEGER NOT NULL
);
INSERT OR IGNORE INTO _sync_seq (id, version) VALUES (1, 0);

CREATE TABLE IF NOT EXISTS _sync_log (
    change_id  INTEGER PRIMARY KEY,
    table_name TEXT    NOT NULL,
    feature_id INTEGER NOT NULL,
    op         TEXT    NOT NULL CHECK (op IN ('I', 'U', 'D')),
    version    INTEGER NOT NULL,
    changed_at TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
);

-- GeoPackage context — AFTER triggers advance the sequence and log the change
CREATE TRIGGER IF NOT EXISTS parcels_log_i AFTER INSERT ON parcels
BEGIN
    UPDATE _sync_seq SET version = version + 1 WHERE id = 1;
    INSERT INTO _sync_log (table_name, feature_id, op, version)
    VALUES ('parcels', NEW.fid, 'I', (SELECT version FROM _sync_seq WHERE id = 1));
END;

CREATE TRIGGER IF NOT EXISTS parcels_log_u AFTER UPDATE ON parcels
BEGIN
    UPDATE _sync_seq SET version = version + 1 WHERE id = 1;
    INSERT INTO _sync_log (table_name, feature_id, op, version)
    VALUES ('parcels', NEW.fid, 'U', (SELECT version FROM _sync_seq WHERE id = 1));
END;

CREATE TRIGGER IF NOT EXISTS parcels_log_d AFTER DELETE ON parcels
BEGIN
    UPDATE _sync_seq SET version = version + 1 WHERE id = 1;
    INSERT INTO _sync_log (table_name, feature_id, op, version)
    VALUES ('parcels', OLD.fid, 'D', (SELECT version FROM _sync_seq WHERE id = 1));
END;

The DELETE trigger records OLD.fid — that tombstone is the only evidence a feature ever existed once its row is gone. The full trigger lifecycle, including compaction of superseded rows, is covered in Tracking Row Changes for Incremental GeoPackage Sync.

2. Export a Delta Newer Than the Watermark #

The exporter reads every change-log row above the device’s last watermark, joins live rows for inserts and updates, and emits deletes as tombstones. Selecting only the latest change per feature keeps the delta minimal.

# Field database — build a delta of changes newer than the watermark
import json
import sqlite3

def export_delta(path: str, table: str = "parcels") -> dict:
    """Return a self-contained delta of changes above the current watermark."""
    conn = sqlite3.connect(path)
    conn.row_factory = sqlite3.Row
    try:
        watermark = conn.execute(
            "SELECT last_pushed_version FROM _sync_state WHERE id = 1"
        ).fetchone()[0]
        max_version = conn.execute(
            "SELECT COALESCE(MAX(version), ?) FROM _sync_log", (watermark,)
        ).fetchone()[0]

        # Latest operation per feature above the watermark
        log_rows = conn.execute(
            "SELECT feature_id, op, MAX(version) AS version "
            "FROM _sync_log "
            "WHERE table_name = ? AND version > ? "
            "GROUP BY feature_id",
            (table, watermark),
        ).fetchall()

        upserts, tombstones = [], []
        for row in log_rows:
            if row["op"] == "D":
                tombstones.append(row["feature_id"])
                continue
            feature = conn.execute(
                f"SELECT fid, name, status, AsGeoJSON(geom) AS geom "
                f"FROM {table} WHERE fid = ?",
                (row["feature_id"],),
            ).fetchone()
            if feature is not None:
                upserts.append(dict(feature))
    finally:
        conn.close()

    return {
        "delta_id": f"{table}-{watermark + 1}-{max_version}",
        "table": table,
        "base_version": watermark,
        "max_version": max_version,
        "upserts": upserts,
        "tombstones": tombstones,
    }

delta = export_delta("field.gpkg")
print(json.dumps({k: v for k, v in delta.items() if k != "upserts"}, indent=2))

The delta_id embeds the version range, giving every delta a stable, human-readable identity the central store uses for idempotency.

3. Apply the Delta Idempotently #

The central store applies the whole delta in one transaction. It first checks an _applied_deltas table; a delta already present is skipped entirely, which is what makes a retried push safe.

# Central store — apply a delta once, inside a single transaction
import sqlite3

def apply_delta(central_path: str, delta: dict) -> str:
    """Apply a delta idempotently; return 'applied' or 'skipped'."""
    conn = sqlite3.connect(central_path, isolation_level=None)
    try:
        conn.execute(
            "CREATE TABLE IF NOT EXISTS _applied_deltas ("
            "  delta_id TEXT PRIMARY KEY,"
            "  applied_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))"
            ")"
        )
        already = conn.execute(
            "SELECT 1 FROM _applied_deltas WHERE delta_id = ?", (delta["delta_id"],)
        ).fetchone()
        if already:
            return "skipped"

        table = delta["table"]
        conn.execute("BEGIN IMMEDIATE")
        for feat in delta["upserts"]:
            conn.execute(
                f"INSERT INTO {table} (fid, name, status, geom) "
                f"VALUES (?, ?, ?, GeomFromGeoJSON(?)) "
                f"ON CONFLICT(fid) DO UPDATE SET "
                f"  name = excluded.name, status = excluded.status, geom = excluded.geom",
                (feat["fid"], feat["name"], feat["status"], feat["geom"]),
            )
        for fid in delta["tombstones"]:
            conn.execute(f"DELETE FROM {table} WHERE fid = ?", (fid,))
        conn.execute(
            "INSERT INTO _applied_deltas (delta_id) VALUES (?)", (delta["delta_id"],)
        )
        conn.execute("COMMIT")
        return "applied"
    except Exception:
        conn.execute("ROLLBACK")
        raise
    finally:
        conn.close()

Because the _applied_deltas insert commits in the same transaction as the row changes, a crash before COMMIT leaves both the data and the ledger untouched — the delta re-applies cleanly on the next run. A production-grade version of this script, with lock retry and checkpointing, is walked through in How to Build an Offline Sync Push Script for GeoPackage.

4. Rebuild the Spatial Index and Advance the Watermark #

Bulk INSERT/DELETE through raw SQL bypasses the driver’s index-maintenance triggers on SpatiaLite. After applying a delta, rebuild the spatial index on the central store, then acknowledge back to the device so its watermark advances.

# Central store (SpatiaLite) — rebuild the index after a delta apply
import sqlite3

def rebuild_spatial_index(path: str, table: str, geom_col: str = "geom") -> None:
    """Rebuild the SpatiaLite R-tree index after a bulk delta apply."""
    conn = sqlite3.connect(path)
    conn.enable_load_extension(True)
    conn.load_extension("mod_spatialite")
    try:
        conn.execute("SELECT DisableSpatialIndex(?, ?)", (table, geom_col))
        conn.execute("SELECT CreateSpatialIndex(?, ?)", (table, geom_col))
        conn.commit()
    finally:
        conn.close()

On GeoPackage the trigger-managed R-tree usually stays consistent through ON CONFLICT upserts, but a full validation pass is still cheap insurance — see the failure modes below.

Validation & Verification #

Never advance a watermark until the apply is proven. These copy-paste checks form the gate.

# Confirm the central store is structurally sound after apply
sqlite3 central.gpkg "PRAGMA integrity_check;" | grep -q '^ok$' && echo "integrity OK"

# Confirm feature counts moved as expected and no orphaned index rows remain
sqlite3 central.gpkg "SELECT COUNT(*) AS features FROM parcels;"
ogrinfo -al -so central.gpkg parcels | grep -E 'Feature Count|Geometry'

# Verify idempotency: applying the same delta twice must not change counts
import sqlite3

def assert_idempotent(central_path: str, delta: dict) -> None:
    """Apply a delta twice and assert the row count is stable the second time."""
    def count(conn):
        return conn.execute(
            f"SELECT COUNT(*) FROM {delta['table']}"
        ).fetchone()[0]

    first = apply_delta(central_path, delta)
    conn = sqlite3.connect(central_path)
    n1 = count(conn)
    conn.close()

    second = apply_delta(central_path, delta)
    conn = sqlite3.connect(central_path)
    n2 = count(conn)
    conn.close()

    assert first == "applied", f"expected first apply, got {first}"
    assert second == "skipped", f"second apply must skip, got {second}"
    assert n1 == n2, f"row count changed on replay: {n1} -> {n2}"

A green run of assert_idempotent is the single most valuable test in the whole pipeline: it proves the property that makes unattended sync safe.

Common Failure Modes & Fixes #

Deletes Vanish Instead of Propagating #

Symptom: A feature deleted on the device reappears after the next sync, or the central store never learns it was removed.

Cause: The delta carries only live rows. Because the deleted row no longer exists, a naive exporter that joins the feature table emits nothing for it, and an absent row is indistinguishable from a feature that was never created.

Fix: Emit tombstones from the AFTER DELETE trigger’s OLD.fid and apply them as explicit DELETE statements, exactly as in steps 1 and 3. Never infer deletions by diffing row sets.

Duplicate Rows After a Retried Push #

Symptom: Feature counts on the central store climb faster than edits justify; the same feature appears twice.

Cause: The delta was applied, the process died before acknowledging, and the retry re-inserted with INSERT rather than an upsert — or the _applied_deltas ledger was written in a separate transaction from the data.

Fix: Use INSERT ... ON CONFLICT(fid) DO UPDATE keyed on a stable feature id, and record the applied delta id inside the same transaction as the row changes so both commit or neither does.

database is locked During Apply #

Symptom: The apply transaction fails with sqlite3.OperationalError: database is locked when a device or another writer holds the file.

Cause: A second connection holds a write lock, or a long-running reader under a rollback journal blocks the writer.

Fix: Open the central store in WAL mode, use BEGIN IMMEDIATE to acquire the write lock up front, and wrap the apply in bounded retry with backoff. The full retry pattern is documented in implementing connection retries for offline apps.

Spatial Queries Return Nothing After Apply #

Symptom: ogrinfo reports the correct feature count but spatial filters return no rows on SpatiaLite.

Cause: Raw-SQL inserts bypassed the SpatiaLite virtual-table index, leaving the R-tree stale.

Fix: Call DisableSpatialIndex then CreateSpatialIndex after each bulk apply, as in step 4. On GeoPackage, confirm the rtree_<table>_geom triggers are intact with an OGC compliance pass.

Version Counter Resets and Replays Everything #

Symptom: A sync re-sends the entire dataset as if nothing had ever been pushed.

Cause: The _sync_seq version or the _sync_state watermark was reset — often by a table drop-and-recreate during a schema migration.

Fix: Treat _sync_seq, _sync_log, and _sync_state as protected system tables. Migrate them explicitly and never DROP them as part of a data reload; if you must rebuild, carry the last version forward.

Performance Notes #

• One delta, one transaction. Wrapping every row in the delta inside a single BEGIN IMMEDIATE ... COMMIT turns thousands of implicit commits into one, cutting apply time by an order of magnitude on large deltas. This is the same batching principle that governs scripting batch spatial queries with the sqlite3 CLI.
• Prune the change-log after a confirmed watermark. Rows below every device’s acknowledged watermark are dead weight. Delete them in a scheduled pass so the log stays small and exports stay fast.
• Defer index rebuilds to the end of the cycle. Rebuilding the R-tree once after all deltas apply is far cheaper than maintaining it per row; disable it during bulk apply and rebuild in one pass.
• Checkpoint the WAL on a schedule. A long apply grows the -wal sidecar; a PRAGMA wal_checkpoint(TRUNCATE); at the end of the cycle keeps the file bounded without stalling collection.
• Keep deltas small with latest-per-feature selection. Collapsing repeated edits to one row per feature (the GROUP BY feature_id in step 2) shrinks transport payloads dramatically for features edited many times between syncs.

Child Pages #

• SpatiaLite vs GeoPackage for Mobile Offline Sync — a decision guide for choosing a format for offline-first mobile workflows: tooling, index maintenance, standards conformance, and change-tracking ergonomics
• How to Build an Offline Sync Push Script for GeoPackage — a complete, runnable Python push script that packages a delta, applies it in one transaction, and retries on lock
• Tracking Row Changes for Incremental GeoPackage Sync — building a robust change-log with triggers, monotonic version counters, tombstones, and log pruning

Frequently Asked Questions #

Related #

• CLI Automation & Offline Sync Pipelines — parent section: the command-line toolchain and scheduling that drive every sync stage
• Tracking Row Changes for Incremental GeoPackage Sync — the capture layer in depth: triggers, version counters, and tombstones
• How to Build an Offline Sync Push Script for GeoPackage — the apply layer as a complete, retry-safe script
• Transaction Scoping & Rollback Strategies — WAL mode, BEGIN IMMEDIATE, and rollback patterns that keep an apply atomic
• Implementing Connection Retries for Offline Apps — bounded retry and backoff for database is locked during apply