SQLite Schema for Storing Code Graph Nodes and Edges in code-review-graph

The code-review-graph project persists its knowledge graph in a local SQLite database using two core tables—nodes for code entities and edges for their relationships—which enables efficient storage and traversal of file dependencies, class hierarchies, and function calls.

The tirth8205/code-review-graph repository implements a code review knowledge graph that uses SQLite as its primary persistence layer. Understanding the SQLite schema for storing code graph nodes and edges is essential for extending the tool, writing custom queries, or integrating the graph data into external analysis pipelines.

Core Schema Architecture

The schema defines two principal tables that work together to model the codebase as a directed graph. According to the source code in docs/schema.md, these tables are created at runtime by code_review_graph/graph.py and maintained via code_review_graph/migrations.py.

The nodes Table

The nodes table stores every graph vertex representing code entities such as files, classes, functions, tests, and types. Each node is uniquely identified by its qualified_name column, which provides a stable reference across the codebase.

Key columns include:

• qualified_name – A unique text identifier (e.g., module.Class.method)
• kind – The entity type (File, Class, Function, etc.)
• file_path, line_start, line_end – Source location tracking
• parent_name – Qualified name of the containing entity
• is_test – Integer flag (0/1) distinguishing test code
• extra – JSON text field for extensible metadata
• community_id – For graph clustering algorithms

The complete SQL definition from docs/schema.md is:

CREATE TABLE nodes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    kind TEXT NOT NULL,
    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 '{}',
    community_id INTEGER,
    updated_at REAL NOT NULL
);

The edges Table

The edges table stores directed relationships between nodes, linking source_qualified to target_qualified. Common relationship types include CALLS, IMPORTS_FROM, and INHERITS.

Key columns include:

• source_qualified and target_qualified – Foreign key references to node qualified names
• kind – Edge type classification
• confidence and confidence_tier – Reliability scoring for extracted relationships
• line – Source line where the relationship occurs

The SQL definition is:

CREATE TABLE edges (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    kind TEXT NOT NULL,
    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
);

Python Implementation Examples

The code_review_graph/graph.py module provides the GraphStore API that interacts with these tables. Below are practical patterns for database initialization and data manipulation.

Creating the Database

Tables are created using standard SQLite execution of the schema definitions:

import sqlite3
import time

def init_db(path: str = "graph.db"):
    conn = sqlite3.connect(path)
    cur = conn.cursor()
    # Schema loaded from docs/schema.md (simplified extraction)

    cur.executescript("""
        CREATE TABLE IF NOT EXISTS nodes (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            kind TEXT NOT NULL,
            name TEXT NOT NULL,
            qualified_name TEXT NOT NULL UNIQUE,
            file_path TEXT NOT NULL,
            updated_at REAL NOT NULL
        );
        CREATE TABLE IF NOT EXISTS edges (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            kind TEXT NOT NULL,
            source_qualified TEXT NOT NULL,
            target_qualified TEXT NOT NULL,
            file_path TEXT NOT NULL,
            updated_at REAL NOT NULL
        );
    """)
    conn.commit()
    return conn

Inserting Nodes

When adding code entities, the qualified_name must be unique, and updated_at requires a timestamp:

def add_node(conn, kind, name, qualified_name, file_path, **extra):
    cur = conn.cursor()
    cur.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,
            community_id, updated_at
        ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
        """,
        (
            kind, name, qualified_name, file_path,
            extra.get("line_start"), extra.get("line_end"), extra.get("language"),
            extra.get("parent_name"), extra.get("params"), extra.get("return_type"),
            extra.get("modifiers"), int(extra.get("is_test", False)),
            extra.get("file_hash"), extra.get("extra", "{}"),
            extra.get("community_id"), time.time()
        ),
    )
    conn.commit()

Inserting Edges

Relationships link qualified names rather than integer IDs, allowing edges to reference nodes that may not yet exist in the database:

def add_edge(conn, kind, source_qn, target_qn, file_path, line=0, **extra):
    cur = conn.cursor()
    cur.execute(
        """
        INSERT INTO edges (
            kind, source_qualified, target_qualified,
            file_path, line, extra, confidence,
            confidence_tier, updated_at
        ) VALUES (?,?,?,?,?,?,?,?,?)
        """,
        (
            kind, source_qn, target_qn,
            file_path, line, extra.get("extra", "{}"),
            extra.get("confidence", 1.0),
            extra.get("confidence_tier", "EXTRACTED"),
            time.time(),
        ),
    )
    conn.commit()

Querying Relationships

To find all functions called by a specific method:

def get_calls(conn, function_qn):
    cur = conn.cursor()
    cur.execute(
        "SELECT target_qualified, line FROM edges WHERE kind='CALLS' AND source_qualified=?",
        (function_qn,),
    )
    return cur.fetchall()

Schema Management and Migrations

The project maintains schema integrity through three key components:

1. docs/schema.md – Contains the authoritative SQL definitions and serves as the reference documentation
2. code_review_graph/graph.py – Implements the GraphStore class that creates tables using CREATE TABLE IF NOT EXISTS statements and provides the high-level API for reading and writing nodes and edges
3. code_review_graph/migrations.py – Handles database migrations to update existing databases when the schema evolves across versions

The updated_at REAL timestamp field in both tables is automatically refreshed whenever records change, supporting incremental synchronization and cache invalidation strategies.

Summary

• The SQLite schema for storing code graph nodes and edges relies on two tables: nodes for code entities and edges for their relationships.
• Nodes are uniquely identified by qualified_name and store metadata including location, language, and hierarchical relationships via parent_name.
• Edges connect source_qualified to target_qualified with typed relationships (CALLS, IMPORTS_FROM, INHERITS) and confidence scoring.
• Schema definitions reside in docs/schema.md while runtime creation and API access are handled by code_review_graph/graph.py and migration logic in code_review_graph/migrations.py.

Frequently Asked Questions

What is the primary key strategy for the nodes and edges tables?

Both tables use an integer id column with PRIMARY KEY AUTOINCREMENT. However, the nodes table enforces a unique constraint on qualified_name, which serves as the stable external identifier used by edges to reference nodes.

How does the schema distinguish between different types of code entities?

The kind TEXT column in the nodes table stores entity classifications such as File, Class, Function, or Test. Similarly, the edges table uses its kind column to specify relationship types including CALLS, IMPORTS_FROM, and INHERITS.

Where is the schema defined and how does the application apply it?

The authoritative schema is documented in docs/schema.md. At runtime, code_review_graph/graph.py applies the schema using CREATE TABLE IF NOT EXISTS statements, while code_review_graph/migrations.py manages versioned upgrades for existing databases.

Can the schema store metadata for nodes and edges not covered by standard columns?

Yes, both tables include an extra column defined as TEXT with a default value of '{}'. This JSON field allows arbitrary extension of node and edge properties without requiring schema migrations.

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