Thank you for championing SQLite and especially OPFS Wasm SQLite on the web — we (PowerSync) are clearly also big proponents of it, so love to see other projects having success with it too.

Comments URL: https://news.ycombinator.com/item?id=43895974

Points: 2

# Comments: 0

1. At a database level: Using something like RLS in Postgres

2. At a backend level: The sync engine processes write operations via the backend API, where custom validation and authorization logic can be applied.

3. At a sync engine level: If the sync engine processes the write operations, there can be some kind of authorization layer similar to RLS enforced by the sync engine on the backend.

In my experience, it can affect the architecture and performance in a significant way. If a client can go offline for an arbitrary period of time, doing a delta sync when they come back online is more tricky, since we need to sync a specific range of operation history (and this needs to be adjusted for specific scope/permissions that the client has access to). If you scale up a system to thousands or millions of clients, having them all do arbitrary range queries doesn't scale well. For this reason I've seen sync engines simply force a client to do a complete re-sync if it "falls behind" with deltas for too long (e.g. more than a day or so.) Maintaining an operation log that is set up and indexed for querying arbitrary ranges of operations (for a specific scope of data) works well.

I agree with this. CRDTs are cool tech but I think in practice most folks would be surprised by the high percentage of use cases that can be solved with much simpler conflict resolution mechanism (and perhaps combined with server reconciliation as Matt mentioned). I also agree that collaborative document editing is a niche where CRDTs are indeed very useful.

I've spent 16 years working on a sync engine and have worked with hundreds of enterprises on sync use cases during this time. I've seen countless cases of developers underestimating the complexity of sync. In most cases it happens exactly as you said: start with a naive approach and then the fractal complexity spiral starts. Even if the team is able to do the initial implementation, maintaining it usually turns into a burden that they eventually find too big to bear.

This is one of the ideas that appears to be central to the genesis of Zero [1]

ElectricSQL allows for a similar pattern and PowerSync is also working on this [2]

[1] https://www.youtube.com/watch?v=rqOUgqsWvbw

[2] https://www.powersync.com/blog/powersync-2025-roadmap-sqlite...

We started with Postgres and added support for MongoDB after they deprecated Atlas Device Sync in September last year.

For the read path it hooks into Postgres logical replication or MongoDB change streams (and MySQL binlog soon). It supports partial syncing using declarative rules. For the write path, it allows writing to the local SQLite database and also places writes into an upload queue, and then uses a developer-defined function to upload writes to the backend API.

We did a deep dive on current options for SQLite on the web, and are currently using an IndexedDB-based VFS, and looking to move to OPFS: https://www.powersync.com/blog/sqlite-persistence-on-the-web

We recently released an integration with TanStack Query to allow leveraging some of its features in conjunction with PowerSync: https://docs.powersync.com/client-sdk-references/js-web/java...

> Meanwhile for “local-only” it would be great to use sqlite in the browser + native file system API so that the db could be stored on the user’s file system and we wouldn’t have to worry about browser storage eviction. i think that could really open up a whole world of privacy preserving offline software delivered through the browser

Agreed. This is a limitation of IndexedDB and OPFS as persistent browser storage currently

This community has been central to the idea of local-first: https://localfirstweb.dev/

The term local-first was coined by Martin Kleppmann et al in this essay: https://www.inkandswitch.com/local-first/