How OmniRoute's SQLite Database Migration System Handles 154 Migrations: A Deep Dive into IS4 (Incremental Schema v4)

OmniRoute handles 154 SQLite migrations through a custom migration runner in src/lib/db/migrationRunner.ts that executes numbered SQL files transactionally while preserving critical data through automatic backup and restore mechanisms.

The OmniRoute routing platform maintains database schema evolution through a robust, self-healing migration system. Rather than relying on external ORM tools, the project implements a lightweight IS4 (Incremental Schema v4) approach centered on plain SQL files and a TypeScript runner. This article examines how diegosouzapw/OmniRoute manages 154 incremental migrations while protecting against corruption, OOM failures, and schema drift.

Startup Probe and Database Sanity Checks

Every database connection begins in src/lib/db/core.ts with a read-only probe that validates the existing storage.sqlite file before any write operations occur.

// core.ts – initial probe with readonly connection
const probeDb = openSqliteDatabase(sqliteFile, { readonly: true });

The probe evaluates three failure categories:

• Native driver unavailable — If better-sqlite3 fails to load (isNativeSqliteLoadError), the system throws immediately to prevent silent degradation
• Out-of-memory errors — OOM conditions increment a global counter (__omnirouteDbOomFailureCount) and suggest --max-old-space-size adjustment; retries cap at 3 attempts
• Transient corruption — The retryProbeIfTransient function attempts recovery; persistent failures trigger a backup rename to storage.sqlite.probe-failed-<timestamp> and critical table extraction via captureCriticalDbState()

This probe logic appears around lines 78-112 of core.ts, ensuring no destructive operation touches an unverified database file.

Automatic Recovery from Probe-Failed Backups

When the system detects previously renamed database files through listProbeFailureBackups(), it attempts ordered restoration of the most recent backup. The restoration enforces a three-attempt limit before aborting with a descriptive error, preventing infinite recovery loops that could mask underlying storage problems.

Legacy Schema Detection and Migration

Older OmniRoute releases used a schema_migrations table for version tracking. The probe handles this legacy state:

Condition	Action
Table exists with data	Drop table, preserve all user data, continue with IS4
Table exists but empty	Rename entire DB to <basename>.old-schema, create fresh database

This cleanup ensures the 154 current migrations build from a clean IS4 foundation without orphan tables interfering with ledger queries.

Critical-Table Data Preservation

Before any destructive database operation, captureCriticalDbState() iterates CRITICAL_DB_TABLES — a whitelist including provider_connections, key_value, combos, and other configuration tables. The function extracts rows up to a configurable limit, storing them for later restoration.

After database replacement, restoreCriticalDbState() re-inserts captured rows, ensuring zero loss of provider configurations even when schema changes prevent direct table migration.

Baseline Schema and Migration Ledger

Regardless of database state, OmniRoute executes the inline SCHEMA_SQL constant to establish current table structures. Immediately following, it creates the IS4 ledger table:

-- Executed during every startup in core.ts
CREATE TABLE IF NOT EXISTS _omniroute_migrations (
  version TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  applied_at TEXT NOT NULL DEFAULT (datetime('now'))
);

INSERT OR IGNORE INTO _omniroute_migrations (version, name)
VALUES ('001', 'initial_schema');

The 001 entry represents the inline schema baseline — the state before any numbered migration files execute. All 153 subsequent migrations reference versions 002 through 154.

The Migration Runner: Executing 154 Incremental Upgrades

The runMigrations() function in src/lib/db/migrationRunner.ts orchestrates schema progression through five deterministic steps:

