6krrt/config/schema.sql

-- Model router decision table schema
-- SQLite. Run with: sqlite3 router.db < schema.sql

PRAGMA foreign_keys = ON;

-- One row per (model_id, provider). Refreshed by the pricing poller.
CREATE TABLE IF NOT EXISTS models (
    model_id                    TEXT NOT NULL,
    provider                    TEXT NOT NULL,          -- 'neuralwatt' | 'ollama-local'
    -- The model family under the serving suffixes: glm-5.2-short-fast-flex
    -- and glm-5.2 share one. Proficiency and leaderboard priors are properties
    -- of the weights, not the queue, so both key on this and every variant
    -- inherits from its family.
    base_model_id               TEXT,
    display_name                TEXT,
    cost_per_1m_prompt          REAL,                    -- USD, null if pricing_tbd
    cost_per_1m_completion      REAL,
    cost_per_1m_prompt_cached   REAL,                    -- null if provider has no cache discount
    context_window              INTEGER,                 -- advertised max tokens
    effective_context_window    INTEGER,                 -- derived, see §3.2 of design doc
    max_output_tokens           INTEGER,
    tier                        INTEGER,                 -- 1-3, set manually / by a tiering pass
    supports_tools              INTEGER DEFAULT 0,       -- boolean 0/1
    supports_json_mode          INTEGER DEFAULT 0,
    supports_vision             INTEGER DEFAULT 0,
    supports_reasoning          INTEGER DEFAULT 0,       -- capabilities.reasoning: "API accepts a
                                                         -- reasoning param", NOT a quality signal.
                                                         -- True for ~90% of the catalog; do not tier on it.
    -- Whether reasoning is ON by default (metadata.reasoning.default_enabled), falling back to
    -- supports_reasoning when the model exposes no reasoning block. This is the tier-bearing signal.
    reasoning_default_enabled   INTEGER DEFAULT 0,

    -- Serving class. NeuralWatt ships one base model as several rows that differ only by suffix;
    -- these three dimensions are orthogonal, hence ids like 'glm-5.2-short-fast-flex'. They carry
    -- no price difference in the catalog, so without these columns such rows tie exactly and the
    -- router picks between them arbitrarily.
    latency_class               TEXT DEFAULT 'standard', -- 'standard' | 'flex' (-flex: discounted
                                                         -- async, held server-side during peak)
    reasoning_mode              TEXT DEFAULT 'default',  -- 'default' | 'reduced' (-fast: thinking
                                                         -- disabled or capped to a short budget)
    context_variant             TEXT DEFAULT 'full',     -- 'full' | 'short' (-short: 200K pool with
                                                         -- a bounded reasoning budget)

    -- Access gating is prose-only in the catalog ("Private preview (grant-gated)", "canary"), so
    -- it is parsed from the description. Non-public rows are excluded from routing by default,
    -- otherwise the dispatcher selects them and takes a 403.
    access_level                TEXT DEFAULT 'public',   -- 'public' | 'preview' | 'canary'
    eligible_categories         TEXT,                    -- comma-joined category allowlist, NULL = unrestricted (cloud rows)

    pricing_tbd                 INTEGER DEFAULT 0,
    deprecated                  INTEGER DEFAULT 0,
    availability                TEXT DEFAULT 'active',   -- 'active' | 'deprecated' | 'stale'
    last_updated                TEXT NOT NULL,            -- ISO8601
    PRIMARY KEY (model_id, provider)
);

