Comment by dlazaro

The self-hosted app Actual Budget [1] sort of takes the second approach, where each client maintains their own SQLite database in local storage. However, instead of just hosting a single file database, the server maintains a list of migrations and is able to resolve conflicts between multiple clients. Clients push/pull the migrations when syncing to keep their database up-to-date. E2EE is also supported. The original creator, James Long, has done talks about the architecture if you want to learn more [2, 3].

[1] https://actualbudget.org/

[2] https://www.youtube.com/watch?v=2dh_gtndayY

[3] https://www.localfirst.fm/7