Data-consistency layers

Helm's read-and-write story is being built as four layers. Each one handles a different failure mode; together they answer the operational questions an in-shop POS keeps asking: "did my edit stick, or did Robbie's edit win?" and "is the number on my screen actually current?"

All four layers live 2026-05-22/23

Layer 1 — Optimistic locking (migration 051, 2026-05-21): server rejects stale writes with HTTP 409; client reloads. ✓
Layer 2 — Durable Object + WebSocket realtime broadcast (v0.4.238, 2026-05-22): server pushes "this topic changed" to every subscribed browser sub-second. ✓
Layer 3 — Cart holds / reserve-on-read inventory (migration 052 + v0.4.239, 2026-05-22): adding a tracked variant to a till instantly drops quantity_available for every other terminal. Replaces the originally-planned offline-merge framing — see Layer 3 below for why. ✓
Layer 4 — Reactive query framework (public/js/helm-query.js, v0.4.235, 2026-05-21): client subscribes to topics; any write invalidates a topic; every subscribed query refetches. ✓

The problem each layer solves

Failure mode	Without the layer	With the layer
Stale write — two operators edit the same record; the second `UPDATE` silently wins, the first one's edits are gone	Last writer wins; the first edit is lost without anyone noticing	Layer 1. Second `UPDATE` returns 409; the client prompts "this record was modified — reload?"
Stale read — a number on the screen is from the last fetch, not from now; another operator did something a minute ago	The screen lies until the next page refresh	Layer 2 (push) or Layer 4 (poll-after-write-with-staleTime) keep the data fresh
Offline divergence — two operators edit the same record while one is offline; both come online and both writes apply	Whatever lands second silently overwrites	Layer 3 merges non-conflicting field changes; surfaces real conflicts as a UI choice
Manual reactivity sprawl — the codebase fills with `fetch + setInterval + document.dispatchEvent` patterns that drift	Hard to know who refreshes when; bugs where staleness slips through	Layer 4 centralizes the pattern: `helmQuery({ topic, endpoint, onData })`

Layer 1 — Optimistic locking (live)

Every UPDATE on a substrate table now requires WHERE id = ? AND version = ? and bumps version + 1 on success. If the WHERE matches zero rows, the worker returns HTTP 409 with the current server state.

Tables with the version column (migration 051, 11 tables):

products              · product_variants
customers             · suppliers
service_tickets       · service_ticket_lines
purchase_orders       · purchase_order_lines
shop_config           · staff
bulk_items

Deliberately excluded:

inventory — quantity_on_hand is a denormalized cache mutated by every sale's stock decrement. Locking it would fight with apiSalesCreate on every transaction.
transactions / transaction_lines — append-only history. Updates after post (status flips, refund stamps) don't compete with concurrent edits in the same way; the audit chain is the safety net.
audit_events / audit_mutations — append-only, never UPDATEd.
idempotency_records — keyed by idempotency_key UNIQUE; INSERT race already handled.
device_sessions / staff_sessions — touched on every authenticated request; the contention isn't between operators, it's between request rate and lock window.

Client integration. The helmMutate({ …, onConflict }) funnel in Layer 4 routes 409 responses to onConflict. The standard handler shows a helmConfirm modal: "This record was modified by another operator while you were editing. Reload and try again?" — clicking Reload re-fetches the topic (Layer 4 invalidation), discarding the operator's in-progress edit. Most edit modals already use helmMutate, so they get this behaviour for free.

Performance cost. One extra WHERE clause; one column read. Negligible.

Layer 2 — Durable Object + WebSocket realtime broadcast (live)

The push side of the freshness problem. A Cloudflare Durable Object per shop owns the list of subscribed browsers. Each operator browser opens a long-lived WebSocket at GET /api/realtime (the original plan was Server-Sent Events; WebSocket won because the same primitive handles two-way client→server signals like "I'm idle / I'm hidden" too). The DO routes invalidate(topic) events to every connected subscriber.

