6krrt/docs/data-model.md

> SQLite schema reference. Back to [README](../README.md).

## Decision Table Schema (SQLite)

Three data tables plus one observability table, `PRAGMA foreign_keys = ON`:

### `models` — one row per served model variant

| Column | Type | Notes |
|---|---|---|
| `model_id` | TEXT | Full catalog id (e.g. `glm-5.2-short-fast-flex` or `qwen2.5-coder-router:14b`) |
| `provider` | TEXT | `neuralwatt` \| `ollama-local` |
| `base_model_id` | TEXT | Model family (e.g. `glm-5.2`). Proficiency/leaderboard keys here. |
| `eligible_categories` | TEXT | Comma-joined category allowlist; `NULL` = unrestricted (cloud rows) |
| `display_name` | TEXT | Human-readable name |
| `cost_per_1m_prompt` | REAL | Listed USD per 1M input tokens |
| `cost_per_1m_completion` | REAL | Listed USD per 1M output tokens |
| `cost_per_1m_prompt_cached` | REAL | Cached prefix price (null if no cache discount) |
| `context_window` | INTEGER | Advertised max tokens |
| `effective_context_window` | INTEGER | `advertised × safety_factor − reserve` |
| `max_output_tokens` | INTEGER | |
| `tier` | INTEGER | 1–3, set by `tier.py` pass |
| `supports_tools` | INTEGER | Boolean 0/1 |
| `supports_json_mode` | INTEGER | |
| `supports_vision` | INTEGER | |
| `supports_reasoning` | INTEGER | "API accepts reasoning param" — NOT a quality signal |
| `reasoning_default_enabled` | INTEGER | The actual tier-bearing signal |
| `latency_class` | TEXT | `standard` \| `flex` (`-flex`: discounted async, held during peak) |
| `reasoning_mode` | TEXT | `default` \| `reduced` (`-fast`: reasoning capped) |
| `context_variant` | TEXT | `full` \| `short` (`-short`: 200K pool bounded budget) |
| `access_level` | TEXT | `public` \| `preview` \| `canary` |
| `pricing_tbd` | INTEGER | |
| `deprecated` | INTEGER | |
| `availability` | TEXT | `active` \| `deprecated` \| `stale` \| `blocked` — the `blocked` value is an operator stop set via the admin override (see [admin-portal.md](admin-portal.md)); it is excluded from routing and pinned requests |
| `last_updated` | TEXT | ISO8601 |

**Serving class:** Neuralwatt ships ~6 base models as 19 catalog rows. The id
suffixes are three **orthogonal** dimensions (`glm-5.2-short-fast-flex`), parsed
by `poller.parse_serving_class` into columns. Rows carry identical catalog
pricing, so routing would pick between them arbitrarily without these — the
`latency_tolerance` hard filter resolves it.

**Local rows:** `provider='ollama-local'` rows come from `config.yaml`'s
`local_dispatch_models:` section and are refreshed by `poller.upsert_local_dispatch_models`
each poll. They start with the three cost columns NULL; once
`seed_local_dispatch_energy.py` has run, those columns hold measured
tariff-priced rates and re-polls never overwrite them.

**Access gating:** 6 of 19 rows are prose-gated
("Private preview (grant-gated)", "(Canary)"). `poller.parse_access_level`
parses them into `access_level` and `routing.allowed_access_levels` (default
`[public]`) excludes them, so dispatch won't earn a 403.

### `proficiency` — one row per (model, provider, category)

| Column | Type | Notes |
|---|---|---|
| `model_id` | TEXT | |
| `provider` | TEXT | |
| `category` | TEXT | See category list below |
| `leaderboard_score` | REAL | 0–1, from external benchmarks |
| `self_eval_score` | REAL | 0–1, from self-eval harness |
| `outcome_score` | REAL | Accumulated client-reported success rate on real traffic (0–1) |
| `outcome_samples` | INTEGER | Number of client-reported `succeeded`/`failed` verifications folded in |
| `self_eval_samples` | INTEGER | Evidence count for benchmark blending threshold |
| `blended_score` | REAL | Expected pass rate on real traffic after empirical-Bayes shrinkage |
| `source` | TEXT | `outcome_blended` \| `outcome_prior` \| `blended` \| `self_eval` \| `self_eval_thin` \| `leaderboard` |
| `inherited_from` | TEXT | Model this row was copied from, NULL if measured directly |
| `last_updated` | TEXT | ISO8601 |

