# Brink mapping for `curated.pos_orders`

## Main staging sources used today

- `staging.brink_sales2_orders`
- `staging.brink_settings2_get_destinations`

## Additional staged sources available for future enrichment

- `staging.brink_settings_get_tenders`
- `staging.brink_sales2_order_payments`
- `staging.brink_settings2_get_tables`

## Mapping groups

- Business property mappings describe how Brink order content is mapped or could be 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(id_encoded, ''), CAST(order_id AS VARCHAR(50)))` | `Mapped` | Actual current insert logic. Best string-style external order identifier. |
| `order_type` | `staging.brink_settings2_get_destinations` | `name` | `CAST(dest.name AS VARCHAR(50))` | `Mapped` | Resolved by joining `brink_sales2_orders` on `destination_id` to `brink_settings2_get_destinations`. |
| `payment_method` | `staging.brink_sales2_order_payments` + `staging.brink_settings_get_tenders` | `tender_id` -> `name` | `NULL` | `Not mapped` | Requires payment summary logic plus tender lookup. |
| `subtotal` | `staging.brink_sales2_orders` | `subtotal` | `CAST(subtotal AS DECIMAL(10, 2))` | `Mapped` | Direct order subtotal. |
| `total` | `staging.brink_sales2_orders` | `total` | `CAST(total AS DECIMAL(10, 2))` | `Mapped` | Direct final total. |
| `created_at` | `staging.brink_sales2_orders` | `opened_time` | `CAST(opened_time AS TIMESTAMP)` | `Mapped` | Direct order-open timestamp. |
| `customer_number` | `staging.brink_sales2_orders` | `customer_id` | `customer_id` | `Mapped` | Brink customer identifier. |
| `id` | `staging.brink_sales2_orders` | `order_id_no_location` | `COALESCE(order_id_no_location, 0)` | `Mapped` | Compact integer-style order identifier with current fallback to `0`. |
| `table_number` | `N/A` | `N/A` | `NULL` | `Not mapped` | Current staged `Settings2.GetTables` dictionary is not joinable from the existing curated insert. |
| `tax` | `staging.brink_sales2_orders` | `tax` | `CAST(tax AS DECIMAL(10, 2))` | `Mapped` | Direct total tax. |
| `status` | `staging.brink_sales2_orders` | `(is_closed, is_future_order, is_refund)` | `CASE WHEN is_refund THEN 'refund' WHEN is_future_order THEN 'future' WHEN is_closed THEN 'closed' ELSE 'open' END` | `Mapped` | Derived readable status. |
| `terminal_id` | `staging.brink_sales2_orders` | `terminal_id` | `CAST(terminal_id AS VARCHAR(50))` | `Mapped` | Direct terminal ID. |
| `points_used` | `N/A` | `N/A` | `NULL` | `Not mapped` | No current loyalty-redemption field is used in this flow. |
| `additional_payment` | `staging.brink_sales2_order_payments` | `tender_id` | `NULL` | `Not mapped` | Could be derived later if business defines how to summarize split or secondary payments. |
| `pos_terminal_id` | `staging.brink_sales2_orders` | `terminal_id` | `CAST(terminal_id AS VARCHAR(50))` | `Mapped` | Same current value as `terminal_id`. |
| `destination` | `staging.brink_settings2_get_destinations` | `name` | `CAST(dest.name AS VARCHAR(100))` | `Mapped` | Resolved by joining `brink_sales2_orders` on `destination_id` to `brink_settings2_get_destinations`. |
| `terminal_name` | `staging.brink_settings_get_registers` | `name` | `NULL` | `Not mapped` | Resolve terminal name by joining `staging.brink_sales2_orders` on `terminal_id` to `staging.brink_settings_get_registers` on `register_id`. |
| `store_id` | `N/A` | `N/A` | `NULL` | `Not mapped` | Likely future source is tenant or location metadata outside `Sales2.GetOrders`. |
| `store_name` | `N/A` | `N/A` | `NULL` | `Not mapped` | Likely future source is tenant or location metadata outside Brink order payload. |
| `status_name` | `staging.brink_sales2_orders` | `(is_closed, is_future_order, is_refund)` | `CASE WHEN is_refund THEN 'refund' WHEN is_future_order THEN 'future' WHEN is_closed THEN 'closed' ELSE 'open' END` | `Mapped` | Currently identical to `status`. |
| `service_channel` | `staging.brink_settings2_get_destinations` | `(is_delivery, name)` | `CASE WHEN COALESCE(dest.is_delivery, FALSE) THEN 'Delivery' WHEN COALESCE(dest.name, '') ILIKE '%qr%' OR COALESCE(dest.name, '') ILIKE '%mobile%' THEN 'Mobile/QR' ELSE 'Counter' END` | `Mapped` | Derived from destination metadata: delivery flag and name-based QR/mobile detection. |
| `fulfillment_channel` | `staging.brink_settings2_get_destinations` | `is_delivery` | `CASE WHEN COALESCE(dest.is_delivery, FALSE) THEN 'Delivery' ELSE 'On-Premise' END` | `Mapped` | Derived from destination delivery flag. |
| `created_at_utc` | `staging.brink_sales2_orders` | `opened_time` | `CAST(opened_time AS TIMESTAMP)` | `Mapped` | Direct UTC timestamp from current staging. |
| `business_date` | `staging.brink_sales2_orders` | `business_date` | `CAST(business_date AS DATE)` | `Mapped` | Direct business date. |
| `currency_code` | `N/A` | `N/A` | `NULL` | `Not mapped` | No canonical currency source is used by the current Brink curated flow. |
| `order_status_code` | `staging.brink_sales2_orders` | `(is_closed, is_future_order, is_refund)` | `CASE WHEN is_refund THEN 'refund' WHEN is_future_order THEN 'future' WHEN is_closed THEN 'closed' ELSE 'open' END` | `Mapped` | Derived machine-friendly status code, currently same as `status` and `status_name`. |

## Technical and ETL property mappings

| Curated Column | Brink Staging Table | Brink Property Path | Current mapping | Status | Notes |
|---|---|---|---|---|---|
| `order_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_id` | `staging.brink_sales2_orders` | `order_id` | `CAST(order_id AS VARCHAR(199))` | `Mapped` | Direct Brink source ID 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_orders` | `_etl_batch_id` | `_etl_batch_id` | `Mapped` | ETL metadata propagated from staging. |
| `_etl_source_type` | `staging.brink_sales2_orders` | `_etl_source_type` | `_etl_source_type` | `Mapped` | ETL metadata propagated from staging. |
| `_etl_ingested_at` | `staging.brink_sales2_orders` | `_etl_ingested_at_utc` | `_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 |
|---|---|---|
| `payment_method` | `Sales2.GetOrders -> Orders[].Payments[].AdditionalData[Name='PaymentSource']` | Sample payloads confirm Brink emits a `PaymentSource` name-value pair that can supplement tender-name-based payment summaries. |
| `terminal_name` | `Settings.GetRegisters -> Register.Name` | Brink portal WSDL confirms a register lookup endpoint. The `TerminalId -> Register.Id` join should still be validated on real data before making it canonical. |
| `order_type` | `Settings2.GetDestinations -> Destinations[].(Indicator, IsDelivery)` | Destination metadata confirmed in sample responses can refine service-channel normalization without replacing the current `Name` mapping. |
| `status` | `Sales2.GetOrders -> Orders[].PickupTime` | Future/scheduled orders can be further qualified with `PickupTime` confirmed in the Brink order contract. |