OmniRoute Database Schema and Migration System: Complete Technical Guide

OmniRoute uses a versioned SQLite database with an incremental migration runner that applies numbered SQL files from src/lib/db/migrations/ while protecting production data through pre-migration backups, collision detection, and idempotent schema helpers.

OmniRoute is an open-source routing and proxy layer that persists all runtime configuration, usage history, and call logs in a single SQLite file. Understanding the OmniRoute database schema is essential for operators who need to query provider connections, debug call logs, or extend the system with custom fields. The schema evolves through a safety-first migration runner implemented in TypeScript that automatically upgrades the database on startup while preventing data loss through transactional guards and automatic backups.

Core Schema Architecture

The OmniRoute database schema organizes runtime data into logical domains, all managed through a thin TypeScript wrapper in src/lib/db/core.ts.

Primary Data Tables

Provider connections are stored in the provider_connections table with columns for authentication type (auth_type), rate limiting configuration (rate_limit_protection, rate_limit_overrides_json), quota management (quota_visible, quota_window_thresholds_json), and proxy settings (proxy_enabled, per_key_proxy_enabled).

Usage tracking lives in usage_history, capturing per-request metrics including latency_ms, ttft_ms (time to first token), service_tier, and combo_strategy used for routing decisions. The account_label and account_label_priority columns enable multi-account routing scenarios.

Call logs populate the call_logs table with detailed request metadata including artifact_relpath, artifact_sha256, token accounting (tokens_cache_read, tokens_cache_creation, tokens_reasoning), and pipeline tracking (combo_step_id, combo_execution_key). The correlation_id and session_tag columns support distributed tracing.

Proxy-specific data resides in proxy_logs, which tracks egress_ip assignments for outbound connections.

Configuration and feature flags are stored in the key_value table under the namespace databaseSettings, holding JSON-encoded defaults inserted after migration 46 via the insertDefaultDatabaseSettings function.

Migration Tracking

The _omniroute_migrations table serves as the source of truth for schema versioning. It contains three columns: version (primary key), name, and applied_at (automatically populated with datetime('now')). This table is created and maintained by ensureMigrationsTable() in src/lib/db/migrationRunner.ts.

Migration Runner Architecture

The migration system in src/lib/db/migrationRunner.ts implements a nine-stage safety pipeline that transforms raw SQL files into reliable schema changes.

Discovery and Ordering

The runner begins with resolveMigrationsDir(), which locates the migrations directory and supports environment variable overrides (OMNIROUTE_MIGRATIONS_DIR and OMNIROUTE_EXTRA_MIGRATIONS_DIRS for downstream distributions). Files must follow the naming convention NNN_description.sql, where NNN is a zero-padded version number. The runner extracts version and name via the regex /^(\d+)_(.+)\.sql$/ and sorts them lexicographically.

Collision Detection and Legacy Handling

Before execution, getMigrationFiles() validates that no duplicate numeric prefixes exist unless explicitly listed in SUPERSEDED_DUPLICATE_MIGRATIONS. The system handles schema evolution through two reconciliation mechanisms:

• Renamed migrations: reconcileRenumberedMigrations() uses the RENAMED_MIGRATION_COMPATIBILITY table to map old migration names to new versions without re-applying changes.
• Legacy version slots: rehomeLegacyVersionSlotMigrations() prevents clashes when older migrations occupy version numbers needed by newer schema changes.

Safety Mechanisms

The runner implements multiple guards to protect production databases:

• Mass-migration protection: If an existing database has more than OMNIROUTE_MAX_PENDING_MIGRATIONS (default 50) pending migrations, the process aborts to prevent accidental wipes of the tracking table. Set OMNIROUTE_MAX_PENDING_MIGRATIONS=0 to disable this check.
• Pre-migration backups: createPreMigrationBackup() executes VACUUM INTO to create a complete snapshot before applying changes, unless the database is new or running in a test environment.
• FTS5 detection: The runner detects FTS5 virtual table requirements and handles them appropriately.

Idempotent Execution

Each migration runs inside a transaction. If a migration attempts to add a column that already exists (SQLite error "duplicate column name"), the runner marks that migration as applied and continues. Specific migrations receive custom logic—for example, migration 032 receives special handling for API-key lifecycle columns, while compression receipt migrations execute conditional logic based on the version string.

