Postgres Schema

All Postgres tables used by obleth, their columns, and the migration file that introduced each.

The base schema lives in schema/postgres/0001_init.sql. Incremental migrations are applied in order:

File	What it adds
`schema/postgres/0001_init.sql`	Full initial schema (all tables and indexes)
`schema/postgres/0002_tracing_flag.sql`	`tracing_enabled` column on `tenants` and `api_keys`

Every statement is idempotent (create … if not exists / add column if not exists) and is applied automatically when the gateway boots, so all files are safe to re-run against an existing database.

Tables

`tenants`

Stores all tenants.

Column	Type	Notes
`id`	`UUID` PRIMARY KEY
`name`	`TEXT` NOT NULL UNIQUE	Human-readable name
`weight`	`BIGINT` NOT NULL DEFAULT `100`	Fairshare weight (`>= 1`)
`tokens_per_minute`	`BIGINT` NOT NULL DEFAULT `60000`	TPM token-bucket rate (`>= 0`)
`max_in_flight`	`BIGINT`	Optional per-tenant concurrency cap; `null` = only the global limit applies
`fairshare_group`	`TEXT` NOT NULL DEFAULT `'default'`	FK → `fairshare_groups.name`
`description`	`TEXT` NOT NULL DEFAULT `''`	Operator note
`organization`	`TEXT` NOT NULL DEFAULT `''`	Optional org metadata
`contact_email`	`TEXT` NOT NULL DEFAULT `''`	Optional contact metadata
`status`	`TEXT` NOT NULL DEFAULT `'active'`	`active`, `suspended`, or `archived` — only `active` admits traffic
`tracing_enabled`	`BOOLEAN` NOT NULL DEFAULT `false`	When `true`, every request from any key in this tenant is span-traced to the ClickHouse `spans` table (added by `0002_tracing_flag.sql`)
`timezone`	`TEXT` NOT NULL DEFAULT `'UTC'`	IANA timezone for access windows
`active_from`	`TIMESTAMPTZ`	Optional activation start; `null` = no bound
`active_until`	`TIMESTAMPTZ`	Optional expiry cutoff; `null` = no bound
`weekly_windows`	`JSONB`	Recurring windows `[{day:0-6, start_min, end_min}]`; `null`/empty = any time
`budget_tokens`	`BIGINT`	Optional cumulative token cap for the term
`budget_cost_usd`	`DOUBLE PRECISION`	Optional cumulative USD cap for the term
`budget_period`	`TEXT`	`lifetime`, `monthly`, or `term`
`budget_started_at`	`TIMESTAMPTZ`	When the current term began
`allowed_models`	`JSONB`	Optional model-name allowlist; `null`/empty = all models permitted
`created_at`	`TIMESTAMPTZ`	Auto-set
`updated_at`	`TIMESTAMPTZ`	Auto-updated

Indexed by fairshare_group and by status (partial, where status <> 'active').

`api_keys`

Stores API key hashes. The raw key is never stored.

Column	Type	Notes
`id`	`UUID` PRIMARY KEY
`tenant_id`	`UUID` NOT NULL	FK → `tenants.id` (`ON DELETE CASCADE`)
`name`	`TEXT` NOT NULL	Display name
`key_prefix`	`TEXT` NOT NULL	First 18 chars (safe to display)
`key_hash`	`TEXT` NOT NULL UNIQUE	SHA-256 of the full secret
`disabled`	`BOOLEAN` NOT NULL DEFAULT `false`
`tracing_enabled`	`BOOLEAN` NOT NULL DEFAULT `false`	When `true`, requests using this key are span-traced to the ClickHouse `spans` table; overrides the tenant default when set per-key (added by `0002_tracing_flag.sql`)
`created_at`	`TIMESTAMPTZ`

Indexed by tenant_id (api_keys_tenant_id_idx).

`models`

Model registry: client-facing names mapped to upstream OpenAI-compatible endpoints.

