Repository Docs & Knowledge Base

Consolidated Spensara documentation repository (722 Markdown & architecture documents in ../repository)

Built Word Documents (.docx): Spensara Overview (.docx) Capability Assessment (.docx) Brink POS Ingestion Analysis (.docx) The Borg Ingestion Architecture (.docx)

Implementation Specs (157)

Detailed engineering and feature implementation specifications.

implementation-specs/specs/KdsCdcDurableRelationRouting/TASK1.md 4,184 bytes · Updated Oct 23, 2026 10:44 AM EDT

Raw File

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.