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/implementation-specs/specs/MonitorSpecsCuratedRedshiftRewrite/TASK.md 9,117 bytes · Updated Oct 23, 2026 10:44 AM EDT

Raw File

Monitor Specs Curated Redshift Rewrite - Implementation Tasks

Overview

Implementation of the Monitor Specs Curated Redshift Rewrite delivery slice.

This task plan addresses the remaining soft blockers in monitor-spec migration after the runtime Redshift read path was switched to tenant curated tables.

This slice rewrites legacy POS, customer, pricing, and forecast monitor specs that still reference pre-Redshift or legacy-source table shapes such as:

curated.pos_transactions
curated.pos_transaction_items
curated.pos_transaction_tenders
curated.pos_hourly_sales
curated.pos_catalog_items
curated.loyalty_customers
curated.pos_tables

The slice outcome is:

rewriteable monitor specs migrated to current bootstrap-backed curated tables,
stale table and column references removed,
analysis logic aligned to current POS/customer/menu surfaces,
unsupported inventory-style outputs explicitly guarded or deferred,
static validation that migrated specs no longer reference unsupported legacy tables.

Source Inputs

../../backend/Kds.Ai.Data/MonitorSpecs/
../../backend/Kds.Ai.Warehouse/Sql/Bootstrap/Tenant/Curated/0609_create_curated_customers.sql
../../backend/Kds.Ai.Warehouse/Sql/Bootstrap/Tenant/Curated/0609_create_curated_pos_menu_items.sql
../../backend/Kds.Ai.Warehouse/Sql/Bootstrap/Tenant/Curated/0611_create_curated_pos_order_items.sql
../../backend/Kds.Ai.Warehouse/Sql/Bootstrap/Tenant/Curated/0612_create_curated_pos_orders.sql
../../backend/Kds.Ai.Warehouse/Sql/Bootstrap/Tenant/Curated/0619_create_curated_employees.sql
../../backend/Kds.Ai.Warehouse/Sql/Bootstrap/Tenant/Curated/0620_create_curated_pos_jobs.sql
../../backend/Kds.Ai.Warehouse/Sql/Bootstrap/Tenant/Curated/0621_create_curated_labor_shifts.sql
../../backend/Kds.Ai.Warehouse/Sql/Bootstrap/Tenant/Curated/0622_create_curated_pos_destinations.sql

Spec Name

This implementation spec should be referred to as Monitor Specs Curated Redshift Rewrite.

Fixed Decisions

The current source of truth for supported monitor query surfaces is the bootstrap SQL under backend/Kds.Ai.Warehouse/Sql/Bootstrap/Tenant/Curated/.
Rewritten specs may reference only currently bootstrapped curated tables unless they explicitly instruct runtime schema discovery for optional enrichment.
The primary POS/customer/menu surfaces for rewritten specs are:
curated.pos_orders
curated.pos_order_items
curated.pos_menu_items
curated.customers
Additional current bootstrap tables may be used only where they materially support the monitor goal:
curated.employees
curated.pos_jobs
curated.labor_shifts
curated.pos_destinations
Specs must not claim that unsupported bootstrap tables currently exist.
Specs must not invent joins to loyalty_customers, pos_tables, pos_transaction_tenders, pos_hourly_sales, pos_transactions, pos_transaction_items, pos_catalog_items, or pos_modifiers.
Inventory-style monitors blocked by missing stock, recipe, supplier, or waste tables remain out of scope for this slice.
The slice edits monitor-spec content only; it does not add new Redshift tables, runtime tool behavior, or ETL contracts.

Scope

This task includes rewriting the legacy-spec group that is supportable from the current bootstrap-backed schema:

backend/Kds.Ai.Data/MonitorSpecs/customer-churn-risk.md
backend/Kds.Ai.Data/MonitorSpecs/customer-profiles.md
backend/Kds.Ai.Data/MonitorSpecs/daily-forecast.md
backend/Kds.Ai.Data/MonitorSpecs/demand-drivers.md
backend/Kds.Ai.Data/MonitorSpecs/identify-high-value-customers.md
backend/Kds.Ai.Data/MonitorSpecs/identify-plowhorse-items.md
backend/Kds.Ai.Data/MonitorSpecs/identify-star-items.md
backend/Kds.Ai.Data/MonitorSpecs/incorporate-seasonality.md
backend/Kds.Ai.Data/MonitorSpecs/menu-item-profitability.md
backend/Kds.Ai.Data/MonitorSpecs/monthly-forecast.md
backend/Kds.Ai.Data/MonitorSpecs/optimal-pricing-adjustments.md
backend/Kds.Ai.Data/MonitorSpecs/pricing-recommendations.md
backend/Kds.Ai.Data/MonitorSpecs/seasonality-trends.md
backend/Kds.Ai.Data/MonitorSpecs/segment-customers.md
backend/Kds.Ai.Data/MonitorSpecs/segment-descriptions.md
backend/Kds.Ai.Data/MonitorSpecs/weekly-forecast.md

