Skip to content
曾亮懿 ZengLiangYi Blog

Migrate sql.js to Node 24 node:sqlite: WAL & Worker Sync

Replace sql.js full-file exports with Node.js 24 node:sqlite WAL in Electron: zero-rewrite compat layer, worker pools, and Claude Code/Cursor/Codex sync.

9 min read
Table of contents Show Hide

In local-first Node.js and Electron apps, sql.js is an attractive starting point because pure WASM avoids native C++ compilation across platforms. Once local AI coding history ingestion grew from 5 tools to 32 agent harnesses—including Claude Code, Cursor, Codex, OpenCode, and Cline across host folders and Windows WSL distributions—full-database db.export() serialization, main-thread parsing, and manual one-shot imports hit a wall simultaneously. In v0.7 (commit 842e44a), ChatCrystal replaced that pipeline with four cooperating layers: built-in Node.js 24 node:sqlite WAL persistence, index-first lazy materialization, a parser-versioned Gzip transcript cache, and a single-flight sync engine backed by worker_threads.

Earlier posts analyzed sql.js WASM persistence limits and bulk chat import concurrency benchmarks against the v0.6 baseline. This article walks through how those bottlenecks were eliminated in production code—and examines three remaining worker-saturation and progressive-refresh boundaries uncovered during architectural review.

Why sql.js Full-File Exports Break Down Under Multi-Source Background Sync

Because sql.js holds the entire SQLite database inside WASM linear memory without page-level file mapping, scaling from a few hundred JSONL files to thousands of sessions, multi-hundred-megabyte external SQLite stores (opencode.db), concatenated .jsonl.zst streams, and workspace trees with 70,000+ plugin files exposed three compounding bottlenecks:

  1. Whole-database write amplification: Every saveDatabase() call and 30-second autosave invoked db.export(), allocating a full-database Uint8Array snapshot and rewriting chatcrystal.db from byte zero. As message tables reached hundreds of thousands of rows, saving became a recurring memory and I/O spike.
  2. UI visibility blocked on the slowest parser: importAll() parsed every transcript inline before committing rows, coupling time-to-first-list to large external SQLite stores and slow \\wsl.localhost reads while synchronous JSON parsing starved the Fastify event loop.
  3. Uncoordinated manual and watcher triggers: A one-shot Import button and an isolated watcher boolean flag could neither share progress nor prevent startup I/O stampedes when chokidar directory walks collided with initial imports.
