# Graph Storage Schema in SQLite: How code-review-graph Persists Nodes and Edges

> Explore the graph storage schema in SQLite for code-review-graph. Learn how nodes and edges are persisted using three core tables and optimized upserts in graph.py.

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

---

**The code-review-graph project stores its entire knowledge graph in a single SQLite database (`graph.db`) using three core tables—nodes, edges, and metadata—plus migration-driven extensions for full-text search, communities, and flow tracking, with all writes performed through optimistic upserts in [`graph.py`](https://github.com/tirth8205/code-review-graph/blob/main/graph.py).**

The code-review-graph repository implements a durable **SQLite graph storage schema** to persist code analysis results across sessions. Rather than using an external graph database, the project leverages SQLite's built-in capabilities with a carefully designed relational schema that maps graph concepts to SQL tables while maintaining ACID compliance and enabling fast lookups via strategic indexing.

## Core Schema Architecture

The foundational schema is initialized 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). This creates the minimal table structure required to represent a code knowledge graph, with subsequent features added through a versioned migration system.

### The nodes Table

The **nodes** table stores every code element discovered during analysis—files, classes, functions, types, and tests. It includes seventeen columns designed to capture both identity and metadata:

- `id`: Primary key
- `kind`, `name`, `qualified_name`: Identity and type classification (UNIQUE constraint on qualified_name)
- `file_path`, `line_start`, `line_end`: Source location
- `language`, `parent_name`: Context and hierarchy
- `params`, `return_type`, `modifiers`: Callable signatures
- `is_test`, `file_hash`, `extra`: Testing flags and extensible metadata
- `updated_at`: Timestamp for cache invalidation

This structure allows the system to distinguish between overloaded methods via the **qualified_name** column, which serves as the natural unique identifier for upsert operations.

### The edges Table

The **edges** table represents relationships between code elements such as `CALLS`, `IMPORTS_FROM`, and `INHERITS`. Key columns include:

- `kind`: Relationship type
- `source_qualified` and `target_qualified`: Foreign key equivalents referencing node identities
- `file_path` and `line`: Callsite location
- `confidence` and `confidence_tier`: Data quality metrics added in migration v9
- `extra`: JSON extensibility
- `updated_at`: For incremental sync

Unlike traditional foreign keys, the schema uses `qualified_name` strings rather than integer IDs for linking, enabling nodes to be referenced before they exist in the database (eventual consistency model).

### Metadata and Indices

The **metadata** table stores key-value pairs including the current schema version, checked on startup to trigger migrations. Multiple indices accelerate graph traversals:

- Indices on `qualified_name` (nodes) and `source_qualified`/`target_qualified` (edges)
- Compound indices added in later migrations for `(target_qualified, kind)` and `(source_qualified, kind)` pairs

## Migration-Driven Schema Evolution

