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