# Debezium Payload Compatibility Notes ## Scope This note records final compatibility and architecture decisions for KDS CDC ingestion against real Debezium payloads under `docs/features/ETL/kds-cdc/payloads/`. This is no longer simulator-vs-production comparison note. It is final target architecture note for Debezium-first ingestion. Current direction is explicit: - Debezium payload is only supported intake contract. - simulator path should be removed. - SQS transport will be FIFO-class, but KDS CDC processing will not depend on `MessageGroupId`. - `SequenceNumber` may be present and should be preserved, but it is not required for correctness. - `store_guid == tenant_id` and is treated as unique and stable for this integration. - fastest, safest first goal is durable raw capture, not immediate curated consistency. - system will use additional landing bucket before tenant RAW promotion. Implementation plan for this direction lives in: - `../../../specs/KdsCdcDebeziumIntegration/TASK.md` - `../../../specs/KdsCdcDebeziumIntegration/TASK1.md` - `../../../specs/KdsCdcDebeziumIntegration/TASK2.md` - `../../../specs/KdsCdcDebeziumIntegration/TASK3.md` - `../../../specs/KdsCdcDebeziumIntegration/TASK4.md` - `../../../specs/KdsCdcDebeziumIntegration/TASK5.md` ## Design Goal Primary requirement for this slice is: ```text SQS message should become durable raw object quickly. ``` Everything after that can happen asynchronously and can rely on ETL-owned state. That changes preferred architecture: - intake path should be small, fast, and durable - semantic parent/child consistency should move out of intake path - curated correctness should come from staging-state rebuild, not from arrival timing ## Recommended High-Level Flow Target shape: ```text SQS -> parse Debezium envelope -> write immutable landing RAW object -> attempt tenant resolution -> if tenant known, promote/copy to tenant RAW -> ack message tenant RAW -> normalize -> load staging -> rebuild curated from tenant staging state -> export ``` This is intentionally two-phase: 1. capture first 2. interpret and apply later This document is decision record. It does not present alternative architectures. ## Final ETL Job Shape This section defines what ETL job will look like for this integration. KDS CDC keeps existing five-stage ETL shape, but entry boundary changes. Final pipeline is: 1. `SQS -> landing RAW capture` 2. `landing RAW -> tenant RAW promotion` 3. `tenant RAW -> normalize_load` 4. `normalize_load -> load_to_staging` 5. `load_to_staging -> staging_to_curated` 6. `staging_to_curated -> curated_to_s3` Important consequence: - SQS worker is not responsible for finishing full ETL pipeline inline - SQS worker is responsible for durable capture and promotion handoff - ETL jobs run from tenant RAW and their own durable state ### Phase 0. SQS Intake And Landing RAW Capture Responsibility: - receive SQS message - parse supported Debezium envelope - write landing RAW object - record routing state - acknowledge SQS after landing write succeeds This phase creates first durable boundary. Output: - landing RAW object - intake metadata - promotion state record ### Phase 1. Landing RAW To Tenant RAW Promotion Responsibility: - resolve tenant from direct `store_guid` or durable relation indexes - promote landing RAW object to tenant RAW when routing is safe - retry unresolved messages later without losing payload durability This phase is routing phase, not semantic merge phase. Output: - tenant RAW object for one tenant and one entity - updated promotion state ### Phase 2. `normalize_load` Responsibility: - read tenant RAW objects - select effective Debezium row image - normalize into source-shaped load rows - preserve delete semantics - preserve Debezium source metadata needed for replay/dedupe/audit - write load artifacts for downstream Redshift load This phase is first place where row-level shaping happens. Output: - load artifacts split by tenant and entity - stage batch metadata ### Phase 3. `load_to_staging` Responsibility: - load normalized artifacts into tenant Redshift staging tables - keep operation idempotent - preserve source identity and delete markers - update durable ETL state needed for downstream dependency resolution This phase establishes tenant-local relational state. Output: - updated `staging.kds_orders` - updated `staging.kds_items` - updated `staging.kds_item_bumps` - updated `staging.kds_customers` - updated ETL-owned relation indexes ### Phase 4. `staging_to_curated` Responsibility: - rebuild affected curated targets from tenant staging state - not from arrival order of current message window - ensure parent/child consistency from current staged tenant dataset This is key semantic decision for this design. Curated correctness comes from staged tenant state, not from intake ordering guarantees. Output: - updated curated tenant tables - durable curated stage state ### Phase 5. `curated_to_s3` Responsibility: - export final curated tenant state to S3 curated area - scope export to affected curated targets only - keep export retry-safe and idempotent This phase produces final externalized analytic state. Output: - curated S3 artifacts - export batch metadata ### Batch Lifecycle Decision Each ETL phase keeps its own durable batch lifecycle. Required rule: - do not treat one SQS poll as one end-to-end ETL batch - do not require full ETL completion before SQS ack - do create explicit lineage from landing RAW through all later ETL stages Batch lineage should be traceable across: - landing RAW capture record - tenant RAW promotion record - `normalize_load` batch - `load_to_staging` batch - `staging_to_curated` batch - `curated_to_s3` batch ## Final End-To-End Flow Diagram ```mermaid flowchart TD A[SQS FIFO queue
Debezium messages] --> B[Phase 0: Intake worker
receive message] B --> C{Supported Debezium envelope?} C -- no --> Q1[Quarantine / poison path
store payload and diagnostics] C -- yes --> D[Extract intake metadata
table op pk source coordinates transport metadata] D --> E[Write landing RAW object
append-only immutable capture] E --> F[Create or update promotion state
captured] F --> G[Acknowledge SQS message] G --> H[Phase 1: Promotion worker] H --> I{Tenant resolvable now?} I -- no --> J[Keep in landing RAW
mark tenant_unresolved
schedule retry] I -- yes --> K[Promote or copy to tenant RAW
mark tenant_promoted] K --> L[Phase 2: normalize_load] L --> M[Select Debezium row image
before or after] M --> N[Normalize by tenant and entity
write load artifacts] N --> O[Phase 3: load_to_staging] O --> P[Redshift COPY into staging tables] P --> R[Update ETL relation state
order_guid -> store_guid
item_guid -> store_guid] R --> S[Phase 4: staging_to_curated] S --> T[Rebuild affected curated targets
from tenant staging state] T --> U[Phase 5: curated_to_s3] U --> V[Export affected curated targets
to S3 curated] V --> W[Final state
tenant staging updated
tenant curated updated
curated S3 exported] J --> H ``` ## Final Phase-By-Phase Flow ```text 1. Debezium message arrives in SQS. 2. Intake worker parses message. 3. If payload is unsupported or malformed, worker quarantines it. 4. If payload is valid, worker writes immutable landing RAW object. 5. Worker records promotion state. 6. Worker acknowledges SQS message. 7. Promotion worker tries to resolve tenant. 8. If tenant is unresolved, message stays in landing RAW and retry is scheduled. 9. If tenant is resolved, message is promoted to tenant RAW. 10. `normalize_load` reads tenant RAW and writes normalized load artifacts. 11. `load_to_staging` loads artifacts into tenant staging tables. 12. ETL state updates relation indexes from staging truth. 13. `staging_to_curated` rebuilds affected curated targets from tenant staging state. 14. `curated_to_s3` exports affected curated targets to curated S3. 15. Final state is durable across landing RAW, tenant RAW, staging, curated, and curated export lineage. ``` ## ETL State Ownership ETL state is now central to this design. ETL state owns: - promotion status after landing capture - retry state for unresolved tenant routing - relation indexes used for child routing - normalized/load/staging/curated/export batch lineage - replay boundaries and checkpoints - idempotent recovery after downstream failure ETL state does not depend on SQS retention after landing RAW capture succeeds. ## Final Failure Boundaries This section defines where failures stop and what remains durable. ### Failure before landing RAW write - SQS message is not acknowledged - retry comes from SQS redelivery - no durable raw capture yet exists ### Failure after landing RAW write but before tenant promotion - SQS message may already be acknowledged - landing RAW is durable source of truth - retry comes from promotion state, not SQS ### Failure during normalize/load/staging/curated/export - landing RAW and tenant RAW remain durable - replay comes from tenant RAW or later durable stage input - no need to re-read SQS ### Failure due to unresolved tenant routing - payload remains in landing RAW - promotion state remains `tenant_unresolved` - retry continues until relation state becomes sufficient or payload is explicitly quarantined ## Why Additional Bucket Exists Additional bucket exists to decouple durability from tenant resolution. Without extra landing bucket, intake has to answer both questions before it can safely finish: 1. is payload valid enough to keep? 2. which tenant RAW path should receive it? That is fine for rows with direct `store_guid`, but it is not fine for child entities that require relation lookup. Examples: - `orders` can resolve tenant directly from `store_guid` - `customers` can resolve tenant directly from `store_guid` - `items` may need `order_guid -> store_guid` - `item_bumps` may need `item_guid -> store_guid` or `item_guid -> order_guid -> store_guid` If relation index is temporarily behind, tenant resolution may lag even though message itself is valid and should be durably retained. Landing bucket solves that cleanly. ## Bucket Model Two raw storage areas are recommended. ### 1. Landing RAW bucket Purpose: - immediate durable capture of all valid Debezium messages - no tenant resolution required before write - append-only replay source Landing RAW stores: - original SQS payload body - parsed Debezium envelope or exact raw copy - SQS transport metadata when present - intake timestamp - derived metadata such as source table, operation, primary key, Debezium coordinates - tenant resolution status at time of intake Landing RAW is system-level safety net. ### 2. Tenant RAW bucket Purpose: - tenant-scoped raw source for downstream ETL - stable input boundary for normalize/load jobs - clear replay unit for one tenant Tenant RAW contains only messages whose tenant is known with confidence. Promotion to tenant RAW happens: - immediately in intake flow when tenant is known - later in resolver/promotion flow when tenant becomes known after retry ## Why This Is Better This shape improves all important properties. ### Simplicity Intake path no longer owns curated consistency problem. ### Speed Valid messages can be durably captured without waiting for downstream parent rows. ### Reliability No valid message needs to be discarded or blocked only because relation-based tenant lookup is temporarily unresolved. ### Replayability Landing RAW becomes canonical replay source if routing logic changes later. ### Sustainability Future FIFO metadata, better tenant resolution, or curated redesign can be added without changing raw capture contract. ## Realities We Are Designing For ### 1. Debezium is real input contract Sample payloads available now: - `payloads/customer_update.json` - `payloads/item_create.json` - `payloads/item_update.json` - `payloads/item_bump_create.json` - `payloads/order_create.json` - `payloads/order_update.json` - `payloads/order_delete.json` Observed shared structure: - top-level `schema` - top-level `payload` - business row data under `payload.before` / `payload.after` - table metadata under `payload.source` - change operation under `payload.op` So intake must be shaped around Debezium-native models, not custom top-level KDS CDC envelope fields. ### 2. Queue is FIFO, but `MessageGroupId` is not part of contract Transport decision for this integration is fixed: - queue is FIFO-class - KDS CDC processing does not require `MessageGroupId` - code must not derive batching, routing, or correctness from `MessageGroupId` - `SequenceNumber` may be preserved when present, but it is informational metadata only Durable capture into landing RAW must not depend on `MessageGroupId` or `SequenceNumber`. ### 3. Tenant identity assumption is fixed for this slice For this integration we assume: - `store_guid == tenant_id` - `store_guid` is globally unique - `store_guid` is stable for tenant lifetime Under that assumption, tenant resolution should not depend on `source_account_id`. ### 4. Some entities still have tenant-resolution gap Orders and customers can resolve tenant directly because row images include `store_guid`. Items do not necessarily include `store_guid`. Item bumps also may require relation lookup depending on payload shape and chosen tenant-routing rule. That means intake still needs relation-based routing support, most likely: ```text order_guid -> store_guid item_guid -> store_guid ``` This is not reason to keep `source_account_id`. It is separate correlation problem. ### 5. Native item-bump payloads do exist Current samples do include native `item_bumps` payloads. That means integration should support native `item_bumps` directly. Do not design around mandatory derivation from `items`. Derived behavior is not baseline design. ## Invariants These rules should stay true across implementation. ### 1. Landing RAW is append-only Never mutate or reinterpret original message in place. ### 2. Tenant RAW is promoted, not invented Every tenant RAW object should trace back to one captured landing RAW object. ### 3. Unresolved tenant routing is not failure of payload durability If message is valid Debezium payload but tenant is not yet resolvable, keep it in landing RAW and mark routing state accordingly. ### 4. Curated correctness comes from staging state Do not depend on arrival order of `orders`, `items`, and `item_bumps` to produce correct curated result. ### 5. Ordering metadata is informative first, blocking second Debezium coordinates and FIFO metadata should be preserved and used for dedupe/replay, but missing FIFO metadata should not prevent landing capture. ## Intake Lifecycle In Detail ### Step 1. Receive from SQS Worker receives message and captures transport metadata: - `MessageId` - `ReceiptHandle` - `MessageGroupId` when present in transport, but processing ignores it as contract input - `SequenceNumber` when present - receive timestamp ### Step 2. Parse Debezium envelope Worker validates enough to determine: - source table - operation - selected row image candidate - primary key - Debezium source coordinates If payload is not valid supported Debezium envelope, route to quarantine or poison path. ### Step 3. Write landing RAW immediately This should happen before any expensive downstream dependency work. Write object containing: - exact original payload - parsed metadata snapshot - intake metadata - initial routing state such as `tenant_resolved`, `tenant_unresolved`, or `invalid` After this point, message is durably retained even if later steps fail. ### Step 4. Attempt tenant resolution Resolution paths: - direct from `store_guid` - from persistent `order_guid -> store_guid` index - from persistent `item_guid -> store_guid` index If tenant resolves now: - promote/copy message to tenant RAW - record promotion state If tenant does not resolve now: - keep message in landing RAW - enqueue retry/promotion work - do not guess tenant ### Step 5. Ack SQS after landing durability Ack is tied to successful landing RAW persistence, not to successful curated application. That is core simplification. ## Tenant Resolution Model Tenant resolution now has one job only: ```text decide safe tenant RAW destination ``` It should not also carry burden of immediate curated correctness. Rules: - `customers`: direct `store_guid` - `orders`: direct `store_guid` - `items`: use row `store_guid` if ever present; otherwise `order_guid -> store_guid` - `item_bumps`: use direct `store_guid` if present; otherwise `item_guid -> store_guid`; if needed fallback through `item_guid -> order_guid -> store_guid` Persistent relation indexes are maintained by ETL-owned state, not process memory only. Recommended indexes: - `order_guid -> store_guid` - `item_guid -> store_guid` - optional `item_guid -> order_guid` if useful for reconstruction ## Promotion And Retry Model Promotion from landing RAW to tenant RAW should be explicit workflow, not hidden side effect. Promotion states: - `captured` - `tenant_resolved` - `tenant_promoted` - `tenant_unresolved` - `quarantined` Retry reasons: - missing relation index entry - temporary mapping/state outage - malformed but retryable metadata extraction failure Non-retry reasons: - unsupported payload contract - missing required primary key - impossible entity inference Use explicit durable promotion state. Do not use complicated in-memory batch heuristics. ## Ordering, Dedupe, And Consistency ### Ordering Ordering should be split into two concerns. Transport metadata: - preserve FIFO metadata when present - do not depend on `MessageGroupId` - do not require `SequenceNumber` for landing capture - do not model correctness around transport grouping Source ordering: - preserve Debezium coordinates from `payload.source` - compare structured token using binlog file + position + row - never compare only trailing numeric suffix ### Dedupe Dedupe should be based on stable source identity, not on process-local memory only. Candidate identity: ```text source.db + source.table + primary_key + source coordinates ``` Landing RAW may keep duplicates for audit, while downstream ETL applies idempotent dedupe during normalize/load. ### Consistency Parent/child consistency should move to async ETL phase. This means: - intake does not need to wait for `orders` before retaining `items` - intake does not need to wait for `items` before retaining `item_bumps` - curated layer should be rebuilt from tenant staging state, not from current arrival order window ## Curated Rebuild Strategy This is final direction for semantic correctness. Instead of treating each incoming tenant/entity slice as final incremental truth for curated tables, system does: 1. load normalized data into tenant staging 2. maintain ETL state there 3. rebuild affected curated targets from staging state for that tenant Benefits: - child rows are not silently skipped forever because parent arrived later - rebuild is deterministic from current staging state - arrival disorder becomes operational concern, not correctness hazard - ETL state becomes place where dependency completeness is evaluated This is required design. Strict intake-time dependency gating is not target architecture. ## Current Code Mismatches ### 1. Parser is still shaped around simulator/custom contract Current code: - `backend/Kds.Ai.Integrations/KdsCdc/KdsCdcEnvelopeParser.cs` - `backend/Kds.Ai.Integrations/KdsCdc/Messages/KdsCdcEnvelope.cs` - `backend/Kds.Ai.Integrations/KdsCdc/Messages/KdsCdcContractProfile.cs` Current parser still expects simulator/custom-style top-level fields such as: - `source_type` - `source_account_id` - top-level `store_guid` - `entity` - `operation` - `primary_key` - custom sequence metadata Real Debezium payload does not match that shape. Required correction: - replace parser/model expectations with Debezium-native envelope models - remove simulator-first contract branching ### 2. Current code still assumes transport metadata as required contract Current parser rejects messages when `MessageGroupId` or `SequenceNumber` is missing. That does not match final target architecture. Required correction: - transport model must allow nullable `MessageGroupId` - transport model must allow nullable `SequenceNumber` - landing RAW write must not depend on FIFO metadata presence - telemetry/diagnostics may record missing metadata, but must not block intake ### 3. Tenant resolution still depends on `source_account_id` Current code: - `backend/Kds.Ai.Integrations/KdsCdc/KdsCdcTenantResolver.cs` - `backend/Kds.Ai.Data/Entities/KdsCdcSourceTenantMapping.cs` - `backend/Kds.Ai.Data/Repositories/EfKdsCdcSourceTenantMappingRepository.cs` Current mapping model is still: ```text sourceType + sourceAccountId + storeGuid -> companyId + tenantId ``` That is now implementation debt. Required correction: - simplify mapping to `sourceType + storeGuid -> tenant` - remove `source_account_id` dependency from resolver contracts - keep one active mapping rule per `sourceType + storeGuid` ### 4. Intake is too coupled to immediate tenant-slice batching Current flow resolves tenant and batches toward downstream processing before raw durability is established as separate first-class boundary. Required correction: - establish landing RAW as first durable boundary - allow tenant promotion after capture - make downstream batching depend on tenant RAW, not direct SQS receive order ### 5. Debezium ordering token semantics are not safe yet Current code: - `backend/Kds.Ai.Integrations/KdsCdc/KdsCdcOrderingToken.cs` Current production ordering logic effectively parses only trailing numeric suffix from coordinates. That is unsafe for Debezium/MySQL coordinates such as: ```text mysql-bin-changelog.237363:824:0 ``` Required correction: - build structured ordering token from Debezium metadata - compare using binlog file + position + row, and transaction order metadata if needed ### 6. Normalization reads wrong JSON level Current code: - `backend/Kds.Ai.Integrations/KdsCdc/KdsCdcNormalizeLoadJobs.cs` Current row mappers read fields directly from envelope payload object as if it were already flat row data. Real Debezium payload nests row image under: - `payload.after` for `c`, `u`, `r` - `payload.before` for `d` Required correction: - choose effective row image first - map staging rows from selected row image - preserve full original Debezium envelope in raw layer ### 7. Simulator footprint is still first-class Current repo still contains simulator/mock-specific code and tests, including: - `backend/Kds.Ai.Integrations/KdsCdc/Mock/` - `KdsCdcConstants.MockContractVersion` - mock parser/runtime branches - mock-heavy KDS CDC tests and live harness setup That conflicts with current direction. Required correction: - remove simulator-specific runtime path - replace tests with Debezium-first fixtures based on real sample payloads ### 8. Current curated flow still assumes parent-first availability Current curated SQL joins child tables against already-present parent curated rows. That makes child application sensitive to arrival timing. Required correction: - rebuild curated from tenant staging state - do not treat join-skipped children as successfully applied final state ## Entity-Specific Compatibility Notes ### Orders Current sample coverage: - create - update - delete Compatibility expectations: - entity inferred from `payload.source.table` - PK derived from selected row image - tenant resolved directly from `store_guid` - delete uses `before` row image - order events should refresh `order_guid -> store_guid` index ### Customers Current sample coverage: - update Compatibility expectations: - tenant resolved directly from `store_guid` - row image selected from Debezium `before` / `after` ### Items Current sample coverage: - create - update Compatibility expectations: - PK derived from item row image - tenant may not be resolved directly because `store_guid` can be absent - relation-based lookup is required before tenant RAW promotion when direct tenant is absent - item events should refresh `item_guid -> store_guid` index after tenant becomes known ### Item Bumps Current sample coverage: - create Compatibility expectations: - support native `item_bumps` payloads directly - tenant resolution may depend on relation lookup - do not require derivation from `items` as baseline design ## Required Implementation Direction ### A. Debezium-only intake contract Required end state: - parser understands Debezium envelope directly - no simulator/custom top-level contract dependency - one production intake contract, not two parallel contracts ### B. Two-stage raw durability Required end state: - every valid supported Debezium message lands in landing RAW quickly - tenant RAW is populated by direct promotion or later retry promotion - SQS ack depends on landing durability, not curated completion ### C. Transport contract without `MessageGroupId` dependency Required end state: - code does not depend on `MessageGroupId` - code preserves transport metadata when present - code accepts nullable `SequenceNumber` - durable capture does not wait for transport ordering metadata ### D. Simplified tenant identity Required end state: - `store_guid` is canonical tenant identity for KDS CDC - mapping no longer requires `source_account_id` - relation lookup is used only when row lacks direct tenant identity ### E. Persistent routing indexes Required end state: - resolver uses durable state, not only in-memory caches - `order_guid -> store_guid` exists - `item_guid -> store_guid` exists - unresolved routing has explicit retry path ### F. Debezium-native ordering and dedupe Required end state: - duplicate detection no longer depends on simulator branch logic - ordering token uses structured Debezium source metadata - raw/checkpoint details tolerate null FIFO sequence numbers - ordering metadata is preserved even when it is not intake gate ### G. Row-image-aware normalization Required end state: - normalizer maps from selected Debezium row image - order/customer/item/item_bump payloads normalize correctly from current samples ### H. Curated rebuild from staging state Required end state: - curated targets rebuild from tenant staging state - child correctness no longer depends on message arrival order - ETL state owns dependency completeness ### I. Simulator removal Required end state: - simulator producer/factory/runtime path removed - Debezium sample payloads become source for parser/integration tests - docs describe Debezium as only supported contract ## Recommended Execution Path Use implementation spec in this order: 1. `../../../specs/KdsCdcDebeziumIntegration/TASK1.md` 2. `../../../specs/KdsCdcDebeziumIntegration/TASK2.md` 3. `../../../specs/KdsCdcDebeziumIntegration/TASK3.md` 4. introduce landing RAW plus tenant promotion boundary 5. rework normalize/load and curated rebuild around tenant RAW and staging-state rebuild 6. `../../../specs/KdsCdcDebeziumIntegration/TASK4.md` 7. `../../../specs/KdsCdcDebeziumIntegration/TASK5.md` ## Bottom Line Current KDS CDC integration must be treated as Debezium migration, not simulator hardening. Main decisions are now: - Debezium is only target contract. - simulator code should go. - queue is FIFO-class, but processing does not depend on `MessageGroupId`. - `store_guid == tenant_id` for this flow. - fastest required win is durable capture into landing RAW. - tenant RAW should be fed by direct or delayed promotion. - ETL state should own dependency handling. - curated targets should be rebuilt from tenant staging state. Main remaining work is implementation, not product discovery.