SQLite Graph Schema for Nodes and Edges Storage in Code-Review-Graph

The code-review-graph project persists its entire knowledge graph in a single SQLite database using two core tables—nodes and edges—defined in docs/schema.md and materialized automatically by the Graph class upon database initialization.

The SQLite graph schema for nodes and edges storage provides a lightweight, portable foundation for analyzing code relationships without external database dependencies. All symbol metadata and dependency links live in standard SQLite tables, enabling direct SQL queries and seamless integration with Python's sqlite3 module. The schema is designed to capture source provenance, type information, and confidence scores for probabilistic analyses.

Nodes Table Schema

The nodes table acts as the vertex store for every code symbol extracted from the repository. According to docs/schema.md (lines 52-73), the table schema is:

Column	Type	Description
id	INTEGER PK AUTOINCREMENT	Internal row identifier
kind	TEXT NOT NULL	Node classification (File, Class, Function, Test, Type, etc.)
name	TEXT NOT NULL	Short identifier of the symbol
qualified_name	TEXT NOT NULL UNIQUE	Fully-qualified path used as the canonical lookup key
file_path	TEXT NOT NULL	Absolute path to the source file
line_start	INTEGER	First line of the definition
line_end	INTEGER	Last line of the definition
language	TEXT	Detected programming language
parent_name	TEXT	Enclosing class or module (if applicable)
params	TEXT	Parameter list as raw source text
return_type	TEXT	Return-type annotation
modifiers	TEXT	Access modifiers (public, abstract, etc.)
is_test	INTEGER DEFAULT 0	Flag set to 1 for test definitions
file_hash	TEXT	SHA-256 checksum of the file contents for change detection
extra	TEXT DEFAULT '{}'	JSON metadata bag for extensibility
community_id	INTEGER	Optional foreign key to the communities table
updated_at	REAL NOT NULL	Unix timestamp of the last update

The qualified_name column serves as the stable external identifier, while file_hash enables efficient incremental re-indexing by detecting when source files have changed.

Edges Table Schema

The edges table captures relationships between nodes, implementing the graph structure. Defined in docs/schema.md (lines 74-86), the schema includes:

Column	Type	Description
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	File where the relationship was observed
line	INTEGER DEFAULT 0	Source line number of the relationship
extra	TEXT DEFAULT '{}'	JSON bag for edge-specific annotations
confidence	REAL DEFAULT 1.0	Numerical weight for probabilistic analyses
confidence_tier	TEXT DEFAULT 'EXTRACTED'	Human-readable confidence category
updated_at	REAL NOT NULL	Unix timestamp of the last update

Edges use logical foreign keys via source_qualified and target_qualified rather than integer references, allowing the graph to remain coherent even if nodes are re-indexed with new internal id values.

Schema Initialization and Migration

The DDL is not executed manually. When opening a database, the Graph class in code_review_graph/graph.py (lines 42-61) automatically runs the table creation logic. For existing databases, code_review_graph/migrations.py manages schema versioning, applying incremental alterations to the nodes and edges tables without requiring data export.

Both tables are accessed using row_factory = sqlite3.Row, enabling dictionary-style column access in Python queries.

Querying Nodes and Edges

The following example demonstrates inserting symbols and relationships, then traversing the graph:

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']}")

Summary

• The SQLite graph schema centers on two tables: nodes for code symbols and edges for their relationships.
• Schema definitions reside in docs/schema.md, while code_review_graph/graph.py handles automatic DDL execution.
• qualified_name serves as the stable identifier for nodes, with file_hash enabling change detection.
• Both tables support extensible metadata via JSON extra columns and track provenance through file_path and line columns.
• The implementation leverages sqlite3.Row for efficient Pythonic access to column data.

Frequently Asked Questions

What is the primary key for nodes and edges?

Both tables use an id column defined as INTEGER PRIMARY KEY AUTOINCREMENT. For the nodes table, qualified_name is additionally constrained as UNIQUE, providing a stable external identifier for graph operations. The edges table references these nodes via source_qualified and target_qualified rather than integer foreign keys.

How does the schema detect source code changes?

The nodes table stores SHA-256 checksums in the file_hash column, allowing the Graph class to identify exactly which files have been modified. The updated_at Unix timestamp column in both tables tracks when rows were last refreshed, supporting incremental re-indexing workflows without full rebuilds.

Where is the DDL defined and executed?

The complete SQLite graph schema specification lives in docs/schema.md, but the tables are created programmatically by the Graph class in code_review_graph/graph.py (lines 42-61) upon first database open. Schema versioning and migrations are handled by code_review_graph/migrations.py to ensure backward compatibility.

Can I store custom metadata in nodes and edges?

Yes. Both tables include an extra column of type TEXT with a default value of '{}'. This JSON bag stores arbitrary tool-specific metadata, enabling extensions to annotate symbols and relationships with domain-specific attributes without altering the core table structure.

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