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

> Explore the OmniRoute database architecture with SQLite domain modules. This guide details typed modules, raw SQL isolation, schema consistency, and AES-256 encryption for secure data management.

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

---

**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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/providers.ts), [`src/lib/db/providerNodeSelect.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/quotaPools.ts), [`src/lib/db/quotaConsumption.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/sqliteComboRepository.ts), [`src/lib/db/sqliteModelComboMappingRepository.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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

```typescript
// 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

```typescript
// 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

```typescript
// 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

```typescript
// 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`](https://github.com/diegosouzapw/OmniRoute/blob/main/migrationRunner.ts) apply versioned schema changes on startup
- **AES-256-GCM encryption** in [`encryption.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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.