Endpoint shape:

GET /api/realtime              — open the WebSocket
GET /api/realtime/status       — debug: how many subscribers + last-broadcast topics

On the write path. Every helmMutate invalidation now does two things: (1) local in-memory invalidation of helmQuery subscribers in this browser (the original Layer-4 behaviour) AND (2) a Durable Object broadcast that fans out to every other browser subscribed to the same topic. Other terminals refetch within sub-second.

Per-topic granularity. A till adding a line to the cart broadcasts inventory; another operator's Sales-Till search refetches and sees quantity_available drop. A refund broadcasts transactions.today; every ledger refetches. The topic list is the same one Layer 4 uses for invalidates: [...] — no new vocabulary.

No per-screen code changes to consume Layer 2. The forward-compat hooks at the top of public/js/helm-query.js were already wired; the WebSocket adapter just bridges realtime:invalidate → helmInvalidate. Every existing helmQuery subscription got push-driven freshness for free.

Fallback. If the WebSocket drops (network blip, idle disconnect, server restart), Layer 4's staleTime polling continues to handle freshness — the union of push + poll covers every failure mode. The WebSocket auto-reconnects with exponential backoff.

Layer 3 — Cart holds / reserve-on-read inventory (live)

The original Layer 3 framing was conflict-aware diff merge for offline writes. That was deferred — the offline write queue isn't built yet, and the more pressing concrete problem turned out to be two cashiers on the floor both ringing the last tube.

Without holds: cashier A adds the last tube to the till; cashier B's Sales-Till search still shows quantity_on_hand = 1 because that column doesn't change until the sale posts; cashier B adds it too; the second apiSalesCreate fails with a 409 from the inventory_movements check — after the customer is at the counter with their wallet out. Late and ugly.

With holds (migration 052): a cart_holds row is written the moment a tracked variant is added to a till. /api/inventory/search subtracts SUM(cart_holds.quantity) from quantity_on_hand to compute quantity_available. Layer 2 broadcasts inventory on every hold change, so other terminals see the reduced count sub-second.

The till_session_id is a per-browser-tab UUID stored in localStorage — survives reload, doesn't survive new-tab. Lets the worker identify which till owns which holds without forcing the staff session into the picture (staff swap mid-cart is normal; the till identity is the cart identity).

Lifecycle:

Operator action	What happens to the hold
Add to till	`POST /api/inventory/cart-hold` → new row
Change line qty	`PUT /api/inventory/cart-hold/:id`
Remove line	`DELETE /api/inventory/cart-hold/:id`
Clear till	`DELETE /api/inventory/cart-holds?till_session_id=…`
Charge	Sale-create clears the session's holds AND writes the real `inventory_movements` `sale` rows in one transactional batch
Park / Estimate / Deposit	Holds preserved — the lines are still committed somewhere (parked / quoted / deposit-earmarked), so other terminals shouldn't see those items "available again"

Expiry. Each hold carries an expires_at (default 4 hours). A daily sweep cron clears expired rows. Tills that idle past the window release their reservations automatically — so a forgotten browser tab doesn't lock inventory indefinitely.

Why not on transactions / staff_sessions / etc. Holds are deliberately keyed by till_session_id (browser tab), not by staff_session_id (operator). Operators come and go from a single till during a sale; the cart is the unit of reservation, not the human.

The deferred offline-conflict-merge idea isn't dead — it'll come back when the offline write queue (offline-arch slices 5-7) lands. At that point we may add it as Layer 3.5 or fold it into Layer 1's onConflict handler. For now, "Layer 3" means cart holds.

Layer 4 — Reactive query framework (live)

public/js/helm-query.js (~440 LOC) replaces the imperative fetch + setInterval + document.dispatchEvent('helm:*') pattern that had grown to 51+ event-dispatch sites. Three public functions on window:

`helmQuery({ topic, endpoint, ... })`

