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)

Warehouse Model (93)

Analytical data warehouse schemas, curated tables, and Redshift/Athena data models.

warehouse-model/docs/features/ETL/warehouse-data/brinkPos-curated-model-mapps/pos_orders.md 7,803 bytes · Updated Oct 23, 2026 10:44 AM EDT

Raw File

Brink mapping for `curated.pos_orders`

Main staging sources used today

staging.brink_sales2_orders
staging.brink_settings2_get_destinations

Additional staged sources available for future enrichment

staging.brink_settings_get_tenders
staging.brink_sales2_order_payments
staging.brink_settings2_get_tables

Mapping groups

Business property mappings describe how Brink order content is mapped or could be mapped into curated business fields.
Technical and ETL property mappings describe lineage, source reconciliation, tenant scoping, and ingestion metadata.

Business property mappings

Curated Column	Brink Staging Table	Brink Property Path	Current mapping	Status	Notes
`order_id`	`staging.brink_sales2_orders`	`id_encoded` fallback `order_id`	`COALESCE(NULLIF(id_encoded, ''), CAST(order_id AS VARCHAR(50)))`	`Mapped`	Actual current insert logic. Best string-style external order identifier.
`order_type`	`staging.brink_settings2_get_destinations`	`name`	`CAST(dest.name AS VARCHAR(50))`	`Mapped`	Resolved by joining `brink_sales2_orders` on `destination_id` to `brink_settings2_get_destinations`.
`payment_method`	`staging.brink_sales2_order_payments` + `staging.brink_settings_get_tenders`	`tender_id` -> `name`	`NULL`	`Not mapped`	Requires payment summary logic plus tender lookup.
`subtotal`	`staging.brink_sales2_orders`	`subtotal`	`CAST(subtotal AS DECIMAL(10, 2))`	`Mapped`	Direct order subtotal.
`total`	`staging.brink_sales2_orders`	`total`	`CAST(total AS DECIMAL(10, 2))`	`Mapped`	Direct final total.
`created_at`	`staging.brink_sales2_orders`	`opened_time`	`CAST(opened_time AS TIMESTAMP)`	`Mapped`	Direct order-open timestamp.
`customer_number`	`staging.brink_sales2_orders`	`customer_id`	`customer_id`	`Mapped`	Brink customer identifier.
`id`	`staging.brink_sales2_orders`	`order_id_no_location`	`COALESCE(order_id_no_location, 0)`	`Mapped`	Compact integer-style order identifier with current fallback to `0`.
`table_number`	`N/A`	`N/A`	`NULL`	`Not mapped`	Current staged `Settings2.GetTables` dictionary is not joinable from the existing curated insert.
`tax`	`staging.brink_sales2_orders`	`tax`	`CAST(tax AS DECIMAL(10, 2))`	`Mapped`	Direct total tax.
`status`	`staging.brink_sales2_orders`	`(is_closed, is_future_order, is_refund)`	`CASE WHEN is_refund THEN 'refund' WHEN is_future_order THEN 'future' WHEN is_closed THEN 'closed' ELSE 'open' END`	`Mapped`	Derived readable status.
`terminal_id`	`staging.brink_sales2_orders`	`terminal_id`	`CAST(terminal_id AS VARCHAR(50))`	`Mapped`	Direct terminal ID.
`points_used`	`N/A`	`N/A`	`NULL`	`Not mapped`	No current loyalty-redemption field is used in this flow.
`additional_payment`	`staging.brink_sales2_order_payments`	`tender_id`	`NULL`	`Not mapped`	Could be derived later if business defines how to summarize split or secondary payments.
`pos_terminal_id`	`staging.brink_sales2_orders`	`terminal_id`	`CAST(terminal_id AS VARCHAR(50))`	`Mapped`	Same current value as `terminal_id`.
`destination`	`staging.brink_settings2_get_destinations`	`name`	`CAST(dest.name AS VARCHAR(100))`	`Mapped`	Resolved by joining `brink_sales2_orders` on `destination_id` to `brink_settings2_get_destinations`.
`terminal_name`	`staging.brink_settings_get_registers`	`name`	`NULL`	`Not mapped`	Resolve terminal name by joining `staging.brink_sales2_orders` on `terminal_id` to `staging.brink_settings_get_registers` on `register_id`.
`store_id`	`N/A`	`N/A`	`NULL`	`Not mapped`	Likely future source is tenant or location metadata outside `Sales2.GetOrders`.
`store_name`	`N/A`	`N/A`	`NULL`	`Not mapped`	Likely future source is tenant or location metadata outside Brink order payload.
`status_name`	`staging.brink_sales2_orders`	`(is_closed, is_future_order, is_refund)`	`CASE WHEN is_refund THEN 'refund' WHEN is_future_order THEN 'future' WHEN is_closed THEN 'closed' ELSE 'open' END`	`Mapped`	Currently identical to `status`.
`service_channel`	`staging.brink_settings2_get_destinations`	`(is_delivery, name)`	`CASE WHEN COALESCE(dest.is_delivery, FALSE) THEN 'Delivery' WHEN COALESCE(dest.name, '') ILIKE '%qr%' OR COALESCE(dest.name, '') ILIKE '%mobile%' THEN 'Mobile/QR' ELSE 'Counter' END`	`Mapped`	Derived from destination metadata: delivery flag and name-based QR/mobile detection.
`fulfillment_channel`	`staging.brink_settings2_get_destinations`	`is_delivery`	`CASE WHEN COALESCE(dest.is_delivery, FALSE) THEN 'Delivery' ELSE 'On-Premise' END`	`Mapped`	Derived from destination delivery flag.
`created_at_utc`	`staging.brink_sales2_orders`	`opened_time`	`CAST(opened_time AS TIMESTAMP)`	`Mapped`	Direct UTC timestamp from current staging.
`business_date`	`staging.brink_sales2_orders`	`business_date`	`CAST(business_date AS DATE)`	`Mapped`	Direct business date.
`currency_code`	`N/A`	`N/A`	`NULL`	`Not mapped`	No canonical currency source is used by the current Brink curated flow.
`order_status_code`	`staging.brink_sales2_orders`	`(is_closed, is_future_order, is_refund)`	`CASE WHEN is_refund THEN 'refund' WHEN is_future_order THEN 'future' WHEN is_closed THEN 'closed' ELSE 'open' END`	`Mapped`	Derived machine-friendly status code, currently same as `status` and `status_name`.

