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

> Explore the SQLite graph schema in code-review-graph. Learn how nodes and edges representing code symbols and relationships are stored in tables for efficient graph management.

- Repository: [Tirth Kanani/code-review-graph](https://github.com/tirth8205/code-review-graph)
- Tags: how-to-guide
- Published: 2026-07-30

---

**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`](https://github.com/tirth8205/code-review-graph/blob/main/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`](https://github.com/tirth8205/code-review-graph/blob/main/docs/schema.md) (lines [52-73](https://github.com/tirth8205/code-review-graph/blob/main/docs/schema.md#L52-L73)), 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`](https://github.com/tirth8205/code-review-graph/blob/main/docs/schema.md) (lines [74-86](https://github.com/tirth8205/code-review-graph/blob/main/docs/schema.md#L74-L86)), 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`](https://github.com/tirth8205/code-review-graph/blob/main/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](https://github.com/tirth8205/code-review-graph/blob/main/code_review_graph/graph.py#L42-L61)).

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`](https://github.com/tirth8205/code-review-graph/blob/main/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:

```python
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`](https://github.com/tirth8205/code-review-graph/blob/main/docs/schema.md)**: Contains the canonical DDL statements and human-readable documentation for the `nodes` and `edges` tables
- **[`code_review_graph/graph.py`](https://github.com/tirth8205/code-review-graph/blob/main/code_review_graph/graph.py)**: Implements the `Graph` class that orchestrates database creation, connection management, and query interfaces
- **[`code_review_graph/migrations.py`](https://github.com/tirth8205/code-review-graph/blob/main/code_review_graph/migrations.py)**: Handles schema versioning and incremental table modifications
- **[`code_review_graph/search.py`](https://github.com/tirth8205/code-review-graph/blob/main/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`](https://github.com/tirth8205/code-review-graph/blob/main/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`](https://github.com/tirth8205/code-review-graph/blob/main/docs/schema.md) at lines [52-86](https://github.com/tirth8205/code-review-graph/blob/main/docs/schema.md#L52-L86), while the programmatic creation logic resides in [`code_review_graph/graph.py`](https://github.com/tirth8205/code-review-graph/blob/main/code_review_graph/graph.py) within the `Graph` class initialization method (lines [42-61](https://github.com/tirth8205/code-review-graph/blob/main/code_review_graph/graph.py#L42-L61)).