Strategies for sync between server and client

[gedw99]

I’m thinking about architecture patterns for sync.

I am wondering if anyone has any thoughts on this. , as I know how to implement this , but I am sure that I have missed a logic loop somewhere ..

Here we go ….

You could have a server db per user , so it’s also a backup, and so sync with that. This when a user goes onto a new device, they pull the db from the server user db .

On the server a A SQLite WAL , would keep track of changes , and then push cdc updates to all of a users devices to ensure they all catchup eventually as they go online and offline. This seems to me like a solid solution.

Until we get to Schema migrations though. This is much harder because you have 1 server db and x user device db’s. Each of a users devices could be at different schema versions , and doing actual mutations.

Option 1: Forbid mutations unless on latest schema . Nope because if your offline , you have no idea what truely is the latest schema version back on the users server db. Maybe there is some trick I am missing here though, but I’m lost to know what it may be . Quantum data entanglement :)

Option 2: something like a CRDT db that has schema versioning too . Only corrosion does it , but they are also still trying to have a solid schema migration story.

https://github.com/superfly/corrosion?tab=readme-ov-file#how-corrosion-works

Either way, the main server db, then hold global read only data, country data , etc . When I say read only, I mean read only for the uses db ( both the server one and their many client ones ). When an admin change the main server db data , It would update each users server db, using the same WAL / CDC mechanism .. then each users client db would get the changes. Schema migrations are forwarded to with no issues too . In the users DB these tables are locked from them changing them .

The other Doozy is mutations . On a users client db , offline edits are going to happen , and so you can save the mutation as an event, play it into the local client db, and save it in an event store , ready to be sent to the server db, when your online . This would actually work without collisions because a user only uses 1 device at a time , so you removed a tricky problem by maintaining a server db for each user.

—-
As far as sync transports. I am partial to SSE , pushing up into each users web worker . It’s just clean as long as there is a sequence associated with each push to ensure de duplication .

Server user db backups are easy to litestream etc , to S3 for every mutation etc