# FreeLLMAPI Database Schema and Migrations: Complete Technical Reference

> Explore the FreeLLMAPI database schema and understand how TypeScript migrations ensure reproducible schema evolution. Learn about automatic startup migrations and idempotency.

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

---

**FreeLLMAPI persists all data in a single SQLite file (`freeapi.db`) and automatically executes TypeScript-based migrations at startup, recording each applied change in a dedicated `migrations` table to ensure idempotent, reproducible schema evolution.**

The FreeLLMAPI repository implements a self-contained data layer using SQLite, eliminating the need for external database servers. Its migration system handles everything from initial table creation to incremental schema updates through TypeScript modules that execute inside transactions, guaranteeing that both fresh installations and existing deployments maintain consistent data structures.

## Core Database Schema Overview

The baseline schema is defined in [`src/db/migrations/20260101_000000_legacy_baseline.ts`](https://github.com/tashfeenahmed/freellmapi/blob/main/src/db/migrations/20260101_000000_legacy_baseline.ts) and establishes fourteen primary tables that handle LLM routing, authentication, quota management, and administrative functions.

### Baseline Tables and Relationships

The initial migration creates the following tables:

- **models** – Stores every LLM model the API can route to, including `platform`, `model_id`, `display_name`, `intelligence_rank`, `speed_rank`, rate limits (`rpm_limit`, `rpd_limit`, `tpm_limit`, `tpd_limit`), `monthly_token_budget`, `context_window`, `enabled`, and `supports_vision`.

- **api_keys** – Encrypts user-provided provider keys with columns for `platform`, `label`, `encrypted_key`, `iv`, `auth_tag`, `status`, `enabled`, `created_at`, `last_checked_at`, and `base_url`.

- **requests** – Logs every API request with `platform`, `model_id`, `key_id`, `status`, `input_tokens`, `output_tokens`, `latency_ms`, `error`, `created_at`, `requested_model`, and `ttfb_ms`.

- **rate_limit_usage** – Tracks per-key usage counters for the rate limiter using `platform`, `model_id`, `key_id`, `kind` (request or tokens), `tokens`, `created_at_ms`, and `created_at`.

- **rate_limit_cooldowns** – Manages temporary bans for keys exceeding limits with `platform`, `model_id`, `key_id`, `expires_at_ms`, and `created_at`.

- **fallback_config** – Defines preference ordering for fallback models when primary models are unavailable, using `model_db_id` (foreign key to `models.id`), `priority`, and `enabled`.

- **profiles** – Supports UI profiles for the desktop client with `name`, `emoji`, `color`, `type`, `is_favorite`, `sort_order`, `auto_sort`, `layout_config`, and `created_at`.

- **profile_models** – Associates profiles with models via `profile_id` (foreign key to `profiles.id`), `model_db_id` (foreign key to `models.id`), `priority`, and `enabled`.

- **settings** – Provides a generic key-value store using `key` as the primary key and `value`.

- **users** – Manages dashboard accounts with `email`, `password_hash`, and `created_at`.

- **sessions** – Stores session tokens for authenticated users with `token_hash` (primary key), `user_id` (foreign key to `users.id`), `expires_at_ms`, and `created_at`.

- **provider_quota_state** – Caches current quota information with `platform`, `key_id`, `quota_pool_key`, `metric`, `limit_value`, `remaining_value`, `reset_at`, `reset_strategy`, `source`, `confidence`, `notes`, `observed_at`, and `updated_at`.

- **provider_quota_observations** – Maintains historical quota probe logs including `id`, `platform`, `key_id`, `provider_account_id`, `model_id`, and detailed quota metrics.

- **migrations** – Created automatically by the migration runner to track applied changes with `id`, `filename`, and `applied_at`.

### Runtime Schema Updates

Additional columns are added idempotently at runtime via helper functions that guard against duplicate column creation. These include `ensureRequestKeyIdColumn`, `ensureApiKeysBaseUrlColumn`, `ensureModelsKeyIdColumn`, `ensureRequestTtfbColumn`, and `ensureRequestRequestedModelColumn`. Each function checks `PRAGMA table_info` before executing `ALTER TABLE … ADD COLUMN`.

## Migration System Architecture

The migration engine resides in [`src/db/migrate/runner.ts`](https://github.com/tashfeenahmed/freellmapi/blob/main/src/db/migrate/runner.ts) and operates synchronously during database initialization. When the server starts, `initDb()` opens the database, enables foreign-key checks, and invokes the migration runner unless explicitly disabled in development mode.

### Migration File Structure

Each migration is a TypeScript module stored in `src/db/migrations/` that exports two functions:

```typescript
export function up(db: Database) {
  // Schema changes executed inside a transaction
}

export function down(db: Database) {
  // Rollback logic to reverse the changes
}

```

File names follow the pattern [`YYYYMMDD_HHMMSS_description.ts`](https://github.com/tashfeenahmed/freellmapi/blob/main/YYYYMMDD_HHMMSS_description.ts), such as [`20260628_120000_request_aggregates.ts`](https://github.com/tashfeenahmed/freellmapi/blob/main/20260628_120000_request_aggregates.ts).

### Migration Runner Workflow

The `runMigrationsSync(db, 'up')` function executes the following steps:

1. **Initialize metadata** – Creates the `migrations` table if it does not exist.
2. **Load definitions** – Obtains migration records from `DEFAULT_MIGRATIONS` in [`defaults.ts`](https://github.com/tashfeenahmed/freellmapi/blob/main/defaults.ts) or from a custom directory specified in options.
3. **Detect pending** – Compares filesystem migrations against the `migrations` table to identify unapplied files.
4. **Execute transactions** – For each pending migration, loads the module, runs `migration.up(db)` inside a SQLite transaction, and inserts the filename into the `migrations` table with the current timestamp.

### Rollback and Status Inspection

Running with direction `'down'` executes the reverse process:

- Finds the most recently applied migration by querying the `migrations` table ordered by `applied_at` descending.
- Loads the corresponding module and executes `migration.down(db)` inside a transaction.
- Removes the entry from the `migrations` table upon successful completion.

For inspection, the `getMigrationStatuses(db, options)` function returns an array indicating which files are **applied** versus **pending**, enabling debugging and deployment verification.

## Schema Evolution Patterns

The initial baseline migration seeds the database with default models and then executes a series of model-specific migration functions (`migrateModels`, `migrateModelsV2`, through `migrateModelsV8`). These functions:

- Rename or remove obsolete model rows that have become paid-only.
- Insert new models discovered through provider catalog synchronization.
- Update intelligence rankings, speed rankings, and quota limits based on live probing of free-tier keys.
- Ensure every model has a corresponding entry in `fallback_config`.

Each step is wrapped in a transaction and written to be **idempotent**—using `INSERT OR IGNORE` and guarded `ALTER TABLE` statements—so re-running migrations never creates duplicates or corrupts existing data.

## Database Access Patterns

Services access the database through the `getDb()` helper function, which throws an exception if called before initialization. Core services such as [`request-retention.ts`](https://github.com/tashfeenahmed/freellmapi/blob/main/request-retention.ts), [`ratelimit.ts`](https://github.com/tashfeenahmed/freellmapi/blob/main/ratelimit.ts), and [`catalog-sync.ts`](https://github.com/tashfeenahmed/freellmapi/blob/main/catalog-sync.ts) query these tables directly via prepared statements.

Pricing information for analytics is maintained in [`src/db/model-pricing.ts`](https://github.com/tashfeenahmed/freellmapi/blob/main/src/db/model-pricing.ts) and applied to the `models` table through the `applyModelPricing(db)` function.

## Summary

- FreeLLMAPI uses a single SQLite file named `freeapi.db` for all persistent storage, requiring no external database server.
- The baseline schema in [`src/db/migrations/20260101_000000_legacy_baseline.ts`](https://github.com/tashfeenahmed/freellmapi/blob/main/src/db/migrations/20260101_000000_legacy_baseline.ts) defines fourteen tables covering models, API keys, request logs, rate limiting, and quota management.
- Runtime helper functions ensure backward compatibility by idempotently adding columns to existing tables.
- The migration runner in [`src/db/migrate/runner.ts`](https://github.com/tashfeenahmed/freellmapi/blob/main/src/db/migrate/runner.ts) automatically executes pending TypeScript migrations at startup, tracking state in the `migrations` table.
- All migrations are transactional and idempotent, supporting both fresh installations and incremental upgrades without data loss.

## Frequently Asked Questions

### What database engine does FreeLLMAPI use?

FreeLLMAPI uses **SQLite** as its sole database engine, storing all data in a local file named `freeapi.db`. This design eliminates external dependencies and simplifies deployment, while the migration system handles schema versioning automatically.

### How do I add a new column to the FreeLLMAPI database?

Add the column through a new migration file in `src/db/migrations/` following the [`YYYYMMDD_HHMMSS_description.ts`](https://github.com/tashfeenahmed/freellmapi/blob/main/YYYYMMDD_HHMMSS_description.ts) naming convention. Export an `up(db)` function that uses `ALTER TABLE … ADD COLUMN` guarded by `PRAGMA table_info` checks to ensure idempotency, matching the pattern used by existing helpers like `ensureRequestTtfbColumn`.

### Can I rollback a migration in FreeLLMAPI?

Yes, the migration runner in [`src/db/migrate/runner.ts`](https://github.com/tashfeenahmed/freellmapi/blob/main/src/db/migrate/runner.ts) supports rollback operations. Call `runMigrationsSync(db, 'down')` to execute the `down()` function of the most recently applied migration, which reverses the schema changes and removes the entry from the `migrations` table.

### Where does FreeLLMAPI track which migrations have been applied?

Applied migrations are recorded in the **`migrations`** table, which the runner creates automatically on first startup. This table contains `id`, `filename`, and `applied_at` columns, allowing the system to determine which TypeScript migration files in `src/db/migrations/` remain pending.