# OmniRoute Database Schema: Provider Connections, Combos, API Keys & Usage History Explained

> Explore the OmniRoute database schema to understand provider connections, combos, API keys, and usage history. Learn how data is managed in SQLite for LLM routing.

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

---

**OmniRoute stores all runtime configuration and analytics data in a single SQLite database (`storage.sqlite`) with four core tables that manage LLM provider credentials, routing combinations, authentication keys, and request logging.**

The OmniRoute open-source LLM router persists its operational state in a local SQLite file rather than an external database server. According to the `diegosouzapw/OmniRoute` source code, the schema defined in [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts) creates tables for managing multi-provider authentication, pre-configured routing targets, API key scoping, and comprehensive usage tracking. Understanding this schema is essential for administrators who need to query connection health, analyze token consumption, or migrate data between instances.

## Core Tables Overview

The OmniRoute database schema centers on four primary tables that handle distinct operational concerns:

- **`provider_connections`** – Stores credentials and metadata for every upstream LLM provider (OpenAI, Anthropic, Azure, etc.)
- **`combos`** – Defines routing combinations that map requests to one or more target models
- **`api_keys`** – Manages client authentication keys with optional model restrictions
- **`usage_history`** – Append-only log of every request with token counts, latency metrics, and error codes

Each table is created via SQL statements in [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts) during the initialization phase orchestrated by `getDbInstance()`.

## Provider Connections Table

The `provider_connections` table is the most complex structure in the OmniRoute database schema, storing authentication state for every upstream provider. As implemented in [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts) (lines 227-272), this table supports multiple authentication flows including OAuth tokens, API keys, and session cookies.

### Schema Definition

```sql
CREATE TABLE IF NOT EXISTS provider_connections (
  id TEXT PRIMARY KEY,
  provider TEXT NOT NULL,
  auth_type TEXT,
  name TEXT,
  email TEXT,
  priority INTEGER DEFAULT 0,
  is_active INTEGER DEFAULT 1,
  access_token TEXT,
  refresh_token TEXT,
  expires_at TEXT,
  token_expires_at TEXT,
  scope TEXT,
  project_id TEXT,
  test_status TEXT,
  error_code TEXT,
  last_error TEXT,
  last_error_at TEXT,
  last_error_type TEXT,
  last_error_source TEXT,
  backoff_level INTEGER DEFAULT 0,
  rate_limited_until TEXT,
  health_check_interval INTEGER,
  last_health_check_at TEXT,
  last_tested TEXT,
  api_key TEXT,
  id_token TEXT,
  provider_specific_data TEXT,
  expires_in INTEGER,
  display_name TEXT,
  global_priority INTEGER,
  default_model TEXT,
  token_type TEXT,
  consecutive_use_count INTEGER DEFAULT 0,
  rate_limit_protection INTEGER DEFAULT 0,
  last_used_at TEXT,
  "group" TEXT,
  max_concurrent INTEGER,
  proxy_enabled INTEGER NOT NULL DEFAULT 1,
  per_key_proxy_enabled INTEGER NOT NULL DEFAULT 0,
  quota_visible INTEGER NOT NULL DEFAULT 1,
  quota_window_thresholds_json TEXT,
  rate_limit_overrides_json TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

```

Key columns include **`auth_type`** (distinguishing OAuth from API key authentication), **`is_active`** (soft-delete flag for connection health), and **`rate_limit_overrides_json`** (custom throttling rules per connection). The **`group`** column enables logical partitioning of connections for different organizational units or environments.

## Combos Table

The `combos` table stores **routing combinations**—pre-configured sets of targets that define how requests fan out to one or more models. This enables OmniRoute's "auto-combo" feature where a single request can be load-balanced or parallelized across multiple providers.

### Schema Structure

Defined in [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts) (lines 299-305):

```sql
CREATE TABLE IF NOT EXISTS combos (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  data TEXT NOT NULL,
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

```

The **`data`** column contains a JSON blob describing the combo configuration, typically including target models, weights, fallback rules, and routing strategies. The **`sort_order`** column controls UI presentation order in management interfaces.

## API Keys Table

Client authentication and authorization scoping are handled by the `api_keys` table. These keys allow external applications to consume the OmniRoute API with specific model restrictions.

### Schema Structure

As defined in [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts) (lines 307-315):

```sql
CREATE TABLE IF NOT EXISTS api_keys (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  key TEXT NOT NULL UNIQUE,
  machine_id TEXT,
  allowed_models TEXT DEFAULT '[]',
  no_log INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL
);

```

The **`allowed_models`** column stores a JSON array of model identifiers permitted for this key, enforcing fine-grained access control. Setting **`no_log`** to `1` excludes requests from the `usage_history` table for privacy-sensitive applications. The **`machine_id`** optional field enables binding keys to specific hardware instances.

## Usage History Table

The `usage_history` table provides the analytical backbone for billing, quota enforcement, and performance monitoring. It records every request passing through the router with granular token accounting.

### Schema Structure

Defined in [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts) (lines 323-345), with the **`endpoint`** column added by migration 105:

