How OmniRoute Stores and Manages Its Data: SQLite Architecture with Encrypted Persistence

OmniRoute uses a single SQLite database via better-sqlite3 as its central persistence layer, storing all runtime state—from provider catalogs and quota accounting to prompt-compression analytics—within an encrypted, migratable file with WAL journaling enabled.

The OmniRoute routing engine delegates every piece of durable state to a local SQLite instance managed through a strongly-typed TypeScript layer. This architecture ensures ACID compliance, transparent column-level encryption for sensitive credentials, and zero-config deployment while supporting high-throughput read operations via WAL mode.

Core Database Engine and Initialization

The database lifecycle is orchestrated in src/lib/db/core.ts, which lazily instantiates a singleton better-sqlite3 connection when the application starts. The implementation enables Write-Ahead Logging (WAL) mode to prevent writer starvation and applies the SCHEMA_SQL constant to create 17 base tables if they do not exist.

Before any query executes, src/lib/db/migrationRunner.ts inspects the internal _omniroute_migrations table and applies pending schema changes from the db/migrations/ directory (containing 110 incremental migration files), guaranteeing idempotent upgrades across releases.

Schema Design and Domain Tables

The initial schema defined in src/lib/db/core.ts establishes the relational foundation for OmniRoute’s feature set:

• providers and provider_connections – Store LLM endpoint metadata, authentication credentials, and connection pooling settings
• combos and model_combo_mappings – Define routing strategies and model-to-provider mappings
• quota_* tables – Snapshot per-API-key consumption and enforce rate limits
• compression_* tables – Archive prompt-compression analytics and combo-specific compression rules
• sessions and session_account_affinity – Link user sessions to provider accounts for sticky routing

Data Access Layer and TypeScript Modules

Rather than scattering raw SQL throughout the codebase, OmniRoute encapsulates each domain into dedicated CRUD modules that import the singleton instance via getDbInstance(). Each module exposes typed functions and re-exports through src/lib/db/index.ts (facilitated by src/lib/db/localDb.ts), allowing the rest of the application to import a single entry point.

Domain	Source Module	Key Functions
Provider Management	src/lib/db/providers.ts	listProviders(), upsertProvider()
Routing Configuration	src/lib/db/combos.ts	listCombos(), createCombo()
Quota & Billing	src/lib/db/quotaSnapshots.ts	recordQuotaSnapshot(), getQuotaForKey()
Analytics & Telemetry	src/lib/db/detailedLogs.ts	insertLogEntry(), queryLogStats()
Prompt Compression	src/lib/db/compression.ts	storeCompressionResult(), getCompressionStats()
Caching Layers	src/lib/db/semanticCache.ts, reasoningCache.ts	setCache(), getCache()
Secure Secrets	src/lib/db/secrets.ts	storeSecret(), fetchSecret()
User Assets	src/lib/db/files.ts	saveFile(), readFile()

Encryption at Rest and Security Controls

Sensitive columns containing API keys, OAuth tokens, and internal encryption keys are protected by the helpers in src/lib/db/encryption.ts. The module derives a per-user master key from the environment variable DATA_ENCRYPTION_KEY (never hard-coded) and stores only ciphertext in the database.

Decryption occurs transparently inside the DB accessor methods, ensuring that callers receive plain values without managing cryptographic operations themselves.

Runtime Maintenance and Operations

OmniRoute includes automated housekeeping to preserve database integrity and file size:

• Vacuuming – src/lib/db/vacuumScheduler.ts runs periodic VACUUM commands to reclaim storage after high-churn operations
• Backup & Recovery – src/lib/db/backup.ts creates timestamped snapshots, while src/lib/db/recovery.ts handles restoration workflows
• Health Monitoring – src/lib/db/healthCheck.ts executes SELECT 1 sanity checks and reports corruption warnings before they cascade

Concurrency and Transaction Safety

