Delta-sync protocol and local data-model spec for offline-first mobile field forms

If you've already committed to offline-first field forms, you've solved the easy part. The hard part isn't caching data on the device — it's what happens when three technicians edit overlapping records on flaky LTE, one of them uploads a 40MB photo that dies at 60%, and your server has to figure out which version of the truth wins.

Most offline specs stop at "we support conflict resolution" and hand-wave the rest. That gap is exactly where field data corruption lives. A dispatcher sees a job marked complete that a tech later reopened. A part gets deducted twice from inventory because the same "consume part" op replayed after a reconnect. These aren't rare edge cases — in real operations they're the normal result of intermittent connectivity plus underspecified sync.

This post is the concrete layer underneath the higher-level guidance in designing offline-first mobile workflows for reliable field data capture. Exact JSON, a local schema you can migrate to, ordering rules, idempotency semantics, an attachment upload protocol, and a QA test matrix your testers can run without reinterpreting anything. If your engineers and QA are arguing about what "should" happen in a given scenario, this is meant to end that argument.

The core model: everything is an operation, not a state

The single biggest mistake teams make is syncing records instead of operations. They push the whole form object up, the server overwrites, and the last writer wins by accident. Fine until two people touch the same job.

The reliable pattern is an append-only op log. The device never says "here's the current state of job 4471." It says "here are the ordered changes I made." The server applies those changes and tells the device what it now believes. This makes idempotency, conflict detection, and replay-after-crash tractable instead of magical.

1. 1
   Every op has a stable client-generated opId so replays are safe.
2. 2
   Every op carries a per-field snapshot so the server can detect field-level conflicts, not just record-level.
3. 3
   Ops are ordered per device via a monotonic sequence number, and causally ordered across devices via server sequence assignment.

The rest of this spec fills in those three ideas precisely.

Local schema

Here's the schema we'd recommend on the device (SQLite columns, simplified). It's opinionated on purpose — vagueness is where bugs breed.

records

The current materialized state the UI reads from.

op_log (the outbox)

Append-only. Nothing here gets mutated except the status field.

tombstones

Deletes need to survive so a stale device can't resurrect a record.

per-field metadata

Store, per field, the serverseq and opid that last set it. This is what lets you merge non-overlapping edits instead of throwing away a whole record. In practice this lives inside the record payload as a shadow map, e.g. _meta: { "status": {"seq": 812, "opId": "..."} }.

The reason to keep prior values inside each op's fields block is conflict payloads. When the server rejects a field, it needs to tell the client both what the client thought the old value was and what the server actually has. Without the prior value you can't build a useful merge UI.

Op ordering and causal rules

Two ordering systems run at once, and confusing them is a classic source of "why did my newer edit disappear" bugs.

1. 1
   client_seq — a per-device counter that only increases. It guarantees ops from one device apply in the order they were made. Never reset it, even across app reinstalls if you can persist it.
2. 2
   serverseq — assigned by the server when an op is accepted. It's the global truth ordering. Every record and every accepted op gets a server seq. Delta pulls are "give me everything with serverseq > my last cursor."

> An op's baseserverseq declares what the client saw before making the change. If the server's current server_seq for that record is higher, the change happened against stale data — a potential conflict.

Note potential. If the newer server change touched different fields, you merge silently. Only overlapping fields become real conflicts. Record-level "reject the whole thing" is lazy and generates false conflicts that techs learn to click through blindly, which defeats the whole point.

Idempotency and opId semantics

The opId is a UUID generated on the device before the op is ever sent. This one detail eliminates an entire class of double-application bugs.

1. 1
   If the server has never seen this opId, process it, assign a serverseq, store (opId → serverseq, result).
2. 2
   If the server has seen this opId, do not reprocess. Return the original stored result (the same ack, same server_seq).
3. 3
   The dedup record must live at least as long as any client could plausibly retry — 30 days is a reasonable floor for field apps that go dark on job sites for extended periods.

The subtle failure here: teams implement dedup at the HTTP layer (idempotency-key header on the batch request) instead of per-op. Then a batch of 12 ops partially succeeds, the client retries the whole batch, and 8 already-applied ops get reprocessed because the batch key was different on retry. Dedup belongs on the individual opId, always.

