Brink POS Orders First ETL Job - Implementation Tasks

Overview

Implementation of the first Brink POS API pull ETL job for the first delivery slice focused on sales orders, order items, and the related technical contracts emitted from Sales2.GetOrders.

This task plan is derived from the documentation set in:

• ./TASK0.md
• ../../docs/ETL.md
• ../../docs/features/ETL/epics/06-source-adapter-brink-pos-api/source-task.md
• ../../docs/features/ETL/epics/06-source-adapter-brink-pos-api/01-first-job-foundation/index.md
• ../../docs/features/ETL/epics/06-source-adapter-brink-pos-api/01-first-job-foundation/08-first-job-design-input.md
• ../../docs/features/ETL/epics/06-source-adapter-brink-pos-api/01-first-job-foundation/09-first-job-design-v1.md
• ../../docs/features/ETL/epics/06-source-adapter-brink-pos-api/01-first-job-foundation/10-foundation-summary.md
• ../../docs/features/ETL/epics/06-source-adapter-brink-pos-api/01-first-job-foundation/11-raw-to-load-job-design-v1.md
• ../../docs/features/ETL/epics/06-source-adapter-brink-pos-api/01-first-job-foundation/12-missing-relational-load-mappings.md
• ../../docs/features/ETL/epics/06-source-adapter-brink-pos-api/01-first-job-foundation/13-load-to-redshift-staging-job-design-v1.md
• ../../docs/features/ETL/epics/06-source-adapter-brink-pos-api/01-first-job-foundation/14-staging-to-curated-job-design-v1.md
• ../../docs/features/ETL/epics/06-source-adapter-brink-pos-api/01-first-job-foundation/15-end-to-end-pipeline-summary.md

This spec intentionally narrows the current Brink POS groundwork package. The current documentation set still describes a broader v1, but this implementation task is only for the first slice needed now:

• scheduled or manual execution for company_id and tenant_id, with optional businessDate
• streaming extract of Brink SOAP XML
• immutable raw capture in S3
• raw -> load normalization to Parquet
• load -> Redshift staging through COPY
• staging -> curated transforms for the first orders and order_items outputs
• export of curated data back to S3 curated/
• stage-level metadata, checkpoints, and observability

The slice outcome is:

• one tenant-day ETL path that can run from cron or manual trigger,
• a Brink extract boundary based on streaming raw SOAP capture,
• relational load/ contracts decomposed from Sales2.GetOrders,
• Redshift staging load for all required orders and order_items contracts,
• the first curated orders and order_items outputs,
• curated Parquet export to S3,
• end-to-end traceability across raw, load, staging, curated, and exported artifacts.

Source Documents

• ./TASK0.md
• ../../docs/ETL.md
• ../../docs/features/ETL/epics/06-source-adapter-brink-pos-api/source-task.md
• ../../docs/features/ETL/epics/06-source-adapter-brink-pos-api/01-first-job-foundation/index.md
• ../../docs/features/ETL/epics/06-source-adapter-brink-pos-api/01-first-job-foundation/08-first-job-design-input.md
• ../../docs/features/ETL/epics/06-source-adapter-brink-pos-api/01-first-job-foundation/09-first-job-design-v1.md
• ../../docs/features/ETL/epics/06-source-adapter-brink-pos-api/01-first-job-foundation/10-foundation-summary.md
• ../../docs/features/ETL/epics/06-source-adapter-brink-pos-api/01-first-job-foundation/11-raw-to-load-job-design-v1.md
• ../../docs/features/ETL/epics/06-source-adapter-brink-pos-api/01-first-job-foundation/12-missing-relational-load-mappings.md
• ../../docs/features/ETL/epics/06-source-adapter-brink-pos-api/01-first-job-foundation/13-load-to-redshift-staging-job-design-v1.md
• ../../docs/features/ETL/epics/06-source-adapter-brink-pos-api/01-first-job-foundation/14-staging-to-curated-job-design-v1.md
• ../../docs/features/ETL/epics/06-source-adapter-brink-pos-api/01-first-job-foundation/15-end-to-end-pipeline-summary.md

Spec Name

This implementation spec should be referred to as Brink POS Orders First ETL Job.

Fixed Decisions

