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

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 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, 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 (lines 29-41):

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:

-- 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 (lines 43-51) maps to this schema:

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) handles this:

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

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.

Upserting a Node

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

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

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

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

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