## Outcome: smallest implementation path

### 1. Versioned local food enrichment

**Add**

- `data/nutrition/food-enrichment-v1.json`
  - Non-patient local ruleset.
  - Top-level: `definition_version`, `source_label`, `source_version`, `license`, `rules`, `aliases`, `payload_sha256`.
- `scripts/health/local_food_enrichment.py`
  - `load_definition(path)`
  - `validate_definition(payload)`
  - `install_definition(connection, payload)`
  - `active_definition(connection)`
  - `classify_food(connection, name)`
  - `enrich_unmapped_items(connection, *, max_days=366)`
  - `main()`

**Modify**

- `scripts/health/yazio_nutrition_sync.py`
  - Replace the body of `classify_histamine()` with the shared `local_food_enrichment.classify_food()`.
  - Extend the `nutrition_histamine_scores` insert in `sync_date()` to persist definition/source provenance.
  - Keep missing mappings unknown; never overwrite an existing reviewed/manual classification.
- `scripts/health/health_dashboard_action_worker.py`
  - In `apply_mapping_action()`, replace the current “versioned rule” lookup at lines 725–741. It currently treats `histamine_food_rules.updated_at` as `source_version`.
  - Resolve `sighi_reference` against the active immutable definition and copy its trusted `source_label`, `source_version`, and `definition_version`.
  - Keep `personal_food_tolerance` completely separate.
- `scripts/health/dashboard_v5/read_api.py`
  - Change `_histamine_definition()` from hard-coded version values to the active local definition metadata.
  - Return only version/source labels, never the ruleset or food inventory.
- `tests/fixtures/dashboard_v5_fixture.py`
  - Seed one immutable synthetic definition and stamp synthetic classifications with its version.

**Schema touchpoints**

Add a dedicated migration, preferably:

- `scripts/health/migrate_sprint6f_b_part_b.py`

Add tables:

```sql
nutrition_food_enrichment_versions(
    definition_version TEXT PRIMARY KEY,
    source_label TEXT NOT NULL,
    source_version TEXT NOT NULL,
    payload_sha256 TEXT NOT NULL,
    license_label TEXT,
    is_active INTEGER NOT NULL CHECK(is_active IN (0,1)),
    installed_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

nutrition_food_enrichment_rules(
    definition_version TEXT NOT NULL,
    canonical_food TEXT NOT NULL,
    sighi_score INTEGER NOT NULL CHECK(sighi_score BETWEEN 0 AND 3),
    category TEXT,
    tags TEXT,
    notes TEXT,
    confidence TEXT NOT NULL CHECK(confidence IN ('low','medium','high')),
    PRIMARY KEY(definition_version, canonical_food),
    FOREIGN KEY(definition_version)
      REFERENCES nutrition_food_enrichment_versions(definition_version)
);

nutrition_food_enrichment_aliases(
    definition_version TEXT NOT NULL,
    alias TEXT NOT NULL,
    canonical_food TEXT NOT NULL,
    PRIMARY KEY(definition_version, alias),
    FOREIGN KEY(definition_version, canonical_food)
      REFERENCES nutrition_food_enrichment_rules(definition_version, canonical_food)
);
```

Add a partial unique index ensuring only one active version:

```sql
CREATE UNIQUE INDEX idx_food_enrichment_one_active
ON nutrition_food_enrichment_versions(is_active)
WHERE is_active=1;
```

Add nullable provenance columns to `nutrition_histamine_scores`:

- `definition_version TEXT`
- `source_label TEXT`
- `source_version TEXT`

Also update:

- `database/schema.sql`
- `database/schema_manifest.json` — table count increases from 44 to 47 and digest must be regenerated.

**Safety behavior**

- Same version + same payload hash: idempotent no-op.
- Same version + different payload hash: fail closed.
- Activate a new version and enrich unknown rows in one transaction.
- Never overwrite a classified item, personal tolerance, raw YAZIO JSON, or mapping-action provenance.
- Recompute only affected days through the existing `recompute_nutrition_day()`.

---

### 2. Local PDF text/OCR pipeline with review separation and repetition hashes

**Add**

