# curated.pos_menu_items

## 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 | Notes |
|---|---|---|---|---|
| `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. |