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

> Discover how OmniRoute's custom runner expertly manages 154 SQLite migrations. Learn about transactional execution, automatic backups, and data preservation with IS4.

- Repository: [Diego Rodrigues de Sa e Souza/OmniRoute](https://github.com/diegosouzapw/OmniRoute)
- Tags: deep-dive
- Published: 2026-08-19

---

**OmniRoute handles 154 SQLite migrations through a custom migration runner in [`src/lib/db/migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts) with a **read-only probe** that validates the existing `storage.sqlite` file before any write operations occur.

```typescript
// 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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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:

```sql
-- 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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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

```typescript
// 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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/optimizationSettings.ts) reapply `PRAGMA` tuning (cache size, `mmap_size`, etc.)
- **Legacy JSON migration** — `migrateFromJson()` converts any remaining [`db.json`](https://github.com/diegosouzapw/OmniRoute/blob/main/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:

```typescript
// 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:

```sql
-- 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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts) | Singleton orchestration, probe logic, schema creation, critical-state management |
| [`src/lib/db/migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/healthCheck.ts) | Integrity verification and auto-repair |
| [`src/lib/db/optimizationSettings.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/migrationRunner.ts)
- **Startup probe logic** in [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/migrationRunner.ts) and invoke it with an existing database instance:

```typescript
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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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.