# Brink POS Integration Flow

## Purpose

Describe the execution flow of the Brink POS ETL adapter on top of the general ETL lifecycle from `../../../../ETL.md`.

This document answers:

- what happens on each scheduled run
- in what order Brink streams should run
- what is written to S3, `staging`, and `curated`
- when checkpoints and batch metadata are updated

## High-Level Flow

Recommended flow for Brink:

1. Schedule starts a Brink sync job for one tenant-location and one stream.
2. Worker resolves Brink connection metadata and target tenant warehouse.
3. Worker reads the current checkpoint for the stream.
4. Worker uses the correct Brink abstraction for the requested stream window.
5. Worker saves the raw SOAP response to S3 `raw/`.
6. Worker reopens or reads the persisted raw object and validates it for SOAP fault, Brink `ResultCode`, and XML validity.
7. Worker converts the validated raw SOAP response into a load-ready file for one `staging` table.
8. Worker writes that load-ready file to S3 `load/`.
9. Worker runs Redshift `COPY` from S3 `load/` into the 1:1 `staging` table.
10. Worker runs SQL transforms from `staging` to target `curated` tables.
11. Worker runs Redshift `UNLOAD` from updated `curated` tables to S3 `curated/` as Parquet.
12. Worker marks the batch successful.
13. Worker advances the checkpoint only after the full flow succeeds.

Important correction:

- the raw object saved from Brink is SOAP XML, not JSON
- JSON or Parquet may be used as the load-ready format for `COPY`, but that is a derived file in `load/`, not the original raw payload

Important abstraction rule:

- use `IBrinkPosFacade` for typed control calls and smaller typed operations
- use `IBrinkRawClient` dedicated semantic methods or semantic request DTOs for heavy raw endpoints that need bounded-memory streaming capture
- keep SOAP actions, envelope XML, and transport request objects internal to the Brink integration module
- treat `BrinkRawCallResult` as transport metadata only; endpoint success requires persisted raw validation

## Runtime Unit

The runtime unit should be:

- one tenant
- one Brink location
- one stream
- one batch

Do not run one giant batch that mixes orders, bumps, and settings together.

Recommended streams:

- `settings`
- `orders`
- `order-bumps`
- `item-bumps`

## Recommended Stream Order

When multiple streams are due in the same scheduler cycle, run them in this order:

1. `settings`
2. `orders`
3. `order-bumps`
4. `item-bumps`

Why this order:

- `settings` refreshes dimensions used later for enrichment
- `orders` is the main fact source and should land before kitchen bump-derived projections
- `order-bumps` references orders
- `item-bumps` references order items and is the most dependent stream

If `settings` is not due or `GetLastModifiedTime` shows no change, skip the settings payload pulls and continue with the transactional streams.

## Detailed Per-Stream Flow

### 1. Settings Stream

Business meaning:

- this stream refreshes the current reference configuration of the location
- it is not a native Brink history stream
- each successful pull is a snapshot of "what the location configuration looks like now"
- this is why settings landing tables should be treated as per-batch snapshots, while curated settings tables should usually expose current state

Recommended sequence:

1. Start `settings` batch.
2. Call `IBrinkPosFacade.GetLastModifiedTimeAsync`.
3. Save the raw SOAP XML to S3 `raw/`.
4. Load the response 1:1 into `staging.brink_settings2_get_last_modified_time`.
5. Compare the returned watermark with the current `settings:last-modified` checkpoint.
6. If unchanged, mark the stream as successful and do not pull the remaining settings calls.
7. If changed, call and land these operations through the typed facade:
8. `Settings2.GetItems`
9. `Settings2.GetModifierGroups`
10. `Settings2.GetDestinations`
11. `Settings2.GetTables`
12. `Settings2.GetKitchenQueues`
13. `Settings2.GetTaxes`
14. `Settings2.GetEmployees`
15. `Settings.GetTenders`
16. Save each raw SOAP response to S3 `raw/`.
17. Reopen each saved raw object and validate it before treating the endpoint as successful.
18. Convert each response into one load-ready file under S3 `load/`.
19. `COPY` each file into its matching 1:1 `staging` table.
20. Run `staging -> curated` SQL for settings-backed curated tables.
21. `UNLOAD` updated curated tables to S3 `curated/`.
22. Mark batch successful.
23. Advance the `settings:last-modified` checkpoint.

### 2. Orders Stream

Recommended sequence:

1. Start `orders` batch.
2. Resolve the current `orders:modified-time` checkpoint.
3. Resolve the `BusinessDate` to query.
4. Call `IBrinkRawClient` through a semantic orders capture API.
5. The raw orders capture uses `GetOrdersRequest(businessDate, ModifiedTime: null, ExcludeOpenOrders: false, PriceRollUp: "RollUp")`.
6. The public raw API may be either:
7. a dedicated method for the current slice, or
8. a semantic request DTO if the orders stream later needs additional business inputs such as `ModifiedTime`
9. Save the raw SOAP XML to S3 `raw/`.
10. Reopen the saved raw object and validate it for SOAP fault and Brink `ResultCode` before marking the endpoint successful.
11. Convert the response to one load-ready file.
12. Write the load-ready file to S3 `load/`.
13. `COPY` into `staging.brink_sales2_get_orders`.
14. Run `staging -> curated` SQL for transaction and order-based curated tables.
15. `UNLOAD` updated curated tables to S3 `curated/`.
16. Mark batch successful.
17. Advance the `orders:modified-time` checkpoint to the highest safely processed Brink `ModifiedTime`.

