# MechanicDB Relational Data Dictionary

MechanicDB is structured around three core normalized tables (`dtc_codes`, `diagnostic_fixes`, `replacement_parts`) and one pre-joined analytical view (`dtc_fixes_joined`). All string fields are guaranteed to have zero embedded linebreaks (`\r` or `\n` are replaced by ` • `) to ensure exact physical row parity in pipe-delimited (`|`) CSVs and PyArrow Parquet files. CSVs are encoded as `utf-8-sig` (UTF-8 with BOM).

**Row identity:** `dtc_code` alone is unique only within the SAE-universal rows (`is_oem_specific = 0`). For manufacturer-specific rows (`is_oem_specific = 1`), the natural key is the composite `(oem_make, dtc_code)` — the same `dtc_code` value can legitimately recur under different `oem_make` values. This is expected: manufacturer-controlled code ranges (`P1`, `P30–P33`, `C1/C2`, `B1/B2`, `U1/U2`) are assigned per-brand, not globally unique. `code_id` remains the single surface-level primary key for joins.

**Badge-engineering duplication:** marques that share engineering platforms (GM's seven marques, Ford/Mercury/Lincoln, Honda/Acura, and other shared-platform groups — see [SOURCES.md](SOURCES.md)) frequently share the same code definitions verbatim across brands. Rows are kept per marque rather than deduplicated across the group, so make-filtered lookups return complete results for the brand a buyer queries. This sample includes 15 OEM codes (of the full dataset's 6,637) so the OEM columns are populated and testable here too.

---

## 1. `dtc_codes` (Master Code Registry)

Contains definitions for both universal SAE-standard OBD-II trouble codes and manufacturer-specific (OEM) trouble codes, distinguished by `is_oem_specific` and `oem_make`.

| Column Name | Data Type | Description | Example |
| :--- | :--- | :--- | :--- |
| `code_id` | Integer (PK) | Primary key unique identifier for the trouble code record. | `1` |
| `dtc_code` | String (5) | Standardized 5-character OBD-II trouble code. Unique alone for SAE rows; unique only in combination with `oem_make` for OEM rows. | `P0171` |
| `system_category` | String | Automotive system classification (`Powertrain`, `Chassis`, `Body`, `Network`). | `Powertrain` |
| `is_oem_specific` | Integer | Boolean flag: `0` for universal SAE standard, `1` for manufacturer-specific. | `0` |
| `oem_make` | String | Vehicle manufacturer make name (`Ford`, `Toyota`, `BMW`) for OEM rows, or empty string for SAE-universal rows. | `` |
| `fault_family` | String | Slug of the authored fault family grouping codes that share a diagnosis and repair path. | `maf_circuit` |
| `short_description` | String | Concise summary title of the trouble code. | `System Too Lean (Bank 1)` |
| `detailed_technical_explanation` | String | In-depth technical explanation of sensor telemetry and ECM detection logic. | `The engine control module (ECM) detects too much oxygen...` |

---

## 2. `diagnostic_fixes` (Ordered Repair Procedures & Cost Matrix)

Maps diagnostic trouble codes to actionable inspection or repair procedures in authored consideration order, accompanied by aftermarket cost matrices. The companion [`ordering_metadata.json`](ordering_metadata.json) records the applicable machine-readable ordering basis.

