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

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 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 at lines 2192‑2198:

-- 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 at lines 2210‑2218:

-- 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, 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 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:

-- 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)

#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.

3. Inserting an Edge (C API)

// 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

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, while runtime CRUD operations are implemented in 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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}