Technical and ETL property mappings

Curated Column	Brink Staging Table	Brink Property Path	Current mapping	Status	Notes
`order_key`	`N/A`	`N/A`	`NULL`	`Not mapped`	Warehouse-generated key, not assigned by current insert.
`source_system`	`N/A`	`N/A`	constant `'brink_pos'`	`Derived constant`	Warehouse constant.
`source_order_id`	`staging.brink_sales2_orders`	`order_id`	`CAST(order_id AS VARCHAR(199))`	`Mapped`	Direct Brink source ID kept for reconciliation.
`tenant_key`	`N/A`	`N/A`	`NULL`	`Not mapped`	Warehouse-generated tenant key, not assigned by current insert.
`tenant_name`	`N/A`	`N/A`	parameter `@tenant_id`	`ETL context`	Populated from ETL context, not from Brink payload.
`_etl_batch_id`	`staging.brink_sales2_orders`	`_etl_batch_id`	`_etl_batch_id`	`Mapped`	ETL metadata propagated from staging.
`_etl_source_type`	`staging.brink_sales2_orders`	`_etl_source_type`	`_etl_source_type`	`Mapped`	ETL metadata propagated from staging.
`_etl_ingested_at`	`staging.brink_sales2_orders`	`_etl_ingested_at_utc`	`_etl_ingested_at_utc AS _etl_ingested_at`	`Mapped`	ETL metadata propagated from staging.

Portal and sample validation

Curated field	Additional validated source	How to use it
`payment_method`	`Sales2.GetOrders -> Orders[].Payments[].AdditionalData[Name='PaymentSource']`	Sample payloads confirm Brink emits a `PaymentSource` name-value pair that can supplement tender-name-based payment summaries.
`terminal_name`	`Settings.GetRegisters -> Register.Name`	Brink portal WSDL confirms a register lookup endpoint. The `TerminalId -> Register.Id` join should still be validated on real data before making it canonical.
`order_type`	`Settings2.GetDestinations -> Destinations[].(Indicator, IsDelivery)`	Destination metadata confirmed in sample responses can refine service-channel normalization without replacing the current `Name` mapping.
`status`	`Sales2.GetOrders -> Orders[].PickupTime`	Future/scheduled orders can be further qualified with `PickupTime` confirmed in the Brink order contract.