-- 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 the hashed session fingerprint in `session_key`, or -- `"c:" + conversation id` when the client sent X-Router-Conversation; never -- `session_dir` and never any prompt or answer text. A test enforces that -- the headerless write path stores the 16-char 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, or -- 'c:' + conversation id when the -- client sent X-Router-Conversation; -- 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. agent TEXT, -- client agent name from -- X-Router-Agent (slugged by the -- plugin, e.g. atlas-plan-executor); -- NULL when the client sends none parent_key TEXT, -- 'c:' plus the parent conversation -- id from X-Router-Parent, set on a -- sub-agent's rows; NULL for -- top-level conversations or when -- unknown -- What the PRIMARY classifier did with this request. `confidence` above is -- the confidence of the classification that ROUTED it, and on a chat row -- that is almost always 1.0 because the chat path re-routes through the -- override branch, which hard-codes it. These three are the classifier's -- own numbers, read before that re-route. NULL on rows from before they -- existed, on override rows and on session-cache hits (no attempt made). classifier_confidence REAL, -- the classifier's confidence in -- its answer, accepted or not classifier_coverage REAL, -- local_decision only: total -- option mass behind that answer classifier_reject TEXT -- NULL when the answer was used, -- else why not: below_confidence_min, -- below_coverage_min, no_logprobs, -- timeout, transport_error, -- parse_error, primary_failed, -- skipped_. classification_source -- then says what routed instead. ); 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, -- 'c:' when the client sent X-Router-Conversation, else -- the content fingerprint, same as route_decisions.session_key -- lets -- local-dispatch answers be attributed to the same conversation as -- cloud-routed ones. session_key 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); -- Watchdog monitoring tables. See src/watchdog_store.py for the code-side -- inline-create that mirrors this schema for live databases. CREATE TABLE IF NOT EXISTS watchdog_ticks ( id INTEGER PRIMARY KEY AUTOINCREMENT, ticked_at TEXT NOT NULL, sessions_seen INTEGER NOT NULL DEFAULT 0, outcome TEXT ); CREATE TABLE IF NOT EXISTS watchdog_verdicts ( id INTEGER PRIMARY KEY AUTOINCREMENT, tick_id INTEGER NOT NULL REFERENCES watchdog_ticks(id), session_id TEXT NOT NULL, session_root TEXT, agent TEXT, model_id TEXT, provider TEXT, flagged INTEGER NOT NULL DEFAULT 0, dup REAL, 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 ); CREATE TABLE IF NOT EXISTS watchdog_alerts ( dedup_key TEXT PRIMARY KEY, state TEXT NOT NULL, severity TEXT NOT NULL, flagged_ticks INTEGER NOT NULL DEFAULT 0, opened_at TEXT NOT NULL, last_fired_at TEXT NOT NULL, resolved_at TEXT ); CREATE TABLE IF NOT EXISTS watchdog_channel_settings ( id INTEGER PRIMARY KEY AUTOINCREMENT, channel_name TEXT NOT NULL UNIQUE, enabled INTEGER NOT NULL DEFAULT 1, min_severity TEXT NOT NULL DEFAULT 'warning' ); CREATE INDEX IF NOT EXISTS idx_watchdog_verdicts_model_id ON watchdog_verdicts(model_id); CREATE INDEX IF NOT EXISTS idx_watchdog_verdicts_created_at ON watchdog_verdicts(created_at); CREATE INDEX IF NOT EXISTS idx_watchdog_verdicts_flagged ON watchdog_verdicts(flagged); CREATE INDEX IF NOT EXISTS idx_watchdog_verdicts_session_root ON watchdog_verdicts(session_root);