# SQLite Database Schema and FTS5 Configuration in CodeGraph

> Discover the SQLite database schema and FTS5 configuration used by CodeGraph. Explore how relational tables and FTS5 enable efficient code intelligence search.

- Repository: [Colby Mchenry/codegraph](https://github.com/colbymchenry/codegraph)
- Tags: internals
- Published: 2026-05-17

---

**CodeGraph stores its entire code intelligence graph in a single SQLite database defined in [`src/db/schema.sql`](https://github.com/colbymchenry/codegraph/blob/main/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`](https://github.com/colbymchenry/codegraph/blob/main/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`](https://github.com/colbymchenry/codegraph/blob/main/src/db/schema.sql) with the following configuration:

```sql
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`](https://github.com/colbymchenry/codegraph/blob/main/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:

```typescript
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:

```typescript
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:

```typescript
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`](https://github.com/colbymchenry/codegraph/blob/main/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`](https://github.com/colbymchenry/codegraph/blob/main/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`](https://github.com/colbymchenry/codegraph/blob/main/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`](https://github.com/colbymchenry/codegraph/blob/main/src/graph/traversal.ts), while search functionality using the FTS5 index resides in [`src/context/index.ts`](https://github.com/colbymchenry/codegraph/blob/main/src/context/index.ts) and query helpers are located in [`src/db/queries.ts`](https://github.com/colbymchenry/codegraph/blob/main/src/db/queries.ts).