-- One row per (model_id, provider, category). Refreshed by the benchmark poller
-- and/or written to directly by your self-eval harness.
CREATE TABLE IF NOT EXISTS proficiency (
    model_id        TEXT NOT NULL,
    provider        TEXT NOT NULL,
    category        TEXT NOT NULL,   -- coding_general, coding_refactor, debugging,
                                      -- docs_writing, summarization, translation,
                                      -- reasoning_math, tool_use_agentic, general_chat
    leaderboard_score REAL,          -- 0-1, from external benchmark sources
    self_eval_score    REAL,         -- 0-1, from your own eval harness
    self_eval_samples  INTEGER DEFAULT 0,
    outcome_score      REAL,         -- 0-1, from verified client outcomes
    outcome_samples    INTEGER DEFAULT 0,
    blended_score       REAL,        -- computed: see blending rule in design doc
    source              TEXT,        -- 'leaderboard' | 'self_eval' | 'blended'
    -- Which model this row's scores were COPIED from, or NULL if they were
    -- measured on this row directly. Provenance, not decoration: without it
    -- an inherited row is indistinguishable from a measured one (both carry
    -- self_eval_samples > 0), so propagate_to_variants could not tell which
    -- rows it was allowed to refresh and froze every variant permanently at
    -- its first inheritance.
    inherited_from      TEXT,
    last_updated         TEXT NOT NULL,
    PRIMARY KEY (model_id, provider, category),
    FOREIGN KEY (model_id, provider) REFERENCES models (model_id, provider)
);

