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)

Branches (328)

Branch-specific documentation across extract-raw-xml, feature-fleet-import, kds_cdc_improvments, life-cycle, and tenat-admin.

branches/extract-raw-xml/etl/docs/features/ETL/epics/06-source-adapter-brink-pos-api/source-task.md 17,791 bytes · Updated Oct 23, 2026 10:44 AM EDT

Raw File

Source Adapter: Brink POS API

../../../../ETL.md - ETL architecture, metadata minimum, and S3 layout
../../../../flows/etl-ingestion.md - ingestion flow and batch lifecycle
technical-integration-notes.md - transport, auth, retry, and checkpoint rules
poc-checklist.md - implementation checklist

Goal

Deliver a minimum viable Brink POS API pull adapter for one tenant-location that:

pulls Brink SOAP data with conservative polling
stores raw SOAP responses in S3
loads source-shaped staging tables into the tenant warehouse
populates the first curated tables already present in the warehouse bootstrap
reuses the existing ETL batch and checkpoint model from docs/ETL.md

Scope

In scope for this epic:

read-only Brink integration over Sales2.svc, Settings2.svc, Settings.svc, and Kitchen.svc
one tenant-location PoC
raw response capture in S3
Brink-specific staging tables needed for the current curated bootstrap
staging -> curated SQL for the first useful curated outputs
reuse of existing operational metadata instead of a new control plane

Out of scope for this epic:

write operations through Brink APIs
full Brink settings coverage
labor, loyalty, inventory, and hourly sales integrations
future-date order flows and deposit-specific publication
production-grade replay or pull-attempt control plane

Document Boundary

This document defines the PoC data contract for the Brink adapter:

what curated tables exist today
which Brink operations we need
how raw payloads should be stored
which staging tables should exist
which staging tables feed which curated tables
what minimum metadata must be persisted

Detailed transport rules, token handling, retry limits, and checkpoint behavior stay in technical-integration-notes.md.

Current Curated Tables

Current tenant curated bootstrap in backend/Kds.Ai.Warehouse/Sql/Bootstrap/Tenant/Curated/060_create_curated_canonical_tables.sql contains these tables:

Curated table	Current purpose	Brink PoC status
`curated.kitchen_item_status_events`	kitchen item lifecycle events	target
`curated.kitchen_order_items`	kitchen-facing order items	target
`curated.kitchen_orders`	kitchen-facing order headers	target
`curated.pos_transaction_items`	POS item transaction facts	target
`curated.pos_transactions`	POS transaction headers	target
`curated.pos_hourly_sales`	hourly sales aggregates	out of scope
`curated.pos_transaction_tenders`	POS payment/tender facts	target
`curated.pos_tables`	POS table lookup	target
`curated.pos_table_definitions`	POS table hierarchy metadata	out of scope
`curated.pos_tenders`	tender lookup	target
`curated.customers`	customer master	out of scope for PoC
`curated.loyalty_customers`	loyalty customer master	out of scope
`curated.pos_catalog_items`	catalog item lookup	target
`curated.pos_modifiers`	modifier group lookup	target
`curated.inventory_items`	inventory master	out of scope
`curated.pos_menu_items`	menu item lookup	optional
`curated.pos_order_items`	order line convenience table	optional
`curated.pos_orders`	order header convenience table	optional

Brink Endpoint Plan

Primary Brink service endpoints:

https://{{server}}.brinkpos.net/Sales2.svc
https://{{server}}.brinkpos.net/Settings2.svc
https://{{server}}.brinkpos.net/Settings.svc
https://{{server}}.brinkpos.net/Kitchen.svc

Planned PoC operations:

