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

Raw File

Brink mapping for `curated.pos_menu_items`

Main staging sources used today

staging.brink_settings2_items
staging.brink_settings2_item_groups

Mapping groups

Business property mappings describe how Brink menu item 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
`item_id`	`staging.brink_settings2_items`	`item_id`	`CAST(i.item_id AS INTEGER)`	`Mapped`	Direct item ID.
`item_name`	`staging.brink_settings2_items`	`name`	`COALESCE(NULLIF(i.name, ''), 'Unknown')`	`Mapped`	Menu-facing item name with fallback to `Unknown`.
`price`	`staging.brink_settings2_items`	`price`	`CAST(COALESCE(i.price, 0) AS DECIMAL(10, 2))`	`Mapped`	Direct base price with fallback to `0`.
`cost`	`staging.brink_settings2_items`	`cost`	`CAST(i.cost AS DECIMAL(10, 2))`	`Mapped`	Direct item cost.
`category_id`	`staging.brink_settings2_item_groups`	`item_group_id`	`COALESCE(CAST(ig.item_group_id AS INTEGER), 0)`	`Mapped`	Resolved by joining `brink_settings2_items` to `brink_settings2_item_groups` on `item_id` with `item_group_ordinal = 1`. Falls back to `0` when no group exists.
`description`	`staging.brink_settings2_items`	`description`	`i.description`	`Mapped`	Direct long description.
`kds_delay`	`staging.brink_settings2_items`	`days_available`	`i.days_available`	`Mapped`	Maps from `days_available` integer in the item master. Used as KDS delay proxy.
`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`.
`kds_station`	`staging.brink_settings2_items`	`video_group_id`	`NULL`	`Not mapped`	Best documented kitchen-routing field, but not yet joined to station name.
`is_86d`	`staging.brink_settings2_items`	`is_active`	`CASE WHEN COALESCE(i.is_active, FALSE) THEN 0 ELSE 1 END`	`Mapped`	Derived inverse of `is_active`. When item is not active, it is treated as 86'd.
`"86d_reason"`	`N/A`	`N/A`	`NULL`	`Not mapped`	No matching field documented.
`"86d_at"`	`N/A`	`N/A`	`NULL`	`Not mapped`	No matching field documented.
`station_name`	`staging.brink_settings2_get_kitchen_queues`	`name`	`NULL`	`Not mapped`	Would require joining `staging.brink_settings2_items` on `video_group_id` to `staging.brink_settings2_get_kitchen_queues` on `kitchen_queue_id`.
`availability_status`	`staging.brink_settings2_items`	`is_active`	`CASE WHEN COALESCE(i.is_active, FALSE) THEN 'active' ELSE 'inactive' END`	`Mapped`	Derived readable availability status from `is_active`.
`is_active`	`staging.brink_settings2_items`	`is_active`	`i.is_active`	`Mapped`	Direct active flag from item master.
`snapshot_date`	`staging.brink_settings2_items`	`snapshot_date`	`CAST(i.snapshot_date AS DATE)`	`Mapped`	Snapshot date from the Brink request.
`currency_code`	`N/A`	`N/A`	`NULL`	`Not mapped`	Currency is not documented in the item snapshot.
`timezone_name`	`N/A`	`N/A`	`NULL`	`Not mapped`	Timezone metadata is not documented in the available Brink staging model.

Technical and ETL property mappings

Curated Column	Brink Staging Table	Brink Property Path	Current mapping	Status	Notes
`menu_item_key`	`N/A`	`N/A`	`NULL`	`Not mapped`	Warehouse-generated key.
`source_system`	`N/A`	`N/A`	`constant 'brink_pos'`	`Derived constant`	Warehouse constant, not a Brink API field.
`source_menu_item_id`	`staging.brink_settings2_items`	`item_id`	`CAST(i.item_id AS VARCHAR(199))`	`Mapped`	Direct Brink source item ID.
`effective_from_utc`	`staging.brink_settings2_items`	`snapshot_date`	`CAST(i.snapshot_date AS TIMESTAMP)`	`Mapped`	Uses `snapshot_date` as effective start for the snapshot.
`effective_to_utc`	`N/A`	`N/A`	`NULL`	`Not mapped`	Not populated in current snapshot-scoped insert.
`tenant_key`	`N/A`	`N/A`	`NULL`	`Not mapped`	Warehouse-generated tenant key.
`tenant_name`	`N/A`	`N/A`	`parameter @tenant_id`	`ETL context`	Tenant metadata is outside the documented Brink staging payloads.
`_etl_batch_id`	`staging.brink_settings2_items`	`_etl_batch_id`	`i._etl_batch_id`	`Mapped`	ETL metadata propagated from staging.
`_etl_source_type`	`staging.brink_settings2_items`	`_etl_source_type`	`i._etl_source_type`	`Mapped`	ETL metadata propagated from staging.
`_etl_ingested_at`	`staging.brink_settings2_items`	`_etl_ingested_at_utc`	`i._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
`category_name`	`Settings2.GetMenu -> Menu.Categories[].Name`	Use the menu contract when the curated menu model needs menu-facing category names rather than master item-group names.
`category_id`	`Settings2.GetMenu -> Menu.Categories[]`	Brink portal WSDL confirms `GetMenu(MenuId)`, which exposes explicit menu categories beyond item groups.
`kds_station`	`Settings2.GetKitchenQueues`	Resolve kitchen station name from `video_group_id` when kitchen queue staging becomes available.