**Category set** (9 categories, defined in `config/config.yaml`):

| Category | Example | Scoring type |
|---|---|---|
| `coding_general` | Merge intervals, parse semver, word wrap | Code (execution) |
| `coding_refactor` | Remove repetition, refactor dispatch chain | Code (execution) |
| `debugging` | Fix closure leak, fix binary search, fix regex | Code (execution) |
| `reasoning_math` | Percent trap, rate trap, counting | Exact match |
| `tool_use_agentic` | Right tool / right args / no tool when empty | Structural |
| `docs_writing` | Docstring quality, must-mention gotchas | Judge |
| `summarization` | Root-cause isolation, buried-lede identification | Judge |
| `translation` | Technical register, hedging/informal tone | Judge |
| `general_chat` | Simple explanations, measured pushback | Judge |

**Scoring kinds** (4 types, objective wherever the category admits it):
- **`code`** — runs model-generated Python in a subprocess, scores fraction of checks passing
- **`exact`** — normalizes & compares a single answer
- **`tool`** — structural: which tool was called, which args matched
- **`judge`** — a strong model scores against a rubric (prose categories only)

**Blending the benchmark signal:** Once `self_eval_samples ≥
self_eval_min_samples` (default 10): `blended = 0.3 × leaderboard + 0.7 ×
self_eval`. Before that, falls back to leaderboard alone. If neither
exists, the benchmark component is neutral.

**From benchmark to expected pass rate:** The files in
`src/proficiency.py` convert the benchmark blend into an expected client
success rate on real traffic. A category with no client outcome traffic
keeps the benchmark score verbatim. A trafficked category with no
per-model outcomes inherits a peer-rate prior. A row with its own
outcomes gets an empirical-Bayes blend of the prior and its observed
rate, with `outcome_prior_strength` pseudo-observations (default 20)
pulling thin data toward the category mean.

Source labels report provenance, not just confidence:
- `outcome_blended` — per-model has real outcome evidence
- `outcome_prior` — category is trafficked, but this row has no own outcomes
- `self_eval_thin` — benchmark measurement below the sample threshold; a
caller wanting to exclude it can

**Proficiency inheritance:** `propagate_to_variants` copies evaluated
`scores` to equivalent serving variants (same weights, same reasoning
setting, same context pool), but never over a row that was measured
directly. A `-fast` row is **not** equivalent to its `-standard` sibling.

"Measured directly" is `inherited_from IS NULL`, not `self_eval_samples > 0`
— inheritance copies the sample count too, so sample count alone cannot tell
an inherited row from a measured one, and using it meant a variant inherited
exactly once and then froze forever. `ensure_columns()` adds the column and
backfills provenance on databases that predate it.

### `energy_observations` — per-request telemetry

| Column | Type | Notes |
|---|---|---|
| `id` | INTEGER | Autoincrement |
| `model_id` / `provider` | TEXT | |
| `task_category` | TEXT | |
| `prompt_tokens` / `completion_tokens` | INTEGER | |
| `energy_kwh` | REAL | **Attributed** billed figure (noisy, 20× within-model) |
| `energy_btu` | REAL | `kwh × 3412.14`, dashboard value |
| `avg_power_watts` / `duration_seconds` | REAL | Pre-attribution product, ~1.8× within-model. `duration_seconds` is the **provider's** reported serving time — see `router_wall_seconds` below, which is a different quantity |
| `attribution_ratio` | REAL | Request's share of shared GPU pool (stable quantized: 0.001, 0.25, 0.5, 0.75) |
| `carbon_g_co2eq` | REAL | Reported by provider |
| `grid_carbon_intensity` | REAL | gCO2/kWh at call time |
| `grid_id` | TEXT | e.g. `FI` |
| `carbon_source` | TEXT | `static_fallback` (constant, excluded from routing) or live measurement |
| `cost_usd` | REAL | **Billed** figure, not tokens × list price |
| `allowance_remaining_usd` | REAL | |
| `service_tier` | TEXT | As billed |
| `cached_prompt_tokens` | INTEGER | `usage.prompt_tokens_details.cached_tokens`. NULL when the provider reported no count — never a substituted 0 |
| `cached_tokens_source` | TEXT | Why the column above is what it is: `reported` (a number arrived, 0 included), `details_no_count` (a details block with no cached count), `no_details` (no details block at all). NULL only on rows written before the column |
| `router_wall_seconds` | REAL | **Router-observed** wall clock for the request, `time.monotonic()`. Not `duration_seconds` — see below |
| `router_ttft_seconds` | REAL | Router-observed time to the first output token. Streaming only; NULL on a buffered row means not applicable |
| `observed_at` | TEXT | ISO8601 |

