feat(www): GH Pages site + SQLite-over-HTTP for VID:PID <-> board fuzzy match

[zackees]

Goal

Host fbuild's USB / board database as a queryable static site so a user (or fbuild itself) can answer: "I have an ESP32-S3 — what's the most likely USB VID:PID I'll see when it enumerates?" — on the first shot, in the browser, with zero server.

This extends the existing online-data orphan-branch + nightly-refresh pattern with a sister www orphan branch that serves a SQLite-over-HTTP database via sql.js (WASM).

Outcomes

1. A new orphan branch www is the GH Pages source. Updated once a day by the nightly job, same cadence as online-data.
2. The site hosts two day-versioned SQLite databases: <YYYY-MM-DD-prev>.db and <YYYY-MM-DD-curr>.db. Live traffic points at <curr>; the rotation gives a >24 h overlap so a client that fetches the URL at midnight doesn't catch a half-written file.
3. The site lets a human run a small set of pre-canned SQL queries (no free-form SQL injection surface) that fuzzy-match a board name → ranked VID:PIDs and vice versa.
4. online-data/manifest.json advertises the website URL + the curr/prev DB URLs with explicit format: "sqlite-over-http", engine: "sql.js", and payload: "wasm" annotations so fbuild clients can discover them.

Why SQLite over HTTP

• Drop-in for raw JSON: still a static asset, still cacheable, still on GH Pages — no server runtime cost.
• Lets the client run real JOINs (USB vendor × MCU vendor × board) and FTS5 fuzzy search without redownloading the entire 1942-vendor / 1553-board JSON corpus per query.
• sql.js fetches the file once, then queries locally — same idea as Datasette-Lite.

Branch / file layout

www/ (orphan, GH Pages source)
├── index.html # query UI (search box, canned query buttons, results table)
├── app.js # sql.js loader + fuzzy ranker
├── sql-wasm.js / .wasm # vendored sql.js (pinned version, integrity-checked)
├── 2026-06-19.db # previous day (kept for 24 h grace window)
├── 2026-06-20.db # current day (live traffic)
└── manifest.json # mirrors online-data, plus { current_db, previous_db, generated_at }

online-data/ (existing, unchanged shape; manifest gains www links)
├── data/usb-vid.json
├── data/pio-boards.json
├── data/vendor_boards.json
├── data/mcu_to_vid.json # NEW — heuristic MCU-family → likely VID(s)
└── manifest.json

The missing edge: MCU ↔ VID

Today none of the three JSON files contain a direct VID:PID ↔ board edge. The match has to flow through MCU-family heuristics. I'll commit a curated data/mcu_to_vid.json on online-data that maps each MCU family (or MCU prefix) to its likely USB VIDs, scored. Seed list:

MCU family	Likely VID(s)	Notes
ESP32 / S2 / S3 / C3 / C6	0x303a (Espressif native USB)	Plus bridge chips below
ESP32 (older, via UART bridge)	0x10c4 (Silicon Labs CP210x), 0x1a86 (QinHeng CH340), 0x0403 (FTDI)	When dev board uses a bridge
STM32	0x0483 (STMicro)	Includes ST-Link 0483:374b
RP2040 / RP2350	0x2e8a (Raspberry Pi)
Teensy 3/4/LC	0x16c0 (PJRC)
ATSAMD / ATmega32u4 / SAMD21	0x2341 (Arduino), 0x239a (Adafruit), 0x1b4f (SparkFun)	Vendor varies by board lineage
nRF52	0x239a (Adafruit Bluefruit), 0x1915 (Nordic)
CH32V / CH32X	0x1a86 (WCH / QinHeng)	Often via WCH-LinkE
Apollo3	0x1cbe (Sparkfun Apollo3)
LPC8xx	0x1fc9 (NXP), 0x0d28 (CMSIS-DAP)	DAPLink-based dev kits

Each entry has a score so SQLite's ranker can rank the most-likely match first.

SQLite schema

-- Authoritative tables (rebuilt from JSON every nightly run)
CREATE TABLE usb_vendor (
 vid INTEGER PRIMARY KEY, -- stored as INT, presented as %04x
 vendor TEXT NOT NULL
);
CREATE TABLE usb_product (
 vid INTEGER NOT NULL,
 pid INTEGER NOT NULL,
 product TEXT NOT NULL,
 PRIMARY KEY (vid, pid)
);
CREATE TABLE board (
 id TEXT PRIMARY KEY, -- e.g. "esp32-s3-devkitc-1"
 name TEXT NOT NULL,
 vendor TEXT,
 mcu TEXT,
 platform TEXT,
 framework TEXT,
 url TEXT
);
CREATE TABLE mcu_to_vid (
 mcu_family TEXT NOT NULL,
 vid INTEGER NOT NULL,
 score REAL NOT NULL,
 reason TEXT,
 PRIMARY KEY (mcu_family, vid)
);

-- Search helpers
CREATE VIRTUAL TABLE board_fts USING fts5(id, name, vendor, mcu, content='board');
CREATE VIRTUAL TABLE usb_fts USING fts5(vendor, product, content='');

-- Convenience view used by the canned queries
CREATE VIEW board_vid_guess AS
SELECT b.id AS board_id,
 b.name AS board_name,
 b.mcu AS mcu,
 m.vid AS vid,
 v.vendor AS usb_vendor,
 m.score AS confidence,
 m.reason AS reason
