Catalog data provenance & source reconciliation

Where NanaSelect's product knowledge comes from, how the sources are reconciled into one catalog, and how to refresh it. This is the reconciliation record for #261 (populate product attributes and publish a demonstration track set).

Sources

Source	What it contributes	Authority
data/Product_Comperison_Matrix_v1.csv	The 23 systems, ~86 comparison attributes, per-system values, per-attribute buyer descriptions, enum option definitions	Primary bulk source for attribute values
data/Product_Factors.csv	The 15 selection factors, their discrete options, applicability notes	Primary source for the selection side
Drupal products taxonomy (www.nanawall.com JSON:API)	Term identity (tid/uuid) and the 9 field-mapped attributes (see drupal_attribute_mappings)	Authoritative for the fields it maps, when synced
Admin edits (/admin)	Corrections and curation by NanaWall staff	Highest — a manual edit overrides everything

System count discrepancy: issue #261 describes the matrix as "~133 feature rows × 24 systems". The delivered CSV's header carries 23 system columns (NW Clad 740 … cero III). No 24th system exists in any source, so the catalog holds 23 — recorded here rather than inventing one.

Encoding: the matrix is a Windows-1252 Excel export (not UTF-8). The ingest decodes it as windows-1252 (readCsv in src/lib/server/ingest.ts); reading it as UTF-8 corrupts en-dashes, ², ° and the Support Type row.

Pipeline & ownership

CSVs ──ingest (canonicalize + type)──▶ seed/seed.sql ──▶ local / remote D1
                                          seed/sql/*.sql fragments (numbered, idempotent)
Drupal JSON:API ──sync (#108, admin-triggered)──▶ mapped attribute values (source='drupal')
Admin UI ──▶ any value (source='manual')

• npm run seed:build regenerates seed/seed.sql from the CSVs (src/lib/server/ingest.ts). Every enum cell passes through the canonicalization alias table (ENUM_LABEL_ALIASES in src/lib/server/values.ts) so spreadsheet variants ("Flush Sill", "and No Floor Track (sockets).", "Residentail") converge on one canonical value. The original cell is always preserved verbatim in system_attribute_values.raw.
• The numbered fragments in seed/sql/ layer curated data the CSVs don't carry: Drupal mappings (030), enum catalog backfill (060), enum value education copy (065, sourced from the matrix's "Definition of options" column), tracks (080), and lifecycle coverage.
• Re-seeding is destructive to derived rows: seed.sql's INSERT OR REPLACE INTO attributes cascades the enum catalog away and resets system_attribute_values.source to manual. The fragments restore the curated layer, so local convergence = seed.sql + all fragments (what dev-up runs), never seed.sql alone.

Precedence (conflict rule)

When two sources disagree about a value, the catalog keeps, in order:

1. Admin manual edit — a row whose content differs from its drupal_raw snapshot is a deliberate override (#109); syncs and re-seeds must not be run in a way that silently clobbers it (see refresh procedure).
2. Drupal sync (source='drupal') — authoritative for the 9 mapped fields, because the taxonomy is the live marketing site's data.
3. Comparison matrix CSV — the bulk source for everything else.
4. Factors sheet — selection-side only (factors/options); it never writes attribute values.

A conflict that needs an editorial call (not covered by this ordering) is recorded as a decision-labelled GitHub issue rather than resolved silently.

The "unknown" convention (no silent gaps)

A missing source value is stored as an explicit typed state, never a missing row, for every attribute × enabled system a published surface depends on:

Stored state	Meaning
empty	No source has a value — unknown
na	Source says not applicable to this system
none	Source says the feature is not offered
untested	Source says explicitly not tested

formatValue renders these distinctly, and the recommendation engine treats a system lacking a present value as failing an attribute condition (it is never recommended on the strength of an unknown).

Factor → attribute reconciliation

The selection factors (Product_Factors.csv) discriminate through these catalog attributes (matrix column → attributes.id). This mapping is the authoring basis for factor_option_attributes and the right-sizing rules; "(sheet)" marks links stated in the factors sheet's "Applicable Product Features" / dependency columns, "(derived)" marks links derived from the matrix semantics.

Factor (factors.id)	Discriminating attributes
interior-exterior	interior-vs-exterior-suitability (derived)
geography	Coastal: miami-dade-hurricane-compliance, kynar-finishes-available, missile-impact-cycling · Mountain: suitable-for-high-altitudes, windloads · Urban: acoustic-rating-range, oitc-sound-rating (derived)
climate	thermal-performance-u-value, unit-u-value-range, thermally-broken-aluminum-insulation, energy-star-qualification-possible (sheet: "Performance")
residential-commercial	ideal-for-x-applications (derived)
ada-compliance-needed	ada-options-available, minimum-sill-depth, sill-options (sheet: "Commercial")
interior-acoustic-control-needed	acoustic-rating-stc-oitc, acoustic-rating-range (sheet: "Commercial, Interior")
exterior-acoustic-control	oitc-sound-rating, acoustic-rating-range (sheet: "Residential")
durability	unit-cycle-tested, swing-door-cycle-tested-500-000 (sheet: "Commercial" cycle testing)
opening-shape	Straight: — (default) · Open Corner: opern-corner-available · Segmented: segmented-curves-available, t-intersection (derived)
material-finish	materials, aluminum-finish-options, wood-finish-options (derived)
stacking	panel-stacking-options, panels-parking-location (sheet)
panel-sizes	max-panel-width, max-panel-height, maximum-opening-width (derived)
swingdoor	swing-door-integration (sheet)
sill-depth-floor-type	minimum-sill-depth, sill-options, high-heel-resistant, floor-track-required (sheet: "Interiors")
structural-requ	structural-load-psf-range, windloads, l-175 (derived)

Per-system values for every attribute above come from the matrix (see pipeline); the Drupal sync additionally covers interior-vs-exterior-suitability, glazing-options, sill-options, swing-door-integration, max-panel-width, max-panel-height, maximum-opening-width, handle-options, wall-design.

Refresh procedure

1. Drop the updated CSV(s) into data/ (keep the filenames).
2. npm run seed:build — regenerate seed/seed.sql; review the diff (new enum variants may need an alias in ENUM_LABEL_ALIASES).
3. Local: npm run db:apply:local && npm run seed:local, then re-run the seed/sql fragments (or simply dev-up, which does all of it).
4. Remote: check for admin overrides first (values whose source='manual' post-date the last seed) — re-seeding resets provenance columns. Then npm run db:apply:remote && npm run seed:remote and re-run the fragments against --remote.
5. Drupal: run the sync from /admin/drupal-sync (preview, then apply) to re-overlay the mapped fields and resolve term ids.