---
id: c4f9b2e7-3d1a-4a8f-b6c2-9e5d7a1f4c83
name: Monitor inventory levels and low stock alerts
kind: InventoryMonitoring
cron: 0 */15 * * * *
analysis_window: 90d
comparison_periods: [7d, 28d]
---

Monitor inventory risk and generate low-stock style alerts only when current curated schema exposes real inventory tables.

The process should analyze recent consumption patterns and compare forecasted demand with available stock only when that stock data exists in the visible curated schema.

The output must contain **low-stock alerts and reorder recommendations 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
- `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) - channel/type
- `destination` (varchar, nullable) - service destination

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

## Analysis Approach

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

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

Verify first whether current curated schema contains inventory stock tables.

If inventory stock data is absent, say so clearly and continue with menu-item demand analysis only. Do not fabricate low-stock alerts from sales data alone.

Identify high-usage menu items and recurring demand patterns.

### Stage 2 — Consumption Forecast
Estimate **near-term menu-item demand** based on recent demand patterns.

Adjust forecasts using contextual signals such as:
- seasonal variations
- promotions
- scheduled events or traffic spikes.

### Stage 3 — Inventory Level Evaluation
Compare forecasted demand with **current or expected stock levels** only when inventory tables are actually visible in schema discovery.

### Stage 4 — Low Stock Risk Detection
Determine whether ingredient inventory will fall below **safe operating levels** by evaluating:
- forecasted consumption
- remaining stock
- safety stock thresholds.

If stock tables are not available, do not estimate remaining stock. Instead report that low-stock detection is blocked by missing curated inventory data.

### Stage 5 — Procurement Context Evaluation
Use only fields actually visible in the current curated schema.

Evaluate whether:
- restocking should occur immediately
- restocking can be combined with upcoming orders
- available stock buffers justify larger or smaller replenishment quantities.

Do **not** assume quantity-tier pricing tables or inventory tables exist unless they are visible in the Redshift schema.

### Stage 6 — Alert Generation
Generate structured alerts for ingredients approaching **low stock conditions**.

Alerts should include:
- estimated time until stockout
- recommended reorder quantity
- urgency level.

## Output Constraints

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

## Output Format

Return structured inventory alerts.