# TASK 4: Update Normalization And Load Path For Debezium Row Images, Native `item_bumps`, And Curated Rebuild From Staging State

## Summary

Update downstream normalization and staging load so KDS CDC works from real Debezium row images, supports native `item_bumps`, and rebuilds curated state from staging truth.

## Current Codebase Findings

Repository analysis shows:

- `KdsCdcNormalizeLoadJobs` currently reads fields directly from `record.Envelope.Payload` as if it were a flat row object.
- real Debezium payload nests data under `payload.before` / `payload.after`.
- current row mappers for orders, items, item_bumps, and customers are structurally close to the target staging schema, but they read from the wrong JSON level.
- sample payload set includes native `item_bumps` message.
- `staging.kds_item_bumps` and `curated.kitchen_item_status_events` still exist and must remain supported downstream.
- current curated SQL depends on parent rows already being present, which makes child application sensitive to arrival timing.

## Dependencies

- TASK 1
- TASK 2
- TASK 3

## Source References

- `../../docs/features/ETL/kds-cdc/payloads/customer_update.json`
- `../../docs/features/ETL/kds-cdc/payloads/item_create.json`
- `../../docs/features/ETL/kds-cdc/payloads/item_update.json`
- `../../docs/features/ETL/kds-cdc/payloads/item_bump_create.json`
- `../../docs/features/ETL/kds-cdc/payloads/order_create.json`
- `../../docs/features/ETL/kds-cdc/payloads/order_update.json`
- `../../docs/features/ETL/kds-cdc/payloads/order_delete.json`
- `../../docs/features/ETL/warehouse-data/logic-control-kds-staging-model/kds_item_bumps.md`
- `../../docs/features/ETL/warehouse-data/curated-model/kitchen_item_status_events.md`

## Scope From Breakdown

This task covers:

- row-image-aware normalization,
- entity mapping for order/item/customer/item-bump Debezium messages,
- downstream stage compatibility with existing staging/curated SQL.
- curated rebuild behavior from tenant staging state.

## Files to Modify

- `backend/Kds.Ai.Integrations/KdsCdc/KdsCdcNormalizeLoadJobs.cs`
- `backend/Kds.Ai.Integrations/KdsCdc/KdsCdcRowModels.cs` if needed
- any raw record model that should expose effective row image or parsed Debezium metadata
- tests under `backend/tests/Kds.Ai.Integrations.Tests/KdsCdc/`

## Required Behavior

### Row-image-aware mapping

Normalizer must map business columns from selected Debezium row image, not from whole envelope payload.

Required rule:

- `c`, `u`, `r` use `after`
- `d` use `before`

Required failure behavior:

- `c`, `u`, or `r` with missing `after` is invalid for normalization,
- `d` with missing `before` is invalid for normalization,
- unknown extra columns are preserved in raw and ignored by normalizers unless mapped explicitly,
- missing required mapped columns fail with diagnostics rather than producing partial silent rows.

Required primary-key map:

- `orders`: `guid`,
- `customers`: `guid`,
- `items`: `guid`,
- `item_bumps`: `guid`.

If a supported table lacks its required primary key in the selected row image, normalization must fail as non-retryable for that row and preserve diagnostics/lineage.

### Orders, items, customers, item bumps

Normalizer must support at least:

- orders create/update/delete,
- customers update,
- items create/update from current sample shape,
- native item-bumps create from current sample shape.

### Native `item_bumps`

Integration must support source-shaped `item_bumps` rows directly from native Debezium payloads.

Do not make derived `item_bumps` the baseline path for this slice.

### Curated rebuild from staging state

After staging load succeeds, downstream curated behavior must rebuild affected curated targets from tenant staging state.

This task must lock semantic rule that:

- `staging_to_curated` does not treat one current arrival window as final truth,
- child entity correctness does not depend on parent arriving first in same intake window,
- join-skipped children are not silently treated as complete success.

Required parent/child disorder behavior:

- if an item or item-bump cannot be promoted because relation state is missing, it remains in landing retry state until relation state is available,
- when the parent order or item later updates relation state, unresolved landing records can promote to tenant RAW,
- once promoted and loaded, curated rebuild reads full tenant staging state, not only tables present in the current load batch,
- a curated target skipped because dependencies are incomplete must leave durable diagnostics/state that can be retried; it must not be reported as final success for that affected child event.

### Schema evolution behavior

Debezium payloads may include columns not currently mapped.

Required behavior:

- raw layers preserve the complete original payload,
- normalizers tolerate unknown extra columns,
- required known columns remain enforced per entity,
- diagnostics identify unsupported tables or missing required fields with entity, operation, primary key when available, and source coordinates.

## Testing

Add normalization-focused tests for:

- order create payload,
- order update payload,
- order delete payload,
- customer update payload,
- item create payload,
- item update payload,
- native item-bump payload,
- delete semantics selecting `before` image correctly,
- missing `after` for create/update/read fails clearly,
- missing `before` for delete fails clearly,
- missing primary key fails clearly,
- unknown extra columns do not break normalization,
- curated rebuild behavior for parent/child arrival disorder,
- item arrives before order, remains unresolved, then promotes after order relation index exists,
- item-bump arrives before item relation exists, remains unresolved, then promotes after item relation index exists.

## Acceptance Criteria

- [ ] Normalizer reads from Debezium row image, not flat payload root
- [ ] Orders normalize correctly from sample payloads
- [ ] Customers normalize correctly from sample payloads
- [ ] Items normalize correctly from sample payloads
- [ ] Native `item_bumps` normalize correctly from sample payloads
- [ ] Missing selected row image and missing primary key cases fail with clear diagnostics
- [ ] Unknown extra columns are tolerated while raw payload remains preserved
- [ ] Existing staging and curated downstream contracts remain satisfiable
- [ ] Curated rebuild semantics are aligned with staging-state source of truth
- [ ] Parent/child disorder tests prove delayed promotion plus staging-state rebuild correctness
- [ ] Tests cover create/update/delete row-image selection and parent/child consistency behavior

## Notes

- Keep stage behavior explicit. Rebuild from staging truth is preferred over fragile intake-time semantics.