# OmniRoute SQLite Database Schema: Domain Modules and Migration Architecture

> Explore the OmniRoute SQLite database schema, including domain modules and its robust migration architecture. Understand how OmniRoute manages state across 17+ tables and 110 migration files for efficient data handling.

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

---

**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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/apiKeys.ts).
- **`api_key_groups`** – Groups keys for quota sharing and organizational policies. Accessed via [`src/lib/db/apiKeyGroups.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/apiKeyUsageLimitFields.ts).
- **`api_key_context_sources`** – Tracks which request fields participate in context hashing. Accessed via [`src/lib/db/apiKeyContextSources.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/apiKeyContextSources.ts).
- **`api_key_column_fallbacks`** – Configures column-level fallbacks for missing data. Accessed via [`src/lib/db/apiKeyColumnFallbacks.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/providers.ts).
- **`provider_connections`** – OAuth and credential records per provider. Accessed via [`src/lib/db/providers.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/providers.ts).
- **`provider_limits`** – Per-model token limits and concurrency caps. Accessed via [`src/lib/db/providerLimits.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/combos.ts).
- **`combo_forecast`** – Pre-computed cost and risk forecasts for combos. Accessed via [`src/lib/db/comboForecast.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/comboForecast.ts).
- **`model_combo_mappings`** – Maps models to combos for UI and routing logic. Accessed via [`src/lib/db/modelComboMappings.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/modelComboMappings.ts).
- **`model_intelligence`** – Heuristics about model capabilities. Accessed via [`src/lib/db/modelIntelligence.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/modelIntelligence.ts).
- **`model_context_overrides`** – Per-model context window overrides. Accessed via [`src/lib/db/modelContextOverrides.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/compressionCombos.ts).
- **`compression`** – Global compression settings. Accessed via [`src/lib/db/compression.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/compression.ts).
- **`memory_vec`** – Vector-search index for the Memory subsystem. Accessed via [`src/lib/db/memoryVec.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/quotaSnapshots.ts).
- **`quota_pools`** – Global and pool-level quota allocation. Accessed via [`src/lib/db/quotaPools.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/quotaPools.ts).
- **`quota_groups`** – Group-level quota definitions. Accessed via [`src/lib/db/quotaGroups.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/plugins.ts).
- **`proxy_logs`** – Audited request/response logs for proxy-based providers. Accessed via [`src/lib/db/proxyLogs.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/proxyLogs.ts).
- **`webhooks`** and **`webhook_deliveries`** – Webhook registration and delivery tracking. Accessed via [`src/lib/db/webhooks.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/sessionAccountAffinity.ts).
- **`inspector_sessions`** and **`inspector_custom_hosts`** – Debug-session plumbing for remote inspection. Accessed via [`src/lib/db/inspectorSessions.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/inspectorSessions.ts).
- **`agent_bridge_*`** – Bridge tables for A2A and ACP protocols. Accessed via [`src/lib/db/agentBridgeState.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/agentBridgeState.ts).
- **`access_tokens`** – JWT-style tokens for CLI and MCP authentication. Accessed via [`src/lib/db/accessTokens.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/accessTokens.ts).
- **`reasoning_cache`** – Cached reasoning outputs for strict providers. Accessed via [`src/lib/db/reasoningCache.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/versionManager.ts).
- **`encryption`** – Helper table for encrypted field storage (e.g., API secret blobs). Accessed via [`src/lib/db/encryption.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/encryption.ts).
- **`settings`** – Global runtime settings including `DATA_DIR` and feature flags. Accessed via [`src/lib/db/settings.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/domainState.ts).

## Migration System

The schema evolves through **110 migration files** stored in `src/lib/db/migrations/`. The [`migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/001_initial_schema.sql)** – Creates the 17 core tables and the migration registry.
- **[`042_compression_combos.sql`](https://github.com/diegosouzapw/OmniRoute/blob/main/042_compression_combos.sql)** – Adds compression pipeline tables.
- **[`043_default_compression_combo_pipeline.sql`](https://github.com/diegosouzapw/OmniRoute/blob/main/043_default_compression_combo_pipeline.sql)** – Establishes default compression modes.
- **[`088_quota_groups.sql`](https://github.com/diegosouzapw/OmniRoute/blob/main/088_quota_groups.sql)** – Introduces quota hierarchy tables.
- **[`087_quota_pool_connections.sql`](https://github.com/diegosouzapw/OmniRoute/blob/main/087_quota_pool_connections.sql)** – Links quota pools to providers.
- **[`080_agent_bridge.sql`](https://github.com/diegosouzapw/OmniRoute/blob/main/080_agent_bridge.sql)** – Creates A2A and ACP protocol bridge tables.
- **[`076_create_plugins.sql`](https://github.com/diegosouzapw/OmniRoute/blob/main/076_create_plugins.sql)** – Establishes plugin infrastructure.
- **[`090_plugin_metrics.sql`](https://github.com/diegosouzapw/OmniRoute/blob/main/090_plugin_metrics.sql)** – Adds plugin usage tracking.
- **[`083_memory_vec.sql`](https://github.com/diegosouzapw/OmniRoute/blob/main/083_memory_vec.sql)** – Creates vector index for Memory subsystem.
- **[`033_create_reasoning_cache.sql`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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

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

```bash

# 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

```typescript
// 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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/migrations/001_initial_schema.sql) and loaded by [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts) on first startup.

### How do migrations work in OmniRoute?

The [`migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/apiKeys.ts) manages the encryption/decryption logic when reading or writing key definitions.