FOREIGN KEY constraint failed during watch sync (v0.9.5 — regression from #28/#42/#53)

[jaepo01]

Summary

FOREIGN KEY constraint failed errors during watch sync, identical to previously fixed #28/#42/#53. However, this occurs on v0.9.5 (latest), where the fix from PR #62 should already be included.

Environment

• CodeGraph version: 0.9.5 (latest via npm)
• OS: Ubuntu 24.04, Linux x64
• Project: Python monorepo, 397 files, 7710 nodes, 7325 edges
• Languages: Python (384), YAML (12), JavaScript (1)
• Storage: External USB drive (ext4)
• Runtime: Node.js v22.22.2

Reproduction

1. codegraph init + codegraph index — succeeds cleanly
2. Start daemon via codegraph serve --mcp
3. Watch sync triggers on file changes → FK errors appear intermittently
4. Some syncs succeed, some fail — inconsistent

daemon.log (after fresh reinit)

[CodeGraph daemon] Listening on ... (pid 593640, v0.9.5). Idle timeout 300000ms.
[CodeGraph MCP] File watcher active — graph will auto-sync on changes
[CodeGraph MCP] Auto-synced 1 file(s) in 1025ms
[CodeGraph MCP] Auto-synced 1 file(s) in 307ms
[CodeGraph MCP] Auto-synced 2 file(s) in 324ms
[CodeGraph MCP] Auto-synced 1 file(s) in 285ms
[CodeGraph] Watch sync failed { error: 'FOREIGN KEY constraint failed' }
[CodeGraph MCP] Auto-sync error: FOREIGN KEY constraint failed
[CodeGraph] Watch sync failed { error: 'FOREIGN KEY constraint failed' }
[CodeGraph MCP] Auto-sync error: FOREIGN KEY constraint failed
... (76 occurrences total in ~2 days of uptime)

Key observations

1. Not limited to C/C++ headers: Previous issues (Failed to index: FOREIGN KEY constraint failed (tensorflow) #28/CodeGraph v0.6.2: FOREIGN KEY constraint failed #42/Failed to index: FOREIGN KEY constraint failed #53) were triggered by tree-sitter producing empty-named nodes in C header files. This project is Python-only (384 files) — the trigger is different.
2. Intermittent: Some watch syncs succeed, others fail. Not deterministic.
3. Compounding: Once FK integrity is broken, subsequent syncs continue to fail.
4. Survives reindex: After fresh codegraph init + codegraph index, the daemon produces FK errors again on its first watch sync cycle (2 errors observed immediately).

Workaround

Delete .codegraph/ entirely and reinitialize:

rm -rf .codegraph/
codegraph init .
codegraph index .

This resolves it temporarily, but FK errors reappear over time.

Related

• Failed to index: FOREIGN KEY constraint failed (tensorflow) #28 — Original FK issue (v0.5.7, C headers)
• CodeGraph v0.6.2: FOREIGN KEY constraint failed #42 — Same issue (v0.6.2)
• Failed to index: FOREIGN KEY constraint failed #53 — Same issue (v0.6.x)
• PR fix: Prevent FK constraint failure from nodes with empty names #62 — Fix for the above

[colbymchenry]

Fixed in #463, on main.

Root cause. PR #62 filtered out stale-target edges at the extraction-layer insertEdges site (your unresolved_refs table is clean), but the resolution-layer insertEdges — the one watch sync hits — wasn't guarded the same way. Some framework resolvers cache native Node references in a WeakMap keyed by the resolver context that persists across syncs; a file rewrite between map build and resolution can leave a dangling node ID in there, and the resulting edge fails FK and aborts the whole resolution batch. That's why it (a) reappeared on a Python project — your repo has 1 JS file which can trigger detection of frameworks with this caching pattern, and (b) compounded — a sync that fails leaves the rest of its edges unwritten, so the next sync has more to do and more chances to hit the same stale entry.

Fix. The resolution layer now mirrors #62's defense — one cache-aware getNodesByIds per pass drops any edge whose source or target is no longer in nodes, so the rest of the batch still lands. The regression test reproduces the exact FOREIGN KEY constraint failed you saw and confirms it goes away.

Will ship in the next release.

[colbymchenry]

Heads-up: I initially merged #463 to fix this, then realized #462 had a cleaner approach — DB-layer filter inside QueryBuilder.insertEdges itself instead of a filter spread across three resolution-layer call sites. The lower-layer fix protects every present and future insertEdges caller automatically and doesn't depend on the queries-layer nodeCache invalidation staying perfect.

So #463 was reverted on main (commit 028d25f) and #462 (commit 572b1ed) is what's actually shipping. Same user-facing fix, slightly cleaner internals. Thanks to @msnandhis for the better take.