-- Per-request energy/cost observations, logged by the dispatcher as real calls
-- happen (energy is only available on actual completions, not the models list).
-- This is what you aggregate into a per-model energy average over time.
CREATE TABLE IF NOT EXISTS energy_observations (
    id               INTEGER PRIMARY KEY AUTOINCREMENT,
    model_id         TEXT NOT NULL,
    provider         TEXT NOT NULL,
    -- The provider's completion id (chatcmpl-...). The client receives this in
    -- the response body and in every stream chunk, so it is the join key that
    -- lets a client report back later whether the answer actually worked.
    request_id       TEXT,
    -- Fingerprint of the conversation this completion belongs to, derived from
    -- its opening message. Stable across a session's turns and distinct
    -- between sessions, so the router can tell whether two clients are active
    -- WITHOUT the clients cooperating. Used to refuse ambiguous outcome
    -- attribution rather than guess.
    session_key      TEXT,
    -- Working directory, when the conversation reveals one. Agent clients
    -- usually put the cwd in their system prompt, which makes an outcome
    -- report from that directory attributable even under concurrency.
    session_dir      TEXT,
    task_category    TEXT,
    prompt_tokens    INTEGER,
    completion_tokens INTEGER,
    -- energy_kwh is what NeuralWatt BILLS. It equals avg_power_watts *
    -- duration_seconds * attribution_ratio, where attribution_ratio is this
    -- request's share of a shared multi-tenant GPU pool. Up close that term
    -- looks like noise: eight identical calls to one model inside one minute
    -- varied 20x, correlating +0.997 with the ratio while power and duration
    -- held steady.
    --
    -- It is not noise, and this is the ATTRIBUTED figure scoring reads. Across
    -- the reference sweep the median ratio spans 750x BETWEEN models while the
    -- typical spread WITHIN one is 1.8x, and the values are quantized (0.001,
    -- 0.25, 0.5, 0.75) -- that is serving concurrency, a stable per-model
    -- property. A model whose GPUs carry more concurrent requests genuinely
    -- costs less per request. The median over repeated sweeps absorbs what
    -- noise remains.
    energy_kwh       REAL,           -- response.energy.energy_kwh (attributed)
    energy_btu       REAL,           -- energy_kwh * 3412.14, purely for comedic dashboard value

    -- The pre-attribution terms. avg_power_watts * duration_seconds is the
    -- pool's energy over the request, independent of how many other tenants
    -- shared it. Scoring on that product was TRIED and is wrong: it discards
    -- the 750x between-model signal above to suppress a 1.8x within-model one.
    -- Kept as a diagnostic (dispatcher.gross_energy_kwh) so the decomposition
    -- stays inspectable, not as a scoring input.
    avg_power_watts  REAL,           -- response.energy.avg_power_watts
    duration_seconds REAL,           -- response.energy.duration_seconds
    attribution_ratio REAL,          -- the share term, kept so the identity checks out

    -- Carbon is what design doc §4 actually scores eco on, and NeuralWatt
    -- reports it per-request rather than making us derive it. Grid intensity
    -- and region are stored alongside because the same energy in a different
    -- region is a different carbon figure -- keeping them makes the open
    -- question (real-time intensity vs per-model average) answerable later
    -- from logged data instead of a re-run.
    carbon_g_co2eq   REAL,           -- response.energy.carbon_g_co2eq
    grid_carbon_intensity REAL,      -- gCO2/kWh at call time
    grid_id          TEXT,           -- e.g. 'FI'
    -- How the carbon figure was obtained. 'static_fallback' means NeuralWatt
    -- could not resolve live grid data and substituted a constant (475.0,
    -- about a global average) while still reporting the original grid_id --
    -- so the number is a placeholder, not a measurement. Routing on it would
    -- penalize a model against a made-up figure, so eco scoring excludes it.
    carbon_source    TEXT,           -- 'agent_cache' | 'static_fallback' | ...

    -- The provider's own billed figure (response.cost.request_cost_usd), NOT
    -- a tokens x list-price estimate. These disagree: flex rows bill roughly
    -- 40% under their standard sibling while the catalog advertises both at
    -- the same price, so the estimate would be wrong for every flex call.
    cost_usd         REAL,
    allowance_remaining_usd REAL,    -- response.cost.allowance_remaining_usd
    service_tier     TEXT,           -- response.service_tier, as billed
    -- measured against routing.assumed_cache_rate's guess. NULL = provider did not report.
    cached_prompt_tokens INTEGER,    -- usage.prompt_tokens_details.cached_tokens
    -- WHY the column above is what it is, because NULL alone cannot say. A
    -- provider that omits the field on a full cache miss and one that never
    -- reports it at all both store NULL, and the difference decides whether
    -- every cache rate computed from the non-NULL rows is conditioned on a
    -- hit having occurred (and so biased upward) or is the whole picture.
    --
    --   'reported'         cached_tokens carried a number -- 0 included, and
    --                      an explicit 0 IS a measured full miss
    --   'details_no_count' a prompt_tokens_details block arrived with no
    --                      cached count in it (key absent, or JSON null)
    --   'no_details'       no prompt_tokens_details block at all, which
    --                      includes responses carrying no usage block
    --
    -- Invariant: cached_prompt_tokens IS NOT NULL exactly when this reads
    -- 'reported'. NULL here means the row predates the column; it does not
    -- mean any of the three.
    cached_tokens_source TEXT,

    -- Router-observed wall clock. DELIBERATELY NOT duration_seconds, which is
    -- the PROVIDER's reported serving time and is a different quantity: this
    -- one includes connection setup, network transit, queueing ahead of the
    -- first token, and the router's own overhead. Conflating them would
    -- corrupt the one clean serving-time measurement that exists.
    --
    -- It exists because duration_seconds does not: on the live DB it is
    -- present on 31,243 of 31,309 NeuralWatt rows and on 0 of 3,850
    -- OpenRouter rows. OpenRouter serves generation timing only from its
    -- separate /api/v1/generation?id= endpoint -- a second HTTP call per
    -- request -- so no request-body opt-in will ever fill it, and OpenRouter
    -- is ~73% of routed decisions. A latency term in the objective needs a
    -- number that exists for every provider, and the router can always take
    -- one.
    --
    -- Measured with time.monotonic() (as local_energy.py already does for the
    -- local ledger's duration), never a wall-clock-of-day difference, so an
    -- NTP step cannot produce a negative duration.
    --
    -- THE SPAN, which differs by path and matters when reading these:
    --
    --   buffered   from immediately before the upstream POST is issued to
    --              the moment requests has the complete response body.
    --   streaming  from immediately before the upstream connection is opened
    --              for the candidate that was ACCEPTED (a failed candidate's
    --              failover attempt is not counted) to the last byte
    --              forwarded to the client. If the client disconnects early,
    --              the generator's finally still records the span up to that
    --              abandonment -- shorter than the full answer would have
    --              been, not longer.
    --
    -- Failed attempts before a failover are excluded on both paths: the mark
    -- is re-taken per attempt, so this reads as "how long the model that
    -- actually answered took", not "how long the request took to satisfy".
    router_wall_seconds REAL,

    -- Time to first OUTPUT token, router-observed, from the same start mark as
    -- router_wall_seconds. First = the first streamed delta carrying non-empty
    -- content OR a tool_calls fragment; a role-only opening delta is not an
    -- answer and does not count.
    --
    -- STREAMING ONLY. NULL on a buffered row means "not applicable" -- the
    -- whole answer arrives at once, so there is no first token distinct from
    -- the last. NULL on a streamed row means no output delta ever arrived
    -- (an upstream that broke before emitting one). For an interactive coding
    -- agent this is closer to what the operator feels than the total.
    router_ttft_seconds REAL,
    observed_at      TEXT NOT NULL
);

-- One row per verified completion. Structural checks are free, so every
-- response gets one; the point is to learn which models fail on REAL work
-- rather than only on the fixed 23-task benchmark in evals/tasks.yaml.
--
-- 'unverifiable' is recorded and is NOT a failure. Most prose lands there,
-- and counting "we could not check this" as "this was wrong" would penalize
-- models for the checker's limits.
CREATE TABLE IF NOT EXISTS verifications (
    id               INTEGER PRIMARY KEY AUTOINCREMENT,
    model_id         TEXT NOT NULL,
    provider         TEXT NOT NULL,
    request_id       TEXT,            -- provider completion id, for client reports
    task_category    TEXT,
    kind             TEXT NOT NULL,   -- 'structural' | 'local_llm' | 'client_outcome'
    -- 'succeeded'/'failed' come only from client_outcome and are ground truth:
    -- the client ran the code, or used the answer, and knows. Every other
    -- verdict is a proxy for that.
    verdict          TEXT NOT NULL,   -- ok | truncated | malformed | unverifiable
                                      -- | succeeded | failed
    detail           TEXT,
    completion_tokens INTEGER,        -- what a wasted answer cost, for the payoff sum
    observed_at      TEXT NOT NULL,
    -- Set once feedback.py has folded this row into proficiency, so re-running
    -- cannot penalize a model repeatedly for the same bad response.
    applied_at       TEXT,
    -- Whether this failure is the MODEL's fault. A client that sets a tight
    -- max_tokens and gets a truncated answer caused that itself; counting it
    -- against the model would let any agent with a small cap systematically
    -- drag down whatever it routed to. Still recorded -- the response really
    -- was unusable -- but excluded from proficiency feedback.
    model_attributable INTEGER DEFAULT 1
);

-- One row per routing decision, logged by the dispatcher on every route that
-- is made (see todo #2 of router-monitoring-tui.md for the writes; this table
-- and its inline-create helper are todo #1). It makes "how is routing
-- performing" answerable: which model was picked, for what category/tier, how
-- long classification took, and — when nothing was selected — which hard
-- filter shut it out.
--
-- This is an OBSERVABILITY table, not a scoring input: nothing in routing.py
-- reads it. It stores only the hashed session fingerprint in `session_key`;
-- never `session_dir` and never any prompt or answer text. A test enforces
-- that the write path stores only the hash.
--
-- Like `energy_observations`, `observed_at` uses
-- datetime.now(timezone.utc).isoformat().
CREATE TABLE IF NOT EXISTS route_decisions (
    id                       INTEGER PRIMARY KEY AUTOINCREMENT,
    observed_at              TEXT NOT NULL,          -- ISO8601, UTC
    kind                     TEXT NOT NULL,          -- 'route' | 'dispatch' | 'chat'
                                                     -- | 'passthrough' | 'local_vision'
                                                     -- | 'local_dispatch_fallback'
                                                     --   (degraded local answer after
                                                     --   cloud refused/exhausted)
    task_category            TEXT,
    task_tier                INTEGER,                -- 1-3
    required_context_tokens  INTEGER,
    confidence               REAL,
    classifier_ms            INTEGER,
    -- 'classifier' | 'override' | 'fallback' | 'cached' | 'session_stale'
    -- | 'session_history' | 'classifier_cloud'. The last three are the
    -- degraded cascade steps; 'cached' predates the cascade and was
    -- already written by the code but missing from this comment.
    classification_source    TEXT,
    latency_tolerance        TEXT,                   -- 'interactive' | 'batch'
    candidates_considered    INTEGER,
    selected_model           TEXT,                   -- null when nothing was selected
    -- 'neuralwatt' for routed/dispatch/passthrough, 'local' for the local-vision
    -- fallback, 'ollama-local' for the local dispatch model (including
    -- kind='local_dispatch_fallback' rows). Kept so a decision row can join to
    -- energy_observations on the (model_id, provider) key that table uses.
    selected_provider        TEXT,
    runner_up_models         TEXT,                   -- JSON array of {"model_id":..,
                                                     -- "provider":..}, <=3; nullable
    est_cost_usd             REAL,
    est_proficiency          REAL,
    rejected_reason          TEXT,                   -- the 422 limits when no selection
    session_key              TEXT,                   -- hashed session fingerprint ONLY,
                                                     -- never session_dir or prompt text
    tools                    INTEGER,                -- 0/1
    images                   INTEGER,                -- 0/1
    json_mode                INTEGER,                -- 0/1
    streamed                 INTEGER,                -- 0/1
    flex_preference          TEXT,                   -- 'no-flex'|'auto'|'prefer-flex'
                                                       -- |'force-flex' (resolved)
    flex_swapped             INTEGER,                -- 0/1 post-rank flex swap applied
    flex_forced              INTEGER,                -- 0/1 swap bypassed interactive
    request_id               TEXT,                   -- provider completion id
    exploration              INTEGER DEFAULT 0,      -- 0/1 whether rollout/exploration
    pinch_original_tokens    INTEGER,                -- estimated tokens before pruning
    pinch_final_tokens       INTEGER,                -- tokens sent after pruning (may
                                                      -- equal original when no pruning)
    profile                  TEXT,                   -- routing profile name (e.g.,
                                                      -- 'default', 'locality')
    -- Prefix-stability probe (pinch.prefix_probe). The provider bills the
    -- longest byte-identical PREFIX at the cached rate, so these three say how
    -- much of the previous turn's cache this turn's pruned payload kept.
    -- HASHES ONLY, and not even those: the per-message digests that produce
    -- these numbers live in process memory for one turn and never reach this
    -- table. Nothing here is reversible to any message. All three are NULL
    -- together when there was no previous turn to compare against in this
    -- process -- which is NOT the same as a measured zero.
    prefix_divergence_index       INTEGER,           -- first message position whose
                                                     -- bytes differ from last turn;
                                                     -- = the longest stable prefix
    prefix_tokens_after_divergence INTEGER,          -- estimated tokens at/after it
                                                     -- in THIS turn: the re-billed part
    prefix_prev_message_count     INTEGER            -- last turn's message count. A
                                                     -- pure append diverges at exactly
                                                     -- this index; anything lower is
                                                     -- rewritten history.
);

CREATE INDEX IF NOT EXISTS idx_verifications_model ON verifications (model_id, provider);
CREATE INDEX IF NOT EXISTS idx_verifications_verdict ON verifications (verdict);
CREATE INDEX IF NOT EXISTS idx_observations_request ON energy_observations (request_id);

CREATE INDEX IF NOT EXISTS idx_models_provider ON models (provider);
CREATE INDEX IF NOT EXISTS idx_models_availability ON models (availability);
CREATE INDEX IF NOT EXISTS idx_models_routing ON models (access_level, latency_class, tier);
CREATE INDEX IF NOT EXISTS idx_proficiency_category ON proficiency (category);
CREATE INDEX IF NOT EXISTS idx_energy_model ON energy_observations (model_id, provider);
-- recent_decisions orders by id, but a time-window query benefits from this.
CREATE INDEX IF NOT EXISTS idx_route_decisions_observed ON route_decisions (observed_at);
-- The cascade's session-history step looks up a session's last real
-- classification by session_key; without this it scans the table.
CREATE INDEX IF NOT EXISTS idx_route_decisions_session_key ON route_decisions (session_key);
-- Guarded — re-applying a fresh schema is a no-op.
CREATE INDEX IF NOT EXISTS idx_energy_observed ON energy_observations (observed_at);
CREATE INDEX IF NOT EXISTS idx_verifications_observed ON verifications (observed_at);

-- Per-call local energy observations for the router's own Ollama calls
-- (classifier, verifier, local-vision fallback, and local dispatch). Greatly
-- simplified compared to energy_observations because local inference has no
-- multi-tenant attribution ratio and no provider billing: we measure wall
-- power, count duration, apply a user-provided tariff, and optionally a grid
-- intensity.
--
-- model_id here is the Ollama model tag (e.g. mistral-nemo-router:12b), not a
-- cloud model. call_type is NOT a closed enum; classify/verify/local_vision are
-- historical, and local dispatch adds file_summarization, diff_checking, and
-- local_dispatch. request_id and session_dir exist so POST /outcome can
-- attribute client outcome reports to local-dispatch answers exactly the way it
-- attributes cloud answers via energy_observations.
CREATE TABLE IF NOT EXISTS local_energy_observations (
    id                INTEGER PRIMARY KEY AUTOINCREMENT,
    model_id          TEXT NOT NULL,
    call_type         TEXT NOT NULL,
    -- The provider completion id that the local dispatch response echoes back.
    -- Same purpose as energy_observations.request_id: lets the client report
    -- whether the answer worked.
    request_id        TEXT,
    -- Working directory of the conversation, when derivable from messages.
    -- Same purpose as energy_observations.session_dir.
    session_dir       TEXT,
    avg_power_watts   REAL,
    duration_seconds  REAL,
    energy_kwh        REAL,
    cost_usd          REAL,
    carbon_g_co2eq    REAL,
    meter             TEXT,
    observed_at       TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_local_energy_model ON local_energy_observations (model_id);
CREATE INDEX IF NOT EXISTS idx_local_energy_request ON local_energy_observations (request_id);

-- Account-level balance observations per dispatch provider (separate from
-- energy_observations because this is a prepaid-account pool, not a
-- per-completion telemetry measure).
CREATE TABLE IF NOT EXISTS provider_balance_observations (
    id                INTEGER PRIMARY KEY AUTOINCREMENT,
    provider          TEXT NOT NULL,
    balance_usd       REAL NOT NULL,
    total_credits_usd REAL,
    total_usage_usd   REAL,
    observed_at       TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_provider_balance ON provider_balance_observations (provider, observed_at);

-- NOTE: OpenRouter seed data lives in config/allowlist-openrouter.sql

-- Provider-model allowlist: a gate that restricts routing to an explicit
-- (provider, model_id) whitelist. When configured, only rows present here
-- survive the hard filters; an empty or absent allowlist is ignored.
CREATE TABLE IF NOT EXISTS provider_model_allowlist (
    provider    TEXT NOT NULL,
    model_id    TEXT NOT NULL,
    added_at    TEXT NOT NULL,
    note        TEXT,
    PRIMARY KEY (provider, model_id)
);
CREATE INDEX IF NOT EXISTS idx_allowlist_provider ON provider_model_allowlist (provider);