---
id: 6e3f1c94-8a27-4b6d-9f52-1d8c7e3b5a41
name: Track waste and adjust forecasts
kind: WasteOptimization
cron: 0 55 6 * * *
analysis_window: 90d
comparison_periods: [7d, 28d]
---

Track waste-like demand and overproduction patterns using current curated schema, and adjust forecasts only when supporting stock data exists.

The process should analyze historical waste patterns and incorporate them into forecast and ordering adjustments only when visible curated tables support that conclusion.

The output must contain **forecast adjustments, waste alerts, and ordering adjustments 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) - sold item name
- `quantity` (int, nullable) - quantity sold
- `unit_price` (decimal, nullable) - selling price
- `category_name` (varchar, nullable) - category name when available
- `business_date` (date, nullable) - business date
- `created_at_utc` (timestamp, nullable) - sale timestamp when available

### `curated.pos_orders`
- `order_id` (varchar) - order identifier
- `created_at` (timestamp, nullable) - order time
- `business_date` (date, nullable) - business date
- `order_type` (varchar, nullable) - sales channel/type
- `destination` (varchar, nullable) - service destination

### `curated.pos_menu_items`
- `item_id` (int) - menu item identifier
- `item_name` (varchar) - canonical item name
- `cost` (decimal, nullable) - configured item cost when available
- `category_name` (varchar, nullable) - menu category
- `availability_status` (varchar, nullable) - active/inactive style flag

## Analysis Approach

### Stage 1 — Historical Waste Extraction
Extract waste-related signals from the last **90 days** using demand and sell-through patterns from `curated.pos_order_items`, `curated.pos_orders`, and `curated.pos_menu_items`.

If current curated schema also contains stock tables, you may include overstock signals from those tables. Otherwise keep analysis at menu-item demand level.

Identify menu items frequently associated with:
- spoilage
- unused stock
- overproduction.

### Stage 2 — Waste Pattern Analysis
Aggregate waste occurrences by:
- menu item
- menu item
- day of week
- service period if available.

Identify recurring patterns such as:
- over-preparation during specific days
- menu items with recurring low sell-through after forecasted demand
- waste spikes after certain delivery cycles.

### Stage 3 — Demand vs Waste Correlation
Compare:
- historical demand from `curated.pos_order_items`
- menu metadata from `curated.pos_menu_items`.

If stock or recipe tables are not available in visible curated Redshift tables, say so clearly and keep analysis at menu-item level.

Detect discrepancies where predicted consumption significantly exceeds actual usage.

### Stage 4 — Forecast Adjustment
Adjust demand forecasts by incorporating detected waste patterns.

Reduce projected usage for ingredients or menu items that consistently generate excess inventory while preserving sufficient stock for high-demand periods.

### Stage 5 — Ordering Strategy Adjustment
Modify purchasing recommendations based on waste insights by:
- reducing focus on menu items with repeated low sell-through
- adjusting safety stock thresholds
- modifying delivery schedules where waste occurs after deliveries.

Consider only fields actually visible in current curated schema.

Do **not** assume quantity-tier pricing tables, stock tables, or supplier tables exist unless they are present in the Redshift schema.

### Stage 6 — Continuous Waste Monitoring
Generate structured insights highlighting:
- menu items with persistent waste-like patterns
- menu items contributing to overproduction
- opportunities to improve ordering or preparation practices.

## Output Constraints

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

## Output Format

Return structured waste insights and forecast adjustments.