Understanding OmniRoute's Database Architecture: Modules, Migrations, and Tables

OmniRoute persists all state in a layered SQLite architecture comprising a singleton core (core.ts), a versioned migration runner (migrationRunner.ts), and 95+ domain-specific modules that manage CRUD operations for entities like providers, combos, and API keys.

The diegosouzapw/OmniRoute repository implements a robust database layer designed to handle routing logic, provider credentials, and usage analytics. Understanding OmniRoute's database architecture reveals how the system balances strict schema evolution with flexible, lazy-loaded domain tables across desktop and browser environments.

The Three-Layer Architecture

OmniRoute splits its data persistence into three tightly coupled components that interact during startup and runtime.

Core Layer: The SQLite Singleton

The Core (src/lib/db/core.ts) establishes the single entry point to the database. It lazily initializes a better-sqlite3 adapter (or falls back to sql.js in browsers) via getDbInstance(), ensuring only one connection handles the storage.sqlite file. The Core also resolves the DATA_DIR (defaulting to ~/.omniroute/), creates the directory on startup, and defines the SCHEMA_SQL constant containing base tables like provider_connections and combos.

Migration Runner: Versioned Evolution

The Migration Runner (src/lib/db/migrationRunner.ts) manages schema changes through numbered SQL files (e.g., 001_initial_schema.sql). It tracks applied versions in the _omniroute_migrations table and enforces safety limits via MAX_PENDING_MIGRATIONS (default 50) to prevent accidental mass migrations.

Domain Modules: Entity-Specific Logic

Domain Modules under src/lib/db/ implement CRUD for specific entities. Each module imports getDbInstance() and calls ensureTable() to lazily create its tables. With over 95 modules covering providers, combos, usage analytics, and compression, this design isolates table logic while sharing the singleton connection.

Core Database Layer: Singleton Management and Base Schema

The Core file serves as the foundation for all database interactions in OmniRoute.

Singleton Creation and Data Directory

The getDbInstance() function in src/lib/db/core.ts acts as the sole gateway to SQLite. It opens the database file and ensures the data directory exists before any queries execute. This singleton pattern prevents connection leaks and ensures thread-safe access across the application's domain modules.

Base Schema and Critical Tables

Lines 89-200 of core.ts define the SCHEMA_SQL constant, which establishes tables required before any domain module runs:

• provider_connections – Stores OAuth tokens and API credentials for LLM providers
• combos – Contains routing configuration and fallback chains
• api_keys – Manages user-issued keys and their rate-limit policies
• proxy_registry and proxy_assignments – Tracks proxy routing state
• model_combo_mappings – Links specific models to combo configurations

The Core also maintains a CRITICAL_DB_TABLES list (lines 98-123) that triggers special backup logic before migrations or shutdowns.

Utility Helpers

The Core exports database-agnostic utilities like rowToCamel() for case conversion, toSnakeCase() for identifiers, and hasTable() for introspection. These functions allow domain modules to remain agnostic to SQLite specifics while maintaining consistent naming conventions.

Migration Runner: Versioned Schema Evolution

Schema changes in OmniRoute follow a strict, file-based versioning system implemented in src/lib/db/migrationRunner.ts.

Migration File Discovery and Tracking

The runner locates SQL files through resolveMigrationsDir() (lines 45-100), which searches multiple paths to support Windows, Linux, and packaged deployments. The getMigrationFiles() function sorts files by numeric prefix and returns version metadata. Before execution, the runner ensures the _omniroute_migrations tracking table exists to record which versions have been applied.

Safety Mechanisms and FTS5 Support

OmniRoute implements several guards to protect data integrity:

• Mass-Migration Guard: If pending migrations exceed MAX_PENDING_MIGRATIONS (50), the process aborts to prevent accidental wipes on fresh databases
• FTS5 Detection: The supportsFts5() check skips full-text search migrations when the SQLite build lacks the extension
• Transactional Safety: Each migration runs inside db.transaction(() => …), ensuring atomic application—if any statement fails, the entire file rolls back

Automatic Pre-Migration Backups

Before applying pending changes, the runner creates a backup copy in DB_BACKUPS_DIR (referenced in core.ts line 90). This provides recovery options if a migration fails or corrupts the schema.

Domain Modules: Entity-Specific CRUD Operations

OmniRoute organizes persistent logic into discrete modules, each responsible for one entity or logical table family.

Module Structure and Lazy Initialization

Every domain module follows a consistent pattern:

1. Import getDbInstance() from the Core
2. Define CREATE TABLE IF NOT EXISTS statements executed via ensureTable() on first access
3. Export typed CRUD functions that return plain JavaScript objects (often processed through rowToCamel())