### 3. Order-Bumps Stream

Recommended sequence:

1. Start `order-bumps` batch.
2. Resolve the current `order-bumps:bump-time` checkpoint.
3. Resolve the `BusinessDate` to query.
4. Call `IBrinkRawClient` through a semantic order-bumps capture API.
5. Save the raw SOAP XML to S3 `raw/`.
6. Reopen the saved raw object and validate it for SOAP fault and Brink `ResultCode` before marking the endpoint successful.
7. Convert the response to one load-ready file.
8. Write the load-ready file to S3 `load/`.
9. `COPY` into `staging.brink_kitchen_get_order_bump_times`.
10. Run the `staging -> curated` SQL dependent on order-level bump data.
11. `UNLOAD` updated curated tables to S3 `curated/`.
12. Mark batch successful.
13. Advance the `order-bumps:bump-time` checkpoint.

### 4. Item-Bumps Stream

Recommended sequence:

1. Start `item-bumps` batch.
2. Resolve the current `item-bumps:bump-time` checkpoint.
3. Resolve the `BusinessDate` to query.
4. Call `IBrinkRawClient` through a semantic item-bumps capture API.
5. Save the raw SOAP XML to S3 `raw/`.
6. Reopen the saved raw object and validate it for SOAP fault and Brink `ResultCode` before marking the endpoint successful.
7. Convert the response to one load-ready file.
8. Write the load-ready file to S3 `load/`.
9. `COPY` into `staging.brink_kitchen_get_order_item_bump_times`.
10. Run the `staging -> curated` SQL dependent on item-level bump data.
11. `UNLOAD` updated curated tables to S3 `curated/`.
12. Mark batch successful.
13. Advance the `item-bumps:bump-time` checkpoint.

## S3 And Redshift Flow

For Brink, the path should be:

1. raw SOAP XML to S3 `raw/`
2. parsed load-ready file to S3 `load/`
3. `COPY` into one matching `staging` table
4. SQL transform into `curated`
5. `UNLOAD` `curated` to S3 `curated/`

Recommended file role by prefix:

- `raw/` = original Brink SOAP XML exactly as returned
- `load/` = load-ready file derived from the raw response for one staging table
- `curated/` = published Parquet exported from Redshift curated tables
- `failed/` = error context and replay artifacts

## Checkpoint Rules

Checkpoint behavior for Brink must be strict:

1. Read checkpoint before calling Brink.
2. Use checkpoint to build the incremental request.
3. Save raw payload before any warehouse mutation.
4. Load `staging`.
5. Transform to `curated`.
6. `UNLOAD` `curated` to S3.
7. Mark batch successful.
8. Only then update the checkpoint.

Rules:

- failed batch means no checkpoint advance
- downstream SQL failure means no checkpoint advance
- `UNLOAD` failure means no checkpoint advance
- preserve Brink `DateTimeOffset` with original offset
- use small overlap windows on incremental streams to reduce missed records at boundaries

## Metadata Update Point

Your interpretation is correct with one important clarification: metadata should be updated at two different moments.

At batch start:

- create `IngestionBatch`
- store tenant, stream, source type, requested window, and initial status

During execution:

- write raw S3 path after raw response is stored
- write load S3 path after the load-ready file is created
- write row counts and timings after `COPY` and transform

At successful completion:

- mark batch success
- persist final row counts and completion timestamps
- update the stream checkpoint

At failed completion:

- mark batch failed
- store error details
- do not update the checkpoint

## Failure Flow

If a Brink call or downstream step fails:

1. keep the raw SOAP response if it was already received
2. write error details to batch metadata and logs
3. optionally write failure context under S3 `failed/`
4. leave checkpoint unchanged
5. let the next scheduler run retry from the same checkpoint window

## Recommended Scheduler Model

Recommended scheduler approach:

1. `EventBridge Scheduler` creates cron triggers
2. each trigger publishes a stream-specific job to `SQS`
3. ECS worker consumes `SQS` messages through `AWS.Messaging` `AddSQSPoller(...)`
4. each message executes one tenant-location-stream batch
5. handler returns success only after full batch success

Fallback PoC option:

1. one internal schedule loop in worker `BackgroundService`
2. worker resolves which tenant-location pairs are active
3. worker executes due streams directly

Recommended cadence for the PoC:

- `orders`: most frequent
- `order-bumps`: similar or slightly less frequent than `orders`
- `item-bumps`: similar or slightly less frequent than `orders`
- `settings`: much less frequent, gated by `GetLastModifiedTime`

Exact cron values should remain configurable per environment.

## Decision Summary

| Decision | Recommendation |
|---|---|
| Raw storage format | save original Brink SOAP XML |
| Load format for staging | derive one load-ready file per stream/table |
| Staging granularity | one table per Brink operation / raw file type |
| Curated granularity | explode nested collections during SQL transforms |
| Batch granularity | one batch per tenant-location-stream |
| Checkpoint update | only after `COPY`, transform, and `UNLOAD` all succeed |
| Stream order | `settings -> orders -> order-bumps -> item-bumps` |

## Related Documents

- `source-task.md`
- `technical-integration-notes.md`
- `poc-checklist.md`
- `../../../../ETL.md`
- `../../../../flows/etl-ingestion.md`