# PCM Database — Schema & Domain Analysis

Source of truth: live MySQL `pcm` @ 192.168.200.4:3307 (MariaDB), read-only SELECT/SHOW only.
Context consulted: `gauntlet/goal.md`, `../cockpit/AGENTS.md` (legacy cockpit app docs), `../aws_offers_import.sql`, `../aws_services_import.sql`, `../entity_content_assets_import.sql` (exports of the *legacy* `astara_cockpit` DB — NOT pcm tables; used only to understand business domain: Astara dealer org, CH market, de/fr/it locales).

---

## 1. Inventory — table → rows → role classification

| Table | Rows | Size | Role |
|---|---|---|---|
| api_version | 1 | 0.02 MB | cache/version marker |
| brands | 9 | 0.02 MB | lookup |
| markets | 1 | 0.02 MB | lookup |
| models | 75 | 0.06 MB | entity |
| model_years | 145 | 0.03 MB | entity |
| body_types | 184 | 0.08 MB | entity |
| engines | 479 | 0.13 MB | entity |
| trims | 597 | 0.13 MB | entity |
| variants | 1080 | 0.30 MB | entity |
| body_type_category_sections | 2576 | 4.69 MB | entity (per-body-type section catalog) |
| body_type_section_elements | 23345 | 6.55 MB | join/entity (section ↔ element codes) |
| body_type_rules | 184 | 0.06 MB | entity/event (JSON rules) |
| body_type_marketing_mapping | 184 | 0.02 MB | legacy/UNKNOWN (all JSON payloads empty) |
| body_type_feature_includes | 666 | 0.14 MB | join (feature → included features) |
| structure_categories | 183 | 0.09 MB | entity (per-brand display category catalog) |
| structure_elements | 5154 | 2.14 MB | lookup (per-brand code → localized name) |
| structure_category_items | 5129 | 2.16 MB | join (category ↔ elements) |
| entity_features | 73370 | 15.55 MB | event/entity (polymorphic feature values) |
| entity_data_values | 19950 | 6.55 MB | event/entity (polymorphic data values) |
| entity_feature_disclaimers | 48 | 0.05 MB | entity (polymorphic disclaimers) |
| **Total** | **133875** | **~47 MB** | |

All 21 tables: InnoDB, utf8mb4_unicode_ci default; all JSON columns are `longtext` with collation `utf8mb4_bin` + `CHECK (json_valid(...))` (MariaDB CHECK, not enforced FK-style).

**No table has created_at/updated_at or any audit/timestamps column.** This is a snapshot-type staging DB (import target of a vendor feed), not an OLTP system.

---

## 2. Core hierarchy (evidence: FK constraints)

```
markets.code ───────┐
                    ├→ models.id ─→ model_years.id ─→ body_types.id ─┬→ engines.id ─┐
brands.code ────────┘    (market_code,  (model_id,        (model_year_id,  │           │
                          brand_code)     year_key)         body_key)      │           │
                                                               │           ├→ trims.id ─┤
                                                               │           │           │
                                                               │           └→ variants.id (trim_id, engine_id, body_type_id)
                                                               │
                                                               ├→ body_type_category_sections.id ─→ body_type_section_elements
                                                               ├→ body_type_rules (1:1, body_type_id PK)
                                                               ├→ body_type_marketing_mapping (1:1, body_type_id PK)
                                                               └→ body_type_feature_includes
```

All FKs enforced with `ON DELETE CASCADE` on child rows (models→market/brand use plain REFERENCES).

### Polymorphic tables (entity_features, entity_data_values, entity_feature_disclaimers)

`entity_type varchar(16)` ∈ {`engine`, `trim`, `variant`} (verified: 0 rows with other values). No FK to engines/trims/variants; referential integrity manually verified — **0 orphans** for every type/table pair (see §7).

---

## 3. Table-by-table columns & meaning

### Lookups

**markets** (1 row): PK `code varchar(8)` — value `CH`. Meaning: market/locale scope (de-CH/fr-CH/it-CH observed in all JSON name maps).

**brands** (9 rows): PK `code varchar(64)` — `abarth, fiat, fiat_pro, hyundai, jeep, kgm, maxus, mg, nissan`. Car brands distributed by Astara Switzerland.

### Hierarchy entities

