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

> Discover how OmniRoute stores and manages data using an encrypted SQLite database with WAL journaling for robust persistence and efficient management of runtime state.

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

---

**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](https://github.com/diegosouzapw/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/index.ts) (facilitated by [`src/lib/db/localDb.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/providers.ts) | `listProviders()`, `upsertProvider()` |
| Routing Configuration | [`src/lib/db/combos.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/combos.ts) | `listCombos()`, `createCombo()` |
| Quota & Billing | [`src/lib/db/quotaSnapshots.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/quotaSnapshots.ts) | `recordQuotaSnapshot()`, `getQuotaForKey()` |
| Analytics & Telemetry | [`src/lib/db/detailedLogs.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/detailedLogs.ts) | `insertLogEntry()`, `queryLogStats()` |
| Prompt Compression | [`src/lib/db/compression.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/compression.ts) | `storeCompressionResult()`, `getCompressionStats()` |
| Caching Layers | [`src/lib/db/semanticCache.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/semanticCache.ts), [`reasoningCache.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/reasoningCache.ts) | `setCache()`, `getCache()` |
| Secure Secrets | [`src/lib/db/secrets.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/secrets.ts) | `storeSecret()`, `fetchSecret()` |
| User Assets | [`src/lib/db/files.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/vacuumScheduler.ts) runs periodic `VACUUM` commands to reclaim storage after high-churn operations
- **Backup & Recovery** – [`src/lib/db/backup.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/backup.ts) creates timestamped snapshots, while [`src/lib/db/recovery.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/recovery.ts) handles restoration workflows
- **Health Monitoring** – [`src/lib/db/healthCheck.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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:**

```typescript
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:**

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

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

```

**Retrieving compression analytics for optimization:**

```typescript
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:**

```typescript
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:**

```typescript
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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts)
- **Schema migrations** are handled automatically by [`src/lib/db/migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/migrationRunner.ts), tracking applied changes in `_omniroute_migrations`
- **Column-level encryption** via [`src/lib/db/encryption.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/encryption.ts) secures API keys using the `DATA_ENCRYPTION_KEY` environment variable
- Domain-specific modules ([`combos.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/combos.ts), [`quotaSnapshots.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/quotaSnapshots.ts), [`compression.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/compression.ts), etc.) provide type-safe CRUD operations through a singleton connection
- **Runtime maintenance** includes automated vacuuming ([`vacuumScheduler.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/vacuumScheduler.ts)), timestamped backups ([`backup.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/backup.ts)), and corruption checks ([`healthCheck.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/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`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/backup.ts), which can be triggered manually or scheduled to run automatically according to the configuration in the backup module.