FSD: Multi-Scenario Decision Intelligence Orchestration Engine

Milestone: M60 (Constraint Intelligence Engine V2) Status: APPROVED - Critical amendments integrated Last Updated: November 5, 2025 (AMENDED) Next Phase: Implementation Planning

⚠️ CRITICAL AMENDMENTS APPLIED: See fsd-m60-critical-amendments.md for detailed fixes to:

Scenario dependencies & sequential execution
CSV entity resolution
Incremental execution modes
Meta-prompting service integration
Financial data architecture
Adaptive event detection
Cache invalidation matrix
Approval workflows
Subscription management
Background worker implementation
Operational recommendations

Executive Summary
Section 1: Product Architecture Overview
Section 2: Data Model & Schema
Section 3: Orchestration Architecture
Section 4: Scenario Execution Engine (Planning + Control)
Section 5: API Contracts & User Experience
Section 6: Performance & Cost Optimization
Appendix A: Decision Log

EXECUTIVE SUMMARY

ChainAlign evolves from a single-use S&OP platform into a multi-scenario decision intelligence platform where different business contexts (S&OP, Finance/CFO, Capital Allocation) run as distinct, parallel decision scenarios with subscription-based access.

Key Product Changes

Aspect	Current State	New State
User Entry Point	Dashboard → single S&OP view	Scenario Selector → chosen context
Data Integration	Manual + API connectors	CSV bootstrap (Day 1-5) → API progressive (Day 5+)
Execution Model	Monthly cycles	Continuous + Event-triggered cycles
Caching Strategy	Real-time queries	Pre-computed results (cache on data change)
Feature Exposure	Fixed features	Tiered subscriptions (Tier A/B/C)

Three Subscription Tiers (MVP)

TIER A: S&OP Decision Intelligence - Operations leaders

Scenarios: [sop]
Data: Sales history, inventory, production (12mo+ data)
Pricing: Base tier

TIER B: CFO Financial Intelligence - Finance leaders

Scenarios: [sop, cfo_finance] (includes TIER A)
Data: Income statement, expenses, cash flow (24mo+ data)
Pricing: 1.5-2x TIER A

TIER C: Capital Allocation Intelligence - Strategic planning

Scenarios: [sop, cfo_finance, capex] (includes TIER A + B)
Data: CapEx proposals, asset utilization, depreciation
Pricing: Premium tier

SECTION 1: PRODUCT ARCHITECTURE OVERVIEW

1.1 System Context Diagram

┌─────────────────────────────────────────────────────────────────┐
│                        CHAINALIGN PLATFORM                      │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  ┌──────────────────┐       ┌──────────────────┐             │
│  │  Frontend (React)│       │ Auth & Tenant    │             │
│  │ Scenario Selector├──────>│ Management       │             │
│  │ Results Display  │       │ (Firebase JWT)   │             │
│  └──────────────────┘       └──────────────────┘             │
│           ▲                                                    │
│           │ Real-time updates (WebSocket)                     │
│           │ Pre-computed results (REST)                       │
│           │                                                    │
│  ┌────────┴─────────────────────────────────────┐             │
│  │      ORCHESTRATION LAYER (Node.js)           │             │
│  ├──────────────────────────────────────────────┤             │
│  │ • Event Detection & Cycle Management          │             │
│  │ • Scenario Router (Tenant → Subscribed)      │             │
│  │ • Result Caching (Event-based invalidation)  │             │
│  │ • Background Job Dispatch (pgmq)             │             │
│  └────────┬─────────────────────────────────────┘             │
│           │                                                    │
│  ┌────────┴─────────────────────────────────────┐             │
│  │   ASYNC EXECUTION (Background Workers)       │             │
│  ├──────────────────────────────────────────────┤             │
│  │ ┌────────────────┐ ┌─────────────────────┐  │             │
│  │ │  SOP Scenario  │ │  CFO Finance        │  │             │
│  │ │  Executor      │ │  Executor           │  │             │
│  │ └──┬─────────────┘ └────────┬────────────┘  │             │
│  │    │                        │               │             │
│  │ ┌──┴───────────────────────┴────────────┐   │             │
│  │ │  Planning Agent (Hybrid Prompting)    │   │             │
│  │ │  + Meta-Prompt Refinement             │   │             │
│  │ └──┬──────────────────────────────────┬─┘   │             │
│  │    │ (generate draft plans/analyses)  │     │             │
│  │ ┌──┴──────────────────────────────────┴──┐  │             │
│  │ │  Control Agent (Constraint Checking)   │  │             │
│  │ │  Tiered Severity Validation            │  │             │
│  │ └──────────────────────────────────────┬─┘  │             │
│  │                                         │    │             │
│  │    ┌─────────────────────────────────┘     │             │
│  │    │ (approve/flag/reject)                 │             │
│  │ ┌──▼──────────────────────────────┐        │             │
│  │ │ CapEx Scenario Executor         │        │             │
│  │ │ (Decision agents TBD)           │        │             │
│  │ └──┬───────────────────────────────┘       │             │
│  │    │                                        │             │
│  └────┼────────────────────────────────────────┘             │
│       │                                                       │
│  ┌────▼────────────────────────────────────────┐             │
│  │  PERSISTENCE LAYER                          │             │
│  ├──────────────────────────────────────────────┤             │
│  │ PostgreSQL (multi-tenant data + results)    │             │
│  │ - sop_plan_data (existing)                  │             │
│  │ - financial_summary (new)                   │             │
│  │ - operating_expense_actuals (new)           │             │
│  │ - capex_proposals (new)                     │             │
│  │ - asset_inventory (new)                     │             │
│  │ - scenario_execution_results (new)          │             │
│  │ - scenario_cycle_management (enhanced)      │             │
│  │ pgmq (message queue for job dispatch)       │             │
│  └──────────────────────────────────────────────┘             │
│       │                                                       │
│       ▼                                                       │
│  ┌──────────────────────────────────────────────┐             │
│  │  EXTERNAL INTEGRATIONS                       │             │
│  ├──────────────────────────────────────────────┤             │
│  │ • CSV Upload (enhanced validation per tier) │             │
│  │ • ERP/CRM APIs (progressive, post-Day 5)    │             │
│  │ • Weather Service (context enrichment)      │             │
│  │ • News Service (market context)             │             │
│  │ • Meta-Prompting Service (port 5002)        │             │
│  │ • Gemini API (LLM backbone)                 │             │
│  └──────────────────────────────────────────────┘             │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

1.2 Component Responsibilities

Orchestration Layer (Node.js Backend)

Event Detection: Monitors data changes (CSV uploads, API syncs, manual inputs)
Cycle Management: Creates/closes cycles based on events or schedules
Scenario Routing: Maps tenant subscriptions to executable scenarios
Result Caching: Pre-computes and caches scenario results; invalidates on data changes
Job Dispatch: Enqueues async jobs to background workers via pgmq

Async Execution Layer (Background Workers)

Scenario Executors: Independent workers per scenario type (SOP, CFO, CapEx)
Planning Phase: Generates draft plans/analyses using LLM agents with hybrid prompting
Control Phase: Validates results against constraints with tiered severity checks
Feedback Loop: Learning from execution outcomes to improve future prompts

Persistence Layer (PostgreSQL)

Operational Data: sop_plan_data (existing product data)
Financial Data: financial_summary, operating_expense_actuals (new)
Capital Data: capex_proposals, asset_inventory (new)
Execution Data: scenario_execution_results, scenario_cycle_management (new)
Message Queue: pgmq for reliable job dispatch

1.3 Multi-Tenancy Model