**models** (75): `id` PK, `market_code` FK→markets, `brand_code` FK→brands, `model_key varchar(128)` (vendor internal code — e.g. `312`, `332`, `332-302`, `332A`, `DOBLO`, `MGS6`; NOT always numeric), `status`, `description` (display name), `name_json` (locale map). UK `(market_code, brand_code, model_key)`. All 75 models are `CH`; brand distribution: fiat 14, hyundai 14, mg 11, nissan 11, kgm 8, maxus 7, abarth 4, fiat_pro 4, jeep 2.

**model_years** (145): `id` PK, `model_id` FK→models, `year_key varchar(128)`, `status`, `description`, `name_json`. UK `(model_id, year_key)`. **year_key is NOT a clean year**: distinct values incl. `2022, 2023, 2024, 2025, 2025_26, 2025DemoRalf, 2026, 2026.5, 2027, 26, 27, DOBLO, MGS6, MY26`. Meaning: vendor model-year label (can be a real year, a model code, or an MY-style code) — UNKNOWN semantics for non-year values.

**body_types** (184): `id` PK, `model_year_id` FK, `body_key varchar(128)` (e.g. `312`, `312_695_75`, `332`), `status`, `description`, `body_type_name_json` (locale map), `test_drive tinyint(1)` (0: 133, 1: 51), `salesforce_id_json` (see §5; 59/184 non-null), `flag varchar(32)` (`None` 183, `New` 1), `range_json` (marketing range tags — observed `[]` and `["suv"]`; semantics UNKNOWN), `vehicle_group varchar(32)` (`PV` 184/184 — single value). UK `(model_year_id, body_key)`.

**engines** (479): `id` PK, `body_type_id` FK, `engine_key varchar(255)` (e.g. `1.4_ICE_180_Manual`, `PEM`, `BEV_155`), `status`, `engine_name`, `description` (226/479 empty), `engine_name_translation_json` (locale map; 302/479 non-`{}`). UK `(body_type_id, engine_key)`.

**trims** (597): `id` PK, `body_type_id` FK, `trim_key varchar(128)` (e.g. `Anniversario`, `595`, `EH0`), `status`, `trim_name`, `description` (323/597 empty). UK `(body_type_id, trim_key)`.

**variants** (1080): `id` PK, `body_type_id` FK, `trim_id` FK→trims, `engine_id` FK→engines, `status`, `name varchar(512)`, `price decimal(12,2)`, `variant_disclaimer_json` (locale map; 48/1080 non-empty). UK `(body_type_id, trim_id, engine_id)`. A variant = concrete (trim × engine) saleable combination. `name` is usually `trim_key X engine_key` (e.g. `Anniversario X 1.4_ICE_180_Manual`) but 72/1080 rows deviate with real product names (e.g. `500e`, `500e Cabrio`) — generation rule UNKNOWN.

### Per-body-type catalog

**body_type_category_sections** (2576 = 184 × 14): `id` PK, `body_type_id` FK, `section_key varchar(128)`, `display_name`, `description`, `flag varchar(32)` ∈ {ENGINE, TRIM}, `elements_json`. UK `(body_type_id, section_key)`. The 14 section_keys occur exactly 184× each; 8 flagged ENGINE (`engine, battery, brakes, consumption_range, dimensions, performance, tires, weights_volumes`), 6 flagged TRIM (`exterior, exterior_colors, interior, interior_trim, pack, safety_security`). **display_name/description are templated per section_key** (e.g. `Engines & transmissions` / `Engines & transmissions Description`) — identical text for every body_type → redundant data, real value is in section_key + elements_json.

**body_type_section_elements** (23345): `id` PK, `section_id` FK→sections, `element_type varchar(32)` ∈ {`feature` (16661), `data` (6684)} — **lowercase** here vs TitleCase in structure_* tables, `code varchar(255)`, `unit` (only for `data`; null for all features), `description` (human label), `sap_code` (3735 non-null, all on `feature` rows — SAP material/code linkage), `sort_order`. UK `(section_id, code, unit)`; idx on `code`. 1952/2576 sections have ≥1 element (avg 12.0/section, max 89); 624 sections have `elements_json = []`.

**body_type_rules** (184, 1:1 with body_types): PK `body_type_id`, `rules_json` = `{"includes": {packName: [featureCode, ...]}}` (e.g. `{"includes": {"STYLE": [...], "TECH": [...]}}`). Semantics: packs/groups imply inclusion of listed feature codes. Empty `{}` for most (182/184 have empty includes).

