Syncing Offline Inspection Data with Conflict Resolution

12 min read

This guide extends capturing field inspection results on mobile devices, part of the Inspection Scheduling & Field Operations track. Where that parent page covers how an inspector records a structured result into a durable on-device queue, this walkthrough covers the harder half: draining that queue back to the server reliably, in order, exactly once, and resolving the conflicts that arise when the same permit was edited in two places at once.

The focused task is narrow and unforgiving. An inspector works a full route offline, accumulating a queue of results and evidence, while back at the office a clerk edits the same permit — reschedules a re-inspection, updates a contact, corrects a parcel reference. Hours later the device reconnects and replays. Two independent edit histories now have to converge on one record without losing either side’s intent and without corrupting the audit trail. The stakes are compliance-grade: a lost failed-inspection result means an unsafe structure advances, and a silently clobbered clerk edit means the official record diverges from what actually happened. The inputs are a queue of durably-stored, content-hashed results with idempotency keys; the outputs are a server state where every result is applied exactly once, concurrent edits are merged by an explicit and defensible rule, and every merge decision is logged.

Conflict resolution decision flow for replayed offline inspection edits A replayed edit carries the device version vector and the base version it last observed. The server compares that vector against the current version vector and classifies the relation. An equal or device-newer relation fast-forwards through a compare-and-set commit. A server-newer relation is already applied and needs no write. Only a genuinely concurrent relation routes into the per-field merge rules, where inspection outcomes and evidence are unioned append-only by content hash, office-owned scheduling keeps the server value, field-observed facts keep the device value, and any case the rules cannot settle is escalated to a supervisor and logged. Replayed edit device vector + base version Compare vectors relation(device, server) against current version equal / device-newer server-newer concurrent Fast-forward commit compare-and-set on base Already applied no write needed Per-field merge rule by field class real conflict only Outcomes: append union by content hash Scheduling: office clerk value wins Escalate + log

Step 1 — Replay the Queue with Idempotency Keys

Draining the outbox is a queue-and-replay loop: read pending rows oldest-first, POST each with its idempotency key, and advance state only on a confirmed acknowledgment. The key — derived on the device from the inspection ID and a hash of the canonical result — is what makes replay safe. A crash, a timeout, or a duplicate send all resolve to the same server-side identity, so the record commits once no matter how many times the loop retries.

import sqlite3
import httpx
from contextlib import closing


async def drain_outbox(conn: sqlite3.Connection, client: httpx.AsyncClient,
                       base_url: str) -> None:
    """Replay pending results oldest-first; advance only on a confirmed ack."""
    rows = conn.execute(
        "SELECT idem_key, permit_id, payload FROM outbox "
        "WHERE state = 'pending' ORDER BY created_at ASC"   # preserve capture order
    ).fetchall()

    for idem_key, permit_id, payload in rows:
        try:
            resp = await client.post(
                f"{base_url}/inspections/result",
                content=payload,                         # canonical bytes, unaltered
                headers={"Idempotency-Key": idem_key,
                         "Content-Type": "application/json"},
                timeout=15.0,
            )
        except (httpx.TimeoutException, httpx.TransportError):
            break            # network still flaky: stop, keep order, retry later

        if resp.status_code in (200, 201):               # committed OR duplicate
            with closing(conn.cursor()) as cur:
                cur.execute("UPDATE outbox SET state='acked' WHERE idem_key=?",
                            (idem_key,))
        elif resp.status_code == 409:                    # conflict: needs merge
            _mark_conflicted(conn, idem_key, resp.json())
        else:
            _bump_attempts(conn, idem_key)               # 4xx/5xx: backoff + retry

Stopping the loop on a transport error rather than skipping ahead preserves ordering: a later result must never overtake an earlier one that has not yet been acknowledged, or the server could see a re-inspection before the original failure it corrects.

Step 2 — Detect Concurrency with a Version Token

Last-write-wins is only safe when writes are not actually concurrent. To know whether they are, every server record carries a version token — a monotonically increasing integer or an opaque ETag — that the device captured when it last synced. On replay the device sends that base version; the server compares it to the current version and can tell the difference between a clean update (versions match) and a concurrent edit (the server moved on while the device was offline).

from pydantic import BaseModel, ConfigDict, Field


class VersionedEnvelope(BaseModel):
    model_config = ConfigDict(strict=True, extra="forbid")
    permit_id: str
    base_version: int = Field(ge=0)   # server version the device last observed
    payload: dict                     # the InspectionResult, canonicalized


