ADR 0001 — Offline strategy via snapshot sync

Status: accepted · Date: 2026-06-24 · Implemented baseline: 2026-07-27 · Supersedes: none

Context

Tradespeople work on building sites with poor or no signal. The app must let them:

1. Browse existing quotes/clients on-site (read).
2. Create and edit drafts on-site (write to own data, no state transition).
3. Send a quote (state transition draft → sent, generates public_token, customer link goes live).
4. See accepts/payments come in (server-originated state changes).

Current architecture (per architecture-schema-cost-model.md): supabase-js as thin HTTP client, every write goes through a Postgres definer RPC, RLS enforces ownership, one writer per state transition. supabase-js is online-only; loss of network = app is dead weight.

Three offline shapes were considered:

Option	Build cost	Offline browse	Offline create	Multi-device merge
A. Optimistic UI + mutation queue (TanStack Query + persister)	~1 week	Last-viewed only	Yes (drafts in cache)	No
B. Snapshot sync + local SQLite mirror	~3-5 days on top of A	Full history	Yes	Read-only mirror, no merge needed
C. Full local-first with bi-dir sync (PowerSync / Electric)	~3-4 weeks or $35/mo + integration	Full history	Yes	Built-in CRDT-ish

Decision

Adopt option B (snapshot sync) as the MVP offline strategy, layered on top of option A.

• Drafts: local-first in expo-sqlite. Create/edit/delete fully offline. TanStack Query cache backed by SQLite persister.
• State transitions (send_quote, accept_quote_by_token, void_quote): server-authoritative. Mutation queue retries on reconnect. UI gates share-sheet on confirmed send.
• Browse history: periodic pull of owner-scoped snapshot into SQLite via single definer RPC get_my_snapshot(p_since timestamptz). Read-only mirror — never written to by user actions directly.
• Teams / approval queues: stay online-first for the initial offline pass. The owner mirror contains the signed-in user’s own sole-trader data only, not rows visible through the manager overlay.

Defer option C (PowerSync) until a user complains about a real limit not covered here (e.g. true live sync across phone + tablet, or browse-everything always-fresh).

Why snapshot sync wins for this stack

• Solo tradesperson, mostly one device → no multi-writer merge problem → CRDT machinery is overkill.
• Server is already authoritative for state transitions (one-writer rule, advisory locks for quote numbering, RLS, audit fields like accepted_ip). Local-first would force re-implementing these client-side or sync-handler-side.
• Customer-facing accept lands on the server (web /q/:token). That write is server-first by definition; SQLite is downstream regardless.
• Snapshot sync = boring, easy to reason about, no third-party sync runtime. The RPC is one function we own.

Schema impact

Add now (cheap, future-proofs):

• updated_at timestamptz not null default now() on clients and quote_items (already on quotes). Backfill in migration.
• deleted_at timestamptz on clients, quotes, quote_items. Soft-delete only — hard deletes leave stale rows in SQLite mirrors with no signal to remove them.
• last_sync_at per device, stored client-side in expo-secure-store. Not a schema concern.

New RPC:

• get_my_snapshot(p_since timestamptz default null) returns jsonb
  • Returns owner-scoped { profile, clients, quotes, quote_items, invoices, invoice_quotes, payments, rebates, reminders, deleted: { ... } } filtered by auth.uid() and updated_at > p_since (or all rows if null).
  • security definer, granted to authenticated, must explicitly filter by auth.uid() (no RLS bypass leak — same risk class as existing get_quote_detail).
  • Single round-trip on app open; subsequent calls incremental.

Client shape

• Initial baseline uses TanStack Query persisted through expo-sqlite/kv-store, so previously loaded core screens render after a cold start without signal.
• Entity-mirror follow-up: expo-sqlite tables mirror entity shape. Store each entity as a jsonb blob keyed by id to avoid migration burden on column adds:
  • quotes(id text primary key, json text, updated_at integer)
  • clients(id text primary key, json text, updated_at integer)
  • quote_items(id text primary key, quote_id text, json text, updated_at integer)
  • JSON1 extension lets us query json_extract(json, '$.profile_id') when needed.
• TanStack Query rehydrates from SQLite on cold start, then triggers a background get_my_snapshot(last_sync_at) to refresh.
• Mutation queue paused via onlineManager.setOnline(NetInfo.isConnected); drains on reconnect.
• Drafts created offline get a client-generated UUID; server accepts the UUID as quotes.id on first sync (already supported by gen_random_uuid() default — pass explicit id from client instead).

