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/life-cycle/etl/docs/features/ETL/epics/06-source-adapter-brink-pos-api/integration-flow.md 10,945 bytes · Updated Oct 23, 2026 10:44 AM EDT
Raw File

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

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 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

DecisionRecommendation
Raw storage formatsave original Brink SOAP XML
Load format for stagingderive one load-ready file per stream/table
Staging granularityone table per Brink operation / raw file type
Curated granularityexplode nested collections during SQL transforms
Batch granularityone batch per tenant-location-stream
Checkpoint updateonly after COPY, transform, and UNLOAD all succeed
Stream ordersettings -> orders -> order-bumps -> item-bumps
  • source-task.md
  • technical-integration-notes.md
  • poc-checklist.md
  • ../../../../ETL.md
  • ../../../../flows/etl-ingestion.md