Service	Operation	What we get	Why we need it	Docs
`Sales2.svc`	`GetOrders`	order headers, order items, modifiers, taxes, payments, payment details, destination/employee/table references, business totals, timestamps	main source for POS and kitchen transactional curated tables	`https://brinkapiportal.parpos.com/sales2#GetOrders`
`Sales2.svc`	`GetCurrentBusinessDate`	current register business date	optional helper for aligning `BusinessDate` windows and daily pulls	`https://brinkapiportal.parpos.com/sales2#GetCurrentBusinessDate`
`Kitchen.svc`	`GetOrderBumpTimes`	order-level bump events with queue and send timing	feeds kitchen order status/timing	`https://brinkapiportal.parpos.com/kitchen#GetOrderBumpTimes`
`Kitchen.svc`	`GetOrderItemBumpTimes`	item-level bump/fire/send events with queue and order item id	feeds kitchen item status/timing	`https://brinkapiportal.parpos.com/kitchen#GetOrderItemBumpTimes`
`Settings2.svc`	`GetLastModifiedTime`	last settings and employee modification timestamps	lightweight settings freshness checkpoint	`https://brinkapiportal.parpos.com/settings2#GetLastModifiedTime`
`Settings2.svc`	`GetItems`	item master with price, tax, revenue center, modifier group links, kitchen name	feeds catalog and transactional enrichment	`https://brinkapiportal.parpos.com/settings2#GetItems`
`Settings2.svc`	`GetModifierGroups`	modifier group headers and group item membership	feeds `pos_modifiers` and modifier enrichment	`https://brinkapiportal.parpos.com/settings2#GetModifierGroups`
`Settings2.svc`	`GetDestinations`	destination lookup such as dine-in, takeout, delivery	enriches orders and transactions	`https://brinkapiportal.parpos.com/settings2#GetDestinations`
`Settings2.svc`	`GetTables`	table lookup for table-service locations	feeds `pos_tables`	`https://brinkapiportal.parpos.com/settings2#GetTables`
`Settings2.svc`	`GetKitchenQueues`	kitchen queue lookup	enriches bump events and kitchen projections	`https://brinkapiportal.parpos.com/settings2#GetKitchenQueues`
`Settings2.svc`	`GetTaxes`	tax lookup and tax metadata	enriches item and order tax mappings	`https://brinkapiportal.parpos.com/settings2#GetTaxes`
`Settings2.svc`	`GetEmployees`	employee lookup	enriches orders, transactions, and tenders	`https://brinkapiportal.parpos.com/settings2#GetEmployees`
`Settings.svc`	`GetTenders`	tender lookup and tender configuration	feeds `pos_tenders` and payment enrichment	`https://brinkapiportal.parpos.com/settings#GetTenders`

Implementation note:

Settings2.GetSettings may later consolidate some lookup pulls, but the PoC should prefer direct operations above because they map more clearly to raw files, staging tables, and checkpoints.

Proposed Raw S3 Files

Raw storage should stay 1:1 with the SOAP response body returned by each Brink call.

Recommended path shape under the ETL raw prefix:

text

raw/{tenant_id}/brink_pos/{yyyy}/{MM}/{dd}/{batch_id}/{stream}/
  sales2_get_orders_{window_start}_{window_end}.xml
  kitchen_get_order_bump_times_{window_start}_{window_end}.xml
  kitchen_get_order_item_bump_times_{window_start}_{window_end}.xml
  settings2_get_last_modified_time.xml
  settings2_get_items.xml
  settings2_get_modifier_groups.xml
  settings2_get_destinations.xml
  settings2_get_tables.xml
  settings2_get_kitchen_queues.xml
  settings2_get_taxes.xml
  settings2_get_employees.xml
  settings_get_tenders.xml

Rules:

one raw object per Brink call execution
store the SOAP body exactly as returned by Brink
do not mutate or normalize the raw XML before saving it
keep request window values in the file name or adjacent object metadata
never store AccessToken or LocationToken in file content, metadata, or logs

Proposed Staging Tables

Staging must be 1:1 with the raw files saved in S3 because the worker will COPY those load-ready files directly into Redshift. That means staging should stay wide and landing-oriented, with as little fragmentation as possible. Splitting nested Brink collections into many tables should happen only in curated transforms, not in the landing layer.

Important semantic rule for correct warehouse design:

Sales2.GetOrders, Kitchen.GetOrderBumpTimes, and Kitchen.GetOrderItemBumpTimes are event/fact-style feeds
Brink settings calls should be treated as snapshot-style reference feeds, not as a native history feed
GetLastModifiedTime only tells us that configuration changed; it does not provide a change log
lookup responses such as items, modifier groups, destinations, tables, taxes, tenders, employees, and kitchen queues represent the current known configuration at pull time
therefore staging for settings should store per-batch snapshots 1:1 with the source payloads
curated settings-backed tables should default to current-state reference tables unless we explicitly add historical/SCD behavior later
if we need history of configuration changes in the future, that history must be built by KDSAI from successive snapshots, not assumed to exist in Brink

