Synchronous SQLite reads block OpenCode for 4–5 seconds at a time

[null-axiom]

What happened?

On large sessions, two Magic Context query patterns block OpenCode's shared backend:

1. Message-ID parts reads use the session index, scanning the session's parts instead of looking up the requested messages.
2. Compaction-marker discovery scans all session parts, then scans messages again for each marker.

Both were directly attributed through slow-query callsites and exact SQL fingerprints. The first problem has a standalone in-memory reproduction below; the second requires a different lookup strategy. Local corrections passed correctness checks and were followed by improved responsiveness.

Expected: reading parts for a bounded set of messages should use targeted message lookups, and marker cleanup should not repeatedly inspect unrelated session data.

Actual: individual synchronous reads take seconds and repeat during reconciliation, preventing the shared backend from servicing other work.

Environment

• @cortexkit/opencode-magic-context 0.42.0, verified from the installed package and matching bundle callsites.
• OpenCode Desktop 1.18.30, custom branch with bounded performance diagnostics.
• macOS; installed Electron 42.3.3, Node 24.15.0, SQLite 3.51.3.
• Large, long-lived sessions; one affected session contained approximately 102,000 parts.
• The custom branch does not modify the relevant upstream schema, migrations, or generated schema. The live indexes match upstream v1.18.30.

Impact and triggers

Repeated synchronous reads delay prompt progress, history loading, and cancellation. During one incident, history requests waited 6.5–17.3 seconds for response headers, although the backend handlers reported only 2–21 ms of work once entered.

Captured operation	Observed blocking
FTS reconciliation parts pages, September 12, 20:39–20:48 EDT	87 calls totaling 374 seconds, up to 4.47 seconds per call
Same query after restart, 20:55–21:04 EDT	49 calls totaling 206 seconds, approximately 4.4 seconds per call
Tail reader, 203–204 requested message IDs	4.20–4.21 seconds per call
Compaction-marker parts scan	5.45 seconds

The totals sum separate calls, not uninterrupted freezes. Diagnostics record statements taking at least 50 ms, capped at 10 records/minute and 100/process; these are partial observations.

Undo followed by resend can trigger reconciliation through message.removed → scheduleClearAndReindex. Ordinary message transformation can also schedule reconciliation when a session is not marked reconciled. Blocking recurred after restart without a recorded Undo. Scrolling is not an established trigger—it needs the backend while that backend is blocked.

1. Message-ID reads choose the wrong access path

Query shape from readRawSessionMessagePageFromDb, used by reconcileSessionIndex in message-index-async.ts (the comment represents generated placeholders; executable reproduction follows):

SELECT message_id, data, time_updated
FROM part
WHERE session_id = ?
  AND likelihood(message_id IN (/* 100 bound parameters */), 0.000001)
ORDER BY message_id ASC, time_created ASC, id ASC

The live database has the stock indexes below and no sqlite_stat1 table. Reproduction does not require skewed ANALYZE statistics or a custom schema.

part_session_idx        (session_id)
part_message_id_id_idx  (message_id, id)

EXPLAIN QUERY PLAN on a read-only connection using the installed Electron/SQLite runtime:

SEARCH part USING INDEX part_session_idx (session_id=?)
USE TEMP B-TREE FOR ORDER BY

The result is bounded to message IDs, but the work traverses the session's parts for each page. Yielding between pages cannot prevent a single synchronous page read from blocking for seconds. The likelihood() hint alone does not prevent this plan.

Local correction

- WHERE session_id = ?
+ WHERE +session_id = ?

SQLite's unary + disqualifies this condition for index selection while retaining the session filter. With the existing message-ID predicate, the corrected plan uses:

SEARCH part USING INDEX part_message_id_id_idx (message_id=?)

The same correction was applied to all four verified instances of this query pattern:

Function	Location in the original 0.42.0 bundle
readRawSessionMessagesFromDb	dist/index-c9hamh36.js:10669–10673
readRawSessionMessagePageFromDb	dist/index-c9hamh36.js:10720–10724
readRawSessionTailFromDb	dist/index-c9hamh36.js:10810
normalizeOpenCodeRows	dist/index.js:13404–13408

In-memory checks on the installed runtime confirmed identical ordered results and exclusion of inconsistent cross-session rows. Unary + also removes column affinity, so this validation covers the actual text session-ID and bound-parameter types. No indexes or statistics were changed, and the patch does not hardcode an index name.

Standalone reproduction

Save the following as repro.mjs and run with a Node runtime supporting node:sqlite. It was verified with the Electron/Node/SQLite versions above using ELECTRON_RUN_AS_NODE=1 /Applications/OpenCode.app/Contents/MacOS/OpenCode repro.mjs.

