SQLite Graph Schema in code-review-graph: How Nodes and Edges Are Stored

The tirth8205/code-review-graph project implements its knowledge graph using a SQLite database with two primary tables—nodes and edges—defined in docs/schema.md and automatically created by the Graph class, where nodes represent code symbols identified by fully-qualified names and edges store typed relationships between them.

The SQLite graph schema provides a lightweight, file-based storage solution for code analysis that eliminates external database dependencies. This schema captures source code entities as vertices in a graph while preserving their hierarchical and dependency relationships through structured edge records. The implementation relies on standard SQLite features with row_factory = sqlite3.Row for Pythonic column access, making it accessible for local code review tooling.

Nodes and Edges: The Core Schema Structure

The graph database centers on two tables that store the complete knowledge representation of analyzed codebases.

Nodes Table: Storing Code Entities

The nodes table acts as the vertex store for all code symbols, with each row representing a distinct entity such as a file, class, function, or test. According to the schema definition in docs/schema.md (lines 52-73), the table structure includes:

Column	Type	Purpose
id	INTEGER PK AUTOINCREMENT	Internal surrogate key
kind	TEXT NOT NULL	Entity classification (File, Class, Function, Test, Type)
name	TEXT NOT NULL	Short identifier of the symbol
qualified_name	TEXT NOT NULL UNIQUE	Globally unique identifier used for lookups and edge connections
file_path	TEXT NOT NULL	Absolute path to the source file
line_start / line_end	INTEGER	Definition span in the source file
language	TEXT	Detected programming language
parent_name	TEXT	Enclosing class or module for nested symbols
params	TEXT	Function parameter list as source text
return_type	TEXT	Return type annotation
modifiers	TEXT	Access modifiers (public, abstract, etc.)
is_test	INTEGER DEFAULT 0	Boolean flag (1=test entity)
file_hash	TEXT	SHA-256 hash for change detection
extra	TEXT DEFAULT '{}'	JSON metadata bag for extensibility
community_id	INTEGER	Optional FK to clustering data
updated_at	REAL NOT NULL	Unix timestamp of last modification

The qualified_name column serves as the canonical identifier for graph operations, typically formatted as /absolute/path/to/file.py::symbol_name.

Edges Table: Storing Relationships

The edges table implements the graph connections using a property graph model where relationships carry semantic meaning. As defined in docs/schema.md (lines 74-86), edges connect nodes through their qualified names rather than internal IDs:

Column	Type	Purpose
id	INTEGER PK AUTOINCREMENT	Internal row identifier
kind	TEXT NOT NULL	Relationship type (CALLS, IMPORTS_FROM, INHERITS, etc.)
source_qualified	TEXT NOT NULL	Qualified name of the origin node
target_qualified	TEXT NOT NULL	Qualified name of the destination node
file_path	TEXT NOT NULL	Location where the relationship was observed
line	INTEGER DEFAULT 0	Source line of the relationship occurrence
extra	TEXT DEFAULT '{}'	JSON metadata for edge-specific data
confidence	REAL DEFAULT 1.0	Weight for probabilistic or inferred relationships
confidence_tier	TEXT DEFAULT 'EXTRACTED'	Human-readable confidence classification
updated_at	REAL NOT NULL	Unix timestamp of last update

This schema supports directed edges with optional probabilistic weighting, enabling both extracted static analysis facts and inferred relationships to coexist in the same graph.

Schema Initialization and the Graph Class

The Graph class in code_review_graph/graph.py materializes this schema automatically when opening a new database connection. On instantiation, the class executes the Data Definition Language (DDL) statements to create the nodes and edges tables if they do not exist (lines 42-61).

The initialization process also sets up auxiliary structures including metadata, flows, and communities tables, along with full-text search virtual tables for advanced querying capabilities. Schema versioning is handled through code_review_graph/migrations.py, which manages incremental updates while preserving existing graph data.

Working with the Graph: Code Examples

The following Python code demonstrates how to interact with the SQLite graph schema using standard sqlite3 operations:

