Surfaced while adding hardware_capabilities in #653. None of this is a live outage: pnpm patch appears in no workflow and is referenced nowhere outside package.json, so the distribution path is the full pnpm build in CI and nothing published goes stale by this route today. It is a trap for whoever eventually wires it up, which is exactly when it stops being cheap to fix.
generate-patch.ts presents itself as an incremental equivalent of build-sqlite.ts, but it only ever handled hardware, hardware_categories and hardware_fts. Three distinct defects follow from that, in rough order of how badly they bite.
1. Thirteen child tables are never synchronized
build-sqlite.ts writes these on a full build. generate-patch.ts neither deletes nor re-inserts them, so on a modified entry a patched database silently keeps the old rows:
hardware_io, hardware_prices, hardware_links, hardware_versions, hardware_variants, hardware_variant_links, hardware_variant_prices, hardware_variant_videos, hardware_videos, hardware_search_terms, hardware_translations, hardware_links_localized, hardware_videos_localized, hardware_io_translations
I/O is the one that matters most in practice, since the setup graph reads it and a stale port list is worse than an absent one.
The same question applies to the software, content, accessory and manufacturer generators in that file, which I have not audited.
2. Categories are inserted without normalization or dedup
build-sqlite.ts normalizes category aliases, dedups the canonical values, and warns on Duplicate category "..." (after normalization). generate-patch.ts does neither, so it inserts raw aliases. Two consequences:
- A patched database can hold an alias where a rebuilt one holds the canonical value, so the same query returns different rows depending on how the database was produced.
- Two distinct aliases collapsing to one canonical value is a real case in this data, which is why
build-sqlite.ts warns about it. Through the patch path that becomes a UNIQUE constraint failed on hardware_categories, aborting the whole patch.
Deduping without normalizing is not a fix here, it just changes which of the two failures you get. hardware_capabilities needed dedup only because its vocabulary is closed with no aliases by design.
3. The version stamp is unconditional
// Update version metadata
sql.push(`UPDATE catalog_meta SET value = '${toVersion}' WHERE key = 'version';`);This runs after the change loop regardless of what the loop produced. A change whose category falls through the switch, or whose file fails to parse, contributes no statements while the patch still stamps a new version, so the database claims to be at a version whose content it does not have.
Suggested shape
Deriving the child-table writes from one shared description of hardware, used by both scripts, would be better than adding fourteen more hand-written blocks to a file that has already drifted once per table. If that is too large, the minimum viable fix is (3) first, since a wrong version stamp is what makes the other two hard to diagnose after the fact.
Surfaced while adding
hardware_capabilitiesin #653. None of this is a live outage:pnpm patchappears in no workflow and is referenced nowhere outsidepackage.json, so the distribution path is the fullpnpm buildin CI and nothing published goes stale by this route today. It is a trap for whoever eventually wires it up, which is exactly when it stops being cheap to fix.generate-patch.tspresents itself as an incremental equivalent ofbuild-sqlite.ts, but it only ever handledhardware,hardware_categoriesandhardware_fts. Three distinct defects follow from that, in rough order of how badly they bite.1. Thirteen child tables are never synchronized
build-sqlite.tswrites these on a full build.generate-patch.tsneither deletes nor re-inserts them, so on amodifiedentry a patched database silently keeps the old rows:hardware_io,hardware_prices,hardware_links,hardware_versions,hardware_variants,hardware_variant_links,hardware_variant_prices,hardware_variant_videos,hardware_videos,hardware_search_terms,hardware_translations,hardware_links_localized,hardware_videos_localized,hardware_io_translationsI/O is the one that matters most in practice, since the setup graph reads it and a stale port list is worse than an absent one.
The same question applies to the software, content, accessory and manufacturer generators in that file, which I have not audited.
2. Categories are inserted without normalization or dedup
build-sqlite.tsnormalizes category aliases, dedups the canonical values, and warns onDuplicate category "..." (after normalization).generate-patch.tsdoes neither, so it inserts raw aliases. Two consequences:build-sqlite.tswarns about it. Through the patch path that becomes aUNIQUE constraint failedonhardware_categories, aborting the whole patch.Deduping without normalizing is not a fix here, it just changes which of the two failures you get.
hardware_capabilitiesneeded dedup only because its vocabulary is closed with no aliases by design.3. The version stamp is unconditional
This runs after the change loop regardless of what the loop produced. A change whose
categoryfalls through theswitch, or whose file fails to parse, contributes no statements while the patch still stamps a new version, so the database claims to be at a version whose content it does not have.Suggested shape
Deriving the child-table writes from one shared description of hardware, used by both scripts, would be better than adding fourteen more hand-written blocks to a file that has already drifted once per table. If that is too large, the minimum viable fix is (3) first, since a wrong version stamp is what makes the other two hard to diagnose after the fact.