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/forecast-demand.md 4,986 bytes · Updated Oct 23, 2026 10:44 AM EDT

Raw File

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.

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.