# PCM → product_db Migration QA Report

Independent verification of the `pcm` → `product_db` migration, run directly against the live
databases on 192.168.200.4:3307 (read-only on `pcm`). All queries were executed by the QA agent
via `PDO("mysql:host=192.168.200.4;port=3307;dbname=...")` — no claims from the migration were
trusted; every number below was re-derived.

Date: 2026-08-10. Verdict: **PASS** (0 failures across 9 check groups).

---

## 1. Row-count matrix (source COUNT(*) vs target COUNT(*))

| PCM table | source | target | delta | notes |
|---|---|---|---|---|
| markets | 1 | 1 | 0 | |
| brands | 9 | 9 | 0 | |
| models | 75 | 75 | 0 | |
| model_years | 145 | 145 | 0 | |
| body_types | 184 | 184 | 0 | |
| engines | 479 | 479 | 0 | |
| trims | 597 | 597 | 0 | |
| variants | 1080 | 1080 | 0 | |
| body_type_category_sections | 2576 | 2576 | 0 | |
| body_type_section_elements | 23345 | 23345 | 0 | |
| body_type_rules | 184 | — (MERGED) | — | pair data proven set-equal with feature_includes (666 = 666, 0 missing, 0 extra per body_type) |
| body_type_marketing_mapping | 184 | — (DEPRECATED) | — | 0/184 rows with payload; zero-information exclusion documented |
| body_type_feature_includes | 666 | 666 | 0 | |
| structure_categories | 183 | 183 | 0 | |
| structure_elements | 5154 | 5156 | **+2** | expected (C-12): exactly 2 reconstructed rows with `pcm_id IS NULL` |
| structure_category_items | 5129 | 5129 | 0 | |
| entity_features | 73370 | 73370 | 0 | |
| entity_data_values | 19950 | 19950 | 0 | |
| entity_feature_disclaimers | 48 | 48 | 0 | |
| api_version | 1 | — (captured) | — | `1.11.0` captured into `pcm_imports.api_version` (both runs) |

Derived dictionaries: `section_definitions` 14, `data_units` 27, `pcm_imports` 2 — all match the
design contract (§12.1).

**+2 reconstructed rows verified in detail** — exactly the 2 documented rows:
`pcm_id = NULL`, market `CH`, brand `kgm`, element_type `feature`, codes `20alloy_pack`
(names: `20"-Leichtmetallfelgen` / `Jantes en alliage léger 20"` / `Cerchi in lega leggera 20"`)
and `TITANIUM_PLUS` (`TITANIUM+-Paket` / `Pack TITANIUM+` / `Pacchetto TITANIUM+`). All six
names match the source `structure_category_items` rows 2944/2950 (`name_json`) byte-for-byte.
Non-NULL `pcm_id` count: 5154, distinct: 5154. PASS.

## 2. Source `pcm` unmodified

- All 20 table counts match the documented data profile exactly (matrix above, column "source").
- Identity sequences unchanged, including the documented deleted-id gaps:
  `structure_elements` MIN=1/MAX=5194 (5154 rows), `structure_category_items` MAX=5138 (5129),
  `entity_features` MAX=73378 (73370). No rows added, none deleted, no renumbering. PASS.

## 3. Transformed data

Locale-extracted columns (`JSON→LOCALE`, C-01) — per-locale non-null count parity
(pcm key-presence → target column count):

