# FreeLLMAPI SQLite Database Schema: Key Tables and Structure Explained

> Explore the FreeLLMAPI SQLite database schema, detailing key tables for models, auth, and observability. Understand its organized structure defined in TypeScript migrations.

- Repository: [Tashfeen/freellmapi](https://github.com/tashfeenahmed/freellmapi)
- Tags: api-reference
- Published: 2026-09-04

---

**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`](https://github.com/tashfeenahmed/freellmapi/blob/main/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`](https://github.com/tashfeenahmed/freellmapi/blob/main/server/src/__tests__/services/request-retention.test.ts) at line 93). The **`response_cache`** table ([`20260903_000002_response_cache.ts`](https://github.com/tashfeenahmed/freellmapi/blob/main/20260903_000002_response_cache.ts), line 31) stores deterministic responses for fast replay, while **`playground_conversations`** ([`20260820_000001_playground_conversations.ts`](https://github.com/tashfeenahmed/freellmapi/blob/main/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`](https://github.com/tashfeenahmed/freellmapi/blob/main/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`](https://github.com/tashfeenahmed/freellmapi/blob/main/20260823_000002_backups_table.ts), line 9) catalogs periodic database snapshots. To prevent duplicate processing during retries, **`idempotency_claims`** ([`20260901_000001_idempotency_claims.ts`](https://github.com/tashfeenahmed/freellmapi/blob/main/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`](https://github.com/tashfeenahmed/freellmapi/blob/main/20260819_000001_custom_model_tombstones.ts), line 11), while **`custom_endpoint_host_labels**` ([`20260802_000001_custom_endpoint_host_labels.ts`](https://github.com/tashfeenahmed/freellmapi/blob/main/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`](https://github.com/tashfeenahmed/freellmapi/blob/main/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:

```typescript
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`](https://github.com/tashfeenahmed/freellmapi/blob/main/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`](https://github.com/tashfeenahmed/freellmapi/blob/main/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`](https://github.com/tashfeenahmed/freellmapi/blob/main/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).