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

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:

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