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

### 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:

```ts
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:

```sql
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.