Column	Type	Notes
`id`	`UUID` PRIMARY KEY
`model_name`	`TEXT` NOT NULL UNIQUE	Name used in client requests
`description`	`TEXT` NOT NULL DEFAULT `''`	Operator-facing model description
`model_type`	`TEXT` NOT NULL DEFAULT `'chat'`	Modality: `chat`, `embedding`, `audio_transcription`, `audio_speech`, or `image`
`upstream_model`	`TEXT`	Model name sent to upstream
`api_base`	`TEXT`	Per-model upstream override (provider base ending in `/v1`)
`api_key`	`TEXT`	Injected upstream credential
`input_cost_per_token`	`FLOAT8`	For billing (chat, embedding)
`output_cost_per_token`	`FLOAT8`	For billing (chat)
`cost_per_image`	`FLOAT8` NOT NULL DEFAULT `0`	Per-image billing (`image`, `× n`)
`cost_per_audio_second`	`FLOAT8` NOT NULL DEFAULT `0`	Per-audio-second billing (`audio_transcription`; reserved)
`cost_per_character`	`FLOAT8` NOT NULL DEFAULT `0`	Per-character billing (`audio_speech`)
`context_window`	`BIGINT`
`admission_weight`	`FLOAT8` DEFAULT `1.0`	Scales fairshare cost
`max_in_flight`	`BIGINT`	Optional per-model in-flight cap; `null` = no cap
`capacity_mode`	`TEXT` NOT NULL DEFAULT `'static'`	`static` or `tuned` — how `max_in_flight` was chosen
`capacity_tuned_at`	`TIMESTAMPTZ`	When auto-tune last applied a slot count; `null` until tuned
`supports_function_calling`	`BOOLEAN` NOT NULL DEFAULT `false`
`supports_system_messages`	`BOOLEAN` NOT NULL DEFAULT `true`
`supports_response_schema`	`BOOLEAN` NOT NULL DEFAULT `false`	Required for JSON-schema requests in `auto` routing
`supports_tool_choice`	`BOOLEAN` NOT NULL DEFAULT `false`	Required for `tool_choice` requests in `auto` routing
`tags`	`JSONB` NOT NULL DEFAULT `'[]'`	Auto-router tags (fixed vocabulary)
`boons`	`JSONB` NOT NULL DEFAULT `'[]'`	Per-model gateway boons opted into (fixed vocabulary: `vision`, `structured_output`, `compression`)
`tool_servers`	`JSONB` NOT NULL DEFAULT `'[]'`	Registered MCP servers whose tools this model may use (gateway tool loop; operator-defined names)
`enabled`	`BOOLEAN` DEFAULT `true`
`cache_enabled`	`BOOLEAN` DEFAULT `false`	Response cache toggle
`cache_ttl_secs`	`BIGINT`	Cache TTL
`request_timeout_secs`	`BIGINT`	Per-request upstream timeout; `null` = use `OBLETH_UPSTREAM_TIMEOUT_SECS`
`max_retries`	`BIGINT` NOT NULL DEFAULT `0`	Extra attempts per endpoint on retryable failures
`retry_backoff_ms`	`BIGINT` NOT NULL DEFAULT `200`	Base retry backoff (exponential, capped)
`endpoint_selection_mode`	`TEXT` NOT NULL DEFAULT `'failover'`	`failover`, `load_balance`, or `session_hash` across endpoints
`energy_slots_per_node`	`BIGINT` NOT NULL DEFAULT `0`	Concurrent requests that saturate one node (energy accounting); `0` = opted out
`health_checks_enabled`	`BOOLEAN` DEFAULT `true`	Scheduled model health checks
`health_alerts_enabled`	`BOOLEAN` DEFAULT `true`	Slack alerts for this model
`health_check_interval_secs`	`BIGINT` DEFAULT `900`	Per-model check interval
`health_failure_threshold`	`BIGINT` DEFAULT `2`	Consecutive failures before alerting
`health_maintenance_until`	`TIMESTAMPTZ`	Suppress alerts while active
`health_maintenance_note`	`TEXT`	Operator note for maintenance
`health_status`	`TEXT` DEFAULT `unknown`	Latest status
`health_consecutive_failures`	`BIGINT` DEFAULT `0`	Current failure streak
`health_alert_state`	`TEXT` DEFAULT `ok`	Alert state (`ok` or `firing`)
`health_next_check_at`	`TIMESTAMPTZ`	Next scheduled check time
`health_last_checked_at`	`TIMESTAMPTZ`	Latest check timestamp
`health_last_latency_ms`	`BIGINT`	Latest check latency
`health_last_http_status`	`BIGINT`	Latest upstream/proxy status
`health_last_message`	`TEXT`	Sanitized latest check summary
`created_at`	`TIMESTAMPTZ`
`updated_at`	`TIMESTAMPTZ`

`model_health_checks`

Append-only history of model health probes.

Column	Type	Notes
`id`	`BIGSERIAL` PRIMARY KEY
`model_id`	`UUID` NOT NULL	FK → `models.id` (`ON DELETE CASCADE`)
`checked_at`	`TIMESTAMPTZ`	Probe timestamp
`trigger`	`TEXT`	`scheduled`, `manual`, or `bulk`
`status`	`TEXT`	`healthy`, `degraded`, `unhealthy`, or `skipped`
`latency_ms`	`BIGINT`	End-to-end proxy latency
`http_status`	`BIGINT`	HTTP status from the proxy/upstream
`message`	`TEXT`	Sanitized summary
`response_excerpt`	`TEXT`	Sanitized body excerpt for failures

`model_endpoints`

