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/curated-model/pos_menu_items.md 3,879 bytes · Updated Oct 23, 2026 10:44 AM EDT

Raw File

Purpose

This table stores menu-facing items as they should be presented in ordering or store-operational surfaces. It combines product, category, pricing, and menu-state fields into a lightweight serving model.

It is especially useful for menu rendering, 86-state tracking, and kitchen-delay or station routing context at the menu level.

Schema contract

The deployed SQL under backend/Kds.Ai.Warehouse/Sql/Bootstrap/Tenant/Curated/ is the source of truth for the current column names, types, and nullability.

Column groups

Business properties describe the business entity, event, fact, or reference record represented by the table.
Technical and ETL properties describe warehouse lineage, surrogate keys, source reconciliation, tenant scoping, slowly changing dimension controls, and ingestion metadata.

Business properties

Column	Type	Nullable	Business Meaning
`item_id`	`INTEGER`	No	Menu item identifier.
`item_name`	`VARCHAR(255)`	No	Customer- or operator-facing item name.
`price`	`DECIMAL(10, 2)`	No	Current selling price of the item.
`category_id`	`INTEGER`	No	Category identifier for menu grouping.
`description`	`VARCHAR(65535)`	Yes	Longer description shown to users or operators.
`kds_delay`	`INTEGER`	Yes	Delay or prep offset used for kitchen-display behavior.
`category_name`	`VARCHAR(100)`	Yes	Human-readable category label.
`kds_station`	`INTEGER`	Yes	Kitchen station or routing identifier for the item.
`is_86d`	`DECIMAL(2, 0)`	Yes	Flag indicating that the item is currently unavailable or 86'd.
`86d_reason`	`VARCHAR(65535)`	Yes	Free-form reason why the item is unavailable.
`86d_at`	`TIMESTAMP`	Yes	Timestamp when the item was marked unavailable.
`station_name`	`VARCHAR(255)`	Yes	Readable kitchen station or routing name for the item.
`availability_status`	`VARCHAR(100)`	Yes	Standardized business status of menu availability such as active, unavailable, or seasonal.
`is_active`	`BOOLEAN`	Yes	Canonical flag indicating whether the menu item is active in the curated model.
`cost`	`DECIMAL(10, 2)`	Yes	Item cost used for margin and cost analysis.
`snapshot_date`	`DATE`	Yes	Date of the menu item snapshot used for ETL partitioning.
`currency_code`	`VARCHAR(10)`	Yes	ISO or business-standard currency code used for menu price values.
`timezone_name`	`VARCHAR(100)`	Yes	Canonical IANA or business-standard time zone name used for menu-state timestamps.

Technical and ETL properties

Column	Type	Nullable	Technical Meaning	Notes
`menu_item_key`	`BIGINT`	Yes	Stable warehouse key for the menu item entity.
`source_system`	`VARCHAR(100)`	Yes	Canonical name of the source platform that produced the menu item row.
`source_menu_item_id`	`VARCHAR(199)`	Yes	Original menu item identifier from the source platform.
`effective_from_utc`	`TIMESTAMP`	Yes	UTC timestamp from which the current menu-item state is valid.
`effective_to_utc`	`TIMESTAMP`	Yes	UTC timestamp until which the current menu-item state is valid.
`tenant_key`	`BIGINT`	Yes	Stable warehouse key for the tenant, brand, or client that owns the menu item.
`tenant_name`	`VARCHAR(255)`	Yes	Readable tenant, brand, or client name associated with the menu item.
`_etl_batch_id`	`VARCHAR(256)`	Yes	ETL batch lineage identifier.
`_etl_source_type`	`VARCHAR(100)`	Yes	Source adapter type used by ETL.
`_etl_ingested_at`	`TIMESTAMP`	No	Warehouse ingestion timestamp for the curated row.	Required ingestion audit timestamp.