SQLite Database Schema and FTS5 Configuration in CodeGraph

CodeGraph stores its entire code intelligence graph in a single SQLite database defined in src/db/schema.sql, utilizing six relational tables for symbols and relationships plus an FTS5 virtual table with automatic triggers for full-text search.

CodeGraph is an open-source code indexing engine that persists semantic data in SQLite. The schema combines traditional relational tables for graph storage with a specialized FTS5 configuration that enables fast symbol searching across names and documentation.

Core Database Tables

The schema in src/db/schema.sql defines six primary tables that manage the indexed codebase.

The nodes Table

The nodes table is the central registry for every code symbol. According to the source code, it stores id, kind (function, class, variable), name, qualified_name, file_path, language, and precise line/column positions. It also captures docstring, signature, visibility flags, and timestamps. This table definition appears at lines 19-41 of the schema file.

The edges Table

Relationships between symbols live in the edges table. Each row defines a graph connection with source and target columns referencing node IDs, a kind field indicating the relationship type (calls, imports, extends), and optional metadata. The table tracks positional information where the relationship occurs in source code.

Supporting Tables

The schema includes several utility tables:

• files: Tracks indexed source files with path, content_hash, language, size metrics, timestamps, node_count, and parsing errors.
• unresolved_refs: Stores references that could not be resolved during initial indexing, including from_node_id, reference_name, reference_kind, location data, and candidate lists.
• schema_versions: Manages database migrations with version, applied_at, and description columns.
• project_metadata: A simple key-value store for version and provenance data.

FTS5 Full-Text Search Configuration

CodeGraph leverages SQLite's FTS5 extension to enable fast text search across symbol definitions without scanning the entire nodes table.

Virtual Table Structure

The FTS5 virtual table nodes_fts is defined in src/db/schema.sql with the following configuration:

CREATE VIRTUAL TABLE IF NOT EXISTS nodes_fts USING fts5(
    id,
    name,
    qualified_name,
    docstring,
    signature,
    content='nodes',
    content_rowid='rowid'
);

The content='nodes' clause establishes an external content table relationship, instructing FTS5 to index data from the nodes table while using the rowid for cross-referencing. This design keeps the full-text index synchronized with the source data.

Automatic Synchronization Triggers

Three triggers defined in the schema file ensure the FTS index remains current without manual intervention:

• nodes_ai: Fires after insert on nodes to add entries to nodes_fts.
• nodes_ad: Fires after delete on nodes to remove corresponding FTS entries.
• nodes_au: Fires after update on nodes to refresh changed records in the FTS table.

These triggers are located at lines 107-123 of src/db/schema.sql.

Querying the Database in Practice

The database design supports both direct SQL access for graph traversal and FTS5-powered search for symbol discovery.

Inserting Symbols with Automatic FTS Updates

When you insert a new node, the trigger automatically populates the search index:

await db.run(`
  INSERT INTO nodes (
    id, kind, name, qualified_name, file_path, language,
    start_line, end_line, start_column, end_column,
    docstring, signature, updated_at
  ) VALUES (
    'node-123', 'function', 'calculate', 'utils.calculate',
    '/src/utils.ts', 'typescript',
    10, 12, 0, 0,
    'Computes a value from inputs.', '(a: number, b: number) => number',
    strftime('%s','now') * 1000
  );
`);

The nodes_ai trigger immediately adds this record to nodes_fts, making it searchable without additional code.

Performing Full-Text Searches

To search symbols using FTS5 syntax, query the virtual table and join back to nodes:

const rows = await db.all(`
  SELECT n.id, n.name, n.file_path
  FROM nodes_fts f
  JOIN nodes n ON n.rowid = f.rowid
  WHERE f MATCH 'compute* OR "computes a value"'
`);

The MATCH operator leverages the inverted index for subsecond performance even on large codebases.

Traversing Code Relationships

The edges table enables graph traversal directly in SQL. For example, finding all callers of a specific function:

const callers = await db.all(`
  SELECT source, kind
  FROM edges
  WHERE target = 'node-123' AND kind = 'calls'
`);

This query pattern powers the graph traversal logic found in src/graph/traversal.ts.

Summary

• Six core tables manage schema versions, code symbols, relationships, files, unresolved references, and project metadata in src/db/schema.sql.
• The nodes table stores comprehensive symbol data including names, signatures, docstrings, and source locations.
• FTS5 virtual table nodes_fts provides full-text search capabilities across symbol names and documentation.
• Automatic triggers (nodes_ai, nodes_ad, nodes_au) keep the search index synchronized with node data without application-layer coordination.
• Graph queries leverage the edges table for efficient relationship traversal between symbols.

Frequently Asked Questions

What SQLite tables does CodeGraph create for code indexing?

CodeGraph creates six tables: schema_versions for migrations, nodes for code symbols, edges for relationships, files for source file tracking, unresolved_refs for pending references, and project_metadata for key-value storage. Additionally, it creates the virtual nodes_fts table for full-text search.

How is FTS5 configured to index code symbols in CodeGraph?

The FTS5 virtual table nodes_fts is configured in src/db/schema.sql to index the name, qualified_name, docstring, and signature columns from the nodes table. It uses content='nodes' and content_rowid='rowid' to maintain synchronization with the source data.

What keeps the FTS5 index synchronized when nodes are modified?

Three database triggers defined in the schema file—nodes_ai (after insert), nodes_ad (after delete), and nodes_au (after update)—automatically propagate changes from the nodes table to the nodes_fts virtual table, ensuring the search index remains current.

Where does CodeGraph implement graph traversal queries?

Graph traversal logic that queries the edges table for relationships like "callers" or "callees" is implemented in src/graph/traversal.ts, while search functionality using the FTS5 index resides in src/context/index.ts and query helpers are located in src/db/queries.ts.

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