How OmniRoute Uses SQLite for State Management with Migrations

OmniRoute stores all runtime state in a single SQLite database and manages schema evolution through a version-controlled migration runner that applies changes transactionally with safety checks and automatic backups.

OmniRoute relies on SQLite as its single source of truth for mutable state, including provider credentials, combo definitions, and usage logs. The platform implements a robust migration system that ensures schema changes are applied exactly once across fresh and existing installations without data loss. According to the diegosouzapw/OmniRoute source code, all database access is centralized through a single connection helper, while the migration runner handles versioning, collision detection, and rollback safety.

Centralized Database Access via getDbInstance()

All modules in OmniRoute interact with state through a shared database instance provided by getDbInstance() in src/lib/db/core.ts. This helper returns a single SQLite connection used throughout the application, enforcing a hard rule that no module issues raw SQL outside the src/lib/db/ layer.

This centralized approach ensures connection pooling consistency and prevents schema fragmentation. When a module needs to read or write state, it imports the helper:

import { getDbInstance } from "@/lib/db/core";

const db = getDbInstance(); // All modules use this same connection

export function sumUsageTokensThisMonth(
  db: SqliteAdapter = getDbInstance()
): number {
  const row = db
    .prepare(
      `SELECT SUM(tokens) AS total FROM usage_history WHERE strftime('%Y-%m', timestamp) = strftime('%Y-%m','now')`
    )
    .get();
  return row?.total ?? 0;
}

Migration File Structure and Discovery

Schema changes are defined as versioned SQL files stored in src/lib/db/migrations/. Each file follows the strict naming pattern NNN_description.sql (for example, 001_initial_schema.sql or 032_api_key_lifecycle.sql).

The getMigrationFiles() function in src/lib/db/migrationRunner.ts scans this directory, sorts files by their numeric prefix, and returns an ordered list of pending migrations. This discovery mechanism ensures that migrations execute in sequential order based on file naming rather than timestamps, preventing out-of-sequence execution.

// Migration files are loaded from src/lib/db/migrations/
// Example: 001_initial_schema.sql

-- src/lib/db/migrations/001_initial_schema.sql
CREATE TABLE IF NOT EXISTS provider_nodes (
  id TEXT PRIMARY KEY,
  provider TEXT NOT NULL,
  token TEXT,
  max_concurrent INTEGER DEFAULT 10
);
-- … additional schema definitions …

Schema Version Tracking with _omniroute_migrations

OmniRoute maintains a persistent record of applied schema versions using an internal tracking table named _omniroute_migrations. On first run, the ensureMigrationsTable() function creates this table if it does not exist, storing the version number, migration name, and timestamp for each applied change.

This SQLite-based ledger allows the system to compute exactly which migrations remain pending by comparing the tracked versions against files on disk. The tracking mechanism ensures migrations run exactly once, even if the server restarts during the process.

The Migration Execution Pipeline

When the server starts, runMigrations() (called via ensureDbInitialized() in the core module) orchestrates the full migration lifecycle. This pipeline operates in distinct phases to ensure safe schema evolution:

1. Ensure tracking table exists – Creates _omniroute_migrations if missing.
2. Detect version collisions – Scans for duplicate numeric prefixes and throws warnings if found.
3. Handle legacy renames – Processes RENAMED_MIGRATION_COMPATIBILITY and LEGACY_VERSION_SLOT_MIGRATIONS to support upgraded databases without losing version history.
4. Compute pending migrations – Compares tracked versions against disk files to identify gaps.

// Example: Run migrations at startup (called from the initialization flow)
import { runMigrations } from "@/lib/db/migrationRunner";

runMigrations(db, { isNewDb: false });

Safety Mechanisms and Data Protection

The migration runner implements multiple safety checks to prevent data loss. Before any schema changes occur, the system creates a pre-migration backup using SQLite's VACUUM INTO command (unless the database is brand new). This creates a complete, restorable snapshot of the database state.

The runner also protects against the mass-migration abort scenario. If an existing database suddenly presents too many pending migrations (indicating a likely wiped tracking table), the process aborts with a MigrationSafetyAbortError instead of blindly executing potentially destructive schema changes on an existing populated database.

Version collision detection (lines 39-48 in migrationRunner.ts) prevents duplicate numeric prefixes that could cause ambiguous execution order.

Transactional Migration Execution

Each migration executes within a single SQLite transaction (db.transaction()), guaranteeing all-or-nothing application per file. If any statement in a migration file fails, the entire transaction rolls back, leaving the database in its pre-migration state.

For complex migrations requiring procedural logic beyond raw SQL, OmniRoute supports special-case handlers. For example, version 032 uses applyApiKeyLifecycleMigration() to programmatically add columns and indexes:

function applyApiKeyLifecycleMigration(db: SqliteAdapter): void {
  ensureColumn(db, "api_keys", "revoked_at", "ALTER TABLE api_keys ADD COLUMN revoked_at TEXT");
  // … additional column modifications …
  db.exec(`
    CREATE INDEX IF NOT EXISTS idx_api_keys_revoked_at ON api_keys(revoked_at);
  `);
}

After successful execution, the migration version is recorded in _omniroute_migrations before the transaction commits.

Diagnostic and Administrative APIs

The migration system exposes getMigrationStatus(db), which returns both applied and pending migrations for monitoring or administrative interfaces. This function enables operators to verify schema state before deployments and diagnose version drift between environments.

Summary

• Centralized access: All state operations route through getDbInstance() in src/lib/db/core.ts to ensure consistent SQLite connection handling.
• Version tracking: The _omniroute_migrations table provides a durable ledger of schema changes, enabling idempotent migration runs.
• Safety first: Pre-migration backups via VACUUM INTO and mass-migration aborts prevent accidental data loss on production databases.
• Transactional integrity: Each migration runs inside a SQLite transaction, ensuring atomic schema changes.
• Flexible handlers: Complex migrations can use TypeScript helper functions like applyApiKeyLifecycleMigration() while maintaining version history.

Frequently Asked Questions

How does OmniRoute prevent duplicate migration executions?

OmniRoute records every successfully applied migration in the _omniroute_migrations tracking table with its version number and timestamp. When runMigrations() executes, it computes the difference between files on disk and versions stored in this table, running only the missing entries. This stateful approach ensures migrations execute exactly once even if the server restarts or the process crashes mid-run.

What happens if migration files are renamed or deleted?

The migration runner handles renamed or legacy migrations through compatibility mappings defined as RENAMED_MIGRATION_COMPATIBILITY and LEGACY_VERSION_SLOT_MIGRATIONS. These configurations allow the system to recognize that a migration previously tracked under an old name corresponds to a new file, preventing version drift when upgrading older databases that may have tracked migrations under previous naming conventions.

How does the system protect against accidental data loss during migrations?

Before applying any changes to an existing database, the runner creates a backup using SQLite's VACUUM INTO command. Additionally, if the system detects an unusually high number of pending migrations on an existing database (suggesting a corrupted or wiped tracking table), it aborts with a MigrationSafetyAbortError rather than risk applying destructive schema changes to production data.

Can I check which migrations have been applied without running them?

Yes. The getMigrationStatus(db) function in src/lib/db/migrationRunner.ts returns a diagnostic object containing both the list of applied migrations (from _omniroute_migrations) and pending migrations (computed from disk files). This allows administrators to inspect schema state, verify version alignment across environments, and preview upcoming changes before executing them.

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