OmniRoute Database Layer Architecture: Modular SQLite with TypeScript

OmniRoute implements its database layer as a modular, type-safe SQLite abstraction under src/lib/db/, using a singleton connection pattern, entity-specific CRUD modules, and a migration-driven schema evolution system.

All persistent state in OmniRoute flows through a single SQLite database accessed via a thin TypeScript-based data access layer. This architecture centralizes database operations within src/lib/db/ while enforcing strict separation between raw SQL and business logic, giving the routing engine a lean yet robust persistence mechanism for providers, API keys, and usage telemetry.

Core Connection Management: The Singleton Pattern

At the heart of the database layer sits src/lib/db/core.ts, which exports getDbInstance() to manage the SQLite connection lifecycle. This function lazily creates—or re-opens—a better-sqlite3 connection wrapped in a SqliteAdapter object, ensuring that every module shares the same database handle without creating multiple connections.

import { getDbInstance } from '@/lib/db/core';

// All database access begins here
const db = getDbInstance();
const row = db.prepare('SELECT * FROM providers WHERE id = ?').get(providerId);

Every other module in src/lib/db/ imports this singleton function rather than instantiating connections directly. This design guarantees connection pooling by default and provides a single point of failure for connection debugging or telemetry injection.

Entity-Module Organization

The database layer follows a strict module-per-entity pattern where each logical table lives in its own file. Rather than scattering queries across handlers, OmniRoute co-locates all data access logic for a specific domain within dedicated modules:

• src/lib/db/providers.ts – CRUD operations for provider discovery and routing configuration
• src/lib/db/apiKeys.ts – API key storage, usage limits, and policy enforcement
• src/lib/db/usageLogs.ts – Per-request analytics and billing telemetry
• src/lib/db/settings.ts – Global configuration and feature-flag persistence

Each module exposes typed helper functions such as getProvider(), upsertApiKey(), or deleteUsageLog(), hiding raw SQL behind strongly-typed interfaces. These modules also export companion TypeScript interfaces like ProviderRow and ApiKeyRow that mirror the database schema, providing compile-time safety for callers throughout the application.

import { getDbInstance } from '@/lib/db/core';
import { ProviderRow } from '@/lib/db/providers';

export async function getProvider(id: string): Promise<ProviderRow | null> {
  const db = getDbInstance();
  return db.prepare('SELECT * FROM providers WHERE id = ?')
           .get(id) as ProviderRow | null;
}

Schema Evolution and Migrations

Database schema versioning lives in src/lib/db/migrationRunner.ts, which executes sequential SQL migrations located in src/lib/db/migrations/. The runner ensures the database version matches the codebase on every startup, preventing drift between deployed application code and the underlying schema.

import { runMigrations } from '@/lib/db/migrationRunner';

// Called during application initialization
await runMigrations(); // Ensures schema is up-to-date

Each migration file follows the incremental .sql naming convention and contains atomic DDL statements. This approach allows OmniRoute to evolve its database layer across releases without downtime or manual intervention, critical for maintaining state consistency in production routing clusters.

Type Safety and Utility Patterns

Beyond basic CRUD, the database layer centralizes common data patterns in utility modules. Helpers like rowToCamel convert snake_case column names to camelCase JavaScript properties, while TTLCache implementations optimize repeated lookups for configuration data. JSON column handling and soft-delete logic are also standardized here, ensuring that modules like src/lib/db/apiKeys.ts remain focused on domain logic rather than serialization boilerplate.

Type definitions such as ApiKeyRow and ProviderRow are defined alongside their respective modules, creating a tight coupling between the SQL schema and TypeScript contracts. This eliminates the need for a heavy ORM while maintaining strict compile-time verification of database interactions.

Isolation of Business Logic from Persistence

A strict architectural constraint governs the database layer: handlers, services, and API routes never issue raw SQL. Instead, they exclusively consume the exported functions from the src/lib/db/ modules. This guarantees a single point of change for schema updates—when the providers table structure changes, only src/lib/db/providers.ts requires modification, leaving the routing logic untouched.

Feature-flag-driven tables in files like src/lib/db/featureFlags.ts and src/lib/db/piiSanitizer.ts extend this isolation further. These tables are only accessed when corresponding feature flags are enabled, keeping the core schema lean and ensuring that experimental features do not impact baseline database performance.

import { upsertApiKey } from '@/lib/db/apiKeys';

// Business logic calls typed functions, never raw SQL
await upsertApiKey({
  id: 'key-123',
  name: 'User CLI',
  hashedSecret: hash('super-secret'),
  scopes: ['chat:write'],
});

Summary

• Singleton connection: getDbInstance() in src/lib/db/core.ts manages a single better-sqlite3 adapter shared across the application
• Modular entities: Each table (providers, API keys, usage logs) has its own module with typed CRUD helpers under src/lib/db/
• Migration safety: migrationRunner.ts automates schema evolution using versioned .sql files in src/lib/db/migrations/
• Type safety: Row types like ProviderRow co-located with modules provide compile-time guarantees without ORM overhead
• Clean architecture: Business logic remains completely isolated from SQL implementation details

Frequently Asked Questions

Does OmniRoute use an ORM for its database layer?

No. OmniRoute intentionally avoids heavy ORM frameworks in favor of a thin data-access layer. The codebase uses better-sqlite3 directly through prepared statements wrapped in typed helper functions, providing the performance benefits of raw SQL with the type safety of TypeScript interfaces.

How does OmniRoute handle database schema changes?

Schema changes are managed through sequential SQL migrations stored in src/lib/db/migrations/. The migrationRunner.ts script executes these files on startup, comparing the current database version against the codebase expectations and applying pending migrations atomically.

Where are TypeScript types for database rows defined?

Row types like ApiKeyRow, ProviderRow, and UsageLogRow are defined alongside their respective database modules in src/lib/db/. For example, ProviderRow lives in src/lib/db/providers.ts, ensuring tight coupling between the SQL schema and TypeScript contracts.

Can OmniRoute run with a different database engine like PostgreSQL?

Currently, no. The database layer is tightly coupled to SQLite through better-sqlite3 and the SqliteAdapter in core.ts. While the modular structure abstracts specific SQL dialects within the CRUD helpers, the connection management and migration system assume SQLite file-based semantics throughout the codebase.

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