# curated.pos_orders

## Purpose

This table stores operational POS order headers in a flattened, application-friendly format. It complements the heavier transaction schema with a simpler order-centric view that can be used by APIs, exports, and troubleshooting tools.

The shape mixes commercial facts, delivery/export status, and operational message fields, so it is best treated as a serving contract rather than a pure canonical order model.

## Warehouse design assessment

- `DW fit:` Good order-header fact table if it stays at one row per business order and separates descriptive channel fields from numeric measures.
- `Cross-client contract:` Standardize order lifecycle, payment summary, fulfillment channel, and customer linkage across all source POS systems.
- `IDs vs business values:` `order_id`, `terminal_id`, and `customer_number` are necessary, but business analysis also needs `store_name`, `terminal_name`, `status_name`, and normalized channel labels.
- `AI and business usability:` AI agents will use this table much more reliably when it exposes direct business terms such as dine-in, delivery, completed, cancelled, and paid.
- `Recommended extensions:` Add `order_key`, `source_system`, `source_order_id`, `store_id`, `store_name`, `status_name`, `service_channel`, `fulfillment_channel`, `created_at_utc`, and `business_date`.

## Detailed modeling guidance

- `Recommended role:` Use this table as the canonical commercial order header with one row per business order.
- `Key differences from a stronger curated model:` The model should favor standardized order lifecycle and channel semantics over source-specific order-type behavior or convenience payment fields.
- `What is already useful:` Order identifiers, totals, created-at time, and customer linkage already make this table highly practical.
- `What should move later to serving:` Customer-facing order summaries, operational KPI labels, and narrative order explanations belong in serving.

## Curated contract columns