**body_type_marketing_mapping** (184, 1:1): PK `body_type_id`, `mapping_json` = `{"features": {...}, "data": {...}}`. **All 184 rows have empty `features` and `data` objects** (verified via JSON_LENGTH = 0) — schema exists, no data → role UNKNOWN/legacy.

**body_type_feature_includes** (666): `id` PK, `body_type_id` FK, `parent_feature_code`, `included_feature_code` (e.g. `cross_look` → `cross_look_underbody_protection`, `16_black_alloy_wheels`). UK `(body_type_id, parent, included)`. Row-level duplicate of the `rules_json.includes` data (both store the same inclusion semantics) — redundancy observed.

### Per-brand display catalogs (configurator UI dictionaries)

**structure_categories** (183): `id` PK, `market_code` FK, `brand_code` FK, `code varchar(128)` (e.g. `OPTIONALS_AB_HIER`, `safety_security`, `comfort`, `driver_assistance`), `name_json`, `category_disclaimer_json` (38/183 non-null, e.g. CO₂ disclaimer text; 145 null). UK `(market_code, brand_code, code)`. Per-brand counts: fiat_pro 26, nissan 22, mg 20, abarth 20, fiat 20, hyundai 19, jeep 19, kgm 19, maxus 18.

**structure_elements** (5154): `id` PK, `market_code` FK, `brand_code` FK, `element_type` ∈ {`Feature` (4065), `Data` (1085), `SubCategory` (4)}, `code varchar(255)` (e.g. `018`, `JAL`, `hyundai_smart_sense`), `name_json`. UK `(market_code, brand_code, code)`. Per-brand dictionary copies: nissan 1175, hyundai 909, fiat 785, mg 656, jeep 352, kgm 342, abarth 333, maxus 304, fiat_pro 298.

**structure_category_items** (5129): `id` PK, `category_id` FK→structure_categories, `element_code varchar(255)`, `item_type` ∈ {`Feature` (4070), `Data` (1056), `SubCategory` (3)}, `name_json`, `sort_order`. UK `(category_id, element_code)`. avg 30.2 items/category (max 196).

**Dictionary overlap (evidence)**: `structure_elements.code` and `body_type_section_elements.code` share much of the same vocabulary (e.g. `JAL`, `rain_sensor`) but are separate per-brand/per-body-type copies with **no FK link** between the two catalogs. Loose coupling — a normalization target for product_db.

### Polymorphic data tables

**entity_features** (73370): `id` PK, `entity_type` + `entity_id` (polymorphic), `feature_code varchar(255)`, `availability` ∈ {Standard 50939, NotAvailable 17059, Optional 5244, NULL 128}, `surcharge decimal(12,2)` (non-null only on Optional rows: 5244), `raw_value_json`. UK `(entity_type, entity_id, feature_code)`.

**entity_data_values** (19950): `id` PK, `entity_type` + `entity_id`, `data_code varchar(255)`, `unit varchar(64)` (27 distinct, e.g. Millimeter 4963, Kilogramm 2709, Freetext 2539, Units 1422, Liter 1007, CO2Emissions 985, Kilowatt 876, HorsePower 657, Torque 613, FuelConsumption 491, KilometerPerHour 470, Meter 450, RevolutionsPerMinute 428, Second 426, EnergyEfficiency 398), `value varchar(255)` (never NULL). UK `(entity_type, entity_id, data_code, unit)`; idx `(entity_type, entity_id)`.

**entity_feature_disclaimers** (48): `id` PK, `entity_type` + `entity_id`, `feature_code`, `disclaimer_json` (locale map, e.g. `{"de-CH": "Nur verfügbar für die Version mit 5 Sitzen", ...}`). UK `(entity_type, entity_id, feature_code)`. Entities: 23 distinct trims (46 rows), 2 distinct variants (2 rows).

**api_version** (1): PK `id tinyint`, `version_json` = `{"version": "1.11.0"}`. API version marker; which consumer reads it — UNKNOWN.

---

## 4. Coverage & relationship semantics (evidence)

