# Curated Redshift Schema

## Conventions

- Nullable columns may be unavailable for some source systems.
- Redshift does not enforce primary-key or foreign-key constraints on these tables. Relationships below are logical relationships.
- Columns ending in `_key` are warehouse keys intended for canonical joins.
- Columns beginning with `_etl_` provide ingestion lineage and audit information.
- `business_date` represents the restaurant's operational date and may differ from the calendar date of a UTC timestamp.
- Monetary values should be interpreted using `currency_code` where that column is available.

## Schema Summary

| Table | Purpose | Grain |
|---|---|---|
| `curated.kitchen_orders` | Kitchen order headers | One row per kitchen order |
| `curated.kitchen_order_items` | Items prepared by the kitchen | One row per kitchen order item |
| `curated.kitchen_item_status_events` | Kitchen item status history | One row per item status event |
| `curated.customers` | Customer records | One row per customer record |
| `curated.pos_menu_items` | Canonical menu item state | One row per menu item snapshot |
| `curated.pos_order_items` | POS order lines | One row per POS order item |
| `curated.pos_orders` | POS order headers | One row per POS order |
| `curated.employees` | Employee dimension | One row per employee snapshot |
| `curated.pos_jobs` | POS job or role dimension | One row per job snapshot |
| `curated.labor_shifts` | Employee labor shifts | One row per shift |
| `curated.pos_destinations` | Order destination or service channel dimension | One row per destination snapshot |

## Logical Relationships

| Parent | Child | Join condition |
|---|---|---|
| `curated.kitchen_orders` | `curated.kitchen_order_items` | `kitchen_orders.guid = kitchen_order_items.order_guid` |
| `curated.kitchen_order_items` | `curated.kitchen_item_status_events` | `kitchen_order_items.guid = kitchen_item_status_events.item_guid` |
| `curated.pos_orders` | `curated.pos_order_items` | `pos_orders.order_id = pos_order_items.order_id` |
| `curated.employees` | `curated.labor_shifts` | `employees.employee_id = labor_shifts.employee_id` |
| `curated.pos_jobs` | `curated.labor_shifts` | `pos_jobs.job_id = labor_shifts.job_id` |

## `curated.kitchen_orders`

Kitchen-facing order headers used to analyze order volume, workflow, priority, and preparation performance.

| Column | Redshift type | Nullable |
|---|---|---|
| `guid` | `VARCHAR(36)` | No |
| `store_guid` | `VARCHAR(36)` | No |
| `destination` | `VARCHAR(255)` | Yes |
| `external_id` | `VARCHAR(199)` | No |
| `guest_table` | `VARCHAR(199)` | Yes |
| `is_priority` | `SMALLINT` | No |
| `items_count` | `INTEGER` | No |
| `order_type` | `VARCHAR(199)` | Yes |
| `pos_terminal` | `VARCHAR(199)` | Yes |
| `preparation_time` | `INTEGER` | Yes |
| `server_name` | `VARCHAR(199)` | Yes |
| `user_info` | `VARCHAR(199)` | Yes |
| `done` | `SMALLINT` | No |
| `create_time` | `INTEGER` | No |
| `update_time` | `INTEGER` | No |
| `upload_time` | `INTEGER` | Yes |
| `is_deleted` | `SMALLINT` | No |
| `update_device` | `VARCHAR(36)` | Yes |
| `phone` | `VARCHAR(65535)` | Yes |
| `create_local_time` | `INTEGER` | Yes |
| `is_hidden` | `SMALLINT` | No |
| `customer_guid` | `VARCHAR(36)` | Yes |
| `smart_order_start_time` | `INTEGER` | Yes |
| `order_messages` | `VARCHAR(255)` | No |
| `customer_name` | `VARCHAR(60)` | Yes |
| `seat` | `INTEGER` | Yes |
| `order_status` | `INTEGER` | Yes |
| `_etl_batch_id` | `VARCHAR(256)` | Yes |
| `_etl_source_type` | `VARCHAR(100)` | Yes |
| `_etl_ingested_at` | `TIMESTAMP` | No |

## `curated.kitchen_order_items`

Kitchen order lines with preparation, routing, transfer, and completion attributes.

