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_order_items.md 5,892 bytes · Updated Oct 23, 2026 10:44 AM EDT

Raw File

Brink mapping for `curated.pos_order_items`

Main staging sources used today

staging.brink_sales2_orders
staging.brink_sales2_order_entries
staging.brink_sales2_order_entry_modifiers
staging.brink_settings2_items
staging.brink_settings2_item_groups

Mapping groups

Business property mappings describe how Brink order-entry content is 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(o.id_encoded, ''), CAST(o.order_id AS VARCHAR(50)))`	`Mapped`	Resolved from parent order row.
`item_id`	`staging.brink_sales2_order_entries`	`item_id`	`CAST(COALESCE(e.item_id, 0) AS INTEGER)`	`Mapped`	Direct item reference with fallback to `0`.
`item_name`	`staging.brink_sales2_order_entries`	`description`	`COALESCE(NULLIF(e.description, ''), 'Unknown')`	`Mapped`	Transactional item description with fallback to `Unknown`.
`quantity`	`N/A`	`N/A`	`1 AS quantity`	`Mapped`	Hardcoded to `1` per Brink order entry. Each entry row represents one unit.
`unit_price`	`staging.brink_sales2_order_entries`	`price`	`CAST(e.price AS DECIMAL(10, 2))`	`Mapped`	Direct line price.
`id`	`staging.brink_sales2_order_entries`	`order_entry_id`	`CAST(COALESCE(e.order_entry_id, 0) AS INTEGER)`	`Mapped`	Direct order-entry identifier with fallback to `0`.
`size_name`	`N/A`	`N/A`	`NULL`	`Not mapped`	No current size/variant field is used in the Brink curated flow.
`modifiers`	`staging.brink_sales2_order_entry_modifiers`	`(modifier_id, item_id, price)`	JSON array via `LISTAGG` with `{"modifier_id":..., "item_id":..., "price":...}` ordered by `modifier_ordinal`	`Mapped`	Built from subquery over `brink_sales2_order_entry_modifiers` grouped by `order_id` and `order_entry_id`.
`kds_delay`	`N/A`	`N/A`	`NULL`	`Not mapped`	No KDS timing field is joined into this curated load.
`category_id`	`staging.brink_settings2_item_groups`	`item_group_id`	`CAST(ig.item_group_id AS VARCHAR(199))`	`Mapped`	Resolved by joining entry `item_id` to `brink_settings2_item_groups` on `item_id` with `item_group_ordinal = 1` and latest `snapshot_date <= business_date`.
`category_name`	`staging.brink_settings_get_item_groups`	`name`	`NULL`	`Not mapped`	No item-group name table exists in staging yet. Would require joining to `staging.brink_settings_get_item_groups` on `item_group_id`.
`item_status`	`staging.brink_sales2_order_entries`	`(is_deleted, is_voided, is_cleared)`	`CASE WHEN e.is_deleted THEN 'deleted' WHEN e.is_voided THEN 'voided' WHEN e.is_cleared THEN 'cleared' ELSE 'active' END`	`Mapped`	Derived readable status.
`modifier_summary`	`staging.brink_sales2_order_entry_modifiers` + `staging.brink_settings2_items`	`item_id -> name`	`LISTAGG(COALESCE(NULLIF(mod_item.name, ''), CAST(m.modifier_id AS VARCHAR(50))), ', ') WITHIN GROUP (ORDER BY m.modifier_ordinal)`	`Mapped`	Comma-separated modifier names resolved from `brink_settings2_items` via `item_id`, ordered by `modifier_ordinal`. Falls back to raw `modifier_id` when name is unavailable.
`created_at_utc`	`staging.brink_sales2_orders`	`opened_time`	`CAST(o.opened_time AS TIMESTAMP)`	`Mapped`	Best available order-line time proxy from parent order.
`business_date`	`staging.brink_sales2_orders`	`business_date`	`CAST(o.business_date AS DATE)`	`Mapped`	Taken from parent order.
`currency_code`	`N/A`	`N/A`	`NULL`	`Not mapped`	No canonical currency source is used by the current Brink curated flow.

Technical and ETL property mappings

Curated Column	Brink Staging Table	Brink Property Path	Current mapping	Status	Notes
`order_item_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_item_id`	`staging.brink_sales2_order_entries`	`order_entry_id`	`CAST(e.order_entry_id AS VARCHAR(199))`	`Mapped`	Direct Brink source line identifier 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_order_entries`	`_etl_batch_id`	`e._etl_batch_id`	`Mapped`	ETL metadata propagated from staging.
`_etl_source_type`	`staging.brink_sales2_order_entries`	`_etl_source_type`	`e._etl_source_type`	`Mapped`	ETL metadata propagated from staging.
`_etl_ingested_at`	`staging.brink_sales2_order_entries`	`_etl_ingested_at_utc`	`e._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
`item_name`	`Settings2.GetItems -> Items[].Name`	Use the item master name as a fallback when transactional `Description` is not stable enough.
`category_name`	`Settings.GetItemGroups -> ItemGroup.Name`	Resolve category name by joining to item-group staging when `staging.brink_settings_get_item_groups` becomes available.
`modifiers`	`Settings.GetModifierCodes -> ModifierCode.Name`	Brink portal WSDL confirms modifier-code lookup that can enrich serialized modifier text with readable names.