Table of contents Show Hide
- Why sql.js Full-File Exports Break Down Under Multi-Source Background Sync
- Migrating sql.js to Node.js 24 node:sqlite (WAL Mode) Without Rewriting Queries
- Lazy-Loading Large JSONL & SQLite Histories: Index-First Rows and Gzip Cache
- Phase 1: Insert pending Index Rows on Scan, Materialize on Demand
- Phase 2: Parser-Versioned Atomic Gzip Transcript Cache
- Node.js worker_threads Pool (tsx Support) and Deferred chokidar v4 Watching
- Parsing 32 AI Coding Harnesses: Shared Substrates, Multi-Frame zstd, and WSL Rules
- Fixing Node.js zlib.zstdDecompressSync Truncation on Multi-Frame .jsonl.zst Logs
- Reading \\wsl.localhost on Windows: UTF-16LE Output and Never Waking Stopped WSL VMs
- Engineering Postmortem: Three Remaining Concurrency and UX Boundaries
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:
- Whole-database write amplification: Every
saveDatabase()call and 30-second autosave invokeddb.export(), allocating a full-databaseUint8Arraysnapshot and rewritingchatcrystal.dbfrom 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.localhostreads 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
chokidardirectory 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, andPRAGMA foreign_keys = ON. Every committed transaction persists directly to the WAL file, reducingsaveDatabase()to a best-effortPRAGMA wal_checkpoint(TRUNCATE). - Lexical multi-statement splitting with
CREATE TRIGGERawareness:sql.jsaccepts semicolon-joined SQL batches inexec()andrun(), whereasDatabaseSync.prepare()compiles a single statement.splitSqlStatements()scans quotes, bracketed identifiers, and SQL comments while keeping internal semicolons intact insideCREATE TRIGGER … BEGIN …; END;bodies. - Named parameter prefixes and empty-result parity:
sql.jspermits:,$, and@prefixes on parameter object keys ({ ':id': 'abc' }) and returns[]when a query yields zero rows;toNamedParams()andexecPrepared()preserve both behaviors whileDatabaseSync.serialize()backsexport()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:
- Single-flight
SyncEngine(server/src/services/sync.ts): Startup sync (2.5 seconds after boot), manualPOST /api/sync/starttriggers, and watcher events all enter one state machine (idle,syncing,cancelled,error). Overlapping triggers merge intopendingSourcesfor an immediate follow-up pass, whilecancel()clears pending work and signals anAbortSignalbetween files. - Gracefully degrading
ParserPool(server/src/services/parse-pool.ts): A lazyworker_threadspool sized tomin(4, max(1, availableParallelism() - 1))keeps heavy JSON and zstd parsing off the Fastify event loop. When running.tssources undertsxin development, workers don’t inherit the parent ESM loader by default, so the pool injectsexecArgv: ['--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
chokidarv4 watcher (server/src/watcher/index.ts): Launchingchokidarconcurrently with initial sync caused heavyreaddir/statcontention on directories like WorkBuddy’s home (~70,000 plugin files).createServernow defersstartWatcher()untilsyncManagercompletes its initial pass. Becausechokidarv4 removed glob support,normalizeWatchTarget()trims wildcard patterns to their static parent directory, ignoresnode_modules/.git/.cache, debounces bursts over 1.8 seconds, and maps changed files back to a singlesourcevia 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:
- Event-loop offloading vs. multi-worker batch saturation:
ParserPoolprovisions up to 4 workers, yetimportAll()still awaitsimportConversationItem(...)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-sourceworker affinity so four workers do not each cache a separatesql.jscopy of a 100 MBopencode.db—will unlock full multi-core throughput. - Progressive UI invalidation during
scanning:useSyncStatuscurrently invalidates['conversations']on${imported}:${replaced}:${skipped}:${errors}, which stays0:0:0:0while 32 sources commitpendingrows duringscanning. Foldingprogress.totalinto that signature will surface index-first rows as each adapter scan finishes. - Empty-state handling when a
pendingrow parses to< 2messages: A 1-line aborted session briefly appears aspendingafterscan(); clicking it before background pruning deletes the placeholder (parsed.messages.length < 2, returning'skipped') currently causes the detail route’s re-query to return404. 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.