KDS CDC Durable Relation Routing - Implementation Tasks

Overview

Implementation of the KDS CDC Durable Relation Routing slice.

This task plan is derived from:

• ../../docs/features/ETL/kds-cdc/index.md
• ../../docs/features/ETL/kds-cdc/implementation-breakdown.md
• ../../docs/features/ETL/kds-cdc/debezium-payload-compatibility-notes.md
• ../../docs/features/ETL/kds-cdc/branch-direction-and-flow.md
• ../KdsCdcDebeziumIntegration/TASK.md
• ../KdsCdcDebeziumIntegration/TASK2.md
• ../KdsCdcDebeziumIntegration/TASK3.md
• ../KdsCdcIngestion/TASK8.md
• ../KdsCdcIngestion/TASK9.md

Repository analysis for this spec also reviewed the current KDS CDC implementation in:

• backend/Kds.Ai.Integrations/KdsCdc/KdsCdcTenantResolver.cs
• backend/Kds.Ai.Integrations/KdsCdc/KdsCdcEnvelopeParser.cs
• backend/Kds.Ai.Integrations/KdsCdc/KdsCdcMessageOrderingValidator.cs
• backend/Kds.Ai.Integrations/KdsCdc/KdsCdcDuplicateDetector.cs
• backend/Kds.Ai.Integrations/KdsCdc/KdsCdcExtractJobs.cs
• backend/Kds.Ai.Integrations/KdsCdc/KdsCdcNormalizeLoadJobs.cs
• backend/Kds.Ai.Integrations/KdsCdc/Raw/KdsCdcRawArtifactWriter.cs
• backend/Kds.Ai.ApiService/Jobs/KdsCdc/KdsCdcWorkerService.cs
• backend/Kds.Ai.Data/Entities/KdsCdcSourceTenantMapping.cs
• backend/Kds.Ai.Data/Repositories/EfKdsCdcSourceTenantMappingRepository.cs
• backend/tests/Kds.Ai.Integrations.Tests/KdsCdc/

The current branch already accepts Debezium-native KDS CDC messages, routes direct store_guid traffic by store_guid == tenant_id, and keeps child rows without direct store_guid in retryable TenantUnresolved state instead of quarantining them.

However, runtime investigation confirmed a concrete gap:

• items can arrive without direct store_guid,
• item_bumps can arrive without direct store_guid,
• worker currently has no durable relation index for parent-to-child routing,
• unresolved child traffic stays in SQS retry/visibility loop,
• FIFO transport therefore still suffers head-of-line blocking when parent-derived tenant resolution is missing.

This spec closes that gap by adding:

• durable Postgres-backed relation indexes,
• relation-aware tenant resolution,
• explicit landing RAW plus tenant RAW promotion state,
• durable retry flow for tenant_unresolved records,
• observability and guardrails around relation-based resolution.

Source Documents

• ../../docs/features/ETL/kds-cdc/index.md
• ../../docs/features/ETL/kds-cdc/implementation-breakdown.md
• ../../docs/features/ETL/kds-cdc/debezium-payload-compatibility-notes.md
• ../../docs/features/ETL/kds-cdc/branch-direction-and-flow.md
• ../KdsCdcDebeziumIntegration/TASK.md
• ../KdsCdcDebeziumIntegration/TASK2.md
• ../KdsCdcDebeziumIntegration/TASK3.md
• ../KdsCdcIngestion/TASK8.md
• ../KdsCdcIngestion/TASK9.md

Spec Name

This implementation spec should be referred to as KDS CDC Durable Relation Routing.

Fixed Decisions

• Debezium payload remains the only supported KDS CDC intake contract.
• store_guid == tenant_id remains the canonical KDS CDC routing identity.
• source_account_id remains out of required routing identity.
• Redis is not the source of truth for this feature.
• Durable child-routing state must live in Postgres/application persistence.
• orders establish durable order_guid -> store_guid relation state.
• resolved items establish durable item_guid -> store_guid relation state.
• items resolve by direct store_guid when present, otherwise by order_guid -> store_guid.
• item_bumps resolve by direct store_guid when present, otherwise by item_guid -> store_guid.
• relation rows are latest-state index rows, not append-only event history.
• relation rows must be updated idempotently and protected against stale older Debezium writes.
• delete events must not immediately erase relation state.
• valid CDC payloads must be durably captured before they depend on tenant resolution success.
• unresolved child records must leave SQS hot path and retry from durable state.
• no historical relation backfill is included in this slice.
• no retention/cleanup policy work is included in this slice.
• no Redis acceleration/cache layer is included in this slice.

Scope

This task plan includes:

• adding durable relation index persistence in Postgres,
• adding relation index read/write repositories and compare-on-write semantics,
• updating tenant resolution to use direct store_guid or durable relations depending on entity,
• introducing landing RAW durability before tenant promotion,
• introducing durable promotion state for resolved and unresolved landing records,
• introducing retryable promotion flow independent of SQS receipt visibility,
• keeping unsupported/malformed payloads on explicit durable quarantine path,
• adding diagnostics, telemetry, and tests for relation-based routing and unresolved promotion behavior.

