Local-first apps

Local-first apps treat the on-device SQLite database as the source of UX truth: reads and writes work offline, then sync reconciles with a server or peers.

Design concerns:

• **Offline writes**: queue mutations with client-generated IDs (UUID) until sync succeeds
• **Conflict resolution**: last-write-wins, version vectors, CRDTs, or domain-specific merge
• **Migrations**: version schema per app release; handle partial upgrades on user devices
• **Sync protocol**: push/pull deltas, tombstones for deletes, backoff and idempotency

					CREATE TABLE notes (
  id TEXT PRIMARY KEY,
  body TEXT NOT NULL,
  updated_at INTEGER NOT NULL,
  deleted INTEGER DEFAULT 0,
  sync_version INTEGER DEFAULT 0
);

-- Pull changes since cursor
SELECT * FROM notes WHERE sync_version > ? OR updated_at > ?;
				

SQLite enables instant UI; sync layer owns consistency boundaries. CRDT libraries or ElectricSQL/LiteFS-style tools change operational shape but not the core trade-off.

On interviews: walk through offline create → reconnect → conflict; explain tombstones and idempotent sync; separate local UX consistency from server authority.

Common pitfalls: server auto-increment IDs breaking offline creates; syncing entire tables; no delete propagation; conflict policy undefined until users complain.

The trade-off is responsive offline UX versus sync complexity, conflict handling, and testing burden across device versions.

Checklist:

• Client IDs for offline creates.
• Explicit conflict and delete (tombstone) policy.
• Versioned migrations on device.
• Idempotent sync with cursors or change feeds.