| Pair | Rows | Verified |
|---|---|---|
| trims with ≥1 entity_features row | 578/597 | 19 trims have zero features |
| trims with ≥1 entity_data_values row | 293/597 | 304 trims have zero data values (51%) |
| engines with ≥1 entity_features row | 435/479 | 44 engines zero |
| engines with ≥1 entity_data_values row | 461/479 | 18 engines zero |
| variants with ≥1 entity_features row | 73/1080 | 1007 variants zero (93%) |
| variants with ≥1 entity_data_values row | 85/1080 | |
| body_types with 0 engines / 0 trims / 0 variants | 12 / 11 / 14 | of 184 |
| body_types with 0 section elements | 13 | of 184 (sections exist but empty) |

Feature rows per trim: min 1, max 310, avg 119.5. Data rows per trim: min 1, max 19, avg 3.7. Engines per body_type: avg 2.8 (max 15). Trims per body_type: avg 3.45 (max 18). Variants per body_type: avg 6.4 (max 28).

### Dictionary coverage (loose coupling)

- `entity_features.feature_code`: 4194 distinct codes; 621 not present in ANY `body_type_section_elements.code`; 680 not in `structure_elements.code`.
- Stricter per-body-type check: **14948 of 69098 trim feature rows (21.6%) reference a code not present in their own body_type's section element dictionary** (query joined sections via `trims.body_type_id`). The dictionary is partial; many features are assigned to entities without a catalog entry.
- `entity_data_values.data_code`: 919 distinct codes; 137 not in section `data` elements.
- `structure_category_items.element_code`: 4637 distinct; only 2 not in `structure_elements.code` (near-complete internal consistency).
- `body_type_feature_includes`: 8 distinct parent codes and 22 distinct included codes not in section element codes (pack codes like `STYLE` are rule-names, not feature codes — expected).

---

## 5. JSON structure inventory (parsed, keys + frequency)

| Column | Shape | Notes |
|---|---|---|
| *.name_json / body_type_name_json / engine_name_translation_json / trim/var disclaimers | flat locale map `{de-CH, fr-CH, it-CH}` → string | languages never fully populated (e.g. models: de 67, fr 65, it 65 of 75) |
| salesforce_id_json | `{test, prod}` → Salesforce record id | 59 rows; **19 distinct prod ids** (same SF id reused across body types — multi-mapping, see §7) |
| range_json | array of strings, e.g. `[]`, `["suv"]` | semantics of values UNKNOWN (marketing range?) |
| rules_json | `{"includes": {pack: [codes]}}` | 2/184 non-empty |
| mapping_json | `{"features": {...}, "data": {...}}` | 0/184 non-empty → dead schema |
| elements_json | `[{type, code, description, unit?, sap_code?}]` | mirrors body_type_section_elements rows |
| raw_value_json | `{"Optional": {"Surcharge": 1234.0}}` (5244) or `{"Optional": "Free"}` (128) | see §7 anomalies |
| disclaimer_json | locale map | e.g. `{"it-CH": "…", "de-CH": "…", "fr-CH": "…"}` |
| version_json | `{"version": "1.11.0"}` | single row |

---

## 6. Enum-like fields (distinct values + counts)

| Field | Values (count) |
|---|---|
| status (models) | Current 54, Draft 13, Past 4, Archived 4 |
| status (model_years) | Current 105, Draft 23, Past 17 |
| status (body_types) | Current 147, Draft 26, Past 11 |
| status (engines) | Current 447, Past 21, Draft 7, Archived 4 |
| status (trims) | Current 554, Draft 36, Past 6, Archived 1 |
| status (variants) | Current 1037, Past 22, Draft 16, Archived 5 |
| flag (body_types) | None 183, New 1 |
| flag (body_type_category_sections) | ENGINE 1472, TRIM 1104 |
| vehicle_group (body_types) | PV 184 |
| test_drive (body_types) | 0 → 133, 1 → 51 |
| element_type (body_type_section_elements) | feature 16661, data 6684 (**lowercase**) |
| element_type (structure_elements) | Feature 4065, Data 1085, SubCategory 4 (**TitleCase**) |
| item_type (structure_category_items) | Feature 4070, Data 1056, SubCategory 3 |
| entity_type (all 3 polymorphic tables) | engine, trim, variant |
| availability (entity_features) | Standard 50939, NotAvailable 17059, Optional 5244, NULL 128 |
| markets.code | CH (1) |

Status vocabulary is **identical across all 6 hierarchy tables**: `Current / Draft / Past / Archived`. "Past" = previous model years, "Archived" = retired. Exact semantics of Draft vs Archived UNKNOWN but consistent.

