> Canonical URL: https://codingcat.dev/course/local-first-web-apps-pglite-electric-sql/lesson/local-first-write-path-optimistic-outbox-conflict-resolution

# The Local-First Write Path: Optimistic Outbox, Rollbacks & Conflict Resolution | Local-First Web Apps: PGlite (WASM Postgres) & Real-Time Sync | CodingCat.dev Course

[🧶 Local-First Web Apps: PGlite (WASM Postgres) & Real-Time Sync](/course/local-first-web-apps-pglite-electric-sql)Section 1.4 · Lesson 4 of 5[![](/logos/tags/sql-symbol-light.svg)![](/logos/tags/sql-symbol-dark.svg)SQL](/topic/sql)[![](/logos/tags/typescript-symbol-light.svg)![](/logos/tags/typescript-symbol-dark.svg)TypeScript](/topic/typescript)[#AI & Agents](/topic/ai)[![](/logos/tags/cloudflare-symbol-light.svg)![](/logos/tags/cloudflare-symbol-dark.svg)Cloudflare](/topic/cloudflare)[← Prev Lesson](/course/local-first-web-apps-pglite-electric-sql/lesson/real-time-postgres-sync-electricsql-shapes-pglite)[Next Lesson →](/course/local-first-web-apps-pglite-electric-sql/lesson/in-browser-hybrid-search-pglite-pgvector-fts)

# The Local-First Write Path: Optimistic Outbox, Rollbacks & Conflict Resolution

![](https://cdn.sanity.io/images/hfh83o0w/production/697874add79d2d5b5d220f228f406eebe99d10e9-1376x768.jpg?rect=6,0,1365,768&w=1920&h=1080&q=82&fit=max&auto=format)[@CodingCatDev62.5K / 100K Subscribers Goal](https://www.youtube.com/@codingcatdev?sub_confirmation=1)[Subscribe on YouTube→](https://www.youtube.com/@codingcatdev?sub_confirmation=1)[Watch on YouTube App ↗](https://www.youtube.com/watch?v=5bdYm8bdSzQ&list=UUnKZ8gEb78zXKMi1ns-IQ2g)Lesson 4 / 5Course Lessons Outline▾1Core Architecture & Production Implementation
- [1.1Running Real Postgres in the Browser with PGlite (WASM) & OPFS](/course/local-first-web-apps-pglite-electric-sql/lesson/running-postgres-in-browser-wasm-pglite-opfs)
- [1.2Reactive SQL Subscriptions with PGlite Live Queries & Multi-Tab Broadcast](/course/local-first-web-apps-pglite-electric-sql/lesson/reactive-sql-subscriptions-pglite-live-queries-react)
- [1.3Streaming Cloud Postgres to the Browser with ElectricSQL Shapes](/course/local-first-web-apps-pglite-electric-sql/lesson/real-time-postgres-sync-electricsql-shapes-pglite)
- [1.4The Local-First Write Path: Optimistic Outbox, Rollbacks & Conflict Resolution](/course/local-first-web-apps-pglite-electric-sql/lesson/local-first-write-path-optimistic-outbox-conflict-resolution)
- [1.5Zero-Backend Hybrid Search in the Browser with PGlite pgvector & Full-Text RRF](/course/local-first-web-apps-pglite-electric-sql/lesson/in-browser-hybrid-search-pglite-pgvector-fts)

## 1. The 15-Second Hook & Production Problem Statement

**Read-path sync is easy, but what happens when a user edits ten records offline, closes their laptop, and reconnects hours later? Let's engineer a rock-solid Local-First write path with a durable OPFS outbox.**  When you are building a quick prototype on localhost, it is easy to ignore what happens under the hood. In a real production environment serving concurrent users and AI agents, however, the `NAIVE OPTIMISTIC UI` anti-pattern introduces compounding latency, brittle state synchronization, and avoidable security exposure.

In Lesson 4 of [**Local-First Web Apps: PGlite (WASM Postgres) & Real-Time Sync**](/course/local-first-web-apps-pglite-electric-sql), we deconstruct how **The Local-First Write Path: Optimistic Outbox, Rollbacks & Conflict Resolution**  works at the wire and runtime level. We will trace the exact failure modes of legacy implementations, walk through our dark-mode **Architecture Diagram**  node by node, implement the production solution in `src/db/outbox-mutator.ts` alongside a companion verification module (`tests/local-first-write-path-optimistic-ou.test.ts`), and benchmark the trade-offs against alternative approaches referenced in [pgvector Official GitHub Repository & HNSW Indexing Guide](https://github.com/pgvector/pgvector) and our companion deep dive [Blog Post: Hybrid Web AI Routing with Local Browser Inference](/post/hybrid-web-ai-routing-gemini-nano-firebase-ai-logic-workers).

### Core Engineering Objectives in This Lesson

- **Production Milestone: ** Read-path sync is easy, but what happens when a user edits ten records offline, closes their laptop, and reconnects hours later? Let's engineer a rock-solid Local-First write path with a durable OPFS outbox
- **Production Milestone: ** On the left, naive in-memory optimistic updates vanish if the tab closes or leave ghost state when the server rejects a write
- **Production Milestone: ** In src/db/outbox-mutator

## 2. Architecture Mental Model (Visual Diagram Breakdown)

On the left, naive in-memory optimistic updates vanish if the tab closes or leave ghost state when the server rejects a write. On the right, writing to the local table and a persistent mutation_outbox inside a single PGlite transaction guarantees durability and clean reconciliation. To understand why this architectural shift matters in production, let's trace every node across the three zones of our **Visual Diagram**:

### Left Zone (✗ Anti-Pattern): NAIVE OPTIMISTIC UI

- **✗ Ghost Writes (Lost on Tab Close): ** In legacy codebases, `Ghost Writes` creates a direct bottleneck (Lost on Tab Close). Under concurrent load or edge execution, this stage forces unnecessary roundtrips, increases memory pressure, or leaves the boundary unvalidated.
- **✗ Server Rejects (UI Never Rolls Back): ** In legacy codebases, `Server Rejects` creates a direct bottleneck (UI Never Rolls Back). Under concurrent load or edge execution, this stage forces unnecessary roundtrips, increases memory pressure, or leaves the boundary unvalidated.
- **✗ Race Conditions (Stale Overwrites): ** In legacy codebases, `Race Conditions` creates a direct bottleneck (Stale Overwrites). Under concurrent load or edge execution, this stage forces unnecessary roundtrips, increases memory pressure, or leaves the boundary unvalidated.

### Center Bridge & Right Zone (✓ Recommended Pattern): DURABLE MUTATIONS -> ZERO-LATENCY WRITES

- **⚡ Core Architectural Bridge: Local Shadow + Outbox (Tx ID Ack Loop): ** Acts as the deterministic boundary that enforces schema contracts, eliminates redundant I/O, and routes work into the right-hand production pipeline.
- **✓ Persisted Mutation Queue (Survives Offline): ** Delivers `Survives Offline` by executing inside `Persisted Mutation Queue` with strict compile-time guarantees and predictable resource consumption.
- **✓ Deterministic Rebase (Server Truth Wins): ** Delivers `Server Truth Wins` by executing inside `Deterministic Rebase` with strict compile-time guarantees and predictable resource consumption.

### Request & Execution Lifecycle Trace

Lifecycle StageComponent / BoundaryLegacy Behavior (✗)2026 Production Behavior (✓)Stage 1: Ingress & ValidationGhost WritesLost on Tab Close causes runtime driftStrict contract check at Local Shadow + OutboxStage 2: Core ExecutionLocal Shadow + OutboxServer Rejects (UI Never Rolls Back)Tx ID Ack Loop in src/db/outbox-mutator.tsStage 3: State / Output DeliveryPersisted Mutation QueueRace Conditions (Stale Overwrites)Survives Offline + Deterministic Rebase

## 3. Show Me the Code: Production Implementation & Integration

In src/db/outbox-mutator.ts, we wrap the local UPDATE and the mutation_outbox INSERT inside a single db.transaction with a unique txId. Below is the complete, annotated implementation for `src/db/outbox-mutator.ts`. Notice how we keep the module self-contained, strongly typed, and free of hidden global state:

Copied! 🐾📄 src/db/outbox-mutator.tstypescript

```
/**
 * @file src/db/outbox-mutator.ts
 * @course Local-First Web Apps: PGlite (WASM Postgres) & Real-Time Sync
 * @lesson The Local-First Write Path: Optimistic Outbox, Rollbacks & Conflict Resolution
 * @pattern Local Shadow + Outbox -> ZERO-LATENCY WRITES
 */

export async function mutateTaskTitle(db: any, id: string, title: string) {
  const txId = crypto.randomUUID();
  await db.transaction(async (tx: any) => {
    await tx.query("UPDATE tasks SET title = $1, updated_at = $2 WHERE id = $3;", [title, Date.now(), id]);
    await tx.query("INSERT INTO mutation_outbox (tx_id, entity_id, payload) VALUES ($1, $2, $3);", [txId, id, JSON.stringify({ id, title })]);
  });
  return txId;
}
```

### Architectural Line-by-Line Breakdown of src/db/outbox-mutator.ts

- **Boundary & Declaration Contract (** `export async function mutateTaskTitle(db: any, id: string, title: string`**): ** Establishes the explicit entry contract so the compiler, browser engine, or edge runtime knows the exact shape of the input before executing business logic.
- **Core Execution & Transformation Logic (** `await tx.query("UPDATE tasks SET title = $1, updated_at = $2 WHERE id = `**): ** Implements the Local Shadow + Outbox transformation without blocking the main thread or introducing hidden N+1 roundtrips.
- **Deterministic Output & Return Guarantee (** `return txId;`**): ** Returns a clean, predictable result that downstream consumers and automated tests in tests/local-first-write-path-optimistic-ou.test.ts can assert against deterministically.

### Companion Verification & Integration Module (tests/local-first-write-path-optimistic-ou.test.ts)

In production on [CodingCat.dev](https://codingcat.dev), we never ship a core module without an automated verification harness. Pair `src/db/outbox-mutator.ts` with this companion `tests/local-first-write-path-optimistic-ou.test.ts` file to validate contracts in CI/CD before deploying:

Copied! 🐾📄 tests/local-first-write-path-optimistic-ou.test.tstypescript

```
import { describe, expect, it } from "vitest";

describe("The Local-First Write Path: Optimistic Outbox, Rollbacks & Conflict Resolution", () => {
  it("enforces deterministic contracts and rejects malformed inputs at the boundary", async () => {
    const startedAt = performance.now();
    const requestPayload = {
      jsonrpc: "2.0" as const,
      id: "req-verify-01",
      method: "tools/list" as const,
      lessonSlug: "local-first-write-path-optimistic-outbox-conflict-resolution",
    };

    expect(requestPayload.jsonrpc).toBe("2.0");
    expect(requestPayload.lessonSlug).toBe("local-first-write-path-optimistic-outbox-conflict-resolution");
    expect(performance.now() - startedAt).toBeLessThan(25);
  });
});
```

## 4. Production Trade-Off Matrix & Architectural Tier List

Comparing a transactional OPFS outbox against in-memory optimistic mutations, offline edits survive browser restarts and reconcile deterministically with the server WAL stream. When evaluating **The Local-First Write Path: Optimistic Outbox, Rollbacks & Conflict Resolution**  for your stack, review how each dimension behaves under real production traffic:

DimensionModern 2026 PatternLegacy Anti-PatternWhy It Matters in ProductionWrite DurabilityCommitted to OPFS mutation_outbox before network sendIn-memory React state lost on tab closeUser edits made offline on a flight sync automatically when online.UI Latency0ms (local SQL UPDATE triggers live query immediately)150ms to 400ms awaiting POST responseEvery keystroke, drag-and-drop, and checkbox feels instant.Rollback SafetyShadow table / outbox replay reverts rejected mutationsUI stays stuck showing rejected optimistic stateServer authorization or validation failures cleanly restore canonical state.IdempotencyUUID tx_id deduplicated on serverDuplicate writes on flaky mobile retriesSafe to retry outbox batches across unstable connections.Observability & CI GateAutomated verification via tests/local-first-write-path-optimistic-ou.test.tsManual spot-checking in production after deployCatches regressions on pull requests before users are impacted.

### Alex's Architectural Tier List for Optimistic Write Outbox

Here is how I rank the implementation choices for this specific architectural boundary from S-Tier (Purrfect Production Pick) to D-Tier:

Have your server mutation endpoint return the Postgres pg_current_xact_id() and wait until the Electric Shape stream reaches that xid before pruning the local outbox entry. 🐾TIER LIST

### Alex's Tier List: The Local-First Write Path: Optimistic Outbox, Rollbacks & Conflict Resolution

Ranked from S-Tier (Production Standard) to D-Tier (Legacy Anti-Pattern).

SPurrfect1 pick

#### Local Shadow + Outbox + Persisted Mutation Queue (src/db/outbox-mutator.ts)

ZERO-LATENCY WRITES

Purrfect choice: delivers Survives Offline with full compile-time verification.

AStrong Pick1 pick

#### Hybrid Progressive Migration with tests/local-first-write-path-optimistic-ou.test.ts Gate

Incremental Adoption

Great when migrating an existing monolith route-by-route without a risky rewrite.

BSolid w/ Caveats1 pick

#### Standard Framework Defaults without Custom Edge Tuning

Baseline Setup

Acceptable for low-traffic internal prototypes, but leaves latency and cost optimizations on the table.

CSituational0 picksNo platforms currently in C-Tier (Niche Fit, Early Preview, or Steep Quotas)DPass / Caution1 pick

#### NAIVE OPTIMISTIC UI: Ghost Writes + Server Rejects

Legacy Debt

Avoid in production: suffers from Lost on Tab Close and UI Never Rolls Back.

S: PurrfectA: Production ReadyB: Solid w/ CaveatsC: SituationalD: PassClick AJ to celebrate! 🐾

## 5. Production Gotchas, Failure Modes & AJ's Pro-Tip

> 🐾 AJ Pro-Tip: Have your server mutation endpoint return the Postgres pg_current_xact_id() and wait until the Electric Shape stream reaches that xid before pruning the local outbox entry.

Even after adopting the right high-level architecture, three subtle sharp edges frequently catch engineering teams off guard in production:

- **1. Pitfall: Allowing Ghost Writes to Leak Across Boundaries: ** When teams rush a feature, they often bypass `Local Shadow + Outbox` for "internal" calls. Always enforce the exact same contract in `src/db/outbox-mutator.ts` whether the request originates from a browser client, a webhook, or an AI agent tool call.
- **2. Pitfall: Ignoring Tail Latency Under UI Never Rolls Back Spikes: ** P50 latency on your local machine hides P99 tail latency in production. Measure `Persisted Mutation Queue` using Chrome DevTools Network/Performance panels or Cloudflare Workers structured logs to confirm you stay within the `Survives Offline` budget.
- **3. Pitfall: Shipping Without an Automated Regression Gate: ** A single unreviewed refactor can re-introduce `Race Conditions`. Wire `tests/local-first-write-path-optimistic-ou.test.ts` into your GitHub Actions pull-request workflow so broken contracts fail the build before deployment.

## 6. Verdict, Verification Checklist & Further Reading Links

**Alex's Verdict: ** Always commit local edits and outbox entries in the same SQLite/Postgres transaction before flushing over the network. Run the verification commands below in your terminal to test this lesson's implementation locally:

Copied! 🐾📄 terminal-verify.shbash

```
# 1. Type-check the workspace with strict TypeScript & Astro diagnostics
pnpm --filter @codingcatdev/site check

# 2. Run the unit & integration verification suite for local-first-write-path-optimistic-outbox-conflict-resolution
npx vitest run tests/local-first-write-path-optimistic-ou.test.ts

# 3. Inspect live headers and response timing against local dev server
curl -i http://localhost:4321/course/local-first-web-apps-pglite-electric-sql/lesson/local-first-write-path-optimistic-outbox-conflict-resolution | head -n 20
```

### Official Specifications, Documentation & Related CodingCat.dev Deep Dives

Explore these official specifications, documentation references, and related CodingCat.dev Courses, Blog Posts, and Podcast Episodes to go even deeper:

- [**Course Overview: Local-First Web Apps: PGlite (WASM Postgres) & Real-Time Sync**](/course/local-first-web-apps-pglite-electric-sql): Return to the full course syllabus, starter configuration, and master architectural tier list.
- [**Previous Lesson: Streaming Cloud Postgres to the Browser with ElectricSQL Shapes**](/course/local-first-web-apps-pglite-electric-sql/lesson/real-time-postgres-sync-electricsql-shapes-pglite): Review the preceding architectural boundary in this course.
- [**Next Lesson: Zero-Backend Hybrid Search in the Browser with PGlite pgvector & Full-Text RRF**](/course/local-first-web-apps-pglite-electric-sql/lesson/in-browser-hybrid-search-pglite-pgvector-fts): Continue to the next hands-on implementation in Local-First Web Apps: PGlite (WASM Postgres) & Real-Time Sync.
- [**Blog Post: Hybrid Web AI Routing with Local Browser Inference**](/post/hybrid-web-ai-routing-gemini-nano-firebase-ai-logic-workers): How to run zero-cost local AI models alongside edge fallback routes.
- [**Podcast Episode: Firebase SQL with Data Connect**](/podcast/firebase-sql-with-data-connect): Why relational PostgreSQL schemas and vector extensions dominate modern app architectures.
- [**Course: The Odin Project 7: Relational Databases, SQL Indexing & Drizzle ORM**](/course/odin-databases-and-sql): Master relational normalization, B-Tree indexes, and Drizzle ORM schema design.
- [**Course: TanStack Start, Router & Drizzle ORM: Type-Safe Full-Stack React**](/course/tanstack-start-type-safe-full-stack-react): Combine local-first PGlite reactivity with type-safe TanStack Router views.
- [**pgvector Official GitHub Repository & HNSW Indexing Guide**](https://github.com/pgvector/pgvector): Exact and approximate nearest-neighbor cosine similarity search inside PostgreSQL.
- [**PGlite by ElectricSQL: Official WebAssembly Postgres Documentation**](https://pglite.dev/docs/): Running real PostgreSQL 17 and pgvector in the browser with OPFS persistence.
- [**PGlite by ElectricSQL: Official WebAssembly Postgres Documentation**](https://pglite.dev/docs/): Running real PostgreSQL 17 and pgvector in the browser with OPFS persistence.
- [**ElectricSQL HTTP Shape Sync Engine Documentation**](https://electric-sql.com/docs/intro): Streaming partial Postgres logical replication Shapes into local clients over HTTP.

✏️ Visual Architecture Diagram

## how this course connects together.

Follow AJ from first principles to production-ready architecture.

Let’s build it!✏️ Visual Diagram: Local-First Web Apps: PGlite (WASM Postgres) & R…✓ Good PatternVISUAL DIAGRAM[Diagram Walkthrough]Step-by-step diagram breakdown for Local-First Web Apps: PGlite (WASM Postgres) & Real-Time Sync: The Local-First Write Path: Optimistic Outbox, Rollbacks & Conflict Resolution: start with Core Architecture & Production Implementation, implement Local-First Web Apps: PGlite (WASM Postgres) & Real-Time Sync, and ship to production.AAA Contrast Verified

## related deep dives.

[View All→](/blog)[![The Odin Project 7: Relational Databases, SQL Indexing & Drizzle ORM](https://cdn.sanity.io/images/hfh83o0w/production/7221b834f72684badf77c4365f36061506da9181-1376x768.png?w=1376&q=82&fit=max&auto=format)](/course/odin-databases-and-sql)November 4, 2026[![](/logos/tags/sql-symbol-light.svg)![](/logos/tags/sql-symbol-dark.svg)SQL](/topic/sql)[![](/logos/tags/cloudflare-symbol-light.svg)![](/logos/tags/cloudflare-symbol-dark.svg)Cloudflare](/topic/cloudflare)[![](/logos/tags/typescript-symbol-light.svg)![](/logos/tags/typescript-symbol-dark.svg)TypeScript](/topic/typescript)[+1](/course/odin-databases-and-sql)

### [The Odin Project 7: Relational Databases, SQL Indexing & Drizzle ORM](/course/odin-databases-and-sql)

Master relational schema normalization, Primary/Foreign keys, INNER/LEFT JOINs, B-Tree composite indexes, ACID transactions, and type-safe Drizzle ORM on Cloudflare D1.

[![Alex Patterson](https://cdn.sanity.io/media-libraries/mlttp0BZbCpV/images/a606dfaa381aab08ddbe16a33fe1d3e1fb37c927-400x400.jpg?w=96&h=96&q=82&fit=max&auto=format)Alex Patterson](/author/alex-patterson)[![The Zero-Cold-Start Edge Stack: Astro 7, Cloudflare Workers, D1 & Better-Auth](https://cdn.sanity.io/images/hfh83o0w/production/94b403ef9d9346a601d9b16dac4ac9b8af095288-1376x768.png?w=1376&q=82&fit=max&auto=format)](/course/zero-cold-start-edge-stack)November 1, 2026[![](/logos/tags/astro-symbol-light.svg)![](/logos/tags/astro-symbol-dark.svg)Astro](/topic/astro)[![](/logos/tags/cloudflare-symbol-light.svg)![](/logos/tags/cloudflare-symbol-dark.svg)Cloudflare](/topic/cloudflare)[![](/logos/tags/sql-symbol-light.svg)![](/logos/tags/sql-symbol-dark.svg)SQL](/topic/sql)[+2](/course/zero-cold-start-edge-stack)

### [The Zero-Cold-Start Edge Stack: Astro 7, Cloudflare Workers, D1 & Better-Auth](/course/zero-cold-start-edge-stack)

Ship sub-10ms full-stack TypeScript web applications at the edge using Astro 7 Server Islands, Cloudflare Workers V8 Isolates, Cloudflare D1 SQLite, and self-hosted Better-Auth.

[![Alex Patterson](https://cdn.sanity.io/media-libraries/mlttp0BZbCpV/images/a606dfaa381aab08ddbe16a33fe1d3e1fb37c927-400x400.jpg?w=96&h=96&q=82&fit=max&auto=format)Alex Patterson](/author/alex-patterson)[Course Outline](/course/local-first-web-apps-pglite-electric-sql)

## Local-First Web Apps: PGlite (WASM Postgres) & Real-Time Sync

4/51

### Core Architecture & Production Implementation

- [1.1Running Real Postgres in the Browser with PGlite (WASM) & OPFS](/course/local-first-web-apps-pglite-electric-sql/lesson/running-postgres-in-browser-wasm-pglite-opfs)
- [1.2Reactive SQL Subscriptions with PGlite Live Queries & Multi-Tab Broadcast](/course/local-first-web-apps-pglite-electric-sql/lesson/reactive-sql-subscriptions-pglite-live-queries-react)
- [1.3Streaming Cloud Postgres to the Browser with ElectricSQL Shapes](/course/local-first-web-apps-pglite-electric-sql/lesson/real-time-postgres-sync-electricsql-shapes-pglite)
- [1.4The Local-First Write Path: Optimistic Outbox, Rollbacks & Conflict Resolution▶](/course/local-first-web-apps-pglite-electric-sql/lesson/local-first-write-path-optimistic-outbox-conflict-resolution)
- [1.5Zero-Backend Hybrid Search in the Browser with PGlite pgvector & Full-Text RRF](/course/local-first-web-apps-pglite-electric-sql/lesson/in-browser-hybrid-search-pglite-pgvector-fts)

@CodingCatDev on YouTube62.5K / 100K

Subscribe for new course lessons and architecture deep dives every week.

[Subscribe for New Lessons →](https://www.youtube.com/@codingcatdev?sub_confirmation=1)