[RED TEAM] PR #111 batch SQLite write fails with UNIQUE constraint on graph_nodes.node_id — graph left empty

[Wolfvin]

Temuan

PR #111 (RAM-first indexing) rewrote populate_graph_tables() in scripts/graph_model.py to do a single BEGIN EXCLUSIVE transaction that DELETEs all graph_nodes then executemany INSERTs the rows from the flat backend.json registry. The INSERT uses plain INSERT INTO graph_nodes ... with no OR IGNORE / OR REPLACE and no dedup of node_rows before insert.

If backend.json contains even one duplicate node_id (which happens routinely on real codebases — see reproduce below), the entire executemany raises sqlite3.IntegrityError: UNIQUE constraint failed: graph_nodes.node_id. The code catches this, issues ROLLBACK, and returns {"nodes": 0, "edges": 0}. The rollback also discards the DELETE FROM graph_nodes, so on a fresh scan the table ends up empty.

Net effect: after a normal codelens scan, graph_nodes has 0 rows and graph_edges contains only IMPORTS edges (added later by refine_call_edges). All CALLS edges are missing. Every downstream consumer of the graph (arch-metrics, callgraph, impact, dependents, graph-schema) silently returns empty/wrong results.

The warning surfaces to stderr but the scan still reports "status": "ok", masking the failure.

Cara reproduce

# Fresh clone, fully installed deps (tree-sitter + tree-sitter-python etc.)
mkdir -p /tmp/cl && cd /tmp/cl
git clone https://github.com/Wolfvin/CodeLens.git
cd CodeLens
pip install -e .
pip install tree-sitter-python tree-sitter-javascript tree-sitter-html tree-sitter-css tree-sitter-rust tree-sitter-typescript

PYTHONUTF8=1 python scripts/codelens.py init .
PYTHONUTF8=1 python scripts/codelens.py scan .

Then inspect the SQLite DB:

python -c "
import sqlite3, json
conn = sqlite3.connect('.codelens/codelens.db')
print('graph_nodes:', conn.execute('SELECT COUNT(*) FROM graph_nodes').fetchone()[0])
print('graph_edges:', conn.execute('SELECT COUNT(*) FROM graph_edges').fetchone()[0])
print('edge_types:', conn.execute('SELECT edge_type, COUNT(*) FROM graph_edges GROUP BY edge_type').fetchall())
backend = json.load(open('.codelens/backend.json'))
ids = [n['id'] for n in backend['nodes']]
from collections import Counter
dups = [(k,v) for k,v in Counter(ids).items() if v > 1]
print('duplicate node_ids in flat registry:', dups[:3])
print('flat_nodes total:', len(ids), 'unique:', len(set(ids)))
"

Output aktual

[codelens] WARNING: populate_graph_tables: batch write error: UNIQUE constraint failed: graph_nodes.node_id
{
  "status": "ok",
  ...
  "backend": { "nodes": 3595, "edges": 38441 },
  "graph":   { "nodes": 0,     "edges": 2810 },
  ...
}

# DB inspection
graph_nodes: 0
graph_edges: 2810
edge_types:   [('IMPORTS', 2810)]          # ← CALLS edges are entirely missing
duplicate node_ids in flat registry: [('scripts/smell_engine.py:511', 2)]
flat_nodes total: 3595 unique: 3594

The duplicate comes from scripts/smell_engine.py line 511, which is a code comment (# Scala: def name(), private def name()) that the smell-engine regex matches as TWO function declarations named name. Any codebase with a similar comment-regex collision triggers the bug.

Downstream symptom — codelens arch-metrics returns:

{
  "status": "ok",
  "metrics": [],
  "total_modules": 0,
  "god_modules": [],
  ...
}

...because arch_metrics._compute_metrics queries SELECT DISTINCT file FROM graph_nodes and the table is empty.

Output yang diharapkan

• The batch write must handle duplicate node_ids gracefully (e.g. INSERT OR IGNORE, INSERT OR REPLACE, or dedup node_rows in Python before insert).
• After scan, graph_nodes should contain the unique node count (3594 in this repro) and graph_edges should contain CALLS + IMPORTS edges.
• arch-metrics should return real module metrics, not an empty list.
• The scan output "graph": {"nodes": 0, ...} is currently lying — it should reflect the true count.

Severity

CRITICAL — every downstream graph-based command (arch-metrics, callgraph, impact, dependents, graph-schema) is silently broken on any codebase that produces a single duplicate node_id. The bug is reproducible on CodeLens's own codebase.

PR terkait

#111 (RAM-first indexing — closes #10)