---
title: "Migrate sql.js to Node 24 node:sqlite: WAL & Worker Sync"
description: "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."
image: "https://zengliangyi.com/og/blog/en/migrate-sqljs-to-node-sqlite-wal-sync.png"
---

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`](https://github.com/ZengLiangYi/ChatCrystal/tree/842e44a)), [ChatCrystal](/en/projects/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](/en/blog/sqljs-wasm-persistence/) and [bulk chat import concurrency benchmarks](/en/blog/bulk-import-performance-plan/) 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:

- 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.

- 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.

- 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](https://nodejs.org/docs/latest-v24.x/api/sqlite.html) (bundled natively in Electron 44) unlocks file-backed [SQLite Write-Ahead Logging (WAL)](https://www.sqlite.org/wal.html) 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`](https://github.com/ZengLiangYi/ChatCrystal/blob/842e44a/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.



```ts
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`](https://github.com/ZengLiangYi/ChatCrystal/blob/842e44a/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`](https://github.com/ZengLiangYi/ChatCrystal/blob/842e44a/server/src/routes/conversations.ts) (or running summarization) triggers `materializeConversationById()` inline:



```ts
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`](https://github.com/ZengLiangYi/ChatCrystal/blob/842e44a/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](/en/blog/source-adapter-internals/)):



```ts
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:

- 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.

- 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`).

