# Curated Model

## Purpose

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

This is the business-facing warehouse layer that AI agents should use first.
The model is meant to be shared across integrations, so each table should expose stable business meaning rather than vendor-specific payload shape.

For this folder, the source of truth for table existence and columns is the deployed SQL.
The documentation explains:

- what business entity each table represents
- what value it gives to PO, analysts, and AI agents
- what the current schema contract looks like
- how the model is intended to support multi-source ingestion

## Current priority

Current active curated tables are:

- `curated.pos_orders`
- `curated.pos_order_items`
- `curated.pos_menu_items`
- `curated.employees`
- `curated.pos_jobs`
- `curated.labor_shifts`
- `curated.pos_destinations`

## Modeling principles for PO

- `curated` is the semantic contract for downstream AI and business use, not a copy of staging.
- The model must stay broad enough to accept data from multiple POS systems.
- If a source does not provide a field yet, the field may stay `NULL`; this is better than silently widening ETL scope.
- Shared business terms such as order, item, status, business date, service channel, and source lineage are more important than vendor-native field names.
- Order-centric and transaction-centric facts can coexist, but their business meaning must stay explicit.
- Source-specific population rules and mappings belong in source-mapping documents, not in the curated schema contract.

## Cross-table summary

| Table | Primary role | Grain | Key business columns | Strong sides | Main modeling risk | Most important next step |
|---|---|---|---|---|---|---|
| `curated.employees` | Employee dimension | One row per employee snapshot | `employee_id`, `display_name`, `is_active`, `hire_date` | Stable workforce reference for joins and attribution | Snapshot semantics must stay explicit | Keep source-agnostic employee identity fields stable |
| `curated.labor_shifts` | Labor fact | One row per labor shift | `shift_id`, `employee_id`, `business_date`, `minutes_worked`, `pay_rate` | Shift timing and labor-cost analysis surface | Vendor-specific break semantics can leak through | Keep shift grain explicit |
| `curated.pos_destinations` | Destination dimension | One row per destination snapshot | `destination_id`, `name`, `is_delivery`, `is_active` | Order-channel and routing context | Snapshot semantics must stay explicit | Keep source-agnostic destination identity fields stable |
| `curated.pos_jobs` | Job dimension | One row per job snapshot | `job_id`, `name`, `is_bartender`, `is_delivery_driver` | Workforce role context for orders and labor | Source role semantics can drift by vendor | Keep canonical job meaning narrow |
| `curated.pos_menu_items` | Menu-state snapshot | One row per menu item per snapshot | `item_id`, `item_name`, `price`, `is_active`, `category_id` | Good availability and routing context | Can drift into serving or UI model | Keep it as canonical sellable menu state |
| `curated.pos_order_items` | Business order-line fact | One row per order line item | `order_id`, `item_id`, `item_name`, `unit_price`, `quantity` | 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 | One row per business order | `order_id`, `order_type`, `subtotal`, `total`, `business_date`, `service_channel` | Strong commercial order surface | Still partly serving-like and source-shaped | Strengthen canonical order lifecycle and channel semantics |

## Table type classification

| Table | Recommended type |
|---|---|
| `curated.employees` | `dimension` |
| `curated.labor_shifts` | `fact` |
| `curated.pos_destinations` | `dimension` |
| `curated.pos_jobs` | `dimension` |
| `curated.pos_menu_items` | `snapshot` |
| `curated.pos_order_items` | `fact` |
| `curated.pos_orders` | `fact` |

## Tables

- `employees.md`
- `labor_shifts.md`
- `pos_destinations.md`
- `pos_jobs.md`
- `pos_menu_items.md`
- `pos_order_items.md`
- `pos_orders.md`
- `todo.md`