• This slice is narrower than the current broader Brink POS groundwork package.
• The first implemented transactional scope is limited to Sales2.GetOrders and the related relational contracts needed to build orders and order_items.
• GetCurrentBusinessDate may be used only as a typed helper when businessDate is not provided.
• Every Brink endpoint other than Sales2.GetOrders is out of scope for this slice unless a later explicit scope change is approved.
• The pipeline stages for this task are extract_raw, normalize_load, load_to_staging, staging_to_curated, and publish_curated.
• Each stage is tenant-day scoped and follows 100% success or nothing.
• Raw SOAP XML must be persisted before parse/validation success is accepted.
• Replay starts from persisted upstream artifacts, never from in-memory DTOs.
• For the first POC replay model, rerun for one tenant-day rebuilds the pipeline from persisted raw/ artifacts rather than patching downstream rows in place.
• For the first POC replay model, raw/ and load/ artifacts remain immutable across reruns.
• For the first POC replay model, Redshift staging and curated outputs are replaced per tenant_id + business_date using a delete-and-rebuild strategy.
• S3 layout follows docs/ETL.md using raw/, load/, curated/, and failed/ prefixes.
• In this spec, staging means Redshift staging schema, not a separate S3 top-level prefix.
• Curated export to S3 is part of this task.
• Athena, Glue catalog, and cross-tenant query routing are not part of this task.
• The exact first curated target names must be frozen before staging_to_curated implementation because the current docs mix curated.pos_orders and curated.pos_transactions, and also mix curated.pos_order_items and curated.pos_transaction_items.
• This spec should speak only in terms of orders and order_items unless a concrete final curated name is being fixed.
• Every module must include unit tests.
• Every completed ETL stage must include live E2E validation whenever it can be verified through Aspire-managed access to Brink POS API, AWS, and later Redshift.

Scope

This task includes:

• freezing the narrower orders and order_items v1 scope against the broader Brink POS documents,
• defining the scheduled and manual execution contract for one tenant-day ETL run,
• resolving tenant Brink binding and credentials through the existing source-binding model,
• streaming raw SOAP extraction for Sales2.GetOrders,
• immutable raw capture and persisted-raw validation,
• per-stage ingestion batch lifecycle, rerun, supersede, and checkpoint semantics,
• raw -> load normalization for orders and order_items relational contracts,
• writing orders and order_items Parquet load artifacts to S3 load/,
• tenant-resolved Redshift COPY into the required staging.brink_sales2_* tables,
• staging -> curated transforms for the first orders and order_items outputs,
• export of the curated outputs to S3 curated/,
• structured logging, stage diagnostics, and smoke validation for the full path.

In short, this slice ends when one tenant-day Brink run can move Sales2.GetOrders from Brink SOAP to S3 raw, to S3 Parquet, to Redshift staging, to Redshift curated, and then back to S3 curated with full metadata and checkpoint traceability.

Out of Scope

The following items are explicitly excluded from this task:

• any non-GetOrders Brink extract flow,
• any non-orders or non-order_items curated contract,
• broader Brink catalog enrichment,
• Athena, Glue, and cross-tenant query execution,
• monitor/runtime read-path migration,
• replay control plane beyond the minimum stage rerun and upstream-batch replay hooks,
• non-order Brink domains such as labor, loyalty, inventory, and hourly sales.

Task Checklist

Module 0: Scope And Documentation Alignment

• [x] TASK 0 - Freeze the v1 decision record for orders and order_items
• [x] Freeze the narrower v1 definition of orders + order_items + related contracts against the broader Brink POS groundwork package.
• [x] Decide and document the first curated target names and minimum field set for orders and order_items.
• [x] Update the Brink POS docs that currently broaden the first slice beyond orders and order_items if this narrower scope becomes the new source of truth.
• [x] Define the testing contract for all later modules: mandatory unit tests and live E2E whenever the ETL boundary is verifiable.

Module 1: Source Binding And Dispatch

• [x] TASK 1 - Implement source binding and dispatch for tenant-day ETL start
• [x] Define the job input contract for scheduled and manual execution with company_id, tenant_id, optional businessDate, trigger, and reset semantics.
• [x] Define or implement the dispatch path so cron and manual runs converge into the same tenant extract contract.
• [x] Resolve Brink binding, secret reference, and runtime config inside the Brink resolver boundary rather than in the scheduler.
• [x] Define the tenant-day logical key and current/superseded semantics for the full pipeline.
• [x] Add unit tests for dispatch contracts, tenant-day key logic, and scheduler/manual convergence.

