SpatiaLite & GeoPackage Automation with Python

Tracking Row Changes for Incremental GeoPackage Sync

Attach AFTER INSERT, AFTER UPDATE, and AFTER DELETE triggers to every syncable table so each edit writes a row into a change-log stamped with a…

Attach AFTER INSERT, AFTER UPDATE, and AFTER DELETE triggers to every syncable table so each edit writes a row into a change-log stamped with a monotonically increasing version; select deltas as everything above the last confirmed watermark, keep deletes alive as tombstones, and prune the log only once every consumer has acknowledged. This page, part of the Offline-First Sync Strategies guide, is the capture layer in full — it produces and records the changes that a push script later applies.

Why This Matters

Incremental sync is only as reliable as its record of what changed. If the change-log misses an edit, that edit never syncs; if it loses a delete, the feature resurrects on the next merge; if the version counter is not strictly monotonic, the exporter cannot tell new changes from old. A change-log built on database triggers captures every mutation at the point it happens — inside the same transaction as the edit — so nothing slips through, even when the writing application crashes a millisecond later.

This page is the counterpart to the push script: that page consumes deltas, this one manufactures them correctly.

Prerequisites

  • SQLite 3.35+ / a GeoPackage with a stable integer fid primary key per tracked table
  • Python 3.9+ with the standard-library sqlite3 module for the helper functions
  • WAL mode enabled so change-log writes never block a live reader
  • Familiarity with the capture / transport / reconcile model this log feeds

Primary Method

A single sequence table supplies a global monotonic version; one change-log table receives rows from three triggers per tracked table. This is the complete capture schema.

sql
-- GeoPackage context — monotonic version source, change-log, and watermark
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'))
);

CREATE INDEX IF NOT EXISTS ix_sync_log_ver ON _sync_log (version);
CREATE INDEX IF NOT EXISTS ix_sync_log_feat ON _sync_log (table_name, feature_id);

CREATE TABLE IF NOT EXISTS _sync_state (
    id                  INTEGER PRIMARY KEY CHECK (id = 1),
    last_pushed_version INTEGER NOT NULL DEFAULT 0
);
INSERT OR IGNORE INTO _sync_state (id, last_pushed_version) VALUES (1, 0);

-- Three triggers per tracked table; shown here for 'parcels'
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;

Because each trigger both bumps _sync_seq and reads it back inside one statement, every logged change gets a distinct, strictly increasing version even under rapid concurrent edits — SQLite serialises writers, so there is no race on the counter.

Step-by-Step Walkthrough

1. Bump a single monotonic counter

All ordering flows from one integer. Keeping it in a dedicated one-row table (rather than per-table AUTOINCREMENT) gives a single global sequence across every tracked table, so a delta can order edits from parcels and poles against each other.

sql
-- GeoPackage context — the version bump every trigger performs first
UPDATE _sync_seq SET version = version + 1 WHERE id = 1;

2. Log inserts and updates with NEW, deletes with OLD

Inserts and updates reference NEW.fid; deletes reference OLD.fid, because there is no NEW row to point at. That OLD.fid is the tombstone — the only durable proof the feature existed.

sql
-- GeoPackage context — the delete trigger preserves the vanished feature id
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;

3. Attach triggers to every tracked table programmatically

Writing three triggers by hand per table is error-prone. Generate them from a table list so the pattern stays identical everywhere.

python
# Python 3.9+ — install the three change-log triggers on each tracked table
import sqlite3

TRIGGER_SQL = """
CREATE TRIGGER IF NOT EXISTS {t}_log_{code} AFTER {event} ON {t}
BEGIN
    UPDATE _sync_seq SET version = version + 1 WHERE id = 1;
    INSERT INTO _sync_log (table_name, feature_id, op, version)
    VALUES ('{t}', {ref}.fid, '{op}', (SELECT version FROM _sync_seq WHERE id = 1));
END;
"""

def install_triggers(path: str, tables: list[str]) -> None:
    """Create AFTER INSERT/UPDATE/DELETE change-log triggers for each table."""
    specs = [
        ("i", "INSERT", "NEW", "I"),
        ("u", "UPDATE", "NEW", "U"),
        ("d", "DELETE", "OLD", "D"),
    ]
    conn = sqlite3.connect(path)
    try:
        for t in tables:
            for code, event, ref, op in specs:
                conn.execute(
                    TRIGGER_SQL.format(t=t, code=code, event=event, ref=ref, op=op)
                )
        conn.commit()
    finally:
        conn.close()

install_triggers("field.gpkg", ["parcels", "poles"])

4. Select a delta above the watermark