1. Read ledger — Query _omniroute_migrations for applied versions
2. Discover files — List src/lib/db/migrations/*.sql in lexical order
3. Filter pending — Skip files whose numeric prefix exists in ledger
4. Transactional execution — Run each pending migration inside db.transaction(() => …)
5. Record success — Insert version and filename into ledger upon commit

// migrationRunner.ts – core migration execution
export function runMigrations(
  db: Database,
  options: { isNewDb: boolean }
): void {
  const ledger = db.prepare(
    'SELECT version FROM _omniroute_migrations'
  ).pluck().all() as string[];
  
  const pending = listMigrationFiles().filter(
    file => !ledger.includes(extractVersion(file))
  );
  
  for (const file of pending) {
    const sql = readFileSync(file, 'utf-8');
    const version = extractVersion(file);
    
    db.transaction(() => {
      db.exec(sql);
      db.prepare(
        'INSERT INTO _omniroute_migrations (version, name) VALUES (?, ?)'
      ).run(version, basename(file));
    })();
  }
}

Transaction boundaries guarantee atomicity — if migration 147 fails, versions 148-154 remain unapplied and the database rolls back to its pre-147 state. The error propagates to startup logic, potentially triggering the critical-state backup flow.

Post-Migration Housekeeping

After the 154 migrations complete, OmniRoute performs several maintenance operations:

• Index verification — ensureUsageHistoryAccountIndex() and ensureProviderConnectionsColumns() create missing indexes required by current code paths
• Optimization reapplication — Settings from src/lib/db/optimizationSettings.ts reapply PRAGMA tuning (cache size, mmap_size, etc.)
• Legacy JSON migration — migrateFromJson() converts any remaining db.json files to SQLite tables
• Health check execution — runDbHealthCheck() validates integrity unless explicitly disabled

Database Reset and Recovery APIs

OmniRoute exposes explicit lifecycle functions for testing and administrative use:

// Force complete database reload
import { resetDbInstance } from '@/lib/db/core';
resetDbInstance(); // Clears singleton, invalidates cache
const db = getDbInstance(); // Fresh load with all 154 migrations

The closeDbInstance({ checkpointMode }) variant optionally performs WAL checkpointing before closure, ensuring durable commits during migration debugging or backup operations.

Example Migration File Structure

Each of the 154 migrations follows a consistent naming and content pattern:

-- src/lib/db/migrations/124_generic_session_affinity_ttl.sql
-- Adds session affinity TTL to provider connections

ALTER TABLE provider_connections ADD COLUMN session_affinity_ttl_ms INTEGER DEFAULT 0;

UPDATE provider_connections SET session_affinity_ttl_ms = 0 WHERE session_affinity_ttl_ms IS NULL;

The numeric prefix 124 corresponds to the ledger entry. Descriptive suffixes assist debugging without affecting execution order. The runner discovers this file automatically and applies it only if version 124 is absent from _omniroute_migrations.

Key Files in the Migration System

File	Purpose
src/lib/db/core.ts	Singleton orchestration, probe logic, schema creation, critical-state management
src/lib/db/migrationRunner.ts	Migration discovery, transactional execution, ledger updates
src/lib/db/migrations/*.sql	154 numbered schema increment files
src/lib/db/healthCheck.ts	Integrity verification and auto-repair
src/lib/db/optimizationSettings.ts	Performance tuning persistence

Summary

• OmniRoute's SQLite migration system uses a custom IS4 (Incremental Schema v4) runner rather than external migration tools
• 154 migrations live as plain SQL files in src/lib/db/migrations/, executed transactionally by runMigrations() in src/lib/db/migrationRunner.ts
• Startup probe logic in src/lib/db/core.ts validates database health before writes, with automatic backup and critical-table preservation on corruption
• The _omniroute_migrations ledger tracks applied versions, enabling idempotent migration runs and skip-ahead optimization
• Atomic transactions ensure failed migrations roll back cleanly, leaving the database consistent for retry or restore
• Post-migration housekeeping recreates indexes, reapplies optimizations, and runs health checks to guarantee production readiness

Frequently Asked Questions

How does OmniRoute know which migrations have already been applied?

The system queries the _omniroute_migrations table — created automatically during startup — which stores each applied version as a primary key alongside the filename and timestamp. The runMigrations() function filters the discovered SQL files against this ledger, executing only pending versions in lexical order.

What happens if a migration fails halfway through?

Each migration executes inside a better-sqlite3 transaction boundary. If execution throws, the transaction rolls back completely, leaving the database in its pre-migration state. The error bubbles to startup logic, which may trigger critical-table backup and database rename procedures depending on failure classification.

Can I manually run migrations without restarting the server?

Yes. Import runMigrations from src/lib/db/migrationRunner.ts and invoke it with an existing database instance:

import { runMigrations } from '@/lib/db/migrationRunner';
runMigrations(db, { isNewDb: false });

This is useful in development when adding new migration files or testing migration logic without full process restart.

Where does the "154" migration count come from if the baseline is version 001?

The count includes the inline schema baseline (001) plus 153 numbered SQL files in src/lib/db/migrations/. The baseline establishes initial tables through the SCHEMA_SQL constant in core.ts, while files 002_*.sql through 154_*.sql provide incremental schema evolution. The _omniroute_migrations ledger records all 154 versions for complete provenance tracking.

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