# curated.pos_order_items

## Purpose

`curated.pos_order_items` is the shared business order-line fact in the warehouse.

It should answer the question: `what exactly was in the basket, in what quantity, at what price, with what item status, and under which order?`

## Why this table matters

- gives one row per business order item
- links item behavior back to the shared order header through `order_id`
- supports basket analytics, mix analysis, and future recommendation logic
- exposes readable business fields such as `item_name`, `unit_price`, and `item_status`
- keeps lineage needed for cross-source reconciliation through `source_system` and `source_order_item_id`

## Grain

One row per business order item in the curated order model.

## Column groups

- Business properties describe the order line itself: product identity, quantity, price, item state, modifier context, and business time.
- Technical and ETL properties describe warehouse lineage, surrogate keys, source reconciliation, tenant scoping, and ingestion metadata.

## Business properties

| Column | Type | Nullable | Business Meaning | Notes |
|---|---|---|---|---|
| `order_id` | `VARCHAR(50)` | No | Business-facing order identifier owning the line. | Foreign-business key linking the line back to the shared order header. |
| `item_id` | `INTEGER` | No | Source or canonical item identifier. | |
| `item_name` | `VARCHAR(255)` | No | Human-readable item name captured on the line. | Snapshot of the item label used on the order line. |
| `quantity` | `INTEGER` | Yes | Sold quantity for the line item. | |
| `unit_price` | `DECIMAL(10, 2)` | Yes | Selling price for one unit on the line. | Unit-level price before line-level aggregation. |
| `id` | `INTEGER` | No | Source-native order line identifier. | Native line identifier for reconciliation with source records. |
| `size_name` | `VARCHAR(100)` | Yes | Size or variant label for the line item. | |
| `modifiers` | `VARCHAR(65535)` | Yes | Raw or serialized modifier representation for the line. | Preserves source-level modifier detail when a normalized representation is not available. |
| `kds_delay` | `INTEGER` | Yes | Kitchen delay or prep hint attached to the line. | |
| `category_id` | `VARCHAR(199)` | Yes | Shared item category identifier. | Canonical category identifier for cross-source product grouping. |
| `category_name` | `VARCHAR(255)` | Yes | Readable category label. | |
| `item_status` | `VARCHAR(100)` | Yes | Shared business status of the line such as `active`, `voided`, `deleted`, or `cleared`. | Normalized line lifecycle status. |
| `modifier_summary` | `VARCHAR(65535)` | Yes | Readable normalized summary of applied modifiers. | Intended for analytics-friendly modifier reporting. |
| `created_at_utc` | `TIMESTAMP` | Yes | UTC timestamp representing line creation time or best available order-line time proxy. | Preferred timestamp for cross-store and cross-timezone analysis. |
| `business_date` | `DATE` | Yes | Restaurant business date used for reporting and ETL partitioning. | May differ from the calendar date of `created_at_utc`. |
| `currency_code` | `VARCHAR(10)` | Yes | Currency of the monetary values. | Required when curated data spans multiple currency contexts. |

## Technical and ETL properties

| Column | Type | Nullable | Technical Meaning | Notes |
|---|---|---|---|---|
| `order_item_key` | `BIGINT` | Yes | Warehouse-generated stable surrogate key for the order line entity. | Surrogate warehouse key for dimensional or fact-style joins. |
| `source_system` | `VARCHAR(100)` | Yes | Canonical integration name used for lineage across source platforms. | Identifies which operational platform produced the record. |
| `source_order_item_id` | `VARCHAR(199)` | Yes | Original source identifier kept for reconciliation and drill-through. | Preserves the native source key even when curated identifiers are normalized. |
| `tenant_key` | `BIGINT` | Yes | Warehouse surrogate key for tenant or brand scope. | |
| `tenant_name` | `VARCHAR(255)` | Yes | Tenant or brand identifier injected by the ETL context. | Human-readable tenant or brand lineage value. |
| `_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. |