**Router-observed latency is a second quantity, not a backfill of the first.**
`duration_seconds` is what the provider says it spent serving; the two
`router_*` columns are what the router measured end to end, which additionally
includes connection setup, network transit, queueing ahead of the first token,
and router overhead. They are never written into each other.

The reason for the second measurement is coverage. On the live database
`duration_seconds` is present on 31,243 of 31,309 NeuralWatt rows and on **0**
of 3,850 OpenRouter rows, and no request-body opt-in will change that:
OpenRouter serves generation timing only from its separate
`/api/v1/generation?id=` endpoint, a second HTTP call per request. OpenRouter
carries ~73% of routed decisions, so a latency term in the objective needs a
number that exists for every provider. The router can always take one.

Spans, which differ by path:

| path | `router_wall_seconds` | `router_ttft_seconds` |
|---|---|---|
| streaming `/v1/chat/completions` | connection open → last byte forwarded | connection open → first delta carrying content or a `tool_calls` fragment |
| buffered `/v1/chat/completions` | just before the POST → complete response body | NULL, not applicable |
| `POST /dispatch` | just before the SDK call → response returned | NULL, not applicable |

Three things follow from those definitions. The mark is re-taken per attempt,
so a failover records only the candidate that actually answered. A role-only
opening delta does not count as a first token — it is protocol, not answer — so
TTFT is measured against output a user could see. And if a streaming client
hangs up early the `finally` still records the span up to abandonment, which
understates that request's latency rather than inflating it.

NULL on rows written before the columns existed, and on `seed_energy.py` and
`eval_proficiency.py` rows, which do not pass the timings.

**Attribution noise:** Billed `energy_kwh = avg_power_watts × duration ×
attribution_ratio`. The attribution term looks like noise up close (8 identical
calls varied 20×), but ranks 750× between models while within-model spread is
1.8× — it's a stable per-model property reflecting serving concurrency. Routing
scores on the attributed figures with a **median** over all `seed_reference`
rows, so repeated sweeps accumulate into a median-across-time.

### `provider_balance_observations` — per-provider prepaid pool snapshots

| Column | Type | Notes |
|---|---|---|
| `id` | INTEGER | Autoincrement |
| `provider` | TEXT | |
| `balance_usd` | REAL | Current prepaid pool balance |
| `total_credits_usd` | REAL | Lifetime credits purchased |
| `total_usage_usd` | REAL | Lifetime usage billed against the pool |
| `observed_at` | TEXT | ISO8601 |

Polled by `poller.py` from each provider's balance endpoint
(`provider.balance_url`). Used by `quota_accounts()` to compute per-provider
burn rate, projected runway, and stale-reading alerts.

### `local_energy_observations` — per-call local hardware draw

| Column | Type | Notes |
|---|---|---|
| `id` | INTEGER | Autoincrement |
| `model_id` | TEXT | Local Ollama tag (e.g. `mistral-nemo:12b` or `qwen2.5-coder-router:14b`), not a cloud model |
| `call_type` | TEXT | Not a closed enum. Current values include `classify`, `verify`, `local_vision`, `local_dispatch`, `file_summarization`, `diff_checking`, and `seed_local_dispatch`. |
| `request_id` | TEXT | Optional; joins to `POST /outcome` reports the same way `energy_observations.request_id` does for cloud rows |
| `session_dir` | TEXT | Optional; used for source-less `/outcome` attribution |
| `session_key` | TEXT | Optional; namespaced conversation key (`"c:" + conversation`) from `X-Router-Conversation`, or NULL for a no-header local row |
| `avg_power_watts` | REAL | Averaged over the call (background `nvidia-smi` sampler) |
| `duration_seconds` | REAL | Wall-clock time for the local call |
| `energy_kwh` | REAL | `avg_power_watts × duration_seconds / 3_600_000` |
| `cost_usd` | REAL | `energy_kwh × tariff_usd_per_kwh`; NULL if tariff not configured |
| `carbon_g_co2eq` | REAL | `energy_kwh × 1000 × grid_intensity_g_per_kwh`; NULL unless grid intensity configured |
| `meter` | TEXT | `nvidia_smi` (room for RAPL / smart plug later) |
| `observed_at` | TEXT | ISO8601 |