In short, this slice ends when KDS CDC child rows no longer rely on immediate parent arrival inside the SQS hot path and instead resolve through durable relation state plus durable landing/promotion flow.

Explicitly Skipped In This Spec

Per user direction, the following are explicitly excluded even though they may be valid future work:

• historical relation backfill from warehouse or Redshift,
• relation retention/cleanup policy,
• Redis cache or read-through acceleration,
• broad ETL redesign outside landing/promotion changes required for this fix,
• changes to Debezium payload contract,
• changes to curated export narrowing behavior,
• admin UI for unresolved/promotion management.

Out of Scope

The following items are explicitly excluded from this task plan:

• rebuilding historical relation state from already-loaded warehouse data,
• adding TTL or cleanup jobs for relation rows,
• using Redis as correctness store or accelerator,
• replacing existing ETL stage model after tenant RAW promotion,
• broad warehouse schema redesign unrelated to KDS CDC routing durability,
• speculative multi-source generic relation framework unless concrete reuse appears.

Task Checklist

Phase 1: Durable Relation Index Persistence

• [ ] TASK 1 - Add authoritative Postgres-backed order/item relation indexes and stale-write-safe upsert behavior

Sources:

• ../../docs/features/ETL/kds-cdc/branch-direction-and-flow.md#tenant-assumptions
• ../KdsCdcDebeziumIntegration/TASK2.md

Phase 2: Relation-Aware Tenant Resolution

• [ ] TASK 2 - Resolve items and item_bumps through durable relation state and write new child relations on success

Sources:

• ../../docs/features/ETL/kds-cdc/debezium-payload-compatibility-notes.md
• ../../docs/features/ETL/kds-cdc/branch-direction-and-flow.md#6-tenant-resolution

Phase 3: Landing RAW And Promotion State

• [ ] TASK 3 - Move valid payload durability ahead of tenant resolution and add durable promotion-state model

Sources:

• ../KdsCdcDebeziumIntegration/TASK3.md
• ../../docs/features/ETL/kds-cdc/branch-direction-and-flow.md#7-raw-capture-and-handoff

Phase 4: Promotion Retry Worker And Observability

• [ ] TASK 4 - Retry tenant_unresolved landing records from durable state and surface relation-resolution diagnostics

Sources:

• ../KdsCdcIngestion/TASK8.md
• ../KdsCdcIngestion/TASK9.md

Phase 5: Verification And Guardrails

• [ ] TASK 5 - Add deterministic and live-readiness coverage for relation-based routing, landing/promotion, and unresolved recovery

Sources:

• ../KdsCdcIngestion/TASK9.md
• ../../docs/features/ETL/kds-cdc/index.md#supported-corner-cases

Dependency Graph

Phase 1
    +-- TASK 1 freezes durable relation tables, keys, and upsert semantics

Phase 2
    +-- TASK 2 relation-aware tenant resolution <-- depends on TASK 1

Phase 3
    +-- TASK 3 landing RAW and promotion state <-- depends on TASK 1, TASK 2

Phase 4
    +-- TASK 4 promotion retry worker and observability <-- depends on TASK 1, TASK 2, TASK 3

Phase 5
    +-- TASK 5 verification and guardrails <-- depends on TASK 1, TASK 2, TASK 3, TASK 4

Recommended Session Order

Key Design Decisions

Durable Relation State Must Be Authoritative

The unresolved-child problem is a correctness problem, not only a speed problem. Therefore relation state must survive worker restarts, SQS redelivery, multiple instances, and delayed child updates. Postgres is the required source of truth.

Landing Before Resolution Is Required To Break FIFO Retry Churn

If unresolved children stay in the SQS hot path, reducing visibility only changes how often the same blocked messages reappear. It does not remove FIFO head-of-line blocking. Therefore the system must durably capture valid messages before tenant resolution fully succeeds.

Relation Index Is Latest-State, Not Full Event History

This spec does not introduce append-only relation history. It introduces latest-known durable routing state keyed by source database plus business GUID. That keeps storage bounded without introducing TTL-dependent correctness loss.

No Backfill In This Slice

By user direction, this spec only covers new CDC data. That means rollout assumptions must be explicit: relation resolution is guaranteed only after parent/newer rows flow through this new path.

No Cleanup In This Slice

Retention work is intentionally deferred. The first priority is correctness and operational stability, not pruning. Relation rows should remain durable until later policy work defines safe cleanup windows.

No Redis In This Slice

Redis may be valid later as optional acceleration, but not for this delivery. This spec intentionally avoids adding a cache dependency so correctness and tests remain centered on durable application persistence.

Child Deletes Still Need Last-Known Relation State

Delete events can still require route resolution after the parent or child was deleted in source. Therefore delete processing must not eagerly remove relation rows.

Preserve Existing ETL Spine After Promotion

This spec changes intake durability and routing, not the downstream normalized/load/staging/curated/export stage model after tenant RAW promotion.