Offline Stock Count Sync Overwrites Field Edits
Northline Utilities runs an OutSystems mobile app for 45 field technicians who record warehouse stock counts in basements with no signal. The app is offline-enabled: technicians commit StockCount records (WarehouseId, ItemId, Quantity, UpdatedOn, UpdatedBy) to local storage and sync when they reach the van hotspot. During a quarterly audit, two technicians counted the same bin 20 minutes apart while offline. The second sync overwrote the first count, and the warehouse manager later saw a quantity that neither technician recognized as their final answer. Separately, a deletion made offline reappeared after sync. Sync currently takes about 25 seconds and operations refuses to let it grow. They want a defensible audit trail and an explicit rule for collisions, without asking technicians to resolve conflicts manually in the field. The server is the system of record for finance, so counts cannot simply be accepted blindly.
1. Why did the later sync silently overwrite the earlier count, and what does that reveal about the default authority model in offline synchronization?
Read the worked answer
The sync is record-level, not field-level. Each device submits the current state of its local StockCount row, and the server accepts that row as the new state. There is no automatic merge of Quantity, UpdatedOn, and UpdatedBy, and no comparison against the version the device originally read. The last device to sync therefore wins, regardless of who counted first or whose count is more trustworthy.
The UpdatedOn value does not protect you because it is generated on the device. Clock skew, timezone settings, and manual clock changes make it unreliable as an ordering key. The deletion reappearing is a related symptom: a physical delete on the client is not a durable tombstone, so the server row survives or is recreated by the next sync.
The design implication is that sync should be treated as submitting a change set with preconditions, not as pushing final state. Add a server-managed Version or RowVersion, send the version the technician read, and reject the update if it no longer matches. On rejection, write a conflict record rather than overwriting. That converts a silent data-loss problem into an explicit, auditable event.
2. Design a collision rule and data model that gives Northline a defensible audit trail without manual field resolution.
Read the worked answer
Use an append-only attempt model rather than mutating a single StockCount row. Each scan creates a StockCountAttempt with a device-generated UUID (for idempotency), WarehouseId, ItemId, Quantity, DeviceId, TechnicianId, device timestamp, and server receipt timestamp. The server never updates an existing attempt. A separate CurrentStockCount projection holds the accepted value and the AttemptId that produced it.
The collision rule should be explicit: for a given bin, the first accepted attempt wins unless a supervisor marks the later attempt as a recount. When a second attempt arrives for the same bin and same audit window, the server accepts it into the attempt table but does not change the projection; it flags a Conflict record and notifies the supervisor. This preserves both counts and makes the decision visible.
Do not rely on client clocks to choose the winner. Order by server receipt time for display, but let the business rule and supervisor decision determine acceptance. Keep payloads small: send only changed attempts, not the whole local table. This keeps sync near the current 25 seconds. The audit trail is the attempt table, so finance can see every count and who submitted it.
3. How should deletions be handled so that an offline delete does not reappear, and what else must change in the sync logic?
Read the worked answer
Replace physical deletes with soft deletes. Add IsDeleted, DeletedAt, and DeletedBy to the synchronized entity, and make deletion an update that syncs like any other change. Queries on both client and server must filter IsDeleted = false. The server retains the row as a tombstone, so a later sync cannot resurrect it as an active record.
This also solves a common offline edge case: if device A deletes a record and device B still holds it, device B's next sync should not recreate it. With a tombstone, the server can reject or ignore updates to a deleted row and return the tombstone to device B, which then removes it locally.
Other required changes: use idempotency keys so a retried sync does not create duplicate attempts; validate the version precondition on every update; and log rejected syncs with the device ID and payload hash. Finally, test the sync with two devices editing the same row and with one device deleting while the other edits. The current 25-second budget should be protected by sending only changed rows and by not adding chatty per-record round trips.