The schema evolves through nine versioned migrations defined in **[`code_review_graph/migrations.py`](https://github.com/tirth8205/code-review-graph/blob/main/code_review_graph/migrations.py)**. The `run_migrations(self._conn)` function checks the `metadata` table on initialization and applies incremental DDL changes:

| Version | Addition |
|---------|----------|
| **v2** | `signature` column to `nodes` |
| **v3** | `flows` and `flow_memberships` tables for dataflow tracking |
| **v4** | `communities` table and `community_id` foreign key |
| **v5** | **FTS5** virtual table `nodes_fts` for full-text search |
| **v6** | `community_summaries`, `flow_snapshots`, and `risk_index` with `risk_score` indexing |
| **v7** | Compound indices on `edges` for query performance |
| **v8** | Composite index for upsert performance |
| **v9** | `confidence` and `confidence_tier` columns to `edges` |

Each migration function (e.g., `_migrate_v5` at lines 49-57) executes raw SQL ALTER statements, allowing existing databases to upgrade in place without data loss.

## Node and Edge Persistence Mechanisms

The `GraphStore` class provides atomic **upsert** operations that translate Python dataclasses (`NodeInfo`, `EdgeInfo`) into SQLite transactions.

### Upserting Nodes

The `GraphStore.upsert_node` method implements an INSERT...ON CONFLICT pattern against the `qualified_name` unique constraint:

```python
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, ...
    """,
    (node.kind, node.name, qualified, ...)
)

```

This guarantees idempotency—running the parser twice on the same file updates existing records rather than creating duplicates.

### Upserting Edges

The `GraphStore.upsert_edge` method uses a lookup-then-update pattern because edges are identified by a composite key (kind, source, target, file_path, line):

```python
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 confidence=?, confidence_tier=?, updated_at=? WHERE id=?",
        (confidence, confidence_tier, now, existing["id"])
    )
else:
    self._conn.execute(
        """INSERT INTO edges (...) VALUES (...)""",
        (edge.kind, edge.source, ...)
    )

```

Both methods utilize `sqlite3.Row` factories and implicit transactions (via `BEGIN IMMEDIATE` in batch operations) to ensure consistency while invalidating the in-memory NetworkX cache when mutations occur.

## Practical Workflow Example

Typical usage combines the parser with the storage layer:

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

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

    fn = NodeInfo(
        kind="function",
        name="process_data",
        qualified_name="my_pkg.handlers.process_data",
        file_path="my_pkg/handlers.py",
        line_start=10,
        line_end=20,
        language="python",
        parent_name="handlers",
        params="data, config",
        return_type="bool",
        modifiers=None,
        is_test=False,
        extra={"docstring": "Process incoming data"},
    )
    store.upsert_node(fn)
    
    # Persist a CALLS relationship

    edge = EdgeInfo(
        kind="CALLS",
        source="my_pkg.main.main",
        target="my_pkg.handlers.process_data",
        file_path="my_pkg/main.py",
        line=45,
        extra={"confidence": 0.98},
    )
    store.upsert_edge(edge)

```

The high-level API abstracts the raw SQL while maintaining the performance characteristics of prepared statements and indexed lookups.

## Summary

- **code-review-graph** uses a single SQLite file (`graph.db`) with a relational schema mapping graph entities to the **nodes**, **edges**, and **metadata** tables
- The **qualified_name** column serves as the natural primary identifier for nodes, enabling string-based foreign key relationships in edges
- Schema versioning in **[`migrations.py`](https://github.com/tirth8205/code-review-graph/blob/main/migrations.py)** supports nine incremental upgrades including FTS5 search and risk indices without breaking existing databases
- Persistence occurs through **upsert** operations in [`graph.py`](https://github.com/tirth8205/code-review-graph/blob/main/graph.py) (lines 18-84) that handle conflict resolution and cache invalidation automatically
- Composite indices on `(source_qualified, kind)` and `(target_qualified, kind)` optimize graph traversal queries common in code analysis

## Frequently Asked Questions

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

The repository implements a **versioned migration system** in [`code_review_graph/migrations.py`](https://github.com/tirth8205/code-review-graph/blob/main/code_review_graph/migrations.py) that checks the `metadata` table on startup. Each schema change (from v2 through v9) is applied incrementally via ALTER TABLE and CREATE INDEX statements, allowing existing `graph.db` files to upgrade automatically while preserving node and edge data.

### Why does the edges table use qualified names instead of integer foreign keys?

The schema uses **string-based identifiers** (`source_qualified`, `target_qualified`) rather than integer IDs to support **eventual consistency** during analysis. This design allows edges to reference nodes that may not yet be parsed or inserted, eliminating foreign key constraint violations when processing files in arbitrary order.

### What performance optimizations exist for large codebases?

The schema includes multiple optimization layers: **FTS5** virtual tables for fast text search (added v5), compound indices on edge lookups (added v7-v8), and composite unique constraints that enable SQLite's native UPSERT syntax. The `GraphStore` class also maintains an in-memory NetworkX graph with write-through caching to minimize database round-trips during complex traversals.

### How are confidence scores stored for inferred relationships?

Since migration v9, the **edges** table includes `confidence` (REAL) and `confidence_tier` (TEXT) columns. These are populated during the `upsert_edge` operation in [`graph.py`](https://github.com/tirth8205/code-review-graph/blob/main/graph.py), allowing the system to distinguish between statically extracted relationships (high confidence) and inferred or heuristic relationships (lower confidence) when querying the graph.