g1t/services/billing/migrations/0048_model_catalogue.sql
| 1 | -- One model catalogue: `gateway_models` becomes every model g1t can use, |
| 2 | -- the agents' tiers, the AI Gateway's and the embeddings model, with what |
| 3 | -- g1t knows about each and where it stands. See src/catalogue.rs. |
| 4 | -- |
| 5 | -- New models are found by the models service listing each provider daily |
| 6 | -- (`record_discovery`) and wait as `new` until staff approve them in sudo, |
| 7 | -- Agents & models. Only `available` models are routed to; the AI Gateway |
| 8 | -- offers `available` and `deprecated` ones that are priced. |
| 9 | |
| 10 | -- Other ids the provider lists it by, comma separated (a dated id). |
| 11 | ALTER TABLE gateway_models ADD COLUMN aliases TEXT NOT NULL DEFAULT ''; |
| 12 | -- `haiku`, `sonnet`, `opus`, `fable`, or a Workers AI author. |
| 13 | ALTER TABLE gateway_models ADD COLUMN family TEXT NOT NULL DEFAULT ''; |
| 14 | -- The agent tier it suits: `small`, `large`, `frontier`, or empty. |
| 15 | ALTER TABLE gateway_models ADD COLUMN tier_hint TEXT NOT NULL DEFAULT ''; |
| 16 | ALTER TABLE gateway_models ADD COLUMN context_window INTEGER NOT NULL DEFAULT 0; |
| 17 | ALTER TABLE gateway_models ADD COLUMN max_output INTEGER NOT NULL DEFAULT 0; |
| 18 | -- Comma separated: effort, thinking, tools, vision, embeddings. |
| 19 | ALTER TABLE gateway_models ADD COLUMN capabilities TEXT NOT NULL DEFAULT ''; |
| 20 | -- An embeddings model's vector length. |
| 21 | ALTER TABLE gateway_models ADD COLUMN dimensions INTEGER NOT NULL DEFAULT 0; |
| 22 | -- `available`, `new` (found, not approved), `deprecated` (the provider |
| 23 | -- stopped listing it) or `retired` (staff took it out). |
| 24 | ALTER TABLE gateway_models ADD COLUMN status TEXT NOT NULL DEFAULT 'available'; |
| 25 | -- 1 when its prices are known. An unpriced model is never routed to, |
| 26 | -- offered or charged for. |
| 27 | ALTER TABLE gateway_models ADD COLUMN priced INTEGER NOT NULL DEFAULT 1; |
| 28 | -- `discovered` or `staff`. |
| 29 | ALTER TABLE gateway_models ADD COLUMN source TEXT NOT NULL DEFAULT 'staff'; |
| 30 | ALTER TABLE gateway_models ADD COLUMN first_seen_at TEXT; |
| 31 | ALTER TABLE gateway_models ADD COLUMN last_seen_at TEXT; |
| 32 | ALTER TABLE gateway_models ADD COLUMN missing_since TEXT; |
| 33 | ALTER TABLE gateway_models ADD COLUMN approved_by TEXT; |
| 34 | ALTER TABLE gateway_models ADD COLUMN approved_at TEXT; |
| 35 | ALTER TABLE gateway_models ADD COLUMN note TEXT NOT NULL DEFAULT ''; |
| 36 | |
| 37 | UPDATE gateway_models SET first_seen_at = updated_at, approved_at = updated_at, approved_by = 'migration'; |
| 38 | |
| 39 | -- What g1t knows about the models it had. |
| 40 | UPDATE gateway_models SET family = 'opus', tier_hint = 'frontier', context_window = 1000000, max_output = 128000, |
| 41 | capabilities = 'effort,thinking,tools,vision' WHERE model = 'claude-opus-5-5'; |
| 42 | UPDATE gateway_models SET family = 'sonnet', tier_hint = 'large', context_window = 1000000, max_output = 128000, |
| 43 | capabilities = 'effort,thinking,tools,vision' WHERE model = 'claude-sonnet-5-5'; |
| 44 | UPDATE gateway_models SET family = 'haiku', tier_hint = 'small', |
| 45 | capabilities = 'effort,thinking,tools,vision' WHERE model = 'claude-haiku-5-5'; |
| 46 | -- Haiku 4.5 takes no effort level. |
| 47 | UPDATE gateway_models SET family = 'haiku', tier_hint = 'small', context_window = 200000, max_output = 64000, |
| 48 | capabilities = 'thinking,tools,vision' WHERE model IN ('claude-haiku-4-5', 'claude-haiku-4-5-20251001'); |
| 49 | UPDATE gateway_models SET aliases = 'claude-haiku-4-5-20251001' WHERE model = 'claude-haiku-4-5'; |
| 50 | UPDATE gateway_models SET family = substr(model, 5, instr(substr(model, 5), '/') - 1) WHERE provider = 'workers-ai'; |
| 51 | UPDATE gateway_models SET capabilities = 'embeddings', dimensions = 768 WHERE model = '@cf/baai/bge-base-en-v1.5'; |
| 52 | UPDATE gateway_models SET capabilities = 'embeddings', dimensions = 1024 WHERE model = '@cf/baai/bge-m3'; |
| 53 | |
| 54 | -- Which model each purpose uses by default, chosen by staff in sudo |
| 55 | -- (`admin_set_model_default`; every change in the audit log with the old |
| 56 | -- and new value and why). A model purpose names a model; a job |
| 57 | -- (`job_<kind>`) its starting tier (`small`, `large`, `frontier`, or |
| 58 | -- `change` to size the change it reads) and effort. Read by the runner and |
| 59 | -- the AI Gateway; the runner's AGENT_ROUTING is only the fallback when |
| 60 | -- billing cannot be reached. Seeded with what AGENT_ROUTING said. |
| 61 | CREATE TABLE IF NOT EXISTS model_defaults ( |
| 62 | purpose TEXT PRIMARY KEY, |
| 63 | model TEXT, |
| 64 | tier TEXT, |
| 65 | effort TEXT, |
| 66 | updated_at TEXT NOT NULL, |
| 67 | updated_by TEXT NOT NULL, |
| 68 | reason TEXT NOT NULL DEFAULT '' |
| 69 | ); |
| 70 | |
| 71 | INSERT OR IGNORE INTO model_defaults (purpose, model, tier, effort, updated_at, updated_by, reason) VALUES |
| 72 | ('tier_small', 'claude-haiku-5-5', NULL, NULL, '2026-10-08T00:00:00Z', 'migration', 'As AGENT_ROUTING had it'), |
| 73 | ('tier_large', 'claude-sonnet-5-5', NULL, NULL, '2026-10-08T00:00:00Z', 'migration', 'As AGENT_ROUTING had it'), |
| 74 | ('tier_frontier', 'claude-opus-5-5', NULL, NULL, '2026-10-08T00:00:00Z', 'migration', 'As AGENT_ROUTING had it'), |
| 75 | ('background', 'claude-haiku-5-5', NULL, NULL, '2026-10-08T00:00:00Z', 'migration', 'The fast tier''s model, as before'), |
| 76 | ('gateway_first', 'claude-haiku-5-5', NULL, NULL, '2026-10-08T00:00:00Z', 'migration', 'The cheapest Claude, listed first since 0047'), |
| 77 | ('job_implement', NULL, 'large', NULL, '2026-10-08T00:00:00Z', 'migration', 'As AGENT_ROUTING had it'), |
| 78 | ('job_revise', NULL, 'large', NULL, '2026-10-08T00:00:00Z', 'migration', 'As AGENT_ROUTING had it'), |
| 79 | ('job_answer', NULL, 'small', 'medium', '2026-10-08T00:00:00Z', 'migration', 'As AGENT_ROUTING had it'), |
| 80 | ('job_review', NULL, 'change', NULL, '2026-10-08T00:00:00Z', 'migration', 'As AGENT_ROUTING had it'), |
| 81 | ('job_update', NULL, 'small', 'low', '2026-10-08T00:00:00Z', 'migration', 'As AGENT_ROUTING had it'), |
| 82 | ('job_plan', NULL, 'small', 'high', '2026-10-08T00:00:00Z', 'migration', 'As AGENT_ROUTING had it'); |
| 83 | |
| 84 | -- Every check of a provider's list, daily or from sudo: what it listed, |
| 85 | -- added and found gone, or why it failed. Kept 90 days. |
| 86 | CREATE TABLE IF NOT EXISTS model_checks ( |
| 87 | id TEXT PRIMARY KEY, |
| 88 | provider TEXT NOT NULL, |
| 89 | checked_at TEXT NOT NULL, |
| 90 | by TEXT NOT NULL, |
| 91 | listed INTEGER NOT NULL DEFAULT 0, |
| 92 | added TEXT NOT NULL DEFAULT '', |
| 93 | deprecated TEXT NOT NULL DEFAULT '', |
| 94 | error TEXT |
| 95 | ); |
| 96 | CREATE INDEX IF NOT EXISTS model_checks_by_time ON model_checks (checked_at); |