Chunk getDeltaLog IN-clause to avoid SQLite parameter overflow

Context

Reproduced 2026-04-28 during a runSandman cycle: after FileSystemIngestor.syncWorkspaceToGraph upserted 249,748 nodes and 249,748 CONTAINS edges, the GraphLog accumulated a delta-log entry per write. The next call to Database.syncCache → SQLite.getDeltaLog collected all pending invalid edge IDs into a single WHERE id IN (?,?,?,...) placeholder list and exceeded SQLite's parameter limit, raising SQLITE_ERROR: too many SQL variables.

The crash surfaced in runSandman.mjs's finally block (GraphService.decayGlobalTopology), but the same code path is on the hot route of every operation that triggers syncCache — including A2A add_message calls, which started failing with the same error mid-session. The graph DB is effectively wedged for any subsequent operation that crosses syncCache until the delta log is drained or the chunking fix lands.

The Problem

SQLite.mjs:307 builds a single dynamic IN-clause:

let edgeIds = Array.from(invalidEdgesMap.keys());
let placeholders = edgeIds.map(() => '?').join(',');
let edgesQuery = this.db.prepare(`SELECT id, source, target FROM Edges WHERE id IN (${placeholders})`);
let edgesData = edgesQuery.all(...edgeIds);

SQLite's SQLITE_MAX_VARIABLE_NUMBER defaults to 32,766 in modern builds (3.32+, 2020-onwards) and 999 in older builds. better-sqlite3 ships with the modern default, but the FileSystemIngestor's full-sync delta volume (~250k entries) blows past even that ceiling.

This is a known SQLite-IN-clause anti-pattern with a canonical fix: chunk the IN list into batches under the parameter ceiling.

Once the crash fires, getDeltaLog cannot complete. syncCache cannot advance its watermark. Every subsequent caller through Database.getAdjacentNodes → GraphService.upsertNode retries the same doomed query. The graph DB enters a wedged state where reads are still possible but any path through syncCache hard-fails.

The Architectural Reality

Crash trace from a real run:

SqliteError: too many SQL variables
    at Database.prepare (.../node_modules/better-sqlite3/lib/methods/wrappers.js:5:21)
    at SQLite.getDeltaLog (.../ai/graph/storage/SQLite.mjs:307:38)
    at Database.syncCache (.../ai/graph/Database.mjs:89:40)
    at Database.getAdjacentNodes (.../ai/graph/Database.mjs:281:12)
    at GraphService.upsertNode (.../ai/mcp/server/memory-core/services/GraphService.mjs:156:17)
    at GraphService.decayGlobalTopology (.../ai/mcp/server/memory-core/services/GraphService.mjs:507:14)

Files / boundaries:

• ai/graph/storage/SQLite.mjs — owns delta-log SQL (the right substrate for chunking)
• ai/graph/Database.mjs — syncCache consumer (no change needed if storage layer chunks correctly)
• ai/mcp/server/memory-core/services/GraphService.mjs — downstream consumers (no change needed)

The chunking belongs strictly inside the storage layer. No service-boundary crossing.

The Fix

In ai/graph/storage/SQLite.mjs:304-312, chunk the IN-clause into batches of 999 placeholders (safe across all SQLite versions; conservative choice over the 32,766 ceiling):

if (invalidEdgesMap.size > 0) {
    const edgeIds = Array.from(invalidEdgesMap.keys());
    const CHUNK_SIZE = 999;

    for (let i = 0; i < edgeIds.length; i += CHUNK_SIZE) {
        const chunk = edgeIds.slice(i, i + CHUNK_SIZE);
        const placeholders = chunk.map(() => '?').join(',');
        const edgesQuery = this.db.prepare(`SELECT id, source, target FROM Edges WHERE id IN (${placeholders})`);
        const edgesData = edgesQuery.all(...chunk);
        for (const row of edgesData) {
            invalidEdgesMap.set(row.id, { id: row.id, source: row.source, target: row.target });
        }
    }
}

Audit ai/graph/storage/SQLite.mjs for any other dynamic IN-clauses that could exhibit the same pattern (e.g., loadNodeVicinitySync at line 329 — verify it's bounded). If similar patterns exist, apply the same chunking.

Acceptance Criteria

• SQLite.getDeltaLog chunks the IN-clause into batches of ≤999 placeholders
• Audit completed: any other dynamic IN-clause in ai/graph/storage/SQLite.mjs that could grow unboundedly is either chunked or its bound is documented
• Empirical reproducer: integration test that simulates a delta log with >32,766 entries and verifies syncCache completes without SQLITE_ERROR
• Existing test test/playwright/unit/ai/mcp/server/memory-core/services/GraphService.spec.mjs extended (or new spec) covers the chunked-IN path
• Empirical post-fix verification: full runSandman cycle with FileSystemIngestor's 249k+ workspace sync completes without decayGlobalTopology crash
• Commit subject ends with (#TICKET_ID) per AGENTS.md §3 Gate 1; type=fix, scope=ai

Out of Scope

• Reducing the GraphLog write volume (e.g., batching FileSystemIngestor writes into delta-log groups). Real architectural concern but separate scope — the storage layer should not crash regardless of upstream write velocity.
• Truncating or rotating the GraphLog table. Out of scope for this fix; if delta-log retention becomes a separate concern, file as a sibling ticket.
• Migrating the IN-clause to a temp table or VALUES (?)-CTE join. Both are valid alternatives but add schema complexity for no measurable gain at this scale; chunked IN is the canonical fix.

Avoided Traps

• Hardcoding the SQLite version's actual ceiling (32,766). Rejected. 999 is the historical-conservative SQLite default and works on every version. The ceiling could change across better-sqlite3 upgrades; pinning to 999 is forward-compatible.
• Single-prepare with binding-array reuse. Rejected. db.prepare doesn't accept variable-length placeholder lists across calls; the placeholder count is part of the SQL text. Each chunk needs its own prepare call (acceptable cost — SQLite prepare cache makes this cheap).
• Increasing SQLITE_MAX_VARIABLE_NUMBER via build flag. Rejected. Requires recompiling better-sqlite3 from source, not portable, and just postpones the problem at higher scales.
• Catching the error and logging-and-continuing. Rejected. Silent skip would corrupt the cache state — the delta log entries would never get consumed, leading to permanent staleness. Chunk to actually do the work, not paper over it.

Related

• Triggered by: #10143 substrate — high-volume FileSystemIngestor + MemorySessionIngestor write paths produce delta-log saturation
• Surfaces silent-partial-ingestion bug in: sibling ticket on DreamService graphDigested gate (which masks the per-memory failures this overflow causes)
• Adjacent: any operation on the graph DB while a large delta log is pending (e.g., A2A add_message failing with the same error mid-session)

Origin Session ID: 4bb6859b-860f-440d-9055-320e20b0ee22

Retrieval Hint: SQLite IN-clause overflow getDeltaLog GraphLog saturation post-FileSystemIngestor too many SQL variables chunked-prepare