| Column | Redshift type | Nullable |
|---|---|---|
| `guid` | `VARCHAR(36)` | No |
| `order_guid` | `VARCHAR(36)` | No |
| `external_id` | `VARCHAR(199)` | No |
| `name` | `VARCHAR(199)` | No |
| `category` | `VARCHAR(100)` | Yes |
| `device_id` | `INTEGER` | Yes |
| `is_priority` | `SMALLINT` | No |
| `pre_modifier` | `VARCHAR(65535)` | Yes |
| `item_bump_guid` | `VARCHAR(36)` | Yes |
| `create_time` | `INTEGER` | No |
| `update_time` | `INTEGER` | No |
| `upload_time` | `INTEGER` | Yes |
| `is_deleted` | `SMALLINT` | No |
| `update_device` | `VARCHAR(36)` | Yes |
| `printed_status` | `SMALLINT` | No |
| `create_local_time` | `INTEGER` | Yes |
| `is_hidden` | `SMALLINT` | No |
| `ready_since_local_time` | `INTEGER` | Yes |
| `preparation_time` | `INTEGER` | Yes |
| `quantity` | `INTEGER` | No |
| `count` | `INTEGER` | Yes |
| `condiments_count` | `INTEGER` | No |
| `s0` | `VARCHAR(45)` | Yes |
| `s1` | `VARCHAR(45)` | Yes |
| `training_video` | `VARCHAR(65535)` | Yes |
| `transfer_from_device_id` | `INTEGER` | Yes |
| `transfer_time` | `INTEGER` | Yes |
| `untransfer_time` | `INTEGER` | Yes |
| `beeped` | `SMALLINT` | No |
| `build_card` | `VARCHAR(65535)` | Yes |
| `recall_time` | `INTEGER` | Yes |
| `_etl_batch_id` | `VARCHAR(256)` | Yes |
| `_etl_source_type` | `VARCHAR(100)` | Yes |
| `_etl_ingested_at` | `TIMESTAMP` | No |

## `curated.kitchen_item_status_events`

Status transitions for kitchen order items, including preparation and completion timing.

| Column | Redshift type | Nullable |
|---|---|---|
| `guid` | `VARCHAR(36)` | No |
| `item_guid` | `VARCHAR(36)` | No |
| `status` | `SMALLINT` | No |
| `last_status` | `SMALLINT` | Yes |
| `upload_time` | `INTEGER` | Yes |
| `update_device` | `VARCHAR(36)` | Yes |
| `create_local_time` | `INTEGER` | Yes |
| `prepared_local_time` | `INTEGER` | Yes |
| `done_local_time` | `INTEGER` | Yes |
| `prepared_device_id` | `INTEGER` | Yes |
| `done_device_id` | `INTEGER` | Yes |
| `hide_level` | `SMALLINT` | No |
| `last_hide_level` | `SMALLINT` | No |
| `create_time` | `INTEGER` | No |
| `update_time` | `INTEGER` | No |
| `is_deleted` | `SMALLINT` | No |
| `_etl_batch_id` | `VARCHAR(256)` | Yes |
| `_etl_source_type` | `VARCHAR(100)` | Yes |
| `_etl_ingested_at` | `TIMESTAMP` | No |

## `curated.customers`

Customer identity and contact records associated with a restaurant store.

| Column | Redshift type | Nullable |
|---|---|---|
| `guid` | `VARCHAR(36)` | No |
| `external_id` | `VARCHAR(199)` | Yes |
| `name` | `VARCHAR(255)` | Yes |
| `phone` | `VARCHAR(255)` | Yes |
| `phone2` | `VARCHAR(255)` | Yes |
| `address` | `VARCHAR(65535)` | Yes |
| `address2` | `VARCHAR(65535)` | Yes |
| `city` | `VARCHAR(255)` | Yes |
| `state` | `VARCHAR(255)` | Yes |
| `zip` | `VARCHAR(50)` | Yes |
| `country` | `VARCHAR(255)` | Yes |
| `email` | `VARCHAR(255)` | Yes |
| `webmail` | `VARCHAR(255)` | Yes |
| `store_guid` | `VARCHAR(36)` | No |
| `create_time` | `INTEGER` | Yes |
| `update_time` | `INTEGER` | Yes |
| `upload_time` | `INTEGER` | Yes |
| `is_deleted` | `SMALLINT` | No |
| `update_device` | `VARCHAR(36)` | Yes |
| `create_local_time` | `INTEGER` | Yes |
| `_etl_batch_id` | `VARCHAR(256)` | Yes |
| `_etl_source_type` | `VARCHAR(100)` | Yes |
| `_etl_ingested_at` | `TIMESTAMP` | No |

