Offline-first form conflict resolution for Kotlin + SQLite

What actually happens when two people edit offline

Offline-first forms let people view and edit data even when the network is slow or unavailable. Instead of waiting for the server, the app writes changes to a local SQLite database first, then syncs later.

That feels instant, but it creates a simple reality: two devices can change the same record without knowing about each other.

A typical conflict looks like this: a field tech opens a work order on a tablet in a basement with no signal. They mark the status as "Done" and add a note. At the same time, a supervisor on another phone updates the same work order, reassigns it, and edits the due date. Both hit Save. Both saves succeed locally. Nobody did anything wrong.

When sync finally happens, the server has to decide what the "real" record is. If you don’t handle conflicts explicitly, you usually end up with one of these outcomes:

• Last write wins: the later sync overwrites earlier changes and someone loses data.
• Hard failure: sync rejects one update and the app shows an unhelpful error.
• Duplicate records: the system creates a second copy to avoid overwriting and reporting gets messy.
• Silent merge: the system combines changes, but mixes fields in ways users don’t expect.

Conflicts aren’t a bug. They’re the predictable result of letting people work without a live connection, which is the whole point of offline-first.

The goal is twofold: protect data and keep the app easy to use. That usually means clear merge rules (often at the field level) and a user experience that interrupts people only when it truly matters. If two edits touch different fields, you can often merge quietly. If two people change the same field in different ways, the app should make that visible and help someone choose the right outcome.

Choose a conflict strategy that matches your data

Conflicts aren’t a technical problem first. They’re a product decision about what "correct" means when two people changed the same record before sync.

Three strategies cover most offline apps:

• Last write wins (LWW): accept the newest edit and overwrite the older one.
• Manual review: stop and ask a human to choose what to keep.
• Field-level merge: combine changes per field and only ask when two people touched the same field.

LWW can be fine when speed matters more than perfect accuracy and the cost of being wrong is low. Think internal notes, non-critical tags, or a draft status that can be edited again later.

Manual review is the safer choice for high-impact fields where the app shouldn’t guess: legal text, compliance confirmations, payroll and invoicing amounts, banking details, medication instructions, and anything that can create liability.

Field-level merge is usually the best default for forms where different roles update different parts. A support agent edits the address while sales updates the renewal date. A per-field merge keeps both changes without bothering anyone. But if both users edited the renewal date, that field should trigger a decision.

Before you implement anything, write down what "correct" means for your business. A quick checklist helps:

• Which fields must always reflect the latest real-world value (like current status)?
• Which fields are historical and should never be overwritten (like a submitted-at time)?
• Who is allowed to change each field (role, ownership, approvals)?
• What’s the source of truth when values disagree (device, server, manager approval)?
• What happens if you choose wrong (minor annoyance vs financial or legal impact)?

When these rules are clear, the sync code has one job: enforce them.

Define merge rules per field, not per screen

When a conflict happens, it rarely affects the whole form evenly. One user might update a phone number while another adds a note. If you treat the entire record as all-or-nothing, you force people to redo good work.

Field-level merge is more predictable because each field has a known behavior. The UX stays calm and fast.

A simple way to start is to separate fields into "usually safe" and "usually unsafe" categories.

Usually safe to merge automatically: notes and internal comments, tags, attachments (often union), and timestamps like "last contacted" (often keep the latest).

Usually unsafe to merge automatically: status/state, assignee/owner, totals/prices, approval flags, and inventory counts.

Then pick a priority rule per field. Common choices are server wins, client wins, role wins (for example, manager overrides agent), or a deterministic tie-breaker like newest server version.

The key question is what happens when both sides changed the same field. For each field, choose one behavior:

• Auto-merge with a clear rule (for example, tags are a union)
• Keep both values (for example, append notes with author and time)
• Flag for review (for example, status and assignee require a choice)

Example: two support reps edit the same ticket offline. Rep A changes status from "Open" to "Pending". Rep B changes notes and adds a tag "refund". On sync, you can safely merge notes and tags, but you shouldn’t silently merge status. Prompt only for status, with everything else already merged.

To prevent debates later, document each rule in one sentence per field:

• "notes: keep both, append newest last, include author and time."
• "tags: union, remove only if explicitly removed on both sides."
• "status: if changed on both sides, require user choice."
• "assignee: manager wins, otherwise server wins."

That one-sentence rule becomes the source of truth for Kotlin code, SQLite queries, and the conflict UI.

Data model basics: versions and audit fields in SQLite

If you want conflicts to feel predictable, add a small set of metadata columns to every synced table. Without them, you can’t tell whether you’re looking at a fresh edit, an old copy, or two edits that need a merge.

A practical minimum for each server-synced record:

• id (stable primary key): never reuse it
• version (integer): increments on every successful write on the server
• updated_at (timestamp): when the record was last changed
• updated_by (text or user id): who made the last change

On the device, add local-only fields to track changes that haven’t been confirmed by the server:

