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

> Learn to migrate SQLite schema versions for code-review-graph. Trigger manual SQLite schema migrations using run_migrations function for testing or debugging.

- Repository: [Tirth Kanani/code-review-graph](https://github.com/tirth8205/code-review-graph)
- Tags: migration-guide
- Published: 2026-07-18

---

**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`](https://github.com/tirth8205/code-review-graph/blob/main/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`](https://github.com/tirth8205/code-review-graph/blob/main/migrations.py)

All migration logic resides in [`code_review_graph/migrations.py`](https://github.com/tirth8205/code-review-graph/blob/main/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`](https://github.com/tirth8205/code-review-graph/blob/main/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`](https://github.com/tirth8205/code-review-graph/blob/main/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`

```python
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

```python
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)

```python
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`](https://github.com/tirth8205/code-review-graph/blob/main/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`](https://github.com/tirth8205/code-review-graph/blob/main/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`](https://github.com/tirth8205/code-review-graph/blob/main/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:

```python
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`](https://github.com/tirth8205/code-review-graph/blob/main/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.