# SQLite Schema for Nodes and Edges: Inside the DeusData Codebase-Memory MCP Store

> Explore the SQLite schema for nodes and edges in the DeusData Codebase-Memory MCP store. Learn how it optimizes graph queries with prepared statements, FTS5, and C extensions.

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

---

**The DeusData Codebase-Memory MCP store implements a high-performance graph database using SQLite, modeling code artifacts as nodes and relationships as edges with prepared-statement caching, FTS5 full-text search, and custom C extensions for vector similarity and regex matching.**

The DeusData/codebase-memory-mcp repository provides a Model Context Protocol (MCP) server that transforms codebase structure into a navigable graph. This article examines the underlying **SQLite schema for nodes and edges** and explains how the store manages queries for fast traversal, text search, and incremental updates.

## SQLite Schema for Nodes and Edges

The schema initialization occurs in [`src/store/store.c`](https://github.com/DeusData/codebase-memory-mcp/blob/main/src/store/store.c) within the `init_schema` function (lines 22-66). The design employs foreign key constraints and generated columns to maintain referential integrity while enabling efficient graph queries.

### Nodes Table: Code Artifacts as Vertices

The `nodes` table stores every code artifact—functions, classes, methods, and files—as a vertex in the graph. According to the DDL in [`store.c`](https://github.com/DeusData/codebase-memory-mcp/blob/main/store.c), the table structure is:

| Column | Type | Description |
|--------|------|-------------|
| `id` | `INTEGER PK AUTOINCREMENT` | Surrogate key for internal references. |
| `project` | `TEXT NOT NULL` | Foreign key to the `projects` table. |
| `label` | `TEXT NOT NULL` | Category: Function, Class, Method, Module, File. |
| `name` | `TEXT NOT NULL` | Short identifier (e.g., `updateCloudClient`). |
| `qualified_name` | `TEXT NOT NULL` | Full dotted path (e.g., `myproj.utils.updateCloudClient`). |
| `file_path` | `TEXT DEFAULT ''` | Relative path to the source file. |
| `start_line` | `INTEGER DEFAULT 0` | Source location start. |
| `end_line` | `INTEGER DEFAULT 0` | Source location end. |
| `properties` | `TEXT DEFAULT '{}'` | JSON blob for extensible metadata. |

A unique constraint on `(project, qualified_name)` ensures idempotent inserts. The corresponding C structure `cbm_node_t` is defined in [`src/store/store.h`](https://github.com/DeusData/codebase-memory-mcp/blob/main/src/store/store.h) (lines 29-41):

```c
typedef struct {
    int64_t id;
    const char *project;
    const char *label;
    const char *name;
    const char *qualified_name;
    const char *file_path;
    int start_line;
    int end_line;
    const char *properties_json;
} cbm_node_t;

```

### Edges Table: Relationship Tracking with Generated Columns

The `edges` table captures directed relationships such as `CALLS`, `IMPORTS`, and `HTTP_CALLS`. The schema includes two **generated columns** that extract JSON values at write-time for indexing:

- `url_path_gen`: Extracts `$.url_path` from the `properties` JSON column.
- `local_name_gen`: For `IMPORTS` edges, extracts `$.local_name` to handle aliased imports.

The full DDL includes:

```sql
-- Simplified from src/store/store.c lines 22-66
CREATE TABLE edges (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    project TEXT NOT NULL,
    source_id INTEGER NOT NULL REFERENCES nodes(id),
    target_id INTEGER NOT NULL REFERENCES nodes(id),
    type TEXT NOT NULL,
    properties TEXT DEFAULT '{}',
    url_path_gen TEXT GENERATED ALWAYS AS (json_extract(properties,'$.url_path')),
    local_name_gen TEXT GENERATED ALWAYS AS (
        CASE WHEN type='IMPORTS' 
        THEN coalesce(json_extract(properties,'$.local_name'),'') 
        ELSE '' END
    ),
    UNIQUE(source_id, target_id, type, local_name_gen)
);

```

The `cbm_edge_t` struct in [`src/store/store.h`](https://github.com/DeusData/codebase-memory-mcp/blob/main/src/store/store.h) (lines 43-51) maps to this schema:

```c
typedef struct {
    int64_t id;
    const char *project;
    int64_t source_id;
    int64_t target_id;
    const char *type;
    const char *properties_json;
} cbm_edge_t;

```

### Supporting Tables and FTS5 Index

Beyond the core graph tables, the schema includes:

- **projects**: Metadata for each indexed codebase with `root_path` and `indexed_at` timestamps.
- **file_hashes**: Incremental indexing cache using SHA-256 and modification times.
- **project_summaries**: Cached high-level metrics and summaries.
- **nodes_fts**: A virtual FTS5 table indexing `name`, `qualified_name`, `label`, and `file_path` for BM25-ranked search.

## Query Management Architecture

The store manages queries through a combination of prepared-statement caching, custom SQL functions, and transaction optimizations.

### Prepared Statement Caching

Every SQL statement is prepared once and cached in the `cbm_store` struct to avoid repeated parse-compile overhead. The `prepare_cached()` function (lines 89-104 in [`src/store/store.c`](https://github.com/DeusData/codebase-memory-mcp/blob/main/src/store/store.c)) handles this:

```c
// From store.c - prepare_cached implementation
static int prepare_cached(cbm_store_t *s, const char *sql, sqlite3_stmt **stmt) {
    if (*stmt) return SQLITE_OK; // Already prepared
    int rc = sqlite3_prepare_v2(s->db, sql, -1, stmt, NULL);
    return rc;
}

```

Callers such as `cbm_store_upsert_node` (lines 188-215) and `cbm_store_find_edges_by_source` reuse these compiled statements. The store handle is **thread-unsafe**; concurrent access requires separate handles per thread or explicit serialization.

### Full-Text Search and Tokenization

The FTS5 virtual table enables fast symbol search. The store registers a custom tokenizer function `cbm_camel_split` (lines 445-465) that splits camelCase identifiers (`updateCloudClient` → `update Cloud Client`), allowing BM25 ranking to match partial identifiers.

Additionally, deterministic `regexp` and `iregexp` functions (lines 506-531) compile patterns once per statement using SQLite's aux-data caching, enabling efficient regex filtering in SQL queries.

### Vector Similarity and Cosine Distance

For semantic search, the store implements `cbm_cosine_i8` (lines 562-589), a custom SQL function that computes cosine similarity over int8 vectors. This allows queries like:

```sql
SELECT * FROM nodes WHERE cbm_cosine_i8(embedding, ?) > 0.85;

```

### Bulk Operations and Write Optimization

The store provides bulk write primitives to maximize ingestion throughput:

- **`cbm_store_begin_bulk`**: Disables synchronous writes and expands the page cache.
- **`cbm_store_end_bulk`**: Restores default PRAGMA settings.
- **`cbm_store_drop_indexes` / `cbm_store_create_indexes`**: Temporarily removes indexes during large imports to reduce write amplification.

## Graph Traversal and Search Operations

Beyond CRUD operations, the store implements graph algorithms and flexible search.

### Breadth-First Search Implementation

The `cbm_store_bfs` function performs bounded graph traversal using prepared statements `stmt_find_edges_by_source` and `stmt_find_edges_by_target`. It accepts parameters for direction (`outbound`, `inbound`, or `both`), edge type filters, max depth, and node limits, returning a `cbm_traverse_result_t` containing visited nodes and edge metadata.

### Read-Only Query Mode

For analytics or read-only filesystems, `cbm_store_open_path_query` opens the database using an immutable `file:` URI with WAL mode disabled. This mode skips schema creation and maintains the same helper function registrations (camel split, regex, cosine) without write capabilities.

### Schema Introspection

`cbm_store_get_schema` and `cbm_store_get_schema_counts` query `sqlite_master` and use `json_each` to enumerate distinct node labels, edge types, and available JSON property keys, enabling dynamic query builders.

## Practical Code Examples

The following examples demonstrate working with the schema using the public C API defined in [`src/store/store.h`](https://github.com/DeusData/codebase-memory-mcp/blob/main/src/store/store.h).

### Upserting a Node

```c
cbm_node_t n = {
    .project = "myproj",
    .label   = "Function",
    .name    = "updateCloudClient",
    .qualified_name = "myproj.utils.updateCloudClient",
    .file_path = "src/utils/cloud.c",
    .start_line = 42,
    .end_line   = 58,
    .properties_json = "{\"doc\":\"Updates the cloud client\"}"
};
int64_t node_id = cbm_store_upsert_node(store, &n);
// Returns the node ID (existing or newly created)

```

### Inserting a Relationship

```c
cbm_edge_t e = {
    .project = "myproj",
    .source_id = src_node_id,
    .target_id = tgt_node_id,
    .type = "CALLS",
    .properties_json = "{\"args\":[{\"name\":\"client\"}]}"
};
int64_t edge_id = cbm_store_insert_edge(store, &e);

```

### Traversing with BFS

```c
cbm_traverse_result_t result = {0};
cbm_store_bfs(store, start_id, "outbound", NULL, 0, 3, 100, &result);
for (int i = 0; i < result.visited_count; ++i) {
    printf("Hop %d: %s\n", 
           result.visited[i].hop,
           result.visited[i].node.qualified_name);
}
cbm_store_traverse_free(&result);

```

### Full-Text Search

```c
cbm_search_params_t sp = {
    .project = "myproj",
    .label = "Function",
    .name_pattern = "update.*Client",
    .limit = 10,
    .sort_by = "relevance",
    .case_sensitive = false
};
cbm_search_output_t out = {0};
cbm_store_search(store, &sp, &out);
// Results ranked by BM25 score and connection degree
cbm_store_search_free(&out);

```

## Summary

- **The SQLite schema** in [`src/store/store.c`](https://github.com/DeusData/codebase-memory-mcp/blob/main/src/store/store.c) models codebases as a directed graph with `nodes` and `edges` tables, using generated columns for JSON extraction and FTS5 for full-text search.
- **Prepared-statement caching** via `prepare_cached()` eliminates SQL parsing overhead for all CRUD and traversal operations.
- **Custom C extensions** provide camelCase tokenization, regex matching, and int8 vector cosine similarity directly within SQL queries.
- **Bulk write pragmas** and temporary index dropping optimize large-scale ingestion, while read-only modes support safe analytics access.
- **BFS traversal** and schema introspection functions enable complex graph analysis and dynamic query generation.

## Frequently Asked Questions

### What is the primary key structure for nodes and edges in the DeusData store?

Nodes use an auto-incrementing `id` integer as the primary key, with a unique constraint on `(project, qualified_name)` to prevent duplicate artifacts. Edges similarly use an `id` primary key, but enforce uniqueness on `(source_id, target_id, type, local_name_gen)` to deduplicate import relationships that may have different local aliases.

### How does the store handle concurrent database access?

The `cbm_store_t` handle is **not thread-safe** according to the header documentation in [`src/store/store.h`](https://github.com/DeusData/codebase-memory-mcp/blob/main/src/store/store.h). Applications must either create a separate store handle per thread or serialize access through a mutex. The SQLite connection itself is compiled with appropriate flags for multi-thread safety at the process level, but individual handles impose single-threaded constraints.

### What custom SQL functions are available for querying the graph?

The store registers several deterministic functions: `cbm_camel_split` for tokenizing identifiers, `regexp` and `iregexp` for pattern matching, and `cbm_cosine_i8` for computing vector similarity. These functions are implemented in C within [`src/store/store.c`](https://github.com/DeusData/codebase-memory-mcp/blob/main/src/store/store.c) and are available in any SQL context once the store is initialized.

### Can the database be opened in read-only mode for analytics?

Yes. The `cbm_store_open_path_query` function opens the database in read-only mode using SQLite's immutable URI syntax. This mode skips schema creation and maintains access to all custom functions (search, regex, vector ops) without risking write locks or WAL file creation, making it suitable for read-only filesystems or concurrent analytics workloads.