Architecting Local-First Software Systems: WASM SQLite, CRDTs, and Edge Relays

Thin-client web architecture is reaching an architectural inflection point. For decades, distributed systems engineers treated the browser as an ephemeral presentation tier, dispatching user intent across high-latency networks to centralized relational databases. This client-server request-response lifecycle introduces systemic liabilities: cross-region latency overhead, fragile optimistic UI reconciliation, total functional degradation during network partitions, and escalating database concurrency costs.

Local-first architecture inverts this topology. By designating the client's local persistent storage as the primary source of truth and treating the network as an asynchronous, eventually consistent synchronization fabric, applications achieve deterministic sub-millisecond mutations, native offline execution, and autonomous multi-device replication. Realizing this model in production requires orchestrating three discrete systems primitives: an in-browser relational execution engine powered by WebAssembly (WASM) and SQLite, convergence verification via Conflict-Free Replicated Data Types (CRDTs), and causally ordered edge relay infrastructure.

In-Browser Persistence via WASM SQLite and OPFS

Local-first architectures require transactional durability and relational indexing within the browser sandbox. Traditional browser storage primitives fail under real-world access patterns: localStorage is synchronous, unindexed, string-serialized, and blocks the browser event loop; IndexedDB avoids main-thread blocking through an asynchronous event-driven API, but exhibits severe cross-browser performance variance, structural transaction lifecycle bugs, and poor throughput under deep relational joins.

Modern browser storage architectures bypass these limitations by running SQLite compiled to WebAssembly, backed by the Origin Private File System (OPFS). Part of the File System Access API specification, OPFS provides a private, sandboxed filesystem root. When accessed from a dedicated Web Worker, OPFS exposes the FileSystemSyncAccessHandle, which enables synchronous, direct-to-disk block read and write operations. This bypasses the asynchronous IPC serialization overhead intrinsic to IndexedDB.

// worker.ts: Initializing SQLite with the OPFS VFS
import { sqlite3Worker1Promiser } from '@sqlite.org/sqlite-wasm';

export async function createDatabaseWorker(dbName: string) {
  const promiser = await new Promise<any>((resolve) => {
    const p = sqlite3Worker1Promiser({
      onready: () => resolve(p),
    });
  });

  // Open the database using the optimized OPFS Virtual File System
  await promiser('open', {
    filename: `${dbName}.sqlite3`,
    vfs: 'opfs',
  });

  // Configure SQLite for high-concurrency client workloads
  await promiser('exec', {
    sql: `
      PRAGMA journal_mode = WAL;
      PRAGMA synchronous = NORMAL;
      PRAGMA temp_store = MEMORY;
      PRAGMA cache_size = -64000; -- 64MB Cache
      
      CREATE TABLE IF NOT EXISTS document_nodes (
        id TEXT PRIMARY KEY NOT NULL,
        parent_id TEXT,
        type TEXT NOT NULL,
        data JSON NOT NULL,
        lamport_time INTEGER NOT NULL
      );
      CREATE INDEX IF NOT EXISTS idx_nodes_parent ON document_nodes(parent_id);
    `,
  });

  return promiser;
}

Configuring SQLite with Write-Ahead Logging (WAL) allows concurrent reads while a write transaction commits. When paired with PRAGMA synchronous = NORMAL, transactions maintain disk durability guarantees while hitting up to 10,000 discrete writes per second in Chromium-based runtimes. The browser client effectively operates an ACID-compliant analytical and transactional database engine over arbitrary local state graphs, executing complex indexed joins and subqueries with zero network hops.

Conflict-Free Replicated Data Types: Convergence at the Edge

Decoupling mutations from a central coordinating authority guarantees concurrent, diverging write streams across disconnected nodes. Ad-hoc heuristics—such as field-level Last-Write-Wins (LWW) governed by client physical wall clocks—suffer from clock skew, NTP drift, and non-deterministic overwrite failures. Local-first systems instead guarantee strong eventual consistency via Conflict-Free Replicated Data Types (CRDTs).

