bug(store): store.New runs repairEnrolledProjectSyncMutations synchronously — O(enrolled projects × table scans) startup breaks MCP handshake timeouts

[xabilarra]

Summary

store.New runs repairEnrolledProjectSyncMutations() synchronously on every process start (any CLI command and engram mcp). The fast-path check projectNeedsBackfill() executes 4 COUNT queries per enrolled project, and the observations/prompts ones cannot use an index for the project filter, so each one full-scans the tables. With many enrolled projects this makes startup take longer than MCP client handshake timeouts, so the MCP server never connects.

Environment

• engram v1.20.0 (Homebrew, macOS arm64)
• ~/.engram/engram.db: 287 MB — 21,733 observations, 9,109 sessions, 15,621 user_prompts, 82,643 sync_mutations
• 88 rows in sync_enrolled_projects (accumulated over months; includes junk like /, tmp.* from throwaway dirs — see "Aggravating factor" below)

Symptom

• Claude Code: Failed to reconnect to plugin:engram:engram: MCP server connection timed out after 30000ms on every session. No mem_* tools available.
• OpenCode: same, worse — its default MCP tool-fetch timeout is 5 s.
• Any CLI command pays the same cost: engram search "x" --limit 1 took 66 s wall clock before returning.

Diagnosis

SIGQUIT goroutine dump of a "hung" engram search shows it busy inside the startup repair, not the search:

modernc.org/sqlite/lib._sqlite3VdbeExec(...)
...
github.com/Gentleman-Programming/engram/internal/store.(*Store).projectNeedsBackfill
	internal/store/store.go:5093
github.com/Gentleman-Programming/engram/internal/store.(*Store).repairEnrolledProjectSyncMutations
	internal/store/store.go:5127
github.com/Gentleman-Programming/engram/internal/store.New
	internal/store/store.go:644
main.cmdSearch

Timing the projectNeedsBackfill queries directly against my DB (per project):

query	wall time
sessions	0.10 s
observations	1.27 s
prompts	0.14 s
relations	0.16 s

≈ 1.7 s × 88 enrolled projects ≈ 60–150 s of startup work, every invocation, even when zero projects actually need backfill.

The observations query is the hot one because the filter can't use an index:

WHERE (ifnull(o.project,'') = ? OR (ifnull(o.project,'') = '' AND ifnull(s.project,'') = ?))

EXPLAIN QUERY PLAN shows SEARCH o USING INDEX idx_obs_deleted (deleted_at=?) — i.e. it walks every live observation for every enrolled project. The NOT EXISTS probe itself is fine (idx_sync_mutations_lookup covers it).

Aggravating factor

sync_enrolled_projects only ever grows. Months of normal use (plus cwd-derived project names from temp dirs) left me with 88 enrollments: /, t, tmp, ten tmp.<random> entries, transcripts, and near-duplicates (ducky, ducky-logistics, duckylogistics). Each one costs a full scan per start, forever.

Workarounds applied locally

1. Pruned sync_enrolled_projects from 88 → 31 rows (backed up first) → startup dropped from 66 s to ~19 s, MCP handshake connects again.
2. Raised client timeouts (MCP_TIMEOUT=120000 in Claude Code, "timeout": 120000 in opencode.json, startup_timeout_sec = 120 in codex).

Suggested fixes (any subset helps)

1. Don't block the MCP handshake: run repairEnrolledProjectSyncMutations in a background goroutine after the server starts answering initialize (or lazily before the first sync push, where the journal is actually consumed).
2. Make the check O(1) per start: persist a per-project "repaired at max(rowid)" watermark and skip the COUNTs when nothing changed since.
3. Fix the scan: batch all enrolled projects into one pass (single scan of observations grouped by project) instead of N scans, and/or index the effective project expression.
4. Stop enrollment rot: let delete project / consolidation clean sync_enrolled_projects, and consider expiring enrollments with no activity (would also cap the cost of 1–3).

Happy to provide more timings or test a patch against this DB.