Local-First Data and Offline Sync
Local-first data means the app remains useful when the network is slow, absent, expensive, or intentionally unused. Start by making the local data durable, exportable, restorable, and testable. Add multi-device sync only when the product actually needs it.
The default ladder for small apps is simple:
- Store source-of-truth data locally.
- Add safe migrations before shipping schema changes.
- Add export/import and restore drills before sync.
- Add encrypted backup if the data is private.
- Add sync when backup-only no longer solves the user problem.
Gotcha: Offline-first is not the same as sync-first. A local-only app with good export and backup is often more trustworthy than a half-built sync engine.
Decision Ladder
| Need | Start with | Add later |
|---|---|---|
| Android relational app data | Room over SQLite | WorkManager backup/sync jobs, encrypted backup |
| Web structured offline data | IndexedDB, usually through Dexie | persistent storage request, worker-backed exports, sync queue |
| Desktop app state | SQLite in app data directory | app-document exports, OS keychain/Stronghold, backup rotation |
| Human-editable knowledge | Markdown, JSON, JSONL, attachments | SQLite/FTS index as derived data |
| Privacy-first finance/health data | local DB, lock screen, CSV/JSON export | user-owned encrypted backup, optional account sync |
| Collaboration | server-authoritative mutations or CRDTs | conflict UI, audit log, observability |
Read First
- Local-First Architecture - layers, boundaries, and data ownership.
- Storage Landscape - SQLite, Room, IndexedDB, OPFS, files, desktop paths, and secure stores.
- Choose a Storage Strategy - practical decision guide.
- Test Local-First Quality Gates - migration, backup, restore, conflict, quota, and encryption checks.
Contents
Architecture
- Local-First Architecture - UI, local store, queue, backup, sync, and remote service boundaries.
- Storage Landscape - storage options and when to use each.
- Sync Topologies - no sync, export/import, backup-only, server-authoritative sync, subset sync, and CRDTs.
Concepts
- SQLite for Local Apps - transactions, WAL, FTS, integrity checks, and app file format usage.
- Android Room - entities, DAOs, migrations, schema export, and device tests.
- IndexedDB and OPFS - browser storage, quotas, workers, and export/import.
- File-Backed Data - JSON, JSONL, Markdown, attachments, atomic writes, and manifests.
- Migrations and Schema Evolution - version discipline across local stores.
- Backup, Export, and Restore - hot backups,
VACUUM INTO, exports, restore drills, and wipe flows. - Encryption and Key Management - threat model, secure stores, keys, backups, and recovery.
- Conflicts and Sync - mutation replay, idempotency, merge rules, and CRDT boundaries.
- Offline UX - pending state, retries, degraded modes, trust copy, and storage pressure.
Quickstarts
- Android Room Local-First - minimal Room shape with migration and export hooks.
- Web IndexedDB with Dexie - versioned IndexedDB, export/import, and offline queue.
- Desktop SQLite - app data paths, migrations, backup, and key storage.
How-To Guides
- Choose a Storage Strategy - decision guide.
- Write Safe Migrations - migration workflow and tests.
- Implement Backup and Restore - backup creation, validation, import, and restore.
- Design an Offline Write Queue - idempotency, retries, and user-visible pending state.
- Add Encryption with Key Recovery - threat model, key storage, backup encryption, and recovery choices.
- Test Local-First Quality Gates - release gates for local-first apps.
Deep Dives
- SQLite WAL and Backups - WAL files, checkpoints, hot backups, and integrity checks.
- Browser Storage Failure Modes - quota, eviction, blocked upgrades, private mode, and multi-tab issues.
- Sync Strategy Tradeoffs - compare backup-only, mutation replay, subset sync, and CRDTs.
Walkthroughs
- Privacy-First Expense Tracker Data Flow - local Android finance app data flow.
- Local AI Agent Session Store - SQLite sessions, JSONL transcripts, FTS, and export/import.
Troubleshooting
- Common Local DB Failures - migration crashes, WAL copying, locks, quota, and restore mismatch.
- Offline Sync Debugging - stuck queues, duplicates, stale reads, auth expiry, and conflict loops.
- Encryption and Backup Failures - key loss, encrypted restores, CryptoKey export limits, and desktop vault issues.
Notes
- Source Index - sources consulted and blocked follow-ups.
- Quality Gates Checklist - compact reusable checklist.
- Glossary - terms used across the topic.
Opinionated Take
For a solo-dev app, ship local durability before sync. For privacy-sensitive apps, ship export and encrypted backup before accounts. For collaboration-heavy apps, choose a sync architecture early because conflict semantics shape the data model.
Do not make CRDTs the default answer. Use them when multi-writer collaboration is core to the product and when you can test, explain, and debug merges.