FROM board b
JOIN mcu_to_vid m ON m.mcu_family = b.mcu OR b.mcu LIKE m.mcu_family || '%'
JOIN usb_vendor v ON v.vid = m.vid;

Pre-canned queries (the UI's only SQL surface)

The UI exposes a fixed dropdown of parameterized queries — no free-form SQL box.

1. "What VID:PID is my ?" — uses board_fts MATCH ? against board_vid_guess, ordered by confidence DESC, LIMIT 20.
2. "What board is this VID:PID?" — joins usb_product × mcu_to_vid × board and ranks by score.
3. "List boards by MCU family" — WHERE mcu = ? ordered by vendor, name.
4. "All products under VID" — WHERE vid = ?.
5. "Vendor name search" — WHERE vendor LIKE ? (escaped client-side).

Each binds user input as parameters, never string-concatenated.

Nightly workflow changes (.github/workflows/nightly-usb-ids.yml)

Add a second worktree for www next to the existing online-data worktree, then after the merger succeeds:

1. tools/build_sqlite.py (committed to online-data alongside the existing mergers) reads the freshly merged JSON and writes <WWW_WORKTREE>/$(date -u +%Y-%m-%d).db.
2. Rotation step: keep only today (curr) + yesterday (prev); delete older .db files.
3. Update www/manifest.json with current_db / previous_db filenames + generated_at.
4. Commit + force-with-lease push to www (history pruned to last 200 commits — same policy as online-data).

Both branches are orphan, both get --force-with-lease push, both retain 200-commit history. The static site assets (index.html, app.js, sql-wasm.*) live as committed sources on www and are NOT regenerated by the workflow.

CI-burn audit (preflight — done in this PR)

Verified all push: triggers across the 90+ workflow files on main already restrict to branches: [main]. online-data (orphan) has no .github/workflows/ so pushes don't fire anything. The new www orphan will likewise carry zero workflows — pushes there will not fire CI. No regression risk from the new branch.

Minor preflight hygiene: a handful of PR-only workflows (crate-gate.yml, loc-gate.yml, acceptance-205.yml, bench-205.yml) have pull_request: with no branches: filter. They only fire on intentional PRs so blast radius is bounded; tightening to branches: [main] is optional polish, included here for symmetry.

TDD acceptance criteria

• tests/unit/online_data/test_build_sqlite.py builds a DB from sample JSON and asserts every JSON row round-trips into the expected table, plus a known canned query returns a known top-1 result (esp32-s3 → 0x303a).
• tests/unit/online_data/test_manifest_links.py asserts online-data/manifest.json after a build run contains website, current_db, and previous_db entries with the correct annotations.
• Workflow dry-run produces both branch worktrees and the rotation deletes >2-day-old DBs.

Verification plan

1. Land all changes via a single PR against main.
2. After merge, run Actions → Nightly USB IDs refresh → Run workflow manually.
3. Confirm online-data carries the new mcu_to_vid.json + updated manifest; confirm www carries index.html, <today>.db, <yesterday>.db (or just today on first run), and manifest.
4. Browse the published GH Pages URL, run the canned "what VID:PID is my esp32-s3?" query, confirm 0x303a ranks top.
5. Close this issue.

Scope NOT included (deferred)

• Rust-side fbuild client integration that reads the SQLite DB instead of the existing JSON (separate issue, follows once this is live).
• Adding mcu_to_vid curation entries beyond the seed table above (community PRs welcome on online-data).
• Free-form SQL UI (deliberately omitted; canned queries only — see security note above).

[zackees]

Update: canned query #2 ("VID + PID → top-10 boards ranked")

Per follow-up: the second canned query takes two input fields (VID, PID) and returns the top 10 candidate boards, ranked by score. Concretely:

-- Bound to ?1 = vid (int), ?2 = pid (int)
SELECT
 b.id AS board_id,
 b.name AS board_name,
 b.vendor AS board_vendor,
 b.mcu AS mcu,
 v.vendor AS usb_vendor,
 p.product AS usb_product,
 (
 -- Base score from the heuristic MCU→VID table
 m.score
 -- Bonus when the exact PID is also a product under this VID
 + CASE WHEN p.pid IS NOT NULL THEN 0.25 ELSE 0.0 END
 -- Bonus when the board's vendor name overlaps the USB vendor name
 + CASE WHEN LOWER(b.vendor) = LOWER(v.vendor) THEN 0.10 ELSE 0.0 END
 ) AS score
FROM mcu_to_vid m
JOIN usb_vendor v
 ON v.vid = m.vid
LEFT JOIN usb_product p
 ON p.vid = m.vid AND p.pid = ?2
JOIN board b
 ON b.mcu = m.mcu_family OR b.mcu LIKE m.mcu_family || '%'
WHERE m.vid = ?1
ORDER BY score DESC
LIMIT 10;

UI: two <input type="text" pattern="[0-9a-fA-F]{4}"> fields (VID, PID), one Search button, results in a sortable table with columns board_id | board_name | mcu | usb_vendor | usb_product | score.

Edge cases the test suite must cover:

• VID present, PID unknown → still returns ranked boards via mcu_to_vid (LEFT JOIN keeps the row, p.pid IS NULL → no bonus).
• VID/PID both totally unknown → empty result set, UI shows "no match" hint.
• Multiple MCU families map to the same VID (e.g. 0x303a for all ESP32 variants) → results are de-duped by (board_id) keeping the highest score.

This becomes the primary canned query (the headline use case: "I plugged in a board, what is it?"); the board-name → VID:PID direction is secondary.