Offline-first sync with Supabase

The pitch is that you can start solving immediately: no account, no sign-up wall, no spinner where your progress should be. Progress lives in localStorage and reads are synchronous. When we added optional accounts, the hard requirement was that this stays true — sign-in as an enhancement, never a dependency on the read path.

1.The constraint: reads cannot wait

Most sync designs start from the network and cache downward. We did the opposite. Every store keeps its existing synchronous get() that reads from localStorage and returns. There is no async accessor, no loading state, and no failure mode where storage is slow. The network is a background concern that may push data when it exists and may not exist at all.

That decides everything else. Conflict resolution runs on the client, because the client already has the data. The server accepts a few coarse writes, because the client decides when they are worth sending. And the feature disappears cleanly when the two Supabase env vars are unset — most local development and every test runs without them.

2.The seam: one factory, one adapter

Every persisted feature is described by a StoreSpec and created by createStore in src/lib/sync/store.ts. The factory keeps the synchronous local behavior and adds one hook after a write.

export interface StoreSpec<T> {
  id: StoreId;
  storageKey: string;   // deepforge:progress:v1, ...
  event: string;        // deepforge:progress-change
  empty: () => T;
  parse: (raw: string | null) => T;
  serialize: (v: T) => string;
  merge?: (local: T, remote: T) => T;
}

On a write, the store updates localStorage, dispatches its existing deepforge:*-change event so the UI updates in the same tab, and then calls notifyLocalWrite(id). In src/lib/sync/backend.ts that function checks the selected backend: remote only when both env vars are present and a cached session exists. If it is remote, the write schedules a debounced flush (~1.5 seconds per store) and attaches pagehide and visibilitychange listeners so a backgrounded tab flushes before it freezes.

The session cache stores a user id and an email. Tokens never touch our keys — supabase-js keeps its own session under sb-<ref>-auth-token. The storage adapter fails soft: SSR, private mode, and quota errors resolve to null and a no-op instead of throwing.

3.Nine stores, one table

The schema is deliberately small. Personal state lives in user_stores — one row per user per store, keyed (user_id, store_id), with the serialized store as JSONB and an updated_at column. Progress, daily streaks, collections, contests, interview results, pen-and-paper answers, labs, research attempts, and the username are nine rows in one table, not nine tables. user_stats holds the leaderboard projection — score, solved, streaks — upserted when progress is pushed, and a security_invoker view joins it to public profiles so RLS still applies underneath.

The trade is obvious: the database cannot query inside the store payloads, and a schema change means changing the client serializer. In exchange, adding a feature that persists does not require a migration — the store shape is the client's business.

4.Merging, not replacing

The row-level write is simple: the client upserts, newest write wins. But a pull never installs the remote payload over local state. Each store has a pure merge function in src/lib/sync/remoteMerge.ts that returns the union of both devices; the merged result is written locally and pushed back.

Progress is the most interesting merge: attempted and solved are sticky true, solvedAt keeps the earliest solve, lastOpened is last-write-wins, and saved code follows whichever side has the newer lastOpened — the editor you touched most recently wins.

// excerpt — the per-problem rules inside mergeProgress()
const solved = Boolean(left.solved || right.solved);
const attempted = Boolean(left.attempted || right.attempted);
const lastOpened = laterValue(left.lastOpened, right.lastOpened);
const solvedAt = solved ? earlierValue(left.solvedAt, right.solvedAt) : undefined;
const savedCode = pickNewerSide(
  left.savedCode, right.savedCode, left.lastOpened, right.lastOpened,
);
// attempted / solved sticky · earliest solve · LWW editor state
// keys present on either side are kept, then the entry is rebuilt

The other rules follow the same spirit. Daily streaks are the union of solved dates with the streak recomputed from the set. Labs keep the best score and OR the passed flag. Research keeps the best score in the challenge's own direction and unions the attempt log, capped at fifty. Collections preserve local order and let the newer updatedAt win per item. Contests and interviews append-merge by id and completion time.

5.First sign-in is a one-way push

A user who has been solving locally for a month and then signs in must never lose a solve. afterSignIn pushes every local store as-is when profiles.synced_at is empty, then stamps the flag. There is nothing remote to merge yet, because this account has never seen another device.

Later devices take the other branch: pull all nine, merge with local, push the merged result. Manual "sync now" does the same and stamps a local timestamp only when nothing errored. No sync entry point throws; failures surface through the sync state the panel subscribes to.

6.RLS is the security boundary

The publishable key ships in the browser, so every table is protected by row-level security rather than by the client. user_stores is readable only by its owner; user_stats and profiles are publicly readable because they feed the leaderboard — aggregated counts and usernames only. Comments and forum rows are public to read and writable by their author, and upvote counters change only through security definer RPCs revoked from anonymous callers.

Two ways in: a magic link and Google OAuth. Both resolve failures inline — if Google is not enabled in the dashboard, the button reports the provider error and magic links keep working. All of it is optional: with the env vars unset, the app never imports Supabase and behaves exactly like the local-only product it was.

7.What we deliberately did not build

No realtime subscriptions: a second tab sees changes at the next pull, not while you type. No pagination: nine rows per user do not need it. No tombstones: deletes do not propagate, so clearing progress locally can be resurrected by another device's pull. No server clock: updated_at is client-authored, so a skewed device can win a row-level race. No retry queue: a failed push retries on the next write or manual sync. No server-side merge: the merge needs the local data anyway.

The local-only tests take the seam seriously: the sync suite injects a fake client and drives sign-in, migration, pull-merge-push, offline writes, and error states without a network. That is how 252 unit tests stay green in CI with no Supabase project at all.

If you want the shape of the product rather than the implementation, read about DeepForge — local-first is the feature, not a fallback.