FreeLLMAPI SQLite Database Schema: Key Tables and Structure Explained

TLDR: The FreeLLMAPI SQLite database schema comprises over 20 normalized tables organized into model catalogues, authentication systems, request observability, quota enforcement, and backup resiliency, all defined in TypeScript migration files under server/src/db/migrations/.

The open-source FreeLLMAPI project (tashfeenahmed/freellmapi) persists operational data in a SQLite database accessed via the better-sqlite3 driver. The schema is instantiated through executable migrations rather than raw SQL files, with the foundational structure established in the legacy baseline migration and extended by targeted feature migrations. Understanding this SQLite database schema is essential for operating the proxy, debugging request flows, or extending the platform’s data model.

Model Catalogue Tables

The core entity-relationship model revolves around LLM capabilities and their metadata. These tables define what models are available, their modalities, and behavioral quirks.

models, embedding_models, and media_models

The models table serves as the primary registry for all LLM providers, storing capabilities and tool-support flags. It is created at line 60 of the legacy baseline migration. Complementary tables partition models by capability: embedding_models (line 1964) indexes text-embedding models, while media_models (line 2032) registers image, audio, and video generation models.

quirks and quirk_targets

Per-model behavioral exceptions are stored in quirks (line 2074) with their associations tracked in quirk_targets (line 2083). This Many-to-Many relationship allows the server to apply special handling rules—such as tokenization fixes or endpoint variations—without hardcoding provider logic.

Authentication and Profile Management

User identity and access control are centralized in a hierarchy of profile and session tables.

api_keys and url_tokens

Platform-specific credentials reside in api_keys (legacy baseline, line 79), which stores encrypted keys for OpenAI, Anthropic, and other providers. The url_tokens table, introduced in the agent-compatibility migration at line 17, manages ephemeral authentication tokens for URL-based access patterns.

users, sessions, and profiles

The users table (line 161) anchors identity records, while sessions (line 168) tracks web UI login state. Logical groupings are handled by profiles (line 133), with profile_models (line 146) enforcing which models a given profile may access. Tenant-level customization is further supported by client_profiles (defined in 20260805_000002_client_profiles.ts at line 19), which stores per-client configuration overrides.

Request Tracking and Observability

Every inference call is instrumented through a granular logging hierarchy that supports debugging, analytics, and caching.

requests and request_attempts

The requests table (line 92) records the metadata for every LLM call submitted to the server. Individual retry attempts—including backoff timing and transient error details—are logged to request_attempts (line 105), enabling reconstruction of full request lifecycles.

Aggregated and Cached Data

For performance analytics, request_hourly aggregates call volumes by time window (referenced in server/src/__tests__/services/request-retention.test.ts at line 93). The response_cache table (20260903_000002_response_cache.ts, line 31) stores deterministic responses for fast replay, while playground_conversations (20260820_000001_playground_conversations.ts, line 29) persists chat history for the web playground UI. System-level telemetry is written to server_logs (20260823_000001_server_logs.ts, line 34).

Quota and Rate Limit Management

The schema enforces hard and soft limits through a dedicated quota subsystem.

rate_limit_usage and cooldowns

The rate_limit_usage table (line 105) tracks per-model consumption against allocated quotas. When thresholds are breached, rate_limit_cooldowns (line 116) records the enforcement window during which requests are blocked.

Provider-level quotas and fallbacks

Global provider states are maintained in provider_quota_state (line 182), with historical observations stored in provider_quota_observations (line 201) to enable usage forecasting. The fallback_config table (line 125) stores routing rules that trigger when primary quotas are exhausted.

Backup and Resiliency Tables

Operational durability is supported by metadata tables for backups and idempotency.

backups and idempotency_claims

The backups table (20260823_000002_backups_table.ts, line 9) catalogs periodic database snapshots. To prevent duplicate processing during retries, idempotency_claims (20260901_000001_idempotency_claims.ts, line 27) tracks processed tokens with TTL semantics.

Cleanup and Labeling