Declares a screen's data dependency.

const q = helmQuery({
  topic: 'transactions.today',
  endpoint: '/api/sales/today',
  staleTime: 30_000,              // refetch every 30s while subscribed
  onData: (rows) => renderLedger(rows),
  onError: (e) => showToast(e.message),
});
// later:
q.refresh();        // force refetch now
q.unsubscribe();    // when the screen unmounts

What the framework manages automatically:

Initial fetch on subscription
Periodic refetch on staleTime
Pause while the tab is hidden (Page Visibility API)
Refetch on screen-show via window.helmOnTopicShown (set by switchScreen)
Refetch on online/offline transitions
Cache deduplication — multiple subscribers on the same {endpoint, params} share one in-flight request
Invalidation — when any caller invalidates the topic, every query subscribing to it refetches

`helmMutate({ endpoint, method, body, invalidates, ...})`

The single funnel for writes.

await helmMutate({
  endpoint: '/api/customers',
  method: 'POST',
  body: { display_name: '…', phone: '…' },
  invalidates: ['customers.list', 'customers.search'],
  onConflict: (current) => helmConfirm({ … }),
});

What it adds on top of plain fetch:

Auto-generates an Idempotency-Key header (uses the existing withIdempotency contract)
Routes 409 to onConflict (Layer 1 integration)
After the server acknowledges, invalidates invalidates[] — Layer-1 + Layer-4 are coupled here, on purpose
Returns the parsed response body

`helmInvalidate(topic)`

Manually invalidate a topic from any code path that mutates state outside helmMutate.

Bridge for existing imperative dispatch

The 51 document.dispatchEvent('helm:transaction-created') / helm:customer-attached / helm:parked-sales-changed etc. sites in the codebase don't need to be rewritten. A small helm:* → helmInvalidate adapter at the bottom of helm-query.js bridges them, so old code keeps working while new code migrates.

First production use

The Sales screen's Today's sales ledger is the proof-of-concept consumer (v0.4.235). It subscribes to transactions.today; every sale post / refund / on-account charge invalidates that topic; the ledger refetches without the imperative setInterval(refreshLedger, 30_000) that was there before.

How the layers compose

Operator clicks Save in an edit modal
   │
   ▼
helmMutate({endpoint, method, body, invalidates, onConflict})
   │
   ├── adds Idempotency-Key
   │
   ▼
Worker: withIdempotency wrapper (existing)
   │
   ▼
Worker: UPDATE WHERE id=? AND version=?     ← Layer 1
   │
   ├── 0 rows → 409 with current state
   │             │
   │             ▼
   │       helmMutate.onConflict → reload prompt → invalidate topic
   │
   └── 1 row updated, version++ → 200 OK
                  │
                  ▼
   helmMutate invalidates listed topics    ← Layer 4
                  │
                  ▼
   every helmQuery on those topics refetches
                  │
                  ▼
   (eventually) Layer 2 SSE pushes the change
   to other operators' browsers too

What this is not

Not an ORM. No model objects, no migrations-from-schema-classes. Plain SQL + plain JSON.
Not a state library (no Redux, no signals, no XState). It's a query/subscribe layer over fetch; topics + invalidations are the only abstraction.
Not solving offline conflict-merge. The original Layer 3 framing got deferred when the live-shop pain point (two tills, last tube) outranked the offline-divergence pain point. The merge story comes back when the offline write queue lands.

The problem each layer solves​

Layer 1 — Optimistic locking (live)​

Layer 2 — Durable Object + WebSocket realtime broadcast (live)​

Layer 3 — Cart holds / reserve-on-read inventory (live)​

Layer 4 — Reactive query framework (live)​

helmQuery({ topic, endpoint, ... })​

helmMutate({ endpoint, method, body, invalidates, ...})​

helmInvalidate(topic)​

Bridge for existing imperative dispatch​

First production use​

How the layers compose​

What this is not​

See also​