| column pair | de | fr | it | rows |
|---|---|---|---|---|
| models.name_json → name_* | 67/67 | 65/65 | 65/65 | 75 |
| model_years.name_json → name_* | 122/122 | 106/106 | 106/106 | 145 |
| body_types.body_type_name_json → name_* | 156/156 | 139/139 | 139/139 | 184 |
| engines.engine_name_translation_json → name_* | 302/302 | 296/296 | 296/296 | 479 |
| variants.variant_disclaimer_json → disclaimer_* | 48/48 | 46/46 | 46/46 | 1080 |
| structure_categories.name_json → name_* | 183/183 | 174/174 | 174/174 | 183 |
| structure_categories.category_disclaimer_json → disclaimer_* | 5/5 | 1/1 | 1/1 | 183 |
| structure_elements.name_json → name_* | 5151/5153* | 5137/5139* | 5135/5137* | 5156 |
| structure_category_items.name_json → name_* | 5129/5129 | 5115/5115 | 5113/5113 | 5129 |
| entity_feature_disclaimers.disclaimer_json → disclaimer_* | 48/48 | 40/40 | 40/40 | 48 |

\* structure_elements target +2 = the 2 reconstructed rows (carry all 3 locales).

**Value-level spot checks (before → after, joined by pcm_id):**
- 20 random structure_elements rows, all 3 locales: 60/60 values equal (e.g. source
  `{"de-CH":"..."}` JSON_UNQUOTE == `name_de`).
- Full-set value equality on all 75 models, 479 engines, 1080 variants, 183 structure_categories
  (name + disclaimer), 5129 structure_category_items, 48 entity_feature_disclaimers:
  **0 mismatches** in every set (missing/surplus/typo: none).

`element_type` / `item_type` normalization (C-05): `Feature→feature`, `Data→data`,
`SubCategory→sub_category` — 0 mismatches on all 23,345 section-element rows and all 5,129
category-item rows. `unit` → `unit_code`: 0 mismatches. PASS.

## 4. Identity & uniqueness

- **pcm_id set equality** (source `id` set == target `pcm_id` set, both directions) on all 14
  tables that carry pcm_id: 0 missing, 0 extra, 0 duplicates per table. structure_elements:
  5154 non-NULL pcm_ids set-equal; exactly 2 NULL (the reconstructed rows, expected).
- **Business-key duplicates** (all 15 design UNIQUE keys, incl. `(entity_type, entity_id,
  data_code, unit_code)`): 0 duplicate groups in target AND 0 in source. PASS.

## 5. Relationships (orphan audit)

All 32 checks = 0 orphans:

- models→brands, models→markets; model_years→models; body_types→model_years
- engines/trims/variants→body_types; variants→trims, variants→engines
- variants trim↔body_type and engine↔body_type cross-consistency: 0 mismatches
- body_type_category_sections→body_types, →section_definitions
- body_type_section_elements→sections, unit_code→data_units (NULL allowed; 0 unknown units)
- body_type_feature_includes→body_types
- structure_categories→brands, →markets; structure_elements→brands, →markets
- structure_category_items→structure_categories, **element_id→structure_elements (C-12): 0** —
  the 2 dangling codes resolve: item 2944 → element 5155 (`20alloy_pack`), item 2950 → element
  5156 (`TITANIUM_PLUS`), both brand kgm, pcm_id NULL
- 9 polymorphic checks (entity_features / entity_data_values / entity_feature_disclaimers ×
  trim/engine/variant): 0 orphans each; 0 unknown entity_type values anywhere. PASS.

## 6. Nullability & sentinels / anomaly preservation

