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/EtlSourceAdapterFramework/TASK2.md 4,924 bytes · Updated Oct 23, 2026 10:44 AM EDT

Raw File

TASK 2: Extract Generic Transactional Curated Target Executor

Summary

Extract the transactional staging_to_curated SQL execution loop from the current Brink implementation into a shared curated target executor.

Implementation Status

Status: implemented.

Verified in code:

shared executor exists in backend/Kds.Ai.Integrations/Etl/CuratedTargetExecutor.cs,
shared request and result models exist under backend/Kds.Ai.Integrations/Etl/CuratedTarget*.cs,
Brink curated stage uses ICuratedTargetExecutor in backend/Kds.Ai.Integrations/BrinkPos/BrinkPosStagingToCuratedJobs.cs,
commit and rollback behavior is covered in backend/tests/Kds.Ai.Integrations.Tests/Etl/CuratedTargetExecutorTests.cs.

The current BrinkPosStagingToCuratedJobs contains behavior that will repeat for every source:

open tenant warehouse connection,
start transaction,
execute per-target delete,
execute per-target insert or merge,
collect diagnostics,
commit or rollback.

This task should move that behavior into a reusable shared executor while leaving target definitions source-owned.

Dependencies

TASK 0

Files to Modify

1. Shared executor

backend/Kds.Ai.Integrations/BrinkPos/BrinkPosStagingToCuratedJobs.cs
shared abstractions and implementations in:
backend/Kds.Ai.Warehouse/
and/or a new framework-oriented backend location if needed

2. Supporting models

curated target definition/result models in shared or source-specific locations only where needed

3. Tests

add or extend tests under:
backend/tests/Kds.Ai.Integrations.Tests/BrinkPos/
backend/tests/Kds.Ai.Data.Tests/

Required Behavior

Shared transaction boundary

The executor must own the transaction boundary for the full curated target execution unit it runs.

If a target execution fails, the transaction must roll back.

Source-owned target definitions

The executor must not own source-specific SQL content.

Adapters must continue to provide:

target name,
required staging dependencies,
SQL template identifier,
delete SQL,
insert or merge SQL.

Shared diagnostics collection

The executor must return per-target execution diagnostics that source stages can include in stage details.

Diagnostics should include at minimum:

target name,
SQL template id,
deleted rows,
inserted or merged rows,
started and completed timestamps,
execution status.

Proposed Contracts

Suggested minimal shared contracts:

csharp

public interface ICuratedTargetExecutor
{
    Task<IReadOnlyList<CuratedTargetExecutionResult>> ExecuteAsync(
        CuratedTargetExecutionRequest request,
        CancellationToken ct = default);
}

public sealed record CuratedTargetExecutionRequest(
    TenantWarehouseContext Warehouse,
    string TenantId,
    DateOnly BusinessDate,
    IReadOnlyList<CuratedTargetDefinition> Targets);

public sealed record CuratedTargetDefinition(
    string TargetName,
    IReadOnlyList<string> RequiredStagingTables,
    string SqlTemplateId,
    string DeleteSql,
    string UpsertSql);

public sealed record CuratedTargetExecutionResult(
    string TargetName,
    string SqlTemplateId,
    string Status,
    int DeletedRows,
    int WrittenRows,
    DateTimeOffset StartedAt,
    DateTimeOffset CompletedAt);

If implementation needs separate InsertSql vs MergeSql, that is acceptable. The framework contract only needs one write-operation slot semantically.

Proposed Execution Shape

Expected executor flow:

open tenant warehouse connection,
begin transaction,
loop targets in declared order,
execute delete SQL with tenant_id and business_date parameters,
execute insert or merge SQL with the same scope parameters,
collect per-target diagnostics,
commit transaction only after all targets succeed,
rollback whole execution unit on any failure.

Boundary Guidance

The executor should not decide:

which curated targets exist,
what SQL is valid for Brink,
what staging dependencies a source requires.

The executor should decide only:

how to execute the declared targets transactionally,
how to parameterize the partition scope,
how to report execution results consistently.

Acceptance Criteria

the curated SQL transaction loop is extracted from BrinkPosStagingToCuratedJobs,
shared executor behavior is source-agnostic,
Brink target definitions remain adapter-owned,
tests cover commit and rollback behavior or the closest practical observable contract.

Notes

This task is about executing curated targets, not choosing which targets exist.
Keep required staging dependency validation either in the stage or in a small shared helper, but do not move source dependencies into the framework core.
Prefer keeping dependency validation outside the low-level executor unless multiple adapters need the same validation helper.