- 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](/en/blog/ai-conversation-data-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`](https://github.com/ZengLiangYi/ChatCrystal/blob/842e44a/server/src/parser/zstd.ts) walks the [RFC 8878 §3.1.1 frame header](https://datatracker.ietf.org/doc/html/rfc8878#section-3.1.1) (`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:

- 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.

- 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.

- 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](/en/projects/chatcrystal/) or inspect [`sqlite-compat.ts`](https://github.com/ZengLiangYi/ChatCrystal/blob/842e44a/server/src/db/sqlite-compat.ts) and [`transcript-cache.ts`](https://github.com/ZengLiangYi/ChatCrystal/blob/842e44a/server/src/services/transcript-cache.ts) in the repository.

Share:

##  Related Posts

[##  Bulk Importing AI Chats: Concurrency Controls and Benchmarks

Profile 1,000 chat imports across parsing, SQLite writes, WASM serialization, and summarization to establish concurrency limits and latency baselines.          Leon Zeng       October 7, 2026      4 min read

#ChatCrystal

#SQLite

#Performance](/en/blog/bulk-import-performance-plan/)    [##  Node.js Memory Budgets for Bulk Chat Imports & sql.js

Profile memory peaks in large chat imports with sql.js and task queues. Size batches from reproducible benchmarks instead of arbitrary fixed thresholds.          Leon Zeng       October 3, 2026      6 min read

#ChatCrystal

#Node.js

#Data parsing

#Performance](/en/blog/conversation-import-memory-budget/)    [##  AI Conversation Formats: JSONL Events vs SQLite Snapshots

Compare 5 AI adapter formats: separate JSONL events from SQLite keys, and inspect indexed, orphan, and unknown-version chat transcripts in memory stores.          Leon Zeng       October 7, 2026      4 min read

#ChatCrystal

#Node.js

#SQLite](/en/blog/ai-conversation-format-divergence/)

##  From writing to code

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

[Visit my GitHub](https://github.com/ZengLiangYi)

```json
{
  "@context": "https://schema.org",
  "@id": "https://zengliangyi.com/#website",
  "@type": "WebSite",
  "name": "ZengLiangYi Blog",
  "alternateName": [
    "Leon Zeng",
    "Zeng Liangyi",
    "ZengLiangYi",
    "zengliangyi.com"
  ],
  "url": "https://zengliangyi.com",
  "description": "Frontend engineering, AI coding and open-source practice",
  "inLanguage": "en",
  "author": {
    "@id": "https://zengliangyi.com/#person"
  }
}
{
  "@context": "https://schema.org",
  "@type": "BlogPosting",
  "headline": "Migrate sql.js to Node 24 node:sqlite: WAL & Worker Sync",
  "description": "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.",
  "url": "https://zengliangyi.com/en/blog/migrate-sqljs-to-node-sqlite-wal-sync/",
  "image": "https://zengliangyi.com/images/blog/migrate-sqljs-to-node-sqlite-wal-sync.svg",
  "datePublished": "2026-11-15T00:00:00.000Z",
  "dateModified": "2026-11-15T00:00:00.000Z",
  "inLanguage": "en",
  "keywords": [
    "ChatCrystal",
    "Node.js",
    "SQLite",
    "Performance"
  ],
  "articleSection": "ChatCrystal",
  "isPartOf": {
    "@id": "https://zengliangyi.com/#website"
  },
  "author": {
    "@id": "https://zengliangyi.com/#person",
    "@type": "Person",
    "name": "曾亮懿",
    "alternateName": [
      "Leon Zeng",
      "Zeng Liangyi",
      "ZengLiangYi",
      "夜雪闻竹"
    ],
    "jobTitle": "AI Native Developer",
    "knowsAbout": [
      "AI Native Development",
      "AI Agents",
      "Model Context Protocol",
      "TypeScript",
      "React",
      "Full-Stack Development",
      "Developer Tools"
    ],
    "url": "https://zengliangyi.com/en/about/",
    "email": "contact@zengliangyi.com",
    "image": "https://zengliangyi.com/branding/zeng-liangyi-avatar.png",
    "address": {
      "@type": "PostalAddress",
      "addressLocality": "深圳",
      "addressRegion": "广东",
      "addressCountry": "CN"
    },
    "sameAs": [
      "https://github.com/ZengLiangYi",
      "https://x.com/LeonZeng_",
      "https://bsky.app/profile/yizel9.bsky.social",
      "https://www.reddit.com/user/ExternalActive4238/",
      "https://juejin.cn/user/1669474445171547",
      "https://blog.csdn.net/2201_75708499",
      "https://www.linkedin.com/in/zeng-liangyi/",
      "https://gravatar.com/radangelb905e883e0"
    ]
  },
  "publisher": {
    "@type": "Organization",
    "name": "ZengLiangYi Blog",
    "url": "https://zengliangyi.com",
    "logo": {
      "@type": "ImageObject",
      "url": "https://zengliangyi.com/favicon-96x96.png"
    }
  },
  "mainEntityOfPage": {
    "@type": "WebPage",
    "@id": "https://zengliangyi.com/en/blog/migrate-sqljs-to-node-sqlite-wal-sync/"
  }
}
{
  "@context": "https://schema.org",
  "@type": "FAQPage",
  "name": "Migrate sql.js to Node 24 node:sqlite: WAL & Worker Sync",
  "mainEntity": [
    {
      "@type": "Question",
      "name": "Why use Node.js 24 node:sqlite for the primary database while keeping sql.js for external IDE databases in Electron?",
      "acceptedAnswer": {
        "@type": "Answer",
        "text": "Built-in node:sqlite (DatabaseSync) provides file-backed WAL durability and cheap checkpoints across CLI, Docker, and Electron 44 without better-sqlite3 native C++ addon rebuilds. External SQLite files (Cursor, OpenCode, Goose) are actively held open by running editors, so loading read-only byte snapshots into sql.js avoids SQLITE_BUSY and WAL lock contention."
      }
    },
    {
      "@type": "Question",
      "name": "Why does Node.js zlib.zstdDecompressSync truncate concatenated .jsonl.zst logs?",
      "acceptedAnswer": {
        "@type": "Answer",
        "text": "Several AI coding agents append one independent RFC 8878 zstd frame per flush into a single .jsonl.zst file. Node.js zlib.zstdDecompressSync decodes only the first frame and silently ignores trailing frames, so multi-frame logs must be split by walking frame and 3-byte block headers before decompression."
      }
    },
    {
      "@type": "Question",
      "name": "How can a Windows Node.js app read \\\\wsl.localhost files without waking stopped WSL distributions?",
      "acceptedAnswer": {
        "@type": "Answer",
        "text": "Statting or watching a \\\\wsl.localhost UNC path inside a stopped Linux distribution cold-boots the WSL virtual machine in the background. Query wsl.exe -l --running -q first (decoding redirected UTF-16LE stdout) to scan only running distributions, and never attach chokidar watchers to WSL UNC paths."
      }
    }
  ]
}
{
  "@context": "https://schema.org",
  "@type": "BreadcrumbList",
  "name": "Breadcrumb",
  "itemListElement": [
    {
      "@type": "ListItem",
      "position": 1,
      "name": "Home",
      "item": "https://zengliangyi.com/en/"
    },
    {
      "@type": "ListItem",
      "position": 2,
      "name": "Blog",
      "item": "https://zengliangyi.com/en/blog/"
    },
    {
      "@type": "ListItem",
      "position": 3,
      "name": "Migrate sql.js to Node 24 node:sqlite: WAL & Worker Sync",
      "item": "https://zengliangyi.com/en/blog/migrate-sqljs-to-node-sqlite-wal-sync/"
    }
  ]
}
```