async def apply_with_check(env: VersionedEnvelope) -> dict:
    current = await store.current_version(env.permit_id)
    if current == env.base_version:
        # No one else touched it: fast path, a clean linear apply.
        new_version = await store.commit(env.permit_id, env.payload,
                                         expected=env.base_version)
        return {"status": "committed", "version": new_version}
    # The server advanced while the device was offline — genuine concurrency.
    raise ConflictError(current_version=current,
                        server_state=await store.load(env.permit_id))

The comparison must be atomic with the commit (a compare-and-set on expected=base_version), or two devices replaying at once can both read the same base, both believe they are clean, and both write — reintroducing the very race the token exists to catch.

Step 3 — Resolve Conflicts by Field Class, Not One Blanket Rule

A single conflict strategy for the whole record is wrong for municipal data. Some fields are field-observed facts the inspector is authoritative for; others are office-managed metadata the clerk owns. Partition the record and apply the right rule per class: last-write-wins is acceptable for low-stakes descriptive fields, but an inspection outcome must never be silently overwritten — it is appended as an immutable event, and only its ordering is resolved.

from enum import Enum


class MergeRule(str, Enum):
    APPEND_ONLY = "append_only"   # outcomes/evidence: never overwrite, only add
    INSPECTOR_WINS = "inspector"  # field-observed facts: device is authoritative
    CLERK_WINS = "clerk"          # office metadata: server is authoritative
    LAST_WRITE_WINS = "lww"       # low-stakes descriptive text


FIELD_RULES: dict[str, MergeRule] = {
    "result_events":   MergeRule.APPEND_ONLY,   # the actual pass/fail records
    "evidence":        MergeRule.APPEND_ONLY,
    "geo":             MergeRule.INSPECTOR_WINS,
    "scheduled_at":    MergeRule.CLERK_WINS,     # office owns the calendar
    "contact_note":    MergeRule.LAST_WRITE_WINS,
}


def merge(field: str, device_val, server_val, device_ts, server_ts):
    rule = FIELD_RULES.get(field, MergeRule.CLERK_WINS)  # unknown → office wins
    match rule:
        case MergeRule.APPEND_ONLY:
            return _union_by_hash(device_val, server_val)   # dedup on content hash
        case MergeRule.INSPECTOR_WINS:
            return device_val
        case MergeRule.CLERK_WINS:
            return server_val
        case MergeRule.LAST_WRITE_WINS:
            return device_val if device_ts > server_ts else server_val

Defaulting unknown fields to CLERK_WINS is a fail-safe: a field nobody has classified cannot be overwritten by an offline device, so adding a column never silently opens a data-loss path. Append-only fields union by content hash, so a result the server already has (because the device retried) deduplicates instead of doubling.

Step 4 — Order Concurrent Events with a Version Vector

Last-write-wins by wall-clock timestamp is fragile across devices with skewed clocks. When two inspectors can touch the same permit — a primary and a re-inspection by a different officer — replace the scalar timestamp with a small version vector: a map of actor → counter that records how many edits each actor has applied. Comparing vectors tells you whether one edit causally precedes another (safe to fast-forward) or whether they are truly concurrent (must be merged by rule), independent of clock accuracy.

def dominates(a: dict[str, int], b: dict[str, int]) -> bool:
    """True if vector `a` causally descends from `b` (every counter >= b's)."""
    keys = a.keys() | b.keys()
    return all(a.get(k, 0) >= b.get(k, 0) for k in keys) and a != b


def relation(a: dict[str, int], b: dict[str, int]) -> str:
    if a == b:
        return "equal"
    if dominates(a, b):
        return "a_newer"     # a saw everything b did, plus more → fast-forward
    if dominates(b, a):
        return "b_newer"
    return "concurrent"      # neither dominates → real conflict, invoke merge()


def bump(vec: dict[str, int], actor: str) -> dict[str, int]:
    out = dict(vec)
    out[actor] = out.get(actor, 0) + 1   # this actor made one more edit
    return out

A vector clock does not decide the winner — it tells you honestly whether there is a conflict at all. Most reconnects are causally clean (a_newer) and fast-forward with no merge; only genuinely concurrent edits fall through to the per-field rules in Step 3, which keeps the expensive path rare.

Parameter and Flag Reference