Gotchas

• Initial sync on slow signal could be megabytes. Mitigate by paginating snapshot newest-first and rendering UI from whatever lands first.
• public_token generation currently server-side in send_quote. For offline-send (deferred), token would move client-side. Defer that decision until offline-send is requested — current model requires network to send anyway, which is acceptable behaviour (you can’t deliver a link offline).
• Quote numbering (next_quote_number) needs server. Drafts created offline carry a local placeholder (DRAFT-<short-uuid>) and are renumbered to Q-NNNN on first sync. UI shows placeholder until sync completes.
• Photos not covered. Stay in Supabase Storage; cache strategy is a separate decision.
• Schema drift: storing as jsonb avoids local migrations on add-column. Removing/renaming a column still needs a local cache bust on app upgrade (bump a SCHEMA_VERSION constant, wipe SQLite if mismatched).

Consequences

• All new tables in MVP scope must include updated_at and deleted_at from the start — add to the schema-conventions section of architecture-schema-cost-model.md.
• All writers (definer RPCs) must touch updated_at on every state change (existing send_quote, void_quote, accept_quote_by_token already do; verify on review).
• Soft-delete becomes the only legal delete shape. No delete from ... in RPCs — replace with update ... set deleted_at = now().
• get_my_snapshot joins the security-critical RPC list. Add to /supabase-security-audit checklist.

Implementation status

Baseline implemented 2026-07-27:

1. App cache dependencies installed: TanStack Query, TanStack persist client, NetInfo, expo-sqlite.
2. Root app wrapped in an offline provider: SQLite-backed query persistence, NetInfo-backed online state, foreground focus refresh, paused-mutation resume hook.
3. Core read surfaces moved to persisted queries: home summary/activity/ready-to-invoice, quote/invoice lists, voided lists, new-quote clients/templates, quote detail, invoice detail/reminders.
4. Migration 20260727000000_offline_snapshot_sync.sql: adds missing updated_at/deleted_at markers, update triggers, and authenticated owner-scoped get_my_snapshot(p_since).

Draft outbox slice implemented 2026-07-27:

1. Migration 20260727000001_quote_draft_client_id.sql: adds idempotent create_quote_draft_with_id(...) so offline-created quote UUIDs survive replay.
2. App local quote-draft outbox in lib/quotes/local-drafts.ts, persisted through expo-sqlite/kv-store.
3. New quote flow saves locally when the device is offline or a likely network failure happens mid-save.
4. Local drafts appear in the quote list/detail immediately as DRAFT-<short-id> and sync automatically on reconnect.
5. Send/share remains server-confirmed: local drafts show a saved-offline callout and do not expose send/discount actions until replay creates the server draft.

Offline edit slice implemented 2026-07-27:

1. Local drafts can be reopened before sync and edited on-device.
2. Edits preserve the local quote UUID and pending replay payload.
3. Editing a draft resets failed/syncing local state back to pending so reconnect replay uses the latest saved version.

Offline hardening slice implemented 2026-07-27:

1. App-wide connectivity state drives a persistent offline banner.
2. Reconnect triggers local draft replay without requiring a manual refresh.
3. Quote and invoice detail screens disable or guard online-only actions while offline.
4. Failed local draft syncs expose the last error plus retry, edit, and discard paths.

Snapshot mirror ingest slice implemented 2026-07-27:

1. App owns a separate sawdust-offline-snapshot-v1.db SQLite mirror.
2. Snapshot rows are stored in normalised entity tables with JSON payloads and local sync metadata.
3. get_my_snapshot(p_since) is pulled on app start/reconnect and applied incrementally using offline_meta.last_sync_at.
4. Tombstones from the RPC remove stale local rows for soft-deleted entities.

Quote mirror read slice implemented 2026-07-27:

1. Quote list falls back to the SQLite mirror when the live Supabase query fails.
2. Quote detail falls back to mirrored quote/client/profile/items data when get_quote_detail is unavailable.
3. Voided quotes, clients, and quote templates also fall back to mirrored rows.
4. Mirror fallback requires a local Supabase session, so signed-out devices do not render stale mirrored business data.

Remaining mirror read slice implemented 2026-07-27:

