Graph Storage Schema in SQLite: How code-review-graph Persists Nodes and Edges

The code-review-graph project stores its entire knowledge graph in a single SQLite database (graph.db) using three core tables—nodes, edges, and metadata—plus migration-driven extensions for full-text search, communities, and flow tracking, with all writes performed through optimistic upserts in graph.py.

The code-review-graph repository implements a durable SQLite graph storage schema to persist code analysis results across sessions. Rather than using an external graph database, the project leverages SQLite's built-in capabilities with a carefully designed relational schema that maps graph concepts to SQL tables while maintaining ACID compliance and enabling fast lookups via strategic indexing.

Core Schema Architecture

The foundational schema is initialized by GraphStore._init_schema in code_review_graph/graph.py (lines 61-104). This creates the minimal table structure required to represent a code knowledge graph, with subsequent features added through a versioned migration system.

The nodes Table

The nodes table stores every code element discovered during analysis—files, classes, functions, types, and tests. It includes seventeen columns designed to capture both identity and metadata:

• id: Primary key
• kind, name, qualified_name: Identity and type classification (UNIQUE constraint on qualified_name)
• file_path, line_start, line_end: Source location
• language, parent_name: Context and hierarchy
• params, return_type, modifiers: Callable signatures
• is_test, file_hash, extra: Testing flags and extensible metadata
• updated_at: Timestamp for cache invalidation

This structure allows the system to distinguish between overloaded methods via the qualified_name column, which serves as the natural unique identifier for upsert operations.

The edges Table

The edges table represents relationships between code elements such as CALLS, IMPORTS_FROM, and INHERITS. Key columns include:

• kind: Relationship type
• source_qualified and target_qualified: Foreign key equivalents referencing node identities
• file_path and line: Callsite location
• confidence and confidence_tier: Data quality metrics added in migration v9
• extra: JSON extensibility
• updated_at: For incremental sync

Unlike traditional foreign keys, the schema uses qualified_name strings rather than integer IDs for linking, enabling nodes to be referenced before they exist in the database (eventual consistency model).

Metadata and Indices

The metadata table stores key-value pairs including the current schema version, checked on startup to trigger migrations. Multiple indices accelerate graph traversals:

• Indices on qualified_name (nodes) and source_qualified/target_qualified (edges)
• Compound indices added in later migrations for (target_qualified, kind) and (source_qualified, kind) pairs

Migration-Driven Schema Evolution

The schema evolves through nine versioned migrations defined in code_review_graph/migrations.py. The run_migrations(self._conn) function checks the metadata table on initialization and applies incremental DDL changes:

Version	Addition
v2	signature column to nodes
v3	flows and flow_memberships tables for dataflow tracking
v4	communities table and community_id foreign key
v5	FTS5 virtual table nodes_fts for full-text search
v6	community_summaries, flow_snapshots, and risk_index with risk_score indexing
v7	Compound indices on edges for query performance
v8	Composite index for upsert performance
v9	confidence and confidence_tier columns to edges

Each migration function (e.g., _migrate_v5 at lines 49-57) executes raw SQL ALTER statements, allowing existing databases to upgrade in place without data loss.

Node and Edge Persistence Mechanisms

The GraphStore class provides atomic upsert operations that translate Python dataclasses (NodeInfo, EdgeInfo) into SQLite transactions.

Upserting Nodes

The GraphStore.upsert_node method implements an INSERT...ON CONFLICT pattern against the qualified_name unique constraint:

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, ...
    """,
    (node.kind, node.name, qualified, ...)
)

This guarantees idempotency—running the parser twice on the same file updates existing records rather than creating duplicates.

Upserting Edges

The GraphStore.upsert_edge method uses a lookup-then-update pattern because edges are identified by a composite key (kind, source, target, file_path, 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 existing:
    self._conn.execute(
        "UPDATE edges SET confidence=?, confidence_tier=?, updated_at=? WHERE id=?",
        (confidence, confidence_tier, now, existing["id"])
    )
else:
    self._conn.execute(
        """INSERT INTO edges (...) VALUES (...)""",
        (edge.kind, edge.source, ...)
    )

Both methods utilize sqlite3.Row factories and implicit transactions (via BEGIN IMMEDIATE in batch operations) to ensure consistency while invalidating the in-memory NetworkX cache when mutations occur.

Practical Workflow Example

Typical usage combines the parser with the storage layer:

from code_review_graph.graph import GraphStore
from code_review_graph.parser import NodeInfo, EdgeInfo

with GraphStore("my_project/graph.db") as store:
    # Persist a function node

    fn = NodeInfo(
        kind="function",
        name="process_data",
        qualified_name="my_pkg.handlers.process_data",
        file_path="my_pkg/handlers.py",
        line_start=10,
        line_end=20,
        language="python",
        parent_name="handlers",
        params="data, config",
        return_type="bool",
        modifiers=None,
        is_test=False,
        extra={"docstring": "Process incoming data"},
    )
    store.upsert_node(fn)
    
    # Persist a CALLS relationship

    edge = EdgeInfo(
        kind="CALLS",
        source="my_pkg.main.main",
        target="my_pkg.handlers.process_data",
        file_path="my_pkg/main.py",
        line=45,
        extra={"confidence": 0.98},
    )
    store.upsert_edge(edge)

The high-level API abstracts the raw SQL while maintaining the performance characteristics of prepared statements and indexed lookups.

Summary

• code-review-graph uses a single SQLite file (graph.db) with a relational schema mapping graph entities to the nodes, edges, and metadata tables
• The qualified_name column serves as the natural primary identifier for nodes, enabling string-based foreign key relationships in edges
• Schema versioning in migrations.py supports nine incremental upgrades including FTS5 search and risk indices without breaking existing databases
• Persistence occurs through upsert operations in graph.py (lines 18-84) that handle conflict resolution and cache invalidation automatically
• Composite indices on (source_qualified, kind) and (target_qualified, kind) optimize graph traversal queries common in code analysis

Frequently Asked Questions

How does code-review-graph handle schema changes without losing data?

The repository implements a versioned migration system in code_review_graph/migrations.py that checks the metadata table on startup. Each schema change (from v2 through v9) is applied incrementally via ALTER TABLE and CREATE INDEX statements, allowing existing graph.db files to upgrade automatically while preserving node and edge data.

Why does the edges table use qualified names instead of integer foreign keys?

The schema uses string-based identifiers (source_qualified, target_qualified) rather than integer IDs to support eventual consistency during analysis. This design allows edges to reference nodes that may not yet be parsed or inserted, eliminating foreign key constraint violations when processing files in arbitrary order.

What performance optimizations exist for large codebases?

The schema includes multiple optimization layers: FTS5 virtual tables for fast text search (added v5), compound indices on edge lookups (added v7-v8), and composite unique constraints that enable SQLite's native UPSERT syntax. The GraphStore class also maintains an in-memory NetworkX graph with write-through caching to minimize database round-trips during complex traversals.

How are confidence scores stored for inferred relationships?

Since migration v9, the edges table includes confidence (REAL) and confidence_tier (TEXT) columns. These are populated during the upsert_edge operation in graph.py, allowing the system to distinguish between statically extracted relationships (high confidence) and inferred or heuristic relationships (lower confidence) when querying the graph.

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