# TASK 1: Add Durable Relation Index Persistence

## Summary

Add authoritative Postgres-backed relation indexes for KDS CDC child routing and define idempotent, stale-write-safe upsert semantics.

## Current Codebase Findings

Repository analysis shows:

- current branch maps direct tenant identity by `store_guid` only,
- no durable `order_guid -> store_guid` relation table exists,
- no durable `item_guid -> store_guid` relation table exists,
- unresolved child rows currently remain in retryable `TenantUnresolved` state,
- duplicate and ordering semantics already rely on Debezium coordinates, which can be reused for stale-write protection.

## Dependencies

- `./TASK.md`
- existing Debezium intake work from `../KdsCdcDebeziumIntegration/`

## Source References

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

## Scope From Breakdown

This task covers:

- relation entity design,
- EF configuration and keys,
- repository contracts and implementations,
- monotonic write/update semantics,
- relation-write metadata required for later diagnostics and retry.

## Files to Create

- relation entities under `backend/Kds.Ai.Data/Entities/`:
  - `KdsCdcOrderStoreRelation.cs`
  - `KdsCdcItemStoreRelation.cs`
- EF configurations under `backend/Kds.Ai.Data/Configurations/`
- repository contracts and implementations under `backend/Kds.Ai.Data/Repositories/`
- migration files if this repo uses EF migrations for schema delivery

## Files to Modify

- `backend/Kds.Ai.Data/DbContexts/...` or equivalent registration seam for new entities
- `backend/Kds.Ai.Integrations/KdsCdc/...` DI registration to consume new repositories
- tests under `backend/tests/Kds.Ai.Data.Tests/` and `backend/tests/Kds.Ai.Integrations.Tests/KdsCdc/`

## Required Behavior

### Order relation table

Need durable latest-state table keyed by:

```text
source_database + order_guid
```

Minimum fields:

- `SourceDatabase`
- `OrderGuid`
- `StoreGuid`
- `LastSourceCoordinates`
- `LastComparableOrderingToken`
- `UpdatedAtUtc`

### Item relation table

Need durable latest-state table keyed by:

```text
source_database + item_guid
```

Minimum fields:

- `SourceDatabase`
- `ItemGuid`
- `StoreGuid`
- `OrderGuid` nullable or optional only when genuinely unavailable
- `LastSourceCoordinates`
- `LastComparableOrderingToken`
- `UpdatedAtUtc`

### Upsert semantics

Writes must be idempotent.

Required behavior:

- first valid relation write inserts row,
- same relation replay does not create duplicates,
- newer Debezium coordinates may update row,
- older Debezium coordinates must not roll row backward,
- if newer event changes `StoreGuid` for same key, overwrite and log warning,
- delete events must not remove relation rows automatically.

### Comparable ordering metadata

Relation persistence must store enough ordering metadata to detect stale writes.

At minimum, use:

- raw `SourceCoordinates`,
- comparable normalized ordering token already derived from Debezium coordinates when available.

If incoming write has no comparable token:

- do not block relation write,
- but record raw coordinates for diagnostics.

## Testing

Add tests for:

- insert new order relation,
- insert new item relation,
- replay same relation write is idempotent,
- newer relation write overwrites older state,
- stale older relation write is ignored,
- store change on newer write updates state and surfaces warning/diagnostic,
- delete event does not remove relation row.

## Acceptance Criteria

- [ ] Durable order relation table exists
- [ ] Durable item relation table exists
- [ ] Relation keys include source database plus business GUID
- [ ] Repository supports idempotent reads and upserts
- [ ] Older Debezium writes cannot roll relation state backward
- [ ] Delete events do not erase relation rows by default
- [ ] Tests cover insert, replay, stale write, and newer overwrite semantics

## Notes

- Do not introduce TTL or cleanup behavior here.
- Do not introduce Redis mirror/cache here.
- Keep schema minimal and routing-focused.