OmniRoute's Database Architecture: How 95 SQLite Modules and 110 Migrations Work Together

TL;DR: OmniRoute uses a singleton SQLite adapter pattern with better-sqlite3 or sql.js fallback to manage a single storage.sqlite file, applying 110+ versioned migrations automatically on startup while 95 domain-specific database modules handle CRUD operations through a centralized core.

OmniRoute (diegosouzapw/OmniRoute) implements a robust SQLite-based persistence layer that balances simplicity with enterprise-grade safety features. The architecture centers on a singleton database connection managed through src/lib/db/core.ts, supported by a comprehensive migration runner handling over 110 schema evolutions. This design allows the API proxy to maintain state—from provider configurations to usage analytics—without requiring external database services.

The Singleton SQLite Core in src/lib/db/core.ts

At the heart of OmniRoute's database architecture lies a singleton adapter pattern that ensures all 95 domain modules share a single SQLite connection.

Database Initialization and Schema Creation

The entry point getDbInstance() (exported from src/lib/db/core.ts) handles lazy initialization of the SQLite file located at DATA_DIR/storage.sqlite. On first run, the system executes SCHEMA_SQL (lines L24-L78) to create foundational tables:

const SCHEMA_SQL = `
  CREATE TABLE IF NOT EXISTS provider_connections ( … );
  CREATE TABLE IF NOT EXISTS provider_nodes ( … );
  CREATE TABLE IF NOT EXISTS key_value ( … );
  CREATE TABLE IF NOT EXISTS combos ( … );
  CREATE TABLE IF NOT EXISTS api_keys ( … );
  CREATE TABLE IF NOT EXISTS db_meta ( … );
  CREATE TABLE IF NOT EXISTS usage_history ( … );
  CREATE TABLE IF NOT EXISTS call_logs ( … );
  …`;

The ensureDbInitialized() function, called during server startup in src/server/index.ts, orchestrates the initialization sequence: open the database connection, execute runMigrations(), and apply optimization settings.

Driver Abstraction with Native and WASM Fallbacks

The core module implements adaptive driver selection to support diverse deployment environments:

• Primary driver: better-sqlite3 for Node.js environments
• Fallback driver: sql.js WASM module for constrained environments
• Detection helpers: isNativeSqliteLoadError and isSqliteDriverUnavailableError determine availability before instantiation

This guarantees that OmniRoute remains operational even when native SQLite bindings are unavailable, using the closeProbeIfSafe helper to dispose of temporary probe connections safely.

The Migration System: 110+ Versioned Schema Changes

Schema evolution in OmniRoute is handled by src/lib/db/migrationRunner.ts, which manages a catalog of 110+ incremental .sql files stored in src/lib/db/migrations/.

Migration Discovery and Safety Controls

The runner locates migration files via resolveMigrationsDir(), which traverses the file tree and respects the OMNIROUTE_MIGRATIONS_DIR environment variable. Critical safety mechanisms include:

• Pending threshold protection: If more than OMNIROUTE_MAX_PENDING_MIGRATIONS (default 50) migrations are detected on an existing database, the runner throws MigrationSafetyAbortError to prevent accidental mass migrations
• Version parsing: Files named NNN_description.sql are sorted numerically and applied sequentially

Transactional Migration Execution

Each migration runs inside a single atomic transaction:

db.transaction(() => {
  db.exec(migrationSql);
});
INSERT INTO _omniroute_migrations (version, name) VALUES ('046', 'database_settings.sql');

The _omniroute_migrations tracking table records applied versions, preventing re-execution. The runner also supports optional FTS5 migrations—files listed in OPTIONAL_FTS5_MIGRATION_VERSIONS are silently skipped if the SQLite build lacks FTS5 support.

Notable Migration Milestones

Key schema evolutions in the migration catalog include:

• 001_initial_schema.sql: Creates base tables for provider_connections, api_keys, and call_logs
• 045_compression_tokens.sql: Adds compression_tokens table for token-compression accounting
• 058_command_code_auth_sessions.sql: Introduces session tables for Command-Code OAuth flows
• 071_services.sql: Registers embedded services (Redis, Bifrost) in the database
• 085_quota_pools.sql: Implements quota-pool structures for shared API-key allocation
• 101_api_key_usage_limits.sql: Stores per-key usage-limit configuration
• 112_batch_item_checkpoints.sql: Enables checkpoint tracking for batch-processing jobs

The 95 Database Module Ecosystem