Every component respects tenant isolation:

All queries scoped by tenant_id
Subscriptions tied to tenant (not user)
Results cached per tenant per scenario
Event detection per tenant (isolated data change monitoring)

SECTION 2: DATA MODEL & SCHEMA

2.1 CSV Bootstrap Files per Tier

TIER A: S&OP Decision Intelligence

File: sales_and_operations.csv

Required Columns:
  period (DATE, YYYY-MM-01)
  product_sku (VARCHAR)
  bookings_plan (NUMERIC)
  shipments_actual (NUMERIC)
  inventory_actual (NUMERIC)
  production_actual (NUMERIC)
  revenue_plan (NUMERIC)
  revenue_actual (NUMERIC)

Optional Columns:
  product_name (VARCHAR)
  cost_center (VARCHAR)
  region (VARCHAR)
  segment (VARCHAR)
  capacity_utilized_percent (NUMERIC)

Validation:
  ✓ Minimum 12 months historical data
  ✓ Latest data within 30 days (freshness)
  ✓ All numeric values >= 0
  ✓ No nulls in required columns
  ✓ product_sku must exist in core_entities or be flagged for creation

TIER B: CFO Financial Intelligence

Files: financial_statement.csv + operating_expenses.csv

File 1: financial_statement.csv

Required Columns:
  period (DATE, YYYY-MM-01)
  product_sku (VARCHAR) [optional if company-level summary]
  revenue_plan (NUMERIC)
  revenue_actual (NUMERIC)
  cogs_plan (NUMERIC)
  cogs_actual (NUMERIC)
  gross_margin_plan (NUMERIC)
  gross_margin_actual (NUMERIC)

Optional Columns:
  product_name, gl_account_code, cost_center

Validation:
  ✓ Minimum 24 months historical P&L data
  ✓ cogs_actual <= revenue_actual (logical constraint)
  ✓ gross_margin = (revenue - cogs) / revenue
  ✓ All numeric >= 0 except costs (which are positive)

File 2: operating_expenses.csv

Required Columns:
  period (DATE, YYYY-MM-01)
  expense_category (VARCHAR)
  amount_plan (NUMERIC)
  amount_actual (NUMERIC)

Optional Columns:
  department (VARCHAR)
  cost_center (VARCHAR)
  gl_account_code (VARCHAR)

Validation:
  ✓ expense_category standardized against Chart of Accounts
  ✓ amount_plan, amount_actual > 0
  ✓ Comprehensive coverage (all departments represented)

TIER C: Capital Allocation Intelligence

Files: capex_proposals.csv + asset_utilization.csv

File 1: capex_proposals.csv

Required Columns:
  proposal_id (VARCHAR, unique)
  proposal_name (VARCHAR)
  investment_amount (NUMERIC)
  payback_period_years (NUMERIC)
  roi_target_percent (NUMERIC)
  status (VARCHAR: PROPOSED, APPROVED, IN_PROGRESS, COMPLETED)
  proposal_date (DATE)

Optional Columns:
  department, strategic_alignment_score, risk_level

Validation:
  ✓ investment_amount > 0
  ✓ payback_period_years between 0-30
  ✓ roi_target_percent between -20% and +300%
  ✓ status in allowed values

File 2: asset_utilization.csv

Required Columns:
  asset_id (VARCHAR, unique)
  asset_type (VARCHAR)
  current_utilization_percent (NUMERIC 0-100)
  capacity_units (NUMERIC)
  age_years (NUMERIC)
  depreciation_schedule_years (NUMERIC)

Optional Columns:
  location, acquisition_date, maintenance_cost_annual

Validation:
  ✓ utilization_percent between 0-100
  ✓ capacity_units > 0
  ✓ age_years >= 0
  ✓ depreciation_schedule_years > 0

2.2 New Database Tables

Table: `financial_summary`

CREATE TABLE financial_summary (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  period DATE NOT NULL,

  -- Revenue
  revenue_plan NUMERIC(15,2),
  revenue_actual NUMERIC(15,2),

  -- Cost of Goods Sold
  cogs_plan NUMERIC(15,2),
  cogs_actual NUMERIC(15,2),

  -- Gross Margin
  gross_margin_plan NUMERIC(15,4),  -- stored as ratio (0.40 = 40%)
  gross_margin_actual NUMERIC(15,4),

  -- Metadata
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  data_source VARCHAR(50),  -- 'CSV_UPLOAD', 'API_SYNC', 'MANUAL'

  CONSTRAINT unique_tenant_period UNIQUE(tenant_id, period)
);

CREATE INDEX idx_financial_summary_tenant_period
  ON financial_summary(tenant_id, period DESC);

Table: `operating_expense_actuals`

CREATE TABLE operating_expense_actuals (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  period DATE NOT NULL,

  -- Classification
  expense_category VARCHAR(100) NOT NULL,  -- Standardized enum
  department VARCHAR(100),
  cost_center VARCHAR(100),

  -- Amounts
  amount_plan NUMERIC(15,2) NOT NULL,
  amount_actual NUMERIC(15,2) NOT NULL,

  -- GL Integration
  gl_account_code VARCHAR(20),

  -- Metadata
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  data_source VARCHAR(50),

  CONSTRAINT positive_amounts CHECK(amount_plan > 0 AND amount_actual > 0)
);

CREATE INDEX idx_opex_tenant_period
  ON operating_expense_actuals(tenant_id, period DESC);
CREATE INDEX idx_opex_category
  ON operating_expense_actuals(tenant_id, expense_category);

Table: `capex_proposals`

CREATE TABLE capex_proposals (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,

  -- Proposal Identity
  proposal_id VARCHAR(100) NOT NULL UNIQUE,
  proposal_name VARCHAR(255) NOT NULL,

  -- Financial
  investment_amount NUMERIC(15,2) NOT NULL,
  payback_period_years NUMERIC(5,2) NOT NULL,
  roi_target_percent NUMERIC(7,2) NOT NULL,

  -- Status & Dates
  status VARCHAR(50) NOT NULL
    DEFAULT 'PROPOSED'
    CHECK(status IN ('PROPOSED', 'APPROVED', 'IN_PROGRESS', 'COMPLETED')),
  proposal_date DATE NOT NULL,

  -- Strategic
  department VARCHAR(100),
  strategic_alignment_score NUMERIC(5,2),
  risk_level VARCHAR(50),  -- 'LOW', 'MEDIUM', 'HIGH'

  -- Metadata
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  data_source VARCHAR(50),

  CONSTRAINT positive_investment CHECK(investment_amount > 0),
  CONSTRAINT reasonable_payback CHECK(payback_period_years BETWEEN 0 AND 30),
  CONSTRAINT reasonable_roi CHECK(roi_target_percent BETWEEN -20 AND 300),
  CONSTRAINT unique_tenant_proposal UNIQUE(tenant_id, proposal_id)
);

CREATE INDEX idx_capex_tenant_status
  ON capex_proposals(tenant_id, status);
CREATE INDEX idx_capex_tenant_date
  ON capex_proposals(tenant_id, proposal_date DESC);

Table: `asset_inventory`