Setting Type Default Rationale for offline inspection sync
replay_order str "created_at ASC" Preserve capture order so a re-inspection never lands before its original.
idempotency_ttl_days int 90 Server remembers committed keys long enough to reject late replays after a device sat offline.
conflict_default_rule MergeRule CLERK_WINS Unclassified fields fail safe to the office record; an offline device cannot clobber them.
outcome_rule MergeRule APPEND_ONLY Inspection results are immutable events; only ordering is resolved, never overwrite.
version_scheme str "vector" Vector clocks tolerate cross-device clock skew that scalar LWW cannot.
max_replay_batch int 50 Bound a single sync burst so a day-long queue does not monopolize a constrained host.
retry_backoff_base_s float 2.0 Exponential backoff on 5xx; jittered to avoid a thundering herd when a tower comes back.
cas_on_commit bool True Compare-and-set on base_version; without it two clean-looking writes race.

Common Failure Patterns and Fixes

Duplicate commits after a lost acknowledgment

The device commits, the server’s 201 is lost on the way back, and the loop replays the same result. Without idempotency this doubles the record. Fix: derive the key server-side from the payload and record committed keys; a repeat returns duplicate and applies nothing, so exactly-once holds even though delivery is at-least-once.

Reordered results from parallel replay

Two connections drain the same outbox and a later result is acknowledged before an earlier one, so the server sees a correction before the failure it corrects. Fix: replay strictly oldest-first on a single in-flight request, and stop the loop on the first transport error rather than skipping ahead.

Silent clobber of a clerk edit

The device replays with a stale base_version, blindly overwrites, and the office’s scheduling change vanishes. Fix: make every commit a compare-and-set against base_version; a mismatch raises a 409 that routes into merge() instead of overwriting, and scheduled_at resolves to CLERK_WINS.

Clock skew breaking last-write-wins

A device with a fast clock wins every LWW comparison regardless of actual order. Fix: use vector clocks for causality and reserve wall-clock LWW for genuinely low-stakes descriptive text where a wrong tiebreak is harmless.

Merge that drops an inspection outcome

Treating the whole record as one blob and taking the newer side discards a failed-inspection event captured offline. Fix: classify result_events and evidence as APPEND_ONLY so outcomes are unioned by content hash and never overwritten — the failed result survives even if every other field came from the server.

Audit and Logging Guidance

Every conflict resolution is a decision that altered an official record, so log it with the same rigor as the result itself. For each merge, record the permit ID, the field, the rule applied, both competing values (or their hashes for evidence), the version vectors on each side, and the winning value — then hash-chain the entry into the same append-only audit log the platform uses elsewhere, so a merge cannot be edited after the fact. Record clean fast-forwards too, at a lower level, because “no conflict” is itself an auditable claim during a records dispute. Retain these entries for the full state-mandated period, and redact nothing that bears on the decision while still keeping raw device tokens and precise GPS coordinates out of the trail unless retention law requires them. When a merge cannot be resolved automatically — two concurrent APPEND_ONLY edits that need human adjudication — queue it for a supervisor and log the escalation rather than guessing, using the same permission tiers defined for role-based access in clerk portals. Where a merged outcome flips a permit to failed, the resulting enforcement action is itself a logged event in the code violation lifecycle, so the audit trail runs unbroken from field capture through conflict resolution into compliance.

Frequently Asked Questions

When is last-write-wins actually safe to use?

Only for low-stakes descriptive fields where an occasional wrong tiebreak causes no compliance harm — a free-text contact note, for instance. It is never safe for inspection outcomes or office-owned scheduling data. Even where you do use it, prefer a version vector over a wall-clock timestamp so a skewed device clock cannot systematically win, and reserve pure timestamp LWW for cases where losing a field entirely would be acceptable.

Do I need vector clocks, or is a simple version integer enough?

A single monotonic integer is enough when exactly one writer can touch a record between syncs — most single-inspector permits. Reach for vector clocks when two actors can edit concurrently across devices, such as a primary inspection and a re-inspection by a different officer, because a scalar version cannot distinguish “newer” from “concurrent” and will hide a real conflict. The vector only tells you whether a conflict exists; the per-field rules still decide the winner.

How do I stop a day-long offline queue from overwhelming the server on reconnect?

Bound each sync to a batch of results, replay oldest-first, and back off exponentially with jitter on any 5xx so a whole fleet reconnecting when a tower returns does not stampede. Because every record is idempotent, an interrupted batch simply resumes on the next pass with no duplication, so you can keep batches small without risking lost or double-applied results.