6krrt/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' (only provider today)
    -- 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'

    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,
    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
    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'
    task_category            TEXT,
    task_tier                INTEGER,                -- 1-3
    required_context_tokens  INTEGER,
    confidence               REAL,
    classifier_ms            INTEGER,
    classification_source    TEXT,                   -- 'classifier' | 'override' | 'fallback'
    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. 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
);

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