import sqlite3
from pathlib import Path

# Open (or create) the graph database

db_path = Path("my_repo/graph.db")
conn = sqlite3.connect(db_path)
conn.row_factory = sqlite3.Row

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

# Insert a new node (a Python function)

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

node_sql = """
INSERT INTO nodes (kind, name, qualified_name, file_path,
                  line_start, line_end, language, is_test, updated_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, strftime('%s','now'))
"""
conn.execute(
    node_sql,
    (
        "Function",
        "process_data",
        "/absolute/path/to/module.py::process_data",
        "/absolute/path/to/module.py",
        10, 25, "python", 0,
    ),
)
conn.commit()

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

# Insert a CALLS edge from `process_data` to `helper`

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

edge_sql = """
INSERT INTO edges (kind, source_qualified, target_qualified,
                   file_path, line, confidence, updated_at)
VALUES (?, ?, ?, ?, ?, ?, strftime('%s','now'))
"""
conn.execute(
    edge_sql,
    (
        "CALLS",
        "/absolute/path/to/module.py::process_data",
        "/absolute/path/to/utils.py::helper",
        "/absolute/path/to/module.py",
        15,            # line where the call occurs

        1.0,           # full confidence

    ),
)
conn.commit()

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

# Query nodes and their outgoing CALLS edges

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

for node in conn.execute(
    "SELECT qualified_name FROM nodes WHERE kind='Function'"
):
    print("Function:", node["qualified_name"])
    for edge in conn.execute(
        """
        SELECT target_qualified, line FROM edges
        WHERE kind='CALLS' AND source_qualified = ?
        """,
        (node["qualified_name"],),
    ):
        print(f"  calls {edge['target_qualified']} at line {edge['line']}")

This example shows the direct use of qualified_name as the join key between nodes and edges, illustrating the schema's property graph design where relationships reference entities by their stable identifiers rather than ephemeral row IDs.

Key Implementation Files

The SQLite graph schema implementation spans several critical files in the repository:

• docs/schema.md: Contains the canonical DDL statements and human-readable documentation for the nodes and edges tables
• code_review_graph/graph.py: Implements the Graph class that orchestrates database creation, connection management, and query interfaces
• code_review_graph/migrations.py: Handles schema versioning and incremental table modifications
• code_review_graph/search.py: Demonstrates consumption patterns for graph queries supporting code search operations

Summary

• The SQLite graph schema uses two core tables (nodes and edges) to implement a property graph model in a local database file
• Nodes store code entities with rich metadata including qualified_name, kind, location, and language-specific attributes
• Edges represent typed relationships (CALLS, IMPORTS_FROM, INHERITS) connecting source and target qualified names with optional confidence weighting
• The Graph class in code_review_graph/graph.py automatically initializes the schema and maintains the database through migrations
• Both tables use Unix timestamps for updated_at and JSON columns for extensible metadata storage

Frequently Asked Questions

What is the primary key strategy for nodes and edges in the SQLite graph schema?

Both tables use auto-incrementing INTEGER primary keys (id) as internal row identifiers. However, graph operations rely on the qualified_name column in nodes and the source_qualified/target_qualified columns in edges for entity identification and relationship traversal, enabling stable references that survive database rebuilds.

How does the schema handle different programming languages?

The nodes table includes a language TEXT column that stores the detected programming language for each symbol. This allows the graph to mix entities from Python, JavaScript, TypeScript, or other supported languages while maintaining language-specific metadata in the extra JSON column.

Can the graph store inferred or uncertain relationships?

Yes, the edges table includes confidence (REAL, default 1.0) and confidence_tier (TEXT, default 'EXTRACTED') columns specifically for this purpose. Static analysis tools can insert edges with reduced confidence scores or categories like 'INFERRED' to distinguish probable relationships from directly observed ones.

Where is the SQLite graph schema defined in the source code?

The canonical schema definition lives in docs/schema.md at lines 52-86, while the programmatic creation logic resides in code_review_graph/graph.py within the Graph class initialization method (lines 42-61).

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