SQLite Graph Storage & Migrations

Relevant source files

The following files were used as context for generating this wiki page:

• code_review_graph/migrations.py
• code_review_graph/graph.py
• tests/test_migrations.py
• code-review-graph-vscode/src/backend/sqlite.ts
• code-review-graph-vscode/test/sqlite.test.ts
• CLAUDE.md

The SQLite Graph Storage system serves as the local-first, persistent backbone for the code-review-graph project. It stores a structural representation of codebases, including nodes (functions, classes, files) and edges (calls, imports, inheritance), enabling token-efficient code analysis and review through Model Context Protocol (MCP) tools.

The system uses SQLite in WAL (Write-Ahead Logging) mode to ensure high performance and concurrency, specifically designed to handle large graphs while maintaining local availability. A versioned migration framework ensures the schema evolves safely from version 1 to the current latest version (v9), supporting features like Full-Text Search (FTS5), community detection, and execution flow tracing.

Sources: CLAUDE.md:31-34, code_review_graph/migrations.py:12-16, code-review-graph-vscode/src/backend/sqlite.ts:133-134

Core Data Model

The graph is represented through two primary tables: nodes and edges. Nodes represent structural entities in the code, while edges represent the relationships between them.

Entity Relationship Diagram

The following diagram illustrates the relationship between the primary storage tables and the metadata used for versioning and statistics.

Database Tables Summary

Table	Description	Key Fields
nodes	Stores code entities (File, Class, Function, etc.)	qualified_name, kind, file_path, signature
edges	Stores relationships (CALLS, IMPORTS_FROM, etc.)	source_qualified, target_qualified, kind
metadata	Internal key-value store for versioning and stats	key, value
communities	Clusters of related nodes for architectural overview	id, name, cohesion, size
flows	Traced execution paths through the codebase	entry_point_id, criticality, path_json
nodes_fts	FTS5 virtual table for keyword search	name, qualified_name, signature

Sources: code-review-graph-vscode/test/sqlite.test.ts:21-59, code_review_graph/migrations.py:84-140, code-review-graph-vscode/src/backend/sqlite.ts:51-118

Migration Framework

The project implements an idempotent, incremental migration framework to manage schema changes. Each migration is assigned a version number and performed within a transaction.

Migration Logic Flow

The following flow represents how the system checks and applies pending migrations when the GraphStore is initialized.

Schema Version History

The schema has evolved through several versions to support advanced graph analysis features:

• v2: Added signature column to the nodes table for signature-based identification.
• v3: Introduced flows and flow_memberships tables for execution path tracing.
• v4: Added communities table and community_id to the nodes table for Leiden-based clustering.
• v5: Created nodes_fts (FTS5) virtual table for high-performance keyword search.
• v6: Implemented summary tables: community_summaries, flow_snapshots, and risk_index.
• v9: Introduced confidence scoring with confidence and confidence_tier columns for edges.

Sources: code_review_graph/migrations.py:73-195

Cross-Platform Access (Python & TypeScript)

The SQLite database is designed for multi-language access. While the Python backend performs all writes and migrations, the VS Code extension uses a read-only TypeScript reader (SqliteReader).

Sequence: VS Code Extension Initialization

TypeScript Node & Edge Types

The TypeScript implementation maps the SQLite snake_case schema to camelCase interfaces for the VS Code environment.

// code-review-graph-vscode/src/backend/sqlite.ts:51-87
export type NodeKind = 'File' | 'Class' | 'Function' | 'Type' | 'Test';

export type EdgeKind =
  | 'CALLS' | 'IMPORTS_FROM' | 'INHERITS' | 'IMPLEMENTS'
  | 'CONTAINS' | 'TESTED_BY' | 'DEPENDS_ON';

export interface GraphNode {
  id: number;
  kind: NodeKind;
  qualifiedName: string;
  filePath: string;
  lineStart: number | null;
  isTest: boolean;
}

Performance & Optimization

To handle thousands of nodes and edges effectively, the system employs several SQLite-specific optimizations:

1. WAL Mode & Busy Timeout: The database is opened with PRAGMA journal_mode = WAL and a busy timeout of 5000ms to allow concurrent reads during backend write operations.
2. Strategic Indexing: Indexes are maintained on qualified_name, file_path, and composite indexes on edges (e.g., idx_edges_composite on kind, source, target, file_path, line) to speed up upserts and impact radius lookups.
3. FTS5 Search: A virtual table using the Porter stemmer provides fast, keyword-based lookup for code entities across large projects.

Sources: code-review-graph-vscode/src/backend/sqlite.ts:133-134, code_review_graph/migrations.py:112-114, code_review_graph/migrations.py:180-185

Summary

The SQLite Graph Storage and Migration system provides a robust, versioned data layer for code structural analysis. By using a shared SQLite database with a strict migration path, the project enables high-performance synchronization between Python-based analysis tools and the VS Code user interface, maintaining structural integrity across codebase updates.

Sources: CLAUDE.md:31-34, code-review-graph-vscode/src/backend/sqlite.ts:133-167