- `scripts/health/document_text_pipeline.py`
  - `fingerprint_source(path)`
  - `_run_pdftotext(path, timeout_s)`
  - `_run_ocrmypdf(source, output, timeout_s)`
  - `extract_pdf_text(path)`
  - `build_repetition_hash(source_sha256, pipeline_version, engine_signature)`
  - `process_document(connection, document_id, path)`
  - `main()`

Recommended deterministic flow:

1. Validate regular PDF, size cap, magic `%PDF-`, and reject symlinks.
2. Run `pdftotext` first.
3. Normalize through `dashboard_v5.document_chunks.normalize_document_text()`.
4. If text is below a documented quality threshold, run OCRmyPDF into a private temporary PDF:
   - never modify the original;
   - `--skip-text`, `--deskew`, `--rotate-pages`, `-l deu+eng`;
   - then run `pdftotext` on the OCR output.
5. Prefer the stronger normalized extraction.
6. Compute:
   - source SHA-256;
   - normalized text SHA-256;
   - repetition hash over source hash + pipeline version + exact engine versions/options.
7. Atomically update only technical extraction fields/text.
8. Do **not** change `dokumente.review_status`.
9. Do **not** rebuild FTS automatically.

**Modify**

- `scripts/health/process_all_health_documents.py`
  - Make `extract_pdf_pymupdf()`, `extract_pdf_docling()`, and the PDF branch of `process_one()` delegate to the new pipeline, or retire those PDF helpers.
  - Remove all extraction-driven `review_status` writes:
    - missing-file update around line 237;
    - successful extraction update around lines 286–292;
    - extraction-error update around line 298.
  - Keep `processing_quality` technical only.
  - Docling should not be in the production fallback chain while its CLI/import path is unstable.
- `scripts/health/document_fts_migrate.py`
  - No functional expansion required.
  - Preserve `rebuild()`’s `WHERE review_status='geprueft'`.
  - Optionally record the extraction text hash in FTS metadata so `--check` can detect a stale reviewed index.
- `scripts/health/dashboard_v5/document_chunks.py`
  - Reuse `normalize_document_text()` unchanged as the one normalization contract for extraction hashes, previews, and FTS chunks.

**Schema touchpoints**

Add:

```sql
document_extraction_runs(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    dokument_id INTEGER NOT NULL,
    pipeline_version TEXT NOT NULL,
    source_sha256 TEXT NOT NULL,
    repetition_hash TEXT NOT NULL,
    normalized_text_sha256 TEXT,
    extraction_method TEXT NOT NULL,
    engine_versions_json TEXT NOT NULL,
    status TEXT NOT NULL CHECK(
      status IN ('extracted','failed','skipped_repeat','unsupported')
    ),
    character_count INTEGER NOT NULL DEFAULT 0,
    error_code TEXT,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(dokument_id, repetition_hash),
    FOREIGN KEY(dokument_id) REFERENCES dokumente(id)
);
```

This raises the proposed schema count to **48** tables.

The canonical text can remain in `dokumente.extrahierte_inhalte`; the run table supplies immutable technical history and repeat detection. Human trust remains solely in `dokumente.review_status`.

**Important current violation**

`process_all_health_documents.process_one()` currently allows extraction quality to influence `review_status`. That must be removed. Extraction success means “preview technically available,” not “reviewed.”

FTS already has the correct high-level boundary: `document_fts_migrate.rebuild()` indexes only currently reviewed documents, and read APIs re-check current review status.

---

### 3. XLSX fail-closed reconciliation

Keep this work inside:

- `scripts/health/lab_source_reconciliation.py`

Do not route it through canonical import code.

**Add functions**

- `WorkbookContractError`
- `_xlsx_file_contract(path)`
- `_validate_workbook_structure(path)`
- `_strict_workbook_records(path)`
- `_workbook_source_digest(path)`

`_workbook_records()` should call the strict validator before using any existing matrix helpers.

**Strict contract**

- Explicit regular, non-symlink `.xlsx` path; bounded size and ZIP/XLSX magic.
- Exactly one expected data sheet; reject hidden or unexpected additional sheets.
- Read with both `data_only=False` and `data_only=True`.
- Reject formulas, external links, macros, and stale formula-cache dependence.
- Require the fixed row/column contract:
  - parameter column A;
  - reference column B;
  - dates from column C onward.
