How OmniRoute Manages 154 SQLite Database Schema Migrations

OmniRoute uses an incremental SQL migration system with a versioned ledger table (omniroute_migrations) and a TypeScript migration runner that applies numbered scripts transactionally, with safety thresholds and optional FTS5 support.

This article examines how the OmniRoute project handles schema evolution across 154 migrations in its single SQLite database. The implementation follows an IS 4-style versioning model where every schema change is tracked, ordered, and applied deterministically across desktop, server, and Docker environments.

The Migration Architecture

OmniRoute stores all operational data in one SQLite file under the user's data directory. Schema changes propagate through numbered SQL migration files (001_… .sql, 002_… .sql, up to 157_… .sql) that execute in strict lexicographic order.

The migration system centers on two core files:

• src/lib/db/migrationRunner.ts — The orchestrator containing runMigrations()
• src/lib/db/core.ts — Database initialization that triggers migrations automatically

How the Migration Runner Works

In src/lib/db/migrationRunner.ts, the runMigrations() function implements a six-step pipeline:

1. Migration Discovery

The runner reads src/lib/db/migrations/ and sorts files lexicographically. This ensures 010_… executes after 009_…, even with leading zeros.

2. Ledger Tracking

A hidden table omniroute_migrations records every successfully applied version. On startup, the runner queries this table and skips any migrations already present.

3. Transactional Application

Pending migrations execute inside a single SQLite transaction. If any script fails, the entire transaction rolls back and startup aborts. This guarantees atomic schema changes.

4. Safety Thresholds

A configurable limit prevents runaway upgrades:

Environment Variable	Default	Purpose
OMNIROUTE_MAX_PENDING_MIGRATIONS	50	Abort if pending count exceeds threshold

The test migration-safety-abort-6260.test.ts validates this protection.

5. Conditional FTS5 Support

Later migrations depend on SQLite's full-text search extension. The runner detects FTS5 capability at runtime and defers optional scripts when unavailable. This allows databases created on minimal drivers to upgrade later without corruption.

6. Idempotent Design

Migrations check for existing objects before creating them. For example, a column-addition migration first verifies the column doesn't exist, enabling safe re-execution.

Running Migrations Manually

Normally getDbInstance() in src/lib/db/core.ts triggers migrations automatically. For debugging or testing, invoke the runner directly:

import { getDbInstance } from '@/src/lib/db/core';
import { runMigrations } from '@/src/lib/db/migrationRunner';

// Obtain SQLite adapter (creates DB file if missing)
const db = await getDbInstance();

// Apply all pending migrations
const appliedCount = runMigrations(db, { isNewDb: false });
console.log(`Applied ${appliedCount} migrations`);

Inspecting Migration State

Query the ledger directly to verify which versions ran:

const ledgerRows = await db.all<{ version: string }>(
  `SELECT version FROM omniroute_migrations ORDER BY version`,
);
console.log('Applied migrations:', ledgerRows.map(r => r.version));

Creating Fresh Databases for Testing

Force a clean database with the reset option:

const freshDb = await getDbInstance({ reset: true });
runMigrations(freshDb, { isNewDb: true }); // executes 001…157 sequentially

The isNewDb: true flag optimizes for empty databases, though the ledger remains the source of truth.

Key Files and Tests

Path	Role
src/lib/db/migrationRunner.ts	Core orchestrator with runMigrations()
src/lib/db/core.ts	getDbInstance() and automatic migration triggers
src/lib/db/migrations/	157 numbered SQL scripts (e.g., 001_create_plugins.sql, 157_exclusive_connection_leases.sql)
tests/unit/db-migration-runner.test.ts	Unit tests for ordering, idempotency, FTS5 handling
tests/unit/migration-safety-abort-6260.test.ts	Safety threshold validation
tests/unit/db-pre-migration-backup-retention-10421.test.ts	Backup pruning and ledger edge cases

Summary

• 154 migrations are managed through numbered SQL files in src/lib/db/migrations/
• The omniroute_migrations ledger table tracks applied versions persistently
• runMigrations() in src/lib/db/migrationRunner.ts orchestrates discovery, filtering, and transactional execution
• Safety thresholds (OMNIROUTE_MAX_PENDING_MIGRATIONS=50) prevent accidental mass upgrades
• FTS5 conditional logic allows graceful degradation on limited SQLite builds
• Idempotent scripts support re-execution without errors

This design gives OmniRoute deterministic schema evolution across releases and environments while maintaining data integrity through transactional guarantees.

Frequently Asked Questions

How does OmniRoute prevent migrations from running out of order?

The migration runner sorts files lexicographically from src/lib/db/migrations/ and cross-references each against the omniroute_migrations ledger before execution. Only versions absent from the ledger run, and they run in sorted sequence.

What happens if a migration script fails halfway through?

All pending migrations execute within a single SQLite transaction. Any failure triggers a rollback, leaving the schema unchanged and aborting startup. This atomicity prevents partial schema updates.

Can OmniRoute upgrade databases created on systems without FTS5?

Yes. The runner detects FTS5 availability at runtime and defers optional full-text search migrations. The database remains functional and can apply deferred migrations later when moved to an FTS5-capable environment.

Where is the migration version history stored?

The omniroute_migrations table lives inside the same SQLite database file as application data. This co-location ensures version tracking travels with the database across backups and migrations.

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