Module 2: Brink Extraction Boundary And Raw Capture

• [x] TASK 2 - Implement live raw extract and S3 raw persistence for GetCurrentBusinessDate and GetOrders
• [x] TASK 2.1 - Implement child extract orchestration and business-date resolution
• [x] TASK 2.2 - Implement Brink raw capture boundary for GetOrders
• [x] TASK 2.3 - Implement S3 raw persistence and persisted-raw validation
• [x] TASK 2.4 - Add unit tests and live E2E validation for extract_raw
• [x] Freeze the Brink boundary so typed control uses IBrinkPosFacade and raw extraction uses IBrinkRawClient, both consuming externally provided Brink connection context.
• [x] Implement streaming Sales2.GetOrders extraction and optional GetCurrentBusinessDate resolution.
• [x] Persist original SOAP XML to S3 raw/ without mutation.
• [x] Validate persisted raw XML for well-formedness, SOAP fault state, and Brink ResultCode success.
• [x] Record endpoint diagnostics and raw artifact references in batch details and failed-package metadata.
• [x] Keep tenant binding lookup, batch semantics, and S3 ownership in Warehouse/app-layer rather than in Kds.Ai.Integrations.BrinkPos.
• [x] Add unit tests for business-date resolution, raw validation, and extract orchestration failure handling.
• [x] Add a live E2E test that calls real Brink POS API and verifies raw XML persistence to S3.

Module 3: Batch Lifecycle, Checkpoints, And Failure Packaging

• [x] TASK 3 - Finalize stage lifecycle, checkpoints, and failed-package behavior
• [x] TASK 3.1 - Extend stage batch and checkpoint contracts
• [x] TASK 3.2 - Implement current-state, supersede, and upstream-batch semantics
• [x] TASK 3.3 - Implement failed-package handling and stage traceability
• [x] TASK 3.4 - Add unit tests and live verification for lifecycle semantics
• [x] Extend the ingestion batch model for stage-specific tenant-day batches and upstream-batch references.
• [x] Implement per-stage is_current and supersede semantics for reruns.
• [x] Implement checkpoints that advance only after full stage success.
• [x] Define the minimal failed/ package shape with error.json, diagnostics, and raw artifact references.
• [x] Ensure end-to-end traceability from request to batch, raw object, load artifact, staging load, curated batch, and curated export.
• [x] Add unit tests for supersede semantics, current-batch resolution, checkpoint advancement rules, and failure packaging.

Module 4: Raw To Load Normalization

• [x] TASK 4 - Implement GetOrders raw-to-load normalization to Parquet contracts
• [x] TASK 4.1 - Implement normalize-load orchestration and upstream raw-batch resolution
• [x] TASK 4.2 - Implement GetOrders relational decomposition and normalization catalog
• [x] TASK 4.3 - Implement load artifact persistence and normalize-load diagnostics
• [x] TASK 4.4 - Add unit tests and live E2E validation for normalize_load
• [x] Freeze the normalization catalog for Sales2.GetOrders.
• [x] Implement full relational decomposition of the GetOrders payload.
• [x] Emit staging.brink_sales2_orders.
• [x] Emit staging.brink_sales2_order_entries.
• [x] Emit staging.brink_sales2_order_entry_voids.
• [x] Emit staging.brink_sales2_order_entry_taxes.
• [x] Emit staging.brink_sales2_order_entry_promotions.
• [x] Emit staging.brink_sales2_order_entry_discounts.
• [x] Emit staging.brink_sales2_order_entry_modifiers.
• [x] Emit staging.brink_sales2_order_entry_modifier_taxes.
• [x] Emit staging.brink_sales2_order_entry_modifier_promotions.
• [x] Emit staging.brink_sales2_order_payments.
• [x] Emit staging.brink_sales2_order_payment_details.
• [x] Emit staging.brink_sales2_order_payment_additional_data.
• [x] Emit staging.brink_sales2_order_taxes.
• [x] Emit staging.brink_sales2_order_discounts.
• [x] Emit staging.brink_sales2_order_promotions.
• [x] Emit staging.brink_sales2_order_surcharges.
• [x] Emit staging.brink_sales2_order_surcharge_taxes.
• [x] Emit staging.brink_sales2_order_deposits.
• [x] Add the ETL metadata envelope and deterministic parent-child keys to every emitted contract.
• [x] Write Parquet load artifacts to S3 load/ and record schema version, row count, and URI diagnostics.
• [x] Add unit tests for GetOrders decomposition, emitted contracts, metadata envelope, and parent-child key consistency.
• [x] Add a live E2E test that starts from a real raw artifact and verifies written Parquet load/ output.