CREATE TABLE asset_inventory (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,

  -- Asset Identity
  asset_id VARCHAR(100) NOT NULL UNIQUE,
  asset_type VARCHAR(100) NOT NULL,

  -- Utilization
  current_utilization_percent NUMERIC(5,2) NOT NULL
    CHECK(current_utilization_percent BETWEEN 0 AND 100),
  capacity_units NUMERIC(15,2) NOT NULL,

  -- Lifecycle
  age_years NUMERIC(5,2) NOT NULL CHECK(age_years >= 0),
  depreciation_schedule_years NUMERIC(5,2) NOT NULL CHECK(depreciation_schedule_years > 0),

  -- Location & Maintenance
  location VARCHAR(255),
  maintenance_cost_annual NUMERIC(15,2),

  -- Metadata
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  data_source VARCHAR(50),

  CONSTRAINT unique_tenant_asset UNIQUE(tenant_id, asset_id)
);

CREATE INDEX idx_asset_tenant_type
  ON asset_inventory(tenant_id, asset_type);

Table: `scenario_execution_results`

CREATE TABLE scenario_execution_results (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  sop_cycle_id UUID NOT NULL REFERENCES sop_cycles(id) ON DELETE CASCADE,

  -- Scenario Identity
  scenario_type VARCHAR(50) NOT NULL
    CHECK(scenario_type IN ('sop', 'cfo_finance', 'capex')),

  -- Execution State
  execution_status VARCHAR(50) NOT NULL
    DEFAULT 'PENDING'
    CHECK(execution_status IN ('PENDING', 'RUNNING', 'SUCCESS', 'FAILED', 'PARTIAL')),

  -- Results
  planning_phase_result JSONB,  -- Draft plan/analysis from Planning agent
  control_phase_result JSONB,   -- Validation results from Control agent
  final_result JSONB,            -- Approved/final output

  -- Violation Tracking (Tiered Severity)
  critical_violations JSONB DEFAULT '[]',  -- Hard-reject violations
  medium_violations JSONB DEFAULT '[]',    -- Flag but allow
  low_violations JSONB DEFAULT '[]',       -- Flag and auto-accept

  -- Cache Metadata
  cached_at TIMESTAMP,
  cache_invalidated_at TIMESTAMP,
  cache_valid BOOLEAN DEFAULT TRUE,
  cache_ttl_minutes INTEGER DEFAULT 1440,  -- 24 hours fallback

  -- Execution Tracking
  started_at TIMESTAMP,
  completed_at TIMESTAMP,
  execution_duration_seconds INTEGER,

  -- Metadata
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT unique_cycle_scenario UNIQUE(sop_cycle_id, scenario_type)
);

CREATE INDEX idx_scenario_results_tenant
  ON scenario_execution_results(tenant_id, scenario_type);
CREATE INDEX idx_scenario_results_cache_valid
  ON scenario_execution_results(tenant_id, scenario_type, cache_valid);

Enhanced Table: `sop_cycles`

-- Add these columns to existing sop_cycles table:

ALTER TABLE sop_cycles ADD COLUMN IF NOT EXISTS
  cycle_type VARCHAR(50) DEFAULT 'PERIODIC'
  CHECK(cycle_type IN ('PERIODIC', 'CSV_BOOTSTRAP', 'EVENT_TRIGGERED'));

ALTER TABLE sop_cycles ADD COLUMN IF NOT EXISTS
  triggered_event VARCHAR(255);  -- Description of event that triggered cycle

ALTER TABLE sop_cycles ADD COLUMN IF NOT EXISTS
  event_severity VARCHAR(50)
  CHECK(event_severity IN ('LOW', 'MEDIUM', 'HIGH'));

ALTER TABLE sop_cycles ADD COLUMN IF NOT EXISTS
  triggered_by_change_summary JSONB;  -- {data_type, change_magnitude, threshold_breached}

-- Index for quick lookups
CREATE INDEX IF NOT EXISTS idx_sop_cycles_tenant_type
  ON sop_cycles(tenant_id, cycle_type, status);

2.3 CSV Validation Enhancements

The existing CSV upload pipeline (in csvUploadRoutes.js, UploadAssistantService, etc.) needs tier-specific validation rules.

Enhancement Points:

File Type Detection (existing) → Add tier-specific detection
- Detect sales_and_operations.csv → auto-apply TIER A validation
- Detect financial_statement.csv → auto-apply TIER B validation
- Detect capex_proposals.csv → auto-apply TIER C validation

Schema Validation (new) → Extend chainAlignSchema.js

// Current: schema per company
// New: schema per company + per tier + per scenario

const tierSchemas = {
  TIER_A: {
    required: ['period', 'product_sku', 'bookings_plan', ...],
    validations: {
      period: validateDate,
      product_sku: validateSKUExists,
      bookings_plan: validateNumericPositive,
      ...
    }
  },
  TIER_B: { ... },
  TIER_C: { ... }
}

Error Reporting (enhancement) → Differentiate by severity
- CRITICAL: Mandatory field missing → block import
- WARNING: Data quality issue (e.g., forecast outside normal range) → allow with flag
- INFO: Optional field detected → log only

SECTION 3: ORCHESTRATION ARCHITECTURE

3.1 Data Event Detection

The system monitors for data changes that trigger scenario executions:

Event Sources

CSV Upload → Creates new sop_cycle (CSV_BOOTSTRAP type)
API Sync → Updates appended to current PERIODIC cycle
Manual Data Entry → Updates appended to current cycle
External Market Events → Creates new sop_cycle (EVENT_TRIGGERED type)

Event Detection Flow

Data Arrives (CSV/API/Manual)
  ↓
Compute change magnitude:
  - For numeric fields: |new - old| / old (% change)
  - For categorical: flag if changed
  ↓
Compare against thresholds:
  - Demand/shipments: >20% change threshold
  - Revenue: >15% change threshold
  - Cost: >25% change threshold
  ↓
If threshold breached:
  - Mark as HIGH severity event
  - Close current sop_cycle (status=CLOSED)
  - Create new sop_cycle (cycle_type=EVENT_TRIGGERED)
  ↓
If threshold not breached:
  - Append data to current cycle (continuous)
  - Mark for INCREMENTAL scenario re-execution

3.2 Cycle Lifecycle Management

┌─────────────────────────────────────────────────────────────┐
│                    SOP CYCLE LIFECYCLE                      │
└─────────────────────────────────────────────────────────────┘

SCENARIO 1: Normal Monthly Cycle
───────────────────────────────────────────────────────────────
  Day 0: Month start
    ↓
  Day 1-25: Continuous data flow (ERP sync, manual entries)
    - Appended to ACTIVE cycle
    - Incremental scenario re-execution on material changes
    - Results pre-computed and cached
    ↓
  Day 26-28: Month-end reconciliation
    - Final data lock-in
    - Complete scenario re-run
    - Results cached and locked
    ↓
  Day 1 (next month): Auto-close previous cycle
    - Mark as CLOSED
    - Create new PERIODIC cycle
    - Reset scenario execution counters


SCENARIO 2: Event-Triggered Cycle (Demand Spike)
───────────────────────────────────────────────────────────────
  Any Day: Demand data arrives
    ↓
  Check: bookings_plan change > 20%?
    ├─ NO: Append to current cycle, continue
    └─ YES: Event threshold breached
       ↓
       Mark current cycle: CLOSED
       Create NEW cycle: EVENT_TRIGGERED
       Set triggered_event: "Demand spike >20%"
       Set cycle_type: EVENT_TRIGGERED
       ↓
       Scenario Execution (immediate):
       - Run ALL subscribed scenarios
       - Generate before/after analysis
       - Compare insights with previous cycle
       ↓
       User sees:
       - New scenario results
       - "Event Triggered" badge
       - Historical comparison (old vs new insights)
       ↓
       Cycle remains ACTIVE until:
       - Manual close by user, OR
       - 7 days pass (auto-close with warning)


