About the app
I am building an application which will have 2 clients: web and mobile. Both clients will need to auth to sync the user data. On average, each user will generate 2000 rows of data.
The mobile application should be able to run in offline mode, making SQLite is an obvious choice for the mobile app to store all user-usage data.
The problem is: how to keep both clients in data sync? Given data the mobile app generates records in offline mode that need to be synced to the web app?
Solution 1: Single-tenant SQLite as source of truth. Save periodic progress to a single SQLite and use this same database by the web client.
- Data loss due to periodic syncing.
- Simplest solution is to overwrite (merge) databases from the 2 clients into a new database. This is more expensive that saving only the newly generated data logs.
Solution 2: Use a Postgres database as source of truth. Local SQLite for the mobile app and sync the data between Postgres and mobile SQLite database.
- Need to support additional DB format
- Syncing logic between databases of different formats
- Probably slower than SQLlite (at 10K users, its 20M rows)
Can anyone shed some light into best practice here?