build-sqlite.ts has no tests — nothing checks the artifact that ships to Studio

[jeffreylouden]

scripts/build-sqlite.ts is 1,432 lines with zero tests. It produces dist/catalog.sqlite, which release.yml hashes, signs with minisign, attests with Sigstore, and publishes to GitHub Releases — where Studio downloads it as its read-only catalog database.

Everything downstream of the build verifies that the file arrived intact. Nothing verifies it was built correctly. A sha256 and a minisig over a database with a silently-empty FTS table are a perfect chain of custody for bad data.

Why it is untestable today, not just untested

$ grep -c '^export' scripts/build-sqlite.ts
0
$ grep -n 'import.meta.url\|require.main' scripts/build-sqlite.ts
(nothing)

Same shape as #645: no exports, and the tail is buildDatabase(CATALOG_VERSION) at module scope, so importing the file builds the real database as a side effect.

buildDatabase also can't be redirected. It closes over module-level constants and writes to a fixed path (114–115):

const SCHEMA_FILE = path.join(import.meta.dirname, "schema.sql");
const OUTPUT_FILE = path.join(OUTPUT_DIR, "catalog.sqlite");

so even with the guard added there is no way to point it at a fixture tree or at :memory:.

Suggested shape

1. Main-guard the tail (build-catalog-index.ts:332 has the in-repo pattern).
2. Give buildDatabase an options object — { dataDir, outputFile, version } defaulting to today's constants — so a test can build from scripts/__tests__/fixtures/ into a tmpdir or :memory:.
3. Export the pure normalizers, which need nothing at all to test:

Function	Line
markdownToHtml	42
normalizeCategory	77
normalizeIOPosition	94
normalizeIOConnection	99
normalizeIOSignalFlow	104

normalizeIOSignalFlow collapses bidirectional → input for Studio compatibility. That is a behavioural contract with another repo expressed as a one-line ternary with no test on either side of it.

What a fixture build should assert

Once (2) lands, a handful of YAML files through a real build gives a lot of coverage for very little test code, because scripts/schema.sql can create the whole thing in memory:

• Row counts per collection — software / content / hardware / accessories, plus the child tables (categories, search terms, formats, platforms, versions, prices, links, videos).
• The FTS tables are populated. software_fts, content_fts, hardware_fts, manufacturers_fts are populated by separate INSERT statements (172, 314, 590, …) from the base-table inserts. If one of those is skipped or its column order drifts, the build still succeeds, the file still hashes, and search in Studio just quietly returns nothing for that collection. This is the single highest-value assertion in the list.
• INVERSE_CATEGORY_ALIASES → search terms (61–70). Alias data is expanded into the FTS search-term column, so an entry categorized equalizer should come back for eq. Worth one end-to-end assertion, since the whole point of that block is recall a user notices only by its absence.
• Compatibility edges resolve — software_compatibility, content_compatibility, content_hardware_compatibility (330, 565, 569) turn slugs into IDs. A slug that doesn't resolve should be visible in the test, not inferred from a missing row.
• supersedes lineage survives the build, in both directions, since the DB is queried both ways.
• Translations and localized links land under the right locale (software_translations, software_links_localized, software_videos_localized).

Scope note

Characterization first, same as #645 — encode current output, change nothing. Splitting the fixture-build work (2) into its own PR from the pure-normalizer tests (3) would keep both reviewable; (3) is small enough to land on its own immediately.