SCENARIO 3: CSV Bootstrap (New Tenant)
───────────────────────────────────────────────────────────────
  Day 0: Customer uploads sales_and_operations.csv (12mo data)
    ↓
  System creates cycle: CSV_BOOTSTRAP
    - cycle_type = 'CSV_BOOTSTRAP'
    - Validation: 12mo data? Required fields? Quality checks?
    ↓
  If validation PASSES:
    - Mark cycle: ACTIVE
    - Enqueue: All subscribed scenarios
    ↓
  If validation FAILS:
    - Mark cycle: FAILED
    - Show customer: Error summary + guidance
    - Cycle stays for 7 days (customer fix window)
    ↓
  After 5 working days:
    - All scenarios complete
    - Results cached
    - User login → sees scenario selector with results ready

3.3 Scenario Routing Logic

/**
 * Scenario-Agnostic Router (pseudo-code)
 *
 * Input: Data event (CSV upload, API sync, etc.)
 * Output: Jobs enqueued per subscribed scenario
 */

async function routeDataEventToScenarios(event) {
  // 1. Get tenant subscription
  const tenant = await tenantsRepository.findById(event.tenantId);
  const subscription = await subscriptionRepository.findByTenantId(tenant.id);

  // 2. Map subscription tier → scenario list
  const scenarioMap = {
    'TIER_A': ['sop'],
    'TIER_B': ['sop', 'cfo_finance'],
    'TIER_C': ['sop', 'cfo_finance', 'capex']
  };

  const subscribedScenarios = scenarioMap[subscription.tier];

  // 3. Identify affected scenarios
  // (Not all data changes affect all scenarios)
  const affectedScenarios = identifyAffectedScenarios(event, subscribedScenarios);

  // Example:
  // - If event = "sales data updated" → affects [sop, cfo_finance]
  // - If event = "capex data updated" → affects [capex]

  // 4. Get or create sop_cycle
  let cycle = event.cycleId || await createNewCycle(event);

  // 5. Enqueue jobs (one per affected scenario)
  for (const scenario of affectedScenarios) {
    const job = {
      jobId: uuid(),
      tenantId: tenant.id,
      cycleId: cycle.id,
      scenarioType: scenario,
      eventData: event.data,
      enqueuedAt: now(),
      priority: event.severity === 'HIGH' ? 'URGENT' : 'NORMAL'
    };

    await pgmq.enqueue('scenario_execution_queue', job);
  }

  // 6. Return job IDs to caller
  return affectedScenarios.map(s => ({
    scenario: s,
    jobStatus: 'QUEUED'
  }));
}

3.4 Result Caching & Invalidation

Strategy: Event-based cache invalidation with time-based fallback

CACHE LIFECYCLE:
───────────────────────────────────────────────────────────────

1. Result Execution Complete
   ├─ Store in scenario_execution_results
   ├─ Cache status: VALID
   ├─ cached_at: now()
   ├─ cache_ttl_minutes: 1440 (24 hours)
   └─ cache_invalidated_at: NULL

2. Data Change Detected (Event-Based)
   ├─ Check: Which scenarios does this data affect?
   ├─ Update cache for affected scenarios:
   │  ├─ cache_valid: FALSE
   │  ├─ cache_invalidated_at: now()
   │  └─ Enqueue re-execution job
   └─ (Example: If sales data changes → invalidate [sop, cfo_finance])

3. Time-Based Fallback (Safety Net)
   ├─ Every 24 hours (or if cache_ttl_minutes elapsed):
   │  ├─ Check: cache_valid == FALSE AND cache_invalidated_at < now() - TTL
   │  ├─ Mark: cache_valid: TRUE (use stale data with warning)
   │  └─ Return: Results with "Last updated X hours ago" badge
   └─ (Prevents stuck state if event detection fails)

4. User Requests Result
   ├─ Check: cache_valid == TRUE?
   ├─ YES → Return cached result (instant)
   ├─ NO → Return stale result + "Re-calculating..." badge
   └─ Backend: Background re-execution in progress


CACHE INVALIDATION MATRIX:
───────────────────────────────────────────────────────────────

Data Change          → Affected Scenarios
─────────────────────────────────────
sales_data updated   → [sop, cfo_finance]
inventory updated    → [sop]
production updated   → [sop]
expense updated      → [cfo_finance]
capex_proposal chg   → [capex]
asset_utilization    → [capex]

When scenario execution COMPLETES:
  ├─ Mark cache_valid: TRUE
  ├─ Update cached_at: now()
  └─ Notify frontend (WebSocket): "Results updated"

SECTION 4: SCENARIO EXECUTION ENGINE (Planning + Control)

4.1 Two-Phase Execution Architecture

Every scenario runs through two phases:

PHASE 1: PLANNING

Generate draft plans/analyses using LLM agents
Use hybrid prompting (static template + dynamic tenant context)
Learn from previous runs via meta-prompt refinement

PHASE 2: CONTROL

Validate planning outputs against business constraints
Apply tiered severity rules (Critical/Medium/Low violations)
Accept, flag, or reject based on severity

Scenario Execution Job
  ↓
┌─────────────────────────────────┐
│ PLANNING PHASE                  │
├─────────────────────────────────┤
│ Planning Agent (LLM)            │
│ ├─ Input: Tenant data + context │
│ ├─ Prompt: Static template      │
│ │   + Tenant context injection  │
│ │   (via meta-prompting service)│
│ ├─ Learn: Past execution history│
│ │   + success/failure patterns  │
│ └─ Output: Draft plan/analysis  │
└─────────────────────────────────┘
  ↓
  Intermediate: planning_phase_result (JSONB)
  ↓
┌─────────────────────────────────┐
│ CONTROL PHASE                   │
├─────────────────────────────────┤
│ Control Agent (Constraint Check)│
│ ├─ Input: Draft plan from Phase 1
│ ├─ Rules: Constraint definitions │
│ │   + Severity tiers            │
│ ├─ Check: Validate each        │
│ │   constraint                  │
│ ├─ Classify violations:         │
│ │   • CRITICAL: Hard reject     │
│ │   • MEDIUM: Flag, allow       │
│ │   • LOW: Flag, auto-accept    │
│ └─ Output: Validation report    │
└─────────────────────────────────┘
  ↓
  Result Decision:
  ├─ Has CRITICAL violations? → Status: PARTIAL, approval_required: true
  ├─ Has MEDIUM violations?   → Status: SUCCESS, flagged: true
  └─ Only LOW violations?     → Status: SUCCESS
  ↓
  Store: scenario_execution_results
  ├─ planning_phase_result
  ├─ control_phase_result
  ├─ critical_violations
  ├─ medium_violations
  ├─ low_violations
  └─ final_result
  ↓
  Cache: scene_valid: TRUE, cached_at: now()

4.2 Planning Phase - Agent Specifications

Scenario 1: S&OP Planning Agent

Purpose: Generate demand, supply, and inventory plans for operations leaders.

Agent Responsibilities:

Forecast next period sales based on historical patterns + external signals
Recommend production & inventory levels
Identify supply chain bottlenecks
Generate operational insights

Prompt Construction (Hybrid):

/**
 * S&OP Planning Agent Prompt (Pseudo-code)
 *
 * Strategy: Static base template + dynamic tenant context injection
 */