## `curated.pos_menu_items`

Canonical menu item state, including pricing, category, kitchen routing, availability, and source lineage.

| Column | Redshift type | Nullable |
|---|---|---|
| `item_id` | `INTEGER` | No |
| `item_name` | `VARCHAR(255)` | No |
| `price` | `DECIMAL(10,2)` | No |
| `category_id` | `INTEGER` | No |
| `description` | `VARCHAR(65535)` | Yes |
| `kds_delay` | `INTEGER` | Yes |
| `category_name` | `VARCHAR(100)` | Yes |
| `kds_station` | `INTEGER` | Yes |
| `is_86d` | `DECIMAL(2,0)` | Yes |
| `86d_reason` | `VARCHAR(65535)` | Yes |
| `86d_at` | `TIMESTAMP` | Yes |
| `menu_item_key` | `BIGINT` | Yes |
| `source_system` | `VARCHAR(100)` | Yes |
| `source_menu_item_id` | `VARCHAR(199)` | Yes |
| `station_name` | `VARCHAR(255)` | Yes |
| `availability_status` | `VARCHAR(100)` | Yes |
| `is_active` | `BOOLEAN` | Yes |
| `cost` | `DECIMAL(10,2)` | Yes |
| `snapshot_date` | `DATE` | Yes |
| `effective_from_utc` | `TIMESTAMP` | Yes |
| `effective_to_utc` | `TIMESTAMP` | Yes |
| `tenant_key` | `BIGINT` | Yes |
| `tenant_name` | `VARCHAR(255)` | Yes |
| `currency_code` | `VARCHAR(10)` | Yes |
| `timezone_name` | `VARCHAR(100)` | Yes |
| `_etl_batch_id` | `VARCHAR(256)` | Yes |
| `_etl_source_type` | `VARCHAR(100)` | Yes |
| `_etl_ingested_at` | `TIMESTAMP` | No |

## `curated.pos_order_items`

Canonical POS order lines for basket, product-mix, pricing, and item-status analysis.

| Column | Redshift type | Nullable |
|---|---|---|
| `order_id` | `VARCHAR(50)` | No |
| `item_id` | `INTEGER` | No |
| `item_name` | `VARCHAR(255)` | No |
| `quantity` | `INTEGER` | Yes |
| `unit_price` | `DECIMAL(10,2)` | Yes |
| `id` | `INTEGER` | No |
| `size_name` | `VARCHAR(100)` | Yes |
| `modifiers` | `VARCHAR(65535)` | Yes |
| `kds_delay` | `INTEGER` | Yes |
| `order_item_key` | `BIGINT` | Yes |
| `source_system` | `VARCHAR(100)` | Yes |
| `source_order_item_id` | `VARCHAR(199)` | Yes |
| `category_id` | `VARCHAR(199)` | Yes |
| `category_name` | `VARCHAR(255)` | Yes |
| `item_status` | `VARCHAR(100)` | Yes |
| `modifier_summary` | `VARCHAR(65535)` | Yes |
| `created_at_utc` | `TIMESTAMP` | Yes |
| `business_date` | `DATE` | Yes |
| `tenant_key` | `BIGINT` | Yes |
| `tenant_name` | `VARCHAR(255)` | Yes |
| `currency_code` | `VARCHAR(10)` | Yes |
| `_etl_batch_id` | `VARCHAR(256)` | Yes |
| `_etl_source_type` | `VARCHAR(100)` | Yes |
| `_etl_ingested_at` | `TIMESTAMP` | No |

## `curated.pos_orders`

Canonical POS order headers for sales, tax, order status, service channel, and fulfillment analysis.

