Handling Conflict Resolution

In this lesson, you will learn how to design strategies to reconcile concurrent local and remote changes that occur when an offline-first application reconnects to a central server.

When using an embedded database like SQLite, your local application acts as a temporary source of truth while disconnected. Once the network returns, you must merge local changes with the remote state. Conflict resolution is the process of deciding which version of the data prevails when the same record was modified in both places.

Common Resolution Strategies

1. Last Write Wins (LWW): This is the simplest strategy. Each record should include a last_modified timestamp. Upon reconnection, you compare your local timestamp with the remote one; the most recent update is kept.
2. Semantic Merging: Instead of replacing the entire record, you apply specific logic. For example, if both the local and remote versions changed a price field, you might choose to take the average or the higher value.
3. Human Intervention (Versioning): You flag the record as a "conflict" in your database and prevent further automated sync for that specific item until the user manually chooses which version to keep.

Implementation Pattern

To manage this, ensure every table has a version_id (or incrementing counter) and a sync_status flag. When you push your local changes to the remote server, your SQL query should check the version before committing:

-- Attempt to update only if the remote version matches our base version
UPDATE products 
SET price = :new_price, version_id = version_id + 1
WHERE id = :id AND version_id = :local_version_at_time_of_edit;

-- If rows_affected == 0, a conflict occurred!

If the UPDATE fails, you know the remote record has changed since your last fetch. At this point, you trigger your resolution strategy: either force the local change (overwriting), pull the new remote state to update your local UI, or prompt the user. Because we are using SQLite locally, you can use temporary tables to hold the "incoming" remote state while you compare it against your local row, ensuring you don't lose user data during the merge process.

Key Takeaway: Effective conflict resolution requires metadata—like timestamps or version counters—to detect when concurrent changes occur, allowing you to choose between automated overwrites or manual user intervention.