This uses only an in-memory database with the relevant stock table/index layout. It reproduces the query-plan difference and result equivalence, not the multi-second latency of a large on-disk session. Do not run ANALYZE for this reproduction; the affected database has no statistics.

import { DatabaseSync } from 'node:sqlite';
import assert from 'node:assert/strict';

const db = new DatabaseSync(':memory:');
db.exec(`CREATE TABLE part (id TEXT PRIMARY KEY, message_id TEXT NOT NULL,
  session_id TEXT NOT NULL, time_created INTEGER NOT NULL,
  time_updated INTEGER NOT NULL, data TEXT NOT NULL);
CREATE INDEX part_session_idx ON part(session_id);
CREATE INDEX part_message_id_id_idx ON part(message_id, id);`);

const insert = db.prepare('INSERT INTO part VALUES (?, ?, ?, ?, ?, ?)');
for (let i = 0; i < 300; i++) {
  for (let j = 0; j < 4; j++) {
    insert.run(`p${i}-${j}`, `m${i}`, 's', i, i, '{}');
  }
}
insert.run('cross-session', 'm0', 'other', 0, 0, '{}');

const ids = Array.from({ length: 100 }, (_, i) => `m${i}`);
const query = (sessionColumn) => `SELECT message_id, data, time_updated FROM part
  WHERE ${sessionColumn} = ?
    AND likelihood(message_id IN (${ids.map(() => '?').join(', ')}), 0.000001)
  ORDER BY message_id ASC, time_created ASC, id ASC`;

for (const column of ['session_id', '+session_id']) {
  console.log(column, db.prepare('EXPLAIN QUERY PLAN ' + query(column))
    .all('s', ...ids).map(row => row.detail));
}
const original = db.prepare(query('session_id')).all('s', ...ids);
const corrected = db.prepare(query('+session_id')).all('s', ...ids);
assert.equal(original.length, 400);
assert.deepEqual(corrected, original);
console.log('Identical ordered results; cross-session row excluded.');
db.close();

Verified output selects part_session_idx for the original and part_message_id_id_idx for the corrected query; both assertions pass.

2. Compaction-marker discovery performs broad, repeated scans

listSessionCompactionMarkers, called by reconcileForkOrphanedCompactionMarkers during the first degraded rebuild pass in prepareCompartmentInjection, starts with:

SELECT id, message_id
FROM part
WHERE session_id = ?
  AND COALESCE(json_extract(data, '$.type'), '') = 'compaction'

This took 5.45 seconds. For each returned marker, it then scans the session's messages for a completed Magic Context summary whose JSON parentID matches that marker's message. An associated summary scan took 803 ms.

Unlike the first query family, this discovery query has no known message-ID set. Disqualifying its session index would cause a full-table scan and make it worse.

Local correction

Reverse the lookup order:

1. Scan session messages once for summaries matching the existing predicates: summary = 1, finish = 'stop', and providerID = 'magic-context'.
2. Group summary IDs by their parent boundary message ID.
3. Fetch compaction parts only for those boundary IDs, in chunks of at most 800, using the targeted message-index pattern above.

The sole caller, reconcileForkOrphanedCompactionMarkers, already ignores markers without completed Magic Context summaries. Omitting those candidates therefore preserves its cleanup decisions. Session filters, protected ownership checks, and marker ordering are retained; no cache or raw-text JSON prefilter was added.

Tests ran the actual old/new functions and their caller against in-memory fixtures. Actionable markers and removal decisions matched for native-only/orphan markers, multiple summaries and parts, cross-session data, 900 boundaries, stale ownership, missing boundary messages, and removal failures. One session-message scan remains, but the full-session part scan and per-marker message scans are removed.

Results after the local patches

After restarting on September 13, the checked interval from 11:22–11:28:49 EDT, including an Undo/send, showed:

• Nine upward-history loads completed in 51–86 ms.
• The repeated 4–5-second query stalls were not recorded; the diagnostic budget was not exhausted.
• Responsiveness was noticeably better in normal use.

This is not a controlled benchmark or proof of complete resolution: the corrections were combined, OpenCode UI changes were also rebuilt, and not every corrected path is proven to have executed in this short interval. Shorter blocking remained: a separate Magic Context query took 1.35 seconds, three later reads took 113–144 ms, and the post-Undo/send event-loop interval peaked at 1.89 seconds, not fully explained by those SQL records.

Requested fix and coverage

• Make all four message-ID readers reliably use targeted lookups on supported stock schemas; test absent and adversarial planner statistics.
• Avoid the broad part scan and repeated summary scans in marker discovery while preserving cleanup decisions.
• Validate index correctness and backend responsiveness during sustained reconciliation, both after Undo/resend and during ordinary post-restart activity.

