Repository Docs & Knowledge Base

Consolidated Spensara documentation repository (722 Markdown & architecture documents in ../repository)

Built Word Documents (.docx): Spensara Overview (.docx) Capability Assessment (.docx) Brink POS Ingestion Analysis (.docx) The Borg Ingestion Architecture (.docx)

Branches (328)

Branch-specific documentation across extract-raw-xml, feature-fleet-import, kds_cdc_improvments, life-cycle, and tenat-admin.

branches/kds_cdc_improvments/monitor-specs/backend/Kds.Ai.Data/MonitorSpecs/inventory-levels.md 4,155 bytes · Updated Oct 23, 2026 10:44 AM EDT

Raw File

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.