How tirth8205/code-review-graph Stores Nodes and Edges in SQLite: Inside the GraphStore Architecture

The GraphStore class persists code entities and relationships using three SQLite tables (nodes, edges, and metadata) with atomic upsert operations and IMMEDIATE transactions to ensure data consistency.

The tirth8205/code-review-graph repository implements a durable knowledge graph for code analysis that stores nodes and edges in SQLite using a relational schema optimized for software entities. The storage layer centers on the GraphStore class defined in code_review_graph/graph.py, which manages database connections, schema initialization, and atomic write operations.

SQLite Schema Structure for the Graph

When a GraphStore instance is created, it executes the module-level constant _SCHEMA_SQL to initialize the database. The design uses three distinct tables to separate entities from relationships and metadata.

The nodes Table

The nodes table stores one row per code entity (files, classes, functions, types, and tests). It uses an auto-incrementing id as the primary key, but enforces uniqueness on qualified_name (a composite identifier including kind and scope). Important columns include:

• kind – entity type (e.g., "Function", "Class")
• name – short identifier
• qualified_name – globally unique name (UNIQUE constraint)
• file_path, line_start, line_end – source location
• language, parent_name, params, return_type – semantic details
• modifiers, is_test – binary flags
• file_hash – content fingerprint for invalidation
• extra – JSON blob for extensible attributes

The edges Table

The edges table captures directed relationships such as CALLS, IMPORTS_FROM, and INHERITS. It links two nodes via their qualified names rather than integer IDs, simplifying queries across the graph. Key columns include:

• kind – relationship type
• source_qualified – origin node name
• target_qualified – destination node name
• file_path, line – location of the relationship
• confidence and confidence_tier – relationship strength metrics
• extra – JSON metadata

The metadata Table

The metadata table functions as a key-value store for global state, most notably the schema_version that powers the migration framework.

Persisting Nodes with Upsert Semantics

Node insertion happens through GraphStore.upsert_node() (lines 99‑130 in code_review_graph/graph.py). The method constructs a qualified name and performs an atomic INSERT ... ON CONFLICT operation, ensuring that re-indexing the same file updates existing records rather than creating duplicates.

The implementation converts the node's extra dictionary to JSON, computes the qualified identifier via self._make_qualified(node), and executes:

qualified = self._make_qualified(node)
extra = json.dumps(node.extra) if node.extra else "{}"
self._conn.execute(
    """INSERT INTO nodes
       (kind, name, qualified_name, file_path, line_start, line_end,
        language, parent_name, params, return_type, modifiers, is_test,
        file_hash, extra, updated_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
       ON CONFLICT(qualified_name) DO UPDATE SET
         kind=excluded.kind, name=excluded.name,
         file_path=excluded.file_path, …, updated_at=excluded.updated_at""",
    (node.kind, node.name, qualified, node.file_path,
     node.line_start, node.line_end, node.language,
     node.parent_name, node.params, node.return_type,
     node.modifiers, int(node.is_test), file_hash,
     extra, now)
)

This upsert pattern guarantees that the qualified_name remains unique while refreshing all mutable attributes (file location, line numbers, hashes) in a single statement.

Persisting Edges with Duplicate Detection

Edge storage in GraphStore.upsert_edge() (lines 32‑65 in code_review_graph/graph.py) follows a slightly different pattern. Because edges do not have a single natural unique key covering all semantic variations, the method first probes for an existing relationship matching the exact kind, source_qualified, target_qualified, file_path, and line:

existing = self._conn.execute(
    """SELECT id FROM edges
       WHERE kind=? AND source_qualified=? AND target_qualified=?
             AND file_path=? AND line=?""",
    (edge.kind, edge.source, edge.target, edge.file_path, edge.line)
).fetchone()

If a matching row exists, the method issues an UPDATE to refresh metadata; otherwise, it performs an INSERT and returns the new rowid. This selective approach prevents duplicate relationship entries while allowing metadata refresh when the same edge is re-discovered.

Transaction Safety and Atomicity

