FreeLLMAPI Database Schema and Migrations: Complete Technical Reference

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 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 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:

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, such as 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 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, ratelimit.ts, and catalog-sync.ts query these tables directly via prepared statements.

Pricing information for analytics is maintained in 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 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 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 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 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.

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