# TASK 3: Rework Intake Durability, Landing RAW, Promotion Flow, Ordering Metadata, And Raw Persistence For Debezium Semantics

## Summary

Align intake durability, landing RAW capture, tenant RAW promotion, duplicate detection, and ordering metadata with Debezium semantics and final ETL architecture.

## Current Codebase Findings

Repository analysis shows:

- `KdsCdcBatchBuilder` assumes one open batch has one `MessageGroupId`.
- `KdsCdcDuplicateDetector` still branches on mock vs production behavior and uses transport FIFO metadata directly.
- `KdsCdcMessageOrderingValidator` keeps separate mock and production logic.
- `KdsCdcOrderingToken` currently parses only the last numeric suffix from `SourceCoordinates`, which is unsafe for values like `mysql-bin-changelog.237363:824:0`.
- worker resolves tenant and hands off tenant slices before raw durability is established as separate first-class boundary.
- extract/raw details and checkpoints store `FirstSequenceNumber` and `LastSequenceNumber`, reflecting FIFO assumptions.

## Dependencies

- TASK 1
- TASK 2

## Source References

- `../../docs/features/ETL/kds-cdc/debezium-payload-compatibility-notes.md`
- `../../docs/features/ETL/kds-cdc/index.md#stage-2-worker-pull-and-micro-batch-creation`
- `../../docs/features/ETL/kds-cdc/index.md#stage-4-store-to-s3-raw-extract_raw`

## Scope From Breakdown

This task covers:

- landing RAW durability boundary,
- tenant RAW promotion flow,
- Debezium ordering token design,
- duplicate key design,
- raw metadata persistence and checkpoint shape updates where required.

## Files to Modify

- `backend/Kds.Ai.Integrations/KdsCdc/KdsCdcBatchBuilder.cs`
- `backend/Kds.Ai.Integrations/KdsCdc/KdsCdcDuplicateDetector.cs`
- `backend/Kds.Ai.Integrations/KdsCdc/KdsCdcMessageOrderingValidator.cs`
- `backend/Kds.Ai.Integrations/KdsCdc/KdsCdcOrderingToken.cs`
- `backend/Kds.Ai.ApiService/Jobs/KdsCdc/KdsCdcWorkerService.cs`
- `backend/Kds.Ai.Integrations/KdsCdc/KdsCdcExtractJobs.cs`
- `backend/Kds.Ai.Integrations/KdsCdc/KdsCdcCheckpointSupport.cs`
- raw/details/checkpoint models that currently assume `SequenceNumber` always exists
- promotion-state or intake-state models if new durable seam is introduced

## Required Behavior

### Landing RAW first

Valid supported Debezium messages must be durably written to landing RAW before tenant promotion and before downstream ETL assumptions.

SQS acknowledgement must depend on landing RAW durability, not on downstream stage completion.

Required acknowledgement behavior:

- landing write fails: do not acknowledge SQS; allow redelivery,
- landing write succeeds and tenant resolves: acknowledge SQS after landing/promotion state is durable,
- landing write succeeds and tenant is unresolved: acknowledge SQS and retry from durable promotion state,
- landing write succeeds and tenant promotion fails transiently: acknowledge SQS and retry promotion from durable state,
- unsupported or malformed payload: acknowledge only after quarantine or poison diagnostics are durably persisted.

### Tenant RAW promotion second

After landing RAW capture, intake or dedicated promotion flow must:

- attempt tenant resolution,
- promote to tenant RAW when routing is safe,
- leave unresolved payloads in landing RAW with durable retry state.

Do not guess tenant for unresolved child entities.

Promotion must be idempotent. Reprocessing the same landing record or receiving the same SQS payload again must not create duplicate tenant RAW promotion or duplicate downstream staging application.

Promotion state must record enough information to retry without SQS:

- landing RAW object key,
- source table,
- operation,
- primary key,
- Debezium source coordinates,
- current routing status,
- retry reason,
- retry count or retry policy state,
- last attempt timestamp,
- tenant id when resolved,
- tenant RAW object key when promoted.

Retry state transitions must distinguish retryable unresolved routing from final quarantine. Valid child rows with missing relation indexes should remain retryable `tenant_unresolved` until relation state appears or an explicit exhaustion/non-retry policy is reached.