OmniRoute's 95 SQLite modules are domain-specific files (e.g., providers.ts, usageHistory.ts, callLogs.ts) that import getDbInstance() from src/lib/db/core.ts. This ensures connection consistency—every module writes to the same storage.sqlite file through the shared adapter.

For example, recording usage metrics flows through the centralized instance:

import { getDbInstance } from "@/lib/db/core";

const db = getDbInstance();

db.prepare(`
  INSERT INTO usage_history (
    provider, model, connection_id, api_key_id,
    tokens_input, tokens_output, latency_ms, timestamp
  ) VALUES (
    @provider, @model, @connection_id, @api_key_id,
    @tokens_input, @tokens_output, @latency_ms, datetime('now')
  )
`).run({
  provider: "openai",
  model: "gpt-4o-mini",
  connection_id: "conn-123",
  api_key_id: "key-456",
  tokens_input: 150,
  tokens_output: 300,
  latency_ms: 85,
});

Optimization, Security, and Reliability Features

Beyond basic CRUD operations, OmniRoute's database architecture includes specialized modules for performance tuning and data protection.

Performance Tuning via optimizationSettings.ts

After opening the database, applyDatabaseOptimizationSettingsForDb() (from src/lib/db/optimizationSettings.ts) configures:

• Page size (default 4096 bytes)
• Cache size allocation
• Auto-vacuum mode settings

These values are read from DatabaseSettings types defined in src/types/databaseSettings.ts.

Data Security and Backup Infrastructure

• encryption.ts: Handles transparent migration of legacy encrypted payloads when upgrading database versions
• backup.ts: Creates timestamped copies of storage.sqlite in the db_backups/ directory (e.g., storage-2024-07-31-12-00-00.sqlite)
• healthCheck.ts: Runs startup sanity checks to verify database integrity before accepting traffic

Read-Through Cache Invalidation

The readCache.ts module maintains a simple in-memory cache for frequently accessed queries. Domain modules call invalidateDbCache() after write operations to ensure subsequent reads reflect the latest data.

Practical Implementation Examples

Initializing the Database on Server Launch

import { ensureDbInitialized } from "@/lib/db/core";

// Called early in src/server/index.ts
await ensureDbInitialized(); 
// → Opens SQLite, runs migrations, applies optimizations

Querying Recent Call Logs

import { getDbInstance } from "@/lib/db/core";

const db = getDbInstance();
const recent = db.prepare(`
  SELECT id, timestamp, model, status, latency_ms
  FROM call_logs
  ORDER BY timestamp DESC
  LIMIT 10
`).all();

console.table(recent);

Running Manual Migrations (Development Only)

# From the repo root

npm run db:migrate  # Internally calls migrationRunner.runMigrations()

Summary

• OmniRoute persists all state in a single SQLite file (storage.sqlite) managed by a singleton adapter pattern in src/lib/db/core.ts
• 110+ migration files in src/lib/db/migrations/ handle schema evolution atomically, with safety thresholds to prevent accidental data loss
• 95 domain modules share the database connection through getDbInstance(), ensuring consistency across the codebase
• Optimization settings, automatic backups, and health checks provide enterprise-grade reliability without external database dependencies
• The architecture supports both native better-sqlite3 and WASM fallbacks, enabling deployment across diverse environments

Frequently Asked Questions

How does OmniRoute handle database schema changes?

OmniRoute uses a versioned migration system where each schema change is a numbered .sql file in src/lib/db/migrations/. On startup, runMigrations() (from src/lib/db/migrationRunner.ts) compares files against the _omniroute_migrations tracking table and applies only missing versions inside atomic transactions. This incremental approach allows upgrading from any previous version without manual intervention.

What happens if a migration fails during startup?

If any migration fails, the transaction rolls back automatically, leaving the database in its pre-migration state. The system also implements a safety threshold: if more than 50 pending migrations are detected (configurable via OMNIROUTE_MAX_PENDING_MIGRATIONS), it aborts with MigrationSafetyAbortError to prevent accidental mass migrations on production databases.

Can OmniRoute run without better-sqlite3?

Yes. The src/lib/db/core.ts module includes fallback logic that automatically switches to the sql.js WASM implementation if better-sqlite3 fails to load. This allows OmniRoute to run in environments where native Node.js modules are restricted, though with potentially different performance characteristics.

How are database backups triggered?

The src/lib/db/backup.ts module creates timestamped copies of storage.sqlite in the db_backups/ directory. Backups run automatically based on internal scheduling (typically during low-usage periods) and can be triggered manually through the backup API, ensuring point-in-time recovery capabilities without external backup tools.

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