**Separate table, by design.** `quota_burn()` sums `energy_kwh` over **all**
of `energy_observations` against the NeuralWatt plan allowance. A separate
table makes it structurally impossible for local electricity to leak into that
number. `local_energy_summary()` in `metrics.py` queries this table exclusively,
over the same 30-day window. `/metrics` surfaces it as a top-level
`"local_energy"` key, and the admin dashboard has a distinct "Local compute"
card. The meter is off by default (`local_energy:` in config); config load
refuses `enabled: true` without a `tariff_usd_per_kwh`. Remote-Ollama safety:
if any Ollama base URL is non-loopback, metering skips with a log warning.
`ensure_local_energy_table()` in `src/local_energy.py` also creates the table
and index idempotently for live databases predating this schema.

### `verifications` — response quality observations

| Column | Type | Notes |
|---|---|---|
| `id` | INTEGER | Autoincrement |
| `model_id` / `provider` | TEXT | |
| `task_category` | TEXT | Optional (prose answers may lack a category) |
| `kind` | TEXT | `structural` \| `local_llm` \| `client_outcome` |
| `verdict` | TEXT | `ok` \| `truncated` \| `malformed` \| `unverifiable` \| `succeeded` \| `failed` |
| `detail` | TEXT | Human-readable reason |
| `completion_tokens` | INTEGER | Wasted answer cost, for payoff sum |
| `observed_at` | TEXT | ISO8601 |
| `applied_at` | TEXT | Set by `feedback.py` when folded into proficiency |
| `model_attributable` | INTEGER | 1 = model's fault; 0 = client caused (e.g. tight cap) |

Verifications drive the feedback loop: `feedback.py` reads unanswered failures,
applies a 0.0 sample per failure to `proficiency`, and marks them
`applied_at` for idempotency. `unverifiable` is recorded but not treated as a
failure — it means the checker had nothing to say, not that the model failed.

### `route_decisions` — routing observability