Because SQLite operates within a single process, OmniRoute serializes writes by default while exposing a read-only replica via db.reader to prevent blocking during heavy analytical queries. Every mutating operation wraps statements in explicit BEGIN … COMMIT blocks within the TypeScript modules, ensuring transactional integrity for multi-table updates such as quota deductions and log insertions.

Practical Implementation Examples

Registering a new provider with automatic encryption:

import { upsertProvider } from '@/lib/db/providers';

await upsertProvider({
  id: 'openai',
  name: 'OpenAI',
  apiKey: process.env.OPENAI_API_KEY, // encrypted automatically
  config: { baseUrl: 'https://api.openai.com/v1' },
});

Recording real-time quota consumption:

import { recordQuotaSnapshot } from '@/lib/db/quotaSnapshots';

await recordQuotaSnapshot({
  apiKeyId: key.id,
  model: 'gpt-4',
  tokensUsed: 215,
  timestamp: Date.now(),
});

Retrieving compression analytics for optimization:

import { getCompressionStats } from '@/lib/db/compression';

const stats = await getCompressionStats({ comboId: 'fast-lite' });
console.log(`Saved ${stats.tokensSaved} tokens (${stats.savingsPct}%)`);

Executing manual database maintenance:

import { runVacuum } from '@/lib/db/vacuumScheduler';
import { createBackup } from '@/lib/db/backup';

await runVacuum(); // reclaims storage via SQLite VACUUM
const backupPath = await createBackup('/tmp/omniroute-backup');
console.log(`Backup saved at ${backupPath}`);

Accessing cached reasoning results:

import { getCache } from '@/lib/db/reasoningCache';

const cached = await getCache({ requestId: 'abc123' });
if (cached) {
  console.log('Using cached reasoning output');
}

Summary

• OmniRoute centralizes all state in a SQLite database managed by better-sqlite3 with WAL journaling enabled in src/lib/db/core.ts
• Schema migrations are handled automatically by src/lib/db/migrationRunner.ts, tracking applied changes in _omniroute_migrations
• Column-level encryption via src/lib/db/encryption.ts secures API keys using the DATA_ENCRYPTION_KEY environment variable
• Domain-specific modules (combos.ts, quotaSnapshots.ts, compression.ts, etc.) provide type-safe CRUD operations through a singleton connection
• Runtime maintenance includes automated vacuuming (vacuumScheduler.ts), timestamped backups (backup.ts), and corruption checks (healthCheck.ts)
• Concurrency is managed through explicit transactions and a read-only replica to maximize throughput without sacrificing consistency

Frequently Asked Questions

How does OmniRoute handle database schema updates?

OmniRoute applies incremental migrations automatically at startup. The src/lib/db/migrationRunner.ts module compares the filesystem’s db/migrations/ directory (containing 110 migration files) against the _omniroute_migrations table in the database, executing only those scripts that have not yet been recorded. This ensures zero-downtime, idempotent schema evolution across versions.

Is the SQLite database encrypted as a whole or per column?

OmniRoute implements per-column encryption rather than full-disk encryption. The src/lib/db/encryption.ts module encrypts individual fields (such as API keys in the providers table) using a master key derived from the DATA_ENCRYPTION_KEY environment variable. This approach allows non-sensitive data to remain queryable while credentials remain protected at rest.

Can OmniRoute run multiple instances against the same database file?

No. Because SQLite locks are process-level and OmniRoute relies on a singleton connection pattern in src/lib/db/core.ts, only one Node.js process should open the database at a time. For horizontal scaling, OmniRoute supports file-level backups to shared storage, but concurrent write access from multiple processes would result in database lock errors.

What happens if the database file becomes corrupted?

The src/lib/db/healthCheck.ts module provides lightweight corruption detection via PRAGMA integrity_check and SELECT 1 heartbeat queries. If corruption is detected, administrators can restore from timestamped snapshots created by src/lib/db/backup.ts, which can be triggered manually or scheduled to run automatically according to the configuration in the backup module.

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