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

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.

Core Schema Design

The database schema is created in 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:

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:

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:

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

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:

/* 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.
• 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. 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, 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 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.

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