The exporter reads every change above last_pushed_version, collapsing to the latest operation per feature so a feature edited repeatedly travels once.

python
# Field database — latest change per feature above the watermark
import sqlite3

def changes_since_watermark(path: str, table: str) -> list[dict]:
    """Return the newest op per feature with a version above the 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]
        rows = conn.execute(
            "SELECT feature_id, op, MAX(version) AS version "
            "FROM _sync_log WHERE table_name = ? AND version > ? "
            "GROUP BY feature_id ORDER BY version",
            (table, watermark),
        ).fetchall()
        return [dict(r) for r in rows]
    finally:
        conn.close()

5. Prune the log after every consumer confirms

Once every device or central store has acknowledged a version, rows at or below the minimum confirmed watermark are dead. Prune them in a scheduled pass to keep the log small and exports fast — but never prune above any consumer’s watermark.

python
# Field database — prune change-log rows below the lowest confirmed watermark
import sqlite3

def prune_change_log(path: str, safe_version: int) -> int:
    """Delete change-log rows at or below safe_version. Returns rows removed."""
    conn = sqlite3.connect(path)
    try:
        cur = conn.execute(
            "DELETE FROM _sync_log WHERE version <= ?", (safe_version,)
        )
        conn.commit()
        return cur.rowcount
    finally:
        conn.close()

Verification

Confirm the triggers fire, the counter is monotonic, and deltas exclude already-pushed rows.

python
# Verify the change-log captures each operation and stays monotonic
import sqlite3

def verify_change_log(path: str, table: str = "parcels") -> None:
    conn = sqlite3.connect(path)
    conn.row_factory = sqlite3.Row
    try:
        conn.execute(
            f"INSERT INTO {table} (fid, name, status) VALUES (9001, 'test', 'new')"
        )
        conn.execute(f"UPDATE {table} SET status = 'done' WHERE fid = 9001")
        conn.execute(f"DELETE FROM {table} WHERE fid = 9001")
        conn.commit()

        ops = conn.execute(
            "SELECT op, version FROM _sync_log "
            "WHERE table_name = ? AND feature_id = 9001 ORDER BY version",
            (table,),
        ).fetchall()
        seq = [r["op"] for r in ops]
        versions = [r["version"] for r in ops]

        assert seq == ["I", "U", "D"], f"expected I,U,D got {seq}"
        assert versions == sorted(set(versions)), "versions not strictly increasing"
        print(f"OK — captured {seq} at versions {versions}")
    finally:
        conn.close()
bash
# Confirm every tracked table has exactly three change-log triggers
sqlite3 field.gpkg \
  "SELECT tbl_name, COUNT(*) FROM sqlite_master \
   WHERE type='trigger' AND name LIKE '%_log_%' GROUP BY tbl_name;"

Alternative Approaches or Edge Cases

fid reuse after a delete

SQLite may reuse a rowid/fid after a row is deleted unless the column is declared AUTOINCREMENT. If a new feature reuses a just-deleted fid within one sync window, the change-log holds a D then an I for the same id — the ORDER BY version and latest-per-feature selection resolve this correctly because the I carries the higher version. To eliminate the ambiguity entirely, declare the primary key INTEGER PRIMARY KEY AUTOINCREMENT so ids are never recycled.

rowid vs fid as the tracking key

GeoPackage feature tables expose an explicit fid that is the table’s INTEGER PRIMARY KEY and therefore an alias for rowid. Track on fid, not on a raw rowid reference, so the key survives a round-trip through ogr2ogr or Fiona, which may not preserve implicit rowid values. Aligning the tracking key with GeoPackage’s fid is one of the ergonomic reasons covered in the format decision guide.

Troubleshooting

sqlite3.OperationalError: no such table: _sync_seq

Cause: Triggers were installed before the _sync_seq sequence table existed, so they reference a missing table at fire time.

Fix: Create _sync_seq, _sync_log, and _sync_state before installing any trigger. Run the schema block from the Primary Method in full before calling install_triggers.

Version counter appears to skip numbers

Cause: A transaction bumped _sync_seq inside a trigger and then rolled back, consuming a version that was never committed to _sync_log.

Fix: Gaps are harmless — the counter only needs to be strictly increasing, not contiguous. Delta selection uses version > watermark, which tolerates gaps. Do not try to “reclaim” skipped numbers.

Pruning removed rows a device still needed

Cause: The log was pruned above a consumer’s confirmed watermark, so a device that had not yet synced lost its pending changes.

Fix: Prune only at or below the minimum watermark across every consumer. Compute that floor before deleting, and if any consumer’s watermark is unknown, skip pruning that cycle.