---

## 7. Data quality issues (evidence-based)

1. **Unit misuse in entity_data_values**: engine 1 `fuel_consumption_combined` has `unit = "Millimeter"` (value `7.5`; correct unit would be FuelConsumption). Engine 8 `battery_capacity` appears with both `KilowattHours` and `Consumption` (same value 42.2). Unit dictionary (27 values) is free-text, no normalization.
2. **availability NULL ↔ raw_value mismatch**: all 128 rows with `availability IS NULL` carry `raw_value_json = {"Optional": "Free"}` — logically they ARE Optional, but the column wasn't set (should be `Optional` + `surcharge 0`).
3. **Surcharge sentinel**: 48 distinct values; min 0.00, **max 99999999.00** (placeholder sentinel, 1 row).
4. **Price sentinel**: variants.price min 0.00 (11 rows), **max 99999.00** (2 rows — likely placeholders), avg 43070.16.
5. **Shared Salesforce IDs**: 59 non-null salesforce_id_json rows → only 19 distinct prod ids; e.g. body_types 3 and 4 both `a1F7S000000qlByUAI`. SF linkage is not 1:1.
6. **Duplicate inclusion data**: `body_type_feature_includes` rows duplicate `rules_json.includes` (same packs/codes in two stores).
7. **Dangling dictionary references (no FK)**: 14948/69098 trim-feature rows point to codes absent from their body type's section catalog; 621 entity feature codes and 137 data codes absent from any section element code.
8. **Case inconsistency in element_type**: `feature/data` (lowercase) in body_type_section_elements vs `Feature/Data/SubCategory` in structure_* tables.
9. **year_key / model_key dirty values**: `2025DemoRalf`, `DOBLO`, `MGS6`, `MY26`, `2026.5`, `26` — vendor labels leaking into a "year" field.
10. **Templated garbage in sections**: `display_name`/`description` of body_type_category_sections are boilerplate per section_key (identical for all 184 body types).
11. **Empty/dead data**: `mapping_json` all-empty (184 rows); 624/2576 sections have `elements_json = []`; 19 trims / 44 engines / 1007 variants with zero feature rows; 1007/1080 variants (93%) carry no entity_features.
12. **No audit columns anywhere** — cannot reconstruct import history from the DB alone.
13. **Polymorphic references have no FK** — integrity currently perfect (0 orphans), but the DB does not enforce it.

---

## 8. Orphans & duplicates (all queries returned these exact numbers)

| Check | Orphans |
|---|---|
| entity_data_values → engines/trims/variants (per type) | 0 / 0 / 0 |
| entity_features → engines/trims/variants | 0 / 0 / 0 |
| entity_feature_disclaimers → engines/trims/variants | 0 / 0 / 0 |
| body_types.model_year_id → model_years | 0 |
| model_years.model_id → models | 0 |
| models.brand_code / market_code | 0 / 0 |
| engines/trims/variants.body_type_id | 0 / 0 / 0 |
| variants.trim_id / engine_id | 0 / 0 |
| variants trim↔body / engine↔body mismatch | 0 / 0 |
| section_elements.section_id | 0 |
| category_items.category_id | 0 |
| feature_includes.body_type_id | 0 |
| Unknown entity_type values (polymorphic) | 0 |

Duplicates: unique keys prevent exact dups; 292 `(entity_type, entity_id, data_code)` groups legitimately carry multiple units (max 4) — the (data_code, unit) pair is the true key (996 distinct pairs vs 919 codes).

---

## 9. UNKNOWN items (not provable from data)

- Meaning/purpose of `body_type_marketing_mapping.mapping_json` (schema only, all data empty) — legacy placeholder.
- `range_json` value semantics (e.g. `["suv"]`) — likely marketing range tags; UNKNOWN.
- `api_version` consumer and update trigger.
- Variant `name` generation rule (72/1080 rows deviate from `trim X engine` pattern).
- Non-year `year_key` values (`DOBLO`, `MGS6`, `MY26`) interpretation.
- `vehicle_group = "PV"` full vocabulary (only PV observed).
- Whether `structure_*` catalogs are the source for `entity_features` codes (overlap partial, 21.6% unmatched).
- Status semantics distinction between `Draft` vs `Archived` vs `Past`.