# How OmniRoute Uses SQLite with 95 Domain Modules for Persistence

> Discover how OmniRoute achieves robust persistence using SQLite and 95 domain modules. Learn how each module manages its own tables and CRUD operations for efficient data handling.

- Repository: [Diego Rodrigues de Sa e Souza/OmniRoute](https://github.com/diegosouzapw/OmniRoute)
- Tags: internals
- Published: 2026-08-03

---

**OmniRoute persists all runtime state in a single SQLite file accessed through a singleton database instance, with 95 domain-specific modules each owning their own tables and CRUD operations.**

OmniRoute is an open-source LLM routing platform that relies on SQLite for durable storage. The persistence architecture centers on a **singleton database instance** defined in [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts) and shared across 95 specialized domain modules. Each module manages its own table schema while leveraging common infrastructure for migrations, encryption, and health monitoring.

## The Singleton Database Pattern

The core of OmniRoute's SQLite persistence lives in **[`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts)**. This module exports `getDbInstance()`, a factory function that maintains a module-level singleton surviving Next.js hot-module-replacement.

### Initialization Sequence

When first called, `getDbInstance()` executes five critical steps:

1. **Singleton retrieval** — Returns an existing database handle or instantiates a new one
2. **Data-directory preparation** — Ensures `DATA_DIR` exists for the `storage.sqlite` file
3. **Schema creation** — Runs embedded `SCHEMA_SQL` with `CREATE TABLE IF NOT EXISTS` statements for all domain tables
4. **Migration runner** — Applies versioned migrations from `db/migrations/` without data loss
5. **Optimization & health checks** — Configures WAL mode, busy timeout, cache size, and schedules `runDbHealthCheck`

```typescript
// Direct singleton access for low-level operations
import { getDbInstance } from '@/lib/db/core';

function rawQueryExample() {
  const db = getDbInstance();
  const row = db.prepare('SELECT COUNT(*) as cnt FROM provider_connections').get() as { cnt: number };
  console.log('Total connections:', row.cnt);
}

```

## How 95 Domain Modules Share One Database

Each domain module follows a consistent pattern: declare a table, validate operations, and expose typed CRUD helpers. All modules import `getDbInstance()` from the core, ensuring atomicity across the entire system.

### Provider Connections Module

The **[`src/lib/db/providers.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/providers.ts)** module demonstrates the pattern. It owns the `provider_connections` table, encrypts credential fields automatically, and exports functions like `getProviderConnections`, `createProviderConnection`, and `updateProviderConnection`.

```typescript
import { getProviderConnections, createProviderConnection } from '@/lib/db/providers';

// Retrieve filtered results with column validation
async function listActiveProviders() {
  const connections = await getProviderConnections({ isActive: true });
  return connections;
}

// Credentials encrypted transparently before storage
async function addConnection() {
  await createProviderConnection({
    provider: 'openai',
    authType: 'apikey',
    name: 'My OpenAI Key',
    apiKey: 'sk-************',
    priority: 10,
    isActive: true,
  });
}

```

### Other Key Domain Modules

| Module | Table | Responsibility |
|--------|-------|--------------|
| [`src/lib/db/combo.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/combo.ts) | `combos` | Routing combo definitions and metadata |
| [`src/lib/db/usageHistory.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/usageHistory.ts) | Usage records | Per-request tokens, latency, status for analytics |
| [`src/lib/db/callLogs.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/callLogs.ts) | Call logs | Request/response artifacts and streaming details |
| [`src/lib/db/quotaSnapshots.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/quotaSnapshots.ts) | Quota state | Rate-limit enforcement data per connection |

## Resilience and Data Protection

The core module provides utilities beyond basic queries. **[`src/lib/db/backup.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/backup.ts)** creates managed backups before destructive operations. The system also handles probe-failure recovery, critical-state capture, and graceful degradation to the WASM-based [`sql.js`](https://github.com/diegosouzapw/OmniRoute/blob/main/sql.js) implementation when the native driver is unavailable.

## Summary

- **Single-file architecture**: All 95 domain modules persist to one `storage.sqlite` file
- **Singleton pattern**: `getDbInstance()` in [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts) ensures consistent, shared database access
- **Module autonomy**: Each domain owns its table schema and CRUD helpers while sharing migration and encryption infrastructure
- **Production hardening**: WAL mode, health checks, automatic backups, and driver fallback protect data integrity

## Frequently Asked Questions

### How does OmniRoute prevent data corruption with multiple domain modules writing simultaneously?

The singleton pattern in [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts) guarantees that all 95 domain modules reference the same Better-SQLite3 instance. SQLite handles concurrency at the file level, and the initialization sets `PRAGMA busy_timeout` along with WAL mode to mediate concurrent access safely.

### What happens if the SQLite schema needs to change in a new release?

The migration runner in `getDbInstance()` executes versioned SQL files from `db/migrations/` on every startup. Each migration runs inside a transaction, evolving tables without destroying existing data. Domain modules remain compatible by using `CREATE TABLE IF NOT EXISTS` in the base `SCHEMA_SQL`.

### How are sensitive credentials protected in the SQLite database?

The provider connections module ([`src/lib/db/providers.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/providers.ts)) automatically encrypts fields like `apiKey` before persisting them. The encryption uses keys derived from environment configuration, ensuring raw credentials never appear in the `storage.sqlite` file or backup archives.

### Can OmniRoute run without a native SQLite driver?

Yes. The core module implements fallback logic that substitutes the WASM-based [`sql.js`](https://github.com/diegosouzapw/OmniRoute/blob/main/sql.js) implementation when Better-SQLite3 is unavailable. All domain modules continue functioning normally because they interact through the abstracted `getDbInstance()` interface rather than direct driver calls.