# 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. - 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](./TASK0.md) - Freeze the v1 decision record for `orders` and `order_items` - [ ] Freeze the narrower v1 definition of `orders + order_items + related contracts` against the broader Brink POS groundwork package. - [ ] Decide and document the first curated target names and minimum field set for `orders` and `order_items`. - [ ] 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. - [ ] 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 - [ ] [TASK 1](./TASK1.md) - Implement source binding and dispatch for tenant-day ETL start - [ ] Define the job input contract for scheduled and manual execution with `company_id`, `tenant_id`, optional `businessDate`, trigger, and reset semantics. - [ ] Define or implement the dispatch path so cron and manual runs converge into the same tenant extract contract. - [ ] Resolve Brink binding, secret reference, and runtime config inside the Brink resolver boundary rather than in the scheduler. - [ ] Define the tenant-day logical key and current/superseded semantics for the full pipeline. - [ ] Add unit tests for dispatch contracts, tenant-day key logic, and scheduler/manual convergence. ### Module 2: Brink Extraction Boundary And Raw Capture - [ ] [TASK 2](./TASK2.md) - Implement live raw extract and S3 raw persistence for `GetCurrentBusinessDate` and `GetOrders` - [ ] [TASK 2.1](./TASK2.1.md) - Implement child extract orchestration and business-date resolution - [ ] [TASK 2.2](./TASK2.2.md) - Implement Brink raw capture boundary for `GetOrders` - [ ] [TASK 2.3](./TASK2.3.md) - Implement S3 raw persistence and persisted-raw validation - [ ] [TASK 2.4](./TASK2.4.md) - Add unit tests and live E2E validation for `extract_raw` - [ ] Freeze the Brink boundary so typed control uses `IBrinkPosFacade` and raw extraction uses `IBrinkRawClient`, both consuming externally provided Brink connection context. - [ ] Implement streaming `Sales2.GetOrders` extraction and optional `GetCurrentBusinessDate` resolution. - [ ] Persist original SOAP XML to S3 `raw/` without mutation. - [ ] Validate persisted raw XML for well-formedness, SOAP fault state, and Brink `ResultCode` success. - [ ] Record endpoint diagnostics and raw artifact references in batch details and failed-package metadata. - [ ] Keep tenant binding lookup, batch semantics, and S3 ownership in Warehouse/app-layer rather than in `Kds.Ai.Integrations.BrinkPos`. - [ ] Add unit tests for business-date resolution, raw validation, and extract orchestration failure handling. - [ ] 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 - [ ] [TASK 3](./TASK3.md) - Finalize stage lifecycle, checkpoints, and failed-package behavior - [ ] Extend the ingestion batch model for stage-specific tenant-day batches and upstream-batch references. - [ ] Implement per-stage `is_current` and supersede semantics for reruns. - [ ] Implement checkpoints that advance only after full stage success. - [ ] Define the minimal `failed/` package shape with `error.json`, diagnostics, and raw artifact references. - [ ] Ensure end-to-end traceability from request to batch, raw object, load artifact, staging load, curated batch, and curated export. - [ ] Add unit tests for supersede semantics, current-batch resolution, checkpoint advancement rules, and failure packaging. ### Module 4: Raw To Load Normalization - [ ] [TASK 4](./TASK4.md) - Implement `GetOrders` raw-to-load normalization to Parquet contracts - [ ] Freeze the normalization catalog for `Sales2.GetOrders`. - [ ] Implement full relational decomposition of the `GetOrders` payload. - [ ] Emit `staging.brink_sales2_orders`. - [ ] Emit `staging.brink_sales2_order_entries`. - [ ] Emit `staging.brink_sales2_order_entry_taxes`. - [ ] Emit `staging.brink_sales2_order_entry_modifiers`. - [ ] Emit `staging.brink_sales2_order_payments`. - [ ] Emit `staging.brink_sales2_order_payment_details`. - [ ] Emit `staging.brink_sales2_order_taxes`. - [ ] Emit `staging.brink_sales2_order_discounts`. - [ ] Emit `staging.brink_sales2_order_promotions`. - [ ] Emit `staging.brink_sales2_order_surcharges`. - [ ] Emit `staging.brink_sales2_order_deposits`. - [ ] Add the ETL metadata envelope and deterministic parent-child keys to every emitted contract. - [ ] Write Parquet load artifacts to S3 `load/` and record schema version, row count, and URI diagnostics. - [ ] Add unit tests for `GetOrders` decomposition, emitted contracts, metadata envelope, and parent-child key consistency. - [ ] Add a live E2E test that starts from a real raw artifact and verifies written Parquet `load/` output. ### Module 5: Load To Redshift Staging - [ ] [TASK 5](./TASK5.md) - Implement Redshift staging load from Parquet contracts - [ ] Define the deterministic contract-to-table catalog for every emitted `orders` and `order_items` load contract. - [ ] Implement tenant-resolved Redshift `COPY` execution for Parquet load artifacts. - [ ] Decide the v1 rerun replacement strategy at the staging boundary. - [ ] Record per-table load diagnostics, S3 inputs, and SQL template identifiers in `load_to_staging` batch details. - [ ] Validate that every required staging table is loaded before the stage is marked current. - [ ] Add unit tests for contract-to-table mapping, `COPY` command construction, and staging rerun semantics. - [ ] Add a live E2E test that verifies Redshift staging load from produced Parquet artifacts. ### Module 6: Staging To Curated - [ ] [TASK 6](./TASK6.md) - Implement `staging -> curated` for `curated.pos_orders` and `curated.pos_order_items` - [ ] Freeze the first `orders` and `order_items` curated targets and their dependency matrix. - [ ] Implement the SQL template catalog and execution path for `staging_to_curated`. - [ ] Build the first `orders` curated transform. - [ ] Build the first `order_items` curated transform. - [ ] Decide how payment-related fields behave in v1 while all non-`GetOrders` enrichment remains out of scope. - [ ] Apply tenant-day rerun semantics so only the newest successful curated batch is current. - [ ] Add unit tests for dependency validation, SQL selection, field mapping rules, and rerun/current-state behavior. - [ ] Add a live E2E test that verifies curated rows in Redshift after staging-to-curated execution. ### Module 7: Curated Export To S3 - [ ] [TASK 7](./TASK7.md) - Implement curated Parquet export to S3 - [ ] 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 - [ ] [TASK 8](./TASK8.md) - Add operational visibility, replay hooks, and full smoke validation - [ ] 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 ```text 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 | Session | Modules | Description | |---|---|---| | 1 | 0, 1 | Freeze the narrowed slice and the tenant-day job contract | | 2 | 2, 3 | Implement extract boundary, raw capture, and stage lifecycle semantics | | 3 | 4 | Deliver relational `GetOrders` normalization and `load/` artifacts | | 4 | 5 | Deliver Redshift `COPY` into orders/order-items staging tables | | 5 | 6 | Freeze curated targets and implement `orders` and `order_items` transforms | | 6 | 7, 8 | Export curated Parquet, add smoke validation, and harden operations | --- ## 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. ### 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.