- Every date header must parse to a unique ISO day.
- Reject duplicate normalized `(parameter, date)` identities before `get_lab_matrix()` can silently overwrite them.
- Reject malformed numeric cells rather than skipping them.
- Preserve missing unit/reference as explicit classifications when structurally valid.
- Entire workbook parse fails before producing candidates if any structural ambiguity exists.
- Include only the workbook SHA-256 and contract version in aggregate output; details remain under the existing private `0600` writer.

**Modify**

- `run_audit()`
  - Bump aggregate `schema_version` to 2.
  - Add `xlsx_contract_version` and source digest.
  - Do not emit partial parity/staging results when workbook validation fails.
- `staging_plan()`
  - Keep plan-only behavior.
  - Continue producing `verified_against_original=0`,
    `status='zur_pruefung'`, and a non-`scanned_original` source.
- `main()`
  - For the new fail-closed mode, make `--xlsx` explicit/required rather than relying on `best_reference_xlsx()` selection.
  - Keep detailed reports private and aggregate reports identifier/value-free.

**Explicitly exclude**

- `health_pipeline.apply_corrections()` must not be called by this sprint.
  - It currently defaults absent status to `validiert`.
  - It silently skips incomplete rows.
  - It updates existing `laborwerte` rows directly.
  - That conflicts with “no canonical lab overwrite.”
- `health_pipeline.get_lab_matrix()` silently overwrites duplicate `(parameter, date)` keys, so duplicate detection must happen before calling it.
- Do not widen the misleading `dokumente.daten_typ` workaround for XLSX in this sprint; reconciliation can consume an explicit XLSX path without registering it as a PDF.

---

## Focused tests

### `tests/test_sprint6f_b_food_enrichment.py`

1. Valid definition installs atomically and becomes the only active version.
2. Reinstalling identical version/hash is a no-op.
3. Same version with changed bytes/hash fails without DB mutation.
4. Duplicate aliases, unknown canonical targets, invalid scores, missing source/license metadata fail closed.
5. Previous definition remains queryable after activating a new version.
6. Only structurally unknown items are enriched; existing manual/reviewed mappings remain byte-equivalent.
7. Every new classification carries exact definition/source provenance.
8. Personal tolerance and raw nutrition tables remain unchanged.
9. Only affected days are recomputed; incomplete days remain null until fully mapped.

### `tests/test_sprint6f_b_document_text_pipeline.py`

1. Generated text PDF uses real `pdftotext`; OCR is not invoked.
2. Generated image-only PDF uses real OCRmyPDF + Tesseract + `pdftotext`.
3. Original PDF hash/bytes are unchanged after OCR.
4. Same source and engine/options produce the same repetition hash and no duplicate extraction run.
5. Changed source or pipeline version produces a different repetition hash.
6. Extraction success/failure/missing file never changes `review_status`.
7. Pending extracted text remains absent from FTS/search/report; reviewed text becomes searchable only after explicit review plus FTS rebuild.
8. Symlink, wrong magic, oversized file, timeout, and failed OCR leave no partial trusted state.
9. Preview/FTS chunks use the same `normalize_document_text()` output.

### `tests/test_sprint6f_b_xlsx_reconciliation.py`

1. Valid synthetic workbook reconciles read-only; DB SHA-256 is unchanged.
2. Duplicate date headers fail before any report or staging plan is emitted.
3. Duplicate normalized parameter/day identities fail rather than being matrix-overwritten.
4. Formula cells and external workbook links fail closed.
5. Invalid dates and nonnumeric lab cells fail closed.
6. Missing unit/reference is classified explicitly, not converted to zero or verified.
7. Workbook candidates remain `zur_pruefung`, unverified, and non-canonical.
8. Aggregate JSON contains no parameter, value, filename, path, or sheet text.
9. Private report remains `0600`, rejects symlink roots/targets, and covers every discrepancy.
10. Assert `laborwerte`, `review_status`, `validierungsstatus`, `verified_against_original`, and `reference_range_source` are unchanged.

## Repository findings

- Repository is clean at `c37b798d6e8100829534a1d9322f3b03e805db14`.
- Existing reviewed-only FTS and shared chunking are reusable.
- OCRmyPDF/Tesseract/pdftotext are the viable local production chain; Docling should remain outside the critical path until its traceback is resolved.
- No files were created or modified.