Spensara Spensara Knowledge Base & Documentation Repository
System Overview Brink & Borg Master Index (722 Docs) Spensara Platform Project Tracker

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)

Categories

Branches (328)

Branch-specific documentation across extract-raw-xml, feature-fleet-import, kds_cdc_improvments, life-cycle, and tenat-admin.
branches/feature-fleet-import/implementation-specs/specs/ImportFromS3ToRedshift/TASK1.md 6,845 bytes · Updated Oct 23, 2026 10:44 AM EDT
Raw File

TASK 1: Add Durable Per-Tenant/Source Batch Reservation And Resume Support

Implementation Status

Completed: 2026-07-22

  • Added generic source-scope and execution-lease fields plus a PostgreSQL unique partial index for running tenant/source reservations.
  • Added explicit atomic reservation, failed-publication resume, guarded details/status update, and execution-lease repository outcomes.
  • Appended FleetS3 without changing existing enum values; Fleet reservations retain null BusinessDate and existing date-scoped current behavior.
  • Added migration coverage for upgrading existing ingestion batches and PostgreSQL-backed concurrency, resume, lease, terminal-state, and scope-isolation tests.
  • Verification: Kds.Ai.Data.Tests passed 113 runnable tests with 8 opt-in live tests skipped; all 29 focused ingestion repository tests and 3 affected integration fake tests passed. The repository-wide warnings-as-errors build passes with NuGet audit disabled.
  • The standard repository-wide warnings-as-errors build remains blocked outside TASK 1 by existing NU1903 findings for Microsoft.OpenApi 2.0.0 in Kds.Ai.ApiService and its test project.

Summary

Extend operational ingestion metadata with a generic source scope, database-enforced running reservation, and renewable worker lease. This prevents different batches or duplicate deliveries from racing across API nodes and supports safe resume of a failed post-commit publication.

Dependencies

Current Codebase Findings

  • IngestionBatch.BusinessDate is nullable and must remain null for Fleet imports.
  • IIngestionBatchRepository has generic create/status/details methods but no atomic scoped reservation.
  • EfIngestionBatchRepository sets Running at creation and stores enums as strings.
  • current uniqueness/current-batch behavior is keyed by tenant/date/stage and intentionally ignores null business dates.
  • an application-level query for an active batch is not race-safe; the database must enforce the reservation.

Files To Modify

  • backend/Kds.Ai.Data/Entities/IngestionBatch.cs
  • backend/Kds.Ai.Data/Configurations/IngestionBatchConfiguration.cs
  • backend/Kds.Ai.Data/Models/Ingestion/CreateIngestionBatchRequest.cs
  • backend/Kds.Ai.Data/Enums/IngestionSourceType.cs
  • backend/Kds.Ai.Data/Repositories/IIngestionBatchRepository.cs
  • backend/Kds.Ai.Data/Repositories/EfIngestionBatchRepository.cs
  • backend/Kds.Ai.Data/Migrations/ plus model snapshot
  • backend/tests/Kds.Ai.Data.Tests/Ingestion/
  • existing IIngestionBatchRepository fakes in Brink and KDS CDC integration tests.

Schema Change

Add nullable SourceScope VARCHAR(200) to the operational PostgreSQL IngestionBatches table. This is not a curated Redshift schema change.

Add a unique partial index equivalent to:

text
(TenantId, SourceScope)
WHERE Status = 'Running' AND SourceScope IS NOT NULL

The Fleet value is fleet_s3:{canonicalSourceFolder}. The generic name allows later operational reuse without teaching the data layer Fleet semantics.

Existing rows receive null and are unaffected.

Add nullable generic execution-lease fields, such as ExecutionLeaseId VARCHAR(100) and ExecutionLeaseExpiresAt TIMESTAMPTZ. They prevent concurrent execution of duplicate deliveries for the same batch. Lease duration and renewal cadence are server options; takeover is allowed only after expiry.

Required Repository Behavior

Add atomic operations equivalent to:

  • reserve a new scoped running batch;
  • report conflict without exposing provider-specific exceptions;
  • transition a failed publication batch back to Running while reacquiring the same scope;
  • fetch a batch by ID for update and verify expected source type/scope/status;
  • update details and status in one database transaction where phase durability requires it.
  • atomically acquire, renew, and release one execution lease for a running batch.

Reservation input includes:

  • company/customer ID;
  • canonical tenant ID and database;
  • IngestionMode.FileUpload;
  • IngestionSourceType.FleetS3 appended to the enum;
  • null BusinessDate;
  • fleet_s3_direct_to_curated pipeline stage;
  • source scope;
  • initial versioned details.

Translate a unique-index race into a domain conflict result. Do not parse exception text at endpoint level.

Resume Rules

A failed batch may reacquire its source scope only when:

  • source type and source scope match the retry request;
  • details parse successfully and indicate redshiftCommitted = true;
  • publication is incomplete;
  • the batch is not already succeeded or superseded;
  • no other running batch holds the same tenant/source scope.

Reacquiring the operational reservation is necessary but not sufficient. Task 5 must also prove that the current Redshift slice is still stamped with this batch ID before old publication can resume.

Failure Rules

  • enqueue failure after reservation marks the batch failed and releases the partial-index reservation;
  • pre-commit job failure marks Failed with failed_pre_commit details;
  • post-commit publication failure marks Failed with failed_publication details;
  • success marks Succeeded only after publication;
  • setting IsCurrent is not required for Fleet because existing current-batch semantics require one business date.
  • a handled pre-commit failure is terminal for that batch; recovery starts a new import batch rather than reacquiring the failed batch as publication-only work;
  • an automatic or duplicate delivery may continue the same batch only while it remains Running and after acquiring its execution lease;
  • a delivery that observes Failed, Succeeded, superseded, or publication_expired performs no work.

Testing

Add PostgreSQL-backed repository tests proving:

  • one tenant/folder reservation succeeds;
  • a simultaneous duplicate conflicts;
  • a different folder for the same tenant succeeds;
  • the same folder for a different tenant succeeds;
  • failed/succeeded rows no longer hold the running reservation;
  • valid publication resume reacquires the reservation;
  • resume conflicts with a newer running batch;
  • concurrent duplicate deliveries allow only one execution lease holder;
  • an expired lease can be taken over while a live renewed lease cannot;
  • terminal batches cannot reacquire an execution lease;
  • existing date-scoped IsCurrent behavior is unchanged;
  • migration upgrades a database with existing ingestion batches.

Acceptance Criteria

  • per-source concurrency is enforced by PostgreSQL, not process memory;
  • no new operational table is introduced;
  • enum addition is append-only and existing serialized names remain unchanged;
  • Fleet batches use null BusinessDate without entering date-scoped lifecycle helpers;
  • repository callers receive explicit reservation/resume outcomes.