Designing offline-first sync for a multi-user desktop app that must keep working through network outages

The problem with your design is that you sync the DB rather than the atomic "commands"

This is a common approach when people write an app and just want to make the db persist through reinstalls or new device installs, but with your shared data db it will be problematic.

If however you put an API in front of your DB and ensure the endpoints are designed with off line potentially conflicting edits in mind, you can eliminate most if not all of your conflict resolution.

For example :

• user 1 deletes and entry and children,

• user 2 edits said entry's children.

• user 1 back online

  entry and children deleted from db

• user 2 back on line

  insert child where parentid = .. fails because parent no longer exists

But consider you have an API endpoint which is idempotent and takes as input fully populated object graphs. Now:

• user 1 deletes entry

• user 2 sends full entry with added child to update endpoint

• user 1 back online

  delete command is synced and entry + children deleted from db

• user 2 back online

  upsert command is synced and entry + children is re-added to db

Storing the commands also gives you an easy access point if you do want to add a conflict resolution UI as you can see both updates and compare. ie "while you were offline user 1 edited the same entry as you with the following changes"

After syncing their commands, each device should refresh its own database to match the server.

It's possible to use an off the shelf product here, but you might have to maintain two versions of the database.

1. The one updated with the offline commands

   this is discarded after syncing

2. The one synced with the server

   This is kept to allow simple delta updates and to leave the conflict resolution to the server.

Although this might seem similar to your Option 2. I think you will need to make your own bespoke command API and design your entities and boundaries specifically to your use cases.

Attempting to squeeze your shared updating into a generic off the shelf solution will be tricky and may end up with a fragile solution.