Dimension v0.6 Baseline v0.7 Architecture Engineering Impact
Primary DB persistence In-memory sql.js + full db.export() rewrite Node 24 node:sqlite (DatabaseSync + WAL) Durable per-transaction commits; saveDatabase() is a cheap WAL checkpoint
External IDE DB reads Read-only sql.js byte snapshot Retained sql.js (with 20s idle handle cache) Zero SQLITE_BUSY or -wal/-shm contention on running editors
Time to list visibility After full transcript parsing Immediate pending rows on scan(); lazy parse Cold-start list populates at filesystem fs.stat speed
Repeat parse avoidance Skipped only via DB size + mtime Backed by <dataDir>/transcript-cache/*.json.gz Survives DB resets, cancelled syncs, and on-demand reads
Sync orchestration Manual button + isolated watcher flag Single-flight SyncEngine + deferred targeted watcher Eliminates startup I/O stampedes and scopes file events per source

Migrating sql.js to Node.js 24 node:sqlite (WAL Mode) Without Rewriting Queries

Switching primary storage from sql.js to the built-in node:sqlite (DatabaseSync) module in Node.js 24 (bundled natively in Electron 44) unlocks file-backed SQLite Write-Ahead Logging (WAL) without reintroducing better-sqlite3 native addon builds (node-gyp and Electron ABI mismatches) across CLI, Docker, and desktop targets.

To avoid rewriting callers across routes, migrations, and tests, server/src/db/sqlite-compat.ts wraps DatabaseSync in a 398-line SqliteCompatDatabase adapter that reproduces the project’s sql.js contract:

  • Immediate WAL durability and foreign keys: Connection startup executes PRAGMA journal_mode = WAL, PRAGMA synchronous = NORMAL, and PRAGMA foreign_keys = ON. Every committed transaction persists directly to the WAL file, reducing saveDatabase() to a best-effort PRAGMA wal_checkpoint(TRUNCATE).
  • Lexical multi-statement splitting with CREATE TRIGGER awareness: sql.js accepts semicolon-joined SQL batches in exec() and run(), whereas DatabaseSync.prepare() compiles a single statement. splitSqlStatements() scans quotes, bracketed identifiers, and SQL comments while keeping internal semicolons intact inside CREATE TRIGGER … BEGIN …; END; bodies.
  • Named parameter prefixes and empty-result parity: sql.js permits :, $, and @ prefixes on parameter object keys ({ ':id': 'abc' }) and returns [] when a query yields zero rows; toNamedParams() and execPrepared() preserve both behaviors while DatabaseSync.serialize() backs export() for snapshot tests.
export class SqliteCompatDatabase {
  private readonly handle: DatabaseSync;

  constructor(filePath: string) {
    this.handle = new DatabaseSync(filePath);
    this.handle.exec('PRAGMA journal_mode = WAL');
    this.handle.exec('PRAGMA synchronous = NORMAL');
    this.handle.exec('PRAGMA foreign_keys = ON');
  }

  checkpoint(): void {
    try {
      this.handle.exec('PRAGMA wal_checkpoint(TRUNCATE)');
    } catch {
      // Active readers or open transactions leave the WAL intact until the next checkpoint
    }
  }
}

Just as important is why sql.js stayed for external databases: while ChatCrystal owns chatcrystal.db via node:sqlite, adapters reading third-party SQLite stores—Cursor (state.vscdb), OpenCode (opencode.db), Goose, Hermes, ZCode, and Alma—still load byte snapshots into sql.js. Opening native WAL-aware connections against another running IDE’s live SQLite file risks SQLITE_BUSY errors and -wal/-shm side effects; in-memory WASM snapshots remain completely lock-free.

Lazy-Loading Large JSONL & SQLite Histories: Index-First Rows and Gzip Cache

Perceived startup speed depends on when conversations appear in the list, not when the thousandth file finishes parsing. server/src/services/import.ts decouples discovery from parsing with a two-phase Index-First pipeline.

Phase 1: Insert pending Index Rows on Scan, Materialize on Demand

Each adapter’s scan() performs only directory walks and fs.stat calls. As each source scan completes, importAll() commits metadata rows with transcript_status = 'pending' via ensureIndexedConversation() (resetting existing rows back to 'pending' whenever file_size or file_mtime changes).

Users can browse the full session list immediately even while hundreds of transcripts are still queued—or after cancelling a sync midway. Opening an unparsed conversation in server/src/routes/conversations.ts (or running summarization) triggers materializeConversationById() inline:

if (
  loaded.conversation.transcript_status &&
  loaded.conversation.transcript_status !== 'materialized'
) {
  const materialized = await materializeConversationById(id);
  if (materialized === 'error') {
    reply.status(500);
    return { success: false, error: 'Failed to load the conversation transcript' };
  }
  loaded = loadConversation();
}

Materialization preserves two invariants: if a parser rewrites the canonical session ID after reading message contents (parsed.id !== meta.id), the temporary scan placeholder is removed first; if the parsed transcript contains fewer than two messages (parsed.messages.length < 2), its indexOnly placeholder is deleted so noise files never diverge from full-import rules.

Phase 2: Parser-Versioned Atomic Gzip Transcript Cache

To avoid reparsing large files across interrupted syncs, on-demand detail views, and database resets, server/src/services/transcript-cache.ts persists compressed ParsedConversation payloads at <dataDir>/transcript-cache/<source>/<sha1>.json.gz.

Every dimension that can affect parser output is folded into a NUL-separated 7-tuple SHA-1 key (extending the identity rules in SourceAdapter internals):

function cacheKey(meta: TranscriptCacheMeta, parserVersion: string): string {
  const parts = [
    meta.source,
    meta.id,
    parserVersion,
    meta.filePath,
    String(meta.fileSize),
    meta.fileMtime,
    meta.rootDir ?? '',
  ];
  return createHash('sha1').update(parts.join('\0')).digest('hex');
}

Hashing parserVersion (such as codex@4 for Codex 0.160+ event streams or opencode@2 for dual-schema deduplication) alongside rootDir (distinguishing host vs. WSL roots) invalidates stale cache entries automatically whenever a parser bug is fixed. Writes stage to <sha1>.json.gz.<pid>.<uuid>.tmp before an atomic rename(), prune archives beyond 512 MB or 30 days every 20 writes, and degrade silently to a fresh parse on corrupt files or Windows file locks.

Node.js worker_threads Pool (tsx Support) and Deferred chokidar v4 Watching

Replacing manual imports with background sync without slowing down developer machines required coordinating concurrency, worker isolation, and filesystem watchers:

  1. Single-flight SyncEngine (server/src/services/sync.ts): Startup sync (2.5 seconds after boot), manual POST /api/sync/start triggers, and watcher events all enter one state machine (idle, syncing, cancelled, error). Overlapping triggers merge into pendingSources for an immediate follow-up pass, while cancel() clears pending work and signals an AbortSignal between files.
  2. Gracefully degrading ParserPool (server/src/services/parse-pool.ts): A lazy worker_threads pool sized to min(4, max(1, availableParallelism() - 1)) keeps heavy JSON and zstd parsing off the Fastify event loop. When running .ts sources under tsx in development, workers don’t inherit the parent ESM loader by default, so the pool injects execArgv: ['--import', 'tsx']; if a restricted packaged environment blocks worker startup for 10 seconds before any task succeeds, the pool warns once and falls back to inline parsing (degraded = true).
  3. Post-sync deferred chokidar v4 watcher (server/src/watcher/index.ts): Launching chokidar concurrently with initial sync caused heavy readdir/stat contention on directories like WorkBuddy’s home (~70,000 plugin files). createServer now defers startWatcher() until syncManager completes its initial pass. Because chokidar v4 removed glob support, normalizeWatchTarget() trims wildcard patterns to their static parent directory, ignores node_modules/.git/.cache, debounces bursts over 1.8 seconds, and maps changed files back to a single source via longest-prefix matching.

Parsing 32 AI Coding Harnesses: Shared Substrates, Multi-Frame zstd, and WSL Rules

Expanding from five local chat formats to 32 agent harnesses—Claude Code, Cursor, Codex, Gemini CLI, OpenCode, Goose, Cline, Roo Code, Kilo Code, Continue, Claude Desktop, Copilot / Copilot CLI, Trae, Factory Droid, Antigravity, Hermes, Kimi, Qwen, MiniMax, DeepSeek Harness, Pi, omp, Raft, Cindy, ZCode, Alma, CodeBuddy, WorkBuddy, Qoder / Qoder CN, and Grok Build—stayed maintainable by consolidating shared storage topologies into three reusable substrates: a Cline-lineage VS Code task substrate (adapters/vscode-tasks.ts), a Pi JSONL session substrate (adapters/pi-sessions.ts for Pi, omp, Raft, and Cindy), and a dual-schema OpenCode SQLite substrate (adapters/opencode.ts, merging 1.x tables with 2.x session_v2 and reused by ZCode).

Two low-level systems pitfalls are especially worth calling out for anyone parsing local agent logs:

Fixing Node.js zlib.zstdDecompressSync Truncation on Multi-Frame .jsonl.zst Logs

DeepSeek Harness and newer Codex .jsonl.zst rollouts append one standalone zstd frame per flush into a single concatenated file. Node’s built-in zlib.zstdDecompressSync() decodes only the first frame and silently drops every subsequent turn; conversely, splitting on raw 28 B5 2F FD magic-byte scans corrupts frames whenever compressed payload bytes match the 4-byte sequence.

server/src/parser/zstd.ts walks the RFC 8878 §3.1.1 frame header (Frame_Header_Descriptor, window descriptor, dictionary ID, and variable-length Frame_Content_Size), steps across 3-byte little-endian block headers (Last_Block + Block_Type + 21-bit Block_Size) and optional 4-byte checksums to slice exact frames, and falls back to bounded magic-offset probing only when the final frame is mid-write—recovering every completed turn cleanly.

Reading \\wsl.localhost on Windows: UTF-16LE Output and Never Waking Stopped WSL VMs

Agents running inside WSL store histories under \\wsl.localhost\<distro>\home\<user>, where two Windows quirks bite naive scanners: redirected wsl.exe stdout is encoded as UTF-16LE rather than UTF-8, and statting or watching a UNC path inside a stopped distribution cold-boots the Linux VM in the background.

parser/wsl.ts decodes UTF-16LE output, runs wsl.exe -l --running -q to inspect only currently running distributions (caching resolved homes for 5 minutes), and pairs with isWslUncPath() checks in watcher/index.ts so chokidar never attaches to \\wsl.localhost or \\wsl$.

Engineering Postmortem: Three Remaining Concurrency and UX Boundaries

Reviewing the v0.7 codebase against multi-thousand-session workloads confirms solid storage and isolation boundaries while surfacing three concrete follow-ups:

  1. Event-loop offloading vs. multi-worker batch saturation: ParserPool provisions up to 4 workers, yet importAll() still awaits importConversationItem(...) serially, driving only 1 worker slot during batch syncs. Adding a bounded prefetch window (concurrency = parsePool.size) ahead of serialized main-thread SQLite writes—with per-source worker affinity so four workers do not each cache a separate sql.js copy of a 100 MB opencode.db—will unlock full multi-core throughput.
  2. Progressive UI invalidation during scanning: useSyncStatus currently invalidates ['conversations'] on ${imported}:${replaced}:${skipped}:${errors}, which stays 0:0:0:0 while 32 sources commit pending rows during scanning. Folding progress.total into that signature will surface index-first rows as each adapter scan finishes.
  3. Empty-state handling when a pending row parses to < 2 messages: A 1-line aborted session briefly appears as pending after scan(); clicking it before background pruning deletes the placeholder (parsed.messages.length < 2, returning 'skipped') currently causes the detail route’s re-query to return 404. Handling 'skipped' explicitly in the route and client view will close that gap cleanly.

Evolving from whole-file WASM exports to WAL-backed node:sqlite, index-first rows, and lineage-shared parsers reinforced one principle for local-first developer tools: responsiveness comes from eliminating full-database rewrites, decoupling first-paint visibility from tail parse latency, and respecting neighboring processes’ locks and VM power states. Browse the full module map on the ChatCrystal project page or inspect sqlite-compat.ts and transcript-cache.ts in the repository.

Share:

From writing to code

Explore ChatCrystal, KeelDesk, n8n workflows and daily learning notes on my GitHub. Questions about the implementation are welcome.