OmniRoute SQLite Database Schema: Domain Modules and Migration Architecture

OmniRoute stores all state in a single SQLite file (~/.omniroute/omniroute.db) organized into 17+ base tables and 110 migration files, with type-safe access provided by domain modules under src/lib/db/.

The OmniRoute AI routing platform persists configuration, provider metadata, and runtime state in a local SQLite database. Understanding the OmniRoute SQLite database schema is essential for customizing deployments, debugging routing decisions, or extending the platform with custom domain logic. The schema is bootstrapped by src/lib/db/core.ts and evolved through a strict migration system that maintains idempotent upgrades.

Core Database Architecture

OmniRoute initializes its database at ~/.omniroute/omniroute.db by default. The bootstrap process in src/lib/db/core.ts creates 17 base tables plus a special bookkeeping table named _omniroute_migrations. This foundation supports the entire routing stack, from API key management to vector memory storage.

The database uses better-sqlite3 as the underlying driver, and the singleton instance is accessible via getDbInstance() exported from src/lib/db/core.ts. All schema modifications occur through SQL migration files located in src/lib/db/migrations/, executed by src/lib/db/migrationRunner.ts.

Domain Modules and Table Organization

Each feature of OmniRoute maps to a dedicated domain module under src/lib/db/ that provides type-safe TypeScript APIs for specific tables. The schema currently comprises over 30 tables organized into functional groups.

API Key Management

Authentication and authorization tables are managed by the API key domain modules:

• api_keys – Stores API key definitions with encrypted secrets. Accessed via src/lib/db/apiKeys.ts.
• api_key_groups – Groups keys for quota sharing and organizational policies. Accessed via src/lib/db/apiKeyGroups.ts.
• api_key_usage_limit_fields – Defines per-key rate limits and token caps. Accessed via src/lib/db/apiKeyUsageLimitFields.ts.
• api_key_context_sources – Tracks which request fields participate in context hashing. Accessed via src/lib/db/apiKeyContextSources.ts.
• api_key_column_fallbacks – Configures column-level fallbacks for missing data. Accessed via src/lib/db/apiKeyColumnFallbacks.ts.

Provider Infrastructure

The catalog of 237 supported providers and their connection details resides in:

• providers – Master catalog of all provider entries. Accessed via src/lib/db/providers.ts.
• provider_connections – OAuth and credential records per provider. Accessed via src/lib/db/providers.ts.
• provider_limits – Per-model token limits and concurrency caps. Accessed via src/lib/db/providerLimits.ts.

Routing and Combos

Auto-Combo configurations and routing strategies use these tables:

• combos – Auto-Combo routing strategy definitions. Accessed via src/lib/db/combos.ts.
• combo_forecast – Pre-computed cost and risk forecasts for combos. Accessed via src/lib/db/comboForecast.ts.
• model_combo_mappings – Maps models to combos for UI and routing logic. Accessed via src/lib/db/modelComboMappings.ts.
• model_intelligence – Heuristics about model capabilities. Accessed via src/lib/db/modelIntelligence.ts.
• model_context_overrides – Per-model context window overrides. Accessed via src/lib/db/modelContextOverrides.ts.

Compression and Memory

Data processing and vector storage utilize:

• compression_combos – Named compression pipelines (lite, standard, ultra). Accessed via src/lib/db/compressionCombos.ts.
• compression – Global compression settings. Accessed via src/lib/db/compression.ts.
• memory_vec – Vector-search index for the Memory subsystem. Accessed via src/lib/db/memoryVec.ts.

Quota and Usage Tracking

Hierarchical quota allocation relies on:

• quota_snapshots – Historical quota usage per API key. Accessed via src/lib/db/quotaSnapshots.ts.
• quota_pools – Global and pool-level quota allocation. Accessed via src/lib/db/quotaPools.ts.
• quota_groups – Group-level quota definitions. Accessed via src/lib/db/quotaGroups.ts.
• quota_consumption – Tracks actual consumption across the hierarchy.

Extensions and Telemetry

Plugin systems and observability tables include:

• plugins and plugin_metrics – Dynamically loaded extensions and usage statistics. Accessed via src/lib/db/plugins.ts.
• proxy_logs – Audited request/response logs for proxy-based providers. Accessed via src/lib/db/proxyLogs.ts.
• webhooks and webhook_deliveries – Webhook registration and delivery tracking. Accessed via src/lib/db/webhooks.ts.

System and Agent Tables

Advanced routing and protocol support:

• session_account_affinity – Sticky-session mapping for multi-account routing. Accessed via src/lib/db/sessionAccountAffinity.ts.
• inspector_sessions and inspector_custom_hosts – Debug-session plumbing for remote inspection. Accessed via src/lib/db/inspectorSessions.ts.
• agent_bridge_* – Bridge tables for A2A and ACP protocols. Accessed via src/lib/db/agentBridgeState.ts.
• access_tokens – JWT-style tokens for CLI and MCP authentication. Accessed via src/lib/db/accessTokens.ts.
• reasoning_cache – Cached reasoning outputs for strict providers. Accessed via src/lib/db/reasoningCache.ts.

