# SQLite Graph Store Data Model in DeusData codebase-memory: Schema, Storage, and Querying

> Explore the SQLite graph store data model in DeusData's codebase-memory. Learn about its efficient schema, node and edge storage, and querying methods for fast data management.

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

---

**DeusData's codebase-memory implements a property graph model in SQLite using separate `nodes` and `edges` tables with JSON-encoded properties, generated columns for fast import lookups, and a prepared-statement API for efficient CRUD operations.**

The codebase-memory project provides a persistent graph store built on SQLite to index and query software repositories. This article examines the SQLite graph store data model, detailing how symbols (nodes) and relationships (edges) are structured, stored, and queried using the C API implemented in [`src/store/store.c`](https://github.com/DeusData/codebase-memory-mcp/blob/main/src/store/store.c).

## Core Schema Design

The database schema is created in [`src/store/store.c`](https://github.com/DeusData/codebase-memory-mcp/blob/main/src/store/store.c) and centers on a property graph model where code symbols are vertices and their relationships are directed edges.

### Projects and File Metadata

Two bookkeeping tables manage project scope and incremental indexing:

- **`projects`**: Stores `name` (primary key), `indexed_at` timestamp, and `root_path` to track each indexed repository.
- **`file_hashes`**: Caches `rel_path`, `sha256`, `mtime_ns`, and `size` per project to enable incremental re-indexing by detecting changed files.

### The Nodes Table (Graph Vertices)

The `nodes` table stores every code symbol as a row with the following schema:

```sql
id INTEGER PRIMARY KEY AUTOINCREMENT,
project TEXT REFERENCES projects(name),
label TEXT,                    -- e.g., "function", "class", "variable"
name TEXT,                     -- Short symbol name
qualified_name TEXT,           -- Fully qualified path (e.g., "mypkg.module.Class.method")
file_path TEXT,
start_line INTEGER,
end_line INTEGER,
properties TEXT                -- JSON-encoded key/value pairs (docstrings, attributes, etc.)

```

Each node represents a distinct symbol in the codebase, with `qualified_name` serving as the primary lookup key for precise symbol resolution.

### The Edges Table (Graph Relationships)

The `edges` table implements directed relationships between nodes:

```sql
id INTEGER PRIMARY KEY AUTOINCREMENT,
project TEXT REFERENCES projects(name),
source_id INTEGER REFERENCES nodes(id),
target_id INTEGER REFERENCES nodes(id),
type TEXT,                     -- Relationship kind: "CALLS", "IMPORTS", "INHERITS", etc.
properties TEXT,               -- JSON metadata about the relationship
url_path_gen TEXT GENERATED ALWAYS AS (json_extract(properties, '$.url_path')) STORED,
local_name_gen TEXT GENERATED ALWAYS AS (json_extract(properties, '$.local_name')) STORED

```

The schema includes a unique constraint preventing duplicate edges while allowing multiple imports from the same source:

```sql
UNIQUE(source_id, target_id, type, local_name_gen)

```

The generated columns `url_path_gen` and `local_name_gen` optimize queries on JSON fields without runtime parsing overhead.

## Prepared Statement API for Graph Traversal

The `cbm_store_t` structure caches SQLite prepared statements for high-performance operations. These statements are created lazily via `prepare_cached()` (lines 194-210 in [`src/store/store.c`](https://github.com/DeusData/codebase-memory-mcp/blob/main/src/store/store.c)) and reset for subsequent calls.

### Node Lookup Operations

The API provides targeted lookups for symbol resolution:

- **`stmt_find_node_by_id`**: Retrieves a node by primary key with `SELECT … FROM nodes WHERE id = ? AND project = ?`.
- **`stmt_find_node_by_qn`**: Looks up nodes by fully qualified name using `qualified_name = ?`, the most common resolution method.
- **`stmt_find_nodes_by_name`**: Finds all symbols sharing a short name to resolve overloads and shadows.
- **`stmt_find_nodes_by_label`**: Filters nodes by type (e.g., all `function` or `class` nodes).
- **`stmt_find_nodes_by_file`**: Enumerates all symbols defined in a specific file path.

### Edge Traversal Operations

Graph traversal uses indexed edge queries:

- **`stmt_find_edges_by_source`**: Retrieves outgoing edges from a node ID.
- **`stmt_find_edges_by_target`**: Retrieves incoming edges to a node ID.
- **`stmt_find_edges_by_type`**: Filters edges by relationship kind (e.g., only `CALLS` edges).
- **`stmt_find_edges_by_source_type`**: Combines source filtering with edge type, utilizing `local_name_gen` for efficient `IMPORTS` queries.
- **`stmt_insert_edge`**: Uses `INSERT OR REPLACE` to create edges with conflict resolution on the unique constraint.

## Query Optimization Strategy

The store creates performance indexes when opened via `create_user_indexes()` (lines 352-362 in [`src/store/store.c`](https://github.com/DeusData/codebase-memory-mcp/blob/main/src/store/store.c)).

### Indexes for Fast Lookups

The following indexes accelerate common query patterns:

- **`idx_nodes_label`**: Composite index on `(project, label)` for label-based filtering.
- **`idx_nodes_name`**: Composite index on `(project, name)` for name searches.
- **`idx_nodes_file`**: Composite index on `(project, file_path)` for file-scoped queries.
- **`idx_edges_source`**: Index on `(source_id, project)` for fast outbound traversal.
- **`idx_edges_target`**: Index on `(target_id, project)` for fast inbound traversal.
- **`idx_edges_type`**: Index on `(type, project)` for relationship-type filtering.

### Full-Text Search with FTS5

The schema includes an FTS5 virtual table `nodes_fts` (lines 330-345) enabling full-text search across symbol names, qualified names, labels, and file paths. This allows natural language queries against the codebase graph without scanning entire tables.

## Practical Querying Examples

The following C example demonstrates opening a store and traversing the graph:

```c
/* Open store for project "myproject" */
cbm_store_t *store = cbm_store_open("myproject");

/* Find node by qualified name */
sqlite3_stmt *stmt = store->stmt_find_node_by_qn;
sqlite3_bind_text(stmt, 1, "my.pkg.Foo.bar", -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 2, "myproject", -1, SQLITE_TRANSIENT);

if (sqlite3_step(stmt) == SQLITE_ROW) {
    int64_t node_id = sqlite3_column_int64(stmt, 0);
    const char *label = (const char*)sqlite3_column_text(stmt, 1);
    
    /* Retrieve outgoing CALLS edges */
    sqlite3_stmt *edge_stmt = store->stmt_find_edges_by_source_type;
    sqlite3_bind_int64(edge_stmt, 1, node_id);
    sqlite3_bind_text(edge_stmt, 2, "CALLS", -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(edge_stmt, 3, "myproject", -1, SQLITE_TRANSIENT);
    
    while (sqlite3_step(edge_stmt) == SQLITE_ROW) {
        int64_t target_id = sqlite3_column_int64(edge_stmt, 0);
        const char *props = (const char*)sqlite3_column_text(edge_stmt, 2);
        /* Process call relationship */
    }
}

```

Higher-level wrapper functions such as `cbm_store_find_node_by_qn()` and `cbm_store_find_edges_by_source()` encapsulate these prepared statement calls.

## Summary

- **Schema**: The SQLite graph store uses normalized `nodes` and `edges` tables with JSON properties, implementing a property graph model in [`src/store/store.c`](https://github.com/DeusData/codebase-memory-mcp/blob/main/src/store/store.c).
- **Storage**: Nodes store symbol metadata including `qualified_name` for unique identification; edges store directed relationships with generated columns for JSON extraction.
- **Querying**: A prepared-statement API (`cbm_store_t`) provides optimized lookups by ID, qualified name, file path, and relationship type.
- **Performance**: Secondary indexes on `(project, label)`, `(project, name)`, and edge source/target columns ensure fast traversal; FTS5 enables full-text symbol search.
- **Concurrency**: The `INSERT OR REPLACE` pattern on edges handles duplicate detection via the unique constraint including `local_name_gen`.

## Frequently Asked Questions

### How does the SQLite graph store handle duplicate edges?

The `edges` table defines a unique constraint on `(source_id, target_id, type, local_name_gen)` as implemented in lines 259-270 of [`src/store/store.c`](https://github.com/DeusData/codebase-memory-mcp/blob/main/src/store/store.c). This ensures that multiple `IMPORTS` edges from the same source can coexist when importing different symbols (differentiated by `local_name_gen`), while preventing identical relationship duplicates. The `stmt_insert_edge` uses `INSERT OR REPLACE` to automatically handle conflicts.

### What is the purpose of generated columns in the edges table?

The `url_path_gen` and `local_name_gen` columns are generated from the `properties` JSON blob using `json_extract()`. According to the schema in [`src/store/store.c`](https://github.com/DeusData/codebase-memory-mcp/blob/main/src/store/store.c), these stored generated columns allow the database engine to index and query JSON fields directly without parsing overhead at query time. This is particularly critical for `IMPORTS` edges where `local_name_gen` participates in the unique constraint.

### How does the store optimize queries for large codebases?

When `cbm_store_open()` initializes a connection, `create_user_indexes()` creates composite indexes on `(project, label)`, `(project, name)`, and `(project, file_path)` for nodes, plus individual indexes on `source_id`, `target_id`, and `type` for edges (lines 352-362). Additionally, an FTS5 virtual table `nodes_fts` provides inverted index access for text searches, ensuring logarithmic lookup times even with millions of symbols.

### Can I query the graph using Cypher instead of SQL?

Yes. While the core store exposes a C API with prepared statements, the [`src/cypher/cypher.c`](https://github.com/DeusData/codebase-memory-mcp/blob/main/src/cypher/cypher.c) module translates Cypher graph queries into equivalent SQLite statements that execute against the `nodes` and `edges` tables. This abstraction layer allows semantic queries like `MATCH (f:function)-[:CALLS]->(g) RETURN f.name` while maintaining the underlying SQLite storage engine's performance characteristics.