# product_db — Information Architecture (Vehicle Catalog UI)

Role: UX / information-architecture artifact. This document designs the navigation, pages,
listings, details, relationships, search, filters and dashboard of the vehicle-catalog UI
(the catalog domain inside the ACE Cockpit shell). It is a **design only** — no code.

Every page, metric, filter and link below is derived from the real domain model
(`product-db-design.md`) and verified against the **live data in `product_db`**
(read-only queries, 2026-08-10; counts quoted below re-verified 1:1 against the imported
tables). Nothing is invented (invariants 9/10). The UI fits the ACE Cockpit design system
(`DESIGN.md`, `existing-system.md` §1, `cockpit-sidebar.tsx`).

Table of contents

1. Design principles (read first)
2. Navigation — cockpit sidebar rework
3. Page inventory & route map
4. Page specifications
   - 4.1 Dashboard
   - 4.2 Models index
   - 4.3 Model detail
   - 4.4 Body-type detail
   - 4.5 Entity detail (engine / trim / variant — polymorphic)
   - 4.6 Variants index
   - 4.7 Structure browser (catalog dictionaries)
   - 4.8 Import status (provenance)
5. Search & filters (global conventions)
6. Language display (DE/FR/IT)
7. Cross-entity relationships navigation
8. Empty / error / loading states
9. Dashboard metric definitions with SQL citations
10. Build priority & effort
11. Rejected from the current mock UI (with reasons)
12. Traceability appendix (page → table.column)

---

## 1. Design principles (read first)

P1. **Browse-only.** product_db is a read-only snapshot staging catalog (design §1.1, §6:
no write timestamps, no audit trail for mutations, invariants 3/10). The UI exposes every
real entity and relationship, but offers **no create/edit/delete flows** anywhere. The only
"live" data is `pcm_imports` (import runs), which is also read-only in the UI.

P2. **Every pixel traces to a row.** Every count, badge, chip and cell must map to a real
table/column (§9, §12). If a number is not derivable from `product_db`, it does not appear.

P3. **Data quality is shown, not hidden.** PCM anomalies are preserved as data (design §8).
The UI renders them with honest labels (`Price 0.00`, `surcharge 99999999.00`,
`availability —` for the 128 NULL rows, empty `description`), and a dedicated "Data quality"
panel on the Dashboard quantifies them. No cleaning, no interpretation (invariants 3/4).

P4. **Sparse localization is honest.** `name_de` is present for 67/75 models, 5153/5156
structure elements; variants have **no** localized name; trims have **no** localized name at
all (design §4, §7). Display uses the documented fallback accessor
`COALESCE(name_de, name_fr, name_it, description, key)` and the UI marks when a fallback was
used (§6).

P5. **Reuse the ACE Cockpit shell.** Sidebar (purple, 260/80px), BrandTags chips, chrome
tabs, SectionCards, AG Grid with `astaraGridTheme`, status badges, 10–13px Montserrat
typography, custom scrollbars — exactly as in `existing-system.md` §1 and `DESIGN.md`. New
pages are thin Inertia wrappers + views in `components/views/*`, layouts via `app.tsx`.

---

## 2. Navigation — cockpit sidebar rework

### 2.1 Proposal

Replaces `NAV_ITEMS` in `resources/js/components/layout/cockpit-sidebar.tsx`:

```
[Catalog]                          [section header "Catalog"]
  Dashboard        /               LayoutGrid   — aggregate metrics (§4.1)
  Models           /models         Car          — models → years → body types → engines/trims
  Variants         /variants       Layers       — 1080 saleable combinations + feature/data/disclaimers
  Structure        /structure      Network      — brand configurator vocabularies + dictionaries
[System]
  Import Status    /imports        History      — pcm_imports runs (provenance)
```

No other items. Everything dropped is listed with reasons in §11.

### 2.2 Justification per item (real data source)

| Item | Data source (product_db) | Why it earns a top-level item |
|---|---|---|
| Dashboard | all tables (metrics in §4.1) | Entry point; every metric traceable to real queries. |
| Models | `models` (75) → `model_years` (145) → `body_types` (184) → `engines` (479) / `trims` (597) | The backbone of the catalog; models are the top of the hierarchy users drill into. |
| Variants | `variants` (1080) + polymorphic `entity_features` (73370) / `entity_data_values` (19950) / `entity_feature_disclaimers` (48) | The saleable product rows — the domain's "what can the customer order" surface, and the home of 73k feature rows. |
| Structure | `structure_categories` (183) → `structure_category_items` (5129) → `structure_elements` (5156); `section_definitions` (14), `body_type_section_elements` (23345), `data_units` (27) | The two display dictionaries (per-brand structure catalog + per-body-type section catalog) are a distinct, queryable domain in their own right. |
| Import Status | `pcm_imports` (2 runs, `api_version` 1.11.0, `row_counts`, `notes`) | The only operational data; provenance & integrity story (invariant 7). |