• dirty (0/1): local changes exist
• pending_sync (0/1): queued to upload, but not confirmed
• last_synced_at (timestamp): last time this row matched the server
• sync_error (text, optional): last failure reason to show in UI

Optimistic concurrency is the simplest rule that prevents silent overwrites: every update includes the version you think you’re editing (an expected_version). If the server record is still at that version, the update is accepted and the server returns the new version. If not, it’s a conflict.

Example: User A and User B both downloaded version = 7. A syncs first; the server bumps to 8. When B tries to sync with expected_version = 7, the server rejects with a conflict so B’s app merges instead of overwriting.

For a good conflict screen, store the shared starting point: what the user originally edited from. Two common approaches:

• Store a snapshot of the last synced record (one JSON column or a parallel table).
• Store a change log (row-per-edit or field-per-edit).

Snapshots are simpler and often enough for forms. Change logs are heavier, but can explain exactly what changed, field by field.

Either way, the UI should be able to show three values per field: the user’s edit, the server’s current value, and the shared starting point.

Record snapshots vs change logs: pick one approach

When you sync offline-first forms, you can upload the full record (a snapshot) or upload a list of operations (a change log). Both work with Kotlin and SQLite, but they behave differently when two people edit the same record.

Option A: Whole-record snapshots

With snapshots, every save writes the latest full state (all fields). On sync, you send the record plus a version number. If the server sees the version is old, you have a conflict.

This is simple to build and fast to read, but it often creates larger conflicts than necessary. If User A edits the phone number while User B edits the address, a snapshot approach can treat it as one big clash even though the edits don’t overlap.

Option B: Change logs (operations)

With change logs, you store what changed, not the entire record. Each local edit becomes an operation you can replay on top of the latest server state.

Operations that are often easier to merge:

• Set a field value (set email to a new value)
• Append a note (adds a new note item)
• Add a tag (adds one tag to a set)
• Remove a tag (removes one tag from a set)
• Mark a checkbox complete (set isDone true with a timestamp)

Operation logs can reduce conflicts because many actions don’t overlap. Appending notes rarely conflicts with someone else appending a different note. Tag adds and removes can merge like set math. For single-value fields, you still need per-field rules when two different edits compete.

The tradeoff is complexity: stable operation IDs, ordering (local sequence and server time), and rules for operations that don’t commute.

Cleanup: compacting after successful sync

Operation logs grow, so plan how to shrink them.

A common approach is per-record compaction: once all operations up to a known server version are acknowledged, fold them into a new snapshot, then delete those older operations. Keep a short tail only if you need undo, auditing, or easier debugging.

Step-by-step sync flow for Kotlin + SQLite

A good sync strategy is mostly about being strict with what you send and what you accept back. The goal is simple: never overwrite newer data by accident, and make conflicts obvious when you can’t safely merge.

A practical flow:

1. Write every edit to SQLite first. Save changes in a local transaction and mark the record as pending_sync = 1. Store local_updated_at and the last known server_version.

2. Send a patch, not the whole record. When connectivity returns, send the record id plus only the fields that changed, along with expected_version.

3. Let the server reject mismatched versions. If the server’s current version doesn’t match expected_version, it returns a conflict payload (server record, proposed changes, and which fields differ). If versions match, it applies the patch, increments the version, and returns the updated record.

4. Apply auto-merge first, then ask the user. Run field-level merge rules. Treat safe fields like notes differently from sensitive fields like status, price, or assignee.

5. Commit the final result and clear pending flags. Whether it was auto-merged or manually resolved, write the final record back to SQLite, update server_version, set pending_sync = 0, and record enough audit data to explain what happened later.

Example: two sales reps edit the same order offline. Rep A changes the delivery date. Rep B changes the customer phone number. With patches, the server can accept both changes cleanly. If both changed the delivery date, you surface one clear decision instead of forcing a full re-entry.

Keep the UI promise consistent: "Saved" should mean saved locally. "Synced" should be a separate, explicit state.

UX patterns for resolving conflicts in forms

Conflicts should be the exception, not the normal flow. Start by auto-merging what’s safe, then ask the user only when a decision is truly needed.

Make conflicts rare with safe defaults

If two people edit different fields, merge without showing a modal. Keep both changes and show a small "Updated after sync" message.

Reserve prompts for true collisions: the same field changed on both devices, or a change depends on another field (like status plus status reason).

When you must ask, make it quick to finish

A conflict screen should answer two things: what changed, and what will be saved. Compare values side by side: "Your edit", "Their edit", and "Saved result". If only two fields conflict, don’t show the entire form. Jump straight to those fields and keep the rest read-only.

Keep the actions limited to what people actually need:

• Keep mine
• Keep theirs
• Edit final
• Review field-by-field (only when needed)

Partial merges are where UX gets messy. Highlight only the conflicting fields and label the source clearly ("Yours" and "Theirs"). Preselect the safest option so the user can confirm and move on.

Set expectations so users don’t feel trapped. Tell them what happens if they leave: for example, "We’ll keep your version locally and retry sync later" or "This record will stay in Needs review until you choose." Make that state visible in the list so conflicts don’t get lost.

