Migration: JSON-driven database → derived SQLite read layer for faster UI & SDK queries

[DiTo97]

Summary

Today the canonical pricing data is git-committed JSON served as static files over the GitHub raw CDN (docs/database.md: "there is no separate backend"). Every consumer materializes the entire dataset to answer one question, which makes the dashboard and SDKs slower than they need to be:

• Dashboard (services/dashboard/src/lib/data.ts) fetches the full ~2.9 MB prices.json on mount, parses it, and explorer.tsx filters the 3,244-row array on every keystroke (O(n) substring scan, no index). History charts fetch up to 12 × ~2.8 MB snapshots via the GitHub contents API.
• SDKs (libraries/python, libraries/typescript) parse the full 2.9 MB blob on cold start (~100–200 ms), cache 6 h, then run search_models as an O(n) linear scan.
• Sync re-parses the full prior 2.9 MB snapshot to diff, and database/history/ is ~115 MB (≤42 snapshots, pruned in CI).

The row count is small (3,244); the cost is payload + parse, not the pricing math (already trivial multiplication).

Decision (ADR 0001)

Keep JSON as the canonical, git-committed, diff-reviewable source of truth, and add a derived SQLite artifact (prices.db) built by the sync pipeline and published as a CI artifact (GitHub Pages + Releases) — not committed to git. Consumers gain a fast SQLite read path (HTTP range requests via sql.js-httpvfs, indexes, FTS5) with the JSON path retained as fallback.

📄 Full rationale, alternatives, and proposed v1 schema: wiki/decisions/0001-canonical-pricing-database-storage.md (draft PR below).

Why this is "way faster"

• Partial reads — sql.js-httpvfs + HTTP Range fetch only the DB pages a query touches (tens of KB) instead of the whole 2.9 MB file.
• Indexes / FTS5 — per-keystroke O(n) filter and search_models scan become index-backed.
• No cold-start parse — SDKs that ship prices.db skip the full-JSON parse; SQLite memory-maps the file.
• History as a time series — one price_history table replaces ~115 MB of snapshot files; charts run one indexed query.

Phased rollout

Phase 0 — Build & publish prices.db (non-breaking) 🔴 blocks the rest

• Add a build-db step to services/sync that materializes JSON → SQLite using the proposed v1 schema (models, model_sources, providers, price_history, models_fts, meta + PRAGMA user_version).
• VACUUM + ANALYZE the DB; emit a checksum.
• Verify HTTP Range support on GitHub Pages and raw.githubusercontent.com (spike). If unreliable, fall back to single-.db-download + in-browser query.
• Publish prices.db to GitHub Pages (alongside the dashboard) and attach to a Release in database-sync.yml. Do not commit the binary.
• JSON↔DB equivalence test in services/sync/tests.
• Decide price storage: REAL (matches current float semantics) vs TEXT decimals.

Phase 1 — Dashboard reads SQLite (JSON fallback)

• Wire sql.js-httpvfs into the dashboard; query models_fts for search and indexed columns for filters.
• Keep loadPricingData() JSON path as fallback when the DB / range requests are unavailable or user_version mismatches.
• Measure cold-load payload and keystroke latency before/after.

Phase 2 — Optional SQLite backend in the SDKs

• Python: sqlite3 (stdlib) read path; optional bundled prices.db or download-and-cache. Keep HTTP-JSON default for ≥1 release.
• TypeScript: better-sqlite3 (Node) / sql.js (browser) read path; same fallback policy.
• search_models and lookups go through indexes/FTS; preserve public API and currency-conversion behavior unchanged.

Phase 3 — History & sync diffing on the DB

• Move dashboard price-history charts to a single price_history query.
• Compute sync changelog via SQL against the prior snapshot rows instead of re-parsing full prior JSON.
• Evaluate shrinking/retiring database/history/*.json once the DB carries history.

Phase 4 — Revisit JSON retention (follow-up decision record)

• Only after Phases 0–3 ship: decide whether JSON stays canonical forever (current recommendation) or is retired in favor of SQLite, in a new decision record under wiki/decisions/. Requires a reviewability answer (e.g. a JSON-export diff bot) before any retirement.

Acceptance criteria

• Dashboard cold-load payload drops from ~2.9 MB to tens of KB; search/filter is index-backed.
• SDK filtered queries are index-backed; existing public APIs and JSON URLs keep working.
• prices.db is reproducible from JSON in CI and validated against it every sync.
• No binary committed to git history.

Open questions

1. REAL vs TEXT-decimal price storage (Phase 0).
2. Is HTTP Range reliable enough on the chosen origin for sql.js-httpvfs, or do we ship a whole-file .db per load?
3. Publish target — GitHub Pages, Releases, or both?
4. Long-term: keep JSON canonical (recommended) or eventually retire it (Phase 4 decision record)?

Tracked by decision record wiki/decisions/0001-canonical-pricing-database-storage.md. Draft PR introduces it and the index.