Build Offline Sync Engine on nextjs-dexie-postgres

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.

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.

What you get

Features:

• Offline-first CRUD: create/edit/delete work with zero network; instant local apply, background sync on reconnect
• Per-field last-write-wins conflict resolution on Hybrid Logical Clocks — deterministic, identical on every device
• Field merge: concurrent edits to different fields of the same record both survive
• Replicated deletes with tombstones, 90-day retention, server compaction, forced-resync recovery for stale clients
• Cursor-paginated pull + batched, blindly-retryable (idempotent) push over two HTTP endpoints
• Clock-skew defense: 5-minute server drift gate + automatic outbox re-stamping
• Versioned wire schema: queued offline mutations survive app upgrades via client-side outbox migration; the server accepts only the current wire version (HTTP 426 otherwise)
• Generic engine (any table/row/column) with pluggable storage (IndexedDB via Dexie in the browser, in-memory for tests)
• Runnable acceptance harness (`verify.sh`) covering conflicts, deletes, pagination, skew, migration, and reset — exit 0 = correct build

Screens (demo notes app, Tailwind 4 + shadcn/ui conventions):

• **Notes** (`/`) — note list with inline title/body editing, add and delete, online/offline badge, pending-changes count, manual Sync button (auto-sync every 15 s and on reconnect)

Build budget: **expected 2 sessions on a $20/mo plan** (Claude Pro or equivalent) — one build session, one verify/fix session; allow a third if the fix pass runs long. The binding constraint is **Opus-tier prompt weight against Pro's rolling-window and weekly caps, not raw token volume**: three tasks route to `claude-opus-5` (T4 server core, T5 engine, T8 verify/fix), and Pro's Opus allowance is what forces the session split (Anthropic doesn't publish an exact token-per-window figure, so treat the session count as an expectation, not a guarantee). Everything else runs on Sonnet/Haiku via the routing table in `TASKS.md`/`prompts/01-build.md`. Plan on **~510k fresh tokens** (input + output, measured per-run mean) for one build pass — the receipt separately reports **~5.7M tokens of total billed input** (93% of it cache reads, billed at roughly a tenth of fresh-input price) and a measured cost of **$4.76 per verification run**, the most reliable of the three numbers.

nextjs-dexie-postgres versions lock