Problem Statement
Field operators — agricultural workers, health workers, logistics teams — need to record and access data in environments where internet connectivity is absent, intermittent, or unaffordable. Applications that require a live connection fail completely in these contexts. The challenge was building a synchronisation layer that allows the application to operate fully offline, buffers changes locally, and reconciles with the central server when connectivity is available — without losing data or creating inconsistencies.
Key Challenges:
- Deterministic conflict resolution when the same record is modified both locally and remotely
- Efficient delta synchronisation — sending only changed records, not full datasets
- Ordering guarantees for operations that depend on each other
- Handling partial sync failures gracefully without corrupting state
- Keeping the local SQLite database in a consistent state across crashes
System Architecture
Each client runs a local SQLite database with an embedded change-log tracking every insert, update, and delete with a logical timestamp and device ID. A background worker monitors connectivity and initiates sync when online. The server reconciles incoming change logs with its own, applies conflict resolution rules, and returns the merged state to the client.
Local Change Log
Every write to the local database appends a change record to a dedicated changelog table containing the operation type, record ID, field deltas, logical clock value, and device ID. This log is the source of truth for sync.
Diff Sync Protocol
Sync sessions exchange only changes since the last successful sync, identified by the client's last known server clock value. Diffs contain field-level deltas rather than full record copies, minimising data transfer.
Conflict Resolution
Conflicts (same record modified on both client and server since last sync) are resolved by a configurable strategy: last-write-wins by logical clock, field-level merge preferring the more recent value per field, or flagging for manual review.
Background Sync Worker
Connectivity monitor triggers sync when a network becomes available. Sync is transactional — changes are committed atomically on both sides, with rollback on partial failure to maintain consistency.
Key Engineering Challenges
Conflict Resolution Correctness
Challenge: Two users editing the same record offline can produce genuinely conflicting states that a simple last-write-wins rule may resolve incorrectly for the domain.
Solution: Field-level conflict resolution with domain-configurable strategies — some fields (e.g., cumulative counters) use sum merge; others use last-writer-wins; critical fields flag for manual review.
Operation Ordering
Challenge: Some operations are semantically dependent: deleting a parent record before processing child updates from a different device causes referential integrity failures.
Solution: Topological sort of incoming changes by foreign key relationships before applying, ensuring parent records are created before children and deletions are applied after all dependent changes.
Partial Sync Failure Recovery
Challenge: A sync session interrupted halfway through can leave the client in an inconsistent state that is difficult to resume.
Solution: Two-phase commit protocol — server prepares and validates the full changeset before the client applies any of it, with a confirmed commit step that is idempotent on retry.
Bandwidth Efficiency
Challenge: Rural deployments often operate on limited, metered data connections where transferring full dataset copies is prohibitive.
Solution: Field-level delta encoding compressing only changed fields, with binary serialisation reducing sync payload size by up to 80% compared to full JSON record transfers.
Solutions Implemented
- Change Log Architecture: Append-only local changelog tracking all mutations with logical clocks enabling accurate delta computation across devices.
- Field-Level Diff Sync: Protocol exchanging only changed fields since last sync, with binary encoding for bandwidth efficiency.
- Configurable Conflict Resolution: Per-field strategy definitions allowing domain-appropriate conflict handling — merge, last-write-wins, or manual review queuing.
- Transactional Sync Sessions: Two-phase commit ensuring sync is atomic — applied fully or not at all — with idempotent retry handling.
- Connectivity-Aware Background Worker: Automatic sync initiation on network availability with exponential backoff for repeated failures.
Outcome & Impact
Full functionality without connectivity
On connectivity restoration
vs. full record sync
No random or undefined outcomes