I’ve been playing with electric-sql for the reading-log prototype. The pitch — Postgres on the server, SQLite on the client, with bidirectional sync of a subset of tables — sounds straightforward; the mental model is anything but, at least at first.
The shift is realising that the local database isn’t a cache of the server. It’s a peer. The ‘source of truth’ isn’t a server; it’s the convergent state across all peers, computed by deterministic CRDT semantics. Reads always go local. Writes always go local. Sync happens in the background, eventually, and the API of your app doesn’t change whether the network is there or not.
I tested this on the train home, which is the platonic ideal of a flaky network. Pages loaded. Edits saved. New notes appeared. When the connection came back at Central, the screen updated without me noticing, and a colleague’s edit from earlier in the afternoon appeared. It felt, briefly, like magic. It felt like what we were promised by progressive web apps and didn’t quite get.
The catch — and there’s always a catch — is that the model leaks when you need cross-row constraints. ‘This user can have at most three pinned notes’ is hard to enforce when two devices can each independently pin a fourth and then sync. The escape hatch is server-authoritative writes for those constraints, which is fine, but it means the local-first model is necessary-and-sufficient for less of your schema than you’d like.
Still — for everything that can be local-first, it should be. The UX delta is significant, and once you’ve felt it, building a spinner-driven app feels like a regression.