[maccaw]

I have a genuine appreciation for how Linear has built this. We have had to build something similar for our note taking application (Reflect). It is very tricky to do and I wish there was more research on this.

In my opinion, what we need is:

1) A client-side performant SQLite database that supports live queries. I.e. you can automatically re-render the page when the queries change. That way your database can drive the UI and be the source of truth in regards to what's displayed on the screen.

2) A separate realtime syncing protocol that syncs database state to client state.

And ideally this is all open source, and that these two endeavors are not coupled tightly.

[1] Wa-sqlite is the best (imo) client-side db - better than than the official Sqlite WASM build (for now) because it had a indexeddb fallback for browsers that aren't the cutting edge Chrome.

[2] cr-sqlite is an interesting project using CRDTs to sync state around. However I still believe that for many production use-cases you want a ultimate server source of truth.

[3] Replicache is still the best closed source solution I know of.

[1] - https://github.com/rhashimoto/wa-sqlite [2] - https://github.com/vlcn-io/cr-sqlite [3] - https://replicache.dev/

[evnc]

You've probably already seen https://riffle.systems/essays/prelude/ - one of my favorite recent research projects in this space, it is very much in line with what you're describing. Sad that it didn't seem to go anywhere after a couple introductory posts.

[schickling]

We're still working on it! We'll hopefully have more to share soon!

In the meanwhile you might like this talk by Geoffrey: https://www.youtube.com/watch?v=zjl7CpG9h3w&pp=ygUNZ2VvZmZyZ...

[liuliu]

(Unfortunately, it is a 1-hour long video and I don't have time to watch it over).

The problem with 2 is often that the database is too far away from the view, hence, what you prioritize to sync might not be the most important thing the user interacts with. That's why there are ideas of syncing via GraphQL API because GraphQL is largely driving by client-side views, so it should have more knowledge on how to prioritize. But that often fails too.

This is not a problem if you are doing multiplayer semi-synchronous collaborations because the states of each player are mostly up-to-date. It is problematic if you are doing asynchronous collaborations and only sync with the server sparsely but have large amount of data to sync.

It is not a well-solved problem, but luckily I think the product surface requires this kind of "prioritized sync" is pretty small.

[samwillis]

ElectricSQL are in this space, and have the concept of a central source of truth. SQLite in the browser and Postgres on the server with two way syncing using CRDTs. All open source but backed and funded.

https://electric-sql.com/

I'm not convinced that separating the two tools is best for usability, loose coupling sure. But some level of integration enables the reactive front end querying to swap between the front end copy and central source depending on if you have the data copied locally.

Also many of the mechanisms needed for reactive re-querying are also needed for two way syncing.

[maccaw]

The trouble with coupling the two is that now I have to either trust some third party db service, or some custom db.

The DB is so fundamental, and so hard to get right / scale, that it's hard to trust a startup to do it well.

We use Firebase and even with all the resources google has this has a lot of issues (especially with the client libs).