After successful execution, the runner inserts a row into _omniroute_migrations via standard INSERT statements. The getMigrationStatus() function provides diagnostic visibility by returning applied and pending migration arrays for UI integration or debugging.

Schema Evolution and Safety Mechanisms

Beyond the migration runner, OmniRoute provides idempotent schema helpers in src/lib/db/schemaColumns.ts for hot-fixes and column additions outside the standard migration flow.

Idempotent Column Addition

The ensureProviderConnectionsColumns function demonstrates the defensive pattern used throughout the codebase. It queries PRAGMA table_info to enumerate existing columns, then conditionally executes ALTER TABLE … ADD COLUMN only when the column is absent. This pattern prevents "duplicate column" errors when migrations are interrupted or when columns are added via multiple paths.

Database Bootstrap Process

When code calls getDbInstance() from src/lib/db/core.ts, the system automatically invokes runMigrations(db, { isNewDb }) before returning the database handle. This ensures every application start leaves the schema in the correct state, whether processing a fresh install or upgrading from an older version.

Working with Migrations in Practice

Adding schema changes requires creating a new numbered SQL file and optionally updating the schema helpers.

Adding a Standard Migration

Create a file in src/lib/db/migrations/ with the next available number:

-- 140_add_user_agent.sql
ALTER TABLE call_logs ADD COLUMN user_agent TEXT DEFAULT NULL;

On the next application startup, getDbInstance() will automatically detect and apply this migration.

Hot-Fix Column Addition

For emergency schema changes outside the migration sequence, add idempotent guards to src/lib/db/schemaColumns.ts:

export function ensureCallLogsColumns(db: SqliteDatabase) {
  const columnNames = getColumnNames(db, 'call_logs');
  
  if (!columnNames.has('user_agent')) {
    db.exec('ALTER TABLE call_logs ADD COLUMN user_agent TEXT');
    console.log('[DB] Added call_logs.user_agent column');
  }
}

Environment Configuration

Control migration behavior through these environment variables:

• OMNIROUTE_MIGRATIONS_DIR: Override the default migrations directory path
• OMNIROUTE_EXTRA_MIGRATIONS_DIRS: Additional directories to scan for migration files
• OMNIROUTE_MAX_PENDING_MIGRATIONS: Threshold for mass-migration protection (default 50)

Summary

• OmniRoute uses SQLite as its single-file database, accessed through TypeScript wrappers in src/lib/db/core.ts.
• Schema versioning relies on the _omniroute_migrations table, populated by the runner in src/lib/db/migrationRunner.ts.
• Migration files follow the NNN_description.sql naming convention and are applied transactionally with automatic backup via VACUUM INTO.
• Safety mechanisms include collision detection, renamed migration reconciliation, and a mass-migration abort threshold (OMNIROUTE_MAX_PENDING_MIGRATIONS).
• Idempotent helpers in src/lib/db/schemaColumns.ts allow runtime column addition without full migration cycles.

Frequently Asked Questions

What database does OmniRoute use?

OmniRoute uses SQLite as its sole database engine, storing all data in a single file accessed through the getDbInstance() function in src/lib/db/core.ts. This design simplifies deployment while supporting complex relational data through standard SQL.

How do I add a new column to an existing table in OmniRoute?

Create a new migration file with the next sequential number (e.g., 141_add_new_column.sql) containing an ALTER TABLE statement, or add an idempotent check to src/lib/db/schemaColumns.ts using ensureProviderConnectionsColumns() as a template. The migration runner will apply the change automatically on the next startup.

What happens if a migration fails in OmniRoute?

The migration runner wraps each migration in a transaction, so failures roll back the specific changes. If the error is "duplicate column name," the runner marks the migration as applied and continues. For other errors, the process aborts after logging the failure, leaving the database in its pre-migration state thanks to the transactional safety and the pre-migration backup created by createPreMigrationBackup().

How does OmniRoute prevent database corruption during upgrades?

The system implements multiple safeguards: it creates a backup via VACUUM INTO before migrating (unless in test mode), aborts if more than 50 pending migrations are detected (indicating a possible tracking table wipe), and uses idempotent column checks in schemaColumns.ts to handle interrupted migrations gracefully.

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