Repository Docs & Knowledge Base

id: 019cb9fd-14a0-7ee4-a201-76118a743fd7 name: Item Recall Storm Monitor kind: Anomaly cron: 0 0 * * * * analysis_window: 1h comparison_periods: [1d, 7d]

Detect an unusual spike in item recalls -- completed items being sent back to the kitchen for rework, indicating quality issues or operational breakdown.

Known Tables & Columns

curated.kitchen_orders

• guid (varchar, PK) - order identifier
• store_guid (varchar) - which store/restaurant
• done (tinyint) - whether the order is complete
• is_deleted (tinyint) - soft delete flag
• create_time (int, unix timestamp) - when the order was created

curated.kitchen_order_items

• guid (varchar, PK) - item identifier
• order_guid (varchar, FK) - parent order
• name (varchar) - menu item name
• category (varchar) - Grill, Fryer, Salads, Drinks, Desserts
• device_id (int) - KDS station ID (1=Grill, 2=Fryer, 3=Salad, 4=Drinks, 5=Desserts)
• recall_time (int, unix timestamp, nullable) - when the item was recalled (NULL or 0 if never recalled)
• is_deleted (tinyint) - soft delete flag
• create_time (int, unix timestamp) - when the item was created
• update_time (int, unix timestamp) - last update

curated.kitchen_item_status_events

• guid (varchar, PK) - bump identifier
• item_guid (varchar, FK) - parent item
• status (tinyint) - 0=pending, 1=prepared, 2=done
• last_status (tinyint, nullable) - previous status before current change
• prepared_local_time (int, unix timestamp, nullable) - when item was prepared
• done_local_time (int, unix timestamp, nullable) - when item was completed (reset to 0 on recall)
• is_deleted (tinyint) - soft delete flag

Analysis Approach

1. Query items from the last {AnalysisWindow} in curated.kitchen_order_items (is_deleted=0) where recall_time IS NOT NULL AND recall_time > 0.
2. Also query total items created in the same window. Verify at least 15 items exist in the window. If not, call emit_no_anomaly — the sample is too small.
3. Count recalled items per 15-minute window and compute the recall rate: recalled items / total items created in that window.
4. Compare against the historical baseline. Under normal operations, recalls should be rare -- typically <2% of items.
5. Flag windows where the recall rate exceeds 10%, sustained over 2+ consecutive windows.
6. Join with curated.kitchen_item_status_events to identify items that were previously at status 2 (done) and are now back at status 1 (prepared) -- this confirms the recall pattern.
7. Optionally break down by device_id (station) or category to find if recalls cluster on specific stations or menu items, which could indicate a quality issue with specific food types.

Baseline Reference

• Normal recall rate: <2% of completed items
• Suspicious: 5-10% recall rate
• Anomalous: >10% recall rate, especially >20% (1 in 5 items being sent back)

Severity Guidance

• Low: 10-20% recall rate sustained for 2+ consecutive windows, possible quality issue
• Medium: 20-30% recall rate sustained over multiple windows, systematic quality problem
• High: >30% recall rate -- kitchen is in a recall storm, completed items are being continuously sent back for rework