I think this is a really compelling pattern for converting traditional server rendered apps to be offline enabled (or choosing to build new ones this way). I have seen similar examples using Python.
Initially these are more of a “backed data” type app, with no offline public writes. [0]
The next step is to have local/offline SQLite to server syncing, enabling writes while offline that are synced back to the server on reconnection. This would inherently be eventually consistent and brings its own issues with relational data integrity, however the SQLite Session Extension could be I good way to build such a system [1]
The alternative is a new data store based on CRDTs (conflict free merging of offline edits), there are some exciting developments happening in that area. I have seen it suggested that the SQLite session extension is almost a crdt if you squint at it.
This is certainly a cool idea, but I don't understand a few things about how reality would work with an async reconciliation process on the database layer:
1.) How would the auto-incremented ID fields be merged in such a system?
2.) How are unique constraint violations handled?
Don't use auto-increment. Use a ULID or some other algorithm driven PK