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

> Explore OmniRoute's database architecture featuring 95 SQLite modules and 110 migrations. Learn how this system manages a single storage file with automatic versioned migrations for efficient CRUD operations.

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

---

**TL;DR:** OmniRoute uses a singleton SQLite adapter pattern with `better-sqlite3` or [`sql.js`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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:

```typescript
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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/NNN_description.sql) are sorted numerically and applied sequentially

### Transactional Migration Execution

Each migration runs inside a **single atomic transaction**:

```typescript
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`](https://github.com/diegosouzapw/OmniRoute/blob/main/001_initial_schema.sql):** Creates base tables for `provider_connections`, `api_keys`, and `call_logs`
- **[`045_compression_tokens.sql`](https://github.com/diegosouzapw/OmniRoute/blob/main/045_compression_tokens.sql):** Adds `compression_tokens` table for token-compression accounting
- **[`058_command_code_auth_sessions.sql`](https://github.com/diegosouzapw/OmniRoute/blob/main/058_command_code_auth_sessions.sql):** Introduces session tables for Command-Code OAuth flows
- **[`071_services.sql`](https://github.com/diegosouzapw/OmniRoute/blob/main/071_services.sql):** Registers embedded services (Redis, Bifrost) in the database
- **[`085_quota_pools.sql`](https://github.com/diegosouzapw/OmniRoute/blob/main/085_quota_pools.sql):** Implements quota-pool structures for shared API-key allocation
- **[`101_api_key_usage_limits.sql`](https://github.com/diegosouzapw/OmniRoute/blob/main/101_api_key_usage_limits.sql):** Stores per-key usage-limit configuration
- **[`112_batch_item_checkpoints.sql`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/providers.ts), [`usageHistory.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/usageHistory.ts), [`callLogs.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/callLogs.ts)) that import `getDbInstance()` from [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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:

```typescript
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`](https://github.com/diegosouzapw/OmniRoute/blob/main/optimizationSettings.ts)

After opening the database, `applyDatabaseOptimizationSettingsForDb()` (from [`src/lib/db/optimizationSettings.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/types/databaseSettings.ts).

### Data Security and Backup Infrastructure

- **[`encryption.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/encryption.ts):** Handles transparent migration of legacy encrypted payloads when upgrading database versions
- **[`backup.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/healthCheck.ts):** Runs startup sanity checks to verify database integrity before accepting traffic

### Read-Through Cache Invalidation

The [`readCache.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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

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

```typescript
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)

```bash

# 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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts) module includes fallback logic that automatically switches to the [`sql.js`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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.