### 2.3 What does NOT become a page

- **Brands** — 9 lookup rows with no attributes beyond `code` (`brands`). It is a *filter
  dimension* on every page (BrandTags), not a page.
- **Market** — 1 row (`markets` = `CH`). Rendered as static context ("Market: CH") in page
  headers, not a page.
- **Engines / Trims as index pages** — their identity is scoped to a body type
  (`engines.uq_engines_identity (body_type_id, engine_key)`; `trims` likewise), and their
  feature/data payload is only meaningful in that context. They are browsed via Model →
  Body-type drill-down and via the Variants index filters (§7), and get full detail pages
  at their natural nesting depth (§4.5). A flat "Engines" index would be a 479-row table
  with a body-type column and no additional navigation value.
- **Model years as an index page** — 145 rows always viewed under a model (`model_years
  .model_id`); rendered as the year strip inside Model detail (§4.3).
- **Body types as an index page** — 184 rows always viewed under a model year; rendered
  inside Model detail, with a full detail page (§4.4).

---

## 3. Page inventory & route map

All routes live under the existing cockpit area (public per AGENTS.md; no auth middleware).
Named routes + Wayfinder helpers per project convention.

| # | Route | Page | Primary entity | Layout |
|---|---|---|---|---|
| 1 | `/` | Dashboard | aggregate | CockpitLayout |
| 2 | `/models` | Models index | `models` | CockpitLayout |
| 3 | `/models/{model}` | Model detail | `models` → `model_years` | CockpitLayout |
| 4 | `/models/{model}/body-types/{bodyType}` | Body-type detail | `body_types` + section catalog | CockpitLayout |
| 5 | `/models/{model}/body-types/{bodyType}/engines/{engine}` | Engine detail | `engines` + polymorphic stores | CockpitLayout |
| 6 | `/models/{model}/body-types/{bodyType}/trims/{trim}` | Trim detail | `trims` + polymorphic stores | CockpitLayout |
| 7 | `/variants` | Variants index | `variants` | CockpitLayout |
| 8 | `/variants/{variant}` | Variant detail | `variants` + polymorphic stores | CockpitLayout |
| 9 | `/structure` | Structure browser | structure catalog + dictionaries | CockpitLayout |
| 10 | `/structure/elements/{element}` | Element detail | `structure_elements` | CockpitLayout |
| 11 | `/imports` | Import status | `pcm_imports` | CockpitLayout |

Routes 5/6/8 share one view component (polymorphic entity detail, §4.5). Routes 3–6 share
the model breadcrumb.

---

## 4. Page specifications

### 4.1 Dashboard (`/`)

- **Purpose:** prove the catalog is alive; answer "what is in product_db" in one glance;
  link into every section.
- **Primary entity:** none — aggregates only (every metric mapped in §9).
- **Layout:** PageHeader (title, "Market: CH" chip, BrandTags global filter that re-runs the
  model-level queries per brand) + a grid of SectionCards:
  1. **Catalog overview** — the 6-count pyramid: Models 75 · Model years 145 · Body types
     184 · Engines 479 · Trims 597 · Variants 1080 (COUNT per table). Numbers link:
     Models → `/models`, Variants → `/variants`, the middle four → `/models` (drill-down
     route) with a tooltip "browse inside Models".
  2. **Lifecycle status** — for each of the six hierarchy tables, a status split
     Current/Draft/Past/Archived (4×6 mini-bars or one grouped table; real distributions
     in §9 M3). Archived chips turn gray; Draft chips use the yellow accent.
  3. **Brand distribution** — models per brand (`models.brand_code`, 9 bars:
     fiat 14, hyundai 14, mg 11, nissan 11, kgm 8, maxus 7, abarth 4, fiat_pro 4, jeep 2).
     Bar click → `/models?brand=<code>`.
  4. **Feature & data volume** — `entity_features` 73 370 rows (Standard 50 939 /
     NotAvailable 17 059 / Optional 5 244 / n/a 128) and `entity_data_values` 19 950 rows
     (engine 18 536 / trim 1 089 / variant 325), §9 M4/M5.
  5. **Dictionaries** — 14 section definitions, 23 345 section elements, 9 brands,
     27 data units, 183 structure categories, 5 129 category items, 5 156 structure
     elements, 4 194 distinct feature codes, 919 distinct data codes (§9 M6).
  6. **Data quality (anomalies, preserved & counted)** — price `0.00` ×11, price
     `99999.00` ×2, surcharge `99999999.00` ×2, `availability IS NULL` ×128 (all with
     `{"Optional":"Free"}` raw payload), 2 reconstructed structure elements (pcm_id NULL),
     67 empty-list pack declarations from the last import notes, 21.6% of trim feature rows
     outside the body-type section dictionary (§9 M7). Each line links to the page where
     the anomaly is visible (variants / trim detail / imports).
  7. **Latest import** — last `pcm_imports` row: `api_version` 1.11.0, status completed,
     started/finished timestamps, "261/261 checks passed", link → `/imports`.
  8. **Locale coverage** — models de 67 / fr 65 / it 65 of 75; structure elements
     de 5153 / fr 5139 / it 5137 of 5156 (from the `_de/_fr/_it` columns, NULL-counted).
     Supports the language-switch story (§6).
