OmniRoute Database Architecture with SQLite Domain Modules: A Complete Technical Guide

OmniRoute uses a single SQLite database (storage.sqlite) accessed through typed domain modules that isolate raw SQL, enforce schema consistency, and provide AES-256 encryption for sensitive data.

The open-source OmniRoute project (diegosouzapw/OmniRoute) implements a production-ready database layer built on better-sqlite3 with Write-Ahead Logging (WAL) mode. Instead of scattering SQL queries throughout the codebase, OmniRoute organizes persistence logic into domain-specific modules under src/lib/db/, creating a maintainable boundary between business logic and data storage.

Core Database Infrastructure

The Singleton Connection Pattern

All database operations route through getDbInstance() in src/lib/db/core.ts. This function establishes a single shared connection using better-sqlite3 configured for WAL mode, which enables concurrent read operations while maintaining ACID compliance.

The core module exposes three critical lifecycle functions:

• getDbInstance() – Returns the singleton database connection, initializing it on first call
• resetDbInstance() – Closes and recreates the connection (useful for testing)
• closeDbInstance() – Gracefully terminates the connection pool
• runManagedDbHealthCheck() – Executes PRAGMA integrity_check with optional auto-repair logic

Production code never imports better-sqlite3 directly; instead, callers import from the domain module barrel export: import { getDbInstance } from "@/src/lib/db".

Domain Module Architecture

OmniRoute organizes database functionality into domain modules, each encapsulating a specific subsystem's tables and business rules. This architecture prevents raw SQL from leaking into application logic and ensures consistent encryption, validation, and error handling.

API Keys and Authentication

Module: src/lib/db/apiKeys.ts

The api_keys table stores encrypted provider credentials and rate-limiting metadata. The createApiKey() function automatically encrypts the key value using AES-256-GCM before insertion, while retrieval methods handle decryption transparently. This module also maintains audit columns for compliance tracking.

Provider Connection Management

Modules: src/lib/db/providers.ts, src/lib/db/providerNodeSelect.ts

These modules manage the provider_connections and provider_nodes tables, persisting OAuth tokens, circuit-breaker state, and per-model lockout timestamps. The provider module implements credential rotation logic and automatically encrypts tokens at rest using the shared encryption utilities.

Quota and Budget Enforcement

Modules: src/lib/db/quotaPools.ts, src/lib/db/quotaConsumption.ts

SQLite transactions in these modules enforce strict per-account quota limits using the quota_pools and quota_consumption tables. The atomic nature of SQLite transactions prevents race conditions during high-concurrency billing calculations, ensuring accurate budget enforcement without external locking services.

Combo Routing Strategy

Modules: src/lib/db/sqliteComboRepository.ts, src/lib/db/sqliteModelComboMappingRepository.ts

User-defined routing strategies reside in the combos table, while model_combo_mappings links specific AI models to these strategies. These repositories handle the complex many-to-many relationships between models, providers, and fallback sequences.

Proxy Registry and Telemetry

Module: src/lib/db/proxies.ts

The proxy_registry table stores active proxy definitions with latency metrics, while proxy_logs records historical performance data. This enables dynamic proxy selection based on real-time performance characteristics stored in SQLite.

Usage Analytics and Cost Tracking

Module: src/lib/db/usageAnalytics.ts

Request-level metadata lands in usage_history and call_logs, supporting cost reporting and dashboard generation. Query helpers automatically convert snake_case database columns to camelCase for TypeScript consumers using the getRecentCalls() function and its equivalents.

Reasoning Cache Layer

Module: src/lib/db/reasoningCache.ts

A hybrid in-memory and SQLite caching system stores replayable reasoning traces in the reasoning_cache table. This module manages cache invalidation and retrieval, providing sub-millisecond access to frequently requested reasoning chains while persisting them for durability.

Global Configuration

