Improve client-server data sync functionality with deltas

Question

The app
I have a web app that currently uses AppCache for offline functionality since users of the system need to create documents offline. The document is first created offline and when internet access is available, the user can click "sync" which will send the document to the server and save it as a revision. To be more specific, the app does not save the change delta as a revision (the exact field modified) but rather the whole document in its entirety. So in other words, a "snapshot" document is saved.

The problem
Users can login from different browsers and devices and work on their documents. When they click "sync", if the server's document is newer, the entire client's version will be overridden by the server's. This leads to one main issue that is depicted in the image below.

The scenario above occurs because of the current implementation which does not rely on deltas (small changes) and rather relies on snapshot revisions.

Some questions

1) My research indicates that I should be upgrading the "sync" mechanism to be expressed in deltas (small changes that can be applied independently). Is this a sound approach?

2) Should each delta be applied independently?

2) According to my research, revision deltas have a numeric value and not a timestamp. What should the value for this be exactly? How would I ensure both the server and the client agree on what the revision number should be?

Stack information

• Angular on the frontend
• IndexedDB to save documents locally (offline mode)
• Postgres DB with JSONB in the backend

Answer 1

What your describing is a version control issue like in this question. The choice is yours with how to resolve. Here are a few examples of other products with this problem:

• Google docs: A makes edit offline, B makes edit online, A goes online, Sync, Google Docs combines A and B's edits
• Apple notes: Same as Google Docs
• Git/Subversion: Throw an error, ask user to resolve conflicts
• Wunderlist: Last edit overwrites previous

For your case, this simplest solution is to use Wunderlist's approach, but it seems that may cause a usability issue. What do your users expect to happen?

Answering your questions directly:

1. A custom sync implementation is necessary if you don't want overwrites.
2. This is a usability decision, what does the user expect?
3. True, revisions are numeric (e.g r1, r2). To get server agreement, alter the return value of the last sync request. You can return the entire model to the client each time (or just a 200 OK if a normal sync happened). If a model is returned to the client, update the client with the latest model.

In any case, the server should always be the source of truth. This post provides some good advice on server/mobile referential integrity:

To track inserts you need a Created timestamp ... To track updates you need to track a LastUpdate timestamp on your rows ... To track deletes you need a tombstone table.

Note that when you do a sync, you need to check the time offset between the server and the mobile device, and you need to have a method for resolving conflicts. Inserts are no big deal (they shouldn't conflict), but updates could conflict, and a delete could conflict with an update.