Repository Docs & Knowledge Base

Consolidated Spensara documentation repository (722 Markdown & architecture documents in ../repository)

Built Word Documents (.docx): Spensara Overview (.docx) Capability Assessment (.docx) Brink POS Ingestion Analysis (.docx) The Borg Ingestion Architecture (.docx)

Implementation Specs (157)

Detailed engineering and feature implementation specifications.

implementation-specs/specs/KdsCdcDebeziumIntegration/TASK4.md 6,788 bytes · Updated Oct 23, 2026 10:44 AM EDT

Raw File

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.