# How to Utilize OmniRoute’s 83 SQLite Domain Modules for Data Persistence

> Discover how to use OmniRoute's 83 SQLite domain modules for efficient data persistence. Leverage CRUD functions for seamless reading and writing, while the core handles connections, encryption, caching, and backups.

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

---

**Import the specific domain module (e.g., [`providers.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/providers.ts), [`combos.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/combos.ts)) and call its CRUD functions to read or write data, while the core layer handles connection pooling, encryption, caching, and automatic backups.**

OmniRoute persists virtually all runtime state through a modular SQLite architecture consisting of 83 specialized domain tables. This TypeScript-based system in the `diegosouzapw/OmniRoute` repository uses a singleton database connection managed by [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts) to ensure ACID compliance across provider connections, combo definitions, usage history, and semantic caching. Understanding how to leverage these **SQLite domain modules for data persistence** enables you to build reliable, encrypted, and performant operations without writing raw SQL.

## Understanding the Core Architecture

The database layer follows a strict hierarchy: a single core module manages the SQLite connection, while 83 domain-specific modules handle table-level operations.

### The Singleton Bootstrap Process

When your application first calls `getDbInstance()`, the core module ([`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts)) executes a four-phase initialization:

1. **Environment Resolution** – It resolves `DATA_DIR` and `SQLITE_FILE` paths to locate the database file on disk.

2. **Connection Initialization** – It opens (or creates) the SQLite file and sets critical pragmas for reliability: `journal_mode = WAL`, `busy_timeout = 2000`, and foreign key enforcement (lines 84–90).

3. **Schema Deployment** – It runs the inline `SCHEMA_SQL` constant which defines all 83 tables including `provider_connections`, `combos`, `usage_history`, `call_logs`, and `semantic_cache` (lines 889–945).

4. **Migration Execution** – It automatically applies versioned migrations from `db/migrations/` using the migration runner to evolve the schema safely without data loss (lines 1010–1012).

### Domain Module Organization

Each of the 83 tables receives a dedicated TypeScript module under `src/lib/db/`. These modules import `getDbInstance`, `rowToCamel`, and `cleanNulls` from the core, then expose type-safe functions for their specific tables:

- **[`providers.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/providers.ts)** – CRUD operations for OAuth, API-key, and cookie credentials.
- **[`combos.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/combos.ts)** – Combo definition storage and routing metadata.
- **[`usageHistory.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/usageHistory.ts)** – Token accounting and per-request audit logging.
- **[`semanticCache.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/semanticCache.ts)** – Prompt deduplication and semantic similarity storage.
- **[`readCache.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/readCache.ts)** – In-memory read-through cache for hot tables.

## Working with Domain Modules

All persistence actions flow through these domain modules, ensuring every write includes field encryption, cache invalidation, and backup creation.

### Managing Provider Connections

The [`providers.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/providers.ts) module encrypts sensitive fields automatically using AES-GCM. To fetch active connections:

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

async function listActiveConnections() {
  const connections = await getProviderConnections({ isActive: true });
  console.log('Active connections:', connections);
}

```

This function internally calls `getDbInstance()`, executes `SELECT * FROM provider_connections WHERE is_active = 1`, and returns camel-cased, decrypted objects (lines 43–62).

To create a new OAuth entry with automatic encryption:

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

async function addGoogleOAuth() {
  await createProviderConnection({
    provider: 'google',
    authType: 'oauth',
    email: 'user@example.com',
    displayName: 'My Google Account',
    accessToken: '<access-token>',
    refreshToken: '<refresh-token>',
    expiresAt: new Date(Date.now() + 3600 * 1000).toISOString(),
  });
}

```

The module normalizes optional fields, encrypts tokens via `encryptConnectionFields`, writes the row, reorders connection priorities, triggers `createManagedDbBackup`, and invalidates the read cache (lines 31–44, 58–69).

### Recording Usage History

Track token consumption and request metadata using the [`usageHistory.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/usageHistory.ts) module:

```typescript
import { recordUsage } from '@/src/lib/db/usageHistory';

async function logRequest(req) {
  await recordUsage({
    provider: req.provider,
    model: req.model,
    connectionId: req.connectionId,
    apiKeyId: req.apiKeyId,
    tokens_input: req.inputTokens,
    tokens_output: req.outputTokens,
    status: 'success',
  });
}

```

This inserts a timestamped row into the `usage_history` table with input/output token counters for cost tracking and audit trails.

### Updating Combo Definitions

Modify routing logic through the [`combos.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/combos.ts) module:

```typescript
import { getCombo, updateCombo } from '@/src/lib/db/combos';

async function renameCombo(comboId: string, newName: string) {
  const combo = await getCombo(comboId);
  if (!combo) throw new Error('Combo not found');

  await updateCombo(comboId, { name: newName });
}

```

The `updateCombo` function executes `UPDATE combos SET name = @name, updated_at = … WHERE id = @id` and triggers an automatic database backup before returning.

## Performance Optimization and Safety

OmniRoute implements several safeguards to ensure data integrity and high performance when utilizing the 83 SQLite domain modules.

### Read-Through Cache Management

The [`readCache.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/readCache.ts) module maintains lightweight in-memory snapshots of frequently accessed tables (such as provider connections) to eliminate repeated SQLite round-trips. After any write operation, domain modules automatically call `invalidateDbCache('connections')` to maintain cache coherence (core.ts line 36).

### Migration Safety and Data Protection

The core layer protects existing user data through defensive mechanisms:

- **Probe-and-Rename Recovery**: If the SQLite file cannot be opened, it is automatically renamed to `<file>.probe-failed-<timestamp>` and a backup is restored.
- **Critical State Capture**: Functions `captureCriticalDbState` and `restoreCriticalDbState` (lines 350–380) ensure corrupted databases never silently delete user data by snapshotting essential tables before risky operations.
- **Health Monitoring**: `runManagedDbHealthCheck` performs periodic integrity checks, while `closeDbInstance` ensures graceful shutdowns with proper connection cleanup (lines 2100–2150).

## Summary

- **Import domain modules** from `src/lib/db/` (e.g., [`providers.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/providers.ts), [`combos.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/combos.ts)) to interact with specific tables rather than writing raw SQL.
- **Rely on the core layer** ([`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts)) for singleton connection management, automatic schema migrations, and WAL mode configuration.
- **Leverage automatic encryption** for sensitive fields via [`encryption.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/encryption.ts) and `encryptConnectionFields`.
- **Cache consciously** by understanding that [`readCache.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/readCache.ts) provides hot-data snapshots, with automatic invalidation handled by write operations.
- **Trust the safety mechanisms**: automatic backups before writes, probe-failed recovery, and critical state capture protect your data from corruption.

## Frequently Asked Questions

### How do I access the SQLite database directly in OmniRoute?

Direct access is possible by importing `getDbInstance` from [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts) and calling `db.prepare()`, but this is discouraged for application code. Instead, use the typed domain modules like [`providers.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/providers.ts) or [`usageHistory.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/usageHistory.ts), which handle connection reuse, field encryption, and cache invalidation automatically.

### What happens if the database becomes corrupted?

The core layer implements a probe-failed recovery system. On startup, if `getDbInstance()` cannot open the SQLite file, it renames the corrupted file with a timestamp suffix and restores from the latest valid backup. Critical tables are preserved through `captureCriticalDbState` snapshots before any destructive operation (core.ts lines 350–380).

### How does OmniRoute handle schema migrations?

Migrations are stored in `db/migrations/` as incremental SQL files. On startup, [`migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/migrationRunner.ts) compares the current database version against available migration files and applies them sequentially after loading the base `SCHEMA_SQL`. This ensures the 83 tables evolve safely without manual intervention or data loss.

### Can I use the domain modules outside of the OmniRoute server context?

Yes, provided you initialize the database environment first. Import `getDbInstance` from [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts) and ensure `DATA_DIR` and `SQLITE_FILE` environment variables are set. The singleton pattern guarantees that subsequent imports of domain modules will reuse the same connection across your application process.