Field Inspection App: Conflicting Offline Edits
A tablet app supports 45 field technicians who inspect equipment at sites with intermittent connectivity. Each inspection record has 30 fields and up to 6 photos. The app writes to local SQLite and enqueues changed rows; a sync worker runs every 15 minutes when a network is available. The server applies last-write-wins using a client-supplied updated_at timestamp. Two technicians were reassigned to the same inspection on the same day. After both synced, one technician's 20 minutes of notes disappeared, photos uploaded but their parent record's metadata reverted, and several records showed updated_at three days in the future. Device clocks drift by up to 11 minutes, and one tablet had been manually set ahead. Support cannot tell which technician's version is correct, and the customer is asking for an audit trail. The product owner wants a fix that does not require technicians to resolve conflicts manually in the field, but compliance requires that no submitted data is silently discarded.
1. Explain the mechanism that caused the lost notes and reverted metadata. Why can client-supplied timestamps not be trusted to order edits in this system?
Read the worked answer
The server is comparing updated_at values supplied by tablets and keeping the larger one. That produces three separate failures.
First, clock quality is not controlled. Drift of 11 minutes is enough to reorder edits made close together, and a tablet set three days ahead wins every conflict until real time catches up. The future-dated records are direct evidence that the ordering key is corrupted. Last-write-wins does not mean last edit wins; it means largest timestamp wins.
Second, the client is enqueueing changed rows, not operations. When a row is uploaded, the server overwrites all 30 fields with the payload it received. If technician A changed notes and technician B changed a status field, B's full row can overwrite A's notes even though the edits did not overlap. This is a lost update caused by whole-record replacement.
Third, photos and metadata travel on separate paths. A photo can upload successfully while the record update is rejected or overwritten, leaving an orphaned or inconsistent attachment. Retries can also duplicate uploads because there is no stable client-generated identifier.
The correct conclusion is architectural: the server must own record revisions and the client must send the revision it started from. Timestamps can be stored for audit, but they must not be the conflict-resolution mechanism.
2. Design a conflict-resolution approach that preserves both technicians' submissions and satisfies the audit requirement without forcing field technicians to merge data manually. State what the server and client store.
Read the worked answer
Use server-authoritative revisions with explicit conflict records.
Server storage per record: a monotonically increasing revision, the accepted field values, and an append-only audit table containing every submitted operation, its client-generated operation ID, device ID, user ID, client timestamp (for information only), and server receive time. The server rejects a write whose base_revision is stale rather than overwriting.
Merge policy: apply field-level merge automatically where the changed field sets are disjoint. Where the same field changed on both sides, keep the server's current value as the accepted value and create a conflict record containing both candidate values. Do not silently pick one.
Client behaviour: after sync, the app shows a non-blocking banner on affected inspections, for example '2 fields need review'. The technician can accept either version, but the record is not blocked from being submitted. A dispatcher queue handles conflicts older than 24 hours so field staff are not forced to resolve them.
Compliance is met because the audit trail retains every submitted value and the conflict record makes the unresolved decision visible. The client also caches the last known revision per record and sends base_revision with each mutation. This turns an invisible overwrite into a detectable, auditable event, which is the key design change.
3. Specify the sync-protocol changes needed to make retries idempotent and keep photo uploads consistent with their parent record.
Read the worked answer
Model the sync as an operation log plus a transactional outbox, not as row dumps.
Client side: generate a UUID for every mutation and every photo before the first attempt. Store operations in an outbox table with state pending, in-flight, or acknowledged. A mutation references its photo UUIDs. Retries reuse the same UUIDs.
Server side: deduplicate by operation UUID and return the original result for duplicates. Accept a mutation only with a valid base_revision; otherwise return a conflict response containing the current revision and field values. Write the mutation and its audit entry in one database transaction.
Photo flow: upload the binary first to object storage using the client UUID as the object key, then commit the record mutation that references the immutable object URL. If the commit fails, the upload can be reused; if the upload succeeds but commit never arrives, a cleanup job removes unreferenced objects after a retention window. Do not make photo upload and metadata commit a single network request unless the server supports atomic multipart handling.
Sync cursor: the client pulls changes since a server-issued cursor, not since a device timestamp. This prevents clock skew from hiding remote updates. Finally, add server-side monitoring for stale-revision rejections and duplicate operation IDs so protocol problems are visible before users report missing data.