const sopPlanningPrompt = async (tenantId, cycleData) => {
  // STATIC BASE TEMPLATE
  const baseTemplate = `
You are an expert Supply & Operations Planning agent.
Your task: Generate a demand and supply plan for next period.

Format your response as JSON with:
{
  "demand_forecast": { period, quantity, confidence_score },
  "production_plan": { period, quantity, lead_time_days },
  "inventory_target": { safety_stock, reorder_point },
  "risks": [ { type, severity, mitigation } ],
  "key_insights": [ ... ]
}
`;

  // DYNAMIC TENANT CONTEXT INJECTION
  // (via meta-prompting service)
  const tenantContext = await enrichContextWithTenantHistory(tenantId);

  // Example enrichment:
  // - Past forecast accuracy: 87% MAPE
  // - Lead time patterns: 14-21 days typical
  // - Seasonal patterns: Summer spike 30%, Winter decline 15%
  // - Recent events: Supplier delay (7 days), demand spike (>20%)

  const contextInjection = `
Based on this tenant's historical performance:
- Average forecast accuracy: ${tenantContext.forecastAccuracy}%
- Typical lead times: ${tenantContext.avgLeadTime} days
- Seasonal patterns detected: ${tenantContext.seasonalPatterns}
- Recent anomalies: ${tenantContext.recentEvents}

Use this context to inform your recommendations.
`;

  // COMBINE: Template + Injection
  const finalPrompt = baseTemplate + contextInjection + JSON.stringify(cycleData);

  return finalPrompt;
};

Learning Loop (Meta-Prompt Refinement):

/**
 * After Planning Phase Completes:
 * 1. Check if plan was accepted by Control agent
 * 2. After deployment, check if plan accuracy met targets
 * 3. If accuracy > 90%: Mark prompt as "High Performer"
 * 4. If accuracy < 70%: Refine prompt via meta-prompting service
 */

async function learnFromPlanExecution(planId) {
  const plan = await scenarioResultsRepository.findById(planId);
  const actualOutcome = await fetchActualOutcome(plan.cycleId);

  const accuracy = calculateAccuracy(plan.forecast, actualOutcome);

  if (accuracy < 0.70) {
    // Trigger prompt refinement
    const refinement = await metaPromptService.call({
      initialPrompt: plan.promptUsed,
      taskDescription: `
        SOP Planning for tenant ${plan.tenantId}.
        Previous accuracy: ${accuracy * 100}%.
        Context: ${plan.tenantContext}
      `,
      feedbackData: {
        forecastError: actualOutcome - plan.forecast,
        contextGaps: detectContextGaps(plan, actualOutcome)
      }
    });

    // Store refined prompt for next run
    await promptVersionRepository.create({
      scenarioType: 'sop',
      tenantId: plan.tenantId,
      promptVersion: refinement.refined_prompt,
      accuracy: accuracy,
      appliedFrom: now() + ONE_DAY  // Use new prompt next cycle
    });
  }
}

Planning Agent Output:

{
  "demand_forecast": {
    "period": "2025-12-01",
    "quantity": 15000,
    "confidence_score": 0.87,
    "reasoning": "Based on 3-year trend + current 12% YoY growth + seasonal adjustment"
  },
  "production_plan": {
    "quantity": 15500,
    "lead_time_days": 14,
    "batch_size": 2500,
    "notes": "5% safety margin for demand uncertainty"
  },
  "inventory_target": {
    "safety_stock": 2000,
    "reorder_point": 5000,
    "target_daysofstock": 8
  },
  "risks": [
    {
      "type": "supplier_delay",
      "severity": "MEDIUM",
      "probability": 0.15,
      "mitigation": "Increase safety stock by 10%, pre-order 2 weeks early"
    }
  ],
  "key_insights": [
    "Demand seasonality suggests prepare for 30% Q4 spike",
    "Current inventory 12% below optimal - recommend production increase",
    "Lead time stability suggests can reduce safety stock by 5%"
  ]
}

Scenario 2: CFO Financial Analysis Agent

Purpose: Generate financial forecasts, expense analysis, and cash flow insights for finance leaders.

Agent Responsibilities:

Forecast revenue & COGS based on S&OP outputs + market trends
Analyze operating expenses (budget vs actual)
Project cash flow and identify funding needs
Recommend cost optimization opportunities

Prompt Construction (Hybrid):

const cfoPlanningPrompt = async (tenantId, sopPlanOutput, cycleData) => {
  const baseTemplate = `
You are a Chief Financial Officer strategic advisor.
Task: Generate financial projections based on operational plan + financial history.

Input:
- S&OP Production Plan: [${JSON.stringify(sopPlanOutput.production_plan)}]
- Historical P&L: [attached data]

Output JSON:
{
  "revenue_forecast": { period, amount, variance_vs_plan },
  "cogs_forecast": { period, amount, margin_percent },
  "expense_analysis": { category, budget, forecast, variance },
  "cashflow_projection": { operating_cf, investing_cf, financing_cf },
  "financial_risks": [ { type, impact, mitigation } ],
  "recommendations": [ ... ]
}
`;

  const contextInjection = await enrichCFOContext(tenantId);
  // - Historical margin trends: Average 42%, trending down 1% YoY
  // - Expense volatility: Salary costs stable, marketing variable
  // - Cash conversion: 45 DPO typical, trending to 50 DPO
  // - Debt covenants: Must maintain 1.2x current ratio

  return baseTemplate + JSON.stringify(contextInjection) + JSON.stringify(cycleData);
};

Planning Agent Output:

{
  "revenue_forecast": {
    "period": "2025-12-01",
    "amount": 4500000,
    "variance_vs_plan": -2.5,
    "reasoning": "S&OP forecast down 3%, offset by +0.5% price increase implemented"
  },
  "cogs_forecast": {
    "period": "2025-12-01",
    "amount": 2610000,
    "cogs_percent": 58,
    "trend": "Up 1% due to raw material cost inflation"
  },
  "expense_analysis": [
    {
      "category": "Salaries",
      "budget": 800000,
      "forecast": 805000,
      "variance": 0.6,
      "notes": "1 new hire planned Nov"
    },
    {
      "category": "Marketing",
      "budget": 250000,
      "forecast": 265000,
      "variance": 6.0,
      "notes": "Increased spend for Q4 campaign - ROI tracking needed"
    }
  ],
  "cashflow_projection": {
    "operating_cf": 450000,
    "investing_cf": -100000,
    "financing_cf": 0,
    "net_cf": 350000,
    "endofperiod_cash": 1250000
  },
  "financial_risks": [
    {
      "type": "DSO_increase",
      "probability": 0.3,
      "impact": "Could reduce operating CF by $80K if DSO rises to 55 days",
      "mitigation": "Strengthen collections, consider early payment discounts"
    }
  ],
  "recommendations": [
    "Consider variable cost reduction: negotiate supplier contracts (potential 2% savings)",
    "Review marketing spend ROI before committing full $265K budget",
    "Monitor cash position - strong but could support strategic investment"
  ]
}

Scenario 3: CapEx Decision Agent

Purpose: Evaluate capital allocation decisions for strategic planning teams.

Agent Responsibilities:

Analyze CapEx proposal ROI and strategic fit
Rank proposals by financial + strategic value
Identify portfolio optimization (conflicts, synergies)
Recommend allocation strategy within budget constraints

Prompt Construction (Hybrid):