Module: src/lib/db/settings/*.ts

The settings table stores feature flags, tier limits, and global configuration values. Domain-specific setting modules provide typed accessors that validate data types and constraints at the application layer.

Schema Evolution and Migrations

Database schema changes are version-controlled through src/lib/db/migrationRunner.ts. This runner executes numbered .sql files from src/lib/db/migrations/ inside a single transaction, ensuring atomic schema updates.

On application startup, ensureDbInitialized() automatically applies pending migrations, eliminating manual intervention during deployments. The migration system supports both additive changes (new tables/columns) and complex transformations requiring data migration.

Security and Encryption Architecture

Sensitive data protection is centralized in src/lib/db/encryption.ts. This module provides AES-256-GCM encryption for column-level data protection, using the STORAGE_ENCRYPTION_KEY environment variable as the master key.

Key security features:

• API keys and OAuth tokens are encrypted before storage
• Full-database encryption is supported via the same key infrastructure
• Encryption helpers are internal to the domain modules—callers interact with plain text while the database stores ciphertext

Backup and Maintenance Operations

Hot Backup System

src/lib/db/backup.ts implements SQLite's native backup API to create timestamped copies under the directory specified by DB_BACKUPS_DIR. The createBackup() function generates consistent snapshots without blocking reads or writes, suitable for automated cron jobs or admin-triggered exports.

Vacuum and Storage Reclamation

src/lib/db/vacuumScheduler.ts manages periodic VACUUM operations to reclaim space from deleted records. The scheduler supports both incremental and full vacuum modes, configurable via environment variables to balance I/O load against storage efficiency.

Implementation Examples

Basic Database Operations

// Obtain the singleton SQLite instance
import { getDbInstance } from "@/src/lib/db";
const db = getDbInstance();

// The db object provides direct access to better-sqlite3 methods
// while maintaining connection pooling and health monitoring

Working with Domain Modules

// Insert a new API key (encryption handled internally)
import { createApiKey } from "@/src/lib/db/apiKeys";

await createApiKey({
  name: "my-service",
  key: "plain-key-value",
  expiresAt: new Date(Date.now() + 30 * 24 * 60 * 60 * 1000), // 30 days
});

Retrieving Analytics Data

// Query usage analytics (automatic camelCase conversion)
import { getRecentCalls } from "@/src/lib/db/usageAnalytics";

const calls = await getRecentCalls({ limit: 10 });
console.log(calls); // Typed array with camelCase properties

Backup and Health Operations

// Run manual database backup for admin scripts
import { createBackup } from "@/src/lib/db/backup";

await createBackup(); // Stores timestamped copy under DB_BACKUPS_DIR

// Trigger integrity check with auto-repair
import { runManagedDbHealthCheck } from "@/src/lib/db/core";

await runManagedDbHealthCheck({ autoRepair: true });

Runtime Configuration

Database paths and operational parameters are environment-driven, documented in docs/reference/ENVIRONMENT.md:

• DATA_DIR/storage.sqlite – Primary database file location
• DB_BACKUPS_DIR – Backup destination directory
• DB_BACKUP_MAX_FILES – Retention policy for backup rotation
• STORAGE_ENCRYPTION_KEY – Master encryption key for AES-256 operations

Summary

• OmniRoute uses a single SQLite database (storage.sqlite) with WAL mode enabled for concurrent access
• Domain modules in src/lib/db/ encapsulate all SQL operations, providing typed CRUD interfaces for authentication, quotas, analytics, and configuration
• Automatic migrations via migrationRunner.ts apply versioned schema changes on startup
• AES-256-GCM encryption in encryption.ts protects API keys and tokens at the column level
• Backup and vacuum utilities support hot snapshots and storage optimization without service interruption
• Health monitoring through core.ts includes integrity checks and auto-repair capabilities

Frequently Asked Questions

How does OmniRoute handle database encryption?

OmniRoute encrypts sensitive columns using AES-256-GCM via src/lib/db/encryption.ts. The system uses the STORAGE_ENCRYPTION_KEY environment variable to encrypt API keys and OAuth tokens before storage, while supporting optional full-database encryption. Domain modules handle encryption transparently—application code works with plaintext while the database stores ciphertext.

What is the WAL mode in OmniRoute's SQLite configuration?

WAL (Write-Ahead Logging) mode, configured in src/lib/db/core.ts, allows OmniRoute to process multiple read queries concurrently while maintaining write durability. This mode creates separate -wal and -shm files alongside storage.sqlite, significantly improving read performance compared to traditional rollback journal modes without sacrificing ACID compliance.

How are database migrations managed in OmniRoute?

The src/lib/db/migrationRunner.ts module automatically applies pending migrations on startup. Migration files are numbered SQL scripts stored in src/lib/db/migrations/. The runner executes these inside a single transaction via ensureDbInitialized(), ensuring that schema updates are atomic and that the application never runs against an outdated database structure.

Can OmniRoute run backup operations without stopping the service?

Yes. The src/lib/db/backup.ts module uses SQLite's native online backup API, allowing createBackup() to generate consistent snapshots while the database remains available for reads and writes. This hot-backup capability makes OmniRoute suitable for production environments requiring high availability and point-in-time recovery options.

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