Postgres schema
Every Postgres table that obleth and its dashboard use, with each column's type and default and the migration that added it.
18 min readUpdated for v2.4.0Edit on GitHub
Postgres holds configuration and the audit trail: tenants, keys, models, endpoints, MCP servers, knowledge collections, Slurm specs, settings, and the dashboard's login tables. Usage history lives in ClickHouse and hot state in Redis; see ClickHouse usage and Redis layout.
Migrations
The gateway compiles schema/postgres/0001 to 0035 into its binary and
applies all of them in order on every boot, under a Postgres advisory lock so
replicas don't race. Every statement is idempotent (if not exists, guarded
constraints), so there is no version table. A configuration backup covers
fairshare_groups, tenants, api_keys, models, model_endpoints,
mcp_servers, model_families, and app_settings.
| File | Adds |
|---|---|
0001_init.sql | fairshare_groups, tenants, api_keys, audit_log, models, model_health_checks, model_endpoints, managed_models, model_replicas, mcp_servers, app_settings |
0002_tracing_flag.sql | tracing_enabled on tenants and api_keys |
0003_guardrails_policy.sql | tenants.guardrails_policy |
0004_saved_recipes.sql | saved_recipes, dropped again by 0006 |
0005_managed_launcher_spec.sql | managed_models.launcher_spec |
0006_recipes.sql | recipes; drops saved_recipes |
0007_replica_port_and_min_replicas.sql | model_replicas.port_base, managed_models.min_replicas |
0008_managed_provision_error.sql | last_provision_error, last_provision_error_at on managed_models |
0009_replica_cancel_requested.sql | model_replicas.cancel_requested |
0010_drop_replica_model_cascade.sql | Drops the model_replicas → models foreign key |
0011_endpoint_selection_session_hash.sql | Allows session_hash in endpoint_selection_mode |
0012_model_debug_diagnostics.sql | models.debug_diagnostics |
0013_compression_policy.sql | tenants.compression_policy |
0014_model_energy_slots.sql | models.energy_slots_per_node |
0015_tenant_synthetic.sql | tenants.synthetic |
0016_api_keys_identity.sql | Identity-key columns on api_keys and their unique index |
0017_model_route_bias.sql | models.route_bias |
0018_knowledge_base.sql | knowledge_collections, knowledge_documents, knowledge_chunks, model_knowledge_collections |
0019_model_auto_eligible.sql | models.auto_eligible |
0020_knowledge_chunk_unique.sql | Unique (document_id, generation, ordinal) on knowledge_chunks |
0021_knowledge_reindex_requested.sql | knowledge_documents.reindex_requested |
0022_model_verifier_for.sql | models.verifier_for, unread since 0023 |
0023_model_speculation_fields.sql | draft_model, verify_api_base, verify_upstream_model on models |
0024_model_aliases_and_quantization.sql | aliases (GIN-indexed) and quantization on models |
0025_api_key_fairshare.sql | weight and max_in_flight on api_keys |
0026_model_upstream_headers.sql | models.upstream_headers |
0027_model_cost_per_video.sql | models.cost_per_video |
0028_video_jobs.sql | video_jobs and two indexes |
0029_model_capacity_discovery.sql | Capacity discovery columns on models, model_endpoints.max_in_flight, discovered capacity mode |
0030_video_jobs_key_index.sql | Index on video_jobs (tenant_id, key_id, created_at desc) |
0031_deployment_launches.sql | deployment_launches, the launch history of Slurm-provisioned replicas, and two indexes |
0032_api_key_end_user_fairshare.sql | api_keys.end_user_fairshare |
0033_model_variants.sql | models.variants and its GIN index |
0034_model_lifecycle.sql | models.lifecycle |
0035_reasoning_effort.sql | reasoning and listed on models; model_families |
0036_model_rejected_request_fields.sql | models.rejected_request_fields |
0037_job_kind.sql | video_jobs.kind |
0038_model_session_affinity.sql | models.session_affinity |
Tenants and keys
fairshare_groups
Seeded with a default group of weight 100.
| Column | Type | Default and notes |
|---|---|---|
name | text primary key | |
weight | bigint not null | 100; at least 1 |
created_at, updated_at | timestamptz not null | now() |
tenants
Indexed by fairshare_group, and by status where it is not active.
| Column | Type | Default and notes |
|---|---|---|
id | uuid primary key | |
name | text not null unique | |
weight | bigint not null | 100; fairshare weight, at least 1 |
tokens_per_minute | bigint not null | 0 = no per-minute cap |
max_in_flight | bigint | Cap within each model's pool; null = none |
fairshare_group | text not null | 'default'; references fairshare_groups.name |
description, organization, contact_email | text not null | '' |
status | text not null | 'active'; or suspended, archived. Only active admits traffic |
tracing_enabled | boolean not null | false; record spans for every key (0002) |
timezone | text not null | 'UTC'; IANA zone for the access windows |
active_from, active_until | timestamptz | Activation window; null = unbounded |
weekly_windows | jsonb | [{day, start_min, end_min}], day 0 = Sunday; null or empty = any time |
budget_tokens | bigint | Term token cap |
budget_cost_usd | double precision | Term USD cap |
budget_period | text | lifetime, monthly, or term |
budget_started_at | timestamptz | Start of the current term |
allowed_models | jsonb | Model allowlist; null or empty = all |
guardrails_policy | jsonb | Guardrails policy (0003) |
compression_policy | jsonb | Compression policy; null = global defaults (0013) |
synthetic | boolean not null | false; traffic recorded as request_type = 'benchmark' (0015) |
created_at, updated_at | timestamptz not null | now() |
api_keys
Only the SHA-256 of a secret is stored, peppered when OBLETH_API_KEY_PEPPER is
set. An identity key has no secret: its
key_hash is jwt: plus the SHA-256 of issuer, a NUL byte, and subject.
Indexed by tenant_id, with one identity key per (identity_issuer, identity_subject). See Tenants and keys.
| Column | Type | Default and notes |
|---|---|---|
id | uuid primary key | |
tenant_id | uuid not null | References tenants.id, on delete cascade |
name | text not null | |
description | text not null | '' |
key_prefix | text not null | First 18 characters of the secret; jwt:<subject> for identity keys |
key_hash | text not null unique | Lookup handle, also the Redis key |
kind | text not null | 'secret' or identity (0016) |
identity_issuer, identity_subject | text | Identity keys only (0016) |
identity_claims | jsonb | Claims captured at first use (0016) |
budget_tokens, budget_cost_usd, budget_period, budget_started_at | as on tenants | Key-level term budget |
weight | bigint not null | 100; share among the tenant's keys, at least 1 (0025) |
max_in_flight | bigint | Key cap within each model's pool; null = none (0025) |
disabled | boolean not null | false |
tracing_enabled | boolean not null | false (0002) |
end_user_fairshare | boolean not null | false; each end user the key names queues on their own (0032) |
created_at, updated_at | timestamptz not null | now() |
Models
models
Indexed by enabled (partial), by health_next_check_at for enabled models
with checks on, and by aliases and variants (GIN). Field meanings are in
Model registry.
| Column | Type | Default and notes |
|---|---|---|
id | uuid primary key | |
model_name | text not null unique | Canonical client-facing name |
aliases | jsonb not null | '[]'; other names that resolve to this model (0024) |
variants | jsonb not null | '[]'; [{name, description, boons}], names that resolve to this model with extra boons (0033). See Variants |
description | text not null | '' |
model_type | text not null | 'chat'; or embedding, audio_transcription, audio_speech, image, video, search |
quantization | text not null | 'unknown'; or none, fp16, bf16, fp8, nvfp4, mxfp4, int8, int4, awq, gptq, gguf. Descriptive only (0024) |
upstream_model | text not null | Name sent upstream |
api_base | text not null | Upstream base URL |
api_key | text | Encrypted when OBLETH_ENCRYPTION_KEY is set |
upstream_headers | jsonb not null | '{}'; lowercase name to value, values encrypted; the API returns names only (0026) |
input_cost_per_token, output_cost_per_token | double precision not null | 0 |
cost_per_image, cost_per_audio_second, cost_per_character | double precision not null | 0 |
cost_per_video | double precision not null | 0; charged once per created job (0027) |
context_window | bigint not null | 8192; at least 0 |
admission_weight | bigint not null | 100; multiplier on tenant weight in this pool, at least 1. See Effective weight |
max_in_flight | bigint | Pool size, at least 1; null = OBLETH_DEFAULT_MODEL_MAX_IN_FLIGHT. The fallback for discovered |
capacity_mode | text not null | 'static'; or tuned, discovered (0029) |
capacity_tuned_at | timestamptz | Last auto-tune apply |
capacity_source | text not null | 'endpoints' or kubernetes (0029). See Pool sizes |
capacity_namespace | text | kubernetes: the Service's namespace; null searches OBLETH_CAPACITY_DISCOVERY_NAMESPACES (0029) |
capacity_service | text | kubernetes: the Service; null uses OBLETH_CAPACITY_DEFAULT_SERVICE (0029) |
per_replica_max_in_flight | bigint | Requests one backend replica serves; at least 1 (0029) |
capacity_headroom | double precision not null | 1; multiplier on the discovered size, above 0 and at most 10 (0029) |
supports_function_calling | boolean not null | false |
supports_system_messages | boolean not null | true |
supports_response_schema | boolean not null | false |
supports_tool_choice | boolean not null | false |
supports_vision | boolean not null | false |
tags | jsonb not null | '[]'; routing tags for auto |
boons | jsonb not null | '[]'; vision, structured_output, compression, knowledge, image_generation, speculation, web_search |
tool_servers | jsonb not null | '[]'; MCP servers for the gateway tool loop |
enabled | boolean not null | true |
lifecycle | jsonb not null | '{}' (active); {status, replacement, retire_at, changed_at, note, redirect} (0034). See Model lifecycle |
reasoning | jsonb not null | '{}' (pass-through); {mode, default_level, levels}, how the model takes reasoning effort (0035). See Reasoning effort |
listed | boolean not null | true; false keeps the model out of /v1/models and /model/info while it is still served by name (0035) |
rejected_request_fields | jsonb not null | '[]'; request body fields refused with 400 before dispatch (0036) |
session_affinity | jsonb not null | '{}' (follows the gateway); {enabled, hold_ms, max_held_percent, max_bypass_secs}, each optional (0038). See Session affinity |
cache_enabled | boolean not null | false |
cache_ttl_secs | bigint not null | 300; 0 disables caching |
request_timeout_secs | bigint | At least 1; null = OBLETH_UPSTREAM_TIMEOUT_SECS |
max_retries | bigint not null | 0 |
retry_backoff_ms | bigint not null | 200 |
endpoint_selection_mode | text not null | 'failover'; or load_balance, session_hash |
debug_diagnostics | boolean not null | false; DNS and TCP probe span on a final 502 or 504 (0012) |
energy_slots_per_node | bigint not null | 0 = off; see Energy accounting (0014) |
route_bias | double precision not null | 1.0; multiplier on the auto score (0017) |
auto_eligible | boolean not null | true; false keeps the model out of auto (0019) |
verifier_for | text not null | ''; not read (0022) |
draft_model | text not null | ''; speculation drafter; '' = boon default (0023) |
verify_api_base | text not null | ''; direct URL that scores drafts; '' = no speculation (0023) |
verify_upstream_model | text not null | ''; name the scorer serves; '' = same (0023) |
health_checks_enabled, health_alerts_enabled | boolean not null | true |
health_check_interval_secs | bigint not null | 900; at least 60 |
health_failure_threshold | bigint not null | 2; failures before alerting |
health_maintenance_until, health_maintenance_note | timestamptz, text | Alert suppression window |
health_status | text not null | 'unknown' |
health_consecutive_failures | bigint not null | 0 |
health_alert_state | text not null | 'ok' |
health_next_check_at | timestamptz not null | now() |
health_last_checked_at | timestamptz | |
health_last_latency_ms, health_last_http_status | bigint | |
health_last_message | text | |
created_at, updated_at | timestamptz not null | now() |
model_families
One name for several chat models; the reasoning effort level picks which one
answers (0035). Members are named, not linked: the API checks renames and
deletes, and a family whose member is gone answers 404 for that level. See
Reasoning effort.
| Column | Type | Notes |
|---|---|---|
id | uuid | Primary key |
name | text not null | Unique; shares one namespace with model names, aliases and variants |
description | text not null | '' |
default_level | text not null | 'none'; the level a request that names none gets |
members | jsonb not null | '{}'; {none, low, medium, high}, each a models.model_name |
created_at, updated_at | timestamptz not null | now() |
model_endpoints
One row per upstream a model routes to; a model with none uses its own
api_base and api_key. Unique (model_id, name). See
Reliability.
| Column | Type | Default and notes |
|---|---|---|
id | uuid primary key | |
model_id | uuid not null | References models.id, on delete cascade |
name | text not null | Unique per model |
api_base | text not null | |
api_key | text | Encrypted; null inherits the model's key |
priority | bigint not null | 100; lower first in failover |
weight | bigint not null | 100; share in load_balance, at least 1 |
enabled | boolean not null | true |
max_in_flight | bigint | Per-endpoint concurrency for the endpoints source; null = model's per_replica_max_in_flight (0029) |
health_status | text not null | 'unknown' |
consecutive_failures | bigint not null | 0 |
alert_state | text not null | 'ok' |
last_checked_at, last_latency_ms, last_http_status, last_message | Latest probe | |
created_at, updated_at | timestamptz not null | now() |
model_health_checks
Probe history, pruned after OBLETH_MODEL_HEALTH_RETENTION_DAYS (30). Indexed
by (model_id, checked_at desc) and checked_at desc.
| Column | Type | Default and notes |
|---|---|---|
id | bigserial primary key | |
model_id | uuid not null | References models.id, on delete cascade |
checked_at | timestamptz not null | now() |
trigger | text not null | scheduled, manual, or bulk |
status | text not null | healthy, degraded, unhealthy, or skipped |
latency_ms, http_status | bigint | |
message, response_excerpt | text | Sanitized |
video_jobs
One row per job created through the gateway, video or 3D mesh (the table keeps
its first name), so follow-ups reach the
endpoint that holds the job and only its owner sees it
(OBLETH_VIDEO_JOB_SCOPE, key or tenant). Pruned after
OBLETH_VIDEO_JOB_RETENTION_HOURS. Indexed by (tenant_id, created_at desc),
(tenant_id, key_id, created_at desc), and created_at. See
Who can see a job.
| Column | Type | Default and notes |
|---|---|---|
job_id | text primary key | The upstream's job id |
kind | text not null | video (default) or mesh; every lookup matches it |
model_name | text not null | Model the job was created through |
tenant_id | uuid not null | Owner; every lookup is scoped by it |
key_id | uuid not null | Creating key; also scopes lookups under key |
upstream_base | text not null | Endpoint that accepted the create |
created_at | timestamptz not null | now() |
Slurm deployments
managed_models
The Slurm launch spec for a model the provisioner keeps running. See Slurm provisioning.
| Column | Type | Default and notes |
|---|---|---|
model_id | uuid primary key | References models.id, on delete cascade |
enabled | boolean not null | true; false drains to zero |
partition | text not null | |
gres | text not null | '' |
nodes | bigint not null | 1; at least 1 |
constraints, exclude, account, qos, time_limit | text | Slurm options |
cpus_per_task | bigint | Null = cluster default |
mem | text | Null = cluster default |
image | text not null | Apptainer image |
preamble | text not null | '' |
log_output_dir | text not null | '' = Slurm default |
launch_command | text not null | |
script_body | text not null | ''; when set, submitted as is instead of image, preamble, and command |
serving_port | bigint not null | Base of the port window, 1–65535 |
health_path | text not null | '/health' |
target_replicas | bigint not null | 2; at least 1 |
min_replicas | bigint not null | 1; healthy floor (0007) |
max_job_failures | bigint not null | 0 = unlimited |
launcher_spec | jsonb | Recipe provenance; the provisioner ignores it (0005) |
last_provision_error, last_provision_error_at | text, timestamptz | Last submit failure (0008) |
created_at, updated_at | timestamptz not null | now() |
model_replicas
One row per Slurm-backed replica, owned by the provisioner. Unique
(model_id, slurm_job_id); indexed by model_id and state.
| Column | Type | Default and notes |
|---|---|---|
id | uuid primary key | |
model_id | uuid not null | No foreign key, so jobs can be cancelled after the model is deleted (0010) |
slurm_job_id | text not null | |
nodes | text | Allocated hostnames |
endpoint_id | uuid | References model_endpoints.id, on delete set null |
state | text not null | 'pending'; or starting, healthy, draining, lost |
port_base | bigint | Base of this replica's port window (0007) |
cancel_requested | boolean not null | false; operator restart (0009) |
last_message | text | |
created_at, updated_at | timestamptz not null | now() |
recipes
Launch templates edited from the dashboard. Indexed by updated_at desc.
| Column | Type | Default and notes |
|---|---|---|
id | uuid primary key | |
name | text not null | |
body | text not null | ''; YAML header plus sbatch script |
author | text not null | '' |
created_at, updated_at | timestamptz not null | now() |
deployment_launches
One row per Slurm replica launch, written as the replica moves through its
states and kept after the replica row is gone (0031). It has no foreign keys,
so it outlives the replica, the launch spec and the model. Indexed by
(model_id, submitted_at desc) and (recipe_id, submitted_at desc). Read it
with GET /api/v1/deployments/launches.
| Column | Type | Default and notes |
|---|---|---|
id | uuid primary key | The replica's id |
model_id, model_name | uuid, text not null | |
recipe_id | text | The recipe it was launched from |
slurm_job_id | text not null | |
partition, account, qos, time_limit, gres, mem, exclude, constraints | text | Snapshot of the spec at submit time |
nodes_requested, cpus_per_task | bigint | Snapshot of the spec at submit time |
launcher_spec | jsonb | Snapshot of the spec's launcher metadata |
nodes | text | Nodes Slurm allocated |
submitted_at | timestamptz not null | now() |
started_at, healthy_at, ended_at | timestamptz | |
end_state | text | Slurm's end state (TIMEOUT, OUT_OF_MEMORY, NODE_FAIL, …), gone when the job vanished, or cancelled:<why> when obleth cancelled it |
end_reason | text | |
updated_at | timestamptz not null | now() |
MCP and knowledge
mcp_servers
| Column | Type | Default and notes |
|---|---|---|
id | uuid primary key | |
name | text not null unique | Reached at /mcp/<name> |
upstream_url | text not null | |
auth_header | text | Encrypted |
enabled | boolean not null | true; partial index |
created_at, updated_at | timestamptz not null | now() |
knowledge_collections
| Column | Type | Default and notes |
|---|---|---|
id | uuid primary key | |
name | text not null unique | |
description | text not null | '' |
embedding_model | text not null | Embedder the operator chose |
indexed_embedding_model | text not null | ''; embedder the index was built with |
embedding_dim | integer not null | 0 |
chunk_tokens | integer not null | 400; above 0 |
chunk_overlap_tokens | integer not null | 50; at least 0, below chunk_tokens |
version | bigint not null | 0; bumped on any content change |
created_at, updated_at | timestamptz not null | now() |
knowledge_documents
Unique (collection_id, content_hash); indexed by status.
| Column | Type | Default and notes |
|---|---|---|
id | uuid primary key | |
collection_id | uuid not null | References knowledge_collections.id, on delete cascade |
title | text not null | |
filename | text not null | '' |
content_type | text not null | 'text/plain' |
content | text not null | Document text |
content_hash | text not null | Identical re-uploads reuse the row |
byte_size | bigint not null | 0 |
status | text not null | 'pending'; or indexing, ready, failed |
error | text | Failure reason |
chunk_count | integer not null | 0 |
active_generation | integer not null | 0; chunk generation served |
indexed_embedding_model | text not null | '' |
indexing_started_at | timestamptz | Reclaimed after index_stale_after_secs |
reindex_requested | boolean not null | false; reindex asked for mid-indexing (0021) |
created_at | timestamptz not null | now() |
indexed_at | timestamptz |
knowledge_chunks
Unique (document_id, generation, ordinal); indexed by
(collection_id, generation).
| Column | Type | Default and notes |
|---|---|---|
id | uuid primary key | |
document_id | uuid not null | References knowledge_documents.id, on delete cascade |
collection_id | uuid not null | References knowledge_collections.id, on delete cascade |
generation | integer not null | 0 |
ordinal | integer not null | Position in the document |
text | text not null | |
token_count | integer not null | 0 |
embedding | bytea not null | Little-endian f32, embedding_dim × 4 bytes, stored uncompressed |
embedding_dim | integer not null |
model_knowledge_collections
Which collections each model retrieves from. Primary key
(model_id, collection_id); both columns cascade on delete. See
Knowledge base.
| Column | Type | Default and notes |
|---|---|---|
model_id | uuid not null | References models.id |
collection_id | uuid not null | References knowledge_collections.id |
Audit and settings
audit_log
One row per Management API change. Indexed by ts desc, (actor, ts desc),
and (entity_type, entity_id, ts desc). See Audit log.
| Column | Type | Default and notes |
|---|---|---|
id | bigserial primary key | |
ts | timestamptz not null | now() |
actor | text not null | X-Obleth-Audit-Actor, else admin |
action | text not null | For example create_tenant |
entity_type | text not null | For example api_key |
entity_id | text not null | |
detail | jsonb not null | '{}' |
app_settings
One JSON value per key: key text primary key, value jsonb not null,
updated_at timestamptz not null default now().
| Key | Holds | Management API |
|---|---|---|
alerts | Slack and email alert channels | /api/v1/settings/alerts |
auto_router | auto routing and classifier | /api/v1/settings/auto-router |
boons | Boon settings, including knowledge retrieval | /api/v1/settings/boons, /api/v1/settings/knowledge |
energy | Energy accounting | /api/v1/settings/energy |
slurm | Slurm connection, JWT encrypted | /api/v1/settings/slurm |
usage_retention | Request history window | /api/v1/settings/usage-retention |
charo_settings | Playground assistant model, tools, benchmark limits | /api/v1/settings/charo |
charo_enabled | The assistant's on switch | /api/v1/settings/charo |
control_plane_key | The control plane's reserved key, encrypted | /api/v1/system/control-plane-key |
Dashboard auth tables
The control plane creates these from control-plane/db/auth-schema.sql when it
starts and reads them through DATABASE_URL. user is a reserved word, so
quote it: select email, role, status from "user";.
| Table | Holds |
|---|---|
"user" | Accounts: id, name, email (unique), emailVerified, image, role (admin or user, default user), status (pending or active, default pending), tenantId (references tenants.id, on delete set null), banned, banReason, banExpires, createdAt, updatedAt |
"session" | Sessions: token (unique), expiresAt, ipAddress, userAgent, userId (cascade), impersonatedBy |
"account" | Credentials per provider: providerId, accountId, userId (cascade), OIDC tokens and expiries, scope, and the hashed password of the break-glass admin |
"verification" | Short-lived values: identifier, value, expiresAt |
Inspecting the schema
\dt -- list tables
\d+ models -- describe one