SQLite Graph Storage Schema in code-review-graph: Node and Edge Persistence Explained

The code-review-graph project stores its entire knowledge graph in a single SQLite database (graph.db) using a core schema of three tables—nodes, edges, and metadata—plus versioned migrations that add features like full-text search and risk indexing.

The tirth8205/code-review-graph repository implements a durable, queryable storage layer for code analysis data. This article examines exactly how the SQLite graph storage schema works, how nodes and edges define their relationships, and how the system evolves through migrations without breaking existing data.

Core Schema: Three Tables and Strategic Indices

The foundational schema is created by GraphStore._init_schema() in code_review_graph/graph.py (lines 61-104). It consists of three permanent tables designed for fast lookups and conflict-free upserts.

The nodes Table

The nodes table stores every code element discovered during analysis—files, classes, functions, types, and tests. Its 16 columns capture location, structure, and metadata:

• qualified_name – UNIQUE primary lookup key (e.g., mypackage.module.Class.method)
• kind – Node type: file, class, function, type, test
• name – Short identifier without namespace
• file_path, line_start, line_end – Source location
• language – Detected programming language
• parent_name – Containing scope for nested elements
• params, return_type, modifiers – Function signatures
• is_test – Boolean flag for test detection
• file_hash – Content hash for invalidation
• extra – JSON blob for extensibility
• updated_at – Timestamp for cache invalidation

The edges Table

The edges table stores directed relationships between nodes using their qualified_name values as foreign keys:

• source_qualified – Origin node identifier
• target_qualified – Destination node identifier
• kind – Relationship type: CALLS, IMPORTS_FROM, INHERITS, CONTAINS, etc.
• file_path, line – Where the relationship was observed
• confidence – Numerical confidence score (0.0-1.0)
• confidence_tier – Categorical label: EXTRACTED, INFERRED, HEURISTIC
• extra – JSON metadata
• updated_at – Timestamp

The metadata table holds singleton configuration, most importantly the current schema version used by the migration system.

Index Strategy

Multiple indices accelerate graph traversal queries:

• qualified_name on nodes – Primary node lookup
• source_qualified and target_qualified on edges – Forward and reverse traversal
• Composite indices on (target_qualified, kind) and (source_qualified, kind) – Filtered neighbor queries

Schema Evolution: Versioned Migrations

The schema is not static. code_review_graph/migrations.py implements a linear migration chain from v1 to v9, each adding capabilities without breaking existing queries.

Version	Change	Purpose
v2	signature column on nodes	Store computed function signatures
v3	flows and flow_memberships tables	Data flow analysis
v4	communities table, community_id on nodes	Community detection results
v5	FTS5 virtual table nodes_fts	Full-text search over code
v6	community_summaries, flow_snapshots, risk_index, risk_score index	Risk analysis and reporting
v7	Compound indices on edges	Query performance
v8	Composite index for upsert performance	Bulk insertion speed
v9	confidence and confidence_tier on edges	Relationship quality tracking

Migration functions like _migrate_v5() (lines 49-57) execute pure SQL DDL. The MIGRATIONS dictionary (lines 45-54) maps version numbers to functions. On initialization, GraphStore.__init__ calls run_migrations(self._conn) to apply pending upgrades automatically.

Node and Edge Persistence: Upsert Semantics

The GraphStore class provides atomic upsert operations that translate between Python data classes and SQLite, handling both insertion and update in single transactions.

Node Upsert: INSERT ... ON CONFLICT

# From GraphStore.upsert_node() in graph.py lines 18-50

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,
         line_start=excluded.line_start,
         line_end=excluded.line_end,
         language=excluded.language,
         parent_name=excluded.parent_name,
         params=excluded.params,
         return_type=excluded.return_type,
         modifiers=excluded.modifiers,
         is_test=excluded.is_test,
         file_hash=excluded.file_hash,
         extra=excluded.extra,
         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)
)