- **Empty/loading/error:** §8.

### 4.2 Models index (`/models`)

- **Purpose:** browse the 75 models; filter by brand / status / search; enter the
  hierarchy.
- **Primary entity:** `models` (joined counts).
- **Layout:**
  - PageHeader: title "Models", search field, BrandTags (9 real brands, §5), status chips
    (§5), market chip "CH".
  - AG Grid (`astaraGridTheme`), columns:
    Brand (brand tag) · Model name (display-name accessor; `name_*` fallback) ·
    Model key (`models.model_key`, monospace) · Years (`COUNT(model_years.*)`) ·
    Body types (`COUNT(body_types.*)`) · Variants (`COUNT(variants.*)` join) · Status
    (badge). Row click → `/models/{id}`. Sortable on name/key/counts.
  - Row count footer "75 models" (filtered count).
- **Query shape:** `SELECT ... FROM models WHERE brand_code=? AND status=? AND (name_de LIKE
  ? OR name_fr LIKE ? OR name_it LIKE ? OR description LIKE ? OR model_key LIKE ?)`
  (indexes: `idx_models_brand`, `idx_models_market`, `uq_models_identity` prefix; name LIKE
  is supported per design §4.4 — indexable prefix scan; full search across the fallback
  accessor is resolved in SQL by OR-ing the locale columns).

### 4.3 Model detail (`/models/{model}`)

- **Purpose:** the hierarchy `model → model years → body types → engines/trims → variants`
  with counts at every level; feature-preview summary.
- **Primary entity:** `models` with children.
- **Layout:**
  - Header: brand tag, display name (fallback accessor), `model_key`, status badge,
    market chip, locale-coverage chips (§6).
  - **Model years** — chrome-tab style strip or accordion: each `model_years` row shows
    `year_key` (verbatim; e.g. `2025`, `MY26`, `2026.5`, `DOBLO` — **not** rendered as an
    integer, design §7), status, body-type count, variant count. Status chips per year
    (e.g. years 2022–2024 are `Past`).
  - Year expansion → **body types** list: `body_key` (monospace), display name
    (fallback), status badge, `flag = 'New'` yellow badge when present, `test_drive`
    toggle icon (51/184 true), `range_json` tags as neutral chips (values verbatim:
    `suv`, `ioniq`, `iRange`, `electro`, `personalCars`, `commercialCars`), counts of
    engines / trims / variants. Click → `/models/{m}/body-types/{bt}`.
  - **Feature preview** (aggregate, real): for the model's whole subtree — total
    `entity_features` rows (join via body types → engines/trims/variants by
    `entity_type`), total `entity_data_values` rows, distinct feature codes, and the
    top feature codes by row count (e.g. `rain_sensor` 376 trim rows) with availability
    mix. Computed server-side; if heavy, it becomes a deferred prop with a skeleton
    (AGENTS.md Inertia v3 pattern).
  - Breadcrumb/back → `/models`.
