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:

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:

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

flowchart TD
    A[SQS FIFO queue<br/>Debezium messages] --> B[Phase 0: Intake worker<br/>receive message]
    B --> C{Supported Debezium envelope?}
    C -- no --> Q1[Quarantine / poison path<br/>store payload and diagnostics]
    C -- yes --> D[Extract intake metadata<br/>table op pk source coordinates transport metadata]
    D --> E[Write landing RAW object<br/>append-only immutable capture]
    E --> F[Create or update promotion state<br/>captured]
    F --> G[Acknowledge SQS message]

    G --> H[Phase 1: Promotion worker]
    H --> I{Tenant resolvable now?}
    I -- no --> J[Keep in landing RAW<br/>mark tenant_unresolved<br/>schedule retry]
    I -- yes --> K[Promote or copy to tenant RAW<br/>mark tenant_promoted]

    K --> L[Phase 2: normalize_load]
    L --> M[Select Debezium row image<br/>before or after]
    M --> N[Normalize by tenant and entity<br/>write load artifacts]

    N --> O[Phase 3: load_to_staging]
    O --> P[Redshift COPY into staging tables]
    P --> R[Update ETL relation state<br/>order_guid -> store_guid<br/>item_guid -> store_guid]

    R --> S[Phase 4: staging_to_curated]
    S --> T[Rebuild affected curated targets<br/>from tenant staging state]

    T --> U[Phase 5: curated_to_s3]
    U --> V[Export affected curated targets<br/>to S3 curated]

    V --> W[Final state<br/>tenant staging updated<br/>tenant curated updated<br/>curated S3 exported]

    J --> H

Final Phase-By-Phase Flow

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:

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:

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:

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:

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:

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.