Module 5: Load To Redshift Staging

• [x] TASK 5 - Implement Redshift staging load from Parquet contracts
• [x] TASK 5.1 - Implement load-to-staging orchestration and upstream normalize-load batch resolution
• [x] TASK 5.2 - Implement staging table catalog and Redshift COPY command construction
• [x] TASK 5.3 - Implement Redshift staging load execution, diagnostics, and rerun semantics
• [x] TASK 5.4 - Add unit tests and live E2E validation for load_to_staging
• [x] Define the deterministic contract-to-table catalog for every emitted orders and order_items load contract.
• [x] Implement tenant-resolved Redshift COPY execution for Parquet load artifacts.
• [x] Apply the v1 POC staging replacement strategy: delete tenant-day rows and rebuild from the selected upstream load/ batch.
• [x] Record per-table load diagnostics, S3 inputs, and SQL template identifiers in load_to_staging batch details.
• [x] Validate that every required staging table is loaded before the stage is marked current.
• [x] Add unit tests for contract-to-table mapping, COPY command construction, and staging rerun semantics.
• [x] Add a live E2E test that verifies Redshift staging load from produced Parquet artifacts.

Module 6: Staging To Curated

• [x] TASK 6 - Implement staging -> curated for curated.pos_orders and curated.pos_order_items
• [x] TASK 6.1 - Implement staging-to-curated orchestration and upstream load-to-staging batch resolution
• [x] TASK 6.2 - Freeze curated target dependency matrix and v1 field behavior
• [x] TASK 6.3 - Implement curated SQL catalog, execution, and diagnostics
• [x] TASK 6.4 - Add unit tests and live E2E validation for staging_to_curated
• [x] Freeze the first orders and order_items curated targets and their dependency matrix.
• [x] Implement the SQL template catalog and execution path for staging_to_curated.
• [x] Build the first orders curated transform.
• [x] Build the first order_items curated transform.
• [x] Freeze v1 field behavior so deferred enrichment stays nullable or explicitly fallback-derived instead of widening raw scope.
• [x] Apply tenant-day rerun semantics so only the newest successful curated batch is current.
• [x] Apply the v1 POC curated replacement strategy: delete tenant-day rows and rebuild curated outputs from the selected upstream staging batch.
• [x] Add unit tests for dependency validation, SQL selection, field mapping rules, and rerun/current-state behavior.
• [x] Add a live E2E test that verifies curated rows in Redshift after staging-to-curated execution.

Module 7: Curated Export To S3 (Out of Scope)

• [ ] TASK 7 - Implement curated Parquet export to S3 (Out of Scope)
• [ ] Define which curated entities are exported to S3 curated/ in this slice.
• [ ] Implement post-curated Redshift export to Parquet under the tenant/company partition layout.
• [ ] Record export diagnostics and link them back to the curated batch.
• [ ] Fail the publish stage when required curated export artifacts are missing or incomplete.
• [ ] Add unit tests for export path building, export diagnostics, and publish failure semantics.
• [ ] Add a live E2E test that verifies exported Parquet artifacts in S3 curated/.

Module 8: Monitoring, Verification, And Operations (Out of Scope)

• [ ] TASK 8 - Add operational visibility, replay hooks, and full smoke validation (Out of Scope)
• [ ] Add structured logging, timings, and row-count diagnostics for every stage.
• [ ] Surface per-stage freshness, checkpoint lag, and last successful tenant-day batch.
• [ ] Add smoke validation for one full tenant-day run.
• [ ] Add replay/backfill hooks that restart from persisted raw or from a selected successful upstream batch.
• [ ] Document the operator path for rerun, supersede, and failure investigation.
• [ ] Add unit tests for freshness/reporting logic and replay entry-point rules.
• [ ] Add a live end-to-end smoke test for the full tenant-day ETL path.