One row per upstream cluster that a model can be routed to. A model with no rows here uses its own api_base/api_key (legacy single-upstream path); with rows, the data plane routes across the enabled, healthy ones using the model's endpoint_selection_mode. See Reliability & Failover.

Column	Type	Notes
`id`	`UUID` PRIMARY KEY
`model_id`	`UUID` NOT NULL	FK → `models.id` (`ON DELETE CASCADE`)
`name`	`TEXT` NOT NULL	Unique per model (`UNIQUE (model_id, name)`)
`api_base`	`TEXT` NOT NULL	Upstream base ending in `/v1`
`api_key`	`TEXT`	Injected upstream credential (encrypted at rest); inherits the model key when null
`priority`	`BIGINT` NOT NULL DEFAULT `100`	Lower is tried first in `failover` mode
`weight`	`BIGINT` NOT NULL DEFAULT `100`	Traffic share in `load_balance` mode
`enabled`	`BOOLEAN` NOT NULL DEFAULT `true`	Disabled endpoints are removed from rotation
`health_status`	`TEXT` NOT NULL DEFAULT `'unknown'`	Latest per-endpoint probe status
`consecutive_failures`	`BIGINT` NOT NULL DEFAULT `0`	Current failure streak
`alert_state`	`TEXT` NOT NULL DEFAULT `'ok'`	Alert state
`last_checked_at`	`TIMESTAMPTZ`	Latest probe timestamp
`last_latency_ms`	`BIGINT`	Latest probe latency
`last_http_status`	`BIGINT`	Latest probe HTTP status
`last_message`	`TEXT`	Sanitized latest probe summary
`created_at`	`TIMESTAMPTZ`
`updated_at`	`TIMESTAMPTZ`

Indexed by model_id (model_endpoints_model_idx).

`fairshare_groups`

Scheduler groups for hierarchical mode. A default group (weight 100) is seeded on first boot.

Column	Type	Notes
`name`	`TEXT` PRIMARY KEY	Group name
`weight`	`BIGINT` NOT NULL DEFAULT `100`	Group-level weight (`>= 1`)
`created_at`	`TIMESTAMPTZ`
`updated_at`	`TIMESTAMPTZ`

`mcp_servers`

MCP server registry. obleth reverse-proxies these through its auth + audit layer.

Column	Type	Notes
`id`	`UUID` PRIMARY KEY
`name`	`TEXT` NOT NULL UNIQUE	Reached at `/mcp/{name}`
`upstream_url`	`TEXT` NOT NULL	SSRF-validated upstream base
`auth_header`	`TEXT`	Injected auth header value (encrypted at rest)
`enabled`	`BOOLEAN` DEFAULT `true`
`created_at`	`TIMESTAMPTZ`
`updated_at`	`TIMESTAMPTZ`

`audit_log`

Append-only record of all Management API mutations.

Column	Type	Notes
`id`	`BIGSERIAL` PRIMARY KEY
`ts`	`TIMESTAMPTZ` NOT NULL DEFAULT `now()`	When the action occurred
`actor`	`TEXT` NOT NULL	Who performed it (e.g. `admin`)
`action`	`TEXT` NOT NULL	e.g. `create_tenant`, `delete_key`
`entity_type`	`TEXT` NOT NULL	e.g. `tenant`, `api_key`
`entity_id`	`TEXT` NOT NULL	ID of the affected entity
`detail`	`JSONB` NOT NULL DEFAULT `'{}'`	Structured detail payload

Indexed by ts desc (audit_log_ts_idx), by actor, ts desc (audit_log_actor_idx), and by entity_type, entity_id, ts desc (audit_log_entity_idx) so filtering the trail by who acted or which entity was touched stays fast as the log grows.

`app_settings`

Key-value store for runtime-reloadable gateway settings. Holds the alerting configuration (Slack webhook + SMTP email) under key alerts, the auto_router classifier settings, the usage-retention window, the model-boon settings (boons), the Slurm provisioner connection (slurm), and the energy accounting configuration (energy). New fields are stored as JSON so they need no schema change.

Column	Type	Notes
`key`	`TEXT` PRIMARY KEY	Setting name (e.g. `alerts`, `auto_router`)
`value`	`JSONB` NOT NULL	Setting payload
`updated_at`	`TIMESTAMPTZ` NOT NULL DEFAULT `now()`	Auto-updated

Applying the schema

The gateway applies all schema files under schema/postgres/ in lexicographic order on boot — there is no per-version migration table to query. All statements are idempotent, so re-running against an existing database is safe. To inspect the live schema, read it from Postgres directly:

-- List obleth's tables
\dt

-- Describe a table
\d+ tenants

PreviousClickHouse Usage

NextRedis Layout

Getting Started

Concepts

Guides

Reference

Operations