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

Import the specific domain module (e.g., providers.ts, 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 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) 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 – CRUD operations for OAuth, API-key, and cookie credentials.
• combos.ts – Combo definition storage and routing metadata.
• usageHistory.ts – Token accounting and per-request audit logging.
• semanticCache.ts – Prompt deduplication and semantic similarity storage.
• 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 module encrypts sensitive fields automatically using AES-GCM. To fetch active connections:

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:

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 module:

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 module:

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 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, combos.ts) to interact with specific tables rather than writing raw SQL.
• Rely on the core layer (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 and encryptConnectionFields.
• Cache consciously by understanding that 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 and calling db.prepare(), but this is discouraged for application code. Instead, use the typed domain modules like providers.ts or 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 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 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.

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