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

Centralized cloud architecture treats the browser as an ephemeral presentation layer reliant on a remote source of truth. For collaborative canvases, offline-capable productivity suites, and latency-critical developer tools, serializing mutations across a round-trip to an arbitrary cloud region imposes an unacceptable UX tax.

Local-first software shifts state ownership back to the periphery. The client's local persistent storage acts as the primary source of truth, while the network becomes an asynchronous, eventual-consistency convergence bus. Designing these systems requires solving three systems problems:

1. Executing durable, sub-millisecond relational transactions on the client.
2. Resolving state divergence deterministically across asynchronous network topologies.
3. Scaling relay infrastructure without bottlenecking on centralized coordination.

1. Client-Side Persistence: SQLite over WASM and OPFS

Traditional browser storage engines fail under high-concurrency transactional loads. localStorage is synchronously blocking and limited to stringified key-value data. IndexedDB exhibits severe latency spikes under cursor iteration, suffers from browser-specific transaction lifecycle quirks, and incurs structural-clone overhead across threads.

The production standard for client-side storage compiles SQLite to WebAssembly (WASM), paired with the Origin Private File System (OPFS). OPFS provides an isolated, optimized disk partition accessed via the File System Access API. Within a dedicated WebWorker, OPFS exposes FileSystemSyncAccessHandle, which provides synchronous, zero-copy read and write primitives directly to underlying OS storage blocks.

+-----------------------------------------------------------+
| Web Application (Main Thread UI)                          |
+-----------------------------------------------------------+
              | (Comlink / MessageChannel RPC)
              v
+-----------------------------------------------------------+
| Dedicated Storage Worker (Web Worker)                     |
|   +-----------------------------------------------------+  |
|   | SQLite WASM Engine                                  |  |
|   |   +-----------------------------------------------+ |  |
|   |   | VFS Layer (Virtual File System)               | |  |
|   +---+-----------------------------------------------+----+  |
|       | synchronous FileSystemSyncAccessHandle read/write |
|       v                                                   |
|   +-----------------------------------------------------+  |
|   | OPFS Storage Partition                              |  |
+---+-----------------------------------------------------+--+

Because FileSystemSyncAccessHandle is restricted to Web Workers to keep file I/O off the UI thread, the application thread communicates with the storage worker through postMessage or MessageChannel abstractions. Running SQLite in Write-Ahead Logging (WAL) mode is not yet fully viable across all WASM VFS implementations due to missing multi-process POSIX shared-memory primitives (shm_*). Most implementations configure SQLite to use a rollback journal (PRAGMA journal_mode = DELETE or MEMORY) over an OPFS VFS driver:

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

const promiser = await new Promise<Function>((resolve) => {
  const p = sqlite3Worker1Promiser({ onready: () => resolve(p) });
});

await promiser('open', {
  filename: 'app_state.db',
  vfs: 'opfs',
});

// Configure synchronous durability and page size
await promiser('exec', {
  sql: `
    PRAGMA page_size = 4096;
    PRAGMA synchronous = NORMAL;
    PRAGMA cache_size = -64000; -- 64MB memory cache
    
    CREATE TABLE IF NOT EXISTS local_mutations (
      id TEXT PRIMARY KEY,
      data BLOB NOT NULL,
      hlc_timestamp TEXT NOT NULL
    );
  `,
});

To run this stack, the hosting origin must serve strict security headers:

Cross-Origin-Opener-Policy: same-origin
Cross-Origin-Embedder-Policy: require-corp

These headers unlock SharedArrayBuffer, allowing efficient memory transfers and high-performance multithreading primitives between the main application and background worker threads.

2. Conflict Resolution: Deterministic Merging via CRDTs

When disconnected clients write concurrently to their local SQLite instances, application state diverges. Resolving divergence through distributed locks is impossible under network partitions, and Last-Write-Wins (LWW) based on unsynchronized wall-clock time causes silent data loss due to clock drift.

Local-first architectures resolve divergence using Conflict-free Replicated Data Types (CRDTs). State-based CRDTs (CvRDTs) model distributed state as a bounded semilattice $\langle S, \sqcup \rangle$, where state transitions satisfy:

1. Associativity: $(a \sqcup b) \sqcup c = a \sqcup (b \sqcup c)$
2. Commutativity: $a \sqcup b = b \sqcup a$
3. Idempotence: $a \sqcup a = a$

The join operator $\sqcup$ ensures that nodes can process updates out of order, repeatedly, or in batches while still converging deterministically on identical state.

Automerge vs. Yjs: Engine Internals

+--------------------------------------------------------------------+
|                         CRDT Architecture                          |
+---------------------------------+----------------------------------+
|           Automerge             |               Yjs                |
+---------------------------------+----------------------------------+
| Model: Immutable Document Tree  | Model: Shared Type Arrays/Maps   |
| Engine: Rust Core (WASM FFI)    | Engine: Monolithic JS / WASM     |
| Internal: Columnar B-Tree       | Internal: Doubly Linked Lists    |
| Conflict: Lamport DAG Ordering  | Conflict: Yata Algorithm         |
+---------------------------------+----------------------------------+

• Automerge represents mutations as an append-only causal Directed Acyclic Graph (DAG). Automerge 2.0 encodes this graph into a columnar binary format using run-length encoding (RLE). This design excels at tracking deep JSON structures and complete immutable histories, but can experience higher memory overhead during continuous linear sequence edits.

• Yjs targets sequence and rich-text editing using the Yata algorithm. Internally, Yjs structures sequence data as a doubly linked list of chunked item structs:

