--- title: "Database Schema & Operations Guide" version: 3.8.40 lastUpdated: 2026-06-28 --- # Database Schema & Operations Guide > **TL;DR**: OmniRoute uses **SQLite with WAL journaling** as its primary store, with **AES-256-GCM** encryption at rest for sensitive fields. This guide covers the schema, migrations, backup/recovery, and operational runbooks. **Sources:** - `src/lib/db/core.ts` — singleton + SCHEMA_SQL (17 base tables) - `src/lib/db/migrationRunner.ts` — versioned migrations - `src/lib/db/migrations/` — 106 versioned SQL files - `src/lib/db/encryption.ts` — encryption helpers - `src/lib/db/backup.ts` — backup export/import - `src/lib/db/healthCheck.ts` — health diagnostics --- ## Why SQLite? OmniRoute chose SQLite over PostgreSQL/MySQL for several reasons: | Factor | SQLite | PostgreSQL | | --------------- | --------------------------------- | --------------------------------- | | **Deployment** | Embedded — no separate server | Requires server setup | | **Encryption** | Application-layer (AES-256-GCM) | Built-in TDE | | **Performance** | Faster for small/medium workloads | Better for huge concurrent writes | | **Concurrency** | WAL mode allows concurrent reads | Full MVCC | | **Backup** | Single-file copy | `pg_dump` or filesystem snapshot | | **Use case** | Per-user install, embedded | Multi-tenant SaaS | For **single-user, single-instance** deployments (the primary OmniRoute use case), SQLite is simpler and faster. ### WAL Journaling `core.ts` opens the database with **WAL (Write-Ahead Logging) mode**: ```ts // src/lib/db/core.ts db.pragma("journal_mode = WAL"); db.pragma("busy_timeout = 2000"); db.pragma("synchronous = NORMAL"); // Settings > System & Storage > Cache Size is applied as KiB. db.pragma("cache_size = -16384"); ``` WAL allows **concurrent reads** during writes — important for the dashboard, which queries while requests are being recorded. --- ## Database Location The SQLite file is stored at: | OS | Path | | ------- | -------------------------------------------------------- | | Linux | `~/.omniroute/storage.sqlite` | | macOS | `~/.omniroute/storage.sqlite` | | Windows | `%USERPROFILE%\.omniroute\storage.sqlite` | | Docker | `/app/data/storage.sqlite` (configurable via `DATA_DIR`) | Companion files: - `storage.sqlite-wal` — write-ahead log - `storage.sqlite-shm` — shared memory file - `call_logs/` — request payload artifacts (if enabled) **Override the location:** ```bash DATA_DIR=/custom/path omniroute ``` --- ## Domain Module Architecture OmniRoute's database has **94 domain modules** in `src/lib/db/`. Each module: - Owns one or more specific tables - Exports typed CRUD functions - Never touches another module's tables - Uses `getDbInstance()` from `core.ts` to access the DB ### The 94 DB Modules OmniRoute has **94 module files** in `src/lib/db/`. Below is a sampling of core modules; see the directory listing for the complete list: | Module | Tables | Responsibility | | ----------------------- | -------------------------------------------------------------- | ------------------------------------------------------------------------- | | `providers.ts` | `provider_connections` | OAuth/API key provider registration and credentials | | `models.ts` | `key_value` (model data) | Model definitions, capabilities, pricing | | `combos.ts` | `combos` | Combo routing configs and ordering | | `apiKeys.ts` | `api_keys` | API key lifecycle, scopes, quota tracking | | `settings.ts` | `key_value`, `api_keys`, `combos` | System configuration and shared KV store | | `backup.ts` | — | Backup export/import operations | | `proxies.ts` | `proxy_registry`, `proxy_assignments`, `provider_connections` | Proxy configs and routing rules | | `prompts.ts` | `prompt_templates` | Reusable prompt templates, versioning | | `webhooks.ts` | `webhooks` | Event-driven webhook subscriptions and logs | | `detailedLogs.ts` | `request_detail_logs` | Per-request audit logging (optional, high volume) | | `domainState.ts` | `domain_*` (5 tables) | Domain budgets, circuit breakers, lockouts, fallback chains, cost history | | `registeredKeys.ts` | `registered_keys`, `account_key_limits`, `provider_key_limits` | Whitelisted API keys for MCP/A2A | | `quotaSnapshots.ts` | `quota_snapshots` | Historical quota usage | | `modelComboMappings.ts` | `model_combo_mappings` | Map models to combo defaults | | `cliToolState.ts` | `cli_tool_state` | CLI-specific persistent state | | `encryption.ts` | — | Helpers for encrypting/decrypting fields | | `readCache.ts` | — | In-memory cache for read-heavy ops | | `secrets.ts` | `key_value` (encrypted entries) | Encrypted secret storage | | `stateReset.ts` | — | Wipe/reset DB state for testing | | `contextHandoffs.ts` | `context_handoffs` | Session context for agent handoff | | `usage*.ts` | `usage_history`, `call_logs`, `proxy_logs` | Usage tracking | | `compression*.ts` | `compression_settings`, `compression_combos` | Compression config | ### Module Boundaries A core architectural rule: **modules don't access each other's tables directly**. To work with another module's data, import the function from that module. ```ts // ❌ WRONG: direct SQL from another module db.prepare("SELECT * FROM provider_connections").all(); // ✅ RIGHT: use the providers module function import { listProviders } from "@/lib/db/providers"; const providers = await listProviders(); ``` This rule is enforced by code review — there's no static check, but violations are flagged. --- ## Base Schema (17 tables) `core.ts` defines the 17 base tables in `SCHEMA_SQL`. These are created by migration `001_initial_schema.sql` and form the core schema. ### Core Tables (created in initial migration) | Table | Purpose | Key columns | | -------------------------- | -------------------------------- | ----------------------------------------------------------------------- | | `provider_connections` | Provider credentials (encrypted) | `id`, `provider`, `auth_type`, `api_key`, `is_active` | | `provider_nodes` | Provider node routing info | `id`, `type`, `name`, `base_url`, `created_at` | | `key_value` | General KV store | `namespace`, `key`, `value` | | `combos` | Routing combo definitions | `id`, `name`, `data`, `sort_order` | | `api_keys` | API keys for the gateway | `id`, `name`, `key`, `machine_id`, `allowed_models` | | `db_meta` | Database metadata | `key`, `value` | | `usage_history` | Request usage records | `id`, `provider`, `model`, `tokens_input`, `tokens_output`, `timestamp` | | `call_logs` | Request payloads & responses | `id`, `timestamp`, `status`, `model`, `provider`, `latency_ms` | | `proxy_logs` | Proxy request logs | `id`, `timestamp`, `proxy_type`, `status`, `provider` | | `domain_fallback_chains` | Model-to-provider chains | `model`, `chain` | | `domain_budgets` | Per-domain spend budgets | `api_key_id`, `daily_limit_usd`, `warning_threshold`, `reset_interval` | | `domain_budget_reset_logs` | Budget reset history | `id`, `api_key_id`, `reset_interval`, `previous_spend`, `reset_at` | | `domain_cost_history` | Per-domain cost tracking | `id`, `api_key_id`, `cost`, `timestamp` | | `domain_lockout_state` | Domain rate-limit state | `identifier`, `attempts`, `locked_until` | | `domain_circuit_breakers` | Circuit breaker state per domain | `name`, `state`, `failure_count`, `last_failure_time` | | `semantic_cache` | LLM response cache | `id`, `signature`, `model`, `prompt_hash`, `response` | | `quota_snapshots` | Historical quota snapshots | `id`, `provider`, `connection_id`, `window_key`, `remaining_percentage` | ### Additional Tables (added by later migrations) Subsequent migrations add tables such as: - `cli_tool_state` (migration 011) — CLI tool state - `mcp_*` tables — MCP server audit - `a2a_*` tables — A2A task state - `usage_*` tables — usage tracking - `plugin_*` tables — plugin system - `skill_executions` — skill execution history - `memory_*` tables — memory system - `compression_*` tables — compression system - `webhook_*` tables — webhook delivery log - `acp_*` tables — Agent Client Protocol - `oneproxy_*` tables — 1proxy marketplace - `proxy_assignments` — proxy scope bindings - `detailed_call_artifacts` — call log artifacts metadata - `quota_alert_history` — quota alert audit - `command_code_auth_sessions` — Command Code OAuth sessions The full list of ~30+ tables is in `src/lib/db/migrations/`. --- ## Migrations OmniRoute uses **versioned, idempotent migrations** in `src/lib/db/migrations/`. Each migration is a single SQL file named `NNN_description.sql`. ### Migration Naming ``` 001_initial_schema.sql 002_mcp_a2a_tables.sql 003_provider_node_custom_paths.sql ... 021_combo_call_log_targets.sql ``` ### How Migrations Run At startup, `migrationRunner.ts`: 1. Creates `_omniroute_migrations` table if not exists 2. Queries for already-applied migrations 3. Applies any new migrations in order, each in a transaction 4. Records each applied migration with timestamp ```ts // src/lib/db/migrationRunner.ts (simplified) export async function runMigrations(db: SqliteDatabase, migrationsDir: string) { const applied = getAppliedMigrations(db); const available = readMigrationFiles(migrationsDir); for (const migration of available) { if (applied.includes(migration.id)) continue; db.transaction(() => { db.exec(migration.sql); recordAppliedMigration(db, migration.id); })(); } } ``` ### Idempotency Migrations must be **idempotent** — running them twice should be a no-op: ```sql -- 004_proxy_registry.sql CREATE TABLE IF NOT EXISTS proxy_registry ( id TEXT PRIMARY KEY, host TEXT NOT NULL, port INTEGER NOT NULL, ... ); ``` Use `IF NOT EXISTS`, `IF EXISTS`, and `OR IGNORE` / `OR REPLACE` clauses liberally. ### Adding a New Migration 1. **Identify the next number**: `ls src/lib/db/migrations/ | tail -1` 2. **Create the file**: `NNN_my_change.sql` 3. **Use safe DDL**: `CREATE TABLE IF NOT EXISTS`, `ALTER TABLE ... ADD COLUMN` 4. **Backfill data carefully**: use `UPDATE ... WHERE ...` to handle existing rows 5. **Test on a copy**: never run untested migrations on production Example: ```sql -- 022_add_combo_priority.sql ALTER TABLE combos ADD COLUMN priority INTEGER DEFAULT 100; UPDATE combos SET priority = 100 WHERE priority IS NULL; CREATE INDEX IF NOT EXISTS idx_combos_priority ON combos(priority); ``` > **Backwards-incompatible changes** (e.g., dropping columns) are tricky. OmniRoute does NOT support downgrade — once a migration is applied, the schema change is permanent. Plan accordingly. --- ## Encryption at Rest Sensitive fields (API keys, OAuth tokens, connection strings) are encrypted at rest using **AES-256-GCM**. ### How It Works ```ts // src/lib/db/encryption.ts (simplified) const key = deriveKeyFromPassphrase(passphrase, salt); const iv = randomBytes(12); const cipher = createCipheriv("aes-256-gcm", key, iv); const encrypted = Buffer.concat([cipher.update(plaintext), cipher.final()]); const authTag = cipher.getAuthTag(); return { encrypted, iv, authTag }; ``` ### Where It's Used - `provider_connections.api_key` — encrypted at application level - `provider_connections.access_token`, `refresh_token`, `id_token` — encrypted at application level - `key_value` entries with `namespace = "secrets"` — encrypted at application level - `proxy_registry.auth` — encrypted at application level (if present) ### Encryption Key The encryption key is derived from a **passphrase** (set via `STORAGE_ENCRYPTION_KEY` env var) and a **salt** (stored in the DB). Both are required to decrypt data. ```bash # Generate a secure passphrase openssl rand -hex 32 # Set in .env STORAGE_ENCRYPTION_KEY= ``` > **Critical**: Losing the encryption key means losing access to all encrypted data. **Back up the key separately from the database**. ### What's NOT Encrypted For performance reasons, the following are stored in plaintext: - Provider display names - Model definitions (already public) - Routing rules - Usage records (no PII) --- ## Encryption Caveats (v3.8.16+) OmniRoute uses **`migrateLegacyEncryptedString()`** to handle two encryption schemes transparently: - **Legacy** (pre-v3.5.0): XOR-based "encryption" (not real crypto) - **Current**: AES-256-GCM with proper IV and auth tag The migration helper detects the legacy format and re-encrypts with the new scheme on first read. This means you can upgrade an old database without losing credentials. --- ## Read Cache For frequently-read data (models, providers, settings), `readCache.ts` provides an **in-memory cache**: ```ts // Cached at startup, invalidated on write const providers = await getCachedProviders(); // Fast, in-memory const fresh = await listProviders(); // Slow, hits DB ``` | Cached entity | Cache key | TTL | | ---------------------- | -------------- | ----------- | | `models` | `models:v1` | Until write | | `provider_connections` | `providers:v1` | Until write | | `settings` | `settings:v1` | Until write | | `combos` | `combos:v1` | Until write | Cache is invalidated on every write to the corresponding table. --- ## Backup and Recovery ### Manual Backup ```bash # Use the CLI to create a local backup omniroute backup create --name pre-migration # Or via the API curl -X PUT http://localhost:20128/api/db-backups \ -H "Authorization: Bearer $MANAGEMENT_KEY" \ -H "Content-Type: application/json" \ -d '{"name": "pre-migration"}' ``` The backup file includes: - All DB tables (serialized to JSON) - Call log artifacts (base64-encoded, optional) - Settings + secrets (encrypted) - Plugin configuration ### Restore ```bash # Via CLI omniroute restore pre-migration # Via API curl -X POST http://localhost:20128/api/db-backups/restore \ -H "Authorization: Bearer $MANAGEMENT_KEY" \ -H "Content-Type: application/json" \ -d '{"name": "pre-migration"}' ``` > **Warning**: Restore overwrites the entire DB. Stop all clients first. ### Automated Backups ```bash # Enable automated daily backups via CLI omniroute backup auto enable --cron "0 2 * * *" --retention 7 ``` ### SQLite Hot Backup For zero-downtime backup of a live DB: ```bash sqlite3 ~/.omniroute/storage.sqlite ".backup /backups/omniroute-hot.db" ``` This uses SQLite's online backup API — safe to run while OmniRoute is running. --- ## Performance Tuning ### WAL Mode WAL is enabled by default. For high-write workloads, consider: ```sql PRAGMA wal_autocheckpoint = 1000; -- Checkpoint every 1000 pages PRAGMA journal_size_limit = 67108864; -- 64MB WAL cap ``` ### Indexes Key indexes for performance (auto-created by migrations): - `idx_models_provider` — model lookups by provider - `idx_combo_targets_combo_id` — combo target expansion - `idx_usage_history_api_key_timestamp` — usage analytics - `idx_quota_snapshots_api_key_window` — quota tracking - `idx_call_logs_timestamp` — call log queries To add a new index, create a migration: ```sql -- 023_add_my_index.sql CREATE INDEX IF NOT EXISTS idx_my_table_my_column ON my_table(my_column); ``` ### Memory-Mapped I/O For very large databases (>10GB), memory mapping can be adjusted via SQLite pragma: ```sql -- Set via SQLite pragma (adjust in core.ts or runtime) PRAGMA mmap_size = 268435456; -- 256MB ``` ### Compaction Long-running OmniRoute instances benefit from occasional `VACUUM`: ```bash sqlite3 ~/.omniroute/storage.sqlite "VACUUM;" ``` Run monthly during low-traffic windows. (WAL mode reduces the need, but doesn't eliminate it.) --- ## Health Check `src/lib/db/healthCheck.ts` provides **DB-level health diagnostics**: ````bash GET /api/db/health Returns: ```json { "status": "healthy", "checks": { "writable": { "status": "pass" }, "integrity": { "status": "pass", "result": "ok" }, "foreign_keys": { "status": "pass", "violations": 0 }, "orphaned_artifacts": { "status": "warn", "count": 12 }, "table_sizes": { "usage_history": { "rows": 12345, "size_mb": 12.3 }, "call_logs": { "rows": 567, "size_mb": 2.1 } } } } ```` Run `PRAGMA integrity_check` to detect corruption: ```bash sqlite3 ~/.omniroute/storage.sqlite "PRAGMA integrity_check;" # Should print: ok ``` If it returns anything other than `ok`, **stop using the database immediately** and restore from backup. --- ## Disaster Recovery ### Scenario 1: WAL File Lost The `-wal` file is missing but `-shm` and main DB are intact: ```bash # Recovers automatically on next open omniroute ``` If SQLite can't auto-recover: ```bash sqlite3 ~/.omniroute/storage.sqlite ".recover" > recovered.sql sqlite3 recovered.db < recovered.sql mv recovered.db ~/.omniroute/storage.sqlite ``` ### Scenario 2: Main DB File Corrupted Restore from backup: ```bash omniroute sync pull --merge # or: omniroute backup restore ``` ### Scenario 3: Encryption Key Lost **No recovery possible** without the key. The encrypted fields are unreadable. Re-add all providers manually with new credentials. > **Mitigation**: Always back up the encryption key separately, ideally in a password manager or KMS. ### Scenario 4: Disk Full SQLite will return `SQLITE_FULL` errors. Free disk space, then: ```bash # Checkpoint WAL to free up space sqlite3 ~/.omniroute/storage.sqlite "PRAGMA wal_checkpoint(TRUNCATE);" ``` --- ## Common Operations ### Inspect a Table ```bash sqlite3 ~/.omniroute/storage.sqlite "SELECT * FROM api_keys LIMIT 5;" ``` ### Count Rows in All Tables ```bash sqlite3 ~/.omniroute/storage.sqlite <