Feature: Offline-first sync - local DB, optimistic updates, conflict resolution & background retries

[hoangsonww]

Summary

Make TaskNexus truly mobile-proof by adding an offline-first sync layer: tasks are stored locally, edits apply instantly (optimistic UI), and a background queue syncs deltas to Supabase. Handle conflicts deterministically, show a tiny connectivity badge, and keep everything resilient to flakey networks.

---

Goals

• Local first: read/write from a local DB; app feels instant with no spinner.
• Optimistic updates with rollback on server rejection.
• Background sync: flush pending ops when connectivity/auth returns.
• Conflict resolution: deterministic merge by updated_at/version + field-level wins.
• Tiny UX affordances: offline chip on the header; per-task “unsynced” dot when pending.

---

UX details

• Status pill: Online • Synced / Offline • Changes pending (3) (tap → small sheet with a queue list + “Retry now”).
• Per-task subtle indicator when not synced yet (e.g., a small cloud-clock icon).
• Pull-to-refresh on Home triggers a pull sync.
• Errors surface in a non-blocking toast; item remains editable.

---

Acceptance criteria

• App works normally with airplane mode; creates/edits/deletes persist locally and survive app restarts.
• When network returns, pending ops are pushed in order and UI indicators clear.
• Conflicts (same task edited on two devices) resolve with latest updated_at wins by default; partial edit merges for distinct fields (e.g., completed vs text).
• Hard failures (e.g., 401) pause queue and show a single actionable toast (“Re-login to resume sync”).
• Cold start on a new device does initial incremental pull (not full table scan).
• Unit tests for queue logic; integration test that simulates offline→online.

---

Tech notes & approach

Local database

• Use expo-sqlite (or expo-sqlite/next) for zero-native-mods storage.
  Table: tasks_local mirroring Supabase schema + client fields:

  local_id TEXT PK, // uuid.v4()
  remote_id TEXT NULL, // Supabase ID, once assigned
  text TEXT, color TEXT, due_date TEXT NULL, completed INTEGER, 
  inserted_at TEXT, updated_at TEXT,
  _dirty INTEGER DEFAULT 0,       // 1 if pending push
  _op TEXT CHECK (_op in ('insert','update','delete')) NULL // last op kind

• Lightweight DAO wrapper with typed helpers.

Delta protocol

• Supabase tasks table: add columns

  • version BIGINT DEFAULT 0 NOT NULL,
  • updated_at TIMESTAMPTZ DEFAULT now() NOT NULL

• Trigger to bump version on updates:

  CREATE OR REPLACE FUNCTION bump_version() RETURNS trigger AS $$
  BEGIN NEW.version := COALESCE(OLD.version,0)+1; NEW.updated_at := now(); RETURN NEW; END; $$ LANGUAGE plpgsql;
  DROP TRIGGER IF EXISTS trg_tasks_version ON public.tasks;
  CREATE TRIGGER trg_tasks_version BEFORE UPDATE ON public.tasks FOR EACH ROW EXECUTE PROCEDURE bump_version();

• Push: send pending ops in FIFO; server returns authoritative row (remote_id, updated_at, version). Upsert locally & clear _dirty.

• Pull: GET rows where updated_at > last_pulled_at. Upsert locally; if local row is _dirty, run conflict resolution:

  • If server updated_at > local base time of edit ⇒ prefer server field, except preserve local _op=delete if server row still exists (tombstone wins).
  • Field-level merge: non-overlapping fields keep the freshest updated_at_field if you choose to track per-field stamps; otherwise start with row-level latest-wins to keep v1 lean.

Optimistic queue

• In memory + persisted (SQLite table ops_queue with {id, ts, local_id, remote_id, op, payload}).
• On app start and when NetInfo.isConnected flips true, drain queue with exponential backoff (cap ~30s).
• On auth loss, pause and require re-login.

Realtime coexistence

• Keep existing Supabase realtime subscription. When a realtime event arrives:

  • If not _dirty, apply immediately.
  • If _dirty, stash as a shadow and reevaluate post-push (prevents UI flicker).

Background flush

• Use expo-task-manager + expo-background-fetch to periodically attempt a short push when app is backgrounded (Android/limited iOS; best-effort).

Dev toggles

• SYNC_LOG=1 env to console log queue transitions.
• Feature flag ENABLE_OFFLINE_FIRST=1 to gate rollout.

Testing

• Deterministic unit tests for queue, merge, and error branches using Jest + fake timers.
• E2E happy path: create 3 tasks offline → kill app → reopen → go online → verify remote.

---

Subtasks

• SQLite schema + DAO + migration bootstrap
• Write queue (enqueue/merge, retry/backoff, pause/resume)
• Push API (insert/update/delete) + server upsert handler
• Pull API (since last_pulled_at) + local upsert
• Conflict policy v1 (row latest-wins) + test matrix
• UI: status pill, per-task pending dot, pull-to-refresh
• NetInfo + background fetch wiring
• Docs: architecture diagram & failure modes

---

Nice-to-have (later)

• Tombstones for deletes on server to enable true CRDT-ish convergence.
• Per-field vector clocks to get safer merges.
• Selective sync (only current user’s last N months).
• “Force resolve” UI for rare unresolved conflicts.

Why this matters: mobile networks are messy. Going offline-first eliminates spinners, protects user trust, and makes TaskNexus feel premium—even on subway rides and spotty Wi-Fi.