ClickHouse usage

The gateway inserts usage rows and never updates them. Each replica buffers rows and trace spans and inserts them every OBLETH_TELEMETRY_FLUSH_INTERVAL_MS (default 5000, from 100 to 60000), or at once when 500 are waiting, so a request reaches Request logs up to that long after it ends. It creates its tables at startup, and adds missing columns and indexes, in the database named by OBLETH_CLICKHOUSE_DB (default obleth).

usage table

PARTITION BY toYYYYMMDD(ts), ORDER BY (tenant_id, ts_ms), with a minmax skip index idx_ts_ms on ts_ms for cross-tenant time-range reads.

• Cost and energy are fixed at completion from the prices and rates in effect then, and never recomputed, so a price change affects only later requests. Energy is 0 unless energy accounting is on for the model. A non-finite value is stored as 0.
• session_id comes from x-obleth-session-id, x-session-id, or the body's session_id or metadata.session_id; otherwise it is derived from the conversation unless OBLETH_SESSION_ID_DERIVATION is off. See Conversations and sessions.
• device_id is kept only when it is at most 64 letters, digits, _ or -. See Identity tokens.
• cached_input_tokens comes from the backend's usage.prompt_tokens_details.cached_tokens, read from the same usage object as prompt_tokens. Estimates, cancelled requests, answers the gateway writes itself and rows written before v2.1.0 record 0. usage_daily has no sum of it.

Request types

request_type drives the Type column and the type: filter in Request logs.

Helper rows have admission boon, carry the tokens and cost of that one call, and name the request they served in parent_request_id. Compression and knowledge retrieval write no row of their own. POST /v1/messages/count_tokens writes none either.

Internal rows are left out of Management API usage, token and cost reads unless you pass include_internal=true. benchmark rows never enter usage_daily; health_probe rows do. Mark a tenant synthetic with PUT /api/v1/tenants/{id}/synthetic; the benchmark harness does this for its own tenants.

usage_daily rollup

One row per day × tenant × key × model, never pruned. PARTITION BY toYYYYMM(day), ORDER BY (day, tenant_id, key_id, model).

Read it through GET /api/v1/usage/daily, which divides the latency sums by success_requests.

spans table

PARTITION BY toYYYYMMDD(fromUnixTimestamp64Milli(start_ms)), ORDER BY (request_id, start_ms), expired by a ClickHouse TTL 14 days after start_ms. Spans are written only for keys or tenants with tracing on (PUT /api/v1/keys/{id}/tracing, PUT /api/v1/tenants/{id}/tracing) and are read by the request spans endpoint. They are dropped, not spilled, while ClickHouse is unavailable.

usage_meta table

Columns key String, value String, updated_at DateTime64(3), ordered by (key, value). It holds the backfilled_v1 marker and the backfill_claim_v1 election rows, and no usage data.

Data retention

A background worker runs at startup and then hourly, dropping whole usage day partitions older than the retention window. The window defaults to OBLETH_USAGE_RETENTION_DAYS (180) and is set live with PUT /api/v1/settings/usage-retention; stored values are clamped to 1 to 36500 days. POST /api/v1/usage/compact prunes now. usage_daily is never pruned, and you need no ClickHouse TTL on usage.

WAL and replay

When ClickHouse is unavailable, including at boot, usage rows spill to a local write-ahead log whatever OBLETH_FAIL_OPEN says. Spill segments are JSON-lines files in <OBLETH_WAL_PATH>.segments/ (default path ./obleth-telemetry.wal), rotated at 1 MiB or 60 seconds. When ClickHouse answers, the gateway applies the schema and replays segments in order, one checkpointed batch at a time with backoff.

• Spill stops at 256 MiB or 1,024 segments; past that, rows are dropped and counted in obleth_telemetry_dropped. The in-memory buffer holds 10,000 rows; a full buffer also drops.
• A corrupt line moves to <file>.corrupt and the rest of the file replays.
• Each batch carries an insert_deduplication_token from its request ids, and usage and usage_daily set non_replicated_deduplication_window = 10000, so a replayed batch that had already committed is discarded, not counted twice.
• The Helm chart sets OBLETH_WAL_PATH to /var/lib/obleth/telemetry.wal on a per-pod emptyDir: spilled rows survive a container restart but not pod deletion.

Useful queries

Add AND request_type NOT IN ('health_probe', 'benchmark') to match what the dashboard and Management API report.

Tokens by tenant, last 24 hours:

SELECT tenant_id,
       sum(input_tokens)  AS input_tokens,
       sum(output_tokens) AS output_tokens,
       count()            AS requests
FROM obleth.usage
WHERE ts_ms >= toUnixTimestamp(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,
       round(countIf(cache_status = 'hit') / count() * 100, 1) AS hit_pct
FROM obleth.usage
WHERE ts_ms >= toUnixTimestamp(now() - INTERVAL 24 HOUR) * 1000
GROUP BY model
ORDER BY hits DESC;

Latency percentiles, last hour:

SELECT quantile(0.50)(ttft_ms)  AS p50_ttft_ms,
       quantile(0.95)(ttft_ms)  AS p95_ttft_ms,
       quantile(0.95)(total_ms) AS p95_total_ms
FROM obleth.usage
WHERE ts_ms >= toUnixTimestamp(now() - INTERVAL 1 HOUR) * 1000
  AND status_code < 400;

Last use per key:

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
FROM obleth.usage
WHERE ts_ms >= toUnixTimestamp(now() - INTERVAL 30 DAY) * 1000
GROUP BY key_id
ORDER BY requests DESC;

Spend by tenant and month, from the rollup:

SELECT tenant_id,
       toStartOfMonth(day) AS month,
       sum(cost_usd_sum)   AS cost_usd
FROM obleth.usage_daily
GROUP BY tenant_id, month
ORDER BY month, cost_usd DESC;