| Column | Redshift type | Nullable |
|---|---|---|
| `order_id` | `VARCHAR(50)` | No |
| `order_type` | `VARCHAR(50)` | Yes |
| `payment_method` | `VARCHAR(50)` | Yes |
| `subtotal` | `DECIMAL(10,2)` | Yes |
| `total` | `DECIMAL(10,2)` | Yes |
| `created_at` | `TIMESTAMP` | Yes |
| `customer_number` | `VARCHAR(50)` | Yes |
| `id` | `INTEGER` | No |
| `table_number` | `VARCHAR(50)` | Yes |
| `tax` | `DECIMAL(10,2)` | Yes |
| `status` | `VARCHAR(50)` | Yes |
| `terminal_id` | `VARCHAR(50)` | Yes |
| `points_used` | `INTEGER` | Yes |
| `additional_payment` | `DECIMAL(10,2)` | Yes |
| `pos_terminal_id` | `VARCHAR(50)` | Yes |
| `destination` | `VARCHAR(100)` | Yes |
| `terminal_name` | `VARCHAR(255)` | Yes |
| `order_key` | `BIGINT` | Yes |
| `source_system` | `VARCHAR(100)` | Yes |
| `source_order_id` | `VARCHAR(199)` | Yes |
| `store_id` | `VARCHAR(199)` | Yes |
| `store_name` | `VARCHAR(255)` | Yes |
| `status_name` | `VARCHAR(100)` | Yes |
| `service_channel` | `VARCHAR(100)` | Yes |
| `fulfillment_channel` | `VARCHAR(100)` | Yes |
| `created_at_utc` | `TIMESTAMP` | Yes |
| `business_date` | `DATE` | Yes |
| `tenant_key` | `BIGINT` | Yes |
| `tenant_name` | `VARCHAR(255)` | Yes |
| `currency_code` | `VARCHAR(10)` | Yes |
| `order_status_code` | `VARCHAR(100)` | Yes |
| `_etl_batch_id` | `VARCHAR(256)` | Yes |
| `_etl_source_type` | `VARCHAR(100)` | Yes |
| `_etl_ingested_at` | `TIMESTAMP` | No |

## `curated.employees`

Canonical employee dimension for workforce, order attribution, and labor analysis.

| Column | Redshift type | Nullable |
|---|---|---|
| `employee_id` | `BIGINT` | No |
| `display_name` | `VARCHAR(1024)` | Yes |
| `first_name` | `VARCHAR(1024)` | Yes |
| `last_name` | `VARCHAR(1024)` | Yes |
| `email_address` | `VARCHAR(1024)` | Yes |
| `cell_phone` | `VARCHAR(256)` | Yes |
| `home_location_id` | `VARCHAR(256)` | Yes |
| `hire_date` | `TIMESTAMP` | Yes |
| `termination_date` | `TIMESTAMP` | Yes |
| `terminated` | `BOOLEAN` | Yes |
| `is_salaried` | `BOOLEAN` | Yes |
| `is_exempt` | `BOOLEAN` | Yes |
| `payroll_id` | `VARCHAR(256)` | Yes |
| `employee_unique_id` | `VARCHAR(256)` | Yes |
| `snapshot_date` | `DATE` | Yes |
| `source_system` | `VARCHAR(100)` | Yes |
| `source_employee_id` | `VARCHAR(199)` | Yes |
| `employee_key` | `BIGINT` | Yes |
| `tenant_key` | `BIGINT` | Yes |
| `tenant_name` | `VARCHAR(255)` | Yes |
| `currency_code` | `VARCHAR(10)` | Yes |
| `is_active` | `BOOLEAN` | Yes |
| `effective_from_utc` | `TIMESTAMP` | Yes |
| `effective_to_utc` | `TIMESTAMP` | Yes |
| `is_current` | `BOOLEAN` | Yes |
| `_etl_batch_id` | `VARCHAR(256)` | Yes |
| `_etl_source_type` | `VARCHAR(100)` | Yes |
| `_etl_ingested_at` | `TIMESTAMP` | No |

## `curated.pos_jobs`

Canonical POS job and employee-role dimension.