Recommended PoC staging tables:

Staging table	Source operation	Raw file / response shape	PoC status
`staging.brink_sales2_get_orders`	`Sales2.GetOrders`	one row per raw `GetOrders` response payload, preserving the `Orders` collection as source-shaped content	required
`staging.brink_kitchen_get_order_bump_times`	`Kitchen.GetOrderBumpTimes`	one row per raw `GetOrderBumpTimes` response payload, preserving the `OrderBumpTimes` collection	required
`staging.brink_kitchen_get_order_item_bump_times`	`Kitchen.GetOrderItemBumpTimes`	one row per raw `GetOrderItemBumpTimes` response payload, preserving the `OrderItemBumpTimes` collection	required
`staging.brink_settings2_get_last_modified_time`	`Settings2.GetLastModifiedTime`	one row per raw freshness response payload	required
`staging.brink_settings2_get_items`	`Settings2.GetItems`	one row per raw `GetItems` response payload, preserving the `Item` collection	required
`staging.brink_settings2_get_modifier_groups`	`Settings2.GetModifierGroups`	one row per raw `GetModifierGroups` response payload, preserving the `ModifierGroups` collection	required
`staging.brink_settings2_get_destinations`	`Settings2.GetDestinations`	one row per raw `GetDestinations` response payload, preserving the `Destination` collection	required
`staging.brink_settings2_get_tables`	`Settings2.GetTables`	one row per raw `GetTables` response payload, preserving the `Table` collection	required
`staging.brink_settings2_get_kitchen_queues`	`Settings2.GetKitchenQueues`	one row per raw `GetKitchenQueues` response payload, preserving the `KitchenQueue` collection	required
`staging.brink_settings2_get_taxes`	`Settings2.GetTaxes`	one row per raw `GetTaxes` response payload, preserving the `Tax` collection	required
`staging.brink_settings2_get_employees`	`Settings2.GetEmployees`	one row per raw `GetEmployees` response payload, preserving the `Employee` collection	required
`staging.brink_settings_get_tenders`	`Settings.GetTenders`	one row per raw `GetTenders` response payload, preserving the `Tender` collection	required

Landing rules:

one staging table per Brink operation / raw file type
one load-ready file maps to one staging table
nested collections remain embedded in the landed payload shape
no separate staging tables for order entries, modifiers, taxes, payments, or payment details in the PoC
decomposition of nested collections happens in staging -> curated SQL only
event streams and settings snapshots should not be modeled the same way in curated

Every staging table should carry at least:

_etl_batch_id
_etl_source_type
_etl_ingested_at
_etl_raw_s3_path

Staging To Curated Matrix