In short, this slice ends when those specs no longer depend on non-bootstrapped legacy tables and their instructions match the current Redshift curated schema.

Out of Scope

The following items are explicitly excluded from this task:

adding new Redshift tables or views,
changing ETL pipelines or curated warehouse bootstrap DDL,
rewriting already-migrated KDS anomaly specs unless needed for consistency,
runtime tenant routing,
warehouse query tool implementation,
anomaly detector orchestration,
inventory or ingredient monitor enablement requiring new curated tables,
pricing elasticity models that require historical list-price snapshots absent from current schema,
loyalty-program integration not present in current bootstrap surfaces.

Task Checklist

Phase 1: Forecast Spec Rewrite

[ ] TASK 1.1 - Rewrite hourly, weekly, and monthly forecast specs to current pos_orders / pos_order_items
[ ] TASK 1.2 - Rewrite seasonality and demand-driver specs to current POS/menu surfaces

[ ] TASK 2.1 - Rewrite profitability and pricing recommendation specs to current sold-price and menu-cost surfaces
[ ] TASK 2.2 - Rewrite star/plowhorse menu-engineering specs to current POS/menu surfaces

Phase 3: Customer Spec Rewrite

[ ] TASK 3.1 - Rewrite churn and high-value customer specs to current customer/order surfaces
[ ] TASK 3.2 - Rewrite customer profile and segmentation specs to current customer/order/menu surfaces

Phase 4: Validation

[ ] TASK 4.1 - Add and run static validation for unsupported legacy monitor-table references

Dependency Graph

Prerequisites
    |-- Redshift Foundation complete
    |   +-- current curated bootstrap SQL exists
    |-- Single-Store Redshift Runtime Read Path complete
    |   +-- anomaly detector reads tenant Redshift curated
    +-- KDS monitor migration may already be partially complete

Phase 1 (Forecast Spec Rewrite)
    |-- 1.1 Forecast specs on pos_orders / pos_order_items
    +-- 1.2 Seasonality and demand-driver specs <-- (depends on 1.1 patterns)

Phase 2 (Pricing and Menu Spec Rewrite)
    |-- 2.1 Profitability and pricing recommendation specs
    +-- 2.2 Star/plowhorse specs <-- (depends on 2.1 pricing/cost conventions)

Phase 3 (Customer Spec Rewrite)
    |-- 3.1 Churn and high-value customer specs
    +-- 3.2 Customer profiles and segmentation specs <-- (depends on 3.1 customer identity conventions)

Phase 4 (Validation)
    +-- 4.1 Static unsupported-reference validation <-- (depends on Phase 1, Phase 2, Phase 3)

Recommended Session Order

Session	Tasks	Description
1	1.1	Rewrite the direct forecast specs to `pos_orders`-based revenue patterns
2	1.2, 2.1	Rewrite seasonality, demand-driver, profitability, and pricing specs
3	2.2, 3.1	Rewrite menu-engineering and simpler customer specs
4	3.2	Rewrite profile, segmentation, and segment-description specs
5	4.1	Run static validation and clean remaining unsupported references

Key Design Decisions

Bootstrap SQL Is The Truth

When a monitor spec conflicts with warehouse models, older docs, or prior assumptions, the bootstrap SQL files under Sql/Bootstrap/Tenant/Curated/ win.

Rewrite To Sold Facts

For forecast, pricing, and menu-engineering monitors, the main behavioral facts come from:

curated.pos_orders for order timing, totals, service channel, and customer linkage,
curated.pos_order_items for sold items, quantities, and sold unit prices,
curated.pos_menu_items for menu metadata and current configured item cost.

Customer Identity Must Stay Conservative

Customer monitors may use curated.pos_orders.customer_number and enrich from curated.customers only when the join path is explicit and confirmed. Specs must avoid pretending loyalty or tender tables exist.

No Invented Historical Surfaces

If the current schema lacks hourly aggregates, tender breakdowns, price history, modifier pricing, party-size tables, or loyalty tables, the rewritten specs must narrow their requested analysis instead of hallucinating those sources.

Inventory Monitors Stay Deferred

Monitors whose core purpose depends on inventory stock levels, supplier data, recipe mappings, or waste capture remain blocked until those tables are added to the curated bootstrap contract.

Validation Must Be Static And Cheap

This slice should end with a simple blocked-reference validation pass proving the rewritten specs no longer mention unsupported legacy tables.