```sql
CREATE TABLE IF NOT EXISTS usage_history (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  provider TEXT,
  model TEXT,
  connection_id TEXT,
  account_key TEXT,
  account_label TEXT,
  account_label_priority INTEGER DEFAULT 0,
  api_key_id TEXT,
  api_key_name TEXT,
  tokens_input INTEGER DEFAULT 0,
  tokens_output INTEGER DEFAULT 0,
  tokens_cache_read INTEGER DEFAULT 0,
  tokens_cache_creation INTEGER DEFAULT 0,
  tokens_reasoning INTEGER DEFAULT 0,
  service_tier TEXT DEFAULT 'standard',
  status TEXT,
  success INTEGER DEFAULT 1,
  latency_ms INTEGER DEFAULT 0,
  ttft_ms INTEGER DEFAULT 0,
  error_code TEXT,
  timestamp TEXT NOT NULL,
  endpoint TEXT
);

```

This schema captures **Time to First Token (TTFT)** in `ttft_ms` and distinguishes between standard tokens, cached tokens, and reasoning tokens for providers like Anthropic. The `account_label` and `account_label_priority` columns support multi-tenant routing strategies where specific provider accounts carry priority weights.

## Database Initialization and Migrations

OmniRoute uses a migration-based schema management system. The [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts) file exports `getDbInstance()` which initializes the SQLite connection and executes `CREATE TABLE IF NOT EXISTS` statements. Subsequent schema changes are handled by files in `src/lib/db/migrations/`.

For example, migration 105 ([`src/lib/db/migrations/105_usage_history_endpoint.sql`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/migrations/105_usage_history_endpoint.sql)) adds the `endpoint` column to `usage_history`:

```sql
ALTER TABLE usage_history ADD COLUMN endpoint TEXT;

```

The [`src/lib/db/migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/migrationRunner.ts) orchestrates these migrations at application startup, ensuring schema compatibility across versions.

## Querying the OmniRoute Database

Administrators can interact with the SQLite database directly or through the TypeScript utility functions.

### Fetching Active Provider Connections

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

const db = getDbInstance();
const activeConns = db.prepare(
  `SELECT * FROM provider_connections 
   WHERE is_active = 1 
   ORDER BY priority ASC`
).all();

```

### Creating a New Combo

```typescript
import { getDbInstance } from '@/lib/db/core';
import { v4 as uuid } from 'uuid';

const db = getDbInstance();
const comboId = uuid();

db.prepare(
  `INSERT INTO combos (id, name, data, sort_order, created_at, updated_at)
   VALUES (?, ?, ?, ?, datetime('now'), datetime('now'))`
).run(
  comboId, 
  'high-availability-gpt4', 
  JSON.stringify({ targets: ['gpt-4o', 'claude-3-opus'] }), 
  1
);

```

### Recording Usage Metrics

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

const db = getDbInstance();
db.prepare(
  `INSERT INTO usage_history (
     provider, model, connection_id, api_key_id, api_key_name,
     tokens_input, tokens_output, latency_ms, timestamp, endpoint, success
   ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, datetime('now'), ?, ?)`
).run(
  'openai',
  'gpt-4o',
  'conn-uuid-123',
  'key-uuid-abc',
  'production-key',
  150,
  200,
  120,
  '/v1/chat/completions',
  1
);

```

## Summary

The OmniRoute database schema efficiently consolidates LLM routing infrastructure into a single SQLite file:

- **Provider connections** store diverse authentication credentials and health status in one extensible table
- **Combos** enable sophisticated multi-model routing through JSON configuration blobs
- **API keys** enforce access control with optional model scoping and privacy flags
- **Usage history** captures granular telemetry for every request including token breakdowns and latency metrics
- **Migration system** ensures schema evolution without data loss, as demonstrated by the endpoint column addition in migration 105

These tables interact to provide the routing, authentication, and observability layers required for production LLM proxy deployments.

## Frequently Asked Questions

### Where is the OmniRoute database file located?

OmniRoute creates and accesses a file named `storage.sqlite` in the application root directory. This path is hardcoded in [`src/lib/db/core.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/core.ts) and the file is created automatically on first run if it does not exist.

### What authentication types does the provider_connections table support?

The `auth_type` column accepts values including "oauth", "api_key", "cookie", and "custom", allowing OmniRoute to store credentials for providers using OAuth 2.0 flows, simple API keys, session-based authentication, or provider-specific schemes. Specific credential data resides in `access_token`, `api_key`, or `provider_specific_data` columns depending on the authentication method.

### How does OmniRoute handle schema updates?

Schema migrations run automatically when the application starts. The [`src/lib/db/migrationRunner.ts`](https://github.com/diegosouzapw/OmniRoute/blob/main/src/lib/db/migrationRunner.ts) checks the current schema version against migration files in `src/lib/db/migrations/` and applies any missing SQL scripts sequentially. For example, migration 105 added the `endpoint` column to `usage_history` using a standard SQLite `ALTER TABLE` statement.

### Can I query usage_history directly for billing analysis?

Yes. The `usage_history` table is designed for direct SQL querying and contains all necessary billing fields including `tokens_input`, `tokens_output`, `tokens_cache_read`, and `api_key_id` for per-customer aggregation. Note that entries with `success = 0` indicate failed requests that typically should not be billed, while rows with `no_log = 1` in the `api_keys` table will not appear in this history.