# SQLite Schema for Nodes and Edges in the code-review-graph Knowledge Graph

> Explore the SQLite schema for nodes and edges in the code-review-graph knowledge graph. Discover how it tracks code elements and their relationships within the graph.py file.

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

---

**The code-review-graph project stores its knowledge graph in a SQLite database where the `nodes` and `edges` tables are defined by the `_SCHEMA_SQL` string in [`code_review_graph/graph.py`](https://github.com/tirth8205/code-review-graph/blob/main/code_review_graph/graph.py), tracking code elements like functions and classes alongside their relationships.**

The tirth8205/code-review-graph repository persists code analysis data using a structured SQLite backend. This article examines the exact SQLite schema for nodes and edges that powers the knowledge graph, detailing table structures, indexes, and Python interactions as implemented in the source code.

## The Schema Definition in graph.py

The complete database schema resides in [`code_review_graph/graph.py`](https://github.com/tirth8205/code-review-graph/blob/main/code_review_graph/graph.py) inside a module-level string constant named `_SCHEMA_SQL`. This definition spans lines 42–73 and establishes the core tables that store the graph structure.

### nodes Table Structure

The `nodes` table stores one row per code element, including files, classes, functions, types, and tests. According to the source, the table creation SQL includes:

```sql
CREATE TABLE IF NOT EXISTS nodes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    kind TEXT NOT NULL,          -- File, Class, Function, Type, Test
    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 '{}',
    updated_at REAL NOT NULL
);

```

Key columns include:
- **qualified_name** – A unique identifier for the node (e.g., `my_pkg.utils.process_data`).
- **kind** – The entity type: `File`, `Class`, `Function`, `Type`, or `Test`.
- **params** and **return_type** – Function signatures captured as text.
- **file_hash** – Enables change detection when source files are modified.
- **extra** – A JSON text field (`'{}'`) for extensible metadata storage.
- **updated_at** – Stored as a REAL timestamp for versioning.

### edges Table Structure

The `edges` table records relationships between nodes using the following schema:

```sql
CREATE TABLE IF NOT EXISTS edges (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    kind TEXT NOT NULL,           -- CALLS, IMPORTS_FROM, INHERITS, REFERENCES, etc.
    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
);

```

Relationship types include **CALLS**, **IMPORTS_FROM**, **INHERITS**, **REFERENCES**, **CONTAINS**, **TESTED_BY**, and **DEPENDS_ON**. The `confidence` and `confidence_tier` columns support probabilistic graph data, with `EXTRACTED` as the default tier for statically analyzed relationships.

### Indexes for Query Performance

The schema includes strategic indexes to accelerate graph traversal and impact-radius queries:

- **nodes table indexes**: `file_path`, `kind`, and `qualified_name` are indexed to support fast lookups of entities by location or qualified identifier.
- **edges table indexes**: `source_qualified`, `target_qualified`, `kind`, and `file_path` are indexed to optimize relationship traversal and file-scoped queries.

These indexes ensure efficient navigation when querying call graphs or dependency chains in large codebases.

## Working with the Schema in Python

The `GraphStore` class in [`code_review_graph/graph.py`](https://github.com/tirth8205/code-review-graph/blob/main/code_review_graph/graph.py) provides Pythonic methods for interacting with this schema. The following example demonstrates inserting nodes and edges using the `NodeInfo` and `EdgeInfo` dataclasses defined in [`code_review_graph/parser.py`](https://github.com/tirth8205/code-review-graph/blob/main/code_review_graph/parser.py):

```python
from pathlib import Path
from code_review_graph.graph import GraphStore, NodeInfo, EdgeInfo

# Initialise the SQLite-backed graph store

store = GraphStore(Path("tmp/graph.db"))

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

# Insert / update a node (e.g., a Python function)

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

node = NodeInfo(
    kind="Function",
    name="process_data",
    qualified_name="my_pkg.utils.process_data",
    file_path="my_pkg/utils.py",
    line_start=10,
    line_end=25,
    language="python",
    parent_name=None,
    params="data: pd.DataFrame",
    return_type="pd.DataFrame",
    is_test=False,
    extra={},               # any additional metadata

)
node_id = store.upsert_node(node, file_hash="abcd1234")
print(f"Node ID: {node_id}")

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

# Insert / update an edge (e.g., function CALLS another function)

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

edge = EdgeInfo(
    kind="CALLS",
    source_qualified="my_pkg.main.run",
    target_qualified="my_pkg.utils.process_data",
    file_path="my_pkg/main.py",
    line=42,
    extra={},               # optional extra data

    confidence=0.95,
    confidence_tier="EXTRACTED",
)
edge_id = store.upsert_edge(edge)
print(f"Edge ID: {edge_id}")

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

# Querying – get all nodes of a given kind

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

functions = store.query_nodes(kind="Function")
for fn in functions:
    print(fn.qualified_name)

store.close()

```

The `upsert_node` and `upsert_edge` methods handle the underlying SQL insertion logic, including conflict resolution for existing records based on unique constraints like `qualified_name` in the nodes table.

## Key Schema Files

- **[`code_review_graph/graph.py`](https://github.com/tirth8205/code-review-graph/blob/main/code_review_graph/graph.py)** – Declares the SQLite schema (`nodes`, `edges`, `metadata`) and implements `GraphStore` with read/write helpers.
- **[`code_review_graph/migrations.py`](https://github.com/tirth8205/code-review-graph/blob/main/code_review_graph/migrations.py)** – Manages schema migrations and versioning.
- **[`code_review_graph/parser.py`](https://github.com/tirth8205/code-review-graph/blob/main/code_review_graph/parser.py)** – Defines the `NodeInfo` and `EdgeInfo` dataclasses used when inserting data into the graph.

## Summary

- The **SQLite schema for nodes and edges** in the code-review-graph project is defined in the `_SCHEMA_SQL` string located in [`code_review_graph/graph.py`](https://github.com/tirth8205/code-review-graph/blob/main/code_review_graph/graph.py).
- The **nodes table** stores code entities with metadata like `qualified_name`, `kind`, `params`, and `file_hash`, supporting file-level change detection.
- The **edges table** captures relationships (CALLS, IMPORTS_FROM, etc.) with confidence scoring and references to source and target qualified names.
- **Indexed columns** on both tables optimize query performance for graph traversal and file-scoped analysis.
- The **`GraphStore`** class provides Python wrappers for schema operations, utilizing `NodeInfo` and `EdgeInfo` structures from [`parser.py`](https://github.com/tirth8205/code-review-graph/blob/main/parser.py).

## Frequently Asked Questions

### What is the purpose of the qualified_name column?

The **`qualified_name`** column serves as a unique identifier for nodes, typically representing the fully qualified path to a code element (e.g., `package.module.ClassName.method`). In the `nodes` table, it has a `UNIQUE` constraint preventing duplicate entries, while the `edges` table uses `source_qualified` and `target_qualified` to establish relationships without foreign key constraints, enabling flexible graph queries across the SQLite schema.

### How does the confidence system work in the edges table?

Each edge includes a **`confidence`** REAL value (default `1.0`) and a **`confidence_tier`** TEXT field (default `"EXTRACTED"`). This system allows the knowledge graph to represent uncertain or inferred relationships, with lower confidence scores indicating heuristic or AI-generated connections versus statically extracted facts, supporting incremental refinement of the graph over time.

### Is there a metadata table in addition to nodes and edges?

Yes, the schema includes a **`metadata`** table defined alongside the nodes and edges tables in `_SCHEMA_SQL`. This table stores three columns (key-value pairs with versioning) for global graph properties and configuration data, though the primary graph structure relies on the nodes and edges tables for code relationships.

### How are schema migrations handled when the database structure changes?

Schema migrations are managed in **[`code_review_graph/migrations.py`](https://github.com/tirth8205/code-review-graph/blob/main/code_review_graph/migrations.py)**, which tracks the current schema version and applies incremental changes to existing SQLite databases. This ensures that knowledge graph files created with earlier versions of the code-review-graph tool can be upgraded without data loss when the underlying `_SCHEMA_SQL` definition is modified.