| Column Name | Data Type | Description | Example |
| :--- | :--- | :--- | :--- |
| `fix_id` | Integer (PK) | Primary key unique identifier for the repair procedure. | `101` |
| `code_id` | Integer (FK) | Foreign key referencing `dtc_codes.code_id`. | `1` |
| `fix_title` | String | Actionable title of the recommended diagnostic inspection or repair procedure. | `Clean or Replace MAF Sensor` |
| `probability_rank` | Integer | Supported legacy-named positive ordinal giving the authored order for considering procedures within one code. Lower values appear earlier; `1` is first. Apply each procedure's prerequisites and diagnostic findings before acting. It does not measure cause likelihood, repair success, severity, urgency or minimum cost, and values are not comparable across codes. | `1` |
| `difficulty_level` | String | Skill requirement rating (`Easy DIY`, `Moderate DIY`, `Professional Required`). | `Easy DIY` |
| `est_parts_cost_min_usd` | Float, nullable | Editorial estimate (see [Cost basis](#cost-basis)) of the minimum parts cost in US dollars. `0` when `parts_cost_status` is `none_required`; NULL when `unknown`. | `15.00` |
| `est_parts_cost_max_usd` | Float, nullable | Editorial estimate (see [Cost basis](#cost-basis)) of the maximum parts cost in US dollars. `0` when `parts_cost_status` is `none_required`; NULL when `unknown`. | `45.00` |
| `est_labor_hours` | Float, nullable | Editorial estimate of professional labor hours to complete the procedure. NULL when `labor_status` is `unknown`. | `0.5` |
| `step_by_step_instructions` | String | Bulleted step-by-step diagnostic and repair instructions (separated by ` • `). | `1. Disconnect battery • 2. Spray sensor...` |
| `action_type` | String, nullable | What the procedure does: `inspect`, `measure`, `scan`, `repair`, `replace`, `program` or `verify` (definitions in [Cost basis](#cost-basis)). NULL until the procedure's family is reviewed. | `replace` |
| `parts_cost_status` | String | `estimated`, `none_required` or `unknown`. | `estimated` |
| `labor_status` | String | `estimated` or `unknown`. | `estimated` |
| `cost_basis_id` | String | Key into `cost_metadata.json`: `obd-editorial-us-aftermarket-v1` for this tier. | `obd-editorial-us-aftermarket-v1` |
| `procedure_rationale` | String, nullable | Why this step, why its cost status and why its position; NULL until reviewed. | `Fits the part once the earlier steps point to it.` |

Labor class follows `action_type` (`labor_kind_by_action` in `cost_metadata.json`): inspect, measure, scan and verify are diagnostic; repair and replace are repair; program is programming.

---

## 3. `replacement_parts` (Aftermarket Part Mappings)

Maps diagnostic repair procedures to common aftermarket replacement parts and catalog search queries.

A procedure with `parts_cost_status = none_required` has no rows here.

| Column Name | Data Type | Description | Example |
| :--- | :--- | :--- | :--- |
| `part_id` | Integer (PK) | Primary key unique identifier for the replacement part record. | `501` |
| `fix_id` | Integer (FK) | Foreign key referencing `diagnostic_fixes.fix_id`. | `101` |
| `part_name` | String | Descriptive name or title of the replacement sensor, component, or repair kit. | `Mass Airflow Sensor (MAF) Cleaner & Sensor Kit` |
| `amazon_search_query` | String | Direct catalog search URL for acquiring the replacement part on Amazon or auto parts catalogs. | `https://www.amazon.com/s?k=EVAP+purge+solenoid+valve+automotive` |

---

## 4. `dtc_fixes_joined` (Pre-Joined Analytical View)

A denormalized analytical table combining code definitions, ranked fixes, and cost estimation matrices into a single flat file for rapid machine learning ingestion and instant frontend rendering. Contains all columns from `dtc_codes` (including `fault_family`) and `diagnostic_fixes` joined on `code_id`, including the five procedure/cost columns appended in schema version 2.

## 5. OEM coverage by make

<!-- COVERAGE:START -->
OEM (manufacturer-specific) codes number **6,637** across **32** makes, and are **not evenly distributed** — coverage is
strongest on US-market brands. The SAE-universal spine (9,249 codes) applies to
every vehicle regardless of make, so this table describes only the
manufacturer-controlled ranges (`P1`, `P30`–`P33`, `C1`/`C2`, `B1`/`B2`, `U1`/`U2`).

| Make | OEM codes | Make | OEM codes |
| :--- | ---: | :--- | ---: |
| Volkswagen | 528 | Plymouth | 113 |
| Ford | 413 | Dodge | 97 |
| Lincoln | 413 | Jeep | 97 |
| Mercury | 413 | Acura | 94 |
| Buick | 402 | Honda | 94 |
| Cadillac | 402 | Infiniti | 94 |
| Chevrolet | 402 | Subaru | 93 |
| GM | 402 | Kia | 76 |
| GMC | 402 | Nissan | 59 |
| Saturn | 402 | Lexus | 49 |
| Oldsmobile | 401 | Toyota | 45 |
| Chrysler | 282 | Mitsubishi | 33 |
| BMW | 250 | Mercedes-Benz | 32 |
| Mazda | 233 | Geo | 19 |
| Jaguar | 163 | Suzuki | 17 |
| Pontiac | 115 | Audi | 2 |
<!-- COVERAGE:END -->

---

## 6. Heavy Duty tier (J1939) — six tables

MechanicDB Heavy Duty is a **standalone** commercial product, `$149`, sold and delivered separately
from the OBD-II Standard/OEM Complete tiers above (on sale since 2026-09-24, see [README.md](README.md) and the
[J1939 fault code database](https://mechanicdb.dataengineered.io/j1939-fault-code-database) page). It maps SAE J1939 SPN+FMI fault pairs drawn from published OEM fault tables (Eaton,
WABCO, Bendix, John Deere, Navistar, Cummins, Caterpillar, PSI, Perkins, Yanmar) to authored
short descriptions, fault families, explanations, ranked fixes and part mappings — currently **4,212
fault pairs, 870 distinct SPNs, 17,049 ranked fixes and 5,913 part mappings across 10 OEMs and 16
official-host sources**, measured at build time. It follows the same physical conventions as the
tables above: pipe-delimited CSV, `utf-8-sig`, no embedded `\r`/`\n` (replaced with ` • `), Parquet
Snappy, one SQLite.

**This is not the SAE J1939 Digital Annex and contains no SAE text.** Every `j1939_faults` row (and so every `j1939_fixes_joined` row) carries its own
`source_url` to the OEM's published fault table; the parameter/failure-mode facts (SPN, FMI, OEM code)
are reported, but `short_description`, `spn_name`, `fmi_name` and the explanation text are our own
words, never a copy of the source document. The on-highway engine fault lists of Cummins, Detroit,
PACCAR, Volvo/Mack and Cat *trucks* are dealer-gated and are not in this tier.

**Natural key:** (`source_id`, `spn`, `fmi`, `oem_code`) identifies a distinct OEM-published fault row.
The same SPN+FMI pair recurs across OEMs by design — exactly as the same `dtc_code` recurs across
makes in `dtc_codes` above — because each OEM publishes and controls its own fault table; `fault_id`
is the single join key for the other tables.

**Editorial-estimate note:** as with `diagnostic_fixes` above, `difficulty_level`,
`est_parts_cost_min_usd`, `est_parts_cost_max_usd` and `est_labor_hours` in this tier's
`diagnostic_fixes` rows are editorial estimates — here, for **US independent heavy-duty / fleet
shops**, not dealer rates and not a quote. They are not sourced from the OEM documents. The basis is `hd-editorial-us-independent-shop-v1` (see [Cost basis](#cost-basis)), carried by the five procedure/cost columns of 6.4 (`action_type`, `parts_cost_status`, `labor_status`, `cost_basis_id`, `procedure_rationale`).

**Free sample:** [`samples/heavyduty/`](samples/heavyduty/) in this repository ships 100 of the 4,212
faults (deterministically sampled across the full spine, so every one of the 10 OEMs is represented),
their referenced SPNs, ranked fixes, part mappings, and the complete 32-row FMI register — same
schema, same ODbL v1.0 terms as the OBD-II sample above (see [LICENSE](LICENSE)); its source documents are listed in [SOURCES.md](SOURCES.md#heavy-duty-sample-j1939).

### 6.1 `j1939_fmi` (Failure Mode Identifier register, 32 rows)

Authored paraphrase of the 32 SAE J1939 Failure Mode Identifiers, cross-checked against public
technical explainers — never SAE text.

| Column Name | Data Type | Description | Example |
| :--- | :--- | :--- | :--- |
| `fmi` | Integer (PK) | The FMI value, 0–31. | `3` |
| `fmi_name` | String | Our paraphrase of the failure mode. | `Circuit voltage high / short to supply` |
| `failure_class` | String | Normalized failure-mode class used to drive fix ordering (see 6.9 below). One of: `range_high`, `range_low`, `erratic`, `circuit_high`, `circuit_low`, `open_circuit`, `short_ground`, `mechanical`, `frequency`, `update_rate`, `rate_of_change`, `unknown_cause`, `device`, `calibration`, `special`, `network_error`, `drift_high`, `drift_low`, `reserved`, `condition`. | `circuit_high` |
| `is_reserved` | Integer | `1` for FMI 22–30 (SAE-reserved; some OEMs assign proprietary meanings), `0` otherwise. | `0` |

### 6.2 `j1939_spn` (Suspect Parameter Number register — one row per (`spn`, `oem_make`))

**Key:** `j1939_spn` is keyed on (`spn`, `oem_make`), not on `spn` alone. SPNs below `516096` are
standard SAE parameters: one row, `oem_make` empty, a neutral name shared across every OEM that
references the SPN. SPNs `>= 516096` fall in the OEM-controlled naming range — this covers both the
OEM-naming band `516096`–`520191` (OEM-published but not SAE-reserved-proprietary) and the
proprietary range `>= 520192` — and get **one row per OEM that uses the SPN**, each named from that
OEM's own document; the same SPN can therefore appear once per OEM with a different `spn_name` in
each row. `j1939_faults.detailed_technical_explanation` renders `{spn_name}` for a standard SPN
from the shared neutral name and, for `spn >= 516096`, from the fault's own source document,
never another OEM's; when two documents of the same OEM name an SPN differently, this table
keeps the name from the first `source_id` (sorted) and each fault keeps its own document's name.

**In the free sample,** [`samples/heavyduty/j1939_spn.csv`](samples/heavyduty/j1939_spn.csv) holds
every full-tier row for each SPN number the sampled faults reference, so an OEM-range SPN can also
appear under an OEM that has no fault in the sample; `source_count` is the full tier's value, so it
can exceed the number of sources visible in the sample's `j1939_faults.csv`.

| Column Name | Data Type | Description | Example |
| :--- | :--- | :--- | :--- |
| `spn` | Integer (PK part 1) | The J1939 Suspect Parameter Number. | `110` |
| `spn_name` | String | Our paraphrase of the parameter. For a standard SPN, one neutral name shared by every OEM. For `spn >= 516096`, this OEM's own name for the parameter, read from this OEM's document. When the source documents do not state a role for the SPN, the name says so rather than guessing: `Parameter <spn> (role not stated by the OEM tables)` for a standard SPN, `<OEM> parameter <spn> (role not stated)` for an OEM-range SPN. | `Engine coolant temperature` |
| `is_proprietary` | Integer | `1` when `spn >= 520192` (the SAE-reserved proprietary SPN block), `0` otherwise — including for the `516096`–`520191` OEM-naming band, which is OEM-published but not SAE-reserved-proprietary. | `0` |
| `oem_make` | String | Part 2 of the composite key. Empty string for a standard SPN (`spn < 516096`); for `spn >= 516096`, the `oem_make` (same values as `j1939_faults.oem_make`) of the OEM whose document uses the SPN. | `` |
| `system_category` | String | Fixed vocabulary: `Engine`, `Fuel`, `Air Intake`, `Aftertreatment`, `Cooling`, `Lubrication`, `Electrical`, `Transmission`, `Brakes/ABS`, `Body/Cab`, `Network`, `Instrument`. | `Cooling` |
| `source_count` | Integer | Number of distinct `source_id` values behind this row: every source that references the SPN for a standard SPN, that OEM's sources for an OEM-range row. | `4` |

### 6.3 `j1939_faults` (Master Fault Registry — this tier's spine, analogue of `dtc_codes`)

One row per extracted OEM SPN+FMI fault pair.

| Column Name | Data Type | Description | Example |
| :--- | :--- | :--- | :--- |
| `fault_id` | Integer (PK) | Permanent source/controller-scoped fault identity; numeric order is independent of its locator and display order. | `280` |
| `spn` | Integer (FK, with `oem_make` below, → `j1939_spn`) | Join to `j1939_spn` on `spn` plus `oem_make` when `spn >= 516096` (this fault's own OEM row, see 6.2), and on `spn` with an empty `oem_make` below `516096`; joining on `spn` alone repeats a fault once per OEM that uses an OEM-range SPN. | `110` |
| `fmi` | Integer (FK → `j1939_fmi.fmi`) | | `3` |
| `oem_make` | String | One of `Eaton`, `WABCO`, `Bendix`, `John Deere`, `Navistar`, `Cummins`, `Caterpillar`, `PSI`, `Perkins`, `Yanmar`. | `Caterpillar` |
| `oem_code` | String | The OEM's own code for this fault as printed in its document; empty string when the source document does not print one. | `110-3` |
| `controller` | String | Fixed vocabulary: `Engine ECU`, `Aftertreatment DCU`, `Transmission`, `ABS/ESC`, `Body controller`. | `Engine ECU` |
| `short_description` | String | Authored, our words, ≤ 120 characters. Never a copy of OEM text. | `Engine coolant temperature sensor circuit voltage above normal (short to supply)` |
| `fault_family` | String | Slug of the authored fault family that groups this fault with others sharing a diagnosis/repair path; joins `diagnostic_fixes` via the family's authored fixes. | `hde_coolant_temp_sensor` |
| `detailed_technical_explanation` | String | Authored explanation, rendered from the family's template — never OEM prose. | `SPN 110 (Engine coolant temperature) with FMI 3 (Circuit voltage high / short to supply) is set by the Engine ECU when the engine coolant temperature sensor signal leaves its plausible window...` |
| `source_id` | String | Register key for the OEM document this row's fact was read from; listed in [SOURCES.md](SOURCES.md#heavy-duty-sample-j1939). | `cat_c10882888` |
| `source_url` | String | The official OEM document URL this row's fact was read from. | `https://s7d2.scene7.com/is/content/Caterpillar/C10882888` |
| `source_page` | String | Page/section marker within the source document, when the source format carries one. | `102` |
| `source_doc` | String | Human-readable document title/ID. | `1200 Series marine auxiliary engines troubleshooting guide` |

### 6.4 `diagnostic_fixes`

Same shape and column names as the OBD-II tier's `diagnostic_fixes` (section 2 above), except the
foreign key is `fault_id` (this tier has no `code_id`). Difficulty and fix ranking are re-authored for
this tier — see 6.7/6.9 below.

| Column Name | Data Type | Description | Example |
| :--- | :--- | :--- | :--- |
| `fix_id` | Integer (PK) | Primary key. | `101` |
| `fault_id` | Integer (FK → `j1939_faults.fault_id`) | | `1` |
| `fix_title` | String | Actionable title of the recommended inspection or repair. | `Inspect coolant temperature sensor connector` |
| `probability_rank` | Integer | Supported legacy-named positive ordinal giving procedure consideration order within this fault; `1` is first. Execution is conditional on prerequisites and findings. The authored FMI-class policy contributes to the order — see 6.9 — but the value is not a measured likelihood or outcome. | `1` |
| `difficulty_level` | String | Fixed vocabulary for this tier — see 6.7. | `Fleet technician` |
| `est_parts_cost_min_usd` | Float, nullable | Editorial estimate, US independent heavy-duty shop (see [Cost basis](#cost-basis)). `0` when `parts_cost_status` is `none_required`; NULL when `unknown`. | `35.00` |
| `est_parts_cost_max_usd` | Float, nullable | Editorial estimate, US independent heavy-duty shop (see [Cost basis](#cost-basis)). `0` when `parts_cost_status` is `none_required`; NULL when `unknown`. | `120.00` |
| `est_labor_hours` | Float, nullable | Editorial estimate, US independent heavy-duty shop. NULL when `labor_status` is `unknown`. | `0.75` |
| `step_by_step_instructions` | String | Bulleted, ` • `-separated. | `1. Disconnect ECU harness connector • 2. Inspect pins for corrosion...` |
| `action_type` | String, nullable | What the procedure does: `inspect`, `measure`, `scan`, `repair`, `replace`, `program` or `verify`. NULL until the procedure's family is reviewed. | `replace` |
| `parts_cost_status` | String | `estimated`, `none_required` or `unknown`. | `estimated` |
| `labor_status` | String | `estimated` or `unknown`. | `estimated` |
| `cost_basis_id` | String | Key into `cost_metadata.json`: `hd-editorial-us-independent-shop-v1` for this tier. | `hd-editorial-us-independent-shop-v1` |
| `procedure_rationale` | String, nullable | Why this step and why its cost status; NULL until reviewed. In this tier it never states an absolute position (first, last, before, after...): the delivered `probability_rank` is reordered per fault by FMI failure class (6.9), so it says at most "in the authored family order", and it never assumes that another procedure has already run. | `A tool reading buys nothing, so no parts are listed.` |

HD `estimated` rows keep `0 <= min <= max`. A 0/0 `estimated` HD row always lists a part (a zero editorial parts estimate although a part is listed); 0/0 HD rows without parts are `none_required`. Labor class follows `action_type` as in section 2.

### 6.5 `replacement_parts`

Same shape and column names as section 3 above, keyed to this tier's `fix_id`. `amazon_search_query`
is filled only where the part is plausibly retail/aftermarket; dealer-only or fabricated-to-order parts
get an empty string (NULL over guess).

| Column Name | Data Type | Description | Example |
| :--- | :--- | :--- | :--- |
| `part_id` | Integer (PK) | | `501` |
| `fix_id` | Integer (FK → `diagnostic_fixes.fix_id`) | | `101` |
| `part_name` | String | | `Engine Coolant Temperature Sensor` |
| `amazon_search_query` | String | Direct catalog search URL; empty string when the part is dealer-only. | `https://www.amazon.com/s?k=heavy+duty+diesel+coolant+temperature+sensor` |

### 6.6 `j1939_fixes_joined` (Pre-Joined Analytical View)

A denormalized flat table combining `j1939_faults` and `diagnostic_fixes` (joined on `fault_id`) —
every column from both tables in one row, for ML ingestion and instant frontend rendering, mirroring
`dtc_fixes_joined` above, including the five procedure/cost columns appended in schema version 2.

### 6.7 Difficulty vocabulary (this tier only)

The Standard/OEM tiers' DIY-oriented scale (`Easy DIY`, `Moderate DIY`, `Professional Required`) is
wrong for heavy-duty trucks — most fixes require shop tooling, air-brake certification or an OEM
service tool, and "DIY" is not a meaningful buyer segment. This tier's `difficulty_level` uses instead:

| Value | Meaning |
| :--- | :--- |
| `Driver check` | A pre-trip/roadside check any driver can do without tools (visual inspection, connector reseat, DPF regen prompt). |
| `Fleet technician` | Requires shop tools and diagnostic software but not dealer-level equipment; typical in-house fleet maintenance scope. |
| `Dealer / specialist` | Requires OEM service tools, calibration equipment or specialist certification (air brake, aftertreatment); typically outsourced to a dealer. |

### 6.8 Controller and system vocabularies

`controller` (on `j1939_faults`) identifies which vehicle ECU/module owns the fault: `Engine ECU`,
`Aftertreatment DCU`, `Transmission`, `ABS/ESC`, `Body controller`.

`system_category` (on `j1939_spn`) classifies the parameter itself, independent of which controller
reported it: `Engine`, `Fuel`, `Air Intake`, `Aftertreatment`, `Cooling`, `Lubrication`, `Electrical`,
`Transmission`, `Brakes/ABS`, `Body/Cab`, `Network`, `Instrument`.

### 6.9 FMI-informed authored procedure ordering

Unlike the OBD-II tiers, where `probability_rank` is purely an authored per-family order, this tier's
order combines the fault's FMI failure class with an authored kind policy. The FMI states the failure
mode, while the kind policy and family rank remain editorial decisions; the result is not an observed
rate of causes or successful repairs. The generated class-kind mapping and tie/fallback rules are in
[`samples/heavyduty/ordering_metadata.json`](samples/heavyduty/ordering_metadata.json). Every
fault gets at least one fix (the family default), so — as with the OBD-II tiers — fixes exist for
100% of faults.

## Cost basis

Every `diagnostic_fixes` row (and its joined view) carries a `cost_basis_id`. Each id resolves
to an entry under `bases` in `cost_metadata.json`, which ships next to `ordering_metadata.json`
in every ZIP and every free sample, scoped to its product (`obd2` or `heavyduty`).

| `cost_basis_id` | Applies to |
| :--- | :--- |
| `obd-editorial-us-aftermarket-v1` | Standard and OEM Complete |
| `hd-editorial-us-independent-shop-v1` | Heavy Duty |

- **Editorial.** The values are set by the dataset's editors. No price observations, invoices or labor-guide data are recorded for them.
- **Currency and geography.** US dollars, United States. OBD-II parts are aftermarket replacement parts; Heavy Duty is an independent heavy-duty or fleet shop, not dealer rates.
- **Not specified.** Sales tax, shipping, core charges, shop-supplies fees, labor rate (labor is given in hours only) and programming or subscription fees. Heavy Duty does not specify parts quality either.
- **Dates.** The OBD-II estimates were first authored in 2026-07 and the Heavy Duty estimates in 2026-09. No re-pricing is recorded.
- **Statuses.** `estimated` means the cost columns hold an editorial estimate. `none_required` means the procedure needs no parts: both values are `0` and it has no `replacement_parts` rows. `unknown` means no estimate exists and both values are NULL (a part can be listed while its price is not). For labor, `estimated` means `est_labor_hours` holds an editorial estimate and `unknown` means it is NULL.
- **Filtering.** `WHERE parts_cost_status = 'estimated'` selects rows with an estimate; `WHERE est_parts_cost_min_usd IS NULL` (equivalently `parts_cost_status = 'unknown'`) selects rows without one. Do not read NULL as zero.
- **Where `unknown` and `none_required` occur.** Only reviewed families (those whose procedures carry an `action_type`) use `unknown`, or `none_required` apart from the legacy Heavy Duty zeros of section 6.4.

| `action_type` | Meaning | Labor class (`labor_kind_by_action`) |
| :--- | :--- | :--- |
| `inspect` | Visual or physical check without an instrument reading. | diagnostic |
| `measure` | Instrumented test such as a meter, gauge, pressure, smoke or scope reading. | diagnostic |
| `scan` | Scan-tool read or command: codes, freeze frame, live data or a bidirectional test. | diagnostic |
| `repair` | Restore the existing component without a new part: clean, reseat, splice, adjust, flush or refill. | repair |
| `replace` | Fit a new or remanufactured part. | repair |
| `program` | Software flash, coding, relearn or calibration. | programming |
| `verify` | Confirm a completed repair: clear codes, drive cycle, readiness or re-test. | diagnostic |

An `action_type` describes what the procedure does unconditionally; a conditional follow-up
("replace if out of spec") is its own procedure. The labor class is documented only: derive it
from `action_type` with the `labor_kind_by_action` map in `cost_metadata.json`.

## 7. SQLite enforcement — schema version 2

This contract applies to the snapshots this build writes (`PRAGMA user_version = 2`; version 1 shipped in the first 2026.10 build; version 2 ships from this release, identified by `data_release_id`).
The four OBD-II and six Heavy Duty tables, existing column order and identifiers are
unchanged from version 1; values change only in reviewed families (see "Version 2 changes"). Standard keeps **all columns TEXT**, including IDs, rank and estimates;
OEM Complete and Heavy Duty retain INTEGER/REAL storage for numeric fields.
CSV and Parquet gain the same five appended columns (estimates are null only when their status is `unknown`); `ordering_metadata.json` is unchanged.

| Table | Enforced key and references |
| :--- | :--- |
| OBD `dtc_codes` | PK `code_id`; UNIQUE `(dtc_code, oem_make)`. SAE uses make `''`; OEM requires a make. A DTC string alone is not a globally unique OEM identifier. |
| OBD `diagnostic_fixes` | PK `fix_id`; FK `code_id`; UNIQUE `(code_id, probability_rank)` and `(fix_id, code_id)`. |
| Both `replacement_parts` | PK `part_id`; FK `fix_id`. Multiple parts per fix and HD fixes without parts remain valid. |
| OBD `dtc_fixes_joined` | PK `fix_id`; composite FK `(fix_id, code_id)`; UNIQUE `(code_id, probability_rank)`. |
| HD `j1939_fmi` | PK `fmi` (0–31); reserved flag agrees with FMI 22–30. |
| HD `j1939_spn` | Composite PK `(spn, oem_make)`; SPN 1–524287; source_count positive; proprietary flag agrees with SPN >= 520192. |
| HD `j1939_faults` | PK `fault_id`; FK `fmi`; UNIQUE `(spn, fmi, oem_make, controller, oem_code, source_id)`. |
| HD `diagnostic_fixes` | PK `fix_id`; FK `fault_id`; UNIQUE `(fault_id, probability_rank)` and `(fix_id, fault_id)`. |
| HD `j1939_fixes_joined` | PK `fix_id`; composite FK `(fix_id, fault_id)`; UNIQUE `(fault_id, probability_rank)`. |

HD's SPN relation is **trigger-enforced**, with four guards covering fault
INSERT/UPDATE and SPN DELETE/key UPDATE. Below **516096**, a fault resolves to
`(spn, '')`; at/above that boundary it resolves to `(spn, fault.oem_make)`.
This naming boundary differs from the proprietary flag boundary **520192**.
There is no direct `(spn, oem_make)` fault foreign key. SPN/controller/OEM code
pairs may repeat across makes, controllers and sources. Empty OEM codes,
unknown controllers `''`, neutral SPN makes, optional page/link values and
zero-cost HD procedures retain their existing meaning.

Required IDs, references and numeric fields are NOT NULL, except the cost and labor estimates, which are nullable in version 2. CHECKs reject
malformed DTCs, noninteger/negative ranks, invalid flags/difficulty values,
nonfinite or negative estimates and reversed costs. For `estimated` rows OBD requires
`min <= max` with `max > 0` and labor 0.1–20 hours; HD allows `0 <= min <= max` and labor >= 0.
The curated-data validator still applies `0 < min < max` to unreviewed OBD families.
Required names, descriptions and instructions are nonblank text. Optional
source_page/search links may be NULL; existing empty strings are preserved.
Standard checks numeric text without converting stored strings: integer
fields use canonical decimal digits, estimates use finite unsigned decimal
text (no exponent notation). These checks do not impose today's maximum rank.

Foreign keys restrict parent deletion/key updates. Enable them **on every writing
connection before a transaction**; the file cannot enable this connection setting
for you. HD SPN triggers operate independently, and `foreign_key_check` does not
audit their conditional relation.

```python
import sqlite3
con = sqlite3.connect("mechanicdb.sqlite")
con.execute("PRAGMA foreign_keys = ON")
assert con.execute("PRAGMA foreign_keys").fetchone()[0] == 1
rows = con.execute(
    "SELECT probability_rank, fix_title FROM dtc_fixes_joined "
    "WHERE dtc_code=? AND oem_make=? ORDER BY CAST(probability_rank AS INTEGER)",
    ("P0420", ""),
).fetchall()
con.close()
```

The numeric cast gives correct rank ordering for Standard's TEXT profile, including
ranks above 9. Its lookup index bounds the matching rows; the numeric sort can use
a temporary B-tree. OEM/HD can order by probability_rank directly using numeric
storage. Always use explicit ordering: added indexes may change unordered results.

```sql
-- HD discovery returns every scoped fault; select fault_id for its procedure order.
SELECT fault_id, oem_make, controller, oem_code, source_id
FROM j1939_faults WHERE spn=110 AND fmi=3;
SELECT fix_title, probability_rank FROM diagnostic_fixes
WHERE fault_id=280 ORDER BY probability_rank;
SELECT part_name FROM replacement_parts WHERE fix_id=1205 ORDER BY part_id;
```

PK/UNIQUE indexes support code/make, scoped fault identity and parent/rank
lookups. Additional indexes cover parts by fix_id, joined OBD code/make/rank,
joined HD SPN/FMI/rank, HD FMI references, and OEM make/code browsing.
There are no substring-search indexes or measured production latency guarantees.

Joined tables remain **materialized snapshots**: the exporter and delivery gates
verify their exact normalized projection. Their copied text and estimates do not
automatically synchronize after customer edits. Replacing a table through
`pandas.to_sql(if_exists="replace")` or CREATE TABLE AS SELECT loses this schema.
Numeric INTEGER PRIMARY KEYs retain SQLite's normal NULL-autoallocation behavior
for customer inserts; the exporter rejects missing IDs and always binds supplied IDs.

Replace an older read-only snapshot after backing it up; importing into a mutable
customer database needs deliberate DDL/migration and reports invalid custom rows.
No customer data is automatically migrated or discarded. Schema introspection and
invalid writes intentionally change; read values and CSV/Parquet interfaces remain
compatible. Byte reproducibility is verified within a pinned runtime, not across
different SQLite or ZIP compression versions.

### Version 2 changes

- **Five appended columns.** `diagnostic_fixes` and both joined views gain `action_type`, `parts_cost_status`, `labor_status`, `cost_basis_id` and `procedure_rationale`, in that order after `step_by_step_instructions`. Existing columns keep their names and order. Unreviewed rows keep their values unless they are removed; rows in reviewed families (non-NULL `action_type`) may change title, rank, steps and estimates; a row that stays keeps its ID, and a removed row's ID is retired, never reused. A file with `PRAGMA user_version = 1` predates them.
- **Nullable cost and labor.** `est_parts_cost_min_usd`, `est_parts_cost_max_usd`, `est_labor_hours`, `action_type` and `procedure_rationale` may be NULL; `parts_cost_status`, `labor_status` and `cost_basis_id` are NOT NULL. Standard stays all TEXT and stores an empty string in a nullable field as NULL.
- **CHECKs.** `action_type` is NULL or one of the seven actions; `parts_cost_status` is `estimated`, `none_required` or `unknown`; `labor_status` is `estimated` or `unknown`; `cost_basis_id` equals the profile's basis (`obd-editorial-us-aftermarket-v1` for Standard and OEM Complete, `hd-editorial-us-independent-shop-v1` for Heavy Duty). `unknown` requires NULL costs; `none_required` requires `0` / `0`; `estimated` requires non-NULL costs with `min <= max` and, for OBD, `max > 0`. A NULL result of any of these checks fails.
- **Labor bounds.** `unknown` requires NULL hours; `estimated` requires hours of 0.1–20 for OBD and >= 0 for HD.
- **Cross-table rules.** Part-row counts per status (for example a `none_required` procedure has no `replacement_parts` rows) span tables, so the build validators enforce them rather than CHECKs.


## Stable identity companions (since edition 2026.10)

Existing code_id, fault_id, fix_id and part_id values are preserved by a committed
allocation registry. IDs may be sparse and are scoped by domain/entity type.
Use qualified references such as `mechanicdb:v1:obd:fix:571`; the same integer
in HD or another table is a different identity. Numeric exports use permanent-ID
order; procedure display remains ascending probability_rank (numeric CAST for
Standard TEXT), with the existing I01 authored-order meaning.

`identity_metadata.json` identifies the immutable data release and artifact;
`identity_map.csv` / `.parquet` provide entitled references, decimal-text numeric
aliases, parent references, authored keys and content revision digests;
`identity_events.csv` provides relevant lifecycle information. The core SQLite
schema is version 2. Public maps include only the authorized fixed samples.
Text, rank and component-name changes preserve intended identity. Retired or
withdrawn allocations are never reused; merge/split migration requires review.

Version 2 uses content canonicalization 2: new columns at their unreviewed default are left out of the content digest, so unreviewed rows keep their digest; NULL estimates hash as null.

Historical integers require an exact artifact or verified source release. Two
known September 2026 HD artifacts reused fault 571 for different entities; month
and number alone are ambiguous. Migration reports preserve original targets and
do not guess missing or changed historical procedure/part mappings. Paid
editions carry these companions from 2026.10.

Paid ZIPs carry the compact Parquet identity map; generated master directories
and public samples provide both CSV and Parquet maps. Content revision detectors
use the declared sha256-128 algorithm; full artifact/file/registry checksums use
SHA-256. CSV consumers can convert the companion with pandas.read_parquet().

HD's delivered natural UNIQUE key remains (`oem_make`, `controller`, `spn`, `fmi`, `oem_code`, `source_id`); the registry resolves this scoped assertion permanently.