| Column | Type | Notes |
|---|---|---|
| `id` | INTEGER | Autoincrement |
| `observed_at` | TEXT | ISO8601, UTC |
| `kind` | TEXT | `route` \| `dispatch` \| `chat` \| `passthrough` \| `local_vision` \| `local_dispatch_fallback` |
| `task_category` | TEXT | |
| `task_tier` | INTEGER | 1–3 |
| `required_context_tokens` | INTEGER | |
| `confidence` | REAL | Confidence of the classification that ROUTED the request. On a `chat` row this is almost always `1.0`, because the chat path re-routes through the override branch, which hard-codes it. Use `classifier_confidence` for what the classifier said |
| `classifier_ms` | INTEGER | Classification latency |
| `classification_source` | TEXT | `classifier` \| `override` \| `fallback` \| `cached` \| `session_stale` \| `session_history` \| `classifier_cloud` |
| `latency_tolerance` | TEXT | `interactive` \| `batch` |
| `candidates_considered` | INTEGER | How many survived hard filters |
| `selected_model` | TEXT | Null when no model was selected |
| `selected_provider` | TEXT | `neuralwatt`, `local` (vision fallback), or `ollama-local` (local dispatch) |
| `runner_up_models` | TEXT | JSON array of up to 3 runner-up candidates |
| `est_cost_usd` | REAL | Estimated cost of the selected model |
| `est_proficiency` | REAL | Estimated proficiency for the task category |
| `rejected_reason` | TEXT | Active filters when nothing was selected |
| `session_key` | TEXT | `"c:" + conversation id` from `X-Router-Conversation` when the client sent one, else a hashed content fingerprint of the conversation; NULL on a no-header local row |
| `agent` | TEXT | Optional; agent name from `X-Router-Agent` (namespaced form, see [api.md](api.md)) |
| `parent_key` | TEXT | Optional; `"c:" + parent` `X-Router-Parent` conversation key, when the request named one |
| `tools` | INTEGER | 0/1 — request carried a `tools` array |
| `images` | INTEGER | 0/1 — request carried image parts |
| `json_mode` | INTEGER | 0/1 — request required JSON mode |
| `streamed` | INTEGER | 0/1 — response was streamed |
| `pinch_original_tokens` | INTEGER | Estimated tokens before pruning (includes `extra_fixed_tokens`) |
| `pinch_final_tokens` | INTEGER | Estimated tokens after pruning |
| `request_id` | TEXT | Provider request id; joins to `energy_observations.request_id` |
| `exploration` | INTEGER | 0/1 — selected as the least-evidenced candidate for exploration |
| `prefix_divergence_index` | INTEGER | First message position whose bytes differ from the previous turn in this session |
| `prefix_tokens_after_divergence` | INTEGER | Estimated tokens at or after that position in THIS turn |
| `prefix_prev_message_count` | INTEGER | The previous turn's message count |
| `classifier_confidence` | REAL | The primary classifier's confidence in its answer, accepted or not. NULL when it produced none (a timeout) or made no attempt (`override`, `cached`) |
| `classifier_coverage` | REAL | `local_decision` only: the total option mass behind that answer |
| `classifier_reject` | TEXT | NULL when the answer was used; otherwise why it was not (codes below) |

### What the classifier did, and why its answer was not used

The three `classifier_*` columns record the **primary classifier's own
attempt**, read before the chat path re-routes on measured context. They exist
because a declined answer used to leave one number behind and it was in a log
line: `classification_source = session_history` says a borrowed label routed the
turn, not whether the classifier was a hair under its floor or nowhere near it,
and `confidence` could not say so (see its row above).

`classifier_reject` is NULL when the answer was used, and otherwise one of:

| code | meaning | numbers recorded |
|---|---|---|
| `below_confidence_min` | answered, but under `classifier.*.confidence_min` | confidence (and coverage for `local_decision`) |
| `below_coverage_min` | `local_decision`: total option mass under `coverage_min` | coverage, and the would-be confidence |
| `no_logprobs` | `local_decision`: Ollama returned none | none |
| `timeout` | the endpoint did not answer in time | none |
| `transport_error` | the endpoint was unreachable or errored | none |
| `parse_error` | the reply could not be parsed | none |
| `primary_failed` | any other failure of the primary | none |
| `skipped_gaming_mode`, `skipped_backoff` | the local classifier was never asked | none |