- **Honest data notes:** 4 models are `Archived`, 13 `Draft` — shown verbatim, no
  interpretation of status semantics (design §11 #1).

### 4.4 Body-type detail (`/models/{model}/body-types/{bodyType}`)

- **Purpose:** the heart of the catalog: what a body type is made of — its section
  catalog, engines, trims, variants matrix, and feature-inclusion rules.
- **Primary entity:** `body_types` + `body_type_category_sections` /
  `body_type_section_elements` + `body_type_feature_includes`.
- **Layout:** header (body_key, name, status, flag New, test_drive, vehicle_group `PV`,
  range tags, Salesforce id present indicator — raw JSON shown in a tooltip, not parsed)
  + chrome tabs:
  1. **Sections (catalog)** — the 14 `section_definitions` keys (`engine`,
     `exterior_colors`, …; ENGINE/TRIM flag groups), each listing its
     `body_type_section_elements`: code, description, `element_type` chip
     (feature/data), `unit_code` (data rows only), `sap_code` (feature rows, when
     present). Real: 14 sections per body type, 23 345 elements across all 184.
     Sections with 0 elements (624 of 2576) render "no elements in vendor feed" (§8).
  2. **Engines** — `engines` rows of this body type: `engine_key`, `engine_name`
     (fallback), status, feature-row count, data-row count. Row → Engine detail.
  3. **Trims** — `trims` rows: `trim_key`, `trim_name`, status, feature-row count,
     data-row count, variant count. Row → Trim detail.
  4. **Variants** — the **trim × engine matrix**: rows = trims, columns = engines,
     cells = the `variants` row if one exists (`variants.trim_id + engine_id +
     body_type_id`), else an honest empty cell (a variant row simply does not exist —
     verified: only 73/1080 variants carry features, and many trim×engine cells have no
     variant at all, e.g. TUCSON 2025 `H1ZP`×`GTW5D5G17`). Cell shows name + price
     (sentinel styling for `0.00` / `99999.00`), click → Variant detail.
  5. **Feature includes** — `body_type_feature_includes` rows for this body type
     (666 total): `parent_feature_code` → `included_feature_code` list; codes resolve to
     section elements only when present (8 of 19 parent codes are rule labels like
     `STYLE` — rendered as plain codes with "rule label, no section element" chip).
- **Loading:** sections tab is the heaviest (≤89 elements/section); defer + skeleton.

### 4.5 Entity detail (engine / trim / variant — one polymorphic component)

- **Purpose:** show everything product_db knows about one entity: features, data values,
  disclaimers — all three polymorphic stores (`entity_features`, `entity_data_values`,
  `entity_feature_disclaimers` keyed by `entity_type` + `entity_id`).
- **Primary entity:** `engines` | `trims` | `variants` (route decides the type).
- **Layout (shared view, header varies):**
  - Header: type chip (ENGINE/TRIM/VARIANT), identity —
    engine: `engine_name` + `engine_key`; trim: `trim_name` + `trim_key` (no localized
    name exists — no language switch effect, §6); variant: `name` + **price** + status +
    links to its `trim`/`engine`/`body_type` (§7). Status badge.
  - **Features grid** (AG Grid): `feature_code` · availability badge
    (Standard green / NotAvailable gray / Optional orange + `surcharge` / `—` for the 128
    NULL rows) · `raw_value_json` payload toggle ("raw payload" disclosure, not parsed).
    Dictionary-coverage chip: "in section catalog" / "not in section catalog" (real
    derived query, §12.5 — 21.6% of trim rows are out-of-dictionary; shown, not hidden).
    Trim grids (69 098 feature rows) and engine grids (18 536 data rows) use
    **server-side pagination / deferred props**.
  - **Data values** (AG Grid): `data_code` · `value` (verbatim, e.g. `205/45 R17`,
    `6.7`) · `unit_code` (chip, e.g. `Millimeter`, `Freetext`). Multi-unit groups
    (292 groups carry >1 unit) render one row per (code, unit) pair — identity is the
    pair, not the code.
  - **Disclaimers** (`entity_feature_disclaimers`, 48 rows total): feature_code → the
    localized disclaimer text in the active locale (fallback chain), with the other
    locales in a tooltip.
- **Honest states:** a variant with no features (1007/1080) renders "No feature rows for
  this variant in the vendor feed" — feature data may live on the trim/engine instead
  (design §11 #19, UNKNOWN — shown as a cross-link hint, not interpreted).

### 4.6 Variants index (`/variants`)

- **Purpose:** browse all 1080 saleable trim×engine combinations globally.
- **Primary entity:** `variants`.
- **Layout:** PageHeader (search + cascading filters: brand → model → body type, §5;
  status chips) + AG Grid:
  Name (`variants.name`) · Brand · Model · Year (`year_key`) · Body type (`body_key`) ·
  Trim (`trim_key`) · Engine (`engine_key`) · Price (CHF, right-aligned; sentinel
  `0.00`/`99999.00` flagged with a warning chip) · Status. Row click → `/variants/{id}`.
- **Query shape:** joins `variants → body_types → model_years → models` (all FKs exist);
  filters on `models.brand_code`, `model_years.model_id`, `body_types.id`,
  `variants.status`, name LIKE.

### 4.7 Structure browser (`/structure`)

- **Purpose:** browse the two display dictionaries that feed the configurator UI:
  the **per-brand structure catalog** (`structure_categories` → `structure_category_items`
  → `structure_elements`) and the **shared vocabulary** (`section_definitions`,
  `body_type_section_elements`, `data_units`).
- **Primary entity:** `structure_categories` / `structure_elements`.
- **Layout:** BrandTags (structure data is per-brand; all 9 brands have rows, e.g.
  nissan 1175 elements, fiat_pro 298) + chrome tabs:
  1. **Categories** — per selected brand: category list (`structure_categories.code` +
     localized name; `disclaimer_*` shown under the name when present — 38/183 rows have
     one, with a language note §6). Each category expands to its items
     (`structure_category_items`): `element_code`, item name (item-level `name_*` shown;
     element-level name in tooltip — precedence is UNKNOWN, design §11 #16, so both are
     present, neither is declared the winner), `element_type` chip, `sort_order`.
     Item count per category (avg 30.2, max 196). Click → `/structure/elements/{id}`.
  2. **Elements** — the brand's `structure_elements` dictionary (code + localized name +
     type), with a "where used" column (categories containing it, via
     `structure_category_items.element_id`).
  3. **Section catalog (shared)** — `section_definitions` (14 rows, display names like
     "Engines & transmissions") and the `body_type_section_elements` vocabulary
     (23 345 rows; browseable by section key with code + description + unit + sap_code).
  4. **Data units** — the 27 `data_units` rows (verbatim labels: `Millimeter`,
     `Freetext`, `Units`, …) with their usage counts in `entity_data_values` and
     `body_type_section_elements` (both FKs exist).
- **Element detail (`/structure/elements/{element}`):** localized names, type, code,
  categories that contain it, and — derived, real — how many `entity_features` /
  `entity_data_values` rows reference the same code (dictionary-coverage link, optional).

### 4.8 Import status (`/imports`)

- **Purpose:** provenance & integrity — the audit trail of the snapshot catalog
  (invariant 7). The only operational page.
- **Primary entity:** `pcm_imports`.
- **Layout:** PageHeader + AG Grid: id · `api_version` (1.11.0) · status badge
  (completed green / running orange with spinner / failed red) · started_at ·
  finished_at · duration · "row_counts" expandable row (JSON → per-table count table,
  compared visually against §9 M1) · notes expandable (validation results, anomalies,
  e.g. "261/261 checks passed", "2 reconstructed structure elements (C-12)", "67
  empty-list pack declarations (X-03)").
- **Empty state:** "No import runs yet — the catalog has not been loaded" (§8).

---

## 5. Search & filters (global conventions)

1. **Brand filter (BrandTags)** — the existing `brand-tags.tsx` pattern (yellow active
   chip, 10px uppercase) reused on every index page + Dashboard, but **data-driven**:
   options come from `brands` (9 codes) with display labels (fiat→Fiat,
   fiat_pro→Fiat Professional, kgm→KGM, maxus→MAXUS, mg→MG). The current mock list
   (`lib/data.ts` `BRAND_FILTERS`, includes "Alfa Romeo" and "System") is rejected —
   those codes have zero rows in `product_db` (§11). The filter maps to
   `brand_code` (models/structure) or through the model join (variants).
2. **Status chips** — new shared `StatusChips` component (variant of BrandTags):
   Current/Draft/Past/Archived, each with its live count for the current table, only
   statuses present in the data rendered (e.g. `model_years` has no Archived row — no
   chip). Counts come from the same GROUP BY that feeds the Dashboard (§9 M3). Active
   chip = yellow (BrandTags convention); Archived = gray.
3. **Search field** — one input per index page; 300 ms debounce; Inertia `router.get`
   with query params (state survives refresh/back). Matches, OR-ed: localized name
   columns (`name_de/fr/it`), `description`/`trim_name`/`engine_name` where applicable,
   and the raw key (`model_key`, `year_key`, `body_key`, `engine_key`, `trim_key`,
   `feature_code`, `element_code`). Keys are monospace in results to distinguish code
   from name.
4. **Cascading filters (Variants index only)** — brand → model → body type selects fed
   by real FK chains (`models.brand_code`; `model_years.model_id`;
   `body_types.model_year_id`). Selecting a brand limits models, etc. Never free-text
   beyond the search box.
5. **Filter state = URL** — every filter combination is expressible as query params
   (`?brand=fiat&status=Current&q=500`); links between pages carry them over
   (e.g. Dashboard brand bar → `/models?brand=fiat`).
6. **No invented facets** — no energy labels, no price bands, no "type" facets: the
   vocabulary must come from columns that exist (`status`, `brand_code`, `range_json`
   tags rendered as chips, `flag='New'` badge).

## 6. Language display (DE/FR/IT)

- **Default locale: DE.** Matches the legacy cockpit default language (existing-system.md
  §2.9, session lang default `de`) and the German UI copy convention (DESIGN.md).
- **Switch:** a DE/FR/IT segmented control in the page header (persisted client-side,
  e.g. `localStorage.catalogLocale`). It changes **data display only** — UI labels stay
  German (existing-system.md §2.9; there is no multi-language UI in the cockpit).
- **Resolution rule** (design §7): `COALESCE(name_{locale}, name_de, name_fr, name_it,
  description, code)` — the app falls back to DE first, then the other locales, then
  non-localized text, then the key. This is the documented display-name accessor, an
  application-layer decision, not schema truth.
- **Honest indication:** each detail page shows locale-coverage chips
  (`DE ✓ / FR — / IT ✓`) built from the `_de/_fr/_it` NULL counts of the shown entity
  (real query). When the active locale is missing for a specific name, a small chip
  "FR: not available — showing DE" appears next to the name.
- **What is NOT localized (never pretend otherwise):** variant names and prices
  (`variants.name`, `variants.price` — no name_json in PCM), trims (`trim_name` +
  `description` only, design §3.7), engine `engine_name`, hierarchy `description`
  columns, section element `description` (mixed-language vendor text, design §11 #18),
  `sap_code`, `unit_code` labels (verbatim vendor strings). The switch simply has no
  effect there.

## 7. Cross-entity relationships navigation

| From | Link | To | Data path |
|---|---|---|---|
| Models index | row click | Model detail | `models.id` |
| Model detail | year strip → body type rows | Body-type detail | `model_years.model_id` → `body_types.model_year_id` |
| Model detail | "N variants" | Variants index pre-filtered | `body_types.model_year_id` → `variants.body_type_id` (+ model via join) |
| Body-type detail | engines/trims lists | Engine/Trim detail | `engines.body_type_id` / `trims.body_type_id` |
| Body-type detail | variants matrix cells | Variant detail | `variants.body_type_id + trim_id + engine_id` |
| Body-type detail | section elements | (deep-link) Structure section-catalog tab | `body_type_section_elements.code` lookup |
| Variant detail | trim / engine / body type chips | Trim / Engine / Body-type detail | `variants.trim_id`, `variants.engine_id`, `variants.body_type_id` |
| Entity detail | features/data/disclaimers | — (inline sections) | `entity_features`/`entity_data_values`/`entity_feature_disclaimers` by `(entity_type, entity_id)` |
| Entity detail | feature code coverage chip | Structure element / section element when resolvable | `entity_features.feature_code` = `structure_elements.code` / `body_type_section_elements.code` (only when present — 21.6% out-of-dictionary get a "not in catalog" chip instead) |
| Body-type detail | feature includes | section elements when resolvable | `body_type_feature_includes.included_feature_code` lookup |
| Structure browser | category → items → element | Element detail | `structure_category_items.element_id` → `structure_elements.id` |
| Element detail | "where used" | Category list | `structure_category_items.element_id` back-join |
| Dashboard cards | every count | corresponding index page | see §9 |

Rules: links are **only** emitted when the target row exists (variant cells with no
`variants` row are empty, never "not available"; code cross-links only when the code
exists in the target dictionary). No synthetic join pages; the polymorphic stores are
always reached through their owning entity.

## 8. Empty / error / loading states

| State | Pattern | Notes |
|---|---|---|
| Loading (server data) | shadcn `Skeleton` rows/table + `PageHeader` shell | Deferred props (Inertia v3) with skeletons for the heavy sections: Model feature preview, Body-type Sections tab, Trim/Engine feature grids. |
| Loading (grid) | AG Grid loading overlay | `astaraGridTheme`, spinner + "Loading…". |
| Filtered-empty | Empty-state card inside the page: "No models match the current filters" + **Reset filters** button (clears query params) | Applied to every index page. |
| Data-absent (honest) | Inline muted note, no button | "No feature rows for this variant in the vendor feed" (1007/1080 variants); "No elements for this section in the vendor feed" (624/2576 sections); "No localized name in the vendor feed — showing DE fallback"; "Rule label — no section element" (STYLE/TECH packs). |
| 404 (bad id) | Inertia exception page (existing) | `models/{id}` etc. with no row. |
| Backend failure (deferred prop) | Inline alert card with "Retry" | One failed prop never blanks the whole page. |
| Import statuses | running: orange spinner + started_at; completed: green; failed: red + notes | `/imports` (§4.8). |
| Empty `pcm_imports` | "No import runs yet — the catalog has not been loaded" | First-run state. |

## 9. Dashboard metric definitions with SQL citations

Every metric is a real query against `product_db` (verified values in parentheses —
re-checked read-only on 2026-08-10; `pcm_imports.notes` confirms 261/261 verification
checks passed on import).

| # | Metric | Query shape | Source |
|---|---|---|---|
| M1 | Catalog counts (75 / 145 / 184 / 479 / 597 / 1080) | `SELECT COUNT(*) FROM <t>` per table | `models`, `model_years`, `body_types`, `engines`, `trims`, `variants` |
| M2 | Brands (9) + models per brand (fiat 14, hyundai 14, mg 11, nissan 11, kgm 8, maxus 7, abarth 4, fiat_pro 4, jeep 2) | `SELECT brand_code, COUNT(*) FROM models GROUP BY brand_code` | `models.brand_code` (FK → `brands.code`) |
| M3 | Status split per table (models 54/13/4/4; years 105/23/17; body types 147/26/11; engines 447/21/7/4; trims 554/36/6/1; variants 1037/22/16/5 — Current/Draft/Past/Archived) | `SELECT status, COUNT(*) FROM <t> GROUP BY status` per table | `status` column, all six hierarchy tables |
| M4 | Feature volume 73 370; availability Standard 50 939 / NotAvailable 17 059 / Optional 5 244 / NULL 128 | `SELECT availability, COUNT(*) FROM entity_features GROUP BY availability`; NULL row = `WHERE availability IS NULL` | `entity_features.availability` |
| M5 | Data values 19 950; by entity type engine 18 536 / trim 1 089 / variant 325 | `SELECT entity_type, COUNT(*) FROM entity_data_values GROUP BY entity_type` | `entity_data_values.entity_type` |
| M6 | Dictionaries: 14 sections, 23 345 section elements (feature 16 661 / data 6 684), 27 units, 183 categories, 5 129 items, 5 156 elements (incl. 2 reconstructed), 4 194 distinct feature codes, 919 distinct data codes, 666 include pairs | `COUNT(*)` / `SELECT DISTINCT` per table | `section_definitions`, `body_type_section_elements.element_type`, `data_units`, `structure_categories`, `structure_category_items`, `structure_elements`, `entity_features.feature_code`, `entity_data_values.data_code`, `body_type_feature_includes` |
| M7 | Data quality counters: price `0.00` ×11; price `99999.00` ×2; surcharge `99999999.00` ×2; `availability IS NULL` ×128 (128 with `raw_value_json = {"Optional":"Free"}`); 2 reconstructed elements (`structure_elements.pcm_id IS NULL`); 21.6% trim features outside own body-type section catalog (14 948 / 69 098) | equality/sentinel queries (design §12.5); coverage query `entity_features f JOIN trims t … JOIN body_types b … WHERE NOT EXISTS (SELECT 1 FROM body_type_category_sections s JOIN body_type_section_elements e ON e.section_id=s.id WHERE s.body_type_id=b.id AND e.code=f.feature_code)`; packs count from `pcm_imports.notes` | `variants.price`, `entity_features.surcharge/raw_value_json`, `structure_elements.pcm_id`, §12.5 query, `pcm_imports.notes` |
| M8 | Latest import: api_version 1.11.0, status completed, 261/261 checks | `SELECT * FROM pcm_imports ORDER BY id DESC LIMIT 1` (+ `notes`) | `pcm_imports.api_version/status/started_at/finished_at/notes` |
| M9 | Locale coverage: models de 67 / fr 65 / it 65 of 75; structure elements de 5153 / fr 5139 / it 5137 of 5156 | `SELECT COUNT(*), COUNT(name_de), COUNT(name_fr), COUNT(name_it) FROM models` (same for `structure_elements`) | `models.name_de/fr/it`, `structure_elements.name_de/fr/it` (NULL counts) |

Explicitly **not** on the dashboard (no data): inbox counts, document counts, visits,
pricelists, user activity, campaign stats — all belong to the legacy cockpit domain
(§11).

## 10. Build priority & effort

Ordering rationale: prove the data plumbing with the smallest page first, then deliver
index pages (fast wins), then the deep hierarchy, then dictionaries; the polymorphic
entity detail is the most complex component and is scheduled once the two grids feeding
it exist.

| # | Page | Effort | Why here |
|---|---|---|---|
| 1 | Import status (`/imports`) | S — 0.5–1 d | Tiny, single table, validates the Laravel→DB read path and the notes/row_counts rendering. |
| 2 | Models index | M — 1–2 d | BrandTags + StatusChips + search conventions are established here for reuse. |
| 3 | Dashboard | M — 2–3 d | Metrics M1–M9 queries + cards; everything already proven by pages 1–2. |
| 4 | Model detail | M — 2–3 d | Hierarchy drill-down + counts; no polymorphic grids yet. |
| 5 | Body-type detail | L — 3–4 d | Sections tab (heaviest static query) + trim×engine matrix; defines the section-catalog rendering reused in Structure. |
| 6 | Variants index + Variant/Engine/Trim detail | L — 3–4 d | Shared polymorphic entity-detail component (features/data/disclaimers grids + pagination for 69k-row trims). |
| 7 | Structure browser + element detail | M — 2–3 d | Standalone dictionary browser; reuses grid patterns from 5–6. |

Total ≈ **14–21 dev-days** (one engineer, shared components, no backend work beyond
read-only controllers). This is a planning estimate, not a data claim.

## 11. Rejected from the current mock UI (with reasons)

Everything below is ported from the notification/documents/links demo domain
(`existing-system.md` §1, §4) and has **no data source in `product_db`** — keeping it
would violate invariants 9/10 (UI must result from the real domain; no fictional
business features).

| Current item | Where it lives | Why rejected |
|---|---|---|
| Inbox + message-detail (tabs Inbox/Scheduled/Archive/Drafts, open rates, attachments) | `pages/cockpit/inbox-view.tsx`, `message-detail-view.tsx` | `aws_user_notifications`/`aws_notifications` are legacy-cockpit tables, not in product_db. Notification counts (3151/1/167/28) have no traceable source (existing-system.md §6). |
| Compose | `compose-view.tsx` | Notification compose workflow — no table. Also contradicts P1 (browse-only). |
| Documents + add-document (folder tree, DE-required upload) | `documents-view.tsx`, `add-document-view.tsx` | `aws_documents*` not in product_db; FOLDER_TREE is mock (existing-system.md §1). |
| Links (Aftersales/DND/Marketing tabs) | `links-view.tsx` | `ACE__links` not in product_db. |
| Admin (profile + notification email-pref toggles) | `admin-view.tsx` | Mock prefs for notification categories (`aws_notification_email_prefs_*` legacy); the real `users` table exists but is starter-kit auth, not catalog — the settings pages stay under AppLayout untouched. |
| Sidebar: Campaign Portal, Content Management, DIAS, Reporting, Pricelists | `cockpit-sidebar.tsx` NAV_ITEMS | Legacy modules (campaign_portal, contentful, dealers, analytics, pricelist) with no product_db data source (existing-system.md §6: no views/data). |
| Dashboard widgets: Inbox list, Latest Documents, Frequently used links, Pricelists, Visits chart, "5 NEW", greeting "Good morning, ACE Admin" | `dashboard-view.tsx` + `lib/data.ts` mocks | All mock (MESSAGES, DOCUMENTS, VISITS, …). Replaced by §4.1 metrics, all traceable to product_db. |
| Mock brand list incl. **Alfa Romeo** and **System** | `lib/data.ts` `BRANDS`/`BRAND_FILTERS` | `brands` table has exactly 9 codes; Alfa Romeo and System have 0 rows anywhere in product_db (0 models, 0 structure rows). The BrandTags component stays, but options become data-driven. |
| Chrome-tab inbox counts on dashboard | inbox-view | No notification table; count semantics unknown (existing-system.md §6). |
| Any write flow (new/edit/delete models, documents, notifications) | — | P1: read-only snapshot domain (design §6; invariants 3/10). |

Kept as-is (outside the catalog scope, real infrastructure): Fortify auth, settings
pages (profile/security/appearance — backed by `users`), the CockpitLayout shell,
BrandTags/BrandPills/ChromeTabs/SectionCard/AG Grid primitives.

## 12. Traceability appendix (page → table.column)

| Page | Reads (tables) | Key columns |
|---|---|---|
| Dashboard | all catalog tables | §9 M1–M9 |
| Models index | `models`, `brands`, `model_years`, `body_types`, `variants` | `models.*`, counts via FKs `model_years.model_id`, `body_types.model_year_id`, `variants.body_type_id` |
| Model detail | `models`, `model_years`, `body_types`, `engines`, `trims`, `variants`, `entity_features`, `entity_data_values` | `models.id`; `model_years.year_key/status`; `body_types.body_key/flag/test_drive/range_json`; polymorphic stores via body-type join |
| Body-type detail | `body_types`, `body_type_category_sections`, `section_definitions`, `body_type_section_elements`, `engines`, `trims`, `variants`, `body_type_feature_includes` | `section_key`, `element_type`, `unit_code`, `sap_code`, `sort_order`, `parent_feature_code`, `included_feature_code` |
| Entity detail (engine/trim/variant) | `engines`\|`trims`\|`variants`, `entity_features`, `entity_data_values`, `entity_feature_disclaimers`, `data_units` | `(entity_type, entity_id)`, `availability`, `surcharge`, `raw_value_json`, `data_code`, `value`, `unit_code`, `disclaimer_*`, `price`, `name` |
| Variants index | `variants`, `body_types`, `model_years`, `models`, `brands` | `variants.price/name/status`, FK join chain |
| Structure browser | `structure_categories`, `structure_category_items`, `structure_elements`, `section_definitions`, `body_type_section_elements`, `data_units` | `market_code`, `brand_code`, `code`, `name_*`, `disclaimer_*`, `element_id`, `element_type`, `sort_order` |
| Element detail | `structure_elements`, `structure_category_items`, `structure_categories`, (optional) `entity_features`, `entity_data_values` | `element_id` back-join; code matches |
| Import status | `pcm_imports` | `api_version`, `status`, `row_counts`, `notes`, `started_at`, `finished_at` |

---

*Written 2026-08-10. All counts re-verified by read-only queries against `product_db`
immediately before writing; nothing in this document derives from a table or column that
does not exist in the schema of `product-db-design.md` §3.*