Dependency Graph

Module 0 (Scope and documentation alignment)
    +-- freezes the narrower slice and curated naming before implementation details spread

Module 1 (Source binding and dispatch)
    +-- defines how a tenant-day run starts

Module 2 (Brink extraction boundary and raw capture) <-- depends on Module 1
    +-- produces persisted raw SOAP and endpoint diagnostics

Module 3 (Batch lifecycle, checkpoints, and failure packaging) <-- depends on Module 1 and Module 2
    +-- provides stage identity and replay/current-state semantics

Module 4 (Raw to load normalization) <-- depends on Module 2 and Module 3
    +-- produces relational Parquet load artifacts for Sales2.GetOrders orders/order-items contracts

Module 5 (Load to Redshift staging) <-- depends on Module 4
    +-- loads all required orders/order-items staging tables through COPY

Module 6 (Staging to curated) <-- depends on Module 5 and Module 0
    +-- builds the first orders/order-items curated outputs

Module 7 (Curated export to S3) <-- depends on Module 6
    +-- publishes curated Parquet artifacts for downstream use

Module 8 (Monitoring, verification, and operations) <-- cross-cutting, but final smoke validation depends on Modules 2-7

Recommended Session Order

Key Design Decisions

Only Orders Scope

This spec should stay strict: it concerns only orders and order_items. References to kitchen or broader settings scope should not drive the implementation plan for this first task.

One Transactional Backbone First

The first delivery slice should prove the full ETL chain on one transactional backbone. Sales2.GetOrders already contains the relational structure needed to validate the raw, load, staging, curated, and export seams for orders and order_items.

Curated Naming Must Be Frozen Early

The current docs still mix multiple curated naming conventions for order headers and order items. That ambiguity should be resolved before creating staging_to_curated SQL and downstream export contracts.

Stage Isolation Matters

Each stage should have its own batch identity, checkpoint, and upstream-batch reference. That keeps replay, supersede, observability, and partial-failure diagnostics explicit instead of inferred.

POC Replay Stays Full Rebuild

For the first POC, replay should stay operationally simple: select one tenant-day upstream boundary, keep raw/ and load/ immutable, and rebuild downstream staging plus curated state by deleting tenant-day rows and loading them again. Avoid partial row patching or merge-style semantics until the simple tenant-day rebuild flow proves stable.

Raw, Load, Staging, Curated, And Export Are One End-To-End Slice

This task is not only an extract_raw slice. The goal is one complete tenant-day ETL path that reaches Redshift curated data and exports it back to S3 curated/.

Testing Is Part Of Done

No module is complete without unit tests. No finished ETL boundary should be considered complete without live E2E validation when Aspire-managed secrets make the real integration reachable.

Non-GetOrders Enrichment Stays Deferred

The first slice should not silently pull in any non-GetOrders Brink endpoint just because broader documents mention it. If orders or order_items outputs need fields that depend on deferred endpoints, those fields must either be explicitly deferred, nullable, or separately approved as a scope expansion.

Source Binding Belongs To App-Layer, Not The Brink Adapter

Kds.Ai.Integrations.BrinkPos should remain a low-level Brink adapter responsible only for Brink transport, SOAP contracts, raw capture, and payload parsing. Tenant resolution, source-binding lookup, and mapping from operational SourceConnection records to BrinkConnectionDefinition belong to Warehouse or another app-layer orchestration module above the adapter. The Brink adapter should consume an externally provided BrinkConnectionDefinition and must not read operational database state directly.

The current temporary home for Brink binding resolution is backend/Kds.Ai.Integrations as an app-layer compromise. This is not the intended long-term ownership boundary. The planned destination is Warehouse orchestration or a dedicated SourceBindings module once the shared binding model for multiple source systems is in place.

Until DB-backed SourceConnections are fully adopted everywhere, BrinkPosConnectionOptions may be used only as a legacy fallback path for local development, live adapter tests, and transitional bootstrap. Binding priority must remain: database first, legacy fallback second, exception last. The fallback path should stay isolated in the app-layer binding service and be easy to remove later.