64 docs indexed

ClickHouse Usage Schema

The usage table schema, useful queries for usage accounting and analytics, and data retention configuration.

ClickHouse stores the append-only usage ledger. It is write-only from obleth's perspective — the gateway inserts rows and never updates or deletes them.

There are two tables: the raw per-request usage ledger (pruned to a rolling retention window), and the permanent usage_daily rollup that aggregates it and is kept forever.

usage table

CREATE TABLE obleth.usage (
    request_id       UUID,
    tenant_id        UUID,
    key_id           UUID,
    model            String,
    admission        LowCardinality(String), -- 'fast' | 'queued' | 'rejected'
    weight           Int64,        -- tenant weight at time of request
    input_tokens     UInt32,
    output_tokens    UInt32,
    estimated_tokens UInt32,       -- pre-request estimate used for budgeting
    queue_wait_ms    UInt32,
    ttft_ms          UInt32,       -- time to first token
    total_ms         UInt32,       -- total request duration
    status_code      UInt16,
    cache_status     LowCardinality(String) DEFAULT 'off', -- 'hit' | 'miss' | 'off'
    cost_usd         Float64 DEFAULT 0, -- USD cost frozen at request completion
    energy_wh        Float64 DEFAULT 0, -- attributed watt-hours (energy accounting)
    energy_cost_usd  Float64 DEFAULT 0, -- energy priced at the configured $/kWh
    co2_g            Float64 DEFAULT 0, -- grams CO₂ at the configured grid intensity
    ts_ms            Int64,        -- Unix timestamp milliseconds
    session_id       String DEFAULT '', -- client session/conversation id (optional)
    request_type     LowCardinality(String) DEFAULT '', -- coarse class from path
    ts               DateTime64(3) MATERIALIZED fromUnixTimestamp64Milli(ts_ms)
) ENGINE = MergeTree()
  PARTITION BY toYYYYMMDD(ts)
  ORDER BY (tenant_id, ts_ms);

cost_usd is the USD cost of the request, computed once at completion from the model's per-token (and per-modality) prices in effect at that moment, then stored. Spend reporting reads this value back directly — it is never recomputed from tokens × current price. This keeps history immutable: editing a model's price later changes only future requests, never the recorded cost of past ones. Rows written before this column existed (and rejected/errored requests) carry cost_usd = 0.

energy_wh / energy_cost_usd / co2_g are the request's attributed energy, electricity cost, and carbon, following the same frozen-at-completion rule as cost_usd — computed once from the power reading, per-model slot count, and rates in effect at completion, never recomputed or backfilled. They are 0 unless energy accounting is enabled and the serving model declares energy_slots_per_node > 0.

session_id is an optional client-supplied identifier for grouping related requests (multi-turn chats). obleth captures it from x-obleth-session-id, x-session-id, or common JSON body fields (session_id, metadata.session_id, user). Empty when the caller did not provide one.

request_type is the coarse request class derived from the API path suffix: chat, completion, responses, embedding, audio, image, rerank, moderation, or other. It powers the Type column in the dashboard Request Logs view. Internal gateway sub-calls carry their own type, all with admission boon — for example model-boon describe calls are recorded as vision_boon, structured-output repairs as structured_output_boon, gateway tool-loop round trips as tool_loop, and guardrails tier-2 harm classifications as guardrails_boon. (Compression makes no helper-model calls, so it produces no usage record of its own.)

Useful queries

Token usage by tenant (last 24h)

SELECT
    tenant_id,
    sum(input_tokens)  AS input_tokens,
    sum(output_tokens) AS output_tokens,
    count()            AS requests
FROM obleth.usage
WHERE ts_ms >= (now() - INTERVAL 24 HOUR) * 1000
GROUP BY tenant_id
ORDER BY output_tokens DESC;

Cache hit rate by model

SELECT
    model,
    countIf(cache_status = 'hit')  AS hits,
    countIf(cache_status = 'miss') AS misses,
    round(countIf(cache_status = 'hit') / count() * 100, 1) AS hit_pct
FROM obleth.usage
WHERE ts_ms >= (now() - INTERVAL 24 HOUR) * 1000
GROUP BY model
ORDER BY hits DESC;

Admission class distribution

SELECT
    admission,
    count() AS requests,
    round(count() / sum(count()) OVER () * 100, 1) AS pct
FROM obleth.usage
WHERE ts_ms >= (now() - INTERVAL 1 HOUR) * 1000
GROUP BY admission;

P50/P95/P99 latency

SELECT
    quantile(0.50)(ttft_ms) AS p50_ttft_ms,
    quantile(0.95)(ttft_ms) AS p95_ttft_ms,
    quantile(0.99)(ttft_ms) AS p99_ttft_ms,
    quantile(0.50)(total_ms) AS p50_total_ms,
    quantile(0.95)(total_ms) AS p95_total_ms
