SQLite Schema for Nodes and Edges in the code-review-graph Knowledge Graph

The code-review-graph project stores its knowledge graph in a SQLite database where the nodes and edges tables are defined by the _SCHEMA_SQL string in code_review_graph/graph.py, tracking code elements like functions and classes alongside their relationships.

The tirth8205/code-review-graph repository persists code analysis data using a structured SQLite backend. This article examines the exact SQLite schema for nodes and edges that powers the knowledge graph, detailing table structures, indexes, and Python interactions as implemented in the source code.

The Schema Definition in graph.py

The complete database schema resides in code_review_graph/graph.py inside a module-level string constant named _SCHEMA_SQL. This definition spans lines 42–73 and establishes the core tables that store the graph structure.

nodes Table Structure

The nodes table stores one row per code element, including files, classes, functions, types, and tests. According to the source, the table creation SQL includes:

CREATE TABLE IF NOT EXISTS nodes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    kind TEXT NOT NULL,          -- File, Class, Function, Type, Test
    name TEXT NOT NULL,
    qualified_name TEXT NOT NULL UNIQUE,
    file_path TEXT NOT NULL,
    line_start INTEGER,
    line_end INTEGER,
    language TEXT,
    parent_name TEXT,
    params TEXT,
    return_type TEXT,
    modifiers TEXT,
    is_test INTEGER DEFAULT 0,
    file_hash TEXT,
    extra TEXT DEFAULT '{}',
    updated_at REAL NOT NULL
);

Key columns include:

• qualified_name – A unique identifier for the node (e.g., my_pkg.utils.process_data).
• kind – The entity type: File, Class, Function, Type, or Test.
• params and return_type – Function signatures captured as text.
• file_hash – Enables change detection when source files are modified.
• extra – A JSON text field ('{}') for extensible metadata storage.
• updated_at – Stored as a REAL timestamp for versioning.

edges Table Structure

The edges table records relationships between nodes using the following schema:

CREATE TABLE IF NOT EXISTS edges (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    kind TEXT NOT NULL,           -- CALLS, IMPORTS_FROM, INHERITS, REFERENCES, etc.
    source_qualified TEXT NOT NULL,
    target_qualified TEXT NOT NULL,
    file_path TEXT NOT NULL,
    line INTEGER DEFAULT 0,
    extra TEXT DEFAULT '{}',
    confidence REAL DEFAULT 1.0,
    confidence_tier TEXT DEFAULT 'EXTRACTED',
    updated_at REAL NOT NULL
);

Relationship types include CALLS, IMPORTS_FROM, INHERITS, REFERENCES, CONTAINS, TESTED_BY, and DEPENDS_ON. The confidence and confidence_tier columns support probabilistic graph data, with EXTRACTED as the default tier for statically analyzed relationships.

Indexes for Query Performance

The schema includes strategic indexes to accelerate graph traversal and impact-radius queries:

• nodes table indexes: file_path, kind, and qualified_name are indexed to support fast lookups of entities by location or qualified identifier.
• edges table indexes: source_qualified, target_qualified, kind, and file_path are indexed to optimize relationship traversal and file-scoped queries.

These indexes ensure efficient navigation when querying call graphs or dependency chains in large codebases.

Working with the Schema in Python

The GraphStore class in code_review_graph/graph.py provides Pythonic methods for interacting with this schema. The following example demonstrates inserting nodes and edges using the NodeInfo and EdgeInfo dataclasses defined in code_review_graph/parser.py:

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

# Initialise the SQLite-backed graph store

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

# ----------------------------------------------------------------------

# Insert / update a node (e.g., a Python function)

# ----------------------------------------------------------------------

node = NodeInfo(
    kind="Function",
    name="process_data",
    qualified_name="my_pkg.utils.process_data",
    file_path="my_pkg/utils.py",
    line_start=10,
    line_end=25,
    language="python",
    parent_name=None,
    params="data: pd.DataFrame",
    return_type="pd.DataFrame",
    is_test=False,
    extra={},               # any additional metadata

)
node_id = store.upsert_node(node, file_hash="abcd1234")
print(f"Node ID: {node_id}")

# ----------------------------------------------------------------------

# Insert / update an edge (e.g., function CALLS another function)

# ----------------------------------------------------------------------

edge = EdgeInfo(
    kind="CALLS",
    source_qualified="my_pkg.main.run",
    target_qualified="my_pkg.utils.process_data",
    file_path="my_pkg/main.py",
    line=42,
    extra={},               # optional extra data

    confidence=0.95,
    confidence_tier="EXTRACTED",
)
edge_id = store.upsert_edge(edge)
print(f"Edge ID: {edge_id}")

# ----------------------------------------------------------------------

# Querying – get all nodes of a given kind

# ----------------------------------------------------------------------

functions = store.query_nodes(kind="Function")
for fn in functions:
    print(fn.qualified_name)

store.close()

The upsert_node and upsert_edge methods handle the underlying SQL insertion logic, including conflict resolution for existing records based on unique constraints like qualified_name in the nodes table.

Key Schema Files

• code_review_graph/graph.py – Declares the SQLite schema (nodes, edges, metadata) and implements GraphStore with read/write helpers.
• code_review_graph/migrations.py – Manages schema migrations and versioning.
• code_review_graph/parser.py – Defines the NodeInfo and EdgeInfo dataclasses used when inserting data into the graph.

Summary

• The SQLite schema for nodes and edges in the code-review-graph project is defined in the _SCHEMA_SQL string located in code_review_graph/graph.py.
• The nodes table stores code entities with metadata like qualified_name, kind, params, and file_hash, supporting file-level change detection.
• The edges table captures relationships (CALLS, IMPORTS_FROM, etc.) with confidence scoring and references to source and target qualified names.
• Indexed columns on both tables optimize query performance for graph traversal and file-scoped analysis.
• The GraphStore class provides Python wrappers for schema operations, utilizing NodeInfo and EdgeInfo structures from parser.py.

Frequently Asked Questions

What is the purpose of the qualified_name column?

The qualified_name column serves as a unique identifier for nodes, typically representing the fully qualified path to a code element (e.g., package.module.ClassName.method). In the nodes table, it has a UNIQUE constraint preventing duplicate entries, while the edges table uses source_qualified and target_qualified to establish relationships without foreign key constraints, enabling flexible graph queries across the SQLite schema.

How does the confidence system work in the edges table?

Each edge includes a confidence REAL value (default 1.0) and a confidence_tier TEXT field (default "EXTRACTED"). This system allows the knowledge graph to represent uncertain or inferred relationships, with lower confidence scores indicating heuristic or AI-generated connections versus statically extracted facts, supporting incremental refinement of the graph over time.

Is there a metadata table in addition to nodes and edges?

Yes, the schema includes a metadata table defined alongside the nodes and edges tables in _SCHEMA_SQL. This table stores three columns (key-value pairs with versioning) for global graph properties and configuration data, though the primary graph structure relies on the nodes and edges tables for code relationships.

How are schema migrations handled when the database structure changes?

Schema migrations are managed in code_review_graph/migrations.py, which tracks the current schema version and applies incremental changes to existing SQLite databases. This ensures that knowledge graph files created with earlier versions of the code-review-graph tool can be upgraded without data loss when the underlying _SCHEMA_SQL definition is modified.

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