Offline Sync Engine

Offline-first sounds simple until two devices edit the same record while disconnected — then you're debugging clock skew, resurrected deletes, and out-of-order commits in production, usually after a user has already noticed. This pack builds a working sync core — per-field last-write-wins on Hybrid Logical Clocks, replicated tombstones, paginated pull, a schema-migration-safe offline queue — wired into a real demo notes app on Next.js 16 + Postgres. The receipt is a runnable convergence proof (`verify.sh`, run against embedded PGlite): 18 simulated conflict/delete/skew/migration scenarios that all have to converge before the build counts as done.

Example use cases

• **A field-inspection app** for a team walking warehouses or basements with no signal — inspectors edit the same job record from different devices, then reconnect in the parking lot; edits to different fields (photos vs. notes vs. status) both survive, and same-field conflicts settle identically everywhere.
• **An indie personal-CRM or notes app builder** who wants "just works across my phone and laptop, even offline" without pulling in a hosted sync platform's SDK and monthly bill for a single-user app.
• **An agency re-stamping the same core across client projects** — ship the identical sync engine into five different small-business inventory or booking apps instead of re-solving conflict resolution per contract.
• **A small internal team tool** (a handful of concurrent editors — think a shared task board or shift schedule) that needs correct multi-device conflict resolution without adopting a service dependency for what is, underneath, a CRUD app.

Why this pack

No "always" claims here — agents are stochastic, and no sync design is free of tradeoffs. This is what the pack pre-solves, what it costs, and what you give up.

**What a bare agent typically ships that this pack pre-solves** (these are the specific bug classes, not a claim that every agent hits every one):

• *Collation divergence* — the HLC tiebreak compares strings byte-wise in JavaScript; if Postgres compares the same column under a locale collation (the managed-Postgres default), same-timestamp ties can sort differently in SQL than in JS, and server and client permanently disagree on which write won.
• *Clock-skew local-cell stranding* — a device with a wrong clock either loses its own edits to values it just pulled from a correctly-clocked device (the edit visibly reverts), or, if only the outbox gets corrected and not the device's own stored cells, the device rejects every legitimate future remote update on those cells forever.
• *The seq/commit race* — two concurrent write transactions can commit out of order; a client that paginates past the sequence number of the transaction that hasn't committed yet never sees that row, because nothing serialized commit order against sequence order.

**What sync platforms are honest about**: PowerSync, ElectricSQL, and Replicache-class services in 2026 are real, actively-maintained products with broader device/mobile SDK coverage and (in some cases) realtime push channels this pack doesn't have. The tradeoff is a recurring bill tied to usage and a client/protocol commitment that's a real migration cost later. This pack is the other point on that tradeoff: owned code, one-time cost, no protocol to be locked into — at the cost of no realtime push and no non-JS mobile SDKs out of the box.

Scale envelope

Derived from the shipped design, not aspiration — and labeled as estimates where they are estimates.

The architecture serializes every push commit through one global `pg_advisory_xact_lock` (this is deliberate: it's what turns "two transactions committed out of order" from a correctness bug into a non-issue — sequence order becomes commit order by construction, at the cost of throughput). That lock is the ceiling to reason about:

• **Push throughput (estimate, not load-tested in the receipt):** a serialized push transaction (acquire lock → per-cell LWW upsert → commit) on managed Postgres in the same region typically completes in tens of milliseconds. That puts a rough order-of-magnitude ceiling around the low hundreds of push transactions per second, sustained. Each transaction can batch up to 500 cell-writes, so this is a ceiling on *concurrent distinct push commits*, not on total edits — a burst of 500 field edits from one client is one transaction against that budget.
• **Concurrent syncing clients:** sync runs on a 15-second interval plus on-reconnect and manual triggers, not a constant stream — clients aren't all pushing in the same second. Even at the low end of the estimate above, that comfortably covers hundreds to low thousands of active clients whose writes are naturally spread out, which is the shape of field-team, personal-app, and small-team usage, not a scenario where thousands of users all commit in the same instant.
• **Rows:** the cell store holds one row per currently-live `(table, row, column)` triple — no append-only history log, so storage is O(live data). Millions of live cells is an ordinary Postgres table size for this schema; it isn't the constraint.
• **First ceiling:** the single global advisory lock key. Every push, across every table and every client, serializes through it. As concurrent push volume approaches the transaction-rate estimate above, pushes start queuing — added latency, not incorrect behavior, but a real ceiling.
• **Upgrade path (v2, not shipped):** partition the lock key — for example, hash a tenant or shard id into the advisory-lock key so unrelated datasets serialize independently instead of sharing one global lock. (This pack ships a single-dataset model; multi-tenancy is a documented seam, not a feature, of the current build.) The architecture's own fallback, if you need it before building tenancy, is fencing pulls at the oldest in-flight transaction instead of removing the lock — a bigger change than it sounds, not something to do casually.

**What this comfortably covers as shipped:** field teams (dozens of devices syncing opportunistically), personal multi-device apps (a handful of clients per user), small-team internal tools (a team's worth of concurrent editors). **What it isn't sized for out of the box:** a multi-tenant consumer SaaS with thousands of organizations all writing to one deployment simultaneously — that's the v2 path above, not this build.

**What it costs to skip the pack (modelled, not measured):** building this yourself the way most people actually work — one agent on a top-tier model, no cheap-tier routing, no parallel subagents, no prompt-caching discipline — models out at roughly **$45–185 in API tokens, likely around $95**. That's this pack's own measured build volume repriced at Opus-class rates, multiplied by an exploration factor for the conflict-resolution decisions this pack pre-makes; the pack-guided build itself measured ≈$5 of tokens per verification run (August 2026 rates), because tasks route down to cheaper tiers. Honest reading: at the likely figure that's about the same as the $99 price, and at the low end it's less — the token math alone doesn't pay for this pack. What it buys is the 18-scenario convergence proof that already passed, 3 of 3, before you spent the weekend.

Verified against

• claude-opus-5
• claude-sonnet-5
• claude-haiku-4-5

Supported stacks

• nextjs-dexie-postgres

  node	24.18.1
  next	16.2.12
  react	19.2.8
  react-dom	19.2.8
  typescript	6.0.3
  dexie	4.4.4
  zod	4.4.3
  pg	8.22.0
  @electric-sql/pglite	0.5.4
  tsx	4.23.1
  @types/node	26.1.2
  @types/pg	8.20.0
  @types/react	19.2.17
  @types/react-dom	19.2.4
  tailwindcss	4.3.3
  @tailwindcss/postcss	4.3.3
  clsx	2.1.1
  tailwind-merge	3.6.0

Estimated buyer token cost

510,000 tokens for one build pass (estimate, not a guarantee).