1. Invoice list, voided invoice list, invoice detail, invoice summary, live-invoice lookup, invoiceable quotes, and ready-to-invoice fall back to the SQLite mirror.
2. Home recent activity falls back to mirrored quotes/invoices and reuses the same merge/label path as live data.
3. Mirror-derived totals continue to account for payment rows when calculating owed/overdue values.

Queued action retry slice implemented 2026-07-27:

1. Server-authoritative quote/invoice state RPCs are persisted when they fail with likely network errors.
2. Reconnect drains the queued actions before local draft replay and snapshot refresh, then invalidates affected quote, invoice, home, and activity surfaces.
3. Validation, permission, and other non-network replay failures are not retried indefinitely; they are captured and discarded from the local queue.

Explicit offline action queue UX implemented 2026-07-27:

1. Quote, invoice, and approval screens let users queue server-authoritative actions while already offline instead of blocking the tap.
2. The app banner shows pending queued-action count so users can see work waiting to sync.
3. External sharing paths remain online-only because they need a real public token/link handoff to another app.

Still pending:

1. Manual offline matrix on a physical device: airplane mode mid-edit, low-connectivity mid-send, app kill during sync, reopen offline, reconnect, two-quote race.

Potential implementation steps

Not committed — sequenced sketch for when this ADR moves to accepted. Each step shippable on its own.

1. Schema prep migration (projects/db/supabase/migrations/)

   • New migration file (init is frozen post-push). Add updated_at timestamptz not null default now() and deleted_at timestamptz to clients and quote_items. Backfill updated_at from created_at for existing rows.
   • Add updated_at trigger function + per-table triggers so any update bumps it (avoid relying on every writer to remember).
   • Replace any delete from in RPCs with update ... set deleted_at = now(). Audit clients direct CRUD policy — owner delete becomes soft-delete via RLS-allowed update.

2. Docs update

   • architecture-schema-cost-model.md schema-conventions section: add “every table carries updated_at + deleted_at; hard deletes are illegal”.
   • CLAUDE.md migration rules: add soft-delete + updated_at trigger to the new-table checklist.

3. get_my_snapshot RPC (new migration)

   • Signature: get_my_snapshot(p_since timestamptz default null) returns jsonb.
   • security definer, set search_path = public, explicit auth.uid() filter on every joined table.
   • Returns { profiles, clients, quotes, quote_items, deleted: { ... } }. deleted arrays = ids with deleted_at > p_since.
   • Revoke from public, anon, authenticated; grant to authenticated only.
   • Add to /supabase-security-audit checklist.

4. Client cache layer in /app (no SQLite yet — start with in-memory persistence to derisk)

   • Install @tanstack/react-query + @tanstack/query-async-storage-persister + @react-native-community/netinfo.
   • Wrap root in QueryClientProvider, wire onlineManager to NetInfo.
   • Convert existing supabase-js calls in /app to useQuery / useMutation. RPC names stay identical; mutation onMutate does optimistic cache update.
   • Persist cache to expo-secure-store first (fastest path). Validates the shape before SQLite work.

5. SQLite mirror (replace secure-store persister)

   • expo-sqlite schema: jsonb-blob tables as in ADR client shape.
   • Custom persister adapter for TanStack Query that writes to SQLite tables instead of one blob.
   • Bootstrap on app open: read SQLite → hydrate cache → render → background-fire get_my_snapshot(last_sync_at) → diff in → save back.
   • SCHEMA_VERSION constant; mismatch on app upgrade wipes SQLite and re-syncs from scratch.

6. Offline draft creation

   • create_quote_draft already takes items payload. Modify RPC (or add create_quote_draft_with_id(p_id uuid, ...)) to accept client-generated UUID so offline-created drafts keep stable identity through sync.
   • UI generates UUID + DRAFT-<short> placeholder number locally; renders identically to a sent one.
   • Send is gated behind connectivity (TanStack mutation queue + share-sheet disabled until success).

7. Verification

   • Manual test matrix on physical device: airplane mode mid-edit, airplane mode mid-send, kill app mid-sync, reopen offline, reconnect, two-quote race.
   • No automated offline tests for MVP — manual is enough at solo-trader scale.

Revisit when

• A user reports needing live updates across two devices (phone + tablet) without manual refresh → re-evaluate PowerSync.
• Snapshot payload regularly exceeds ~5MB for a single user → switch to entity-typed normalised SQLite tables and per-entity incremental sync.
• Offline-send is requested (tradesperson wants to tap “Send” on roof and have it fire later) → move token generation client-side, add pending_send local state.