# 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](./TASK1.md) - 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](./TASK2.md) - 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](./TASK3.md) - 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](./TASK4.md) - 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](./TASK5.md) - 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

```text
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

| Session | Tasks | Description |
|---|---|---|
| 1 | 1 | Add relation entities, EF configuration, repository seams, and stale-write-safe upsert logic |
| 2 | 2 | Teach tenant resolver to use relation indexes and write child relation state |
| 3 | 3 | Introduce landing RAW durability and tenant promotion state before SQS ack |
| 4 | 4 | Add durable unresolved retry flow and operational diagnostics |
| 5 | 5 | Add tests, live/readiness coverage, and regression guardrails |

---

## 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.