| anomaly | expected | verified |
|---|---|---|
| availability IS NULL | 128 | 128 |
| …of which raw `{"Optional":"Free"}` | 128 | 128 |
| variants.price = 99999.00 (sentinel) | 2 | 2 |
| variants.price = 0.00 | 11 | 11 |
| entity_features.surcharge = 99999999.00 (sentinel) | 2 (G.23) | 2 — pcm_ids 65657, 66773 (documented) |
| Optional rows == surcharge non-null | 5244 | 5244; 0 Optionals without surcharge |
| raw_value_json non-null | 5372 | 5372 |
| dangling item codes preserved | 20alloy_pack, TITANIUM_PLUS | yes (resolve via C-12 elements) |
| empty-list pack declarations | 67 / 39 rows / 30 names | 67 / 39 / 30 (independently re-derived from source `body_type_rules.rules_json`; name list matches `pcm_imports.notes` exactly, 0 diff) |
| unit misuse: fuel_consumption_combined/Millimeter | 1 | 1 (engine 1) |
| unit misuse: battery_capacity 2 units (engine 8) | 2 | 2 |
| multi-unit groups | 292 | 292 |
| empty-string descriptions (no ''→NULL coercion) | engines 226, trims 323, models 1 | 226 / 323 / 1 |
| variants.price avg | 43070.16 | 43070.16 |
| salesforce_id_json non-null / distinct prod ids | 59 / 19 | 59 / 19 |
| sections with `[]` elements_json → 0 element rows | 624 | 624 |
| section_definitions per-key triple uniqueness | 0 violations | 0 |
| data_units | 27 | 27, set-equal with both source vocabularies (edv 27 + bse 27); all target in-use units ⊆ data_units; top counts match (Millimeter 4963, Kilogramm 2709, Freetext 2539, Units 1422) |

PASS.

## 7. Polymorphic & dictionary coverage

- entity_features distribution: trim 69098 / engine 4053 / variant 219 — identical in source
  and target (incl. per-type × availability matrix: trim 47821/15975/5177/125, engine 3068/984/1,
  variant 50/100/67/2).
- Out-of-dictionary trim-feature coverage (features absent from own body_type section catalog):
  **14948 in source == 14948 in target** (21.6% of 69098). PASS.

## 8. Run log (`pcm_imports`)

2 rows, both `status = completed` (2026-08-10 17:23:58→17:24:05 and 17:24:08→17:24:14) —
repeatability demonstrated by a second run against unmodified PCM. Both runs:
- `api_version = "1.11.0"` (captured from api_version, B.20)
- `row_counts` JSON matches the §12.1 contract for all 20 PCM tables (incl. rules 184,
  marketing_mapping 184, api_version 1 — source snapshot counts; structure_elements 5154)
- `notes` record: surcharge sentinel count **2** (G.23), price sentinels, availability-NULL 128,
  raw_value shapes 5244/128, empty-pack declarations 67/39/30 with the full 30-name list,
  reconstructed codes `20alloy_pack`/`TITANIUM_PLUS`, marketing-mapping emptiness proof,
  data_units 27 set-equality, section_definitions 14, "261/261 checks passed" on run 2.

All claimable facts in the notes were independently re-verified against the source in this audit.
PASS.

## 9. Sample records (all columns vs source, surrogate id excluded)

| sample | result |
|---|---|
| model id=1 (abarth `500e`, name_json de-CH) | all 7 columns match |
| variant id=1 (`Anniversario X 1.4_ICE_180_Manual`, 36990.00) | all 8 columns match |
| entity_features id=65657 (surcharge sentinel 99999999.00) | all 7 columns match |
| entity_features id=66773 (surcharge sentinel 99999999.00) | all 7 columns match |
| entity_data_values (engine 1, acceleration_100, Second, "6.7") | all 6 columns match |
| body_type id=9 (cross_look rules body type) | all 11 columns match |

PASS.

---

## Invariant compliance

- **Source unmodified**: no writes performed by this audit; source tables byte-identical in
  counts/ids to the documented pre-migration profile.
- **No silent data loss**: every exclusion (body_type_rules MERGE, marketing_mapping, api_version,
  elements_json, empty packs) is documented and was re-proven (0-payload proof, 666-pair
  set-equality, version capture, 624-section mirror, 67/39/30 counts).
- **Repeatability**: 2 completed runs with identical row_counts and unchanged data.
- **Integrity validation**: run log records per-run verification (261/261 on run 2); all 32
  orphan checks reproduce at 0 in the final state.

## FAIL items

None. All checks passed; the +2 reconstructed structure_elements rows and the pcm_imports
run-log rows are the documented exceptions and were verified as exactly the documented values.

**VERDICT: PASS**