Deferred deletion of custom models is handled by custom_model_tombstones (20260819_000001_custom_model_tombstones.ts, line 11), while **custom_endpoint_host_labels** (20260802_000001_custom_endpoint_host_labels.ts, line 2) stores host categorization metadata. API key scoping is enforced via key_model_scope (20260805_000001_key_model_scope.ts, line 5), which maps keys to permitted model subsets.

Querying the FreeLLMAPI Schema

When extending the server or performing operational analytics, you can query these tables directly using better-sqlite3. Below are common read patterns against the core schema:

import Database from 'better-sqlite3';
const db = new Database('freellmapi.db');

// Fetch all registered models with their quirks
const modelsWithQuirks = db.prepare(`
  SELECT m.id, m.name, GROUP_CONCAT(q.name) as quirks
  FROM models m
  LEFT JOIN quirk_targets qt ON qt.model_id = m.id
  LEFT JOIN quirks q ON q.id = qt.quirk_id
  GROUP BY m.id
`).all();

// Retrieve the latest 10 requests with attempt counts
const recentRequests = db.prepare(`
  SELECT r.id, r.model_id, r.created_at, COUNT(a.id) AS attempts
  FROM requests r
  LEFT JOIN request_attempts a ON a.request_id = r.id
  GROUP BY r.id
  ORDER BY r.created_at DESC
  LIMIT 10
`).all();

// Check remaining quota for a specific model
const quota = db.prepare(`
  SELECT used, limit, (limit - used) AS remaining
  FROM rate_limit_usage
  WHERE model_id = ?
`).get('gpt-4-turbo');

// Insert a new provider API key
db.prepare(`
  INSERT INTO api_keys (platform, key, created_at)
  VALUES (?, ?, datetime('now'))
`).run('anthropic', 'sk-ant-xxxxx');

These queries operate against the exact tables defined in server/src/db/migrations/20260101_000000_legacy_baseline.ts and subsequent migration files, reflecting the runtime state of the FreeLLMAPI SQLite database schema.

Summary

• The FreeLLMAPI SQLite database schema is defined through TypeScript migrations in server/src/db/migrations/, with the core baseline established in 20260101_000000_legacy_baseline.ts.
• Model metadata is partitioned across models, embedding_models, media_models, and behavioral quirks tables.
• Authentication relies on api_keys, url_tokens, and a hierarchical profile system (profiles, profile_models, client_profiles).
• Request observability spans granular logs (requests, request_attempts), aggregate analytics (request_hourly), caching (response_cache), and UI state (playground_conversations).
• Quota enforcement uses rate_limit_usage, rate_limit_cooldowns, and provider-specific state tables to manage consumption and fallbacks.
• Resiliency features include backups, idempotency_claims, and tombstone tables for graceful resource cleanup.

Frequently Asked Questions

What is the primary migration file for the FreeLLMAPI database schema?

The foundational schema is created by server/src/db/migrations/20260101_000000_legacy_baseline.ts. This migration instantiates the essential tables—including models, api_keys, requests, and rate_limit_usage—that constitute the core data model. Subsequent migrations add specialized tables like response_cache and idempotency_claims without altering the baseline structure.

How does FreeLLMAPI store API keys in the SQLite database?

API keys are stored in the api_keys table (defined at line 79 of the legacy baseline migration). Each row records the provider platform (e.g., "openai", "anthropic"), the encrypted key string, and timestamps. Scope restrictions are enforced separately via the key_model_scope table, which links specific keys to permitted models.

Which table tracks individual request attempts and retries?

The request_attempts table (line 105 in the legacy baseline migration) logs every discrete attempt made for a given request, including transient errors and retry timestamps. It maintains a foreign key relationship to the parent requests table (line 92), enabling detailed reconstruction of request lifecycles and failure analysis.

How is rate limiting data persisted in the FreeLLMAPI schema?

Rate limiting state is distributed across several tables. rate_limit_usage (line 105) stores current consumption counters per model, while rate_limit_cooldowns (line 116) records active enforcement windows. Provider-level aggregate state is maintained in provider_quota_state (line 182) and provider_quota_observations (line 201), which support forecasting algorithms to preemptively trigger fallback configurations stored in fallback_config (line 125).

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