This lazy initialization allows OmniRoute to start quickly without creating all 95+ tables upfront, while still ensuring schema exists before first use.

Key Domain Modules

The repository includes specialized modules for every major feature:

• providers.ts – Manages provider_connections with functions like getProviders(), upsertProvider(), and deleteProvider()
• combos.ts – Handles combos and model_combo_mappings tables, exposing listCombos(), createCombo(), and deleteCombo()
• apiKeys.ts – Controls api_keys and api_key_groups via listApiKeys(), createApiKey(), and revokeApiKey()
• usageAnalytics.ts – Queries usage_history and domain_cost_history through getDailyUsage() and getProviderCostRows()
• compression.ts – Tracks compression_combos and compression_analytics with listCompressionCombos() and recordCompressionStats()

These modules are consumed by higher-level services including the combo router, MCP toolset, and REST API endpoints.

Database Health and Backup Strategies

OmniRoute includes proactive measures to ensure data integrity across deployments.

Health Checks

The runDbHealthCheck() function (imported in core.ts) validates foreign key integrity and executes sanity queries against known tables. This catches corruption or incomplete migrations before they impact routing logic.

Backup Before Migration

The migration runner automatically copies the current database file to DB_BACKUPS_DIR before executing any pending changes. Combined with the CRITICAL_DB_TABLES tracking, this creates multiple safety nets for production deployments.

Practical Code Examples

Interact with OmniRoute's database layer using these patterns:

// Get the singleton database instance
import { getDbInstance } from "@/src/lib/db/core";

const db = getDbInstance();   // Returns better-sqlite3 or sql.js adapter

// Execute raw SQL against provider connections
const rows = db.prepare("SELECT * FROM provider_connections WHERE is_active = 1").all();
console.log("Active providers:", rows);

// Use high-level domain module APIs
import { listCombos } from "@/src/lib/db/combos";

async function displayRoutingConfig() {
  const combos = await listCombos();   // Returns typed, camel-cased objects
  console.table(combos);
}
displayRoutingConfig();

// Manually trigger migrations in scripts or maintenance tasks
import { runMigrations } from "@/src/lib/db/migrationRunner";

(async () => {
  await runMigrations();   // Applies pending *.sql files transactionally
  console.log("Database schema is current");
})();

Summary

• OmniRoute uses a three-layer SQLite architecture: Core singleton (core.ts), Migration Runner (migrationRunner.ts), and 95+ domain modules (providers.ts, combos.ts, etc.)
• The Core manages getDbInstance(), data directory creation (~/.omniroute/), and base tables including provider_connections and combos
• Migrations are file-based (numbered *.sql files) tracked in _omniroute_migrations, with safety guards including MAX_PENDING_MIGRATIONS (50) and automatic pre-migration backups
• Domain modules use lazy initialization, calling ensureTable() via hasTable() checks only when first accessed, keeping startup fast while ensuring schema compliance
• Critical tables defined in CRITICAL_DB_TABLES receive special backup treatment before migrations or shutdowns

Frequently Asked Questions

What database does OmniRoute use?

OmniRoute uses SQLite as its primary database, specifically the better-sqlite3 package for Node.js environments. In browser contexts where native SQLite is unavailable, it falls back to sql.js (WebSQL). All persistent state stores in a single file named storage.sqlite located in the DATA_DIR (default ~/.omniroute/).

How does OmniRoute handle schema migrations?

OmniRoute handles schema changes through versioned SQL files stored in the migrations directory. The migrationRunner.ts module reads these files, sorts them by numeric prefix, and applies them transactionally. It tracks completed migrations in the _omniroute_migrations table and includes safety features like the MAX_PENDING_MIGRATIONS guard (default 50) to prevent accidental bulk changes on fresh databases.

What are domain modules in OmniRoute?

Domain modules are the 95+ files under src/lib/db/ that implement CRUD operations for specific entities. Each module (e.g., providers.ts, apiKeys.ts, usageAnalytics.ts) owns its table schema and exposes typed functions like upsertProvider() or revokeApiKey(). They import getDbInstance() from the Core and use lazy initialization to create tables only when first accessed.

How does OmniRoute ensure database safety during migrations?

The system ensures safety through multiple mechanisms: transactional execution (each migration runs in a single transaction that rolls back on failure), automatic pre-migration backups (copies created in DB_BACKUPS_DIR), mass-migration guards (aborting if over 50 pending migrations exist), and FTS5 detection (skipping full-text search migrations on unsupported SQLite builds).

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