# Existing QAF Stack — Architecture Research
**Date**: 2026-06-25
**Scope**: Read-only audit of `/root/projekte/Kadi-v2` for QAF-Differences engine planning
**Source files read**: lib/qaf-parser.ts, lib/qaf/comparison.ts, lib/qaf/column-groups.ts, lib/qaf/file-colors.ts, lib/qaf/process-mapping.ts, all __tests__, components/qaf/*, app/project/[id]/qaf/page.tsx, app/lsc-workshop/[id]/qaf/page.tsx, app/api/qaf-template/route.ts, supabase/migrations/supabase-migration-qaf-multi-file.sql, supabase/migrations/supabase-migration-qaf-process-mappings.sql, supabase/bootstrap/supabase-schema.sql (qaf_ sections)

---

## 1. Current DB Model

### Table: `qaf_uploads`
Defined in `supabase/bootstrap/supabase-schema.sql` lines 60-69, amended by `supabase-migration-qaf-multi-file.sql`.

| Column | Type | Constraints | Notes |
|--------|------|-------------|-------|
| id | uuid | PK, DEFAULT gen_random_uuid() | |
| project_id | uuid | NOT NULL, FK -> projects(id) CASCADE | |
| version_type | text | NULLABLE, CHECK IN ('zur_vergabe','nach_vergabe','aktuell') | Was NOT NULL before KAR-340; now nullable to allow unlabelled multi-file uploads |
| file_label | text | NULLABLE | Added in multi-file migration (KAR-340); user-editable display name |
| file_name | text | NOT NULL | Original filename |
| file_path | text | NOT NULL | Storage path (currently also equals file_name — not actually written to Supabase Storage, just stored as identifier) |
| uploaded_at | timestamptz | DEFAULT NOW() | |
| parsed_data | JSONB | NULLABLE | Stores the full parsed QAFRow[] array from the parser; ~22 fields per row |

**RLS**: Enabled. Policy `qaf_uploads_own`: `project_id IN (SELECT id FROM projects WHERE user_id = auth.uid())` — project-owner scoped.

**Used for**: Uploads (insert from qaf-client.tsx), display (select in page.tsx), comparison (all in client-side state derived from parsed_data JSONB).

---

### Table: `qaf_process_mappings`
Defined in `supabase-migration-qaf-process-mappings.sql` + amended by `supabase-migration-qaf-multi-file.sql`.

| Column | Type | Constraints | Notes |
|--------|------|-------------|-------|
| id | uuid | PK, DEFAULT gen_random_uuid() | |
| project_id | uuid | NOT NULL, FK -> projects(id) CASCADE | |
| process_step_id | uuid | NOT NULL, FK -> process_steps(id) CASCADE | Target station |
| qaf_row_key | text | NOT NULL | Currently = prozessbezeichnung string (or `__row_{i}` fallback) |
| confidence | numeric(3,2) | NULLABLE, CHECK 0..1 | 1.0 = auto-exact, 0.x = fuzzy-score, NULL = manual |
| match_source | text | NOT NULL DEFAULT 'manual' | CHECK IN ('auto','fuzzy','manual') |
| confirmed_by | uuid | NULLABLE | user uid — not FK-constrained |
| confirmed_at | timestamptz | DEFAULT now() | |
| created_at | timestamptz | DEFAULT now() | |
| updated_at | timestamptz | DEFAULT now(), via trigger | |
| qaf_upload_id | uuid | NULLABLE, FK -> qaf_uploads(id) CASCADE | Added KAR-340; allows per-file distinct mappings |

**UNIQUE constraint** (post KAR-340): `(project_id, qaf_upload_id, qaf_row_key)` — same row key can appear in multiple files under the same project.

**RLS**: Enabled. Policy `qaf_process_mappings_auth_all`: `auth.uid() IS NOT NULL` — **weaker than qaf_uploads** — any authenticated user can read/write any project's mappings (bug risk for multi-tenant but moot in single-tenant BMW pilot).

**Indexes**: `qaf_process_mappings_project_id_idx`, `qaf_process_mappings_step_id_idx`, `qaf_process_mappings_upload_id_idx`.

**Used for**: Persisting auto/fuzzy/manual QAF-row → process_step assignments. Read by page.tsx (select, passed to QafMappingDialog), written by QafMappingDialog (upsert with onConflict='project_id,qaf_upload_id,qaf_row_key').

---

## 2. Parser — lib/qaf-parser.ts

**Sheet selection** (lines 165-173): ExcelJS scan of all worksheets; picks first sheet whose name (lowercased) contains:
- `fertigungskosten` (DE: "Fertigungskosten")
- `manufacturing cost` (EN: "Manufacturing costs")
- `manufactering cost` (EN misspelling, present in real BMW templates)

Falls back to `ejWb.worksheets[0]` if none match (handles lean Template upload case).

**IMPORTANT**: NO "Zusammenfassung" sheet parsing exists at all. The parser is purely Fertigungskosten-sheet focused.

**Header auto-detection** `findHeaderRow()` (lines 132-151):
- Scans first `HEADER_SCAN_MAX_ROWS = 20` rows
- Normalises each cell via `normalizeHeaderCell()`: collapses `\r\n` → space, multi-space → single space, trims
- Picks row with highest count of cells matching `HEADER_TO_KEY` keys (normalized)
- Requires minimum `HEADER_MATCH_MIN = 5` matching headers (avoids false positives)
- Template format: headers at row index 0
- Real BMW QAF: headers at row index 10 (row 11 in 1-based Excel), preceded by 10 rows of BMW/VERTRAULICH/QAFVersion metadata

**22 mapped columns** via `HEADER_TO_KEY` (lines 64-111):
Both German and English BMW QAF v8 variants supported. German → English mappings:
- `Positionsnummer Fertigungsschritt` → `Item number Manufacturing step` → field `positionsnummer`
- `Teilebenennung` → `Parts designation` → field `teilebenennung`
- `Prozessbezeichnung` → `Process designation` → field `prozessbezeichnung`
- `Bezeichnung Anlage/Maschine/Typ` → `Designation Facility/machine/type` → field `bezeichnungAnlage`
- `Standort` → `Site` → field `standort`
- `Beschaffungswährung BW` → `Procurement currency [BW]` → field `beschaffungswaehrung`
- `Zykluszeit [s]` → `Cycle time [s]` → field `zykluszeit`
- `Teile pro Zyklus` → `Parts per cycle` → field `teileProZyklus`
- `Anzahl direkte Mitarbeiter` → `Number of direct employees` → field `anzahlMA`
- `Kalkulatorisch angesetzte direkte Lohnkosten [BW/h]` → `Imputed direct labor costs [BW/h]` → field `lohnkosten`
- `Kalkulatorisch angesetzte Lohn-zuschlagssätze SGK [%]` → `Imputed social overhead costs (SGK) [%]` → field `lohnzuschlagssaetze`
- `Maschinenstundensatz MSS [BW/h]` → `Machine-hour rate (MSS) [BW/h]` → field `mss`
- `Rüstkosten pro Stück [BW]` → `Setup costs per unit [BW]` → field `ruestkosten`
- `Fertigungseinzelkosten FEK [BW]` → `Direct manufacturing costs (FEK) [BW]` → field `fek`
- `Restfertigungsgemeinkosten (RFGK) Stundensatz [BW/h]` → `Remaining manufacturing overhead costs (RFGK) Hourly rate [BW/h]` → field `rfgk`
- `Fertigungskosten FK [BW]` → `Manufacturing costs (FK) [BW]` → field `fk`
- `Angebotswährung AW` → `Quotation currency [AW]` → field `angebotswaehrung`
- `Wechselkurs [AW/BW]` → `Exchange rate [AW/BW]` → field `wechselkurs`
- `Anzahl pro Angebotsteil` → `Number per quotation part` → field `anzahlProAngebotsteil`
- `Fertigungskosten FK [AW]` → `Manufacturing costs FK [AW]` → field `fkAW`
- `Ausschuss pro Prozessschritt [%]` → `Scrap per process step [%]` → field `ausschuss`
- `Ausschusskosten Fertigung [AW]` → `Scrap costs Manufacturing [AW]` → field `ausschusskosten`

**Formula handling** (lines 180-183): ExcelJS cell value — if the value is an object with a `.result` property (formula cell), reads `result`; otherwise uses raw value. So formula-computed numbers are resolved correctly.

**NO source-cell address tracking**: The parser does not record which Excel cell address (e.g. "B12") a value came from. Cell addresses are never stored.

**Row filtering** (lines 255-257): A row is included only if `prozessbezeichnung || teilebenennung` is truthy. Completely empty rows trigger an early exit (BMW QAF "Summen" footer detection).

**Text vs Numeric fields**:
- TEXT_FIELDS (7): positionsnummer, teilebenennung, prozessbezeichnung, bezeichnungAnlage, standort, beschaffungswaehrung, angebotswaehrung
- Numeric (15): all others, via `toNum()` → `Number(v)`, returns null for NaN/null/undefined/''

---

## 3. Comparison/Diff Logic — lib/qaf/comparison.ts + components/qaf/qaf-comparison-board.tsx

### What comparison.ts provides (pure functions, fully tested)

**`deltaReferenceIndices(slotsCount, mode)`** (lines 52-62):
- `'vs-basis'`: returns `[null, 0, 0, 0, 0]` for 5 slots — every non-basis slot compares to slot 0
- `'aufbauend'`: returns `[null, 0, 1, 2, 3]` — each slot compares to its immediate predecessor

**`computeDelta(current, ref)`** (lines 68-72):
- Returns `current - ref`, rounded to 4 decimal places via `toFixed(4)`
- Returns null if either side is null/undefined

**`computeMetricDeltas(slots, field, mode)`** (lines 78-92):
- Per-field delta array across all slots
- Returns null where either slot is empty or either value is non-numeric

**`deltaSentiment(delta, lowerBetter, epsilon=1e-6)`** (lines 101-112):
- Returns `'better' | 'worse' | 'equal' | 'na'`
- `lowerBetter=true`: negative delta = better (applicable to zykluszeit, anzahlMA, lohnkosten, etc.)
- `lowerBetter=false`: positive delta = better (applicable to teileProZyklus, wechselkurs, anzahlProAngebotsteil)
- `|delta| < 1e-6` → 'equal'

### What is ABSENT from comparison.ts

- **No percentage delta** (delta/ref * 100) — only absolute delta
- **No percentage-point delta** — same as absolute for rate fields, but not semantically distinguished
- **No automated cross-file matching** — comparison is purely manual slot assignment
- **No root-cause analysis**
- **No plausibility checks**
- **No confidence scoring at comparison level**
- **No summation/totals across process steps**

### ComparisonBoard UI (qaf-comparison-board.tsx)

**Layout**: Left sidebar (file list with expandable process rows, each row draggable) + Right main area (5-slot comparison table).

**Slots**: MAX_SLOTS = 5. Slot 0 = "Basis", Slots 1-4 = "Vergleich 1-4". Slots are filled by drag-and-drop from sidebar OR by clicking a row and choosing a slot in a picker modal.

**Metrics table** (15 rows, defined as METRICS array, lines 35-51):
```
zykluszeit (CT [s], lower-better, prec 2, suffix 's')
teileProZyklus (T/Zyklus, higher-better, prec 0)
anzahlMA (MA, lower-better, prec 1)
lohnkosten (Lohn, lower-better, prec 2)
lohnzuschlagssaetze (LZ [%], lower-better, prec 2)
mss (MSS, lower-better, prec 2)
ruestkosten (Rüst., lower-better, prec 2)
fek (FEK, lower-better, prec 2)
rfgk (RFGK, lower-better, prec 2)
fk (FK [BW], lower-better, prec 2)
wechselkurs (WK, higher-better, prec 4)
anzahlProAngebotsteil (Anz., higher-better, prec 0)
fkAW (FK [AW], lower-better, prec 2)
ausschuss (Ausch.%, lower-better, prec 3)
ausschusskosten (Ausch.K., lower-better, prec 2)
```

**Delta display**: Each cell shows the raw value + below it the delta (colored green/red per sentiment). German locale formatting (`de-DE`).

**Column group filter** from parent (passed as `columnGroup` prop):
- `alle`: all 15 metrics visible
- `material`: beschaffungswaehrung + wechselkurs (only 2 metrics of the 15; note beschaffungswaehrung is text, doesn't appear in METRICS)
- `fertigung`: 11 fields (positionsnummer through mss)
- `sbm`: 9 fields (ruestkosten through ausschusskosten)

**Color palette** (file-colors.ts): 7-color set using stable hash of qaf_upload_id. CSS tokens only (bg-primary, bg-success, etc.), no hardcoded hex.

**No process-step-aware matching in the board**: The board renders raw QAFRow values. The mapping dialog (qaf-mapping-dialog.tsx) is a separate feature that assigns rows to process_steps, but those mappings are NOT used to align rows in the comparison board. The comparison board is purely manual/visual.

---

## 4. Process Mapping — lib/qaf/process-mapping.ts

### autoMatchQafRows (single-file variant, lines 64-91)
- Builds exact-match index: `normalize(station_name) → step_id`
- `normalize()` = `trim().toLowerCase()`
- For each QAF row: looks up `normalize(prozessbezeichnung)` in the index
- Hit: `confidence = 1.0`, `match_source = 'auto'`
- Miss: goes to `unmatched[]`

### autoMatchQafFiles (multi-file variant, lines 120-151)
- Same logic but keys the result Map by `compositeKey(qaf_upload_id, row.key)`
- `compositeKey(id, key)` = `"${id}::${key}"` (null/undefined id → `"__nofile__::${key}"`)

### scoreCandidate (lines 50-62)
- Tokenizes both strings on `[\s\-_/,;]+`
- Exact normalized match → score = 1.0
- Otherwise: `shared_tokens / union_size` (Jaccard coefficient), rounded to 3 decimals
- Empty tokens → 0

### fuzzySuggest (lines 103-113)
- Scores all candidate steps against query
- Filters by `minScore` (dialog uses `FUZZY_MIN_SCORE = 0.15`)
- Returns top `topN` (dialog uses `FUZZY_TOP_N = 3`) sorted descending by score

### MappingDialog UX flow (qaf-mapping-dialog.tsx)
1. Auto-match runs on mount (`autoMatchQafFiles`)
2. Pre-existing DB mappings (initialMappings) override auto-match results
3. Unmatched rows show fuzzy suggestions (top 3 ≥ 0.15 threshold) — user clicks a suggestion → `match_source = 'fuzzy'`, `confidence = score`
4. "Manuelles Zuordnen" (collapsed `<details>`) shows all process steps → click → `match_source = 'manual'`, `confidence = null`
5. Process-steps sidebar allows drag-and-drop onto row cards
6. Unmatched rows sorted by best fuzzy score descending (most promising first)
7. "Speichern" upserts to `qaf_process_mappings` with onConflict='project_id,qaf_upload_id,qaf_row_key'

**Row key identity**: Derived in qaf-client.tsx line 219: `row.prozessbezeichnung ? row.prozessbezeichnung : '__row_${i}'`. This means the key is NOT a stable numeric ID but the process name string itself — if two rows have the same prozessbezeichnung, they get the same key.

---

## 5. UI / Navigation

### Routes
QAF exists at **two** sub-page routes, both rendering the same `QafClient` component:
1. `/project/[id]/qaf` → `app/project/[id]/qaf/page.tsx` (project/visit sub-page)
2. `/lsc-workshop/[id]/qaf` → `app/lsc-workshop/[id]/qaf/page.tsx` (LSC workshop sub-page)

Both are server components that fetch:
- `qaf_uploads` (all columns, project_id=id, order uploaded_at DESC)
- `process_steps` (id, step_number, station_name, area_name, project_id=id, order sort_order ASC)
- `qaf_process_mappings` (qaf_upload_id, qaf_row_key, process_step_id, match_source, project_id=id)

QAF is NOT a top-level page — it is nested under a project/visit detail.

### Action bar (QafClient)
- **Template herunterladen**: GET /api/qaf-template?version=zur_vergabe → downloads blank Template xlsx
- **+ QAF-Datei(n)**: Multi-file `<input type="file" accept=".xlsx,.xlsm" multiple>` → parseQAFTemplate() client-side → insert to qaf_uploads
- **Aus Datenablage**: DocumentPickerModal → signed URL from Supabase Storage `documents` bucket → download → parseQAFTemplate() → insert to qaf_uploads
- **Mapping ↔ Prozess-Steps**: Opens QafMappingDialog (disabled if no process steps OR no files)
- Per-file version-type selector (Variante/Zur Vergabe/Nach Vergabe/Aktuell) → UPDATE qaf_uploads
- Per-file delete → DELETE qaf_uploads

---

## 6. Export — app/api/qaf-template/route.ts

**What it does**: Generates and returns a blank QAF Excel template for download.

**Single sheet**: "Fertigungskosten" — German column names only (uses TEMPLATE_HEADERS from lib/qaf-parser.ts).

**Structure**:
- Row 1: Header (22 columns, BMW blue background ARGB FF0066B1, white bold Calibri 10pt, center+wrap, row height 52)
- Rows 2-21: 20 empty data rows, alternating light grey shading (FF F7F9FB) on odd rows, row height 18
- Frozen pane: ySplit=1, activeCell A2
- AutoFilter: entire header row

**Version parameter**: `?version=zur_vergabe|nach_vergabe|aktuell` → affects filename only (e.g. "Template_Zur_Vergabe.xlsx")

**Auth**: Protected by `supabase.auth.getClaims()` check.

**NO comparison export**: There is no route or component that exports the comparison board results, delta table, or mapping results to Excel. The only Excel output is this blank template.

---

## 7. REUSE-VS-BUILD Table for QAF-Differences Engine

For each module required by the new spec:

| Module | Status | Existing file(s) | Notes |
|--------|--------|-----------------|-------|
| **Parser (Fertigungskosten sheet)** | REUSE | `lib/qaf-parser.ts` | Handles DE+EN headers, formula cells, header auto-detect (rows 0 or 10), empty-row stop. Use as-is for Fertigungskosten ingestion. |
| **Parser (Zusammenfassung sheet)** | NEW | — | Zero Zusammenfassung parsing exists. Must add sheet detection + cell/range extraction for the summary totals. |
| **Source-cell address tracking** | NEW | — | Parser never records Excel cell addresses (e.g. "B12"). If audit trail needs cell coordinates, add to parser output. |
| **Normalizer** | NEW | — | No currency normalisation, no unit conversion (e.g. BW → EUR), no baseline designation logic. Must build from scratch. |
| **Matching-Cascade (5 stages, confidence_score, requires_review)** | EXTEND | `lib/qaf/process-mapping.ts` | Existing: 2 stages (exact+fuzzy) + manual. Has scoreCandidate (Jaccard), fuzzySuggest, autoMatchQafFiles. Need to add: positionsnummer-keyed matching, combined-key matching (positionsnummer+bezeichnung), machine-name fuzzy, area-grouping stage, and `requires_review` boolean flag. The `confidence` field in qaf_process_mappings already exists. |
| **Baseline-selection** | NEW | — | version_type (zur_vergabe/nach_vergabe/aktuell) is the only baseline hint. No automated baseline-pair selection or "this is the reference" designation logic exists. |
| **Diff — absolute** | REUSE | `lib/qaf/comparison.ts::computeDelta` | `current - ref`, rounds to 4 dp. Directly reusable. |
| **Diff — %** | EXTEND | `lib/qaf/comparison.ts` | `computeDelta` only returns absolute. Add: `computeDeltaPct(current, ref) = (current-ref)/|ref|*100` — ~3 lines. |
| **Diff — %-points** | EXTEND | `lib/qaf/comparison.ts` | Same as absolute for rate fields, but needs semantic annotation (which fields are rates vs absolute values). Add a `isRateField` flag to metric definitions. |
| **Delta reference modes** | REUSE | `lib/qaf/comparison.ts::deltaReferenceIndices` | vs-basis and aufbauend modes already implemented and tested. |
| **Delta sentiment** | REUSE | `lib/qaf/comparison.ts::deltaSentiment` | lowerBetter flag, epsilon, 4 states. Directly reusable. |
| **Root-Cause (deterministic)** | NEW | — | No root cause engine exists. Must build. |
| **Plausibility-check** | NEW | — | No plausibility logic exists. Must build. |
| **Column group definitions** | REUSE | `lib/qaf/column-groups.ts` | material/fertigung/sbm groupings + groupContains() + columnGroupOf(). Directly reusable for filter/display logic. |
| **File color system** | REUSE | `lib/qaf/file-colors.ts` | Stable hash → 7-color CSS-token palette. Reusable for multi-file display in new engine. |
| **8-sheet Excel export** | EXTEND | `app/api/qaf-template/route.ts` | Template route proves ExcelJS setup works for QAF. BMW styling constants (ARGB colors, column widths) are reusable. Must build all 8 sheets: README, Import_Log, Zusammenfassung_Vergleich, Fertigungskosten_Vergleich, Neu_Entfallen, Delta_Highlights, Top_Treiber_Prozess, Plausibilitätscheck. Route needs complete replacement, not minor extension. |
| **Data model (qaf_uploads)** | REUSE | supabase-migration-qaf-multi-file.sql | Table already has id, project_id, version_type, file_label, file_name, file_path, uploaded_at, parsed_data (JSONB). Good foundation. |
| **Data model (qaf_process_mappings)** | EXTEND | supabase-migration-qaf-process-mappings.sql | Has confidence, match_source, confirmed_by. Extend: add requires_review boolean, matched_by_stage text, extend match_source to 5 stages. |
| **Data model (~13 new tables)** | NEW | — | Need: qaf_comparison_pairs, qaf_diff_results, qaf_summary_comparison, qaf_root_cause_findings, qaf_plausibility_results, qaf_audit_log, qaf_baselines, qaf_import_log, etc. |
| **Audit-log** | NEW | — | No QAF audit log exists at all. `confirmed_by`/`confirmed_at` in qaf_process_mappings is the closest artefact but is only for mappings, not for diff/export events. |
| **UI — comparison board** | EXTEND | `components/qaf/qaf-comparison-board.tsx` | Current board: 5-slot manual drag-drop, absolute delta only, no process-step awareness. For differences engine: needs automated row alignment by matched process step, % delta column, requires_review highlighting, summary totals row. Major extension rather than light touch. |
| **UI — mapping dialog** | EXTEND | `components/qaf/qaf-mapping-dialog.tsx` | Good foundation: auto+fuzzy+manual, drag-and-drop, confidence display. Needs: 5-stage cascade display, requires_review flag, stage labelling. |
| **UI — QafClient action bar** | EXTEND | `components/qaf/qaf-client.tsx` | Add: baseline selection UI, export trigger, diff engine trigger. |

---

## Key Architecture Gaps for QAF-Differences

1. **No Zusammenfassung sheet parsing** — the spec references a "Zusammenfassung_Vergleich" output sheet, implying both sides' summaries need to be read. Zero infra for this.

2. **No cell-address provenance** — if the diff engine needs to cite specific cells (e.g. "FK [BW] row 12, cell R12"), the parser must be extended.

3. **parsed_data = raw JSONB in qaf_uploads** — the full QAFRow array is one JSONB blob. For diff results, the engine will need structured DB rows (one per process step per file), not JSONB. This means the new data model must normalize parsed_data into relational rows.

4. **Row key fragility** — current key = `prozessbezeichnung` string. If a supplier renames a step between versions ("Spritzguss A" vs "Spritzguss-A"), the matching breaks. The 5-stage cascade must handle this.

5. **No percent delta** — only absolute delta exists. For cost-driver analysis (% increase in FK), must add %.

6. **Confidence stored as numeric(3,2)** in qaf_process_mappings — good for 0.00-1.00, but the new engine's 5-stage cascade may need finer metadata (which stage matched, what evidence string). Consider adding `match_stage int` and `match_evidence text` columns.

7. **RLS gap in qaf_process_mappings** — policy allows any authenticated user to read all mappings. For a multi-user (future) scenario, this must be tightened to match qaf_uploads_own pattern.