AI Conversation Formats: JSONL Events vs SQLite Snapshots

A shared extension does not establish shared message semantics, and a shared SQLite container does not establish identical tables or keys. ChatCrystal normalizes output at its adapter boundary, with retention limited by each parser. Output boundaries examine those choices. This article returns to storage and lookup: reading JSONL or opening state.vscdb is only the beginning.

Five pinned entry points are not permanent storage specifications

This table describes the pinned implementation, rather than every current third-party version or an independently established design motive.

Source	Shape read by this adapter	Lookup and interpretation work
Claude Code	Project JSONL with uuid-bearing user/assistant records and string or block content	Message types, parentUuid, and selective block extraction
Codex	Selected session_meta, event_msg, and response_item branches in rollout JSONL	Events, local duplicates, and tool flags rather than complete thread replay
Cursor	workspace ItemTable composer.composerData; global cursorDiskKV bubbleId keys	Separate session index and message storage, plus orphan scanning
Trae	ItemTable memento/icube-ai-agent-storage; data.list matched by sessionId	Nested content, turnIndex ordering, and increasing synthetic timestamps
Copilot	Literal first snapshot line in .jsonl, or a legacy .json file	kind=0/v wrappers versus legacy shape; later lines are not replayed here

parser/index.ts statically registers these five sources. Default project or sessions paths and platform branches for Cursor, Trae, and Copilot live in their respective adapters; configurable overrides also belong in lookup acceptance. One Windows path example cannot represent every platform or deployment.

JSONL frames records without defining their model

Line-delimited JSON supports append-oriented and incremental processing, with parsing work still required. Crashes, unflushed buffers, and incomplete trailing records need specific durability evidence. An extension cannot guarantee all earlier lines are safe.

Claude adapter selects valid uuid-bearing user/assistant branches and extracts chosen blocks. Codex adapter selects event branches, removes local duplicates, and marks tool calls on the latest assistant. It does not restore a complete causal thread from all events. Titles may also come from session_index.jsonl, rather than message content alone.

Copilot adapter reads the literal first JSONL line, checks kind, and selects v. It does not treat the last line as a latest full snapshot. Legacy JSON uses whole-file parsing. Establish later patches’ effects from upstream versions and samples; unconsumed information is not thereby permanently worthless.

SQLite still requires source-specific message lookup

Cursor adapter gets composer heads from the workspace index, reads bubbles from global cursorDiskKV, and also attempts to scan orphan composers. The original’s TableItemTable is not the actual table name here. Trae adapter reads nested data.list inside ItemTable, so the same composer/bubble query cannot serve both.

SQLite supports SQL and transactions, but copying a live main file does not automatically capture every latest message. ChatCrystal loads a main-file byte snapshot without merging native WAL in this path. Obtaining a consistent sample and interpreting its nested JSON are separate tasks.

Inventory associations and omissions in two memory databases

Save lesson.mjs and run Node 24+, without additional npm packages. It creates two Node SQLite memory databases with Cursor-style tables and keys. It inventories synthetic data only: no state.vscdb, real adapter, complete conversation restoration, concurrent writer, or native WAL.

import assert from 'node:assert/strict';
import { DatabaseSync } from 'node:sqlite';
const workspace = new DatabaseSync(':memory:');
const global = new DatabaseSync(':memory:');
try {
  workspace.exec('CREATE TABLE ItemTable(key TEXT PRIMARY KEY, value TEXT)');
  global.exec('CREATE TABLE cursorDiskKV(key TEXT PRIMARY KEY, value TEXT)');
  workspace
    .prepare('INSERT INTO ItemTable VALUES (?, ?)')
    .run(
      'composer.composerData',
      JSON.stringify({ allComposers: [{ composerId: 'known', name: 'fixture session' }] })
    );
  const insert = global.prepare('INSERT INTO cursorDiskKV VALUES (?, ?)');
  insert.run('bubbleId:known:u1', JSON.stringify({ type: 1, text: 'question', _v: 3 }));
  insert.run('bubbleId:known:a1', JSON.stringify({ type: 2, text: 'answer', _v: 3 }));
  insert.run('bubbleId:orphan:a2', JSON.stringify({ type: 2, text: 'unindexed answer', _v: 99 }));
  insert.run('bubbleId:known:bad', 'broken JSON');
  const row = workspace
    .prepare('SELECT value FROM ItemTable WHERE key = ?')
    .get('composer.composerData');
  const heads = new Map(JSON.parse(row.value).allComposers.map((head) => [head.composerId, head]));
  const groups = new Map();
  const errors = [];
  const unknownVersions = [];
  for (const row of global
    .prepare("SELECT key, value FROM cursorDiskKV WHERE key LIKE 'bubbleId:%'")
    .all()) {
    const [, id, messageId] = row.key.split(':');
    let bubble;
    try {
      bubble = JSON.parse(row.value);
    } catch {
      errors.push(row.key);
      continue;
    }
    if ((bubble._v ?? 0) > 3) unknownVersions.push(row.key);
    const group = groups.get(id) ?? { id, head: heads.get(id) ?? null, messages: [] };
    group.messages.push({ messageId, bubble });
    groups.set(id, group);
  }
  assert.equal(groups.get('known').messages.length, 2);
  assert.equal(groups.get('known').head.name, 'fixture session');
  assert.ok(groups.has('orphan'));
  assert.equal(groups.get('orphan').head, null);
  assert.equal(groups.get('orphan').messages.length, 1);
  assert.deepEqual(errors, ['bubbleId:known:bad']);
  assert.deepEqual(unknownVersions, ['bubbleId:orphan:a2']);
  assert.throws(() => global.prepare('SELECT value FROM ItemTable').all(), /no such table/);
  console.log('two-store inventory, orphan data, and unknown-version evidence pass');
} finally {
  workspace.close();
  global.close();
}

known has an index entry and two valid messages; orphan has only global messages. Invalid JSON and unknown _v=99 are recorded separately. A version warning does not establish compatibility. Querying the message database as if it contained ItemTable really fails with a missing-table error.

Discard ids without heads before grouping and the orphan-presence assertion fails. Omit unknown-version recording and its list assertion fails. Restore each to pass. Real Cursor orphan scanning has its own sampling, cache, and limits; this inventory is not its full implementation.

Normalization needs version and loss records

SourceAdapter separates detect, scan, and parse entry shapes, but scan may read content or query a database. The interface is not a runtime validator for external data. A new source also involves shared source unions, configuration, remote payloads, and watcher/UI paths; registration alone cannot complete integration.

Normalized output simplifies summaries and retrieval without promising every tool, thought, parent reference, attachment, or original timestamp is retained. Keep the source, input copy, extraction rules, and omission reasons for each supported version, then inspect output and imported state. Continue with conversation-management options for migration choices.