Choose a Storage Strategy

Start with the data, not the database.

Decision Tree

flowchart TD
    start["What owns the data?"] --> userData{"User-owned durable data?"}
    userData -->|No| cache["Use cache or preferences"]
    userData -->|Yes| platform{"Runtime?"}
    platform -->|Android| room["Room over SQLite"]
    platform -->|Browser| indexeddb["IndexedDB or OPFS"]
    platform -->|Desktop or CLI| sqlite["SQLite or files"]
    sqlite --> fileNeed{"Human-editable files matter?"}
    fileNeed -->|Yes| files["Markdown JSON JSONL plus derived index"]
    fileNeed -->|No| sqliteDb["SQLite DB"]
    room --> privateData{"Private data?"}
    indexeddb --> privateData
    sqliteDb --> privateData
    files --> privateData
    privateData -->|Yes| keys["Add key storage and encrypted backup design"]
    privateData -->|No| backup["Add export and restore"]

Practical Defaults

Product	Default
Android finance tracker	Room, CSV export, app lock, optional encrypted backup
Offline PWA	IndexedDB through Dexie, service worker cache, export/import, quota UX
Tauri desktop tool	SQLite in app data path, Stronghold/keychain for secrets, app-document export
Local AI assistant	SQLite sessions, JSONL transcripts, Markdown memory, FTS/vector derived indexes
Document vault	file/attachment store plus manifest, encrypted backup, SQLite metadata index

Questions To Answer

Is the data source-of-truth or cache?
Must the user inspect/edit it outside the app?
What is the restore path after device loss?
How large can it get?
Does it need relational queries?
Does it need multi-device sync or just backup?
What is the threat model?

If you cannot answer 3 and 7, do not claim the app is safe for private local-first data.