# product_db — Logical Domain Model Design (PCM Vehicle Catalog) Role: domain-architect artifact. This document designs the `product_db` MySQL 8 schema as a logical domain model of the PCM vehicle product catalog. It is a **design only** — no migrations, no code. Every decision is derived from the evidence in `pcm-analysis.md`, `pcm-data-profile.md`, `existing-system.md`, `goal.md`, and from live read-only queries against `pcm` (192.168.200.4:3307, user `developer`) run on 2026-08-10 for this design (see §0). The invariants in `invariants.md` are honored: no guessing (UNKNOWN marked), no silent data loss (every change documented with problem / change / reason / risk / migration / validation), repeatable + verifiable migration, PCM never modified. --- ## 0. Evidence verification performed for this design Before designing, the live PCM database was re-queried (read-only SELECT/SHOW only). Findings: - **Row counts match `pcm-analysis.md` / `pcm-data-profile.md` exactly** for all 20 tables. A discrepancy observed via `information_schema.table_rows` was proven to be stale InnoDB estimates — real `COUNT(*)` matches the documents 1:1. - **`rules_json.includes` and `body_type_feature_includes` are pair-for-pair identical**: `rules_json` contains exactly **666 `(parent, included)` pairs** — equal to the `body_type_feature_includes` row count — with 0 pairs missing in either direction (checked per body_type, 2026-08-10). `rules_json` is non-empty on **88** rows (`{"includes":{}}` on the other 96). Additionally, 39 rows declare **67 empty-list packs** (30 distinct pack names, e.g. `n_line_pack: []`, `test_paket_ralf: []`) — pack labels with zero codes that have no pair representation. The two stores are exact duplicates for all pair data → merge is lossless for pairs; the empty-list pack declarations are documented exclusions (X-03, Change C-02). - **The units vocabulary is shared**: `body_type_section_elements.unit` and `entity_data_values.unit` both contain exactly the same **27 distinct unit strings** (`Millimeter`, `Freetext`, `Units`, …). One lookup table serves both (Change C-08). - **Localization JSON keys are bounded**: every `*_json` locale map uses only the key set `{de-CH, fr-CH, it-CH}` (key order varies; some rows are `{}`). No other locales observed. - **Text sizing evidence** (max observed lengths): localized names ≤ 371 chars (structure elements), localized disclaimers ≤ 178 chars (variants) / ≤ 113 (categories), hierarchy descriptions ≤ 34–63 chars, `entity_data_values.value` ≤ 113 chars, all codes/keys ≤ 32 chars, Salesforce ids ≤ 18 chars, unit strings ≤ 20 chars. Column sizes below follow this. - **`range_json` has 7 distinct values** (`[]`, `["suv"]`, `["ioniq"]`, `["iRange"]`, `["electro"]`, `["personalCars"]`, `["commercialCars"]`) — not just `[]`/`["suv"]` as a sample suggested. Semantics UNKNOWN; kept raw. - **markets**: single row `CH`. **models.status** vocabulary confirmed `Archived, Current, Draft, Past`. - PCM runs on **MariaDB** (JSON stored as `longtext` + `CHECK (json_valid(...))`); the target is **MySQL 8** (native `JSON` type, CHECK constraints enforced from 8.0.16). Every JSON payload must pass `json_valid` re-validation during import (verification plan §12). --- ## 1. Domain overview ### 1.1 Domain statement product_db is the **vehicle product catalog** domain: the Astara Switzerland (market `CH`) passenger/commercial range (`vehicle_group` = `PV`) as published by the PCM vendor feed. The catalog is a hierarchy of *market → brand → model → model year → body type → (engine | trim) → variant*, decorated with two display dictionaries — the per-body-type *section catalog* and the per-brand *structure catalog* (configurator UI vocabulary) — and three *entity value stores* that attach features, data values and disclaimers to engines, trims and variants. The domain is a **snapshot staging catalog**: PCM has no timestamps, no history, no audit trail (evidence: zero temporal columns in all 20 tables). product_db preserves this character and adds only import-side audit fields (§6). ### 1.2 Entity map | product_db entity | PCM source table | Decision | Role | Rows (evidence) | |---|---|---|---|---| | `markets` | `markets` | KEEP (lookup) | market/locale scope | 1 (`CH`) | | `brands` | `brands` | KEEP (lookup) | brand registry | 9 | | `models` | `models` | KEEP (entity) | model | 75 | | `model_years` | `model_years` | KEEP (entity) | model year | 145 | | `body_types` | `body_types` | KEEP (entity) | body type | 184 | | `engines` | `engines` | KEEP (entity) | engine | 479 | | `trims` | `trims` | KEEP (entity) | trim | 597 | | `variants` | `variants` | KEEP (entity) | saleable trim × engine combination | 1080 | | `body_type_category_sections` | `body_type_category_sections` | KEEP (normalized) | per-body-type section catalog | 2576 = 184 × 14 | | `section_definitions` | extracted from section boilerplate | NEW (normalize) | section-key dictionary (14 keys) | 14 | | `body_type_section_elements` | `body_type_section_elements` | KEEP | section ↔ element codes | 23345 | | `body_type_feature_includes` | `body_type_feature_includes` **+ `body_type_rules`** | MERGE | feature inclusion rules | 666 pairs (set-equal, C-02; empty-list packs excluded X-03) | | `structure_categories` | `structure_categories` | KEEP | per-brand display categories | 183 | | `structure_elements` | `structure_elements` | KEEP | per-brand element dictionary | 5154 (+2 reconstructed, C-12) | | `structure_category_items` | `structure_category_items` | KEEP (hardened) | category ↔ element join | 5129 | | `entity_features` | `entity_features` | KEEP (polymorphic) | feature values per entity | 73370 | | `entity_data_values` | `entity_data_values` | KEEP (polymorphic) | data values per entity | 19950 | | `entity_feature_disclaimers` | `entity_feature_disclaimers` | KEEP (polymorphic) | per-feature disclaimers | 48 | | `data_units` | extracted from `unit` values | NEW (normalize) | unit vocabulary | 27 | | `pcm_imports` | — | NEW (infrastructure) | import provenance log | 0+ | Excluded (documented in §10): `body_type_marketing_mapping`, `body_type_rules` (merged into `body_type_feature_includes`), `api_version`. ### 1.3 Relationship graph ``` markets.code ──────────┐ ├→ models ─→ model_years ─→ body_types ─┬→ engines ────────────────┐ brands.code ───────────┘ │ │ └── (structure_categories, structure_elements: market+brand scoped) │ │ body_types ──┬→ body_type_category_sections ─→ body_type_section_elements │ ├→ body_type_feature_includes │ ├→ engines ──┐ ├→ trims ──┐ │ └────────────┤ └──────────┤ │ └────────────→ variants (trim × engine) ──────┘ │ │ entity_features / entity_data_values / entity_feature_disclaimers ──(entity_type, entity_id)──→ engines | trims | variants (polymorphic, no FK — see C-09) ``` FKs mirror PCM semantics: hierarchy children `ON DELETE CASCADE`, market/brand links plain `REFERENCES` (no cascade). Polymorphic references have **no FK** (MySQL 8 cannot express a conditional foreign key) — integrity is enforced by CHECK + validation queries (§8, §12). ### 1.4 Lifecycle Every catalog entity carries the same four-value lifecycle vocabulary, identical across all six hierarchy tables (evidence): **`Current` / `Draft` / `Past` / `Archived`**. - Observed semantics: `Past` = previous model years (e.g. model_years 2022–2024 are `Past`), `Archived` = retired (small counts: 4 models, 4 engines, 1 trim, 5 variants), `Current` = actively published range, `Draft` = in preparation. - UNKNOWN: the exact operational distinction between `Draft` and `Archived` (no timestamps exist to reconstruct transitions). Kept verbatim as data; not interpreted by the schema. - Status is a **data-level value with no audit trail in PCM** → product_db does not invent transition timestamps (see §6). ### 1.5 Identity strategy - **Surrogate `BIGINT UNSIGNED AUTO_INCREMENT` primary keys** on all entity/join tables (Laravel convention), PLUS a **`pcm_id`** column holding the PCM surrogate id with a UNIQUE constraint on every table that has one. - `markets` and `brands` keep their **natural keys** (`code`) as PK — PCM has no surrogate there. - This gives: (1) repeatable migration — every import upserts by `pcm_id`; (2) verification — row counts and id-range checks per table; (3) decoupling — product_db ids never collide with vendor id lifecycle (reuse/deletion); (4) stable provenance even if PCM ids change. - Natural business keys are preserved as UNIQUE constraints exactly as in PCM (e.g. `(market_code, brand_code, model_key)`). --- ## 2. Decisions at a glance | # | Topic | Decision | |---|---|---| | D-1 | PK strategy | Surrogate bigint PK + `pcm_id` UNIQUE everywhere a PCM id exists; natural keys (code) for `markets`/`brands`; PCM business keys preserved as UNIQUE | | D-2 | Localization | JSON locale maps → **per-language columns** `{col}_de/_fr/_it` (one convention for all content); JSON kept only for non-localized raw payloads (`raw_value_json`, `salesforce_id_json`, `range_json`) | | D-3 | Statuses | Shared `varchar(32)` + CHECK `IN ('Current','Draft','Past','Archived')` on all six hierarchy tables; NOT NULL; no lookup table | | D-4 | Audit | `created_at`/`updated_at` on all tables; no publish/transition timestamps (no evidence); `pcm_imports` provenance log | | D-5 | Polymorphism | `entity_features` / `entity_data_values` / `entity_feature_disclaimers` stay polymorphic (`entity_type` CHECK + `entity_id`, no FK) with documented risk + orphan validation | | D-6 | Sections | Boilerplate `display_name`/`description`/`flag` extracted to `section_definitions` (14 rows); `elements_json` dropped (proven mirror) | | D-7 | Rules | `body_type_rules.rules_json` merged into `body_type_feature_includes` (proven set-equal); rules table dropped | | D-8 | Units | Free-text `unit` → `data_units` lookup + FK on both unit-carrying tables (misuse anomalies preserved as data) | | D-9 | element_type | Case/vocabulary unified to lowercase `feature` / `data` / `sub_category` across both catalogs | | D-10 | Dead schema | `body_type_marketing_mapping` (all payloads empty) and `api_version` (cache marker, consumer UNKNOWN) excluded, documented | | D-11 | Structure catalog | `structure_category_items` gains `element_id` FK → `structure_elements` (2 missing element rows reconstructed from item data, C-12) | | D-12 | Codes | Feature/data codes stay free `varchar` — **no FK to any dictionary** (21.6% of trim-feature rows legitimately out of dictionary); detection via validation queries | --- ## 3. Target schema (MySQL 8 DDL-level detail) Conventions for every table: `ENGINE=InnoDB`, `DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci` (same collation as PCM — keeps code-key comparison consistent during migration verification). Localized columns are `varchar(512)` (evidence: max name 371 chars, max disclaimer 178) and nullable (locale coverage is sparse — e.g. models `name_de` present for 67/75). JSON columns use the native MySQL `JSON` type (MariaDB `longtext` payloads re-validated on import). `created_at`/`updated_at` `timestamp NULL DEFAULT NULL` are on every table (§6) and omitted from the listings below except where noted. Column names keep PCM naming unless a change is documented in §9. ### 3.1 `markets` — KEEP (lookup) ```sql CREATE TABLE markets ( code varchar(8) NOT NULL, PRIMARY KEY (code) ); ``` One row: `CH`. Natural PK, no `pcm_id` (PCM has none). Laravel model `Market`. ### 3.2 `brands` — KEEP (lookup) ```sql CREATE TABLE brands ( code varchar(64) NOT NULL, PRIMARY KEY (code) ); ``` Nine rows: `abarth, fiat, fiat_pro, hyundai, jeep, kgm, maxus, mg, nissan`. Natural PK. ### 3.3 `models` — KEEP (entity) ```sql CREATE TABLE models ( id bigint unsigned NOT NULL AUTO_INCREMENT, pcm_id bigint unsigned NOT NULL, market_code varchar(8) NOT NULL, brand_code varchar(64) NOT NULL, model_key varchar(128) NOT NULL, status varchar(32) NOT NULL, -- CHECK IN ('Current','Draft','Past','Archived') description text NULL, -- display name (e.g. '500e'); kept raw name_de varchar(512) NULL, -- ← name_json.'de-CH' name_fr varchar(512) NULL, -- ← name_json.'fr-CH' name_it varchar(512) NULL, -- ← name_json.'it-CH' PRIMARY KEY (id), UNIQUE KEY uq_models_pcm_id (pcm_id), UNIQUE KEY uq_models_identity (market_code, brand_code, model_key), KEY idx_models_brand (brand_code), KEY idx_models_market (market_code), CONSTRAINT fk_models_brand FOREIGN KEY (brand_code) REFERENCES brands(code), CONSTRAINT fk_models_market FOREIGN KEY (market_code) REFERENCES markets(code), CONSTRAINT chk_models_status CHECK (status IN ('Current','Draft','Past','Archived')) ); ``` Index note: `(market_code, brand_code)` prefix of `uq_models_identity` serves brand+market filtering; `(brand_code)` serves the global brand filter (sidebar convention). ### 3.4 `model_years` — KEEP (entity) ```sql CREATE TABLE model_years ( id bigint unsigned NOT NULL AUTO_INCREMENT, pcm_id bigint unsigned NOT NULL, model_id bigint unsigned NOT NULL, year_key varchar(128) NOT NULL, -- NOT a clean year (evidence: '2025DemoRalf', 'MY26', 'DOBLO') status varchar(32) NOT NULL, -- CHECK as models description text NULL, name_de varchar(512) NULL, name_fr varchar(512) NULL, name_it varchar(512) NULL, PRIMARY KEY (id), UNIQUE KEY uq_model_years_pcm_id (pcm_id), UNIQUE KEY uq_model_years_identity (model_id, year_key), CONSTRAINT fk_model_years_model FOREIGN KEY (model_id) REFERENCES models(id) ON DELETE CASCADE, CONSTRAINT chk_model_years_status CHECK (status IN ('Current','Draft','Past','Archived')) ); ``` `year_key` semantics UNKNOWN for non-year values; kept verbatim (§11). Index `(model_id)` is the FK; "model years of a model" uses the UNIQUE prefix. ### 3.5 `body_types` — KEEP (entity) ```sql CREATE TABLE body_types ( id bigint unsigned NOT NULL AUTO_INCREMENT, pcm_id bigint unsigned NOT NULL, model_year_id bigint unsigned NOT NULL, body_key varchar(128) NOT NULL, -- e.g. '312', '312_695_75' status varchar(32) NOT NULL, -- CHECK as models description text NULL, name_de varchar(512) NULL, -- ← body_type_name_json.'de-CH' name_fr varchar(512) NULL, name_it varchar(512) NULL, test_drive tinyint(1) NOT NULL, -- 0 → 133, 1 → 51 (evidence); exact meaning UNKNOWN salesforce_id_json json NULL, -- {test, prod}; kept raw JSON (1:n mapping, UNKNOWN) flag varchar(32) NOT NULL, -- 'None' 183, 'New' 1; vocabulary UNKNOWN → no CHECK range_json json NOT NULL, -- marketing range tags; 7 distinct values; kept raw vehicle_group varchar(32) NOT NULL, -- 'PV' 184/184; vocabulary UNKNOWN → no CHECK PRIMARY KEY (id), UNIQUE KEY uq_body_types_pcm_id (pcm_id), UNIQUE KEY uq_body_types_identity (model_year_id, body_key), CONSTRAINT fk_body_types_year FOREIGN KEY (model_year_id) REFERENCES model_years(id) ON DELETE CASCADE, CONSTRAINT chk_body_types_status CHECK (status IN ('Current','Draft','Past','Archived')) ); ``` `salesforce_id_json` and `range_json` are **non-localized raw payloads** — kept as JSON verbatim (D-2). The shared Salesforce prod id (19 distinct ids for 59 rows) is a data-level UNKNOWN, not a schema problem. ### 3.6 `engines` — KEEP (entity) ```sql CREATE TABLE engines ( id bigint unsigned NOT NULL AUTO_INCREMENT, pcm_id bigint unsigned NOT NULL, body_type_id bigint unsigned NOT NULL, engine_key varchar(255) NOT NULL, -- e.g. '1.4_ICE_180_Manual', 'PEM', 'BEV_155' status varchar(32) NOT NULL, -- CHECK as models engine_name varchar(255) NULL, -- non-localized display name (e.g. '1.4 T-Jet (180 PS) Manual') description text NULL, -- 226/479 empty string — kept as-is (empty ≠ NULL) name_de varchar(512) NULL, -- ← engine_name_translation_json.'de-CH' name_fr varchar(512) NULL, name_it varchar(512) NULL, PRIMARY KEY (id), UNIQUE KEY uq_engines_pcm_id (pcm_id), UNIQUE KEY uq_engines_identity (body_type_id, engine_key), CONSTRAINT fk_engines_body FOREIGN KEY (body_type_id) REFERENCES body_types(id) ON DELETE CASCADE, CONSTRAINT chk_engines_status CHECK (status IN ('Current','Draft','Past','Archived')) ); ``` ### 3.7 `trims` — KEEP (entity) ```sql CREATE TABLE trims ( id bigint unsigned NOT NULL AUTO_INCREMENT, pcm_id bigint unsigned NOT NULL, body_type_id bigint unsigned NOT NULL, trim_key varchar(128) NOT NULL, -- e.g. 'Anniversario', '595', 'EH0' status varchar(32) NOT NULL, -- CHECK as models trim_name varchar(255) NULL, -- non-localized display name description text NULL, -- 323/597 empty string — kept as-is PRIMARY KEY (id), UNIQUE KEY uq_trims_pcm_id (pcm_id), UNIQUE KEY uq_trims_identity (body_type_id, trim_key), CONSTRAINT fk_trims_body FOREIGN KEY (body_type_id) REFERENCES body_types(id) ON DELETE CASCADE, CONSTRAINT chk_trims_status CHECK (status IN ('Current','Draft','Past','Archived')) ); ``` Note: PCM `trims` has **no name_json** (no localized name) — only `trim_name` + `description`. No localization columns added. ### 3.8 `variants` — KEEP (entity) ```sql CREATE TABLE variants ( id bigint unsigned NOT NULL AUTO_INCREMENT, pcm_id bigint unsigned NOT NULL, body_type_id bigint unsigned NOT NULL, trim_id bigint unsigned NOT NULL, engine_id bigint unsigned NOT NULL, status varchar(32) NOT NULL, -- CHECK as models name varchar(512) NOT NULL, -- usually 'trim_key X engine_key'; 72 deviations kept raw (UNKNOWN rule) price decimal(12,2) NOT NULL, -- sentinels 0.00 (11 rows) and 99999.00 (2 rows) kept as-is disclaimer_de varchar(512) NULL, -- ← variant_disclaimer_json.'de-CH' disclaimer_fr varchar(512) NULL, disclaimer_it varchar(512) NULL, PRIMARY KEY (id), UNIQUE KEY uq_variants_pcm_id (pcm_id), UNIQUE KEY uq_variants_identity (body_type_id, trim_id, engine_id), KEY idx_variants_trim (trim_id), KEY idx_variants_engine (engine_id), CONSTRAINT fk_variants_body FOREIGN KEY (body_type_id) REFERENCES body_types(id) ON DELETE CASCADE, CONSTRAINT fk_variants_trim FOREIGN KEY (trim_id) REFERENCES trims(id) ON DELETE CASCADE, CONSTRAINT fk_variants_engine FOREIGN KEY (engine_id) REFERENCES engines(id) ON DELETE CASCADE, CONSTRAINT chk_variants_status CHECK (status IN ('Current','Draft','Past','Archived')) ); ``` Price sentinels kept verbatim (user decision #2); detection query in §12. Variant → body_type consistency of trim/engine is enforced by PCM data (0 mismatches) and re-verified post-import. ### 3.9 `section_definitions` — NEW (normalized dictionary, 14 rows) ```sql CREATE TABLE section_definitions ( section_key varchar(128) NOT NULL, -- 'engine', 'exterior', 'pack', … flag varchar(32) NOT NULL, -- 'ENGINE' (8 keys) | 'TRIM' (6 keys) display_name varchar(255) NOT NULL, -- templated text, e.g. 'Engines & transmissions' description text NOT NULL, -- templated text, e.g. 'Engines & transmissions Description' PRIMARY KEY (section_key), CONSTRAINT chk_section_definitions_flag CHECK (flag IN ('ENGINE','TRIM')) ); ``` Extracted from `body_type_category_sections` boilerplate — the same 14 `section_key` values recur exactly 184× each, each with identical `flag`/`display_name`/`description` (evidence: `flag` counts ENGINE 1472 = 8×184, TRIM 1104 = 6×184; display text templated per key). See Change C-03. ### 3.10 `body_type_category_sections` — KEEP (normalized) ```sql CREATE TABLE body_type_category_sections ( id bigint unsigned NOT NULL AUTO_INCREMENT, pcm_id bigint unsigned NOT NULL, body_type_id bigint unsigned NOT NULL, section_key varchar(128) NOT NULL, PRIMARY KEY (id), UNIQUE KEY uq_sections_pcm_id (pcm_id), UNIQUE KEY uq_sections_identity (body_type_id, section_key), CONSTRAINT fk_sections_body FOREIGN KEY (body_type_id) REFERENCES body_types(id) ON DELETE CASCADE, CONSTRAINT fk_sections_definition FOREIGN KEY (section_key) REFERENCES section_definitions(section_key) ); ``` Dropped columns vs PCM: `display_name`, `description`, `flag` (→ `section_definitions`), `elements_json` (proven mirror of `body_type_section_elements`, C-04). ### 3.11 `body_type_section_elements` — KEEP ```sql CREATE TABLE body_type_section_elements ( id bigint unsigned NOT NULL AUTO_INCREMENT, pcm_id bigint unsigned NOT NULL, section_id bigint unsigned NOT NULL, element_type varchar(16) NOT NULL, -- 'feature' | 'data' (lowercase, unified — D-9) code varchar(255) NOT NULL, -- e.g. 'rain_sensor', 'gears_no_free' unit_code varchar(64) NULL, -- data rows only; FK → data_units (C-08) description text NULL, -- human label; single (non-localized) text sap_code varchar(255) NULL, -- 3735 non-null, feature rows only; SAP linkage sort_order int NOT NULL, PRIMARY KEY (id), UNIQUE KEY uq_section_elements_pcm_id (pcm_id), UNIQUE KEY uq_section_elements_identity (section_id, code, unit_code), KEY idx_section_elements_code (code), CONSTRAINT fk_section_elements_section FOREIGN KEY (section_id) REFERENCES body_type_category_sections(id) ON DELETE CASCADE, CONSTRAINT fk_section_elements_unit FOREIGN KEY (unit_code) REFERENCES data_units(code), CONSTRAINT chk_section_elements_type CHECK (element_type IN ('feature','data')) ); ``` `unit_code` nullable — NULL exactly for the 16661 feature rows, non-null for the 6684 data rows (evidence). Search: `idx_section_elements_code` serves exact code lookup; name search on `description` is a LIKE scan (23k rows; fulltext deliberately not added — mixed-language text, UNKNOWN language per column; revisit if search becomes a requirement). ### 3.12 `body_type_feature_includes` — KEEP (absorbs `body_type_rules`) ```sql CREATE TABLE body_type_feature_includes ( id bigint unsigned NOT NULL AUTO_INCREMENT, pcm_id bigint unsigned NOT NULL, body_type_id bigint unsigned NOT NULL, parent_feature_code varchar(255) NOT NULL, -- pack/rule label, e.g. 'STYLE', 'cross_look' included_feature_code varchar(255) NOT NULL, -- feature code implied by the parent PRIMARY KEY (id), UNIQUE KEY uq_feature_includes_pcm_id (pcm_id), UNIQUE KEY uq_feature_includes_identity (body_type_id, parent_feature_code, included_feature_code), CONSTRAINT fk_includes_body FOREIGN KEY (body_type_id) REFERENCES body_types(id) ON DELETE CASCADE ); ``` Holds all 666 PCM rows — `body_type_rules.rules_json` contains exactly the same 666 pairs (set-equality proven in both directions, §0), so the merge adds 0 rows and the rules table carries no extra pair data. The 67 empty-list pack declarations from `rules_json` have no pair representation (X-03). Change C-02. ### 3.13 `data_units` — NEW (normalized vocabulary, 27 rows) ```sql CREATE TABLE data_units ( code varchar(64) NOT NULL, -- exact PCM unit string: 'Millimeter', 'Freetext', 'Units', … PRIMARY KEY (code) ); ``` One row per distinct unit string observed in BOTH `entity_data_values.unit` (27) and `body_type_section_elements.unit` (same 27, verified). Natural PK. Unit strings are display labels as shipped by the vendor ("Freetext", "Units" are not physical units — semantics UNKNOWN, kept verbatim). Change C-08. ### 3.14 `structure_categories` — KEEP ```sql CREATE TABLE structure_categories ( id bigint unsigned NOT NULL AUTO_INCREMENT, pcm_id bigint unsigned NOT NULL, market_code varchar(8) NOT NULL, brand_code varchar(64) NOT NULL, code varchar(128) NOT NULL, -- e.g. 'OPTIONALS_AB_HIER', 'safety_security' name_de varchar(512) NULL, -- ← name_json.'de-CH' name_fr varchar(512) NULL, name_it varchar(512) NULL, disclaimer_de varchar(512) NULL, -- ← category_disclaimer_json.'de-CH' (38/183 rows non-null) disclaimer_fr varchar(512) NULL, disclaimer_it varchar(512) NULL, PRIMARY KEY (id), UNIQUE KEY uq_structure_categories_pcm_id (pcm_id), UNIQUE KEY uq_structure_categories_identity (market_code, brand_code, code), KEY idx_structure_categories_brand (brand_code), KEY idx_structure_categories_market (market_code), CONSTRAINT fk_structure_categories_brand FOREIGN KEY (brand_code) REFERENCES brands(code), CONSTRAINT fk_structure_categories_market FOREIGN KEY (market_code) REFERENCES markets(code) ); ``` ### 3.15 `structure_elements` — KEEP ```sql CREATE TABLE structure_elements ( id bigint unsigned NOT NULL AUTO_INCREMENT, pcm_id bigint unsigned NULL, -- NULL only for the 2 reconstructed rows (C-12); else NOT NULL market_code varchar(8) NOT NULL, brand_code varchar(64) NOT NULL, element_type varchar(16) NOT NULL, -- 'feature' | 'data' | 'sub_category' (unified — D-9) code varchar(255) NOT NULL, -- e.g. 'JAL', '018', 'hyundai_smart_sense' name_de varchar(512) NULL, -- ← name_json.'de-CH' name_fr varchar(512) NULL, name_it varchar(512) NULL, PRIMARY KEY (id), UNIQUE KEY uq_structure_elements_pcm_id (pcm_id), UNIQUE KEY uq_structure_elements_identity (market_code, brand_code, code), KEY idx_structure_elements_brand (market_code, brand_code), KEY idx_structure_elements_code (code), CONSTRAINT fk_structure_elements_brand FOREIGN KEY (brand_code) REFERENCES brands(code), CONSTRAINT fk_structure_elements_market FOREIGN KEY (market_code) REFERENCES markets(code) ); ``` 5154 PCM rows + **2 reconstructed rows** (C-12) to make the `structure_category_items` FK total. ### 3.16 `structure_category_items` — KEEP (hardened) ```sql CREATE TABLE structure_category_items ( id bigint unsigned NOT NULL AUTO_INCREMENT, pcm_id bigint unsigned NOT NULL, category_id bigint unsigned NOT NULL, element_id bigint unsigned NOT NULL, -- NEW FK → structure_elements.id (C-12) element_code varchar(255) NOT NULL, -- kept for raw fidelity + identity item_type varchar(16) NOT NULL, -- 'feature' | 'data' | 'sub_category' (unified — D-9) name_de varchar(512) NULL, -- ← name_json.'de-CH' (item-level name; precedence vs element name UNKNOWN) name_fr varchar(512) NULL, name_it varchar(512) NULL, sort_order int NOT NULL, PRIMARY KEY (id), UNIQUE KEY uq_category_items_pcm_id (pcm_id), UNIQUE KEY uq_category_items_identity (category_id, element_code), KEY idx_category_items_element (element_id), KEY idx_category_items_code (element_code), CONSTRAINT fk_cat_items_category FOREIGN KEY (category_id) REFERENCES structure_categories(id) ON DELETE CASCADE, CONSTRAINT fk_cat_items_element FOREIGN KEY (element_id) REFERENCES structure_elements(id), CONSTRAINT chk_category_items_type CHECK (item_type IN ('feature','data','sub_category')) ); ``` Both the item-level `name_*` and the element-level `name_*` are retained — which one the configurator UI displays is UNKNOWN (§11); no precedence rule invented. ### 3.17 `entity_features` — KEEP (polymorphic) ```sql CREATE TABLE entity_features ( id bigint unsigned NOT NULL AUTO_INCREMENT, pcm_id bigint unsigned NOT NULL, entity_type varchar(16) NOT NULL, -- 'engine' | 'trim' | 'variant' (CHECK) entity_id bigint unsigned NOT NULL, -- references engines/trims/variants by type — NO FK (C-09) feature_code varchar(255) NOT NULL, -- 4194 distinct codes; free text (D-12) availability varchar(32) NULL, -- 'Standard' | 'NotAvailable' | 'Optional' | NULL (128 rows kept NULL) surcharge decimal(12,2) NULL, -- only on Optional rows; sentinel 99999999.00 kept raw_value_json json NULL, -- raw vendor payload kept verbatim PRIMARY KEY (id), UNIQUE KEY uq_entity_features_pcm_id (pcm_id), UNIQUE KEY uq_entity_features_identity (entity_type, entity_id, feature_code), KEY idx_entity_features_code (feature_code), CONSTRAINT chk_entity_features_type CHECK (entity_type IN ('engine','trim','variant')), CONSTRAINT chk_entity_features_availability CHECK (availability IS NULL OR availability IN ('Standard','NotAvailable','Optional')) ); ``` `(entity_type, entity_id)` lookups use the UNIQUE prefix. `idx_entity_features_code` serves dictionary-coverage detection (D-12, §12.5). ### 3.18 `entity_data_values` — KEEP (polymorphic) ```sql CREATE TABLE entity_data_values ( id bigint unsigned NOT NULL AUTO_INCREMENT, pcm_id bigint unsigned NOT NULL, entity_type varchar(16) NOT NULL, -- 'engine' | 'trim' | 'variant' (CHECK) entity_id bigint unsigned NOT NULL, -- NO FK (C-09) data_code varchar(255) NOT NULL, -- 919 distinct codes unit_code varchar(64) NOT NULL, -- FK → data_units; NOT NULL (0 nulls in PCM, verified) value varchar(255) NOT NULL, -- free text, never NULL; e.g. '205/45 R17', '6.7' PRIMARY KEY (id), UNIQUE KEY uq_entity_data_pcm_id (pcm_id), UNIQUE KEY uq_entity_data_identity (entity_type, entity_id, data_code, unit_code), KEY idx_entity_data_code (data_code), CONSTRAINT fk_entity_data_unit FOREIGN KEY (unit_code) REFERENCES data_units(code), CONSTRAINT chk_entity_data_type CHECK (entity_type IN ('engine','trim','variant')) ); ``` Multi-unit groups (292 `(entity_type, entity_id, data_code)` groups with >1 unit) are intentional in PCM — the `(data_code, unit_code)` pair is the identity, preserved. `value` is never cast to a numeric type (evidence: tire sizes, free text). Unit misuse anomalies (e.g. `fuel_consumption_combined` in `Millimeter`) are preserved as data; detection in §12.5. ### 3.19 `entity_feature_disclaimers` — KEEP (polymorphic) ```sql CREATE TABLE entity_feature_disclaimers ( id bigint unsigned NOT NULL AUTO_INCREMENT, pcm_id bigint unsigned NOT NULL, entity_type varchar(16) NOT NULL, -- observed: 'trim' (46) | 'variant' (2) — CHECK allows engine too entity_id bigint unsigned NOT NULL, -- NO FK (C-09) feature_code varchar(255) NOT NULL, disclaimer_de varchar(512) NULL, -- ← disclaimer_json.'de-CH' disclaimer_fr varchar(512) NULL, disclaimer_it varchar(512) NULL, PRIMARY KEY (id), UNIQUE KEY uq_entity_disclaimers_pcm_id (pcm_id), UNIQUE KEY uq_entity_disclaimers_identity (entity_type, entity_id, feature_code), CONSTRAINT chk_entity_disclaimers_type CHECK (entity_type IN ('engine','trim','variant')) ); ``` CHECK allows `engine` for future vendor payloads even though 0 engine rows exist today (schema-level flexibility, not an invented entity type — the vocabulary is PCM's own). ### 3.20 `pcm_imports` — NEW (infrastructure, import provenance) ```sql CREATE TABLE pcm_imports ( id bigint unsigned NOT NULL AUTO_INCREMENT, api_version varchar(32) NULL, -- ← api_version.version_json.version, if readable (UNKNOWN consumer) status varchar(16) NOT NULL, -- 'running' | 'completed' | 'failed' row_counts json NULL, -- per-table counts of the PCM snapshot used notes text NULL, -- validation results, exceptions, anomalies found started_at timestamp NULL, finished_at timestamp NULL, PRIMARY KEY (id) ); ``` Justified by invariants 6/7 (repeatable, validated migration): every import run is recorded with the source snapshot counts and the verification results (§12). This is migration infrastructure, not a domain concept — it carries no PCM business data. `api_version` is recorded here (not as a domain table) so the vendor API version that produced a snapshot is preserved if readable — the PCM table itself is excluded (§10.3). --- ## 4. Localization convention (one coherent convention) **Decision: every localized PCM JSON map becomes three per-language columns `{col}_de`, `{col}_fr`, `{col}_it`.** This is the single convention for ALL localized content in product_db. | PCM column | product_db columns | |---|---| | `models.name_json` | `models.name_de/fr/it` | | `model_years.name_json` | `model_years.name_de/fr/it` | | `body_types.body_type_name_json` | `body_types.name_de/fr/it` | | `engines.engine_name_translation_json` | `engines.name_de/fr/it` | | `variants.variant_disclaimer_json` | `variants.disclaimer_de/fr/it` | | `entity_feature_disclaimers.disclaimer_json` | `entity_feature_disclaimers.disclaimer_de/fr/it` | | `structure_categories.name_json` | `structure_categories.name_de/fr/it` | | `structure_categories.category_disclaimer_json` | `structure_categories.disclaimer_de/fr/it` | | `structure_elements.name_json` | `structure_elements.name_de/fr/it` | | `structure_category_items.name_json` | `structure_category_items.name_de/fr/it` | Rules of the convention: 1. **Locale key mapping**: JSON keys `de-CH`/`fr-CH`/`it-CH` → columns `_de`/`_fr`/`_it` (market `CH` fixed; evidence: only these keys occur). If an import ever encounters an unknown locale key, the import **fails with a report** — never silently drops it. 2. **All three columns are nullable, no defaults.** PCM locale coverage is sparse and unequal (e.g. models: de-CH 67/75, fr-CH 65/75, it-CH 65/75; structure elements: 5151/5137/5135 of 5154). The "DE required, FR/IT optional" rule from the legacy cockpit applies to cockpit *content entry forms* (documents, notifications — evidence in existing-system.md), NOT to the catalog: catalog German coverage is incomplete, so `name_de` must not be NOT NULL. 3. **No separate localizations table.** Rationale: single fixed market `CH` with a closed three-locale set (evidence: no other locale keys ever observed), read-mostly snapshot catalog, no per-locale write workflows. A generic `localizations(entity, id, field, locale, value)` table would add joins and a second polymorphic layer with no measured benefit. Cost of the chosen convention: adding a locale later requires an ALTER (acceptable for a 3-locale CH-only catalog; documented risk). 4. **Per-language columns are indexable** (varchar(512), utf8mb4_unicode_ci) — German-name search via `LIKE 'x%'` on `name_de` is supported without JSON extraction. 5. **Non-localized raw JSON stays JSON**: `raw_value_json`, `salesforce_id_json`, `range_json`, `pcm_imports.row_counts` — these are vendor payloads with UNKNOWN or non-text semantics; normalizing them would guess meaning. 6. **Non-localized text stays single-column text**: `description` (all hierarchy tables), `engine_name`, `trim_name`, section `display_name`/`description`, section element `description` — PCM stores these as single non-localized strings; kept as-is. 7. **Display-name resolution is an application-layer accessor**, not a stored column (see §7): `COALESCE(name_de, name_fr, name_it, description, code)` per entity. The precedence is a UI decision, not schema truth. ## 5. Status convention - **One vocabulary across all six hierarchy tables**: `varchar(32) NOT NULL` + CHECK `IN ('Current','Draft','Past','Archived')` — the exact PCM values, verified identical in all six tables (models, model_years, body_types, engines, trims, variants). - **NOT NULL**: PCM declares `DEFAULT NULL` but every row carries a status (0 nulls verified in all six tables) → enforced NOT NULL. No default is declared (no invented default semantics); an import missing a status fails validation. - **Enum column, not a lookup table.** Rationale: the vocabulary is closed (4 values, stable across 133k rows), carries no attributes, and a `statuses` lookup would add a join with zero information gain. The values are data from the vendor feed, so they stay varchar with a CHECK (matching how PCM stores them); the Laravel side maps them to a shared PHP enum. - **Semantics**: `Current` = live range; `Past` = previous model years (evidence: retired year_keys like 2022–2024 are Past); `Archived` = retired (small counts); `Draft` = in preparation. The exact operational distinction Draft vs Archived vs Past is **UNKNOWN** (§11) and is not interpreted by the schema. - `flag` (body_types: `None`/`New`) and `vehicle_group` (`PV`) are NOT statuses — they are vendor attributes with too little vocabulary evidence for CHECK constraints; kept as plain `varchar(32) NOT NULL` (§3.5, §11). ## 6. Audit fields - **All domain tables get `created_at` / `updated_at`** (`timestamp NULL DEFAULT NULL`, maintained by Laravel). These record product_db import/record times — they are NOT claims about PCM history. - **No publish / status-transition timestamps.** PCM contains zero temporal columns (evidence: SHOW CREATE TABLE on all 20 tables). Status transitions, import times and publish dates are not reconstructable from PCM — inventing `published_at`/`status_changed_at` would fabricate data (invariant 3/10). If the business later needs publish timing, it must come from the vendor or from product_db's own write events — a future decision, not this one. - **`pcm_imports`** records each import run (source API version if readable, per-table counts, verification results) — the audit trail for repeatability and integrity validation (invariants 6/7). ## 7. Derived fields Only two derivations are made, both fully evidenced: 1. **`section_definitions` (14 rows)** — extracted from the 184×-duplicated templated `display_name`/`description`/`flag` values of `body_type_category_sections`. This is a normalization, not a guess: the 14 distinct section_keys each have exactly one distinct (flag, display_name, description) triple (Change C-03; validation proves it). 2. **Display-name accessor (application layer, not stored)** — `COALESCE(name_de, name_fr, name_it, description, code)` for models/model_years/body_types; `COALESCE(name_de, name_fr, name_it, engine_name, engine_key)` for engines; `COALESCE(trim_name, trim_key)` for trims; `name` for variants (variants have no localized name). Rationale for not storing: the fallback chain is a UI concern; PCM's sparse locale coverage makes a stored copy a redundancy that can drift on import. **Deliberately NOT derived** (documented to prevent future "improvements" that would violate invariant 3): - **Variant canonical name** — 72/1080 rows deviate from `trim_key X engine_key`; the generation rule is UNKNOWN (§11). Deriving a canonical name would either discard the 72 real names or guess the rule. `name` is kept verbatim. - **Model year as integer** — `year_key` contains non-year values (`2025DemoRalf`, `MY26`, `DOBLO`). No parsing. - **Status effective ranges** — no timestamps exist (§6). - **Price validation/normalization** — sentinels are kept as data (user decision #2). ## 8. Data quality handling The schema is designed so PCM anomalies are **preserved, detectable, and reportable** — never silently "cleaned" (user decision #2, invariants 3/5). | Anomaly (evidence) | Schema handling | |---|---| | `availability` NULL × 128 rows with `raw_value_json={"Optional":"Free"}` | Column nullable + CHECK allows NULL; rows kept verbatim. Detectable: `WHERE availability IS NULL` → exactly 128. | | Surcharge sentinel `99999999.00` (2 rows) | `decimal(12,2)` NULL; kept. Detectable: `WHERE surcharge = 99999999.00`. | | Price sentinels `99999.00` (2 rows), `0.00` (11 rows) | `decimal(12,2) NOT NULL`; kept. Detectable: equality queries. | | Out-of-dictionary feature codes (14948/69098 trim rows, 21.6%) | No FK from `entity_features.feature_code` to any dictionary (neither dictionary covers the code space; D-12). Detectable: the §12.5 coverage queries reproduce the 21.6% number. | | Out-of-dictionary data codes (137) | Same approach (no FK, detection query). | | Unit free-text + misuse (`fuel_consumption_combined` in `Millimeter`; `battery_capacity` in two units) | Vocabulary normalized to `data_units` + FK (values themselves unchanged). Misuse is data, still present; detectable via per-data_code unit-consistency query (§12.5). | | Empty string vs NULL | Preserved: `description` columns are nullable and empty strings are imported as empty strings (e.g. 226/479 engines). No `IF NULL->''` or `''->NULL` coercion. | | Non-year `year_key` / `model_key` values | Verbatim varchar(128); UNKNOWN semantics documented (§11). | | Shared Salesforce ids (19 prod ids / 59 body_types) | JSON kept raw; the 1:n mapping is data-level UNKNOWN, not constrained away. | | Case-inconsistent `element_type` (`feature`/`data` vs `Feature`/`Data`/`SubCategory`) | Unified to lowercase `feature`/`data`/`sub_category` with CHECK (Change C-05) — a deliberate, validated transformation, not data loss. | | Duplicate inclusion data (`rules_json` vs `feature_includes`) | Deduplicated by merging (Change C-02, set-equality proven). | | Templated boilerplate (sections) | Extracted to `section_definitions` (Change C-03). | | Dead payloads (`mapping_json`, empty `elements_json`) | `mapping_json` → table excluded (§10); `elements_json` → column dropped (proven mirror, C-04). | | Polymorphic integrity (no FK possible) | CHECK on `entity_type` + import-time orphan validation (0 orphans today, re-verified every import, §12.4). Risk note in C-09. | --- ## 9. Changes vs PCM (problem / change / reason / risk / migration / validation) Every divergence from the PCM schema is listed here. Everything not listed is a 1:1 KEEP (column names, types where noted in §3, nullability as documented). "Migration" = the read-only PCM → product_db import step; it is idempotent (upsert by `pcm_id`) and repeatable. ### C-01 JSON locale maps → per-language columns - **Problem**: 10 columns store locale maps as JSON with keys `{de-CH,fr-CH,it-CH}`; JSON is not indexable, not validated per locale, and mixes raw vendor payload with UI-facing text. - **Change**: every localized JSON map → `{col}_de/_fr/_it` (varchar(512) NULL), mapping `de-CH→_de`, `fr-CH→_fr`, `it-CH→_it` (§4). Raw payload JSON (`raw_value_json`, `salesforce_id_json`, `range_json`) is untouched. - **Reason**: one coherent convention for all localized content (design brief), legacy cockpit parity (`title_DE/FR/IT`, DE-required/FR-IT-optional UI), indexable German search, no joins, no second polymorphic layer; locale set is proven closed at 3. - **Risk**: new locale requires ALTER + new columns; unknown JSON keys would be lost — the importer **fails loudly** on unknown keys instead of dropping them. - **Migration**: for each row: `name_de = JSON_EXTRACT(col,'$.de-CH')` (or NULL when absent/ `{}`); same for fr/it; assert key set ⊆ {de-CH,fr-CH,it-CH} per row. - **Validation**: per-table per-locale non-null counts in product_db equal JSON key-presence counts in PCM (e.g. models: de 67, fr 65, it 65 of 75; structure_elements: 5151/5137/5135 of 5154). Value-content spot-check: 100 random rows, `JSON_UNQUOTE(...)` == column value. ### C-02 `body_type_rules` merged into `body_type_feature_includes` - **Problem**: inclusion semantics stored twice — `body_type_rules.rules_json.includes` ({pack → [codes]}) and relational `body_type_feature_includes` (parent → included); analysis flagged redundancy. - **Change**: `body_type_rules` is dropped; `body_type_feature_includes` is the single store. - **Reason**: proven exact duplicates for all pair data — `rules_json` holds exactly 666 `(parent, included)` pairs and `body_type_feature_includes` holds 666 rows; 0 pairs missing in either direction per body_type (verified 2026-08-10). The relational form is the canonical one (indexed, queryable); the JSON is reconstructable 1:1 from it. - **Risk**: (a) a future vendor payload could add non-`includes` top-level keys (e.g. `excludes`) — importer rejects unexpected keys and reports, never silently accepts; (b) **empty-list pack declarations** (`pack: []`, 67 occurrences across 39 rows, 30 pack names incl. `test_paket_ralf`) carry zero pair data and are not representable as pairs — documented exclusion X-03 with counts; if the vendor later populates those packs, pairs appear in `body_type_feature_includes` naturally. - **Migration**: import `body_type_feature_includes` 1:1 (666 rows); validate every pair of every non-empty `rules_json` against imported rows (0 missing, 0 extra); count and log empty-list pack declarations in `pcm_imports.notes`; then drop the rules table. 96 rows with `{"includes":{}}` carry no information (empty set, no loss). - **Validation**: pair-set equality queries (§12.6) return 0 missing / 0 extra; pair totals equal (666 = 666); empty-list pack count (67) reported in `pcm_imports.notes`. ### C-03 section boilerplate → `section_definitions` - **Problem**: `body_type_category_sections.display_name/description/flag` are templated — identical text per `section_key` across all 184 body types (e.g. "Engines & transmissions" ×184); 2576× duplicated storage of 14 distinct values. - **Change**: new `section_definitions(section_key PK, flag, display_name, description)` (14 rows); sections table keeps only `section_key` + FK. - **Reason**: normalization of provably redundant data; UI renders the same section headers regardless of body type, so the dictionary is the true source. - **Risk**: if the vendor ever per-body-type-izes display text, the dictionary breaks — but then a body_type-level override column can be added later; today the evidence is 100% templated. - **Migration**: `INSERT INTO section_definitions SELECT DISTINCT section_key, flag, display_name, description FROM body_type_category_sections` (must yield exactly 14 rows); then FK sections.section_key → section_definitions. - **Validation**: `GROUP BY section_key HAVING COUNT(DISTINCT flag||display_name||description) > 1` over PCM returns 0 rows; product_db `section_definitions` count = 14; sections count still 2576. ### C-04 `elements_json` dropped - **Problem**: 1952/2576 sections carry `elements_json` mirroring `body_type_section_elements` rows (same codes/types/units/descriptions/sap_codes) — a second, JSON, copy of the same catalog. - **Change**: column not migrated. - **Reason**: verified 0 mismatches: every element object in `elements_json` resolves to an identical `body_type_section_elements` row (checked across all 1952 non-empty sections, 2026-08-10). The relational table is the authoritative form. - **Risk**: none — JSON is reconstructable; importer re-checks the mirror property and fails on mismatch. - **Migration**: none (column excluded); validation below runs against PCM and product_db. - **Validation**: for every section with non-empty `elements_json`, the element set must equal the section's `body_type_section_elements` set (code, type, unit, description, sap_code); sections with `[]` must have 0 element rows (624 sections). ### C-05 element_type case/vocabulary unification - **Problem**: `body_type_section_elements.element_type` = lowercase `feature`/`data`; `structure_elements.element_type` = TitleCase `Feature`/`Data`/`SubCategory`; `structure_category_items.item_type` = TitleCase — the same concept in three spellings. - **Change**: single normalized vocabulary `feature` / `data` / `sub_category` (lowercase, underscore) with CHECK constraints on all three tables; `item_type` renamed to `element_type` for consistency. - **Reason**: one vocabulary for one concept across both catalogs (design brief: "NORMALIZATION decisions"); kills a proven inconsistency; bounded mapping (2 and 3 values). - **Risk**: negligible — mapping is total and one-to-one; any new vendor value fails the CHECK loudly. - **Migration**: `feature→feature`, `data→data`, `SubCategory→sub_category` during import. - **Validation**: distinct-value counts: section_elements {feature,data} = 2; structure_elements {feature,data,sub_category} = 3; row-count equality per mapped value before/after. ### C-06 surrogate PKs + `pcm_id` identity columns - **Problem**: PCM ids are vendor-owned auto-increment surrogates; a product_db keyed on them would couple identity to the vendor's lifecycle and complicate repeated imports. - **Change**: every entity/join table: `id bigint unsigned AUTO_INCREMENT` PK + `pcm_id bigint unsigned NOT NULL UNIQUE` holding the PCM id; `markets`/`brands` keep natural code PKs (PCM has no surrogate); PCM business keys preserved as UNIQUEs. - **Reason**: repeatable, verifiable migration (invariants 6/7): upsert by `pcm_id`; row-count and id-range checks; stable provenance. - **Risk**: none for data; two "id" columns require discipline in Laravel models (`$primaryKey='id'` default, `pcm_id` as a plain unique column). - **Migration**: copy PCM `id` → `pcm_id`; upsert `ON DUPLICATE KEY UPDATE pcm_id`. - **Validation**: `COUNT(*)` equal; `MIN(pcm_id)=1`; `MAX(pcm_id)` equals PCM `MAX(id)`; `COUNT(DISTINCT pcm_id) = COUNT(*)`. ### C-07 status hardened (NOT NULL + CHECK) - **Problem**: PCM `status` is `varchar(32) DEFAULT NULL` with 0 null rows in all six hierarchy tables; values unconstrained. - **Change**: `varchar(32) NOT NULL` + CHECK `IN ('Current','Draft','Past','Archived')` on models, model_years, body_types, engines, trims, variants. - **Reason**: enforced, uniform lifecycle vocabulary across the hierarchy (§5). - **Risk**: vendor introducing a 5th status value → import fails with report (desired). - **Migration**: none (values unchanged); constraint added at schema build. - **Validation**: distribution query equality with PCM per table (e.g. models 54/13/4/4). ### C-08 units normalized to `data_units` FK - **Problem**: `unit` is free text on two tables (27 distinct strings each, same vocabulary); two proven misuse cases (unit ≠ data semantics); no way to validate unit-per-data_code consistency. - **Change**: new `data_units(code PK)` with the 27 observed strings; `entity_data_values.unit` → `unit_code varchar(64) NOT NULL` FK; `body_type_section_elements.unit` → `unit_code varchar(64) NULL` FK (NULL = feature rows). - **Reason**: single vocabulary for both unit-carrying tables; FK integrity; enables the unit/data_code consistency detection query (§12.5). Strings themselves unchanged → no data loss, misuse preserved as data (user decision #2). - **Risk**: vendor emits a 28th unit → import fails with report (desired); "Freetext"/"Units" are labels, not physical units — kept verbatim, semantics UNKNOWN. - **Migration**: seed `data_units` from `SELECT DISTINCT unit` of both tables; map rows; assert both source vocabularies ⊆ data_units. - **Validation**: `data_units` count = 27; `entity_data_values.unit_code` 0 NULLs; feature-row `unit_code` 100% NULL (16661 rows) and data-row 100% NOT NULL (6684); per-data_code unit-consistency query reproduces the 2 known misuse pairs. ### C-09 polymorphic tables: CHECK + no FK (integrity strategy) - **Problem**: `entity_features` / `entity_data_values` / `entity_feature_disclaimers` use `entity_type` + `entity_id` with no FK; MySQL 8 cannot express a conditional FK; current integrity is manual (0 orphans, verified). - **Change**: schema keeps the polymorphic shape (per brief decision D-5) and adds CHECK `entity_type IN ('engine','trim','variant')` + `(entity_type, entity_id)` indexes (UNIQUE prefix). No split into typed tables. - **Reason**: PCM vocabulary is closed (0 other values); splitting into 9 tables triples schema surface, complicates variant/trim/engine queries and future feed changes for zero measured benefit; integrity is perfect today and enforced by import-time validation. - **Risk**: no FK ⇒ future orphan rows possible if the vendor feed corrupts ids — mitigated by mandatory orphan validation per import (§12.4) that FAILS the import on orphans. - **Migration**: 1:1 copy; `entity_id` values unchanged; orphan check before commit. - **Validation**: the 9 orphan queries (§12.4) return 0; `entity_type` distributions match PCM (trim 69098 / engine 4053 / variant 219; data: engine 18536 / trim 1089 / variant 325). ### C-10 audit columns - **Problem**: no table has any timestamp; import history is unreconstructable. - **Change**: `created_at`/`updated_at` on all domain tables + `pcm_imports` provenance log. - **Reason**: invariants 6/7; §6. - **Risk**: none. **Migration/validation**: trivial (columns empty on first load; filled by importer/Laravel). ### C-11 JSON columns typed as native `JSON` (was `longtext` + json_valid CHECK) - **Problem**: MariaDB stores JSON as longtext with an unenforced CHECK; MySQL 8 offers a native validated type. - **Change**: `raw_value_json`, `salesforce_id_json`, `range_json`, `pcm_imports.row_counts` → MySQL `JSON` type. - **Reason**: native validation (json_valid on write), JSON path functions, no schema drift. - **Risk**: MariaDB→MySQL JSON re-validation can reject syntactically invalid payloads — importer pre-validates and reports instead of dropping (0 invalid payloads observed). - **Migration**: pass-through with `json_valid()` assertion per row. - **Validation**: `COUNT(*) WHERE NOT JSON_VALID(col)` = 0 per JSON column (both sides); value equality spot-checks. ### C-12 `structure_category_items` gains `element_id` FK (+2 reconstructed elements) - **Problem**: `structure_category_items.element_code` (4637 distinct) is almost — but not fully — consistent with `structure_elements.code`: 2 item codes have no dictionary entry, so a direct FK is impossible today. - **Change**: new `element_id bigint unsigned NOT NULL` FK → `structure_elements(id)`; the 2 missing element rows are reconstructed into `structure_elements` from the item rows themselves (code, element_type←item_type, name from item's name map), keeping `element_code` for raw fidelity. - **Reason**: "FK where feasible" (invariants/data-quality brief): the structure catalog is 99.96% internally consistent — the FK is achievable and makes the dictionary authoritative; the 2 rows are data-derived, not invented (their content exists in the item rows). - **Risk**: reconstructed rows have no PCM `pcm_id` (PCM has no matching row) — handled by making `pcm_id` on `structure_elements` **nullable** for these 2 rows (documented exception; all other rows NOT NULL); if the vendor later supplies the rows, the reconstruction is replaced by the real ones (pcm_id backfilled, validated). - **Migration**: import 5154 rows; identify the 2 dangling item codes (query: `NOT EXISTS`); insert reconstructed elements with `pcm_id = NULL`; map item rows to element ids; assert 0 unresolved. - **Validation**: `COUNT(*) FROM structure_category_items c LEFT JOIN structure_elements e ON e.id = c.element_id WHERE e.id IS NULL` = 0; elements with NULL pcm_id = exactly 2; category_items count 5129; the 2 exceptional codes documented in `pcm_imports.notes`. ### C-13 NOT NULL enforcement where PCM data is provably complete - **Problem**: PCM declares many columns nullable that are 100% populated (verified 0 nulls), and one column (`value`) "nullable" that is never NULL. - **Change**: `test_drive`, `flag`, `vehicle_group` (body_types), `name`/`price` (variants), `value` (entity_data_values), `unit_code` (entity_data_values, C-08), `status` (C-07), `sort_order` (both catalogs) → NOT NULL. - **Reason**: schema reflects reality; NULL would misrepresent absence where absence is impossible in the evidence; empty-string columns (`description`, 226/479 engines) stay nullable-with-empty — no coercion either way. - **Risk**: vendor starts emitting NULL → import fails with report (desired). App-level: the ORM sees NOT NULL columns as always present. - **Migration/validation**: 0-NULL counts re-verified per import per column. ### C-14 evidence-based column sizing (varchar/text widths) - **Problem**: PCM uses `longtext` for JSON (dropped by C-01) and `text`/`varchar(255)` for display text with no length evidence. - **Change**: localized names/disclaimers → `varchar(512)` (max observed 371/178), codes/keys keep PCM widths, `value`/`sap_code` keep 255, hierarchy `description` → `text`. - **Reason**: §0 sizing evidence; varchar(512) utf8mb4 (2048 bytes) stays under the 3072-byte index limit so name columns remain indexable; text for non-indexed prose. - **Risk**: a future value longer than 512 in a name column fails import with report. - **Migration/validation**: `MAX(CHAR_LENGTH(...))` per imported column ≤ declared width. --- ## 10. Excluded items Anything excluded has a documented reason; nothing is dropped silently (invariant 5). | # | Item | Type | Reason | |---|---|---|---| | X-01 | `body_type_marketing_mapping` | whole table (184 rows) | All 184 `mapping_json` payloads are `{"features":{},"data":{}}` (proven via JSON_LENGTH = 0 on both keys). Schema with zero information content; purpose UNKNOWN (§11). Re-introducible later without data impact if the vendor starts populating it. | | X-02 | `api_version` | whole table (1 row) | Cache/version marker (`{"version":"1.11.0"}`); consuming system UNKNOWN. Not domain data. The version string is recorded into `pcm_imports.api_version` per import run, so the information is preserved where it matters (import provenance). | | X-03 | `body_type_rules` | whole table (184 rows) | Pair data fully merged into `body_type_feature_includes` (C-02) — 666 pairs proven set-equal, so pair data is preserved 1:1. The 67 **empty-list pack declarations** (`pack: []`, 39 rows, 30 pack names incl. vendor test data `test_paket_ralf`) are not representable as pairs and carry no inclusion data — excluded with counts logged in `pcm_imports.notes` (risk documented in C-02). `{"includes":{}}` rows (96) carry no information. | | X-04 | `body_type_category_sections.elements_json` | column | Proven mirror of `body_type_section_elements` (C-04); reconstructable. | | X-05 | `body_type_category_sections.display_name`, `description`, `flag` | columns | Templated boilerplate; extracted to `section_definitions` (C-03). | | X-06 | JSON keys outside `{de-CH,fr-CH,it-CH}` | hypothetical | Would abort import with report (C-01) — a fail-loud exclusion, not a silent one. | **Full column-coverage proof**: every remaining PCM column is listed in §3 (tables 3.1–3.19) and accounted for: either kept 1:1 (same name/type) or transformed with a change id in §9. Columns transformed: all `*_json` locale maps (C-01), `unit` (C-08), `item_type` (C-05), `elements_json` (X-04), section boilerplate (X-05), polymorphic tables (C-09), status (C-07), `price`/`name`/etc. NOT NULL (C-13). No other column is dropped or renamed. --- ## 11. UNKNOWN items (marked, not guessed) Carried from the evidence and design work; each is a deliberate "do not interpret" decision: 1. **Status semantics**: operational distinction between `Draft` vs `Archived` vs `Past` (all six tables share the vocabulary; exact meaning per status not provable — no timestamps). 2. **`body_type_marketing_mapping` purpose** (excluded table; all payloads empty; legacy placeholder?). 3. **`api_version` consumer and update trigger** (excluded; recorded per import instead). 4. **`range_json` value semantics** — 7 distinct values (`suv`, `ioniq`, `iRange`, `electro`, `personalCars`, `commercialCars`, `[]`) — likely marketing range tags. 5. **Variant `name` generation rule** — 72/1080 rows deviate from `trim_key X engine_key`. 6. **Non-year `year_key`/`model_key` values** (`2025DemoRalf`, `DOBLO`, `MGS6`, `MY26`, `2026.5`, `26`). 7. **`vehicle_group` full vocabulary** (only `PV` observed). 8. **`flag` (body_types) vocabulary** (`None` 183, `New` 1). 9. **Salesforce linkage semantics** — 59 body_types share 19 prod ids; test/prod meaning; no 1:1. 10. **Whether `structure_*` catalogs are the source vocabulary for `entity_features` codes** — overlap partial; 21.6% of trim feature rows out of the body-type section dictionary. 11. **Unit label semantics** — `Freetext`, `Units`, `TireDescription` etc. are display labels, not physical units; the 2 proven misuse pairs (`fuel_consumption_combined`/`Millimeter`, `battery_capacity` in two units) preserved as data. 12. **`availability IS NULL` anomaly resolution** — 128 rows are semantically `Optional (Free)` per `raw_value_json`; kept as-is per user decision #2. 13. **Sentinel price/surcharge meaning** (`99999.00`, `99999999.00`, `0.00` ×11) — placeholders suspected; not interpreted. 14. **Empty-string `description` semantics** (226 engines / 323 trims) — absent text vs intentionally empty; kept verbatim. 15. **`test_drive` flag meaning** (0/1; 133/51). 16. **Item-level vs element-level `name_*` precedence** in `structure_category_items` vs `structure_elements` (both retained). 17. **`sap_code` linkage meaning** (3735 feature rows; SAP material/code; downstream use unknown). 18. **Section element `description` language** — mixed German/English single text, no locale marker. 19. **Why 93% of variants carry no `entity_features`** (1007/1080) — variant feature sets may be derived from trim+engine at display time; not provable from data. 20. **`salesforce_id_json`/`raw_value_json`/`range_json` future key shapes** — raw JSON retained so future shapes do not lose data. 21. **Inclusion-rule pack semantics** — pack labels mix feature codes (`HT: ["HT"]` self-inclusion), empty declarations (`n_line_pack: []`), and vendor test data (`test_paket_ralf`, matching the `2025DemoRalf` year_key) — no uniform rule for what a pack means or how it renders. Pairs are preserved; label semantics UNKNOWN. --- ## 12. Schema verification plan (prove design completeness) Run after every import; a failing check fails the import (`pcm_imports.status='failed'` with `notes`). All numbers are the current PCM snapshot (verified 2026-08-10). ### 12.1 Table + row-count mapping (completeness) | PCM table | PCM rows | product_db table | Expected rows | |---|---|---|---| | markets | 1 | markets | 1 | | brands | 9 | brands | 9 | | models | 75 | models | 75 | | model_years | 145 | model_years | 145 | | body_types | 184 | body_types | 184 | | engines | 479 | engines | 479 | | trims | 597 | trims | 597 | | variants | 1080 | variants | 1080 | | body_type_category_sections | 2576 | body_type_category_sections | 2576 | | body_type_section_elements | 23345 | body_type_section_elements | 23345 | | body_type_rules | 184 | (merged) body_type_feature_includes | 666 (no growth — set-equal) | | body_type_marketing_mapping | 184 | — (excluded X-01) | — | | body_type_feature_includes | 666 | body_type_feature_includes | 666 | | structure_categories | 183 | structure_categories | 183 | | structure_elements | 5154 | structure_elements | 5156 (= +2 reconstructed, C-12) | | structure_category_items | 5129 | structure_category_items | 5129 | | entity_features | 73370 | entity_features | 73370 | | entity_data_values | 19950 | entity_data_values | 19950 | | entity_feature_disclaimers | 48 | entity_feature_disclaimers | 48 | | api_version | 1 | (value) pcm_imports.api_version | per import run | | — | — | section_definitions | 14 (C-03) | | — | — | data_units | 27 (C-08) | ### 12.2 Column-presence mapping For every PCM table, a scripted mapping file lists each column → target (table.column or transformation id C-xx / exclusion X-xx). The importer asserts that the PCM SHOW CREATE TABLE column set equals the expected set exactly (unknown column → fail), and that every expected column is covered. This is the executable form of §9/§10. ### 12.3 Localization extraction checks Per localized column: product_db `_de` non-null count == PCM JSON key-presence count for `de-CH` (same for fr/it). Spot-check 100 random rows per table: extracted value == `JSON_UNQUOTE` value. `{}` JSON → all three NULL. ### 12.4 Referential integrity (orphan checks, all must be 0) The 9 polymorphic checks (entity_data_values / entity_features / entity_feature_disclaimers × engines/trims/variants per entity_type), body_types→model_years, model_years→models, models→brands/markets, engines/trims/variants→body_types, variants→trims/engines, variants trim/engine body_type consistency, section_elements→sections, category_items→ categories (and →elements, C-12), feature_includes→body_types. Reproduces the 0-orphan proof in pcm-data-profile §2. ### 12.5 Anomaly detection checks (preservation proof) - `availability IS NULL` = 128; of those, `raw_value_json = {"Optional":"Free"}` = 128. - `surcharge = 99999999.00` = 2; `price = 99999.00` = 2; `price = 0.00` = 11. - Unit misuse pairs still present: engine 1 `fuel_consumption_combined`/`Millimeter`; engine 8 `battery_capacity` with 2 units. - Out-of-dictionary feature codes: the coverage query from pcm-analysis §4 (trim features vs own body-type section dictionary) = 14948 rows (21.6%); entity feature codes absent from any section element = 621; data codes absent = 137; structure items absent from structure_elements = 0 (was 2 — resolved by C-12 reconstruction, documented in pcm_imports.notes). - Multi-unit groups: 292 `(entity_type, entity_id, data_code)` groups with >1 unit; 996 distinct (data_code, unit_code) pairs vs 919 codes. ### 12.6 Set-equality checks (merged/dropped data) - `rules_json.includes` pairs == `body_type_feature_includes` pairs per body_type: 0 missing, 0 extra, totals equal (666 = 666) — proves C-02 merge lossless; empty-list pack declaration count (67) matches the value logged in `pcm_imports.notes`. - For every section: `elements_json` element set == `body_type_section_elements` set; sections with `[]` == sections with 0 element rows (624) — proves C-04. - `section_definitions` = 14 rows and per-key single (flag, display_name, description) — proves C-03. ### 12.7 Identity mapping checks (pcm_id) - Per table: `COUNT(*)` = `COUNT(DISTINCT pcm_id)`; `MIN(pcm_id)=1`; `MAX(pcm_id)` == PCM `MAX(id)`; all FK-bearing `pcm_id` pairs in child rows resolve to the parent's mapped row (e.g. every `variants.pcm_id`'s trim/engine pcm ids exist in trims/engines with matching mapping). `structure_elements` has exactly 2 rows with NULL pcm_id (C-12). - Business-key uniqueness: all PCM UNIQUE constraints re-asserted in product_db (same definitions, §3) — duplicate audit reproduces pcm-data-profile §2. ### 12.8 Repeatability / idempotence Run the import twice against an unmodified PCM: second run changes nothing (`COUNT(*)` unchanged per table, `pcm_imports` gains one row). Partial-failure recovery: re-run from the failed run's point; upserts by `pcm_id` make this safe. ### 12.9 Laravel implementation notes (design→migration fidelity) - Table names map directly to `Schema::create('models', ...)` etc.; model classes: `VehicleModel` (`$table='models'` — avoids the `Model` base-class name clash), `Market`, `Brand`, `ModelYear`, `BodyType`, `Engine`, `Trim`, `Variant`, `SectionDefinition`, `BodyTypeCategorySection`, `BodyTypeSectionElement`, `BodyTypeFeatureInclude`, `StructureCategory`, `StructureElement`, `StructureCategoryItem`, `EntityFeature`, `EntityDataValue`, `EntityFeatureDisclaimer`, `DataUnit`, `PcmImport`. - No name collisions with the starter-kit tables present in product_db (users, password_reset_tokens, sessions, cache, cache_locks, jobs, job_batches, failed_jobs, personal_access_tokens, migrations, passkeys) — verified against the design table list. - CHECK constraints: Laravel `Schema` supports them (`->check()` / raw DDL) and MySQL 8.0.16+ enforces them; the design's CHECKs are the only ones needed. - `pcm_id` nullable only on `structure_elements` (C-12 exception); everywhere else NOT NULL. - Audit timestamps via `$table->timestamps()`; `pcm_imports` uses `->timestamps()` plus the explicit `started_at/finished_at` columns listed in §3.20.