Duplicate edges: `INSERT OR IGNORE` on `edges` is a no-op without a UNIQUE constraint

[inth3shadows]

Heads up — I'm fairly confident but not certain about the mechanism here. I found this while verifying #1023, and I've reproduced the numbers deterministically on my end, but I haven't traced every layer, so please treat the root-cause as a hypothesis.

What I'm seeing

The graph carries duplicate edge rows. Two distinct flavors, and I'm more sure of the first than the second.

(A) Intra-build duplicates — appears in every clean build, ~527 rows

A clean index of this repo (the codegraph repo itself) produces 527 byte-identical edge rows — same source, target, kind, line, col, same metadata. Deterministic: two independent uninit -f && init builds give the exact same count.

codegraph init
# extra rows beyond one-per-(source,target,kind,line,col):
sqlite3 .codegraph/codegraph.db \
  "SELECT (SELECT count(*) FROM edges)
        - (SELECT count(*) FROM (SELECT DISTINCT source,target,kind,line,col FROM edges));"
# => 527

Pulling one such group, the two rows have consecutive ids and identical metadata, which is what makes me think the reference resolver emits the same resolved edge twice within a single build (rather than this being a crash/re-run artifact):

{"id":19844,"source":"class:5803…","target":"class:5803…","kind":"references","line":153,"col":12,"provenance":null,"metadata":"{\"confidence\":0.9,\"resolvedBy\":\"exact-match\"}"}
{"id":19845,"source":"class:5803…","target":"class:5803…","kind":"references","line":153,"col":12,"provenance":null,"metadata":"{\"confidence\":0.9,\"resolvedBy\":\"exact-match\"}"}

All 527 are provenance = null (resolution stage), not synthesized.

(B) Accumulating duplicates on repeated full re-index — smaller, narrower, less certain

Re-running codegraph index repeatedly over the same DB (no file changes) holds the null-provenance edges constant at 19,885 but grows provenance='heuristic' (synthesized) edges by +7 each run: 19,892 → 19,899 → … The synthesis pass (src/resolution/index.ts, the synthesizeCallbackEdges call) looks additive and I don't see anything clearing prior heuristic edges before it re-runs.

Important caveats on (B), since I don't want to overstate it:

• A no-op codegraph sync (the git-hook path) does not accumulate — stable across repeated runs. So this is not "grows on every commit."
• I tested full index only; I have not tested sync with file changes, so I don't know whether normal incremental syncs trigger it.

Why INSERT OR IGNORE doesn't catch either

insertEdge uses INSERT OR IGNORE INTO edges (...), but as far as I can tell the edges table has no UNIQUE constraint on the identity columns — schema.sql defines only an autoincrement PK and non-unique indexes, and I couldn't find a UNIQUE in the migration history either. OR IGNORE only ignores on a constraint conflict, so with nothing unique to conflict on it behaves like a plain INSERT — no dedup. I think this is easy to miss on a read precisely because OR IGNORE reads as "dedup handled."

Impact (my read)

• Inflated edge counts (~2.7% on this repo from (A) alone) vs. the "no edge explosion" expectation.
• Duplicate rows appear to flow into getCallers / getCallees / impact, since traversal dedups visited nodes but not edges.

Precision note

I'm only flagging the 527 exact-identical rows as wrong. There are also ~6.4k (source,target,kind) duplicates, but most of those are legitimately distinct call sites (different line/col), so I'm not claiming those are bugs.

Possible directions (genuinely unsure which fits the architecture)

• A UNIQUE index on edges(source,target,kind,line,col) so OR IGNORE actually dedups (needs a migration), and/or
• deduping in the resolver/synthesizer before insertEdges, and/or
• clearing synthesized edges before the synthesis pass re-runs (for (B)).

Environment

Reproduced with the published 1.0.1 CLI indexing current main. The relevant code (INSERT OR IGNORE, no UNIQUE constraint) looks unchanged on main, so I'd expect it to reproduce with a local build too — but flagging that I indexed with 1.0.1. Node 22 / node:sqlite backend.

Happy to share the exact queries or a repro script.

[colbymchenry]

Fixed in #1050 (merged to main).

You diagnosed this exactly right, and the trace held up: the edges table had no UNIQUE constraint, so insertEdge's INSERT OR IGNORE had nothing to conflict on and behaved like a plain INSERT. I reproduced flavor (A) deterministically — a clean index of this repo's src/ carried 476 byte-identical duplicate references rows — and traced it to two extraction passes emitting the same edge (e.g. a self-referential return type captured twice).

Fix: a UNIQUE identity index on (source, target, kind, IFNULL(line,-1), IFNULL(col,-1)) — added to the schema for fresh databases and via a migration (dedup existing rows, then create the index) for existing ones. That makes INSERT OR IGNORE actually dedup, catching every double-emit rather than chasing each emitting pass. IFNULL folds the nullable line/col so coordinate-less edges dedup too; distinct call sites (same endpoints/kind, different line/col) are preserved. Your existing index is de-duplicated automatically the next time CodeGraph opens it.

On flavor (B): it doesn't reproduce on current main — codegraph index now rebuilds from scratch, so heuristic edges stay flat across re-runs (that was likely specific to the 1.0.1 behavior you indexed with). The new uniqueness constraint covers that case defensively anyway.

Ships in the next release. Thanks again — the precision note distinguishing the 527 byte-identical rows from the ~6.4k legitimately-distinct call sites saved real time.