GraphLog grows unbounded: no compaction past consumer watermark

Context

Investigating the operator's flag that the graph at .neo-ai-data/sqlite is "way more worrying" than the chroma test-collection leak (#12143). Empirical read of the live production graph db (memory-core-graph.sqlite, 2026-06-02):

• DB size: 441 MB (112,951 pages × 4096; freelist=0 → not un-vacuumed dead space, it is live rows).
• Nodes: 74,101 rows (~35 MB) · Edges: 53,307 rows (~25 MB) — the actual Native Edge Graph, healthy.
• GraphLog: 7,328,097 rows (~365 MB) — 83% of the db.
• GraphLog composition by entity_type: edges 7,166,812 (98%) · nodes 160,573 · heartbeat_pulse 712.

The Problem

GraphLog is a change-data-capture (CDC) log: triggers append a (log_id, entity_id, entity_type) row on every Nodes/Edges INSERT/UPDATE/DELETE so remote workers can sync graph diffs incrementally. It is append-only and never compacted → it grows monotonically and unbounded (7.3M rows / 365 MB and counting; the latest GraphLog: NNNN in every heartbeat pulse is this autoincrement id).

The 98%-edges composition reflects heavy edge churn: only ~53K edges currently exist, but 7.17M edge-mutations have been logged (~134 logged mutations per surviving edge), consistent with Hebbian decay/reinforcement updating edge weights frequently. Each mutation is logged forever.

This is the graph-store analog of the chroma test-collection leak (#12143 / #12180), but more serious: it is the core Native Edge Graph store, it grows during normal operation (not just tests), and every one of the ~22 GB of daily .neo-ai-data/backups bundles carries the full 365 MB GraphLog — so GraphLog's unbounded growth inflates each (retention-swept) backup bundle too.

The Architectural Reality

• Producers — ai/graph/storage/SQLite.mjs:130,135 (+ the INSERT/UPDATE triggers): CREATE TRIGGER ... AFTER DELETE ON Nodes/Edges BEGIN INSERT INTO GraphLog(entity_id, entity_type) VALUES (OLD.id, 'nodes'/'edges'); END;
• Consumers — all strictly watermark-based, never full-replay:
  • ai/daemons/bridge/queries.mjs:110 — SELECT log_id, entity_id, entity_type FROM GraphLog WHERE log_id > ? ORDER BY log_id ASC (lastSyncId)
  • ai/graph/storage/SQLite.mjs:341 — same WHERE log_id > ? (sinceId)
  • ai/services/memory-core/WakeSubscriptionService.mjs:736 — same WHERE log_id > ?
  • Watermark reads: queries.mjs:22, SQLite.mjs:325/439, WakeSubscriptionService.mjs:106 (SELECT MAX(log_id)).
• No compaction exists. ai/scripts/maintenance/defragSQLiteDB.mjs is a VACUUM (useless here — freelist=0), and recreateGraphDb.mjs is a full rebuild. Nothing prunes GraphLog.

Because every consumer reads strictly WHERE log_id > <its last-seen watermark>, rows with log_id <= min(all live consumers' watermarks) are dead and safe to delete — standard CDC-log truncation.

The Fix

A GraphLog compaction step that deletes rows at or below the minimum live-consumer watermark, run periodically (a maintenance script invoked by the orchestrator/cron, or a low-frequency daemon task):

1. Determine the min consumer watermark across all live consumers (bridge daemon, WakeSubscriptionService, any remote workers). If consumer positions are already tracked durably, use their min; otherwise persist them, or fall back to a conservative retention (never prune above MAX(log_id) - SAFETY_MARGIN).
2. DELETE FROM GraphLog WHERE log_id <= :minWatermark; then checkpoint the WAL so the on-disk file actually shrinks.
3. One-time compaction of the existing 7.3M-row backlog (operator-gated — live store).

Invariant (safety contract): never delete a row any live consumer has not yet synced — prune strictly at or below the min watermark.

Contract Ledger

Decision Record impact

none — no existing ADR governs GraphLog retention; this ticket establishes the compaction policy. Sibling to ADR 0003's unified-store topology (which #12143 cites for chroma); GraphLog retention is the graph-store equivalent gap.

Acceptance Criteria

• AC1 — Measure + report current GraphLog row count + byte size (replaces the ~7.3M / 365 MB snapshot).
• AC2 — A compaction mechanism deletes GraphLog rows with log_id <= min(live-consumer watermarks), runnable periodically; statically verifiable that it never prunes above the min watermark.
• AC3 — No live consumer (bridge daemon, WakeSubscriptionService, remote workers) loses an unsynced entry (integration test: append → partial sync → compact → unsynced rows intact).
• AC4 — Post-compaction the graph db size drops toward Nodes+Edges (~60 MB) + a bounded GraphLog; verified by a follow-up page_count read.
• AC5 — WAL checkpointed after compaction so the on-disk db shrinks (post-merge / operator-gated for the one-time backlog purge).

Out of Scope

• Nodes/Edges garbage collection / Hebbian fade (#9945) — this ticket prunes the CHANGE-LOG, not the graph itself.
• .neo-ai-data/backups size (~22 GB) — NOT a separate retention bug. ai/scripts/maintenance/backup.mjs already runs a 30-day retention sweep (cleanOldBackups, keepMinimum=3 / maxDays=30, config-driven via aiConfig.maintenance.backup.retention). The ~22 GB is ~30 days of large daily bundles, each large because it carries the 365 MB GraphLog; compacting GraphLog (this ticket) shrinks every future bundle proportionally. If still too large afterward, that is a maxDays/keepMinimum config tune, not code.
• chroma test-collection leak + in-store backup-<ts> folders (#12143 / #12180).
• test-daemon-*.sqlite files leaking into the graph sqlite dir (SQLite analog of #12143; separate sibling).

Related

• #9945 (graph Hebbian decay + GC — the Nodes/Edges side)
• #10158 (Memory/Session graph nodes: telemetry + retention policy)
• #12143 / #12180 (chroma test-collection leak — sibling data-hygiene)

Origin Session ID: da9a6007-1250-4363-8c15-dff69eccb3be Retrieval Hint: "GraphLog CDC log unbounded compaction watermark"