I am developing a flutter app that should also be able to run in offline mode. Because I am using flutter I want to also offer the use of the web version of my application. The application I want to build is data reliant therefore to make it work offline I need to have some kind of local database, but for the web version to work, I also need to store the data on a remote database so it can be accessed from the web. The problem that this proposes is how do I makes sure that the local and remote databases are always on the same page. If something is changed from the web it needs to also affect the local database on the device and if something is changed locally it also has to affect the remote database. I simply need a tip on how to generally achieve what I am looking for.
This can be easily achieved using Firebase Firestore which supports offline.
Or
If you plan to do this from scratch one way to do this is to keep a separate local database and execute every transaction on it. If online do the same transactions on the remote database. If offline keep a record of transactions pending on the local database ( preferably in a separate DB from the main DB) and execute them when the remote database is connected.
You can use Hive or sqflite for local DB
Or
Keep track of records, which the transactions were performed on. Before synchronize merge these records from both local(done offline) and remote(done on the web, phone not connected). If multiple transactions were performed on the same record, Update both remote & local DB records to the latest record state from DB where the latest transaction was performed (if the latest transaction was performed on the remote DB, update the local DB& vice-versa)