If you’re building this flow in AppMaster, the same UX approach holds: auto-merge safe fields first, then show a focused review step only when specific fields collide.

Tricky cases: deletes, duplicates, and "missing" records

Most sync issues that feel random come from three situations: someone deletes while someone else edits, two devices create the "same" thing offline, or a record disappears and then reappears. These need explicit rules because LWW often surprises people.

Delete vs edit: who wins?

Decide whether a delete is stronger than an edit. In many business apps, delete wins because users expect a removed record to stay removed.

A practical rule set:

• If a record is deleted on any device, treat it as deleted everywhere, even if there are later edits.
• If deletes must be reversible, convert "delete" into an archived state instead of a hard delete.
• If an edit arrives for a deleted record, keep the edit in history for audit, but don’t restore the record.

Offline creation collisions and duplicate drafts

Offline-first forms often create temporary IDs (like a UUID) before the server assigns a final ID. Duplicates happen when users create two drafts for the same real-world thing (the same receipt, the same ticket, the same item).

If you have a stable natural key (receipt number, barcode, email plus date), use it to detect collisions. If you don’t, accept that duplicates will happen and provide a simple merge option later.

Implementation tip: store both local_id and server_id in SQLite. When the server responds, write a mapping and keep it at least until you’re sure no queued changes still reference the local ID.

Preventing "resurrection" after sync

Resurrection happens when Device A deletes a record, but Device B is offline and later uploads an old copy as an upsert, recreating it.

The fix is a tombstone. Instead of removing the row immediately, mark it deleted with deleted_at (often also deleted_by and delete_version). During sync, treat tombstones as real changes that can override older non-deleted states.

Decide how long to keep tombstones. If users can be offline for weeks, keep them longer than that. Purge only after you’re confident active devices have synced past the delete.

If you support undo, treat undo as another change: clear deleted_at and bump the version.

Common mistakes that cause data loss or user frustration

Many sync failures come from small assumptions that quietly overwrite good data.

Mistake 1: Trusting device time to order edits

Phones can have wrong clocks, time zones change, and users can manually set time. If you order changes by device timestamps, you’ll eventually apply edits in the wrong order.

Prefer server-issued versions (monotonically increasing serverVersion) and treat client timestamps as display-only. If you must use time, add safeguards and reconcile on the server.

Mistake 2: Accidental LWW on sensitive fields

LWW seems simple until it hits fields that shouldn’t be "won" by whoever syncs later. Status, totals, approvals, and assignments usually need explicit rules.

A safety checklist for high-risk fields:

• Treat status transitions as a state machine, not a free-text edit.
• Recompute totals from line items. Don’t merge totals as raw numbers.
• For counters, merge by applying deltas, not by picking a winner.
• For ownership or assignee, require explicit confirmation on conflicts.

Mistake 3: Overwriting newer server values with stale cached data

This happens when the client edits an old snapshot, then uploads the full record. The server accepts it and newer server-side changes disappear.

Fix the shape of what you send: send only changed fields (or a change log), plus the base version you edited. If the base version is behind, the server rejects or forces a merge.

Mistake 4: No "who changed what" history

When conflicts happen, users want one answer: what did I change, and what did the other person change? Without editor identity and per-field changes, your conflict screen becomes guesswork.

Store updatedBy, server-side update time if you have it, and at least a lightweight per-field audit trail.

Mistake 5: Conflict UI that forces full-record comparisons

Making people compare entire records is exhausting. Most conflicts are only one to three fields. Show only the conflicting fields, preselect the safest option, and let the user accept the rest automatically.

If you’re building forms in a no-code tool like AppMaster, aim for the same outcome: resolve conflicts at the field level so users make one clear choice instead of scrolling through the whole form.

Quick checklist and next steps

If you want offline edits to feel safe, treat conflicts as a normal state, not an error. The best results come from clear rules, repeatable tests, and UX that explains what happened in plain language.

Before you add more features, make sure these basics are locked in:

• For each record type, assign a merge rule per field (LWW, keep max/min, append, union, or always ask).
• Store a server-controlled version plus an updated_at you control, and validate them during sync.
• Run a two-device test where both edit the same record offline, then sync in both orders (A then B, B then A). The outcome should be predictable.
• Test the hard conflicts: delete vs edit, and edit vs edit on different fields.
• Make state obvious: show Synced, Pending upload, and Needs review.

Prototype the full flow end-to-end with one real form, not a demo screen. Use a realistic scenario: a field tech updates a job note on a phone while a dispatcher edits the same job title on a tablet. If they touch different fields, auto-merge and show a small "Updated from another device" hint. If they touch the same field, route to a simple review screen with two choices and a clear preview.

When you’re ready to build the full mobile app and the backend APIs together, AppMaster (appmaster.io) can help. You can model data, define business logic, and build web and native mobile UIs in one place, then deploy or export source code once your sync rules feel solid.