The local edits are temporary installed-bundle patches. Validation did not mutate live database records, indexes, or statistics. This report is scoped to the two verified query problems; the patches do not change scheduling.

Exact SQL fingerprints and original bundle callsites

These SHA-256 fingerprints were reproduced from the exact original SQL templates, including whitespace, and matched captured Magic Context callsites:

Query	Original callsite	SQL SHA-256
Reconciliation parts page, 100 IDs	index-c9hamh36.js:10724:66 → index.js:21983:63	0fa41171c48d92cee4981cf2ad5918e5a964a0f6e1140308af8b3edd911011e2
Tail parts, 203 IDs	index-c9hamh36.js:10810	609ca9ade103ab670d70d66a8cf3e7c78df3ac7fbb74ecd193f40941a872bad3
Compaction-marker parts	index-c9hamh36.js:31021	c7bd051b0313f2bc346b7806c3fe847eb964336529ac4d6f539dd30fe8eb5cc0
Per-marker summary messages	index-c9hamh36.js:31033	535e81bb87676afd7b08a425338920aff55ffe01f08c81df88f9703192e6df44

[coleleavitt]

Fixed by 72cb0371 and included in the v0.42.3 line. The bounded message-ID readers now retain the session correctness predicate while disqualifying it from index selection (+session_id), and compaction-marker discovery now performs one completed-summary scan followed by targeted boundary-part pages of at most 800 IDs instead of scanning all parts and then messages per marker.

packages/plugin/src/hooks/magic-context/opencode-query-plan.test.ts proves part_message_id_id_idx selection with absent and adversarial planner statistics and checks cross-session equivalence. degraded-reanchor.test.ts differentially preserves actionable marker/removal behavior across 900 boundaries and failure cases. A focused local run passed 17/17 with an extended timeout; the deliberately slow legacy side of the 900-boundary differential exceeds Bun's default five-second test timeout.

This addresses both SQL fingerprints in the report. I recommend closing; please reopen with a post-v0.42.3 fingerprint if either 4–5 second statement remains.

[coleleavitt]

Complete open-item audit index

Baseline: fork merge 2d6a8af1; upstream 5bf26e65 (v0.42.3). Live refresh confirms the current open set is exactly these 12 issues and five PRs.

Item	Final disposition	Per-item audit
#440	close fixed v0.42.3	comment
#439	close fixed v0.42.2	comment
#431	close fixed v0.42.x	comment
#428	close core bug fixed v0.42.0; extra knobs out of scope	comment
#405	keep open; feature absent and PR #406 blocked	comment
#360	keep open; launcher-aware installer absent	comment
#358	keep open; policy packs absent	comment
#353	keep open for public API; internal pi-web failures fixed	comment
#335	keep open; #340 unmerged/superseded	comment
#332	keep open upstream/platform-blocked pending fixed-Bun retest	comment
#310	keep open upstream-SDK-blocked	comment
#260	keep open; V2 scaffold only	comment
PR #434	keep open/request changes	comment
PR #414	keep open/request changes	comment
PR #406	keep open/request changes and rebase	comment
PR #340	close unmerged as superseded; keep #335 open	comment
PR #339	close/replace stale branch; retain fresh production-audit objective	comment

Verification: 17/17 items audited; 17/17 new per-item comments posted; all 17 exact bodies fetched back successfully; zero missing items; zero failed URLs; zero running audit agents. This account lacks triage permission, so the recommended closures require a maintainer.

[magic-alfonso]

Confirmed at source, and thank you for the exact SQL fingerprints, the standalone reproduction and the validated +session_id fix — that is the whole diagnosis. Two things were wrong: on the stock schema SQLite planned our bounded message-ID part reads on part_session_idx (a scan of the whole session's parts per page), and compaction-marker discovery scanned every session part and then messages once per marker.

Fixed in v0.42.3 (published): every bounded part lookup now disqualifies the session-only index (all four readers you named plus three sibling shapes and two delete statements), and marker discovery scans summaries once, then fetches boundary parts in pages of at most 800. The plans are pinned by EXPLAIN QUERY PLAN tests on the stock schema with absent and adversarial statistics, on both SQLite runtimes we ship on; old-vs-new marker reconciliation is proven identical across 900 boundaries, cross-session rows, stale ownership and removal failures. We also audited every statement Magic Context issues against opencode.db (41 reads, 5 writes) — the remaining full-session scans are the one-time compaction-off safety preflight and single ordered session scans, which is what your residual 1.35 s read most likely was; if it recurs after the fix, the fingerprint would pin it.

Closing as shipped; please reopen with a post-0.42.3 fingerprint if either statement is still slow.