Data access layer

src/lib/server/queries.ts is the single source of truth for reading the NanaSelect knowledge base. Routes, the guided flow, comparison, and right-sizing all go through it — no other module queries D1 directly. Every read parses the stored typed payloads back into TypedValue (see value typing), so callers receive structured values, not raw strings.

All functions take the Drizzle D1 handle from getDb(platform.env.DB).

Read API

Function	Returns	Use
listSystems(db)	SystemSummary[]	Catalog / picker — all systems with their lifted key columns (operation type, budget tier, material, interior/exterior, use cases, image).
getSystem(db, id)	SystemDetail | null	One system with every attribute value, typed and joined to the attribute catalog.
listFactors(db)	FactorView[]	The guided-flow inputs — each factor with its options.
getComparison(db, systemIds, attributeIds?)	Comparison	Side-by-side matrix: rows are attributes, each with a values map keyed by system id and a differs flag (#78 — see below). Pass attributeIds to limit/order the rows.
getRules(db, opts?)	Rule[]	Right-sizing rules (conditions + rank-ordered outcomes, each joined to the system budget_tier). Defaults to fire-eligible rules (enabled and not retired); pass { includeRetired, includeDisabled } for the admin editor. See right-sizing rules.
healthCounts(db)	{ systems, attributes, factors }	Row counts for the /health probe.

Comparison differs flag (#78)

Each ComparisonRow carries differs: boolean — whether the compared systems do not all share the same typed value for that attribute. It drives the "only show differences" comparison view (#79).

• Equality is over the typed payload via valueKey (values.ts), never the raw/display text: enum label sets compare order- and case-insensitively, ranges by their numeric bounds (label only when both bounds are absent), dimensions by measurement + note, and the presence-like types (untested/na/none/empty) each collapse to their type alone. Different types never compare equal.
• A cell missing for some compared systems keys as empty: absent-vs-present differs; all-absent does not.
• Fewer than two compared systems → differs is always false.
• shapeComparison(rows, attributeIds?, systemIds?) stamps the flag; getComparison passes the resolved system set (an unknown requested id neither appears nor counts as missing). When systemIds is omitted the set is derived from the input rows.

Pure shapers (unit-tested)

The DB functions are thin; the transform logic lives in pure, driver-independent shapers — shapeAttributeValues, shapeFactors, shapeComparison, shapeRules, and parseStored — which are covered directly in queries.spec.ts. This keeps query correctness testable without a live D1 driver.

HTTP endpoints

Thin JSON wrappers over the read API (consumed by the frontend):

• GET /api/systems → { systems: SystemSummary[] }
• GET /api/factors → { factors: FactorView[] }
• GET /health → { ok, counts }

Drupal sync provenance & mapping (#109)

The one-way Drupal→NanaSelect attribute sync (#111) needs two things from the schema: a record of where each value came from, and a map of which Drupal data feeds which NanaSelect entity.

Provenance (on system_attribute_values)

Column	Meaning
source	manual (seeded/CSV-ingested or admin-edited — the default) or drupal (written by the sync engine).
drupal_synced_at	ISO timestamp of the last Drupal sync that touched the row. NULL = never synced.
drupal_raw	The raw Drupal value captured at the last sync. Kept even when an admin overrides the value.

Override semantics: every admin edit stamps source = 'manual' (see upsertAttributeValue in admin/systems.ts) but leaves drupal_synced_at/drupal_raw alone. So a row with source = 'manual' and a non-NULL drupal_raw is a detectable override of a synced value — the admin sync UI (#112) compares the current value against drupal_raw and can offer revert.

Mapping tables

Table	Maps	Key facts
drupal_system_mappings	Drupal products-vocabulary term ↔ NanaSelect system	drupal_term_name (the products-term name, = systems.name today — #121) is the stable join key; drupal_term_id/drupal_term_uuid are resolved and cached by the fetch layer (#110).
drupal_attribute_mappings	Drupal field on the products term ↔ NanaSelect attribute	drupal_field is the field machine name (e.g. field_glazing_type); drupal_vocabulary names the referenced vocabulary for term-reference fields (NULL = resolved by the fetch layer or scalar).

Both are seeded idempotently in seed/sql/030-drupal-mappings.sql — all 23 known systems, plus the 8 attributes whose Drupal field maps unambiguously (verified against the nanawalld8 config export). An unmapped attribute is simply ignored by the sync engine.

Attribute mappings are seed-bootstrapped but editor-owned thereafter (#130): the mapping editor on /admin/drupal-sync lists every contract field with its mapping state and mutates drupal_attribute_mappings live — map an unmapped field to an existing attribute, re-point or remove a mapping, or create a new attribute and its mapping in one flow (value type prefilled from the contract kind). Uniqueness holds both ways (one field per attribute, one attribute per field), every mutation lands a mapping audit entry, and the next sync preview reflects the change without a redeploy. The write layer is src/lib/server/admin/drupal-mappings.ts; the mappable-field universe is derived from the same typed contract the fetch layer uses (excluded fields are never offered).

The fetch layer and the full field-by-field contract (including the transport decision and live-site quirks) are documented in drupal-product-contract.md.

Attribute enable/disable and reads (#131)

attributes.enabled is a quick on/off toggle independent of retired_at, mirroring recommendation_rules. Disabled = hidden from buyer-facing surfaces; retired = archived. The read API treats them as follows:

• listAttributes returns only live attributes (enabled and non-retired) by default; admin lists pass { includeDisabled: true } / { includeRetired: true }. The factors/rules reference pick-lists keep the live-only default, so a new reference can never point at a disabled attribute.
• getComparison and getSystem exclude disabled attributes' values (the admin value editor passes { includeDisabled: true } to getSystem). A comparison deep link naming a disabled attribute id degrades gracefully — the row is simply absent.
• getRecommendation joins values against live attributes only, so a disabled attribute never feeds matching.
• Disable is usage-guarded (listAttributeReferences in admin/attributes.ts): blocked while any selection factor (factor_option_attributes) or right-sizing rule condition (rule_conditions) references the attribute. Retire carries the same guard.
• Sync interplay: the Drupal sync has no enabled-based filtering — a disabled attribute keeps its mapping and continues receiving synced values/baselines (data flows; display hides). The sync/mapping surfaces badge the disabled state.

Sync engine (#111)

src/lib/server/drupal/sync.ts — runDrupalSync(db, actor, { baseUrl }) runs fetch → plan → apply → audit. The planning is pure (planSync, unit-tested); per (mapped system × mapped field with a value):

Existing row	Action
none	created — typed via parseValue with the attribute's value-type hint, stamped source=drupal.
source=drupal, baseline unchanged	unchanged — zero writes (no timestamp churn; re-runs are no-ops).
source=drupal, baseline changed	updated — value + baseline rewritten, drupal_synced_at restamped.
source=manual (override)	held-back — the value is never touched; only drupal_raw/drupal_synced_at refresh (and only when Drupal actually changed).

The sync never deletes: a term or field absent from Drupal leaves local data alone. A partial fetch (ok: false) aborts before any writes. Term tid/uuid are backfilled into drupal_system_mappings on first contact. The typed SyncResult reports counts, unmapped terms/fields (never silently dropped), fetch warnings, and per-write errors; every run lands one sync entry in the admin audit trail (#21) with the same summary.