# Curated Model

## Purpose

This folder documents the current `curated` schema created by the per-table bootstrap SQL files under `backend/Kds.Ai.Warehouse/Sql/Bootstrap/Tenant/Curated/`.

Each file describes one curated table, what business surface it provides, and what each column is used for.

Each table document now separates columns into fields currently used by monitors and fields with potential future monitor value.
Columns marked as not used by monitors are omitted from the per-table column sections.

## Cross-table summary

| Table | Primary role | Strong sides | Main modeling risk | Most important next step |
|---|---|---|---|---|
| `curated.customers` | Canonical customer entity | Readable customer attributes, good AI and business usability base | Mixed master data and weak explicit history strategy | Make it an explicit SCD-style dimension |
| `curated.inventory_items` | Inventory snapshot with item context | Useful stock, reorder, vendor, and category context | Mixes descriptive item data with changing stock-position facts | Split later into item dimension plus inventory snapshot or fact if scale grows |
| `curated.kitchen_item_status_events` | Kitchen lifecycle event stream | Strong operational event semantics, great for anomaly detection | Needs deterministic event ordering and standardized status semantics | Treat as append-only canonical event fact |
| `curated.kitchen_order_items` | Kitchen execution line fact | Good workload, prep-time, recall, and routing signals | State may be spread across snapshot and events | Keep canonical item snapshot and formalize linkage to event stream |
| `curated.kitchen_orders` | Kitchen order operational header | Good queue, load, and priority surface | Snapshot semantics are not fully explicit | Define clearly as current-state kitchen order header |
| `curated.loyalty_customers` | Loyalty member dimension | Good retention and loyalty context | Loyalty identity and history can drift without explicit versioning | Make membership state and history explicit |
| `curated.pos_catalog_items` | Canonical item or product dimension | Strong product base for analytics | Still partly source-config driven | Move toward a fully conformed product dimension |
| `curated.pos_hourly_sales` | Aggregate fact | Good for forecasting and trend baselines | Grain must stay very strict across clients | Lock grain and aggregation rules explicitly |
| `curated.pos_menu_items` | Menu-state dimension or snapshot | Good availability and routing context | Can drift into serving or UI model | Keep it as canonical sellable menu state |
| `curated.pos_modifiers` | Transitional reference model | Some useful modifier pricing and group context | Slot-based structure is weak warehouse design | Plan redesign into normalized modifier group plus option model |
| `curated.pos_order_items` | Business order-line fact | Good basket and order composition context | Can overlap with transaction-line model | Separate order semantics from settlement semantics |
| `curated.pos_orders` | Business order header fact | Strong commercial order surface | Still partly serving-like and source-shaped | Strengthen canonical order lifecycle and channel semantics |
| `curated.pos_table_definitions` | Seating-layout reference dimension | Useful hierarchy and layout metadata | Source semantics vary strongly by vendor | Keep only stable seating concepts in curated |
| `curated.pos_tables` | Physical or logical dining table dimension | Useful dine-in context | Can become too operational if overloaded | Keep it as lightweight reference dimension |
| `curated.pos_tenders` | Payment-method dimension | Good basis for payment family standardization | Too many low-level tender flags | Promote standardized payment-family semantics |
| `curated.pos_transaction_items` | Transaction-line sales fact | One of the strongest analytic tables | Still carries many coded legacy POS attributes | Keep as core canonical sales-line fact with stronger semantic labels |
| `curated.pos_transaction_tenders` | Payment-event fact | Strong payment behavior and tender mix analysis base | Payment semantics are still partly coded | Standardize payment family and payment event meaning |
| `curated.pos_transactions` | Transaction-header fact | Strong top-level commercial fact | Too dependent on decomposed time fields and coded attributes | Make it explicitly canonical commercial transaction header |

## Table type classification

| Table | Recommended type |
|---|---|
| `curated.customers` | `dimension` |
| `curated.inventory_items` | `snapshot` |
| `curated.kitchen_item_status_events` | `event` |
| `curated.kitchen_order_items` | `snapshot` |
| `curated.kitchen_orders` | `snapshot` |
| `curated.loyalty_customers` | `dimension` |
| `curated.pos_catalog_items` | `dimension` |
| `curated.pos_hourly_sales` | `fact` |
| `curated.pos_menu_items` | `snapshot` |
| `curated.pos_modifiers` | `dimension` |
| `curated.pos_order_items` | `fact` |
| `curated.pos_orders` | `fact` |
| `curated.pos_table_definitions` | `dimension` |
| `curated.pos_tables` | `dimension` |
| `curated.pos_tenders` | `dimension` |
| `curated.pos_transaction_items` | `fact` |
| `curated.pos_transaction_tenders` | `fact` |
| `curated.pos_transactions` | `fact` |

## Tables

- `customers.md`
- `inventory_items.md`
- `kitchen_item_status_events.md`
- `kitchen_order_items.md`
- `kitchen_orders.md`
- `loyalty_customers.md`
- `pos_catalog_items.md`
- `pos_hourly_sales.md`
- `pos_menu_items.md`
- `pos_modifiers.md`
- `pos_order_items.md`
- `pos_orders.md`
- `pos_table_definitions.md`
- `pos_tables.md`
- `pos_tenders.md`
- `pos_transaction_items.md`
- `pos_transaction_tenders.md`
- `pos_transactions.md`
- `todo.md`