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