Configuration and Metadata

System-wide settings and encryption:

• version_manager – Migration version metadata and upstream-proxy configuration. Accessed via src/lib/db/versionManager.ts.
• encryption – Helper table for encrypted field storage (e.g., API secret blobs). Accessed via src/lib/db/encryption.ts.
• settings – Global runtime settings including DATA_DIR and feature flags. Accessed via src/lib/db/settings.ts.
• domain_state – Per-domain state used by the router (e.g., combo health). Accessed via src/lib/db/domainState.ts.

Migration System

The schema evolves through 110 migration files stored in src/lib/db/migrations/. The migrationRunner.ts executes these SQL scripts sequentially and records each filename in _omniroute_migrations to ensure idempotent upgrades.

Key migration files include:

• 001_initial_schema.sql – Creates the 17 core tables and the migration registry.
• 042_compression_combos.sql – Adds compression pipeline tables.
• 043_default_compression_combo_pipeline.sql – Establishes default compression modes.
• 088_quota_groups.sql – Introduces quota hierarchy tables.
• 087_quota_pool_connections.sql – Links quota pools to providers.
• 080_agent_bridge.sql – Creates A2A and ACP protocol bridge tables.
• 076_create_plugins.sql – Establishes plugin infrastructure.
• 090_plugin_metrics.sql – Adds plugin usage tracking.
• 083_memory_vec.sql – Creates vector index for Memory subsystem.
• 033_create_reasoning_cache.sql – Adds reasoning output caching.

Migrations are plain SQL containing CREATE TABLE or ALTER TABLE statements. The runner applies transactions automatically, rolling back on failure to maintain schema integrity.

Type-Safe Database Access

TypeScript definitions mapping tables to row types are centralized in src/lib/db/_rowTypes.ts. Domain modules import these types to provide compile-time safety when querying the SQLite database.

The getDbInstance() function in src/lib/db/core.ts returns a singleton better-sqlite3 database instance that all domain modules share. This ensures connection pooling and transaction consistency across the application.

Practical Code Examples

Querying Providers and Quotas

import Database from 'better-sqlite3';
import { getDbInstance } '@/src/lib/db/core';

// Obtain the singleton DB instance
const db = getDbInstance();

// List all registered providers
const providers = db.prepare('SELECT id, name, type FROM providers').all();
console.table(providers);

// Fetch quota snapshot for a specific API key
const apiKeyId = 'abc123';
const quota = db
  .prepare(`
    SELECT used, limit 
    FROM quota_snapshots 
    WHERE api_key_id = ? 
    ORDER BY updated_at DESC 
    LIMIT 1
  `)
  .get(apiKeyId);
console.log(`API key ${apiKeyId} used ${quota.used}/${quota.limit} tokens`);

Running Migrations Manually

# From the repository root

npm run typecheck:core

node --import tsx/esm --eval="
import { runMigrations } from '@/src/lib/db/migrationRunner';
await runMigrations();
"

Inspecting Schema via Health Endpoint

// GET /api/v1/health returns DB version details
fetch('http://localhost:3000/api/v1/health')
  .then(r => r.json())
  .then(info => console.log('DB version:', info.dbVersion));

Summary

• OmniRoute uses a single SQLite file at ~/.omniroute/omniroute.db initialized by src/lib/db/core.ts.
• The schema includes 17 base tables created at bootstrap, expanded to 30+ tables through migrations.
• Domain modules under src/lib/db/ provide type-safe TypeScript APIs for each functional area.
• 110 migration files in src/lib/db/migrations/ handle schema evolution, tracked in _omniroute_migrations.
• Type definitions in src/lib/db/_rowTypes.ts ensure compile-time safety when accessing tables like api_keys, providers, and memory_vec.

Frequently Asked Questions

How do I inspect the OmniRoute SQLite database schema?

Run the SQLite CLI command sqlite3 ~/.omniroute/omniroute.db ".schema" to dump the complete schema. Alternatively, query the _omniroute_migrations table to see which migration files have been applied, or use the /api/v1/health endpoint to retrieve the current database version programmatically.

What are the 17 base tables created by core.ts?

The initial bootstrap creates foundational tables including api_keys, providers, combos, settings, encryption, and the migration registry _omniroute_migrations. These are defined in src/lib/db/migrations/001_initial_schema.sql and loaded by src/lib/db/core.ts on first startup.

How do migrations work in OmniRoute?

The migrationRunner.ts executes SQL files from src/lib/db/migrations/ in alphabetical order. Each file name is recorded in _omniroute_migrations to prevent duplicate execution. This design supports idempotent upgrades, allowing you to safely rerun the bootstrap process or deploy new versions without manual schema changes.

Where are API secrets stored in the schema?

API secrets are stored in the api_keys table with encryption handled by the encryption table helper. The actual secret blobs are encrypted at rest, and the domain module src/lib/db/apiKeys.ts manages the encryption/decryption logic when reading or writing key definitions.

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