bug(sync): `engram sync` hangs indefinitely on Windows with a large multi-project database — reproduces on 1.20.0 and 2.0.0-rc.11

[hjagar]

📝 Bug Description

engram sync (local export, no --cloud) hangs indefinitely on a machine with a large, long-lived engram.db shared across many projects. The process prints only the initial "Exporting memories..." line and never returns. It is not a classic I/O deadlock: Get-Process shows CPU time actively climbing throughout, but the engram.db-wal file size stays static for minutes at a time — active computation with no visible forward progress.

Reproduced independently on both 1.20.0 and 2.0.0-rc.11. The 2.0.0-rc.11 build already contains the projects×rows repair-scaling fix from #858 / PR #912 (verified via gh api repos/.../compare/v2.0.0-rc.11...<PR#912 merge commit> → "behind", i.e. the fix commit is an ancestor of the tag), so this does not appear to be fully explained by that fix — either a residual scaling issue in the same area, or a separate bottleneck elsewhere in the sync export path.

🔄 Steps to Reproduce

1. Accumulate a ~/.engram/engram.db over an extended period across many projects (in our case: ~30 enrolled/foreign cloud sync targets — confirmed via engram doctor's sync_target_closed_space check — and several hundred thousand total observations rows; the live DB was ~1.13 GB before a schema migration and ~2.5 GB after).
2. cd into one project's directory — in our reproduction, a project that was never cloud-enrolled (engram cloud upgrade doctor --project <name> returns reason_code: cloud_config_error, "cloud configuration is required before upgrade bootstrap").
3. Run engram sync (no flags).
4. Observe: it prints Exporting memories for project "<project>"... and never returns.

✅ Expected Behavior

engram sync for one project's local export should complete in a reasonable, bounded time (seconds), scaling with that project's own new/changed rows — not with the total size or project count of the shared engram.db, and not with cloud-enrollment state of unrelated projects.

❌ Actual Behavior

• On 1.20.0: reproduced twice in a row in the same session. Each attempt hung ~6 minutes with zero output before being killed manually (TaskStop/taskkill). engram sync --status (read-only) responded instantly and correctly both before and after each hang, so only the export path (sync with no flags) is affected.
• On 2.0.0-rc.11: after upgrading, the first invocation of any command triggered a one-time schema migration (~8 minutes, engram.db-wal growing to ~1.67 GB, CPU actively climbing — this part completed successfully, exit code 0, and is a separate expected cost, not part of this report). Immediately after, a fresh engram sync attempt for the same previously-hanging project ran for 16+ minutes with Get-Process CPU time continuously increasing (92s → 421s → 608s → 952s across the observation window) while engram.db-wal size did not change at all for multi-minute stretches, before being manually terminated.
• No error, stack trace, or partial progress indicator is ever printed. The only way to stop it is to kill the process externally.

🖥️ Environment

Operating System: Windows

Engram Version: Reproduced on both 1.20.0 and 2.0.0-rc.11

Agent / Client: Claude Code

📋 Relevant Logs

$ engram sync
Exporting memories for project "<project>"...
[hangs — no further output; process killed externally after 6-16+ minutes across multiple attempts]

$ engram sync --status
Sync status:
  Local chunks:    17
  Remote chunks:   15
  Pending import:  0
[returns instantly, both before and after each hung attempt — unaffected]

# CPU time (PowerShell Get-Process -Id <pid>).CPU, seconds, on v2.0.0-rc.11 attempt:
t+0s:    106.20
t+~5min: 421.86
t+~6min: 607.86
t+~11min: 937.13
t+~11.5min: 952.42
# engram.db-wal size stayed at exactly 1665184552 bytes across the entire t+5min..t+11.5min window

💡 Additional Context

• Closely related to bug(sync): enrolled repair still scales by projects x rows before first sync #858 / PR fix(store): optimize enrolled mutation repair (#858) #912 ("enrolled repair still scales by projects × rows before first sync"), but that fix's commit is confirmed present in the 2.0.0-rc.11 build we tested, and the hang still reproduces — at a scale (~30 projects, hundreds of thousands of rows) beyond what that issue's own benchmark matrix covered (worst case there was 100 projects / 100K rows). This suggests either a residual multiplicative cost not covered by the fix(store): optimize enrolled mutation repair (#858) #912 fix, or a different bottleneck in the sync export path itself (as opposed to the pre-sync repair step bug(sync): enrolled repair still scales by projects x rows before first sync #858 targeted).
• The affected project in our reproduction was never cloud-enrolled, ruling out per-project cloud state as the direct cause — the cost appears tied to the shared database's overall size/project count rather than the invoked project's own data.
• We did not have access to sync.go's source to pinpoint the exact loop; happy to provide the full engram doctor --json output, exact DB statistics, or run further instrumented reproduction steps if useful.
• Both the database and the previous binary were backed up before any of this investigation, so we have a clean before/after state if a maintainer wants specific before/after comparisons run.

[hjagar]

Update: found a likely contributing factor + a workaround

While investigating this further (via engram doctor and direct read-only inspection of ~/.engram/engram.db), the local database on this machine turned out to have a massive amount of duplicate observations rows spread across several projects — on the order of ~417k total rows, of which only ~1.2k were genuinely distinct (grouped by sync_id; in a couple of cases the same sync_id even carried unrelated content across "revisions", which looks like a separate sync_id-collision issue rather than a normal revision history). The live engram.db was ~1.13 GB.

After deduplicating those rows (keeping one row per distinct sync_id, verified byte-identical content within each group before removing the rest) and running VACUUM, the database shrank to ~429 MB.

After that cleanup, engram sync for the same project that was hanging 15+ minutes (active CPU, but engram.db-wal size static for long stretches — see the original report above) now completes in well under 1 second.

This doesn't necessarily pin down the exact code path, but it's a strong signal that sync's cost scales with total database size/row count (across all projects, not just the invoked one) in a way that becomes pathological once the database accumulates a few hundred thousand rows — regardless of what caused those rows to accumulate in the first place (in our case, an apparent duplicate-insert bug, not something we could identify precisely from the outside). A database this size might be a realistic worst case worth a dedicated perf pass on the sync export path itself, separate from the projects×rows repair-scaling issue in #858/#912.

Happy to share more detail (redacted engram doctor --json output, timing numbers) if useful.

[dnlrsls]

Thanks for the unusually strong production evidence. This is still actionable. The project-scoped export path exists on current main, but Git history shows that path predates the affected RC11 build, so its presence does not supersede the reproduced hang.\n\nApproving this as a bounded performance investigation/fix: profile the local export path with a duplicate-heavy multi-project fixture, identify the actual hotspot, keep work scoped to one-project export, and add a deterministic benchmark or regression that fails on the pathological behavior.

[dnlrsls]

Exact-main profile and accepted optimization boundary

Profiled main@70870987f56ae662acf3cfa36fa7e9a99efa9fb3 with the target project fixed at 1 session, 10 observations, and 1 prompt while only unrelated rows changed.

Unrelated rows	Projects	Local export
1,000	1	122 ms
25,000	25	205 ms
100,000	100	636 ms
250,000	250	1,406 ms
100,000	1	590 ms

The 16-minute production hang was not reproduced at this scale, but the root scaling violation is confirmed: cost follows unrelated row count, not target-project rows or project count.

CPU profile for five exports over 250k unrelated rows:

• runtime.cgocall: 77.43%
• SQLite sqlite3VdbeExec: 73.96% cumulative
• SQLite sqlite3BtreeNext: 62.85% cumulative
• exportWithProjectScope: 44.79% cumulative
• ExportRelationMutations: 17.36%

Query plans show full scans of sessions, observations, and prompts. Existing project indexes are defeated by ifnull(project, '') and OR predicates; sessions has no project index.

Accepted design

1. Rewrite project-scoped export into indexable direct-project and legacy-blank branches, using UNION ALL/joins or equivalent so project = ? can use existing indexes.
2. Add an index for sessions(project).
3. Apply the same project-set strategy to relation selection and relation-mutation export; do not leave one full-scan tail.
4. Preserve legacy NULL/blank ownership rescue semantics exactly.
5. Keep full unscoped backup behavior unchanged.

Required proof

• query-plan tests show indexed searches for direct project rows rather than full scans;
• a duplicate-heavy multi-project benchmark keeps target rows fixed while unrelated rows grow and records the slope;
• legacy NULL/blank observation and prompt rows still follow their session's project;
• relation endpoints and relation mutation export remain complete;
• export bytes/order remain deterministic;
• local sync output and import round-trip are unchanged.

Non-goals

• Do not combine this with autosync pagination (feat(cloud): add manual cloud mutation pull command (#327) #1282) or blocked-push behavior (Autosync skips the pull step while non-enrolled projects have pending mutations, blocking cross-machine replication #1273).
• Do not delete/deduplicate user data as a performance workaround.
• Do not hide long work only with a progress spinner.