The SQLite UPSERT pattern (ON CONFLICT ... DO UPDATE) ensures idempotent writes: re-parsing the same file updates existing nodes rather than creating duplicates. The excluded.* syntax references the attempted insert values.

Edge Upsert: Select-Then-Modify Pattern

# From GraphStore.upsert_edge() in graph.py lines 52-84

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 line=?, extra=?, confidence=?, confidence_tier=?,"
        " updated_at=? WHERE id=?",
        (edge.line, extra, confidence, confidence_tier, now, existing["id"])
    )
else:
    self._conn.execute(
        """INSERT INTO edges
           (kind, source_qualified, target_qualified, file_path, line,
            extra, confidence, confidence_tier, updated_at)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)""",
        (edge.kind, edge.source, edge.target, edge.file_path, edge.line,
         extra, confidence, confidence_tier, now)
    )

Edges use a query-then-decide pattern because they lack a single natural unique key. The composite match on (kind, source_qualified, target_qualified, file_path, line) identifies "the same relationship observed at the same location." This allows incremental updates to confidence scores as analysis improves.

Practical Usage: Creating Nodes and Edges

The storage layer is accessed through GraphStore context managers:

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

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

    handler = NodeInfo(
        kind="function",
        name="validate_request",
        qualified_name="api.handlers.validate_request",
        file_path="api/handlers.py",
        line_start=42,
        line_end=89,
        language="python",
        parent_name="handlers",
        params="request, schema",
        return_type="ValidationResult",
        modifiers="async",
        is_test=False,
        extra={"decorators": ["@require_auth"]},
    )
    
    # Persist with upsert semantics

    node_id = store.upsert_node(handler)
    
    # Create calling relationship

    call = EdgeInfo(
        kind="CALLS",
        source="api.routes.submit",
        target="api.handlers.validate_request",
        file_path="api/routes.py",
        line=156,
        extra={"context": "POST /submit endpoint"},
    )
    
    edge_id = store.upsert_edge(call)
    store.commit()

The NodeInfo and EdgeInfo dataclasses are defined in code_review_graph/parser.py, providing type-safe construction before storage.

Summary

• Single-file architecture: The entire SQLite graph storage schema lives in graph.db, making backup and transport trivial.
• Three core tables: nodes (entities), edges (relationships), and metadata (configuration) with strategic indices for graph traversal.
• Conflict-free updates: Both node and edge persistence use upsert patterns—SQLite's native ON CONFLICT for nodes, application-level select-then-modify for edges.
• Migration safety: Nine schema versions extend functionality incrementally; v5 adds FTS5 search, v6 adds risk analysis tables.
• Source locations: All persistence logic resides in code_review_graph/graph.py (schema, upserts) and code_review_graph/migrations.py (evolution).

Frequently Asked Questions

What is the primary key for nodes in the code-review-graph SQLite schema?

The qualified_name column serves as the unique identifier and implicit primary key for nodes. This design choice enables the ON CONFLICT(qualified_name) DO UPDATE upsert pattern, ensuring that re-parsing the same code element updates metadata rather than creating duplicates.

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

The repository implements a linear migration system in code_review_graph/migrations.py. Each version (v1 through v9) adds tables or columns through pure SQL DDL executed transactionally. The current version is stored in the metadata table, and GraphStore.__init__ automatically applies pending migrations on startup.

Why do edges use a different upsert pattern than nodes?

Edges lack a single natural unique key, so the system queries by compound criteria—(kind, source_qualified, target_qualified, file_path, line)—then decides between UPDATE or INSERT. This supports incremental confidence refinement: the same call discovered by different analyzers updates the existing edge rather than spawning duplicates.

Where is full-text search implemented in the schema?

Version 5 migration (_migrate_v5 at lines 49-57) creates an FTS5 virtual table named nodes_fts. This enables fast text search over node names, qualified names, and documentation without modifying the core nodes table structure.

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