const capexPlanningPrompt = async (tenantId, currentAssets, proposals) => {
  const baseTemplate = `
You are a Capital Allocation strategist.
Task: Evaluate and rank CapEx proposals; recommend optimal portfolio.

Constraints:
- Total budget: $${currentBudget}M
- Strategic priority: ${strategicFocus}
- Risk tolerance: ${riskTolerance}

Output JSON:
{
  "proposal_rankings": [ { id, rank, score, rationale } ],
  "portfolio_recommendation": { selected, rejected, reasoning },
  "synergies": [ { proposals, synergy_value } ],
  "risks": [ { proposal, risk, mitigation } ],
  "financial_impact": { npv, irr, payback_years }
}
`;

  const contextInjection = await enrichCapexContext(tenantId);
  // - Existing asset utilization: 78% average (some underutilized)
  // - Past project execution: 92% on time, 105% cost (historically overrun)
  // - Depreciation schedule: $400K/year, 5-year average asset life
  // - Strategic priorities: Growth (40%), Cost reduction (35%), Risk mitigation (25%)

  return baseTemplate + JSON.stringify(contextInjection) + JSON.stringify(proposals);
};

Planning Agent Output:

{
  "proposal_rankings": [
    {
      "proposal_id": "CAP-001",
      "proposal_name": "Production Line Automation",
      "rank": 1,
      "overall_score": 8.7,
      "financial_score": 8.5,
      "strategic_score": 8.9,
      "rationale": "Strong ROI (28%), aligns with cost reduction strategy, 3-year payback"
    },
    {
      "proposal_id": "CAP-003",
      "proposal_name": "Warehouse Expansion",
      "rank": 2,
      "overall_score": 7.2,
      "financial_score": 7.0,
      "strategic_score": 7.5,
      "rationale": "Supports growth, but execution risk (complexity), 4-year payback"
    }
  ],
  "portfolio_recommendation": {
    "total_investment": 8500000,
    "selected_proposals": ["CAP-001", "CAP-003"],
    "rejected_proposals": ["CAP-002"],
    "rejected_rationale": "CAP-002 (Building Renovation) low strategic fit, 6-year payback misaligned with strategy",
    "budget_utilization": "85% ($8.5M of $10M budget)"
  },
  "synergies": [
    {
      "proposals": ["CAP-001", "CAP-003"],
      "synergy_type": "Operational Integration",
      "synergy_value": 200000,
      "details": "Automation can improve warehouse throughput; combined efficiency gains"
    }
  ],
  "financial_impact": {
    "total_investment": 8500000,
    "npv_3year": 2100000,
    "irr": 0.285,
    "payback_years": 3.4,
    "roi_percent": 24.7
  },
  "risks": [
    {
      "proposal": "CAP-001",
      "risk": "Implementation complexity",
      "probability": 0.3,
      "impact": "Could delay 6 months, add $200K cost",
      "mitigation": "Hire experienced integrator, phased rollout"
    }
  ]
}

4.3 Control Phase - Constraint Validation

Purpose: Validate planning outputs against business constraints with tiered severity.

Control Agent Logic:

/**
 * Control Phase Agent (Pseudo-code)
 */

async function runControlPhase(planningOutput, tenantId, scenarioType) {
  const constraints = await loadConstraints(tenantId, scenarioType);

  const violations = {
    critical: [],    // Hard-reject
    medium: [],      // Flag but allow
    low: []          // Flag and auto-accept
  };

  for (const constraint of constraints) {
    const breached = await validateConstraint(constraint, planningOutput);

    if (breached) {
      violations[constraint.severity].push({
        constraint: constraint.name,
        violation: breached.detail,
        remediation: constraint.remediation_suggestion
      });
    }
  }

  return {
    violations,
    approved: violations.critical.length === 0,
    requiresApproval: violations.critical.length > 0,
    flagged: violations.medium.length > 0 || violations.low.length > 0,
    recommendations: generateRecommendations(violations)
  };
}

Example Constraints per Scenario:

S&OP Constraints

{
  "constraint_id": "SOP-001",
  "name": "Production Capacity",
  "description": "Production plan cannot exceed max capacity",
  "expression": "production_plan.quantity <= assets.capacity",
  "severity": "CRITICAL",
  "remediation": "Reduce production quantity or increase capacity investment"
}

CFO Constraints

{
  "constraint_id": "CFO-001",
  "name": "Debt Covenant",
  "description": "Current ratio must stay >= 1.2",
  "expression": "current_assets / current_liabilities >= 1.2",
  "severity": "CRITICAL",
  "remediation": "Increase liquid assets or reduce short-term liabilities"
}

CapEx Constraints

{
  "constraint_id": "CAPEX-001",
  "name": "Budget Limit",
  "description": "Total investment cannot exceed approved budget",
  "expression": "sum(selected_proposals.investment) <= budget_limit",
  "severity": "CRITICAL",
  "remediation": "Reduce proposal scope or reject lower-priority proposals"
}

4.4 Result Finalization

/**
 * Finalize scenario result based on Control phase
 */

const finalizeScenarioResult = async (cycleId, scenarioType, planning, control) => {

  let executionStatus = 'SUCCESS';
  let requiresApproval = false;

  // Determine status based on violations
  if (control.violations.critical.length > 0) {
    executionStatus = 'PARTIAL';
    requiresApproval = true;
  }

  if (control.violations.medium.length > 0) {
    requiresApproval = false;  // Auto-flagged but no approval needed
  }

  // Store final result
  const result = {
    sop_cycle_id: cycleId,
    scenario_type: scenarioType,
    execution_status: executionStatus,
    planning_phase_result: planning,
    control_phase_result: control,
    final_result: {
      primary_recommendation: planning.recommendations?.[0],
      constraints_validated: !requiresApproval,
      flagged_items: control.violations.medium.concat(control.violations.low),
      requires_approval: requiresApproval
    },
    critical_violations: control.violations.critical,
    medium_violations: control.violations.medium,
    low_violations: control.violations.low,
    cached_at: now(),
    cache_valid: true,
    completed_at: now()
  };

  await scenarioResultsRepository.create(result);

  // Notify frontend
  await notificationService.broadcastScenarioComplete({
    tenantId: cycleId.tenantId,
    scenario: scenarioType,
    status: executionStatus,
    requiresApproval
  });

  return result;
};

SECTION 5: API CONTRACTS & USER EXPERIENCE

5.1 Scenario Selector (Entry Point)

Route: GET /api/scenarios/selector

Query Params:

tenant_id: UUID

Response:

{
  "tenant_id": "uuid-123",
  "subscription_tier": "TIER_B",
  "available_scenarios": [
    {
      "scenario_type": "sop",
      "display_name": "S&OP Planning",
      "icon": "📊",
      "description": "Demand, supply, and inventory planning",
      "last_cycle_id": "cycle-456",
      "last_execution": "2025-11-05T14:30:00Z",
      "cache_valid": true,
      "result_summary": {
        "status": "SUCCESS",
        "key_metrics": {
          "demand_forecast_next_period": 15000,
          "recommendation": "Increase production by 8%"
        }
      }
    },
    {
      "scenario_type": "cfo_finance",
      "display_name": "Financial Intelligence",
      "icon": "💰",
      "description": "Revenue, expenses, and cash flow forecasting",
      "last_cycle_id": "cycle-456",
      "last_execution": "2025-11-05T14:35:00Z",
      "cache_valid": false,
      "cache_invalidated_at": "2025-11-05T15:00:00Z",
      "result_summary": {
        "status": "UPDATING",
        "last_valid_result": {
          "revenue_forecast": 4500000,
          "margin_trend": "down 1%"
        },
        "stale_message": "Results being recalculated based on latest data..."
      }
    }
  ]
}

5.2 Scenario Results Endpoint

Route: GET /api/scenarios/{scenarioType}/results

Query Params:

cycle_id: UUID (optional - defaults to latest)
tenant_id: UUID

Response:

{
  "scenario_type": "sop",
  "cycle_id": "cycle-456",
  "execution_status": "SUCCESS",
  "cached": true,
  "cached_at": "2025-11-05T14:30:00Z",
  "last_updated": "2025-11-05T14:30:00Z",

  "planning_phase": {
    "demand_forecast": { ... },
    "production_plan": { ... },
    "inventory_target": { ... },
    "key_insights": [ ... ]
  },

  "control_phase": {
    "constraints_validated": true,
    "critical_violations": [],
    "medium_violations": [],
    "low_violations": [
      {
        "constraint": "Safety stock threshold",
        "violation": "Recommended safety stock 2000 vs historical 1800",
        "severity": "LOW"
      }
    ]
  },

  "final_recommendation": "Proceed with production plan; monitor demand for deviations",
  "requires_approval": false,
  "approval_link": null
}

5.3 CSV Upload Enhanced Endpoint

Route: POST /api/csv/upload (existing, enhanced)

Changes:

Auto-detect tier-specific file (detect sales_and_operations.csv → apply TIER A validation)
Return tier + validation rules in response
Enhanced error messages per tier

Response (Enhanced):

{
  "fileId": "file-123",
  "detectedTier": "TIER_A",
  "detectedFileType": "sales_and_operations",
  "headers": [ ... ],
  "sampleRows": [ ... ],
  "validationRules": {
    "required": ["period", "product_sku", ...],
    "constraints": {
      "period": "DATE (YYYY-MM-01), min 12 months",
      "product_sku": "Must exist in core_entities",
      ...
    }
  },
  "mappingSuggestions": [ ... ],
  "piiFields": [ ... ]
}

5.4 Cycle Events & Notifications

WebSocket Message (on cache invalidation):

{
  "event": "scenario_results_updated",
  "tenantId": "uuid-123",
  "scenarioType": "cfo_finance",
  "cycleId": "cycle-456",
  "status": "SUCCESS",
  "timestamp": "2025-11-05T15:05:00Z",
  "message": "Financial analysis updated with latest data"
}

WebSocket Message (on event-triggered cycle):

{
  "event": "cycle_boundary_triggered",
  "tenantId": "uuid-123",
  "previousCycleId": "cycle-456",
  "newCycleId": "cycle-457",
  "trigger": "Demand spike >20% detected",
  "triggerTime": "2025-11-05T15:00:00Z",
  "affectedScenarios": ["sop", "cfo_finance"],
  "message": "Major event detected - new decision context created. See 'Before/After' analysis.",
  "beforeAfterLink": "/scenarios/comparison?old_cycle=cycle-456&new_cycle=cycle-457"
}

SECTION 6: PERFORMANCE & COST OPTIMIZATION

6.1 Execution Time Targets

Scenario	Planning Phase	Control Phase	Total	Cache Hit
S&OP	10-15s	2-3s	12-18s	<100ms
CFO Finance	15-20s	3-5s	18-25s	<100ms
CapEx	20-30s	5-8s	25-38s	<100ms

Optimization Strategies:

Prompt Caching - Cache refined prompts after first execution (5s savings per scenario)
Parallel Control Checks - Run constraint checks in parallel for CapEx proposals
Incremental Planning - For continuous data (small changes), do delta planning instead of full re-run
Result Caching - Event-based invalidation prevents redundant executions

6.2 Cost Optimization

Cost Drivers:

LLM API calls (Gemini) - $0.00075/1K input tokens, $0.003/1K output tokens
Database - Fixed + storage + compute
Message Queue (pgmq) - Minimal overhead
Background Worker Compute - Fixed instance cost

Cost Control Mechanisms:

1. Subscription Stacking
   - Tier A: Only SOP scenario runs → lower cost → lower price
   - Tier B: SOP + CFO scenario runs → higher cost → higher price
   - Tier C: All 3 scenarios run → highest cost → highest price
   - Pricing reflects cost structure

2. Smart Triggering
   - Event threshold: >20% change triggers re-run
   - <20% change: Append to current cycle (no re-run)
   - Prevents running scenarios for every tiny data change

3. Incremental Execution
   - Full re-run: On major events (10-30s per scenario)
   - Delta run: On minor updates (2-5s per scenario)
   - Cost: Proportional to change magnitude

4. Result Caching
   - 24-hour TTL = 1 execution per day per scenario minimum
   - Event-based invalidation = Only re-run if data changes
   - Worst case: 1 full execution per day per subscribed scenario

5. Batch Processing
   - Aggregate pending jobs (wait 30 seconds)
   - Run in batch to amortize LLM overhead

Cost Estimation (Monthly per Tenant):

TIER A (SOP only):
- SOP Planning: 30 executions/month × $0.10/execution = $3.00
- SOP Control: 30 executions/month × $0.02/execution = $0.60
- Infrastructure: $5/month
- Total: ~$9/month → Charge $50/month (5.6x margin)

TIER B (SOP + CFO):
- SOP: 30 × ($0.10 + $0.02) = $3.60
- CFO: 30 × ($0.12 + $0.03) = $4.50
- Infrastructure: $5/month
- Total: ~$13/month → Charge $100/month (7.7x margin)

TIER C (All 3):
- SOP: $3.60
- CFO: $4.50
- CapEx: 30 × ($0.15 + $0.04) = $5.70
- Infrastructure: $8/month
- Total: ~$22/month → Charge $300/month (13.6x margin)

Note: Infrastructure cost is shared across all scenarios.
Marginal cost per additional scenario: $1-2/month.

6.3 Scalability Considerations

Horizontal Scaling:

Background workers can scale independently (add worker instances)
Each worker processes one job at a time (thread-safe)
pgmq distributes jobs fairly across workers

Vertical Scaling:

Database: Add read replicas for result queries
Cache layer: Add Redis for extremely hot results (future optimization)

Cost Scaling:

Cost grows linearly with:
- Number of tenants
- Frequency of data updates (triggers re-runs)
- Number of subscribed scenarios
Cost SHRINKS with:
- Better event detection (fewer false triggers)
- Prompt optimization (fewer LLM calls or tokens)
- Cache hit ratio improvements

APPENDIX A: DECISION LOG

Decision 1: CSV Bootstrap + Progressive API Integration ✅

Chosen: Option C (Hybrid)

Rationale: Fast path to value while enabling future sophistication
Implementation: Day 1-5 CSV uploads only; Day 5+ optional API connectors
FSD Impact: Section 2 specifies CSV file formats for each tier
Dev Impact: No changes to existing API integration code; CSV-first bootstrapping

Decision 2: Financial Data Storage ✅

Chosen: Option C (Both Tables)

Rationale: Cleaner separation of operational vs financial data; matches CFO mental model
Implementation: Keep sop_plan_data for product-level S&OP; add financial_summary for company-level CFO data
FSD Impact: Section 2 defines both tables with clear responsibility boundaries
Dev Impact: New financial_summary migration + repository

Decision 3: Cycle Management ✅

Chosen: Hybrid Approach (Continuous + Event-Driven)

Rationale: Respects monthly decision cycles while capturing major anomalies
Implementation: Default continuous flow into PERIODIC cycle; create EVENT_TRIGGERED cycles on major changes
FSD Impact: Section 3 specifies event thresholds and cycle boundary logic
Dev Impact: Event detection engine + cycle routing logic

Decision 4: Subscription Stacking ✅

Chosen: Tiers stack features (A ⊂ B ⊂ C)

Rationale: Clearer pricing, prevents confusion, enables upsell path
Implementation: TIER B = TIER A + CFO scenario; TIER C = TIER A + B + CapEx scenario
FSD Impact: Section 1 specifies tiered data requirements; Section 4 specifies 3 agents
Dev Impact: Scenario router filters by subscription tier