Mathematical Formalism: Join-Semilattices

State-based CRDTs (CvRDTs) formalize deterministic convergence by modeling state evolution as an algebraic join-semilattice. A join-semilattice is a partially ordered mathematical set defined by the tuple $\langle S, \sqcup \rangle$, where $S$ constitutes the set of all reachable states, and $\sqcup$ is an idempotent, commutative, associative binary join operator defining the least upper bound:

$$\forall x, y, z \in S:$$ $$x \sqcup y = y \sqcup x \quad \text{(Commutativity)}$$ $$(x \sqcup y) \sqcup z = x \sqcup (y \sqcup z) \quad \text{(Associativity)}$$ $$x \sqcup x = x \quad \text{(Idempotence)}$$

A partial order relation $\le$ is derived over the lattice:

$$x \le y \iff x \sqcup y = y$$

Provided that all mutation functions compute monotonic state transitions along the partial order $\le$, any two replicas $A$ and $B$ that have received the same set of updates will converge to the exact same state, irrespective of message delivery order, packet drops, or network duplication:

$$S_{\text{final}} = S_A \sqcup S_B$$

Automerge vs. Yjs: Engine Internals

Selecting an in-memory CRDT library requires evaluating core memory models, serialization formats, and garbage collection mechanisms:

Yjs implements continuous sequences by mapping content blocks to an in-memory doubly linked list, resolving concurrent insertion conflicts via the deterministic YATA tie-breaking algorithm. Automerge 2.0 compiles its core engine from Rust to WASM, organizing changes into compressed columnar chunks inspired by Apache Parquet. This provides cryptographic verification of historical operation directed acyclic graphs (DAGs), ensuring comprehensive auditability at the cost of higher CPU overhead during deserialization.

The Edge Relay Topology: Causally Sound Synchronization

Local-first systems do not eliminate server-side infrastructure; they shift the server's role from a centralized transactional bottleneck to a distributed, stateless edge relay. Edge relays do not evaluate schema validation or execute domain transactions. Instead, they act as authenticated causal message brokers that route binary deltas and persist an append-only change log.

To track distributed causality across $N$ dynamic nodes without synchronous lock coordination, replicas track causal precedence via Vector Clocks. A vector clock $V$ maps unique replica identifiers to monotonically increasing logical counters. Given two state vectors $V_A$ and $V_B$, state $A$ strictly causally precedes state $B$ ($V_A < V_B$) if and only if:

$$\forall i \in [1, N], \, V_A[i] \le V_B[i] \quad \land \quad \exists j \in [1, N], \, V_A[j] < V_B[j]$$

If neither $V_A \le V_B$ nor $V_B \le V_A$ holds, the mutations are formally concurrent ($V_A \parallel V_B$), requiring CRDT lattice join resolution.

       [Client 1: WASM SQLite + CRDT]
                    │  ▲ (Binary Delta over WebSocket)
                    ▼  │
┌────────────────────────────────────────────────────────┐
│   Edge Relay Cluster (Cloudflare Durable Objects / Fly) │
│    - Ephemeral WebSocket Session Termination           │
│    - Causal Ingestion Log: Append-only Chunk Storage   │
└────────────────────────────────────────────────────────┘
                    │  ▲
                    ▼  │ (Binary Delta over WebSocket)
       [Client 2: WASM SQLite + CRDT]

Terminating client WebSockets at an edge layer—such as Cloudflare Durable Objects or Fly.io LiteFS nodes—reduces client-to-relay network round-trip time ($RTT$) to sub-10ms ranges globally, preserving causal synchronization guarantees without routing across centralized regional backbones.

Unifying the Layers: Complete Sync Loop

The following implementation coordinates reactive in-memory CRDT mutations (Yjs), background disk persistence through the WASM SQLite worker, and edge relay network transport:

