# How tirth8205/code-review-graph Stores Nodes and Edges in SQLite: Inside the GraphStore Architecture

> Explore how tirth8205/code-review-graph uses SQLite tables nodes edges and metadata for atomic upserts and consistent data storage within its GraphStore architecture.

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

---

**The `GraphStore` class persists code entities and relationships using three SQLite tables (`nodes`, `edges`, and `metadata`) with atomic upsert operations and IMMEDIATE transactions to ensure data consistency.**

The **tirth8205/code-review-graph** repository implements a durable knowledge graph for code analysis that stores nodes and edges in SQLite using a relational schema optimized for software entities. The storage layer centers on the `GraphStore` class defined in [`code_review_graph/graph.py`](https://github.com/tirth8205/code-review-graph/blob/main/code_review_graph/graph.py), which manages database connections, schema initialization, and atomic write operations.

## SQLite Schema Structure for the Graph

When a `GraphStore` instance is created, it executes the module-level constant **`_SCHEMA_SQL`** to initialize the database. The design uses three distinct tables to separate entities from relationships and metadata.

### The nodes Table

The **`nodes`** table stores one row per code entity (files, classes, functions, types, and tests). It uses an auto-incrementing `id` as the primary key, but enforces uniqueness on **`qualified_name`** (a composite identifier including kind and scope). Important columns include:

- `kind` – entity type (e.g., "Function", "Class")
- `name` – short identifier
- `qualified_name` – globally unique name (UNIQUE constraint)
- `file_path`, `line_start`, `line_end` – source location
- `language`, `parent_name`, `params`, `return_type` – semantic details
- `modifiers`, `is_test` – binary flags
- `file_hash` – content fingerprint for invalidation
- `extra` – JSON blob for extensible attributes

### The edges Table

The **`edges`** table captures directed relationships such as `CALLS`, `IMPORTS_FROM`, and `INHERITS`. It links two nodes via their qualified names rather than integer IDs, simplifying queries across the graph. Key columns include:

- `kind` – relationship type
- `source_qualified` – origin node name
- `target_qualified` – destination node name
- `file_path`, `line` – location of the relationship
- `confidence` and `confidence_tier` – relationship strength metrics
- `extra` – JSON metadata

### The metadata Table

The **`metadata`** table functions as a key-value store for global state, most notably the **`schema_version`** that powers the migration framework.

## Persisting Nodes with Upsert Semantics

Node insertion happens through **`GraphStore.upsert_node()`** (lines 99‑130 in [`code_review_graph/graph.py`](https://github.com/tirth8205/code-review-graph/blob/main/code_review_graph/graph.py)). The method constructs a qualified name and performs an atomic **INSERT ... ON CONFLICT** operation, ensuring that re-indexing the same file updates existing records rather than creating duplicates.

The implementation converts the node's `extra` dictionary to JSON, computes the qualified identifier via `self._make_qualified(node)`, and executes:

```python
qualified = self._make_qualified(node)
extra = json.dumps(node.extra) if node.extra else "{}"
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, …, 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)
)

```

This upsert pattern guarantees that the **qualified_name** remains unique while refreshing all mutable attributes (file location, line numbers, hashes) in a single statement.

## Persisting Edges with Duplicate Detection

Edge storage in **`GraphStore.upsert_edge()`** (lines 32‑65 in [`code_review_graph/graph.py`](https://github.com/tirth8205/code-review-graph/blob/main/code_review_graph/graph.py)) follows a slightly different pattern. Because edges do not have a single natural unique key covering all semantic variations, the method first probes for an existing relationship matching the exact `kind`, `source_qualified`, `target_qualified`, `file_path`, and `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 a matching row exists, the method issues an **UPDATE** to refresh metadata; otherwise, it performs an **INSERT** and returns the new `rowid`. This selective approach prevents duplicate relationship entries while allowing metadata refresh when the same edge is re-discovered.

## Transaction Safety and Atomicity

All write-heavy operations in the repository wrap mutations inside **IMMEDIATE** transactions via `_begin_immediate`. Unlike SQLite's default autocommit mode, this strategy locks the database at the start of the transaction, preventing race conditions during bulk imports.

If any operation fails, the transaction rolls back completely, ensuring the graph remains in a consistent state. This is particularly important for batch operations like `store_file_nodes_edges`, which may insert hundreds of nodes and edges in a single logical unit.

## Schema Evolution and Migrations

The repository includes a migration framework in **[`code_review_graph/migrations.py`](https://github.com/tirth8205/code-review-graph/blob/main/code_review_graph/migrations.py)** to handle schema changes without data loss. On initialization, `GraphStore` queries the **`metadata`** table for the current `schema_version` using `get_schema_version()`.

If the version is outdated or missing, the system invokes `run_migrations()` to execute incremental SQL scripts (e.g., adding columns, creating FTS5 virtual tables, or introducing community detection tables). This versioning strategy allows the `nodes` and `edges` tables to evolve while preserving existing graph data.

## Working with GraphStore: Complete Example

The following example demonstrates initializing a graph database, inserting a function node, and creating a `CALLS` relationship:

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

# 1. Initialise the graph database

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

# 2. Insert a function node

fn_node = NodeInfo(
    kind="Function",
    name="process_data",
    qualified_name="my_pkg.process_data",
    file_path="my_pkg/utils.py",
    line_start=10,
    line_end=20,
    language="python",
    parent_name="MyClass",
    params="data: dict",
    return_type="dict",
    modifiers=None,
    is_test=False,
    extra={"docstring": "Process incoming data"}
)
node_id = store.upsert_node(fn_node, file_hash="abc123")
print(f"Node stored with id {node_id}")

# 3. Insert a CALLS edge

call_edge = EdgeInfo(
    kind="CALLS",
    source="my_pkg.process_data",
    target="my_pkg.validate",
    file_path="my_pkg/utils.py",
    line=15,
    extra={"confidence": 0.98}
)
edge_id = store.upsert_edge(call_edge)
print(f"Edge stored with id {edge_id}")

# 4. Query directly via the connection

rows = store._conn.execute(
    "SELECT qualified_name, file_path FROM nodes WHERE kind='Function'"
).fetchall()
for r in rows:
    print(r["qualified_name"], "→", r["file_path"])

```

## Summary

- **Three-table schema:** The `nodes`, `edges`, and `metadata` tables separate entities, relationships, and versioning information.
- **Qualified name uniqueness:** Nodes are uniquely identified by `qualified_name` rather than auto-generated integers, enabling stable references across file changes.
- **Atomic upserts:** Both `upsert_node()` and `upsert_edge()` use SQLite's conflict resolution or manual existence checks to prevent duplicates while refreshing metadata.
- **Immediate transactions:** Write operations use `_begin_immediate` to guarantee atomicity and prevent partial writes during batch imports.
- **Migration support:** The [`migrations.py`](https://github.com/tirth8205/code-review-graph/blob/main/migrations.py) module tracks schema versions in the `metadata` table, enabling safe evolution of the storage layer.

## Frequently Asked Questions

### What is the primary key for nodes in the SQLite database?

The **`nodes`** table uses an auto-incrementing integer `id` as its formal primary key, but enforces uniqueness via the **`qualified_name`** column, which combines the entity's kind, name, and scope. This qualified name serves as the application-level identifier for upsert operations and edge references.

### How does GraphStore handle duplicate node insertions?

The **`upsert_node()`** method uses SQLite's **`INSERT ... ON CONFLICT(qualified_name) DO UPDATE`** syntax. When a node with the same qualified name already exists, the statement updates all mutable columns (file path, line numbers, hash, JSON extras) rather than inserting a new row, maintaining a single canonical record per entity.

### What transaction mode does code-review-graph use?

The repository explicitly uses **IMMEDIATE** transactions via `_begin_immediate`. This mode acquires locks at the transaction start rather than on first write, preventing SQLITE_BUSY errors during concurrent access and ensuring that bulk operations in `store_file_batch` remain atomic.

### How does the schema evolve when new columns are needed?

The **[`migrations.py`](https://github.com/tirth8205/code-review-graph/blob/main/migrations.py)** module manages schema evolution by reading the `schema_version` from the `metadata` table. When `GraphStore` initializes, it compares this version against the codebase's expected version and runs any pending migration scripts (adding columns, indexes, or virtual tables) through the `run_migrations()` function, preserving existing node and edge data.