### Ordering token redesign

Production ordering must stop using `last numeric suffix` parsing.

New comparable ordering token should be derived from Debezium metadata such as:

- binlog file ordinal,
- position,
- row,
- optional transaction ordering metadata when present.

Minimum comparison behavior:

- compare binlog file ordinal/name first,
- compare position second,
- compare row third,
- never compare only the last numeric suffix,
- preserve raw Debezium coordinates even when a comparable token cannot be produced,
- inability to produce a comparable token must not block landing RAW capture for otherwise valid payloads.

### Transport metadata behavior

Transport metadata is preserved when present, but final design does not let it define correctness contract.

Required behavior:

- code must continue to capture supported payloads without `MessageGroupId`,
- raw/checkpoint models must tolerate null FIFO sequence fields,
- operational telemetry should make transport metadata presence or absence visible.

### Raw durability

Raw stages must preserve:

- original Debezium envelope body,
- parsed source metadata,
- nullable actual FIFO metadata,
- ordering token or normalized coordinates used downstream.

Landing RAW must additionally preserve:

- intake timestamp,
- promotion status,
- enough metadata to retry routing without re-reading SQS.

### Stable identity and duplicate behavior

Stable downstream identity should be based on Debezium source identity, not SQS delivery identity.

Candidate identity:

```text
source.db + source.table + primary_key + source.file + source.pos + source.row
```

Required behavior:

- landing RAW may retain duplicate deliveries for audit if desired,
- promotion and downstream ETL must be idempotent by stable source identity,
- duplicate SQS redelivery after landing write but before ack must converge on the existing promotion state,
- duplicate SQS redelivery after promotion must not create another tenant RAW logical event,
- `MessageId`, `MessageGroupId`, and `SequenceNumber` may be diagnostic metadata but must not be required for dedupe correctness.

### Debezium non-row and malformed cases

Define explicit handling for Debezium messages that are not normal row changes:

- null payload tombstones,
- connector heartbeat/control messages if they can reach the queue,
- missing `payload.source`,
- missing `payload.op`,
- missing source table,
- missing source coordinates,
- missing primary key in selected row image.

Unsupported non-row/control messages should go to quarantine or poison diagnostics after durable recording of the original payload. Missing optional ordering metadata should be diagnostic unless it prevents stable identity construction.

## Testing

Add tests for:

- comparable Debezium ordering token generation,
- ordering comparisons across different binlog coordinates,
- landing RAW write before downstream handoff,
- promotion behavior for resolved and unresolved tenant routing,
- duplicate detection under Debezium-native identity,
- raw details/checkpoints persisting nullable FIFO metadata without failure,
- telemetry or diagnostics preserving transport metadata without making it required.
- landing succeeds plus unresolved tenant still acknowledges SQS,
- landing succeeds plus transient promotion failure still acknowledges SQS and records retry state,
- landing fails does not acknowledge SQS,
- malformed payload is acknowledged only after durable quarantine/poison diagnostics,
- duplicate redelivery converges on existing landing/promotion state,
- null Debezium tombstone or non-row/control message follows explicit unsupported-message path.

## Acceptance Criteria

- [ ] Landing RAW is established as first durable intake boundary
- [ ] SQS ack depends on landing RAW durability
- [ ] Tenant RAW promotion exists as explicit step after capture
- [ ] Production ordering token uses structured Debezium coordinates
- [ ] Duplicate detection no longer depends on simulator-specific branches
- [ ] Raw stage persists full Debezium payload plus parsed metadata
- [ ] Checkpoint/details models tolerate missing FIFO sequence numbers
- [ ] Ack behavior is explicitly tied to landing/quarantine durability, not tenant promotion success
- [ ] Promotion state is durable and idempotent across SQS redelivery
- [ ] Retryable `tenant_unresolved` records can be retried without SQS
- [ ] Debezium tombstone/control/missing-metadata cases have explicit behavior
- [ ] Tests cover landing capture, promotion behavior, and Debezium ordering

## Notes

- This task is about durable intake correctness first. Do not attempt broad throughput tuning here beyond what new semantics require.