Curated table	Input staging tables	Brink mapping note	PoC status
`curated.kitchen_item_status_events`	`staging.brink_kitchen_get_order_item_bump_times`, `staging.brink_sales2_get_orders`, `staging.brink_settings2_get_kitchen_queues`	explode item bump payloads and join back to order/item structures from `GetOrders` inside the curated transform	target
`curated.kitchen_order_items`	`staging.brink_sales2_get_orders`, `staging.brink_kitchen_get_order_item_bump_times`, `staging.brink_settings2_get_items`	explode order entries from `GetOrders`, then enrich with bump timing and item lookup	target
`curated.kitchen_orders`	`staging.brink_sales2_get_orders`, `staging.brink_kitchen_get_order_bump_times`, `staging.brink_settings2_get_destinations`, `staging.brink_settings2_get_kitchen_queues`	explode order headers from `GetOrders`, then add queue and bump timing	target
`curated.pos_transaction_items`	`staging.brink_sales2_get_orders`, `staging.brink_settings2_get_items`	derive item facts by exploding entries, modifiers, and taxes from the landed `GetOrders` payload	target
`curated.pos_transactions`	`staging.brink_sales2_get_orders`, `staging.brink_settings2_get_destinations`, `staging.brink_settings2_get_employees`	derive one transaction row per Brink order from the landed `GetOrders` payload	target
`curated.pos_hourly_sales`	none	Brink PoC does not expose a direct hourly aggregate feed	out of scope
`curated.pos_transaction_tenders`	`staging.brink_sales2_get_orders`, `staging.brink_settings_get_tenders`, `staging.brink_settings2_get_employees`	derive payment and payment-detail facts by exploding nested payments from the landed `GetOrders` payload	target
`curated.pos_tables`	`staging.brink_settings2_get_tables`	direct lookup projection from landed settings payload	target
`curated.pos_table_definitions`	none	no Brink source selected in PoC	out of scope
`curated.pos_tenders`	`staging.brink_settings_get_tenders`	direct lookup projection from landed settings payload	target
`curated.customers`	none	`GetOrders` exposes nullable `CustomerId`, but not a full customer master contract	out of scope for PoC
`curated.loyalty_customers`	none	no Brink loyalty feed in PoC	out of scope
`curated.pos_catalog_items`	`staging.brink_settings2_get_items`, `staging.brink_settings2_get_taxes`	derive item lookup rows from landed item payload, enriched with tax metadata	target
`curated.pos_modifiers`	`staging.brink_settings2_get_modifier_groups`, `staging.brink_settings2_get_items`	explode modifier group membership inside curated transform	target
`curated.inventory_items`	none	no Brink inventory feed in PoC	out of scope
`curated.pos_menu_items`	`staging.brink_settings2_get_items`, `staging.brink_settings2_get_destinations`, `staging.brink_settings2_get_kitchen_queues`	optional convenience projection from landed settings payloads	optional
`curated.pos_order_items`	`staging.brink_sales2_get_orders`, `staging.brink_settings2_get_items`	optional convenience projection by exploding order entries from the landed `GetOrders` payload	optional
`curated.pos_orders`	`staging.brink_sales2_get_orders`, `staging.brink_settings2_get_destinations`, `staging.brink_settings_get_tenders`	optional convenience projection by exploding order headers and nested payments from the landed `GetOrders` payload	optional

Minimum Metadata Required By `docs/ETL.md`

The Brink adapter must satisfy the ETL baseline without adding new metadata tables in the PoC.

Operational metadata to reuse

TenantWarehouseMapping
IngestionBatch
IngestionCheckpoint

Minimum batch fields

For each Brink sync stream, IngestionBatch must preserve at least the fields explicitly required by docs/ETL.md:

batch_id
tenant_id
tenant_database
ingestion_mode = api_pull
source_type = brink_pos
received_at
load_started_at
load_completed_at
status
row counts and error details
raw S3 path
load S3 path

Recommended Brink-specific additions on the batch record or adjacent structured logs:

stream_name
location_key
service_name
operation_name
request_window_start
request_window_end
result_code

Minimum checkpoint keys

Use one checkpoint per location and stream:

location:{location_key}:orders:modified-time
location:{location_key}:order-bumps:bump-time
location:{location_key}:item-bumps:bump-time
location:{location_key}:settings:last-modified

Minimum raw and staging traceability

To satisfy replay and debugging requirements from docs/ETL.md, each successful Brink load must let us answer:

which raw SOAP file was pulled
which batch loaded it
which staging rows came from that file
which curated publication was produced from that batch

Minimum fields needed across raw storage, staging, and logs:

tenant_id
source_type
batch_id
stream_name
raw_response_s3_path
load_s3_path
request_window_start
request_window_end
checkpoint_before
checkpoint_after
ingested_at

Minimum connection metadata

Service-owned connection metadata for the PoC must include:

tenant identifier
Brink server or endpoint stack
Brink location identifier used by the adapter
encrypted AccessToken
encrypted LocationToken
connection status such as active or disabled

Minimum Acceptance Bar

This epic is complete enough for the PoC when all of the following are true:

one tenant can be resolved to one Brink endpoint stack and location
the adapter stores raw SOAP responses in S3 for each Brink call
required staging tables are populated from the selected Brink operations
target curated tables can be rebuilt from staging without reading raw XML again
one IngestionBatch exists per Brink sync stream run
checkpoints advance only after end-to-end success
failed runs keep raw payloads and do not advance checkpoints
logs and metadata are sufficient to replay or debug a failed time window