ClickHouse Usage Schema

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)
    session_id_source LowCardinality(String) DEFAULT '', -- how session_id was obtained
    request_type     LowCardinality(String) DEFAULT '', -- coarse class from path
    ts               DateTime64(3) MATERIALIZED fromUnixTimestamp64Milli(ts_ms),
    INDEX idx_ts_ms ts_ms TYPE minmax GRANULARITY 4
) ENGINE = MergeTree()
  PARTITION BY toYYYYMMDD(ts)
  ORDER BY (tenant_id, ts_ms);

The sort key leads with tenant_id, so cross-tenant time-range reads (the live request log, the usage series) cannot use the primary index. The idx_ts_ms minmax skip index lets those queries skip granules outside the requested window. It is added idempotently to databases created before it existed and applies to newly written parts; older parts age out through the retention worker.

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 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). When the caller supplies none, obleth derives a stable conversation id from the request itself unless OBLETH_SESSION_ID_DERIVATION is off. session_id_source records which of those paths produced the value. See Conversations & Sessions.

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.)

Two request_type values replace the path-derived class instead of describing the path:

Both are treated as internal traffic and excluded from usage, token, and cost reads by default. Pass include_internal=true on a Management API usage endpoint to include them. Benchmark rows additionally never enter the permanent usage_daily rollup, since that table has no request_type dimension and an immutable sort key, so it could not be filtered at read time. Health-probe rows do roll up, under the nil tenant.

Flag a tenant synthetic with PUT /api/v1/tenants/{id}/synthetic. The benchmark harness tags its own fixture tenants that way automatically.

Useful queries

Every example below reads all traffic. To match what the Management API and the dashboard report by default, add AND request_type NOT IN ('health_probe', 'benchmark').

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
  AND request_type NOT IN ('health_probe', 'benchmark')
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. The backfill runs only when the rollup is empty, so a restart can never double-count. SummingMergeTree collapses rows sharing the sort key on merge, so the summed columns stay correct.

Both the view and the backfill exclude request_type = 'benchmark', so synthetic-tenant traffic never enters permanent history.

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 spill segments are replayed in order, in checkpointed batches with retry backoff, so a large backlog is drained with bounded memory. New spill stops at 256 MiB or 1,024 files; past that, records are dropped and counted in the obleth_telemetry_dropped gauge. Rows in the WAL have the same structure as usage.