Decision 5: Planning Phase Prompting ✅

Chosen: Hybrid Approach (Static Base + Dynamic Context)

Rationale: Predictable base + adaptable to tenant-specific patterns
Implementation: Static template per scenario + meta-prompt service injects tenant context + historical patterns
FSD Impact: Section 4.2 specifies prompt construction per scenario with learning loop
Dev Impact: Extend meta-prompting service; track execution accuracy per prompt version

Decision 6: Control Phase Violations ✅

Chosen: Tiered Severity (Critical/Medium/Low)

Rationale: Avoid binary approve/reject; flag issues for human review but allow continuation when safe
Implementation: CRITICAL violations block approval; MEDIUM violations flag but allow; LOW violations auto-accept
FSD Impact: Section 4.3 specifies constraint examples per scenario
Dev Impact: Constraint definition schema + severity classification logic

Decision 7: Cache Invalidation ✅

Chosen: Event-Based with Time-Based Fallback

Rationale: Minimize stale results; prevent stuck states
Implementation: Invalidate on data change detection; fall back to 24-hour TTL if event detection fails
FSD Impact: Section 3.4 specifies invalidation matrix and lifecycle
Dev Impact: Event detector + cache table with validity flags

APPROVAL CHECKLIST

Before implementation, confirm:

NEXT STEPS

Phase 1: Design Approval (Current - Awaiting Review)

Review this FSD document
Raise questions or request clarifications
Approve architectural decisions

Phase 2: Implementation Planning (After Approval)

Use superpowers:writing-plans to generate detailed task breakdown
Create feature branch: feature/m60-scenario-orchestration
Implementation order:
1. Data layer (new tables + migrations)
2. Orchestration layer (event detection + routing)
3. Scenario execution (Planning + Control agents)
4. API & UI (scenario selector, results display)
5. CSV upload enhancements

Phase 3: Implementation & Testing (Post-Planning)

Implement in phases with code review between layers
Integration tests for end-to-end workflows
Load testing for performance targets

Phase 4: Demo & Launch (Post-Implementation)

Demo: CSV upload → instant scenario results
Demo: Scenario switching (S&OP ↔ CFO ↔ CapEx)
Demo: Event-triggered cycle boundary
Launch: Invite pilot customers for early feedback

Document Status: DRAFT - Awaiting Architectural Review Last Updated: November 5, 2025 Author: Pramod Prasanth + Claude Code Milestone: M60 (Constraint Intelligence Engine V2)

TABLE OF CONTENTS​

EXECUTIVE SUMMARY​

Key Product Changes​

Three Subscription Tiers (MVP)​

SECTION 1: PRODUCT ARCHITECTURE OVERVIEW​

1.1 System Context Diagram​

1.2 Component Responsibilities​

Orchestration Layer (Node.js Backend)​

Async Execution Layer (Background Workers)​

Persistence Layer (PostgreSQL)​

1.3 Multi-Tenancy Model​

SECTION 2: DATA MODEL & SCHEMA​

2.1 CSV Bootstrap Files per Tier​

TIER A: S&OP Decision Intelligence​

TIER B: CFO Financial Intelligence​

TIER C: Capital Allocation Intelligence​

2.2 New Database Tables​

Table: financial_summary​

Table: operating_expense_actuals​

Table: capex_proposals​

Table: asset_inventory​

Table: scenario_execution_results​

Enhanced Table: sop_cycles​

2.3 CSV Validation Enhancements​

SECTION 3: ORCHESTRATION ARCHITECTURE​

3.1 Data Event Detection​

Event Sources​

Event Detection Flow​

3.2 Cycle Lifecycle Management​

3.3 Scenario Routing Logic​

3.4 Result Caching & Invalidation​

SECTION 4: SCENARIO EXECUTION ENGINE (Planning + Control)​

4.1 Two-Phase Execution Architecture​

4.2 Planning Phase - Agent Specifications​

Scenario 1: S&OP Planning Agent​

Scenario 2: CFO Financial Analysis Agent​

Scenario 3: CapEx Decision Agent​

4.3 Control Phase - Constraint Validation​

S&OP Constraints​

CFO Constraints​

CapEx Constraints​

4.4 Result Finalization​

SECTION 5: API CONTRACTS & USER EXPERIENCE​

5.1 Scenario Selector (Entry Point)​

5.2 Scenario Results Endpoint​

5.3 CSV Upload Enhanced Endpoint​

5.4 Cycle Events & Notifications​

SECTION 6: PERFORMANCE & COST OPTIMIZATION​

6.1 Execution Time Targets​

6.2 Cost Optimization​

6.3 Scalability Considerations​

APPENDIX A: DECISION LOG​

Decision 1: CSV Bootstrap + Progressive API Integration ✅​

Decision 2: Financial Data Storage ✅​

Decision 3: Cycle Management ✅​

Decision 4: Subscription Stacking ✅​

Decision 5: Planning Phase Prompting ✅​

Decision 6: Control Phase Violations ✅​

Decision 7: Cache Invalidation ✅​

APPROVAL CHECKLIST​

NEXT STEPS​

TABLE OF CONTENTS

EXECUTIVE SUMMARY

Key Product Changes

Three Subscription Tiers (MVP)

SECTION 1: PRODUCT ARCHITECTURE OVERVIEW

1.1 System Context Diagram

1.2 Component Responsibilities

Orchestration Layer (Node.js Backend)

Async Execution Layer (Background Workers)

Persistence Layer (PostgreSQL)

1.3 Multi-Tenancy Model

SECTION 2: DATA MODEL & SCHEMA

2.1 CSV Bootstrap Files per Tier

TIER A: S&OP Decision Intelligence

TIER B: CFO Financial Intelligence

TIER C: Capital Allocation Intelligence

2.2 New Database Tables

Table: `financial_summary`

Table: `operating_expense_actuals`

Table: `capex_proposals`

Table: `asset_inventory`

Table: `scenario_execution_results`

Enhanced Table: `sop_cycles`

2.3 CSV Validation Enhancements

SECTION 3: ORCHESTRATION ARCHITECTURE

3.1 Data Event Detection

Event Sources

Event Detection Flow

3.2 Cycle Lifecycle Management

3.3 Scenario Routing Logic

3.4 Result Caching & Invalidation

SECTION 4: SCENARIO EXECUTION ENGINE (Planning + Control)

4.1 Two-Phase Execution Architecture

4.2 Planning Phase - Agent Specifications

Scenario 1: S&OP Planning Agent

Scenario 2: CFO Financial Analysis Agent

Scenario 3: CapEx Decision Agent

4.3 Control Phase - Constraint Validation

S&OP Constraints

CFO Constraints

CapEx Constraints

4.4 Result Finalization

SECTION 5: API CONTRACTS & USER EXPERIENCE

5.1 Scenario Selector (Entry Point)

5.2 Scenario Results Endpoint

5.3 CSV Upload Enhanced Endpoint

5.4 Cycle Events & Notifications

SECTION 6: PERFORMANCE & COST OPTIMIZATION

6.1 Execution Time Targets

6.2 Cost Optimization

6.3 Scalability Considerations

APPENDIX A: DECISION LOG

Decision 1: CSV Bootstrap + Progressive API Integration ✅

Decision 2: Financial Data Storage ✅

Decision 3: Cycle Management ✅

Decision 4: Subscription Stacking ✅

Decision 5: Planning Phase Prompting ✅

Decision 6: Control Phase Violations ✅

Decision 7: Cache Invalidation ✅

APPROVAL CHECKLIST

NEXT STEPS