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:

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:

// 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:

Companion files:

• storage.sqlite-wal — write-ahead log
• storage.sqlite-shm — shared memory file
• call_logs/ — request payload artifacts (if enabled)

Override the location:

DATA_DIR=/custom/path omniroute

Domain Module Architecture

OmniRoute's database has 110 top-level TypeScript modules in src/lib/db/. Each domain 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 110 Top-Level DB Modules

OmniRoute has 110 top-level TypeScript files in src/lib/db/. Below is a sampling of core modules; see the directory listing for the complete list:

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.

// ❌ 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)

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

// 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:

-- 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:

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

// 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.

# Generate a secure passphrase
openssl rand -hex 32

# Set in .env
STORAGE_ENCRYPTION_KEY=<your-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:

// Cached at startup, invalidated on write
const providers = await getCachedProviders(); // Fast, in-memory
const fresh = await listProviders(); // Slow, hits DB

Cache is invalidated on every write to the corresponding table.

Backup and Recovery

Manual Backup

# 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

# 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

# Enable automated daily backups via CLI
omniroute backup auto enable --cron "0 2 * * *" --retention 7

The schedule is executed server-side by a background job that ticks every 30 seconds (default) and evaluates the cron expression against local server time.

SQLite Hot Backup

For zero-downtime backup of a live DB:

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:

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:

-- 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:

-- Set via SQLite pragma (adjust in core.ts or runtime)
PRAGMA mmap_size = 268435456;  -- 256MB

Compaction

Long-running OmniRoute instances benefit from occasional VACUUM:

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:

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:

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:

# Recovers automatically on next open
omniroute

If SQLite can't auto-recover:

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:

omniroute sync pull --merge   # or: omniroute backup restore <backup-id>

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:

# Checkpoint WAL to free up space
sqlite3 ~/.omniroute/storage.sqlite "PRAGMA wal_checkpoint(TRUNCATE);"

Common Operations

Inspect a Table

sqlite3 ~/.omniroute/storage.sqlite "SELECT * FROM api_keys LIMIT 5;"

Count Rows in All Tables

sqlite3 ~/.omniroute/storage.sqlite <<EOF
SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%';
EOF

Reset (Wipe) All Data

# Stop OmniRoute first
omniroute stop

# Delete the DB file
rm ~/.omniroute/storage.sqlite*

# Restart (will recreate empty DB)
omniroute

For a selective reset (keep providers, wipe usage):

DELETE FROM usage_history WHERE timestamp < datetime('now', '-30 day');
DELETE FROM call_logs WHERE timestamp < datetime('now', '-30 day');
DELETE FROM proxy_logs WHERE timestamp < datetime('now', '-30 day');

Export Single Table

sqlite3 ~/.omniroute/storage.sqlite <<EOF
.mode csv
.output api_keys.csv
SELECT * FROM api_keys;
EOF

Troubleshooting

"Database is locked"

Another process is holding a write lock. Either:

• Wait for the other process to finish (check lsof | grep storage.sqlite)
• Kill the other process
• If persistent, restart OmniRoute

"Foreign key constraint failed"

A domain module is violating referential integrity. Check:

• Orphaned rows in dependent tables
• Cascading deletes that didn't propagate
• Recent migration that changed a foreign key

Run PRAGMA foreign_key_check; to find violations.

"Out of memory"

SQLite's memory-mapped I/O is exceeding the OS limit. Reduce via SQLite pragma:

PRAGMA mmap_size = 134217728;  -- 128MB instead of 256MB

Or disable:

PRAGMA mmap_size = 0;

"Migration failed mid-way"

The migration ran in a transaction, so it should have rolled back. If not:

1. Stop OmniRoute (prevent further attempts)
2. Check the DB state with sqlite3
3. Manually fix the partial migration
4. Re-run OmniRoute (the migration will be retried)

To prevent this, always test migrations on a copy first.

See Also

• USAGE_QUOTA_GUIDE.md — usage tables
• MONITORING_GUIDE.md — health monitoring
• RELEASE_CHECKLIST.md — release flow
• Source: src/lib/db/ (80+ files, ~25K LOC)