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/KdsCdcDebeziumIntegration/TASK3.md 8,840 bytes · Updated Oct 23, 2026 10:44 AM EDT

Raw File

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