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

> Discover the SQLite graph schema for nodes and edges storage in code-review-graph. Learn how this project uses two core tables for efficient knowledge graph persistence.

- Repository: [Tirth Kanani/code-review-graph](https://github.com/tirth8205/code-review-graph)
- Tags: api-reference
- Published: 2026-07-20

---

**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`](https://github.com/tirth8205/code-review-graph/blob/main/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`](https://github.com/tirth8205/code-review-graph/blob/main/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`](https://github.com/tirth8205/code-review-graph/blob/main/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`](https://github.com/tirth8205/code-review-graph/blob/main/code_review_graph/graph.py) (lines 42-61) automatically runs the table creation logic. For existing databases, [`code_review_graph/migrations.py`](https://github.com/tirth8205/code-review-graph/blob/main/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:

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

```

## 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`](https://github.com/tirth8205/code-review-graph/blob/main/docs/schema.md), while [`code_review_graph/graph.py`](https://github.com/tirth8205/code-review-graph/blob/main/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`](https://github.com/tirth8205/code-review-graph/blob/main/docs/schema.md), but the tables are created programmatically by the **`Graph`** class in [`code_review_graph/graph.py`](https://github.com/tirth8205/code-review-graph/blob/main/code_review_graph/graph.py) (lines 42-61) upon first database open. Schema versioning and migrations are handled by [`code_review_graph/migrations.py`](https://github.com/tirth8205/code-review-graph/blob/main/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.