$$\text{Item} = \langle \text{id}, \text{originLeft}, \text{originRight}, \text{content}, \text{deleted} \rangle$$

Yata resolves concurrent insertions at the same position without re-traversing the document history. It evaluates origin boundaries and client identifiers to place nodes deterministically in $\mathcal{O}(k)$ time, where $k$ is the number of concurrent inserts at that exact coordinate:

import * as Y from 'yjs';

const doc1 = new Y.Doc();
const doc2 = new Y.Doc();

const text1 = doc1.getText('content');
const text2 = doc2.getText('content');

// 1. Concurrent divergent writes occur offline
text1.insert(0, 'Alpha ');
text2.insert(0, 'Omega ');

// 2. Exchange state vectors to compute diffs
const stateVector1 = Y.encodeStateVector(doc1);
const stateVector2 = Y.encodeStateVector(doc2);

// 3. Compute minimal binary deltas
const deltaForDoc2 = Y.encodeStateAsUpdate(doc1, stateVector2);
const deltaForDoc1 = Y.encodeStateAsUpdate(doc2, stateVector1);

// 4. Apply updates deterministically across peers
Y.applyUpdate(doc2, deltaForDoc2);
Y.applyUpdate(doc1, deltaForDoc1);

// doc1 and doc2 converge to the exact same string
console.assert(text1.toString() === text2.toString());

3. Synchronization: Edge Relays as Blind Brokers

Traditional web systems process synchronization by routing all traffic through transactional application servers that unpack payloads, run validation logic, and write to a monolithic database. In local-first systems, validation is client-side or verified cryptographically, allowing edge synchronization relays to act as content-agnostic binary routers.

+----------+                               +----------+
| Client A |                               | Client B |
+----+-----+                               +-----+----+
     |                                           |
     | (State Vector $V_A$)                      | (State Vector $V_B$)
     v                                           v
+-----------------------------------------------------+
|                  Edge Relay Mesh                    |
|     (Cloudflare Workers / Durable Objects)          |
|  - Content-Agnostic Pipe                            |
|  - Ephemeral Memory Session                         |
|  - Broadcasts Opaque Byte Buffers (Uint8Array)      |
+--------------------------+--------------------------+
                           |
                           v (Periodic Chunked Flush)
                 +-------------------+
                 | Cold Storage (S3) |
                 +-------------------+

Synchronization relies on State Vectors—compact representations tracking the latest contiguous sequence number processed from each client ID:

$$V = \{ C_1: s_1, C_2: s_2, \dots, C_n: s_n \}$$

Protocol Execution Phase

1. Vector Exchange: Client $A$ establishes a WebSocket or WebTransport stream to the nearest edge relay, transmitting its state vector $V_A$.
2. Delta Extraction: The relay compares $V_A$ with the vector of the remote channel $V_{Relay}$. Missing updates are calculated via: $$\Delta_{Relay \to A} = \text{diff}(V_A, \text{State}_{Relay})$$
3. Stream Delta: The relay emits $\Delta_{Relay \to A}$ as an opaque Uint8Array. Client $A$ applies the binary payload locally within an OPFS transaction.
4. Broadcast Pipeline: When Client $A$ generates a local write, it commits the mutation to local SQLite and immediately streams the raw CRDT update slice to the relay. The relay fans out this byte payload to all active subscriptions on that document ID.

Because the edge relay avoids parsing JSON or compiling CRDT logic, throughput is bound strictly by network I/O rather than CPU time.

4. Architectural Trade-offs and Failure Modes

Tombstone Bloat and History Pruning

CRDTs implement deletion by appending "tombstones" to preserve causal relationships. Over time, documents with high churn accumulate substantial metadata overhead, degrading both memory performance and disk throughput.

Cleaning tombstones requires establishing causal stability: a tombstone can only be safely reclaimed if every participating peer has acknowledged a state vector strictly greater than the tombstone's logical timestamp. In decentralized systems with intermittent connectivity, a single abandoned offline client can block garbage collection, causing unbounded storage growth unless the relay enforces a hard retention horizon that forces out-of-date clients to re-bootstrap from a compacted snapshot.

Schema Evolution over Replicated Relational Tables

Embedding CRDTs directly into an application can be achieved through two primary models:

1. Document-as-a-Blob: Storing serialized CRDT state trees inside single SQLite table rows. This simplifies synchronization, but prevents developers from running index-accelerated relational queries (JOIN, arbitrary WHERE filtering) directly against the inner CRDT fields.
2. Conflict-Free Replicated Relations (CRRs): Modifying SQLite via extensions (such as cr-sqlite) to introduce column-level Conflict-Free Replicated Relations using Hybrid Logical Clocks (HLCs).

Under the CRR model, schema migrations are distributed systems operations. Modifying, renaming, or dropping a column must be strictly backwards-compatible across older application builds running on disconnected edge clients. Migrations must follow additive-only paradigms: new columns can be introduced, but existing columns must never be removed destructively without multi-version deprecation windows.

Zero-Trust Access Control

Because local-first relays treat CRDT payloads as opaque binary streams, they cannot enforce application-level Row-Level Security (RLS) dynamically at execution time. Authorization must occur at document-partition boundaries, or rely on end-to-end cryptographic schemes where only authorized clients hold the keys to decrypt the synchronization deltas.

Synthesis

Local-first system design treats the browser as a durable compute node and demotes the cloud to an asynchronous transport bus. Combining SQLite over OPFS for low-latency relational transactions, CRDT semilattices for deterministic convergence, and lightweight edge relays for delta distribution produces applications that operate reliably across network partitions, eliminate round-trip UI latency, and remain fully functional offline.