---
id: 8f2c6a14-1b4e-4f7a-9d8c-5e7a2c3b6d91
name: Forecast menu item demand and inventory ordering
kind: InventoryForecast
cron: 0 45 6 * * *
analysis_window: 90d
comparison_periods: [7d, 28d]
---

Forecast **menu item demand by day or service period** and produce purchasing guidance only when supporting inventory data exists in the visible curated schema.

The process should analyze historical sales patterns and translate forecasted menu item demand into recommendations only when inventory or recipe mapping data is actually available.

The output must contain **ordering recommendations and alerts only**, without explanations or descriptions of methodology.

## Known Tables & Columns

### `curated.pos_order_items`
- `order_id` (varchar) - parent order identifier
- `item_id` (int) - menu item identifier
- `item_name` (varchar) - item name as sold
- `quantity` (int, nullable) - quantity sold
- `unit_price` (decimal, nullable) - selling price per unit
- `category_id` (varchar, nullable) - category identifier when available
- `category_name` (varchar, nullable) - category name when available
- `business_date` (date, nullable) - business date for aggregation
- `created_at_utc` (timestamp, nullable) - order item timestamp when available

### `curated.pos_orders`
- `order_id` (varchar) - order identifier
- `order_type` (varchar, nullable) - dine-in / takeout / delivery style channel
- `total` (decimal, nullable) - order total
- `status` (varchar, nullable) - order status
- `destination` (varchar, nullable) - service destination
- `created_at` (timestamp, nullable) - order creation time
- `business_date` (date, nullable) - business date
- `service_channel` (varchar, nullable) - sales/service channel when available
- `fulfillment_channel` (varchar, nullable) - fulfillment mode when available

### `curated.pos_menu_items`
- `item_id` (int) - menu item identifier
- `item_name` (varchar) - canonical item name
- `price` (decimal) - configured menu price
- `cost` (decimal, nullable) - configured item cost when available
- `category_id` (int) - menu category identifier
- `category_name` (varchar, nullable) - category name when available
- `is_active` (boolean, nullable) - whether item is active
- `availability_status` (varchar, nullable) - active/inactive style availability
- `snapshot_date` (date, nullable) - menu snapshot date

## Analysis Approach

### Stage 1 — Historical Demand Extraction
Extract historical sales data from the last **90 days** using `curated.pos_order_items`, joined to `curated.pos_orders` when order-level context is needed.

Aggregate sales by:
- item
- day of week
- service period or time window if available.

Identify recurring demand patterns such as:
- weekday vs weekend demand differences
- peak service periods
- frequently ordered menu items.

### Stage 2 — Menu Item Demand Forecast
Estimate **expected sales for each menu item** for upcoming days or service periods based on historical demand patterns.

Adjust forecasts using contextual signals such as:
- seasonal demand variations
- upcoming promotions
- scheduled events or high-traffic periods.

### Stage 3 — Availability Check For Inventory Guidance
Before giving purchasing or ingredient recommendations, verify that the visible curated schema actually contains inventory or recipe-mapping data required for that step.

If no such table exists, state clearly that current curated Redshift schema supports **menu-item demand forecasting only** and do not invent inventory-level outputs.

### Stage 4 — Shortage Risk Detection
If inventory data is available in the visible schema, compare forecasted demand against that data.

If inventory data is not available, limit the result to **demand forecast risk signals** such as fast-rising menu items, peak periods, and categories likely to need attention.

### Stage 5 — Ordering Optimization
Only provide ordering guidance when the required inventory fields are visible in the schema.

Do **not** invent inventory tables, bulk pricing tiers, supplier matrices, or recipe relationships unless those fields are actually present in the visible Redshift schema.

Determine optimal order quantities by considering:
- forecasted menu-item demand
- safety stock buffers
- delivery schedules
- bulk purchase discounts.

### Stage 6 — Smart Ordering Adjustments
Enhance ordering recommendations by:
- aligning recommendations with visible service-period demand spikes
- highlighting items or categories needing review when supporting inventory data is missing
- suggesting follow-up data collection when key inventory inputs are unavailable.

## Output Constraints

- Use historical data from the **last 90 days** only.
- Prefer aggregated summaries.
- Avoid requesting more than **3 database queries** unless necessary.
- Return **ordering recommendations and alerts only**.
- **Do not include explanations, reasoning, or methodology**.

## Output Format

Return structured ordering recommendations and alerts.