Delta sync: the actual JSON

Push request (client → server)

{ "deviceId": "dev-8842", "clientSeqRange": { "from": 4101, "to": 4104 }, "sinceServerSeq": 20713, "ops": [ { "opId": "b3f1e0c2-0a1d-4c7e-9f21-2a6d5b1c8e40", "recordId": "job-4471", "type": "update", "clientSeq": 4102, "baseServerSeq": 20701, "fields": { "status": { "old": "inprogress", "new": "complete" }, "laborminutes": { "old": 90, "new": 135 } }, "dependsOn": [] }, { "opId": "a71c9d55-6b2e-49aa-8f0c-1d2e3f405162", "recordId": "part-usage-9920", "type": "create", "clientSeq": 4103, "baseServerSeq": null, "fields": { "job_id": { "old": null, "new": "job-4471" }, "sku": { "old": null, "new": "CAP-47uf" }, "qty": { "old": null, "new": 2 } }, "dependsOn": ["att-5501"] } ] }

Push response (server → client)

{ "results": [ { "opId": "b3f1e0c2-0a1d-4c7e-9f21-2a6d5b1c8e40", "status": "applied", "serverSeq": 20714, "recordId": "job-4471" }, { "opId": "a71c9d55-6b2e-49aa-8f0c-1d2e3f405162", "status": "deferred", "reason": "dependency_pending", "missing": ["att-5501"] } ], "newServerSeq": 20714 }

deferred matters here: the part-usage op depends on an attachment (att-5501, maybe a photo of the installed part) that hasn't finished uploading. The server holds it rather than rejecting, and the client retries once the attachment acks.

Delta pull response

{ "changes": [ { "recordId": "job-4471", "serverSeq": 20714, "type": "update", "payload": { "status": "complete", "laborminutes": 135 }, "fieldMeta": { "status": { "seq": 20714 }, "laborminutes": { "seq": 20714 } } }, { "recordId": "job-4409", "serverSeq": 20715, "type": "delete", "tombstone": { "deletedServerSeq": 20715 } } ], "newServerSeq": 20715, "hasMore": false }

When the client pulls with sinceServerSeq, it gets forward-only changes.

Conflict payloads and deterministic tie-breakers

When two devices edit the same field against the same base, the server must reject one deterministically. Determinism is the whole point — the same conflict must resolve the same way regardless of arrival order, or you get split-brain across devices.

{ "opId": "c9d2...", "status": "conflict", "recordId": "job-4471", "conflicts": [ { "field": "status", "clientOld": "inprogress", "clientNew": "complete", "serverCurrent": "onhold", "serverSeq": 20720, "winner": "server" } ] }

Tie-breaker rules, in strict order

