SQLite Database Schema for Providers, Combos, and Quotas in OmniRoute

OmniRoute persists all configuration and runtime data in a normalized SQLite database with three core table groups: provider_connections for authentication and limits, combos for routing strategies, and quota_* tables for usage tracking and enforcement.

OmniRoute implements a relational SQLite schema to manage AI provider routing, combination strategies, and quota enforcement. The database design separates concerns between connection credentials, routing logic, and consumption tracking, enabling flexible multi-provider deployments with fine-grained rate limiting.

Provider Connections Schema

The provider_connections table serves as the atomic unit of access within the OmniRoute database. Defined in the initial migration at src/lib/db/migrations/001_initial_schema.sql, this table stores authentication credentials, activation states, and runtime constraints for each provider-account pairing.

Core Columns and Configuration

Each row in provider_connections represents a distinct provider endpoint with the following key fields:

• id – Primary key for the connection
• provider – Provider identifier (e.g., OpenAI, Anthropic)
• account_id – Optional account grouping field
• is_active – Boolean flag enabling or disabling the connection
• priority – Routing priority for fallback ordering
• max_concurrent – Maximum simultaneous requests allowed
• quota_visible – Boolean determining if quota displays in monitoring
• proxy_enabled and per_key_proxy_enabled – Proxy configuration flags
• last_ping_at and last_pinged_reset_key – Health check timestamps
• quota_window_thresholds_json – JSON configuration for custom quota reset windows

Routing Combos Schema

OmniRoute defines routing combinations in the combos table, implemented in src/lib/db/combos.ts. Combos represent ordered lists of provider connections with specific strategies for request distribution.

Table Structure and Relationships

The combos table contains:

• id – Primary key
• name and description – Human-readable identifiers
• targets – JSON array storing objects with providerConnectionId, modelId, and weight properties
• strategy – Routing algorithm (e.g., priority, weighted, round-robin)
• created_at and updated_at – Timestamp tracking

The targets JSON field maintains the relationship to provider_connections without strict foreign key constraints, allowing flexible reordering and dynamic target resolution through the resolveComboTargets() function.

Quota Enforcement Tables

OmniRoute implements a hierarchical quota system across four specialized tables that track usage from individual connections up to grouped pools.

Quota Snapshots

The quota_snapshots table in src/lib/db/quotaSnapshots.ts captures periodic usage metrics per connection:

• connection_id – Reference to the provider connection
• tokens_used and requests – Consumption counters
• snapshot_at – Timestamp for the recorded metrics

Quota Pools and Groups

Defined in src/lib/db/quotaPools.ts and src/lib/db/quotaGroups.ts, these tables enable shared and hierarchical quota management:

quota_pools columns:

• id – Pool identifier
• provider – Associated provider type
• quota_limit – Maximum allowance
• reset_policy – Daily, monthly, or custom window configuration

quota_groups columns:

• id – Group identifier
• pool_ids – JSON array of associated pool IDs
• group_quota – Aggregate limit across pooled connections

Quota Consumption Logging

The quota_consumption table in src/lib/db/quotaConsumption.ts records individual request costs:

• pool_id – Reference to the quota pool
• cost – Token or request count consumed
• consumed_at – ISO timestamp of the transaction

Database Relationships and Flow

The OmniRoute SQLite schema connects these three domains through logical relationships:

1. Provider connections store the underlying credentials and limits in provider_connections
2. Combos reference connections via JSON in the targets field, resolving to concrete provider instances at runtime
3. Quota tables link back through quota_pools.provider (matching provider_connections.provider) and quota_consumption.pool_id, creating a chain from request → consumption → pool → provider

The quota_window_thresholds_json column in provider_connections stores reset policies that the quota system evaluates against quota_consumption.consumed_at timestamps to enforce window-based throttling.

Working with the Schema

The following examples demonstrate how to interact with the OmniRoute database schema using the provided helper functions.

Retrieve active provider connections:

import { getProviderConnections } from "@/lib/db/providers";

const activeConns = await getProviderConnections({ is_active: 1 });

Resolve a combo to concrete provider targets:

import { resolveComboTargets, getComboByName } from "@/lib/db/combos";

const combo = await getComboByName("default-chat");
const targets = await resolveComboTargets(combo.id);

Record quota consumption for a request:

import { recordQuotaConsumption } from "@/lib/db/quotaConsumption";

await recordQuotaConsumption({
  poolId: pool.id,
  cost: tokenCount,
  consumedAt: new Date().toISOString(),
});

Summary

• Provider connections are defined in 001_initial_schema.sql with columns for credentials, activation state, and quota configuration
• Combos stored in src/lib/db/combos.ts use a JSON targets array to reference provider connections with weight and strategy metadata
• Quota enforcement spans four tables: quota_snapshots for metrics, quota_pools and quota_groups for limit hierarchy, and quota_consumption for transaction logging
• All tables reside in a single SQLite database under src/lib/db/migrations/, with helper functions in corresponding TypeScript files for type-safe access

Frequently Asked Questions

Where is the SQLite database schema defined in OmniRoute?

The initial schema creation resides in src/lib/db/migrations/001_initial_schema.sql, which defines the provider_connections table. Subsequent migrations add quota-related columns like quota_visible and quota_window_thresholds_json, while separate TypeScript files in src/lib/db/ define the combos, quota_snapshots, quota_pools, quota_groups, and quota_consumption tables.

How does the quota system track usage across multiple provider connections?

OmniRoute tracks usage through the quota_pools table, which groups connections by the provider column. Each request logs consumption to quota_consumption with a pool_id reference. The system aggregates these records against quota_groups (which contain JSON arrays of pool_ids) to enforce hierarchical limits across connection pools.

What data structure does the targets JSON field contain in the combos table?

The targets column in the combos table stores a JSON array of objects, each containing providerConnectionId (referencing provider_connections.id), modelId (specific model identifier), and weight (numeric value for weighted routing strategies). This structure enables the resolveComboTargets() function to map abstract combo names to concrete provider endpoints.

How do quota windows and reset policies work in the database?

Reset policies are stored as JSON in the quota_window_thresholds_json column of provider_connections, defining windows such as daily or monthly cycles. The system evaluates these thresholds against consumed_at timestamps in the quota_consumption table to determine when counters reset, while quota_snapshots provides periodic backups of usage states for historical analysis.

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