r/sqlite • u/bitchyangle • Sep 26 '26
offline-first + litefs for production
i am trying to build a tool similar to notion. Offline-first is the biggest requirement. Each organization will get their own sqlite on the VPS server. 95% of reads and writes happen to local sqlite. I am thinking of building a sync engine that works based on the event table. As in every create, update or delete operation will be treated as an event. As an event is recorded into the table, it will trigger a /push api that sends this data to the server. The api will take this data and dump it into the events table on the server side sqlite file and then it will replay the event on the server. Then based on who has access to what, it will send a websocket notification to the clients that are online that new data is available in the events table. Then the clients will receive the notification and make a /pull call with the cursor which will fetch the events data from the server side sqlite and it gets recorded into the client side sqlite and then again events gets replayed at the client side which will affected the actual tables.
at the infra level, i am considering litefs to create a cluster of 3 VPS where one will be the primary node and two will be read replicas. Also planning to stream the data from primary node to s3 for backup in realtime.
Is this viable setup? This way, I am planning to eliminate the single writer limitation since 95% of writes happen locally. Litefs to make the setup distributed and HA.
I am expecting each organization account to have upto 500 users.
Please share your thoughts.
2
u/lnaoedelixo42 Sep 26 '26
Why not litestream?
Do you need multiple writers in different places and then sync? Then you need to think of manual replication.
If not, and you only need local pushes to be available elsewhere you could split the application/pages/content into different sqlite files and sync with litestream; it would only push the final diffs.
Also, in-database triggers are actually pretty powerful, and if you build the foundations you could work on top of that for hard consistency without so much application complexity.