1. 1
   Field priority class. Some fields have domain rules. A status of cancelled beats complete (you don't complete a cancelled job). Encode these as explicit precedence tables per field, not general logic.
2. 2
   Higher server_seq wins for fields with no domain rule. The change the server already committed stays; the incoming stale change loses.
3. 3
   Deterministic tiebreak on opId (lexicographic) only when seqs are genuinely equal, which is rare but must not deadlock.

The mistake is mixing "newest wins" with "server wins" inconsistently. Pick server-seq as the spine, layer domain precedence on top, and never fall through to timestamps.

Resumable attachment upload protocol

Photos and signatures are where offline sync quietly dies. A 30MB inspection photo over a 200kbps connection at a rural site will fail mid-upload constantly. The protocol has to resume, not restart.

The flow

1. 1
   Register the attachment. Client generates attId, computes a whole-file SHA-256, declares total size and chunk size (256KB works well for field networks).
2. 2
   Server responds with an upload session and, critically, which chunks it already has (for resume):
3. 3
   Client uploads only missing chunks. Each chunk carries its own checksum so a corrupted chunk fails alone:
4. 4
   Finalize. Once all chunks land, client calls finalize. Server reassembles, verifies the whole-file sha256. Mismatch → server names the suspect chunks and asks for re-upload of just those.
5. 5
   Dependency release. Only after finalize succeeds does any op with dependsOn: ["att-5501"] become eligible to apply. This is why the part-usage op earlier came back deferred.

Diagram of the resumable attachment upload workflow.

{ "attId": "att-5501", "recordId": "part-usage-9920", "totalBytes": 5242880, "chunkBytes": 262144, "sha256": "9f2c...ab", "chunkCount": 20 }

{ "attId": "att-5501", "uploadUrl": "/attachments/att-5501/chunks", "receivedChunks": [0, 1, 2, 3, 4], "expiresAt": 1730000000 }

{ "attId": "att-5501", "chunkIndex": 5, "chunkSha256": "1a4f...", "bytes": "<base64 or binary>" }

Once all chunks land, client calls finalize. Server reassembles, verifies the whole-file sha256. Mismatch → server names the suspect chunks and asks for re-upload of just those.

Only after finalize succeeds does any op with dependsOn: ["att-5501"] become eligible to apply. This is why the part-usage op earlier came back deferred.

Dependency rules that matter

1. 1
   An op referencing an attachment must never apply before the attachment finalizes. Half-committed records pointing at nonexistent files are genuinely painful to debug.
2. 2
   Attachments must be content-addressed by hash so the same photo captured and retried isn't stored twice.
3. 3
   Chunk sessions need an expiry, but make it generous — 72 hours minimum. Techs finish uploads days later from the shop parking lot.

These three rules aren't optional. Skip any one of them and you'll eventually end up with records in a state that's nearly impossible to reconstruct after the fact.

Server behavior for duplicate ops (explicit)

Because this is where people cut corners, here it is spelled out:

1. 1
   Duplicate opId, already applied → return the original applied result with the original server_seq. Never a new seq.
2. 2
   Duplicate opId, was previously rejected/conflict → return the same rejection. Don't re-evaluate; the record may have moved on and you'd produce a different answer for the same op, breaking determinism.
3. 3
   Duplicate opId, currently deferred → return deferred again with current missing dependencies.
4. 4
   Unknown opId referencing a recordid that was tombstoned → return rejected with reason recorddeleted, include the tombstone seq so the client can drop its local copy.

Unknown opId referencing a recordid that was tombstoned → return rejected with reason recorddeleted, include the tombstone seq so the client can drop its local copy.

One end-to-end example

Walk the whole loop for a single realistic scenario: a tech marks job 4471 complete and logs extra labor, while a dispatcher put it on hold from the office.

1. 1
   1. Client op (offline). Tech taps Complete. Device writes an op:
2. 2
   2. Connectivity returns, client pushes. The batch goes up with baseServerSeq: 20701.
3. 3
   3. Server processing. Server looks up job-4471. Its current serverseq is 20720 — the dispatcher's on-hold change landed at 20720 with status = onhold. The incoming op's base is 20701. Overlap on status. Domain precedence table says on_hold blocks complete. Server rejects the field.
4. 4
   4. Ack (conflict).
5. 5
   5. Client reconcile. Device marks the op rejected, pulls delta from sinceServerSeq, receives job-4471 at seq 20720 with status = onhold. It overwrites local status, sets dirty = 0, and surfaces a non-blocking notice to the tech: "This job was placed on hold by dispatch — your completion wasn't recorded." The tech's laborminutes change, if it had been in the op and didn't conflict, would have merged silently.

{ "opId": "b3f1e0c2-...-8e40", "recordId": "job-4471", "type": "update", "clientSeq": 4102, "baseServerSeq": 20701, "fields": { "status": { "old": "in_progress", "new": "complete" } } }

Local records row updates immediately (status = complete, dirty = 1) so the UI is responsive. Op sits in op_log as pending.

The incoming ack is a conflict and the device reconciles by pulling the newer server_seq, applying non-conflicting merges, and surfacing a non-blocking notice to the tech.

QA test matrix

Prioritized, with exact expected server responses and pass/fail lines. Run top-down; the early tests catch the failures that actually corrupt data.

P0 — data integrity

1. 1
   Replay of an applied op. Push op, get applied at seq N, push identical op again. - Expected: second response applied, same seq N. - Pass: no new seq, no duplicate record change. Fail: new seq issued.
2. 2
   Double part consumption. Create part_usage op, ack it, kill app before marking acked locally, relaunch, resync. - Expected: server dedups on opId, inventory decremented once. - Pass: single deduction. Fail: qty deducted twice.
3. 3
   Op against tombstoned record. Delete record on device A (server acks), device B (stale) pushes update to same record. - Expected: rejected, reason record_deleted, tombstone seq returned. - Pass: device B drops record. Fail: record resurrected.

P1 — conflict correctness

1. 1
   Overlapping-field conflict. Two devices set status from same base. - Expected: deterministic winner by domain rule / higher server_seq; loser gets conflict payload with both values. - Pass: same winner regardless of push order. Fail: winner depends on arrival order.
2. 2
   Non-overlapping merge. Device A edits labor_minutes, device B edits notes, same base. - Expected: both applied, both fields present in final record. - Pass: no false conflict raised. Fail: one edit lost.

P1 — attachments

1. 1
   Resume after 60% failure. Upload 20 chunks, kill network at chunk 12, reconnect. - Expected: session returns receivedChunks [0–11], client sends 12–19 only. - Pass: no re-upload of 0–11. Fail: full restart.
2. 2
   Corrupt chunk. Flip a byte in chunk 7 before send. - Expected: finalize fails whole-file hash, server names chunk 7 (or all suspect), client re-sends only that. - Pass: targeted re-send. Fail: silent corruption accepted.
3. 3
   Dependency ordering. Push op with dependsOn on an unfinalized attachment. - Expected: op deferred, applies only after finalize. - Pass: record never points at missing file. Fail: op applied early.

P2 — ordering & cursors

1. 1
   Out-of-order clientseq. Force ops to arrive 4103 before 4102. - Expected: server applies per clientseq order for that device. - Pass: final state matches in-order application.
2. 2
   Delta cursor gap. Pull with sinceServerSeq far behind, verify hasMore paging. - Expected: forward-only changes, no gaps, cursor advances monotonically. - Pass: no missed or duplicated records across pages.

These ten tests won't catch everything, but they'll catch the bugs that actually show up in production and are hardest to reproduce after the fact.

Platform notes (Android / iOS)

Short, because the protocol is platform-agnostic, but a few patterns save real grief:

1. 1
   Background upload

   On iOS use URLSession background configuration for attachment chunks so uploads survive app suspension. On Android use WorkManager with a unique work name per attId and a network constraint — don't roll your own retry loop.

2. 2
   The op log must be crash-durable. Write ops to SQLite in the same transaction that updates the records row. If those two writes can diverge, you'll ship the "UI shows saved, server never got it" bug.
3. 3
   Persist client_seq outside app storage that gets wiped on reinstall where feasible (Keychain / Keystore-backed). A reset counter causes ordering ambiguity.
4. 4
   Never use the device clock for conflict logic — only for display. Field devices drift, techs manually change time, and you'll get non-deterministic tie-breaks.
5. 5
   Batch size

   keep push batches small (10–20 ops) so a flaky connection commits something instead of timing out on a giant payload.

Most of these are easy to get right once and forget about. The dangerous ones are the op log durability and the clock dependency — both tend to look fine in testing and only surface under real field conditions.

Where this pays off

The value of nailing this spec isn't theoretical cleanliness. A technician on a bad connection in a basement mechanical room can finish a job, log parts, attach photos, and drive off — and everything reconciles correctly hours later without a dispatcher manually fixing records or an inventory count going sideways.

Teams that get burned are the ones who treated sync as "we'll push the JSON and sort out conflicts later." Later is a support ticket about a job that completed itself twice. Specify the op log, the idempotency keys, the deterministic tie-breakers, and the attachment dependency rules up front, and that whole class of field-data corruption bugs mostly stops existing. Pair this with the workflow-level thinking in designing offline-first mobile workflows, hand your QA the matrix above, and you've got something both engineering and testing can build against without reinterpreting a single line.