# SQLite Graph Storage Schema in code-review-graph: Node and Edge Persistence Explained

> Explore the SQLite graph storage schema in code-review-graph. Understand node and edge persistence using three core tables and versioned migrations for enhanced features.

- Repository: [Tirth Kanani/code-review-graph](https://github.com/tirth8205/code-review-graph)
- Tags: internals
- Published: 2026-08-14

---

**The code-review-graph project stores its entire knowledge graph in a single SQLite database (`graph.db`) using a core schema of three tables—nodes, edges, and metadata—plus versioned migrations that add features like full-text search and risk indexing.**

The `tirth8205/code-review-graph` repository implements a durable, queryable storage layer for code analysis data. This article examines exactly how the SQLite graph storage schema works, how nodes and edges define their relationships, and how the system evolves through migrations without breaking existing data.

## Core Schema: Three Tables and Strategic Indices

The foundational schema is created by `GraphStore._init_schema()` in **[`code_review_graph/graph.py`](https://github.com/tirth8205/code-review-graph/blob/main/code_review_graph/graph.py)** (lines 61-104). It consists of three permanent tables designed for fast lookups and conflict-free upserts.

### The nodes Table

The `nodes` table stores every code element discovered during analysis—files, classes, functions, types, and tests. Its 16 columns capture location, structure, and metadata:

- **`qualified_name`** – UNIQUE primary lookup key (e.g., `mypackage.module.Class.method`)
- **`kind`** – Node type: `file`, `class`, `function`, `type`, `test`
- **`name`** – Short identifier without namespace
- **`file_path`**, **`line_start`**, **`line_end`** – Source location
- **`language`** – Detected programming language
- **`parent_name`** – Containing scope for nested elements
- **`params`**, **`return_type`**, **`modifiers`** – Function signatures
- **`is_test`** – Boolean flag for test detection
- **`file_hash`** – Content hash for invalidation
- **`extra`** – JSON blob for extensibility
- **`updated_at`** – Timestamp for cache invalidation

### The edges Table

The `edges` table stores directed relationships between nodes using their `qualified_name` values as foreign keys:

- **`source_qualified`** – Origin node identifier
- **`target_qualified`** – Destination node identifier
- **`kind`** – Relationship type: `CALLS`, `IMPORTS_FROM`, `INHERITS`, `CONTAINS`, etc.
- **`file_path`**, **`line`** – Where the relationship was observed
- **`confidence`** – Numerical confidence score (0.0-1.0)
- **`confidence_tier`** – Categorical label: `EXTRACTED`, `INFERRED`, `HEURISTIC`
- **`extra`** – JSON metadata
- **`updated_at`** – Timestamp

The `metadata` table holds singleton configuration, most importantly the current schema version used by the migration system.

### Index Strategy

Multiple indices accelerate graph traversal queries:

- `qualified_name` on `nodes` – Primary node lookup
- `source_qualified` and `target_qualified` on `edges` – Forward and reverse traversal
- Composite indices on `(target_qualified, kind)` and `(source_qualified, kind)` – Filtered neighbor queries

## Schema Evolution: Versioned Migrations

The schema is not static. **[`code_review_graph/migrations.py`](https://github.com/tirth8205/code-review-graph/blob/main/code_review_graph/migrations.py)** implements a linear migration chain from v1 to v9, each adding capabilities without breaking existing queries.

| Version | Change | Purpose |
|--------|--------|---------|
| v2 | `signature` column on `nodes` | Store computed function signatures |
| v3 | `flows` and `flow_memberships` tables | Data flow analysis |
| v4 | `communities` table, `community_id` on `nodes` | Community detection results |
| v5 | **FTS5** virtual table `nodes_fts` | Full-text search over code |
| v6 | `community_summaries`, `flow_snapshots`, `risk_index`, `risk_score` index | Risk analysis and reporting |
| v7 | Compound indices on `edges` | Query performance |
| v8 | Composite index for upsert performance | Bulk insertion speed |
| v9 | `confidence` and `confidence_tier` on `edges` | Relationship quality tracking |

Migration functions like `_migrate_v5()` (lines 49-57) execute pure SQL DDL. The `MIGRATIONS` dictionary (lines 45-54) maps version numbers to functions. On initialization, `GraphStore.__init__` calls `run_migrations(self._conn)` to apply pending upgrades automatically.

## Node and Edge Persistence: Upsert Semantics

The `GraphStore` class provides atomic upsert operations that translate between Python data classes and SQLite, handling both insertion and update in single transactions.

### Node Upsert: INSERT ... ON CONFLICT

```python

# From GraphStore.upsert_node() in graph.py lines 18-50

self._conn.execute(
    """INSERT INTO nodes
       (kind, name, qualified_name, file_path, line_start, line_end,
        language, parent_name, params, return_type, modifiers, is_test,
        file_hash, extra, updated_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
       ON CONFLICT(qualified_name) DO UPDATE SET
         kind=excluded.kind,
         name=excluded.name,
         file_path=excluded.file_path,
         line_start=excluded.line_start,
         line_end=excluded.line_end,
         language=excluded.language,
         parent_name=excluded.parent_name,
         params=excluded.params,
         return_type=excluded.return_type,
         modifiers=excluded.modifiers,
         is_test=excluded.is_test,
         file_hash=excluded.file_hash,
         extra=excluded.extra,
         updated_at=excluded.updated_at
    """,
    (node.kind, node.name, qualified, node.file_path,
     node.line_start, node.line_end, node.language,
     node.parent_name, node.params, node.return_type,
     node.modifiers, int(node.is_test), file_hash,
     extra, now)
)

```

The **SQLite UPSERT** pattern (`ON CONFLICT ... DO UPDATE`) ensures idempotent writes: re-parsing the same file updates existing nodes rather than creating duplicates. The `excluded.*` syntax references the attempted insert values.

### Edge Upsert: Select-Then-Modify Pattern

```python

# From GraphStore.upsert_edge() in graph.py lines 52-84

existing = self._conn.execute(
    """SELECT id FROM edges
       WHERE kind=? AND source_qualified=? AND target_qualified=?
         AND file_path=? AND line=?""",
    (edge.kind, edge.source, edge.target, edge.file_path, edge.line)
).fetchone()

if existing:
    self._conn.execute(
        "UPDATE edges SET line=?, extra=?, confidence=?, confidence_tier=?,"
        " updated_at=? WHERE id=?",
        (edge.line, extra, confidence, confidence_tier, now, existing["id"])
    )
else:
    self._conn.execute(
        """INSERT INTO edges
           (kind, source_qualified, target_qualified, file_path, line,
            extra, confidence, confidence_tier, updated_at)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)""",
        (edge.kind, edge.source, edge.target, edge.file_path, edge.line,
         extra, confidence, confidence_tier, now)
    )

```

Edges use a **query-then-decide** pattern because they lack a single natural unique key. The composite match on `(kind, source_qualified, target_qualified, file_path, line)` identifies "the same relationship observed at the same location." This allows incremental updates to confidence scores as analysis improves.

## Practical Usage: Creating Nodes and Edges

The storage layer is accessed through `GraphStore` context managers:

```python
from code_review_graph.graph import GraphStore
from code_review_graph.parser import NodeInfo, EdgeInfo

with GraphStore("project/graph.db") as store:
    # Define a function node

    handler = NodeInfo(
        kind="function",
        name="validate_request",
        qualified_name="api.handlers.validate_request",
        file_path="api/handlers.py",
        line_start=42,
        line_end=89,
        language="python",
        parent_name="handlers",
        params="request, schema",
        return_type="ValidationResult",
        modifiers="async",
        is_test=False,
        extra={"decorators": ["@require_auth"]},
    )
    
    # Persist with upsert semantics

    node_id = store.upsert_node(handler)
    
    # Create calling relationship

    call = EdgeInfo(
        kind="CALLS",
        source="api.routes.submit",
        target="api.handlers.validate_request",
        file_path="api/routes.py",
        line=156,
        extra={"context": "POST /submit endpoint"},
    )
    
    edge_id = store.upsert_edge(call)
    store.commit()

```

The `NodeInfo` and `EdgeInfo` dataclasses are defined in **[`code_review_graph/parser.py`](https://github.com/tirth8205/code-review-graph/blob/main/code_review_graph/parser.py)**, providing type-safe construction before storage.

## Summary

- **Single-file architecture**: The entire SQLite graph storage schema lives in `graph.db`, making backup and transport trivial.
- **Three core tables**: `nodes` (entities), `edges` (relationships), and `metadata` (configuration) with strategic indices for graph traversal.
- **Conflict-free updates**: Both node and edge persistence use upsert patterns—SQLite's native `ON CONFLICT` for nodes, application-level select-then-modify for edges.
- **Migration safety**: Nine schema versions extend functionality incrementally; v5 adds FTS5 search, v6 adds risk analysis tables.
- **Source locations**: All persistence logic resides in [`code_review_graph/graph.py`](https://github.com/tirth8205/code-review-graph/blob/main/code_review_graph/graph.py) (schema, upserts) and [`code_review_graph/migrations.py`](https://github.com/tirth8205/code-review-graph/blob/main/code_review_graph/migrations.py) (evolution).

## Frequently Asked Questions

### What is the primary key for nodes in the code-review-graph SQLite schema?

The `qualified_name` column serves as the unique identifier and implicit primary key for nodes. This design choice enables the `ON CONFLICT(qualified_name) DO UPDATE` upsert pattern, ensuring that re-parsing the same code element updates metadata rather than creating duplicates.

### How does code-review-graph handle schema changes without data loss?

The repository implements a linear migration system in [`code_review_graph/migrations.py`](https://github.com/tirth8205/code-review-graph/blob/main/code_review_graph/migrations.py). Each version (v1 through v9) adds tables or columns through pure SQL DDL executed transactionally. The current version is stored in the `metadata` table, and `GraphStore.__init__` automatically applies pending migrations on startup.

### Why do edges use a different upsert pattern than nodes?

Edges lack a single natural unique key, so the system queries by compound criteria—`(kind, source_qualified, target_qualified, file_path, line)`—then decides between `UPDATE` or `INSERT`. This supports incremental confidence refinement: the same call discovered by different analyzers updates the existing edge rather than spawning duplicates.

### Where is full-text search implemented in the schema?

Version 5 migration (`_migrate_v5` at lines 49-57) creates an FTS5 virtual table named `nodes_fts`. This enables fast text search over node names, qualified names, and documentation without modifying the core `nodes` table structure.