# What Is the Graph Data Model Used for Storing Nodes and Edges in SQLite?

> Explore the graph data model for SQLite storing nodes and edges. Learn how Codebase-Memory-MCP uses normalized tables, foreign keys, and JSON for efficient graph persistence and lookups.

- Repository: [Martin Vogel/codebase-memory-mcp](https://github.com/DeusData/codebase-memory-mcp)
- Tags: deep-dive
- Published: 2026-07-28

---

**The Codebase-Memory-MCP persists a directed code graph in SQLite using two normalized tables—`nodes` and `edges`—that enforce referential integrity through foreign keys, support JSON property bags, and use generated columns for fast lookups.**

The **DeusData/codebase-memory-mcp** repository implements a high-performance graph database on top of SQLite to store code entities and their relationships. Understanding the **graph data model used for storing nodes and edges in SQLite** is essential for extending the indexer or building custom queries against the stored codebase memory.

## Core Schema Architecture

The schema is defined in [`internal/cbm/sqlite_writer.c`](https://github.com/DeusData/codebase-memory-mcp/blob/main/internal/cbm/sqlite_writer.c) and centers on two tables that implement a classic directed graph with strict referential integrity.

### The nodes Table

The `nodes` table stores entities such as classes, functions, and variables. Each row contains:

- **`id`**: `INTEGER PRIMARY KEY AUTOINCREMENT` — The internal row identifier used as the canonical node ID.
- **`project`**: `TEXT NOT NULL` — Foreign key referencing `projects(name)` that scopes the node to a specific codebase.
- **`label`**: `TEXT NOT NULL` — High-level category (e.g., `CLASS`, `FUNCTION`, `VARIABLE`).
- **`name`**: `TEXT NOT NULL` — Short symbol name.
- **`qualified_name`**: `TEXT NOT NULL` — Fully qualified identifier (e.g., `pkg.module.Class.method`).
- **`file_path`**: `TEXT DEFAULT ''` — Path of the source file containing the symbol.
- **`start_line`** / **`end_line`**: `INTEGER DEFAULT 0` — Source-code line range.
- **`properties`**: `TEXT DEFAULT '{}'` — JSON-encoded bag of additional attributes (e.g., docstrings, signatures).

A **unique constraint** on `(project, qualified_name)` ensures that a fully qualified symbol appears only once per project.

*Source definition* — see the schema creation in [`sqlite_writer.c`](https://github.com/DeusData/codebase-memory-mcp/blob/main/sqlite_writer.c) at lines 2192‑2198:

```sql
-- https://github.com/DeusData/codebase-memory-mcp/blob/main/internal/cbm/sqlite_writer.c#L2192-L2198
CREATE TABLE nodes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    project TEXT NOT NULL,
    label TEXT NOT NULL,
    name TEXT NOT NULL,
    qualified_name TEXT NOT NULL,
    file_path TEXT DEFAULT '',
    start_line INTEGER DEFAULT 0,
    end_line INTEGER DEFAULT 0,
    properties TEXT DEFAULT '{}',
    UNIQUE(project, qualified_name)
);

```

### The edges Table

The `edges` table stores directed relationships between nodes. Each row contains:

- **`id`**: `INTEGER PRIMARY KEY AUTOINCREMENT` — Internal edge identifier.
- **`project`**: `TEXT NOT NULL` — Foreign key to `projects(name)`.
- **`source_id`**: `INTEGER NOT NULL` — Foreign key to `nodes(id)` representing the relationship origin.
- **`target_id`**: `INTEGER NOT NULL` — Foreign key to `nodes(id)` representing the relationship destination.
- **`type`**: `TEXT NOT NULL` — Relationship classification (e.g., `CALLS`, `IMPORTS`, `INHERITS`).
- **`properties`**: `TEXT DEFAULT '{}'` — JSON metadata for the edge.
- **`url_path_gen`**: Generated column — Extracted `url_path` from `properties` using `json_extract`.
- **`local_name_gen`**: Generated column — For `IMPORTS` edges, extracts `local_name` from `properties`.

A **unique constraint** on `(source_id, target_id, type, local_name_gen)` prevents duplicate edges of the same type between the same pair of nodes.

*Source definition* — see [`sqlite_writer.c`](https://github.com/DeusData/codebase-memory-mcp/blob/main/sqlite_writer.c) at lines 2210‑2218:

```sql
-- https://github.com/DeusData/codebase-memory-mcp/blob/main/internal/cbm/sqlite_writer.c#L2210-L2218
CREATE TABLE edges (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    project TEXT NOT NULL,
    source_id INTEGER NOT NULL,
    target_id INTEGER NOT NULL,
    type TEXT NOT NULL,
    properties TEXT DEFAULT '{}',
    url_path_gen TEXT AS (json_extract(properties, '$.url_path')),
    local_name_gen TEXT AS (json_extract(properties, '$.local_name')),
    UNIQUE(source_id, target_id, type, local_name_gen)
);

```

## Referential Integrity and Relationships

The schema enforces **foreign key constraints** with cascading deletes to maintain graph consistency:

- Both `nodes.project` and `edges.project` reference `projects(name)`.
- `edges.source_id` and `edges.target_id` reference `nodes.id` with `ON DELETE CASCADE`, ensuring that removing a node automatically deletes its connected edges.
- Deleting a project cascades to all associated nodes and edges, keeping the database free of orphaned records.

## Runtime Implementation

All database operations are handled by [`src/store/store.c`](https://github.com/DeusData/codebase-memory-mcp/blob/main/src/store/store.c), which prepares parameterized statements against the schema defined above.

**Key prepared statements** include:

- **`stmt_upsert_node`**: Wraps the `INSERT INTO nodes` logic.
- **`stmt_upsert_edge`**: Wraps the `INSERT INTO edges` logic.
- **`stmt_find_edges_by_source`**: Queries outgoing edges for traversal.

The public API is exposed in [`src/store/store.h`](https://github.com/DeusData/codebase-memory-mcp/blob/main/src/store/store.h) through the `cbm_store_t` interface.

## Practical Examples

### 1. Creating the Schema

While the schema is typically created by the writer utility, the SQL executed internally is:

```sql
-- Executed internally by sqlite_writer.c during database initialization
CREATE TABLE IF NOT EXISTS nodes (...);
CREATE TABLE IF NOT EXISTS edges (...);
CREATE INDEX IF NOT EXISTS idx_nodes_project ON nodes(project);
CREATE INDEX IF NOT EXISTS idx_nodes_qname ON nodes(qualified_name);
CREATE INDEX IF NOT EXISTS idx_edges_src ON edges(source_id);
CREATE INDEX IF NOT EXISTS idx_edges_tgt ON edges(target_id);

```

### 2. Inserting a Node (C API)

```c
#include "store.h"

cbm_store_t *store = cbm_store_open_path("memory.db");
const char *project = "myapp";

int rc = cbm_store_upsert_node(
    store,
    project,
    "FUNCTION",               // label
    "calculate_sum",          // name
    "myapp.utils.calculate_sum", // qualified_name
    "src/utils.c",            // file_path
    45,                       // start_line
    52,                       // end_line
    "{\"doc\":\"Returns sum of two ints\"}" // properties JSON
);

if (rc == CBM_STORE_OK) {
    printf("Node inserted with ID: %lld\n", cbm_store_last_insert_id(store));
}

```

*Implementation note*: This binds to the prepared statement `INSERT_NODE_SQL` defined in [`store.c`](https://github.com/DeusData/codebase-memory-mcp/blob/main/store.c).

### 3. Inserting an Edge (C API)

```c
// Assuming node IDs 101 (source) and 204 (target) exist
int rc = cbm_store_upsert_edge(
    store,
    "myapp",
    101,                      // source_id
    204,                      // target_id
    "CALLS",                  // type
    "{\"url_path\":\"/utils/calculate_sum\"}" // properties
);

```

The `url_path_gen` column is automatically populated from the JSON properties.

### 4. Querying Outgoing Edges

```c
cbm_edges_t *results = NULL;
int rc = cbm_store_find_edges_by_source(store, "myapp", 101, &results);

if (rc == CBM_STORE_OK) {
    for (size_t i = 0; i < results->count; i++) {
        printf("Edge %zu: target=%d type=%s\n", 
               i, 
               results->edges[i].target_id, 
               results->edges[i].type);
    }
    cbm_store_edges_free(&results);
}

```

This executes the prepared statement `SELECT * FROM edges WHERE source_id=? AND project=?`.

## Summary

- The **graph data model** consists of two tables: `nodes` for entities and `edges` for relationships.
- **`nodes`** uses `qualified_name` and `project` as a composite unique key, storing metadata and JSON properties.
- **`edges`** implements a directed graph via `source_id` and `target_id` foreign keys, with generated columns for extracted JSON fields.
- **Referential integrity** is enforced through foreign keys and `ON DELETE CASCADE`, ensuring consistency when projects or nodes are removed.
- Low-level schema creation resides in [`internal/cbm/sqlite_writer.c`](https://github.com/DeusData/codebase-memory-mcp/blob/main/internal/cbm/sqlite_writer.c), while runtime CRUD operations are implemented in [`src/store/store.c`](https://github.com/DeusData/codebase-memory-mcp/blob/main/src/store/store.c).

## Frequently Asked Questions

### What is the primary key for nodes and edges?

Both tables use an auto-incrementing integer column named **`id`** as the primary key. For nodes, this ID is referenced by the `source_id` and `target_id` columns in the `edges` table to establish graph connections.

### How does the schema prevent duplicate symbols or relationships?

The `nodes` table defines a **unique constraint** on the combination of `project` and `qualified_name`, ensuring a fully qualified symbol appears only once per project. The `edges` table similarly enforces uniqueness on `(source_id, target_id, type, local_name_gen)`, preventing multiple identical relationships between the same nodes.

### Why are the edges columns url_path_gen and local_name_gen marked as generated?

These are **virtual generated columns** that extract values from the JSON `properties` field using SQLite's `json_extract` function. They allow indexing and querying on specific JSON attributes without duplicating storage, while maintaining a flexible schema for edge metadata.

### What happens to edges when a node is deleted?

Because `edges.source_id` and `edges.target_id` are defined with `ON DELETE CASCADE` foreign key constraints, deleting a node automatically removes all edges where that node appears as either the source or target. This maintains referential integrity without requiring manual cleanup.