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

> Explore OmniRoute's robust database architecture. Learn about its core, versioned migrations, and numerous modules managing providers, combos, and API keys for efficient data operations.

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

---

**OmniRoute persists all state in a layered SQLite architecture comprising a singleton core ([`core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/core.ts)), a versioned migration runner ([`migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/migrationRunner.ts)) manages schema changes through numbered SQL files (e.g., [`001_initial_schema.sql`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/providers.ts)** – Manages `provider_connections` with functions like `getProviders()`, `upsertProvider()`, and `deleteProvider()`
- **[`combos.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/combos.ts)** – Handles `combos` and `model_combo_mappings` tables, exposing `listCombos()`, `createCombo()`, and `deleteCombo()`
- **[`apiKeys.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/apiKeys.ts)** – Controls `api_keys` and `api_key_groups` via `listApiKeys()`, `createApiKey()`, and `revokeApiKey()`
- **[`usageAnalytics.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/usageAnalytics.ts)** – Queries `usage_history` and `domain_cost_history` through `getDailyUsage()` and `getProviderCostRows()`
- **[`compression.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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:

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

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

```

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

```

```typescript
// 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();

```

```typescript
// 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`](https://github.com/diegosouzapw/OmniRoute/blob/main/core.ts)), Migration Runner ([`migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/migrationRunner.ts)), and 95+ domain modules ([`providers.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/providers.ts), [`combos.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/providers.ts), [`apiKeys.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/apiKeys.ts), [`usageAnalytics.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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).