| Column | Type | Monitor status | Purpose | Monitor rationale |
|---|---|---|---|---|
| `order_id` | `VARCHAR(50)` | Used by monitors | Business or external order identifier. | Core order key for linking order-level facts back to customer and item behavior. Monitors: customer-churn-risk, customer-profiles, identify-high-value-customers, segment-customers. |
| `order_type` | `VARCHAR(50)` | Used by monitors | Order type such as dine-in, carryout, or delivery. | Distinguishes service channels and supports customer behavior segmentation by fulfillment mode. Monitors: customer-profiles, segment-customers. |
| `payment_method` | `VARCHAR(50)` | Used by monitors | Payment method summary for the order. | Useful as a simplified payment preference attribute when full tender joins are not required. Monitors: customer-churn-risk, customer-profiles, identify-high-value-customers, segment-customers. |
| `subtotal` | `DECIMAL(10, 2)` | Used by monitors | Order subtotal before tax and other adjustments. | Supports spend analysis and basket-size interpretation before taxes. Monitors: customer-churn-risk, customer-profiles, identify-high-value-customers, segment-customers. |
| `total` | `DECIMAL(10, 2)` | Used by monitors | Final order total. | Main order-value measure used in customer spend and value analysis. Monitors: customer-churn-risk, customer-profiles, identify-high-value-customers, segment-customers. |
| `created_at` | `TIMESTAMP` | Used by monitors | Timestamp when the order was created. | Core event time for customer recency, visit patterns, and segmentation windows. Monitors: customer-churn-risk, customer-profiles, identify-high-value-customers, segment-customers. |
| `customer_number` | `VARCHAR(50)` | Used by monitors | Customer or loyalty account number attached to the order. | Main identity bridge from orders to loyalty customer records. Monitors: customer-churn-risk, customer-profiles, identify-high-value-customers, segment-customers. |
| `id` | `INTEGER` | Available for future monitors | Internal identifier of the curated order row. | Useful for traceability inside the serving contract, but current customer monitors usually join on `order_id` or `customer_number`. Potential monitors: customer-profiles. |
| `table_number` | `VARCHAR(50)` | Available for future monitors | Dining table or location marker for the order. | Adds dine-in context, but current customer monitors can work without table-level detail. Potential monitors: customer-profiles, segment-customers. |
| `tax` | `DECIMAL(10, 2)` | Available for future monitors | Total tax amount for the order. | Helps break down spend composition, but current customer monitors focus more on total spend than tax structure. Potential monitors: customer-profiles. |
| `status` | `VARCHAR(50)` | Available for future monitors | Current business status of the order. | Could help future filters that separate completed and cancelled orders more explicitly. Potential monitors: customer-churn-risk, customer-profiles. |
| `terminal_id` | `VARCHAR(50)` | Available for future monitors | Terminal identifier used for the order. | Can hint at drive-thru or kiosk behavior, but current customer monitors do not depend on terminal analysis. Potential monitors: customer-profiles, segment-customers. |
| `points_used` | `INTEGER` | Available for future monitors | Loyalty points redeemed on the order. | Adds loyalty-engagement context that could enrich churn or high-value customer scoring. Potential monitors: customer-churn-risk, identify-high-value-customers. |
| `additional_payment` | `DECIMAL(10, 2)` | Available for future monitors | Additional payment amount applied outside the main method. | Can explain split-pay behavior, but current customer monitors do not use it directly. Potential monitors: customer-profiles. |
| `pos_terminal_id` | `VARCHAR(50)` | Available for future monitors | POS-native terminal identifier when different from the serving field. | Can help channel segmentation if the team later distinguishes kiosk, web, or staffed terminals. Potential monitors: customer-profiles, segment-customers. |
| `destination` | `VARCHAR(100)` | Available for future monitors | Operational destination or queue label for the order. | Can help split dine-in, pickup, and other destinations in future customer behavior analysis. Potential monitors: customer-profiles, segment-customers. |
| `terminal_name` | `VARCHAR(255)` | Available for future monitors | Human-readable terminal name. | Easier-to-read terminal context could improve future channel diagnostics. Potential monitors: customer-profiles. |
| `order_key` | `BIGINT` | Recommended extension | Stable warehouse key for the order entity. | Recommended to create one canonical order identifier across clients and source systems. |
| `source_system` | `VARCHAR(100)` | Recommended extension | Canonical name of the source platform that produced the order row. | Recommended to preserve lineage in the shared curated schema. |
| `source_order_id` | `VARCHAR(199)` | Recommended extension | Original order identifier from the source platform. | Recommended to support reconciliation and drill-through into staging. |
| `store_id` | `VARCHAR(199)` | Recommended extension | Canonical store identifier associated with the order. | Recommended to make store joins consistent across different client systems. |
| `store_name` | `VARCHAR(255)` | Recommended extension | Readable store name associated with the order. | Recommended because business users and AI agents need store names, not only IDs. |
| `status_name` | `VARCHAR(100)` | Recommended extension | Readable business label for the order status. | Recommended to make lifecycle analysis easier than relying only on raw status fields. |
| `service_channel` | `VARCHAR(100)` | Recommended extension | Standardized business channel such as dine-in, takeout, drive-thru, or delivery. | Recommended to normalize order-channel analysis across clients. |
| `fulfillment_channel` | `VARCHAR(100)` | Recommended extension | Standardized fulfillment method used to complete the order. | Recommended to improve operational and customer behavior analysis across source systems. |
| `created_at_utc` | `TIMESTAMP` | Recommended extension | UTC timestamp when the order was created in canonical warehouse time. | Recommended to standardize timestamps across tenants and time zones. |
| `business_date` | `DATE` | Recommended extension | Business date associated with the order in curated reporting logic. | Recommended to simplify grouping and filtering for downstream analysis. |
| `tenant_key` | `BIGINT` | Recommended extension | Stable warehouse key for the tenant, brand, or client that owns the order. | Recommended because a shared curated schema should preserve explicit cross-client context for core commercial facts. |
| `tenant_name` | `VARCHAR(255)` | Recommended extension | Readable tenant, brand, or client name associated with the order. | Recommended to improve business and AI usability in cross-client reporting. |
| `currency_code` | `VARCHAR(10)` | Recommended extension | ISO or business-standard currency code used for order monetary values. | Recommended because commercial analysis across clients should not assume one currency. |
| `order_status_code` | `VARCHAR(100)` | Recommended extension | Standardized canonical code for the business order lifecycle state. | Recommended to complement `status_name` with a stable machine-friendly business status contract. |