// syncEngine.ts: Coordinating SQLite persistence with CRDT edge deltas
import * as Y from 'yjs';

export class LocalFirstSyncEngine {
  private doc: Y.Doc;
  private ws: WebSocket;
  private sqliteWorker: Worker;

  constructor(documentId: string, sqliteWorker: Worker) {
    this.doc = new Y.Doc();
    this.sqliteWorker = sqliteWorker;
    this.ws = new WebSocket(`wss://relay.internal.net/v1/sync/${documentId}`);
    
    this.bindEngine();
  }

  private bindEngine(): void {
    // 1. Capture local CRDT modifications, write to DB and forward to Relay
    this.doc.on('update', (update: Uint8Array, origin: unknown) => {
      if (origin === 'network') return; // Prevent replication echo loops

      // Persist the binary delta directly to the SQLite change table
      this.sqliteWorker.postMessage({
        action: 'EXECUTE',
        sql: `INSERT INTO mutations (id, delta, created_at) VALUES (?, ?, ?)`,
        params: [crypto.randomUUID(), update, Date.now()],
      });

      // Stream binary delta to the edge relay
      if (this.ws.readyState === WebSocket.OPEN) {
        this.ws.send(update);
      }
    });

    // 2. Ingest remote updates from Edge Relay
    this.ws.binaryType = 'arraybuffer';
    this.ws.onmessage = (event: MessageEvent<ArrayBuffer>) => {
      const delta = new Uint8Array(event.data);
      
      // Apply remotely received updates into CRDT context
      Y.applyUpdate(this.doc, delta, 'network');

      // Index state snapshot locally in SQLite for fast cold boots
      this.sqliteWorker.postMessage({
        action: 'EXECUTE',
        sql: `INSERT INTO mutations (id, delta, created_at) VALUES (?, ?, ?)`,
        params: [crypto.randomUUID(), delta, Date.now()],
      });
    };
  }

  public getDocument(): Y.Doc {
    return this.doc;
  }
}

Production Trade-offs and Failure Modes

Deploying local-first primitives introduces complex systems-level failure modes:

1. CRDT Tombstone Accumulation: Deleting a record inside a sequence CRDT cannot deallocate memory immediately; it inserts an invisible tombstone item to guarantee that concurrent insertions at adjacent indices resolve unambiguously. In high-churn data stores, tombstones cause monotonic memory growth. Mitigating this requires garbage-collection protocols driven by stable vector clock frontiers: when an update's Lamport timestamp precedes the lowest common vector timestamp of all active peers, tombstones can be safely pruned during compaction.
2. Schema Drift and Decentralized Migrations: In a centralized relational database, data Definition Language (DDL) migrations execute synchronously. In local-first systems, an offline replica may record changes against schema version $v1.0$ and attempt replication months later into an application running $v2.3$. Schema architectures must be strictly additive: columns cannot be dropped or mutated destructively, table structures must leverage column capability detection, and polymorphic content must reside in forward-compatible JSON fields.
3. Decentralized Authorization Invariants: When reads and writes execute entirely client-side, traditional server-gatekeeper middleware cannot protect relational writes. Access controls must transition to asymmetric cryptographic assertions. Clients must sign discrete mutation envelopes using private keys (e.g., via ed25519 signatures or User-Controlled Authorization Networks (UCANs)), which the edge relay verifies prior to persisting or fanning out updates across the network.

Conclusion

Local-first architecture transforms web applications from fragile terminal displays into autonomous, distributed compute nodes. By anchoring durable storage inside the browser via WASM-compiled SQLite and OPFS, guaranteeing convergence across asynchronous partitions using mathematically proven CRDT join-semilattices, and synchronizing state across low-latency edge relays, systems engineers eliminate network I/O from the interactive UI loop. The result is software that is resilient to network partitions, executes with sub-millisecond local latency, and scales horizontally with minimal centralized infrastructure load.