# OmniRoute Database Schema and Migration System: Complete Technical Guide

> Explore the OmniRoute database schema and migration system. Learn about versioned SQLite, incremental runners, and robust production data protection strategies for seamless updates.

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

---

**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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/migrationRunner.ts).

## Migration Runner Architecture

The migration system in [`src/lib/db/migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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:

```sql
-- 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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/schemaColumns.ts):

```typescript
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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts).
- **Schema versioning** relies on the `_omniroute_migrations` table, populated by the runner in [`src/lib/db/migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/migrationRunner.ts).
- **Migration files** follow the [`NNN_description.sql`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/141_add_new_column.sql)) containing an `ALTER TABLE` statement, or add an idempotent check to [`src/lib/db/schemaColumns.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/schemaColumns.ts) to handle interrupted migrations gracefully.