`classification_source` then says what routed the turn instead: `session_history`
(this session's last real label), `fallback` (the static guess) or
`classifier_cloud`. Tune `confidence_min` from the distribution: for
`local_decision` rows, compare `classifier_confidence` where `classifier_reject`
is NULL against where it is `below_confidence_min`.

Rows from before these columns existed hold NULL in all three, which is the
honest value; the numbers were never stored, so there is nothing to backfill.
The admin decisions page shows them as the tooltip on the source badge, the TUI
detail popup shows all three, and the `/metrics` degraded-share warning lists the
reasons it saw.

The three `prefix_*` columns are the prefix-stability probe
(`pinch.prefix_probe`, on by default). The provider bills the longest
byte-identical **prefix** of a prompt at the cached rate, so a divergence
early in the payload re-bills everything after it; these say how much of the
previous turn's cache this turn kept. Read them together:
`prefix_divergence_index == prefix_prev_message_count` is a healthy append,
and anything lower is rewritten history —
`prefix_tokens_after_divergence` is then what it cost.

They are **NULL together** when there was no previous turn to compare against
(first turn of a session, or first after a restart), which is deliberately
distinct from a measured `0` — a zero would read as total cache loss at
message 0. **Hashes only, and not even those**: the per-message digests that
produce these numbers live in process memory for exactly one turn and never
reach the database, so nothing here is reversible to any message. Rows written
before the columns existed are NULL and nothing may be inferred for them; the
payloads were never stored, by design.

`pinch_original_tokens` and `pinch_final_tokens` capture context-pruning
outcomes: `tokens_saved = original - final`, and `pruned = original > final`.
Both are NULL when pinch is disabled or the request predates the columns.
`request_id` is written back after the provider returns a completion id; it is
NULL on `/route` (no upstream call) until backfilled. `exploration` is 1 when
`exploration.choose` replaced the ranked winner with a least-evidenced
candidate.

Rows with `kind='local_dispatch_fallback'` record a degraded answer after the
cloud refused or was exhausted — they are **NOT evidence that local was
competitive on merit** (do not feed them into proficiency analysis). They carry
`selected_provider='ollama-local'`.

`route_decisions` stores one row per routing decision so "how is routing
performing" is answerable: which model was picked, for what category/tier,
how long classification took, and — when nothing was selected — which hard
filter shut it out. It is an observability table: nothing in routing reads it.

It keys a conversation by `session_key` -- `"c:" + conversation id` when the client
sent `X-Router-Conversation`, else a hashed content fingerprint; `session_dir`,
prompts, and answers are deliberately excluded. A test enforces that the write
path does not store prompt or answer text. The write is gated by
`logging.log_route_decisions` and is **best-effort**: a failed write is logged
at warning and swallowed so monitoring cannot slow or fail a request.

## Watchdog Schema (SQLite)

Four tables written by `watchdog.py` via `src/watchdog_store.py` (the code-side
inline-create mirrors this schema for live databases). See
[watchdog.md](watchdog.md) for how the loop reads them.

### `watchdog_ticks` — one row per detection run

| Column | Type | Notes |
|---|---|---|
| `id` | INTEGER | Autoincrement |
| `ticked_at` | TEXT | ISO8601 run time |
| `sessions_seen` | INTEGER | Count of sessions examined this run, default 0 |
| `outcome` | TEXT | | 

### `watchdog_verdicts` — one row per session per tick

| Column | Type | Notes |
|---|---|---|
| `id` | INTEGER | Autoincrement |
| `tick_id` | INTEGER | FK → `watchdog_ticks.id`, NOT NULL |
| `session_id` | TEXT | NOT NULL |
| `session_root` | TEXT | |
| `agent` | TEXT | |
| `model_id` | TEXT | |
| `provider` | TEXT | |
| `flagged` | INTEGER | 0/1, default 0 |
| `dup` | REAL | Duplicate-fraction signal |
| `top` | INTEGER | |
| `top_what` | TEXT | |
| `landed` | INTEGER | |
| `slow` | INTEGER | |
| `coverage` | REAL | |
| `calls_since_landed` | INTEGER | |
| `cost_since_landed_usd` | REAL | |
| `llm_second_opinion` | TEXT | |
| `created_at` | TEXT | NOT NULL |

Four indexes (`config/schema.sql` L482-485):

| Index | On |
|---|---|
| `idx_watchdog_verdicts_model_id` | `watchdog_verdicts(model_id)` |
| `idx_watchdog_verdicts_created_at` | `watchdog_verdicts(created_at)` |
| `idx_watchdog_verdicts_flagged` | `watchdog_verdicts(flagged)` |
| `idx_watchdog_verdicts_session_root` | `watchdog_verdicts(session_root)` |

### `watchdog_alerts` — one row per open alert

| Column | Type | Notes |
|---|---|---|
| `dedup_key` | TEXT | PRIMARY KEY; e.g. `opencode-loop:<root session id>` |
| `state` | TEXT | NOT NULL |
| `severity` | TEXT | NOT NULL |
| `flagged_ticks` | INTEGER | Default 0 |
| `opened_at` | TEXT | NOT NULL |
| `last_fired_at` | TEXT | NOT NULL |
| `resolved_at` | TEXT | |

### `watchdog_channel_settings` — per-channel notification config

| Column | Type | Notes |
|---|---|---|
| `id` | INTEGER | Autoincrement |
| `channel_name` | TEXT | NOT NULL, UNIQUE |
| `enabled` | INTEGER | Default 1 |
| `min_severity` | TEXT | Default `warning` |