A modern programming language that makes developers happier.

kotlinlang

Hi,I am building an *application* with a *local SQLite* database and a *remote PostgreSQL server* using *Ktor* and *Exposed*. While the basic CRUD operations and authentication are working well, I am struggling to implement the *synchronization* between the *local* and *server* databases. I've encountered many *race conditions* and *bugs*, and the *synchronization logic* is becoming *messy*. Could anyone recommend a *design pattern* or *architecture* specifically for handling *client-server data synchronization*? _Here the file for more context:_ <https://github.com/yassineAbou/LLMS/blob/master/composeApp/src/commonMain/kotlin/org/yassineabou/llms/app/core/data/async/AsyncManagerImpl.kt|asyncManagerImp>

(Disclosure: I'm being paid to work on this) <https://www.powersync.com/|PowerSync> is a sync engine that supports Kotlin multiplatform with SQLite on the client and Postgres as a backend database.

The rough architecture is that a client uses SQLite update hooks for reactive queries as a source of truth and then the sync engine can stream consistent snapshots from the backend database as transactions. Local writes are collected and can be uploaded to your backend later, the client knows when writes are supposed to have made it to the backend database to then reconcile sync state. This isn't a plug, so even if you don't want to consider PowerSync at all I can send some public design docs describing how it works. But it is a lot of effort to get usable consistency guarantees working.

thank you <@U08J4JWSHD5>  for the suggestion. PowerSync looks  a powerful, but for this specific pet project, I’ve decided to implement the synchronization logic myself for learning purpose . I would, however, be very interested in those public design docs you mentioned.

Your schema doesn't look that complicated. there are no update queries either, only create and delete as far as I can tell?
there are two simple options for pulling: 1) you either add a change log table that records all CRUD events, with an autoincrementing pk or 2) you utilise updated_at/deleted_at columns on all rows (and only soft delete rows; row is deleted if deleted_at is not null).
so a client would fetch like /chats?updated_after=xxx and you filter at db level all rows/entities that have changed after the parameter.
for pushing, if you even make changes locally while being offline, similarly you can keep a change log table or something like a 'pending_push' column.
overall, you should utilise your database layer more.

should you need column level merging, jsonb is great for storing timestamp metadata and you can write a plpgsql function to do the merging at the db layer

Hi Dominik, thank you for the help, I'll try to implement your approach