How to Migrate from Older Schema Versions to the Current SQLite Schema in Code-Review-Graph

The code-review-graph repository automatically upgrades its SQLite database schema when you open a connection via GraphStore, but you can also trigger migrations manually using the run_migrations function in migrations.py for testing or debugging purposes.

The code-review-graph project maintains its knowledge graph in a SQLite database whose schema evolves through numbered versions. When you initialize a GraphStore instance, the system detects the current schema version stored in the metadata table and applies any pending migrations to reach the current version (currently 9). This design ensures that older databases remain compatible with new code releases without manual intervention.

Understanding the Migration Architecture

The migration system consists of three core components that work together to ensure safe, incremental schema upgrades.

The Migrations Registry in migrations.py

All migration logic resides in code_review_graph/migrations.py. This module exports a MIGRATIONS dictionary that maps version numbers to migration functions (e.g., _migrate_v2 through _migrate_v9). Each function is idempotent: it checks for existing tables or columns before attempting to alter the schema, using guards like CREATE TABLE IF NOT EXISTS and custom _has_column checks.

Automatic Migration via GraphStore

When you instantiate GraphStore from code_review_graph/graph.py, the __init__ method (lines 55‑75) automatically handles the upgrade path. It creates the SQLite connection, ensures the metadata table exists, initializes fresh databases at schema_version = 1, and invokes run_migrations to bring the database to LATEST_VERSION.

The Metadata Table

The metadata table stores a single row with key = 'schema_version' and an integer value. The helper functions get_schema_version and _set_schema_version (lines 16‑38 of migrations.py) read and write this value. If the table is missing, get_schema_version returns 0; if the key is missing, it returns 1.

How the Migration Process Works

The migration flow follows a strict three-step sequence to ensure data integrity:

1. Detect Current Version – get_schema_version queries the metadata table to determine the starting point.
2. Execute Pending Migrations – run_migrations iterates through the sorted MIGRATIONS map, skipping versions ≤ the current version. Each migration runs in its own transaction.
3. Update Version Tracking – After each successful migration, _set_schema_version updates the metadata table to reflect the new version.

When the loop completes, the database is guaranteed to be at LATEST_VERSION (currently 9). Because every migration function includes idempotency checks, re-running run_migrations on an up-to-date database is completely safe and results in no changes.

Migration Paths and Upgrade Scenarios

Depending on your database's current state, the system follows different upgrade paths:

• Fresh Installation (Version 0) – If no metadata table exists, run_migrations creates the base schema via _init_schema, inserts schema_version = 1, then sequentially applies versions 2 through 9.
• Version 1 Upgrade – Databases at version 1 (base tables only) receive additions including the signature column, flows table, communities table, FTS5 virtual table, and summary tables.
• Version 5 to Current – Databases at version 5 skip the FTS5 and early schema changes, applying only versions 6‑9, which introduce community/flow summary tables, risk indexes, compound edge indexes, and edge confidence columns.
• Version 9 (Latest) – If get_schema_version returns 9, run_migrations exits immediately without executing any SQL.

Running Migrations Manually

While automatic migration covers most use cases, you might need manual control in these scenarios:

• Testing – Create a database at a specific older version using fixtures, then call run_migrations to verify upgrade logic.
• Partial Upgrades – Apply specific migrations individually (e.g., _migrate_v6) rather than the full sequence.
• Debugging – Step into a specific migration function to inspect schema state after a failure.

Automatic Upgrade via GraphStore

from pathlib import Path
from code_review_graph.graph import GraphStore

# Initialize the store — migrations run automatically on first open

db_path = Path("/tmp/crg.db")
with GraphStore(db_path) as store:
    # The DB is now at the latest schema version

    version = store._conn.execute(
        "SELECT value FROM metadata WHERE key='schema_version'"
    ).fetchone()["value"]
    print(f"Current schema version: {version}")

Manual Migration of an Existing Database

import sqlite3
from code_review_graph.migrations import run_migrations, get_schema_version

# Open an existing (possibly older) SQLite file

conn = sqlite3.connect("/tmp/old_crg.db")
print("Before migration – version:", get_schema_version(conn))

# Apply all pending migrations

run_migrations(conn)

print("After migration – version:", get_schema_version(conn))
conn.close()

Applying a Single Migration (Advanced)

import sqlite3
from code_review_graph.migrations import _migrate_v6

conn = sqlite3.connect("/tmp/partial.db")

# Apply only version 6 (e.g., adding summary tables) to a version 5 database

_migrate_v6(conn)
conn.commit()

Safety Mechanisms and Guarantees

The migration system implements multiple safeguards to prevent data corruption:

• Transaction Isolation – Each migration runs inside its own transaction. If an error occurs, conn.rollback() ensures the database remains at the previous valid version.
• Audit Logging – The module's logger reports each migration step (e.g., "Migration v4: added 'community_id' column to nodes"), creating a traceable history of schema changes.
• Idempotent Design – Migration functions use IF NOT EXISTS clauses and column-existence checks, making the process safe for CI pipelines where multiple workers might attempt concurrent upgrades.

Summary

• Automatic migrations occur when you instantiate GraphStore from code_review_graph/graph.py, which calls run_migrations during initialization.
• Version tracking relies on the metadata table and the get_schema_version function in code_review_graph/migrations.py.
• Incremental upgrades move sequentially through the MIGRATIONS registry from the current version to LATEST_VERSION (currently 9).
• Idempotent functions ensure that re-running migrations on an up-to-date database causes no errors or side effects.
• Manual control is available via run_migrations or individual _migrate_vX functions for testing and debugging scenarios.

Frequently Asked Questions

What happens if a migration fails midway through the process?

If a single migration fails, the transaction for that specific version rolls back, leaving the database at the last successfully applied version. The run_migrations function catches exceptions, performs conn.rollback(), and re-raises the error. You can then inspect the schema state, fix the underlying issue, and safely re-run run_migrations to continue from the failed version.

Can I downgrade from the current SQLite schema to an older version?

The migration system in code_review_graph/migrations.py does not provide downgrade functions. The MIGRATIONS dictionary only maps versions to upgrade logic. To revert to an older schema, you must restore from a database backup taken before the upgrade, as the codebase does not implement reverse migrations.

How do I check the current schema version without running migrations?

Call get_schema_version directly with an active connection. This function reads the metadata table and returns an integer (0 if the table is missing, 1 if the key is absent, or the current version number). Unlike run_migrations, this is a read-only operation that never modifies the schema:

from code_review_graph.migrations import get_schema_version
import sqlite3

conn = sqlite3.connect("/tmp/crg.db")
print(get_schema_version(conn))  # Returns integer like 5 or 9

Are the migrations safe to run multiple times on the same database?

Yes. Every migration function in migrations.py is designed to be idempotent. They use CREATE TABLE IF NOT EXISTS, ALTER TABLE … ADD COLUMN guarded by _has_column checks, and CREATE INDEX IF NOT EXISTS. Running run_migrations on a database already at version 9 exits immediately after detecting the current version, performing zero schema changes.

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