FROM obleth.usage
WHERE ts_ms >= (now() - INTERVAL 1 HOUR) * 1000;

Recent requests (newest first)

The dashboard Request Logs page and GET /api/v1/usage/logs read individual rows from this table. This is the only Management API usage endpoint that returns per-request detail rather than an aggregate:

SELECT
    request_id,
    ts_ms,
    tenant_id,
    key_id,
    model,
    request_type,
    session_id,
    status_code,
    input_tokens + output_tokens AS total_tokens,
    ttft_ms,
    total_ms,
    cost_usd
FROM obleth.usage
WHERE ts_ms >= (now() - INTERVAL 1 HOUR) * 1000
ORDER BY ts_ms DESC, toString(request_id) DESC
LIMIT 50;

Last used per key

The dashboard Keys table and GET /api/v1/keys/{id}/usage report when each key was last seen and what it last called. argMax reads the most recent row's value without a self-join:

SELECT
    key_id,
    max(ts_ms)                 AS last_used_ms,
    argMax(model, ts_ms)       AS last_model,
    argMax(status_code, ts_ms) AS last_status_code,
    count()                    AS requests,
    sum(cost_usd)              AS cost_usd
FROM obleth.usage
WHERE ts_ms >= (now() - INTERVAL 30 DAY) * 1000
GROUP BY key_id
ORDER BY requests DESC;

The single-key endpoint filters by key_id and drops the ts_ms floor on last_used_ms (it stays accurate across the full retention window), applying the window only to the count/sum columns via countIf / sumIf.

Cost by tenant

Each request stores its USD cost frozen at completion time, so spend is a plain sum — no join against the pricing table and no read-time multiply:

SELECT
    tenant_id,
    sum(cost_usd) AS total_cost_usd
FROM obleth.usage
WHERE ts_ms >= (now() - INTERVAL 30 DAY) * 1000
GROUP BY tenant_id;

The Management API /api/v1/costs endpoint returns this per model, and /api/v1/usage/daily exposes cost_usd at any group_by (including key_model for per-key, per-model spend). For long ranges, sum cost_usd_sum from the permanent usage_daily rollup below instead of the raw ledger.

usage_daily rollup

The raw usage ledger is pruned on a retention window to bound storage, so long-range reporting reads from a permanent daily rollup instead. usage_daily holds one row per day × tenant × key × model and is never pruned, so historical totals survive indefinitely.

CREATE TABLE obleth.usage_daily (
    day              Date,
    tenant_id        UUID,
    key_id           UUID,
    model            String,
    requests         UInt64,
    success_requests UInt64,   -- status_code 2xx/3xx
    error_requests   UInt64,   -- status_code >= 400
    input_tokens     UInt64,
    output_tokens    UInt64,
    estimated_tokens UInt64,
    cache_hits       UInt64,
    cache_misses     UInt64,
    ttft_ms_sum      UInt64,   -- summed over successful requests only
    total_ms_sum     UInt64,   -- summed over successful requests only
    cost_usd_sum     Float64,  -- summed USD cost (frozen at request time)
    energy_wh_sum    Float64,  -- summed attributed watt-hours
    energy_cost_usd_sum Float64, -- summed energy cost (USD)
    co2_g_sum        Float64   -- summed grams CO₂
) ENGINE = SummingMergeTree()
  PARTITION BY toYYYYMM(day)
  ORDER BY (day, tenant_id, key_id, model);

A materialized view (usage_daily_mv) keeps the rollup current as new requests land, and a one-time guarded backfill seeds it from any history that predates the view. SummingMergeTree collapses rows sharing the sort key on merge, so the summed columns stay correct.

Latency sums (ttft_ms_sum / total_ms_sum) accumulate over successful (2xx/3xx) requests only. The Management API divides them by success_requests to report avg_ttft_ms / avg_total_ms, so timeouts and upstream errors don't distort the daily averages. Query this rollup through GET /api/v1/usage/daily rather than reading it directly.

Data retention

obleth prunes the raw usage ledger automatically. A background worker runs hourly and drops whole day-partitions older than the retention window — an O(1) metadata operation rather than a row delete. The window defaults to OBLETH_USAGE_RETENTION_DAYS (180) and is tunable live from the control plane via PUT /api/v1/settings/usage-retention, with a floor of 1 day so a misconfigured value can never wipe the whole ledger. The usage_daily rollup is never touched by retention.

To prune on demand instead of waiting for the worker, call POST /api/v1/usage/compact. You do not need to add a manual ClickHouse TTL — retention is handled by the gateway.

WAL and replay

When ClickHouse is unavailable after startup, obleth spills rows to a WAL file (OBLETH_WAL_PATH). On reconnect, the WAL is replayed in order. Rows in the WAL have the same structure as usage.