All write-heavy operations in the repository wrap mutations inside IMMEDIATE transactions via _begin_immediate. Unlike SQLite's default autocommit mode, this strategy locks the database at the start of the transaction, preventing race conditions during bulk imports.

If any operation fails, the transaction rolls back completely, ensuring the graph remains in a consistent state. This is particularly important for batch operations like store_file_nodes_edges, which may insert hundreds of nodes and edges in a single logical unit.

Schema Evolution and Migrations

The repository includes a migration framework in code_review_graph/migrations.py to handle schema changes without data loss. On initialization, GraphStore queries the metadata table for the current schema_version using get_schema_version().

If the version is outdated or missing, the system invokes run_migrations() to execute incremental SQL scripts (e.g., adding columns, creating FTS5 virtual tables, or introducing community detection tables). This versioning strategy allows the nodes and edges tables to evolve while preserving existing graph data.

Working with GraphStore: Complete Example

The following example demonstrates initializing a graph database, inserting a function node, and creating a CALLS relationship:

from pathlib import Path
from code_review_graph.graph import GraphStore, NodeInfo, EdgeInfo

# 1. Initialise the graph database

store = GraphStore(Path("/tmp/myproject/graph.db"))

# 2. Insert a function node

fn_node = NodeInfo(
    kind="Function",
    name="process_data",
    qualified_name="my_pkg.process_data",
    file_path="my_pkg/utils.py",
    line_start=10,
    line_end=20,
    language="python",
    parent_name="MyClass",
    params="data: dict",
    return_type="dict",
    modifiers=None,
    is_test=False,
    extra={"docstring": "Process incoming data"}
)
node_id = store.upsert_node(fn_node, file_hash="abc123")
print(f"Node stored with id {node_id}")

# 3. Insert a CALLS edge

call_edge = EdgeInfo(
    kind="CALLS",
    source="my_pkg.process_data",
    target="my_pkg.validate",
    file_path="my_pkg/utils.py",
    line=15,
    extra={"confidence": 0.98}
)
edge_id = store.upsert_edge(call_edge)
print(f"Edge stored with id {edge_id}")

# 4. Query directly via the connection

rows = store._conn.execute(
    "SELECT qualified_name, file_path FROM nodes WHERE kind='Function'"
).fetchall()
for r in rows:
    print(r["qualified_name"], "→", r["file_path"])

Summary

• Three-table schema: The nodes, edges, and metadata tables separate entities, relationships, and versioning information.
• Qualified name uniqueness: Nodes are uniquely identified by qualified_name rather than auto-generated integers, enabling stable references across file changes.
• Atomic upserts: Both upsert_node() and upsert_edge() use SQLite's conflict resolution or manual existence checks to prevent duplicates while refreshing metadata.
• Immediate transactions: Write operations use _begin_immediate to guarantee atomicity and prevent partial writes during batch imports.
• Migration support: The migrations.py module tracks schema versions in the metadata table, enabling safe evolution of the storage layer.

Frequently Asked Questions

What is the primary key for nodes in the SQLite database?

The nodes table uses an auto-incrementing integer id as its formal primary key, but enforces uniqueness via the qualified_name column, which combines the entity's kind, name, and scope. This qualified name serves as the application-level identifier for upsert operations and edge references.

How does GraphStore handle duplicate node insertions?

The upsert_node() method uses SQLite's INSERT ... ON CONFLICT(qualified_name) DO UPDATE syntax. When a node with the same qualified name already exists, the statement updates all mutable columns (file path, line numbers, hash, JSON extras) rather than inserting a new row, maintaining a single canonical record per entity.

What transaction mode does code-review-graph use?

The repository explicitly uses IMMEDIATE transactions via _begin_immediate. This mode acquires locks at the transaction start rather than on first write, preventing SQLITE_BUSY errors during concurrent access and ensuring that bulk operations in store_file_batch remain atomic.

How does the schema evolve when new columns are needed?

The migrations.py module manages schema evolution by reading the schema_version from the metadata table. When GraphStore initializes, it compares this version against the codebase's expected version and runs any pending migration scripts (adding columns, indexes, or virtual tables) through the run_migrations() function, preserving existing node and edge data.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}