| Column | Redshift type | Nullable |
|---|---|---|
| `job_id` | `BIGINT` | No |
| `name` | `VARCHAR(1024)` | Yes |
| `job_unique_id` | `VARCHAR(256)` | Yes |
| `export_code` | `VARCHAR(256)` | Yes |
| `is_bartender` | `BOOLEAN` | Yes |
| `is_delivery_driver` | `BOOLEAN` | Yes |
| `is_delivery_dispatcher` | `BOOLEAN` | Yes |
| `is_hostess` | `BOOLEAN` | Yes |
| `training` | `BOOLEAN` | Yes |
| `location_type` | `VARCHAR(128)` | Yes |
| `snapshot_date` | `DATE` | Yes |
| `source_system` | `VARCHAR(100)` | Yes |
| `source_job_id` | `VARCHAR(199)` | Yes |
| `job_key` | `BIGINT` | Yes |
| `tenant_key` | `BIGINT` | Yes |
| `tenant_name` | `VARCHAR(255)` | Yes |
| `currency_code` | `VARCHAR(10)` | Yes |
| `is_active` | `BOOLEAN` | Yes |
| `effective_from_utc` | `TIMESTAMP` | Yes |
| `effective_to_utc` | `TIMESTAMP` | Yes |
| `is_current` | `BOOLEAN` | Yes |
| `_etl_batch_id` | `VARCHAR(256)` | Yes |
| `_etl_source_type` | `VARCHAR(100)` | Yes |
| `_etl_ingested_at` | `TIMESTAMP` | No |

## `curated.labor_shifts`

Canonical employee shifts for worked-time, overtime, pay-rate, tip, and break analysis.

| Column | Redshift type | Nullable |
|---|---|---|
| `shift_id` | `VARCHAR(36)` | No |
| `employee_id` | `BIGINT` | Yes |
| `job_id` | `BIGINT` | Yes |
| `business_date` | `DATE` | Yes |
| `start_time` | `TIMESTAMPTZ` | Yes |
| `end_time` | `TIMESTAMPTZ` | Yes |
| `minutes_worked` | `INTEGER` | Yes |
| `regular_minutes_worked` | `INTEGER` | Yes |
| `overtime_minutes_worked` | `INTEGER` | Yes |
| `extended_minutes_worked` | `INTEGER` | Yes |
| `pay_rate` | `DECIMAL(18,4)` | Yes |
| `declared_tips` | `DECIMAL(18,4)` | Yes |
| `shift_number` | `SMALLINT` | Yes |
| `break_count` | `INTEGER` | Yes |
| `total_break_minutes` | `INTEGER` | Yes |
| `source_system` | `VARCHAR(100)` | Yes |
| `source_shift_id` | `VARCHAR(199)` | Yes |
| `labor_shift_key` | `BIGINT` | Yes |
| `tenant_key` | `BIGINT` | Yes |
| `tenant_name` | `VARCHAR(255)` | Yes |
| `currency_code` | `VARCHAR(10)` | Yes |
| `_etl_batch_id` | `VARCHAR(256)` | Yes |
| `_etl_source_type` | `VARCHAR(100)` | Yes |
| `_etl_ingested_at` | `TIMESTAMP` | No |

## `curated.pos_destinations`

Canonical order destination and service-channel dimension, including delivery behavior.

| Column | Redshift type | Nullable |
|---|---|---|
| `destination_id` | `BIGINT` | No |
| `name` | `VARCHAR(1024)` | Yes |
| `indicator` | `VARCHAR(256)` | Yes |
| `description` | `VARCHAR(65535)` | Yes |
| `is_active` | `BOOLEAN` | Yes |
| `is_delivery` | `BOOLEAN` | Yes |
| `delivery_minutes` | `INTEGER` | Yes |
| `snapshot_date` | `DATE` | Yes |
| `source_system` | `VARCHAR(100)` | Yes |
| `source_destination_id` | `VARCHAR(199)` | Yes |
| `destination_key` | `BIGINT` | Yes |
| `tenant_key` | `BIGINT` | Yes |
| `tenant_name` | `VARCHAR(255)` | Yes |
| `currency_code` | `VARCHAR(10)` | Yes |
| `effective_from_utc` | `TIMESTAMP` | Yes |
| `effective_to_utc` | `TIMESTAMP` | Yes |
| `is_current` | `BOOLEAN` | Yes |
| `_etl_batch_id` | `VARCHAR(256)` | Yes |
| `_etl_source_type` | `VARCHAR(100)` | Yes |
| `_etl_ingested_at` | `TIMESTAMP` | No |

## ETL Audit Columns

Every curated table contains the following common fields:

| Column | Purpose |
|---|---|
| `_etl_batch_id` | Identifies the ingestion batch that produced the row. |
| `_etl_source_type` | Identifies the source adapter or ingestion type. |
| `_etl_ingested_at` | Records when the row was written to the curated warehouse layer. |