Implementation Guide: Monitor supplier lead times and proactively adjust production schedules when delays detected

sudo nano /etc/netplan/00-installer-config.yaml

network:
  ethernets:
    eno1:
      addresses: [192.168.10.50/24]
      routes:
        - to: default
          via: 192.168.10.1
      nameservers:
        addresses: [8.8.8.8, 8.8.4.4]
  version: 2

sudo netplan apply
sudo apt update && sudo apt upgrade -y
sudo apt install -y curl git wget htop net-tools ufw openssh-server
sudo ufw allow 22/tcp
sudo ufw allow 443/tcp
sudo ufw allow 5678/tcp comment 'n8n web UI'
sudo ufw allow 3000/tcp comment 'Grafana'
sudo ufw enable

sudo apt install -y ca-certificates gnupg
sudo install -m 0755 -d /etc/apt/keyrings
curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo gpg --dearmor -o /etc/apt/keyrings/docker.gpg
sudo chmod a+r /etc/apt/keyrings/docker.gpg
echo "deb [arch=$(dpkg --print-architecture) signed-by=/etc/apt/keyrings/docker.gpg] https://download.docker.com/linux/ubuntu $(. /etc/os-release && echo $VERSION_CODENAME) stable" | sudo tee /etc/apt/sources.list.d/docker.list > /dev/null
sudo apt update
sudo apt install -y docker-ce docker-ce-cli containerd.io docker-buildx-plugin docker-compose-plugin
sudo useradd -m -s /bin/bash agentops
sudo usermod -aG docker agentops
docker --version
docker compose version
sudo mkdir -p /opt/supply-chain-agent/{n8n,crewai,postgres,grafana,prometheus,nginx,logs}
sudo chown -R agentops:agentops /opt/supply-chain-agent

cd /opt/supply-chain-agent

cat > docker-compose.yml << 'EOF'
version: '3.8'

services:
  postgres:
    image: postgres:16-alpine
    container_name: scm-postgres
    restart: unless-stopped
    environment:
      POSTGRES_DB: supply_chain_agent
      POSTGRES_USER: scm_admin
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
    volumes:
      - ./postgres/data:/var/lib/postgresql/data
      - ./postgres/init:/docker-entrypoint-initdb.d
    ports:
      - '5432:5432'
    healthcheck:
      test: ['CMD-SHELL', 'pg_isready -U scm_admin -d supply_chain_agent']
      interval: 10s
      timeout: 5s
      retries: 5

EOF

cat > .env << 'EOF'
POSTGRES_PASSWORD=<GENERATE_STRONG_PASSWORD_HERE>
OPENAI_API_KEY=<AZURE_OPENAI_KEY_HERE>
ERP_API_BASE_URL=<CLIENT_ERP_API_ENDPOINT>
ERP_API_KEY=<CLIENT_ERP_API_KEY>
TEAMS_WEBHOOK_URL=<TEAMS_INCOMING_WEBHOOK_URL>
EOF
chmod 600 .env

cat > postgres/init/01-schema.sql << 'SQLEOF'
-- Supplier master data cache
CREATE TABLE suppliers (
  supplier_id VARCHAR(50) PRIMARY KEY,
  supplier_name VARCHAR(255) NOT NULL,
  category VARCHAR(50) DEFAULT 'standard',
  criticality VARCHAR(20) DEFAULT 'medium' CHECK (criticality IN ('critical','high','medium','low')),
  avg_lead_time_days NUMERIC(6,1),
  lead_time_std_dev NUMERIC(6,2),
  on_time_delivery_rate NUMERIC(5,2),
  last_synced_at TIMESTAMPTZ DEFAULT NOW(),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Purchase order tracking
CREATE TABLE purchase_orders (
  po_id VARCHAR(50) PRIMARY KEY,
  erp_po_number VARCHAR(50) NOT NULL,
  supplier_id VARCHAR(50) REFERENCES suppliers(supplier_id),
  part_number VARCHAR(100),
  part_description TEXT,
  quantity NUMERIC(12,2),
  order_date DATE NOT NULL,
  requested_delivery_date DATE NOT NULL,
  confirmed_delivery_date DATE,
  actual_receipt_date DATE,
  current_status VARCHAR(30) DEFAULT 'open',
  delay_days INTEGER GENERATED ALWAYS AS (
    CASE WHEN actual_receipt_date IS NOT NULL THEN
      (actual_receipt_date - requested_delivery_date)
    WHEN confirmed_delivery_date IS NOT NULL THEN
      (confirmed_delivery_date - requested_delivery_date)
    ELSE NULL END
  ) STORED,
  last_checked_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_po_supplier ON purchase_orders(supplier_id);
CREATE INDEX idx_po_status ON purchase_orders(current_status);
CREATE INDEX idx_po_delivery ON purchase_orders(requested_delivery_date);

-- Delay events detected by agents
CREATE TABLE delay_events (
  event_id SERIAL PRIMARY KEY,
  po_id VARCHAR(50) REFERENCES purchase_orders(po_id),
  supplier_id VARCHAR(50) REFERENCES suppliers(supplier_id),
  detected_at TIMESTAMPTZ DEFAULT NOW(),
  delay_type VARCHAR(30) NOT NULL CHECK (delay_type IN ('confirmed_late','predicted_late','shipment_delayed','no_confirmation')),
  original_date DATE NOT NULL,
  new_expected_date DATE,
  delay_magnitude_days INTEGER,
  confidence_score NUMERIC(4,3),
  detection_method VARCHAR(50),
  raw_signal JSONB,
  resolved_at TIMESTAMPTZ,
  resolution_action VARCHAR(100)
);

-- Schedule adjustment recommendations and actions
CREATE TABLE schedule_adjustments (
  adjustment_id SERIAL PRIMARY KEY,
  delay_event_id INTEGER REFERENCES delay_events(event_id),
  work_order_id VARCHAR(50) NOT NULL,
  original_start_date DATE,
  original_end_date DATE,
  recommended_start_date DATE,
  recommended_end_date DATE,
  impact_description TEXT,
  priority VARCHAR(20) DEFAULT 'medium',
  status VARCHAR(30) DEFAULT 'pending' CHECK (status IN ('pending','approved','rejected','auto_executed','rolled_back')),
  approved_by VARCHAR(100),
  approved_at TIMESTAMPTZ,
  executed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Agent decision audit log (compliance-critical)
CREATE TABLE agent_audit_log (
  log_id BIGSERIAL PRIMARY KEY,
  timestamp TIMESTAMPTZ DEFAULT NOW(),
  agent_name VARCHAR(100) NOT NULL,
  action_type VARCHAR(50) NOT NULL,
  input_summary JSONB,
  output_summary JSONB,
  llm_model VARCHAR(50),
  token_usage JSONB,
  decision_rationale TEXT,
  confidence_score NUMERIC(4,3),
  human_override BOOLEAN DEFAULT FALSE,
  execution_time_ms INTEGER
);

CREATE INDEX idx_audit_timestamp ON agent_audit_log(timestamp);
CREATE INDEX idx_audit_agent ON agent_audit_log(agent_name);

-- Supplier performance metrics (daily aggregation)
CREATE TABLE supplier_metrics_daily (
  metric_date DATE,
  supplier_id VARCHAR(50) REFERENCES suppliers(supplier_id),
  total_open_pos INTEGER,
  delayed_pos INTEGER,
  avg_delay_days NUMERIC(6,1),
  risk_score NUMERIC(4,3),
  PRIMARY KEY (metric_date, supplier_id)
);
SQLEOF

docker compose up -d postgres

sleep 10
docker exec scm-postgres psql -U scm_admin -d supply_chain_agent -c '\dt'

cat >> docker-compose.yml << 'EOF'

  n8n:
    image: docker.n8n.io/n8nio/n8n:latest
    container_name: scm-n8n
    restart: unless-stopped
    environment:
      - N8N_HOST=agent.${CLIENT_DOMAIN}
      - N8N_PORT=5678
      - N8N_PROTOCOL=https
      - NODE_ENV=production
      - WEBHOOK_URL=https://agent.${CLIENT_DOMAIN}/
      - DB_TYPE=postgresdb
      - DB_POSTGRESDB_HOST=postgres
      - DB_POSTGRESDB_PORT=5432
      - DB_POSTGRESDB_DATABASE=supply_chain_agent
      - DB_POSTGRESDB_USER=scm_admin
      - DB_POSTGRESDB_PASSWORD=${POSTGRES_PASSWORD}
      - N8N_ENCRYPTION_KEY=${N8N_ENCRYPTION_KEY}
      - N8N_USER_MANAGEMENT_JWT_SECRET=${N8N_JWT_SECRET}
      - EXECUTIONS_DATA_PRUNE=true
      - EXECUTIONS_DATA_MAX_AGE=168
    volumes:
      - ./n8n/data:/home/node/.n8n
    ports:
      - '5678:5678'
    depends_on:
      postgres:
        condition: service_healthy
    links:
      - postgres

EOF
echo "N8N_ENCRYPTION_KEY=$(openssl rand -hex 32)" >> .env
echo "N8N_JWT_SECRET=$(openssl rand -hex 32)" >> .env
docker compose up -d n8n
sleep 15
curl -s http://localhost:5678/healthz

mkdir -p /opt/supply-chain-agent/crewai/integrations

curl -X GET 'https://<epicor-server>/api/v2/odata/<company>/Erp.BO.POSvc/POs?$top=5&$select=PONum,VendorNum,OrderDate,DueDate,OpenOrder' \
  -H 'Authorization: Basic <base64-encoded-credentials>' \
  -H 'X-API-Key: <epicor-api-key>' \
  -H 'Content-Type: application/json'

curl -X POST 'https://<acumatica-instance>/entity/auth/login' \
  -H 'Content-Type: application/json' \
  -d '{"name":"apiuser","password":"<password>","company":"<company>"}'

curl -X GET 'https://<acumatica-instance>/entity/Default/24.200.001/PurchaseOrder?$top=5' \
  -H 'Content-Type: application/json' \
  --cookie 'cookie-from-login'

curl -X POST 'https://login.microsoftonline.com/<tenant-id>/oauth2/v2.0/token' \
  -d 'client_id=<app-id>&scope=https://<d365-instance>.operations.dynamics.com/.default&client_secret=<secret>&grant_type=client_credentials'

# saved to /opt/supply-chain-agent/crewai/integrations/erp_config.yaml

erp_system: epicor_kinetic  # Options: epicor_kinetic, acumatica, dynamics365
base_url: https://<erp-server>/api/v2
auth_method: api_key  # Options: api_key, basic, oauth2
company_id: <COMPANY_ID>
api_endpoints:
  purchase_orders:
    list: /odata/{company}/Erp.BO.POSvc/POs
    detail: /odata/{company}/Erp.BO.POSvc/POs('{po_num}')
    fields: [PONum, VendorNum, VendorName, OrderDate, DueDate, PromiseDt, OpenOrder, OrderQty, ReceivedQty]
  work_orders:
    list: /odata/{company}/Erp.BO.JobEntrySvc/JobHeads
    detail: /odata/{company}/Erp.BO.JobEntrySvc/JobHeads('{job_num}')
    update: /odata/{company}/Erp.BO.JobEntrySvc/JobHeads('{job_num}')
    fields: [JobNum, PartNum, ProdQty, StartDate, DueDate, JobClosed, JobReleased, SchedCode]
  suppliers:
    list: /odata/{company}/Erp.BO.VendorSvc/Vendors
    fields: [VendorNum, VendorID, Name, Address1, City, State, Country]
  bom:
    list: /odata/{company}/Erp.BO.EngWorkBenchSvc/ECORevs
polling_interval_minutes: 15
rate_limit_requests_per_minute: 60

# ETL script for loading historical PO data into PostgreSQL

cat > /opt/supply-chain-agent/crewai/scripts/historical_etl.py << 'PYEOF'
import os
import requests
import psycopg2
from psycopg2.extras import execute_values
from datetime import datetime, timedelta
import json
import logging

logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)

# Configuration
ERP_BASE_URL = os.environ['ERP_API_BASE_URL']
ERP_API_KEY = os.environ['ERP_API_KEY']
DB_CONN = f"host=localhost port=5432 dbname=supply_chain_agent user=scm_admin password={os.environ['POSTGRES_PASSWORD']}"

def fetch_erp_data(endpoint, params=None):
    """Generic ERP API fetcher with pagination."""
    headers = {'X-API-Key': ERP_API_KEY, 'Content-Type': 'application/json'}
    all_records = []
    url = f"{ERP_BASE_URL}{endpoint}"
    while url:
        resp = requests.get(url, headers=headers, params=params)
        resp.raise_for_status()
        data = resp.json()
        all_records.extend(data.get('value', []))
        url = data.get('@odata.nextLink')
        params = None  # nextLink includes params
        logger.info(f"Fetched {len(all_records)} records so far...")
    return all_records

def load_suppliers(conn):
    """Load supplier master data."""
    suppliers = fetch_erp_data('/odata/COMPANY/Erp.BO.VendorSvc/Vendors', 
                                {'$select': 'VendorNum,VendorID,Name'})
    with conn.cursor() as cur:
        execute_values(cur, 
            "INSERT INTO suppliers (supplier_id, supplier_name) VALUES %s ON CONFLICT (supplier_id) DO UPDATE SET supplier_name = EXCLUDED.supplier_name",
            [(str(s['VendorNum']), s['Name']) for s in suppliers])
    conn.commit()
    logger.info(f"Loaded {len(suppliers)} suppliers")

def load_purchase_orders(conn):
    """Load 3 years of PO history."""
    cutoff = (datetime.now() - timedelta(days=1095)).strftime('%Y-%m-%dT00:00:00Z')
    pos = fetch_erp_data('/odata/COMPANY/Erp.BO.POSvc/POs',
                          {'$filter': f"OrderDate ge {cutoff}",
                           '$select': 'PONum,VendorNum,OrderDate,DueDate,PromiseDt,OpenOrder'})
    with conn.cursor() as cur:
        for po in pos:
            cur.execute("""
                INSERT INTO purchase_orders (po_id, erp_po_number, supplier_id, order_date, 
                    requested_delivery_date, confirmed_delivery_date, current_status)
                VALUES (%s, %s, %s, %s, %s, %s, %s)
                ON CONFLICT (po_id) DO NOTHING
            """, (
                str(po['PONum']), str(po['PONum']), str(po['VendorNum']),
                po['OrderDate'][:10], po['DueDate'][:10],
                po.get('PromiseDt', po['DueDate'])[:10] if po.get('PromiseDt') else None,
                'open' if po.get('OpenOrder', True) else 'closed'
            ))
    conn.commit()
    logger.info(f"Loaded {len(pos)} purchase orders")

def calculate_supplier_metrics(conn):
    """Calculate baseline metrics per supplier."""
    with conn.cursor() as cur:
        cur.execute("""
            UPDATE suppliers s SET
                avg_lead_time_days = sub.avg_lt,
                lead_time_std_dev = sub.std_lt,
                on_time_delivery_rate = sub.otd
            FROM (
                SELECT supplier_id,
                    AVG(actual_receipt_date - order_date) as avg_lt,
                    STDDEV(actual_receipt_date - order_date) as std_lt,
                    (COUNT(*) FILTER (WHERE delay_days <= 0)::NUMERIC / NULLIF(COUNT(*),0) * 100) as otd
                FROM purchase_orders
                WHERE actual_receipt_date IS NOT NULL
                GROUP BY supplier_id
            ) sub
            WHERE s.supplier_id = sub.supplier_id
        """)
    conn.commit()
    logger.info("Supplier baseline metrics calculated")

if __name__ == '__main__':
    conn = psycopg2.connect(DB_CONN)
    try:
        load_suppliers(conn)
        load_purchase_orders(conn)
        calculate_supplier_metrics(conn)
        logger.info("Historical ETL complete!")
    finally:
        conn.close()
PYEOF

sudo apt install -y python3-pip python3-venv
cd /opt/supply-chain-agent/crewai
python3 -m venv venv
source venv/bin/activate
pip install requests psycopg2-binary pyyaml

source /opt/supply-chain-agent/.env
python scripts/historical_etl.py

mkdir -p /opt/supply-chain-agent/crewai/{agents,tools,tasks,config}

cat > /opt/supply-chain-agent/crewai/requirements.txt << 'EOF'
crewai[tools]==0.86.0
langchain-openai==0.3.0
psycopg2-binary==2.9.9
requests==2.32.3
pyyaml==6.0.2
python-dotenv==1.0.1
pydantic==2.9.0
EOF

cat > /opt/supply-chain-agent/crewai/Dockerfile << 'DOCKEREOF'
FROM python:3.12-slim
WORKDIR /app
COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt
COPY . .
CMD ["python", "main.py"]
DOCKEREOF

cat >> /opt/supply-chain-agent/docker-compose.yml << 'EOF'

  crewai-agents:
    build:
      context: ./crewai
      dockerfile: Dockerfile
    container_name: scm-crewai
    restart: unless-stopped
    environment:
      - OPENAI_API_KEY=${OPENAI_API_KEY}
      - OPENAI_API_BASE=https://<azure-openai-endpoint>.openai.azure.com/
      - OPENAI_MODEL_NAME=gpt-5.4
      - POSTGRES_HOST=postgres
      - POSTGRES_PORT=5432
      - POSTGRES_DB=supply_chain_agent
      - POSTGRES_USER=scm_admin
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
      - ERP_API_BASE_URL=${ERP_API_BASE_URL}
      - ERP_API_KEY=${ERP_API_KEY}
      - TEAMS_WEBHOOK_URL=${TEAMS_WEBHOOK_URL}
      - AGENT_MODE=monitor_and_recommend
      - SCHEDULE_CHANGE_THRESHOLD_DAYS=3
      - AUTO_EXECUTE_ENABLED=false
      - POLLING_INTERVAL_MINUTES=15
    volumes:
      - ./crewai:/app
      - ./logs:/app/logs
    depends_on:
      postgres:
        condition: service_healthy
    links:
      - postgres

EOF

cd /opt/supply-chain-agent
docker compose build crewai-agents
docker compose up -d crewai-agents

docker logs scm-crewai --tail 50

curl -X POST ${TEAMS_WEBHOOK_URL} \
  -H 'Content-Type: application/json' \
  -d '{
    "@type": "MessageCard",
    "@context": "http://schema.org/extensions",
    "themeColor": "FF0000",
    "summary": "Supply Chain Agent Test",
    "sections": [{
      "activityTitle": "🤖 Supply Chain AI Agent - Test Notification",
      "activitySubtitle": "Agent system connectivity verified",
      "facts": [
        {"name": "Status", "value": "Connected"},
        {"name": "Server", "value": "scm-agent-01"},
        {"name": "Timestamp", "value": "'$(date -u +%Y-%m-%dT%H:%M:%SZ)'"}
      ],
      "markdown": true
    }]
  }'

cat >> /opt/supply-chain-agent/docker-compose.yml << 'EOF'

  prometheus:
    image: prom/prometheus:latest
    container_name: scm-prometheus
    restart: unless-stopped
    volumes:
      - ./prometheus/prometheus.yml:/etc/prometheus/prometheus.yml
      - ./prometheus/data:/prometheus
    ports:
      - '9090:9090'
    command:
      - '--config.file=/etc/prometheus/prometheus.yml'
      - '--storage.tsdb.retention.time=90d'

  grafana:
    image: grafana/grafana-oss:latest
    container_name: scm-grafana
    restart: unless-stopped
    environment:
      - GF_SECURITY_ADMIN_USER=admin
      - GF_SECURITY_ADMIN_PASSWORD=${GRAFANA_ADMIN_PASSWORD}
      - GF_SERVER_ROOT_URL=https://grafana.${CLIENT_DOMAIN}
    volumes:
      - ./grafana/data:/var/lib/grafana
      - ./grafana/provisioning:/etc/grafana/provisioning
    ports:
      - '3000:3000'
    depends_on:
      - prometheus
      - postgres

EOF

cat > /opt/supply-chain-agent/prometheus/prometheus.yml << 'PROMEOF'
global:
  scrape_interval: 30s
  evaluation_interval: 30s

scrape_configs:
  - job_name: 'node-exporter'
    static_configs:
      - targets: ['host.docker.internal:9100']
  - job_name: 'postgres-exporter'
    static_configs:
      - targets: ['postgres-exporter:9187']
  - job_name: 'crewai-metrics'
    static_configs:
      - targets: ['crewai-agents:8000']
    metrics_path: /metrics
PROMEOF

mkdir -p /opt/supply-chain-agent/grafana/provisioning/datasources
cat > /opt/supply-chain-agent/grafana/provisioning/datasources/datasources.yml << 'DSEOF'
apiVersion: 1
datasources:
  - name: PostgreSQL
    type: postgres
    url: postgres:5432
    database: supply_chain_agent
    user: scm_admin
    secureJsonData:
      password: ${POSTGRES_PASSWORD}
    jsonData:
      sslmode: disable
      maxOpenConns: 5
      maxIdleConns: 2
  - name: Prometheus
    type: prometheus
    url: http://prometheus:9090
    isDefault: true
DSEOF

echo "GRAFANA_ADMIN_PASSWORD=$(openssl rand -base64 16)" >> .env

docker compose up -d prometheus grafana

curl -s http://localhost:3000/api/health

docker exec scm-n8n n8n import:workflow --input=/app/workflows/erp_polling.json
docker exec scm-n8n n8n import:workflow --input=/app/workflows/delay_detection.json
docker exec scm-n8n n8n import:workflow --input=/app/workflows/human_approval.json

from crewai import Agent
from tools.erp_connector import ERPWorkOrderTool, ERPBOMTool, ERPInventoryTool
from tools.database_connector import SupplierMetricsTool
from tools.llm_analyzer import DelayImpactAnalysisTool

def create_delay_analyzer_agent():
    return Agent(
        role='Supply Chain Delay Impact Analyst',
        goal=(
            'For each detected supplier delay, perform a comprehensive impact analysis by: '
            '(1) Identifying all Work Orders that depend on the delayed material via BOM explosion, '
            '(2) Checking current inventory levels to determine if safety stock can absorb the delay, '
            '(3) Calculating the production impact window — which production days are affected, '
            '(4) Assessing customer delivery risk — which customer orders are at risk of late delivery, '
            '(5) Assigning a severity score (Critical/High/Medium/Low) based on customer impact and '
            'financial exposure.'
        ),
        backstory=(
            'You are a production planning expert who understands MRP logic, BOM structures, '
            'and the cascading effects of material shortages in discrete manufacturing. You know '
            'that not all delays are equal — a 5-day delay on a commodity fastener with 30 days '
            'of safety stock is a non-event, while a 1-day delay on a sole-sourced machined '
            'component on the critical path can halt an entire production line. You always '
            'check inventory levels before escalating.'
        ),
        tools=[
            ERPWorkOrderTool(),
            ERPBOMTool(),
            ERPInventoryTool(),
            SupplierMetricsTool(),
            DelayImpactAnalysisTool()
        ],
        verbose=True,
        memory=True,
        max_iter=8,
        allow_delegation=False
    )

from crewai import Task

def create_impact_analysis_task(agent, context=None):
    return Task(
        description=(
            'For each delay event provided by the Supplier Monitor Agent:\n\n'
            '1. MATERIAL TRACING: Use the BOM tool to find all parent assemblies and '
            'finished goods that require the delayed part. Build a dependency tree.\n\n'
            '2. INVENTORY CHECK: Query current on-hand inventory and allocated inventory '
            'for the delayed part. Calculate: available_buffer_days = (on_hand_qty - allocated_qty) '
            '/ daily_usage_rate. If available_buffer_days > delay_magnitude_days, the delay '
            'can be absorbed by inventory — downgrade severity.\n\n'
            '3. WORK ORDER IMPACT: Identify all open/released Work Orders that consume this part '
            'and have a scheduled start date within (delay_magnitude_days + available_buffer_days) '
            'from today. These are the directly impacted work orders.\n\n'
            '4. CUSTOMER IMPACT: For each impacted Work Order, identify the associated Sales Order '
            'and customer. Flag any work orders tied to key accounts or contractual delivery penalties.\n\n'
            '5. SEVERITY SCORING:\n'
            '   - CRITICAL: Customer delivery at risk, no safety stock buffer, critical path item\n'
            '   - HIGH: Customer delivery at risk but partial buffer exists, or high-value order\n'
            '   - MEDIUM: Production disruption likely but customer delivery achievable with overtime\n'
            '   - LOW: Inventory buffer absorbs delay, no customer impact expected\n\n'
            '6. OUTPUT: For each delay event, produce a structured impact report.'
        ),
        expected_output=(
            'A JSON array of impact assessments, each containing: delay_event_id, severity '
            '(critical/high/medium/low), affected_work_orders (list with WO IDs, part numbers, '
            'customer names, original due dates), inventory_buffer_days, production_days_at_risk, '
            'estimated_financial_impact_usd, recommended_urgency (immediate/today/this_week/monitor), '
            'and a human_readable_summary (2-3 sentences explaining the impact in plain language).'
        ),
        agent=agent,
        context=context
    )

# File: /opt/supply-chain-agent/crewai/agents/schedule_optimizer.py

from crewai import Agent
from tools.erp_connector import ERPWorkOrderTool, ERPCapacityTool
from tools.database_connector import ScheduleAdjustmentTool
from tools.llm_analyzer import ScheduleOptimizationTool

def create_schedule_optimizer_agent():
    return Agent(
        role='Production Schedule Optimizer',
        goal=(
            'Generate specific, implementable production schedule adjustments that minimize '
            'the impact of detected supplier delays on customer deliveries and overall plant '
            'efficiency. For each impacted work order, propose concrete date changes, work order '
            'resequencing, or alternative actions (overtime, alternate supplier, partial shipment '
            'to customer). Always present options with tradeoff analysis, never a single rigid solution.'
        ),
        backstory=(
            'You are a master production scheduler with deep expertise in finite capacity '
            'scheduling for discrete manufacturing. You understand that schedule changes have '
            'ripple effects — moving one work order affects machine utilization, labor allocation, '
            'and downstream assembly operations. You always consider: (1) Can we pull forward '
            'other work to fill the gap? (2) Is overtime a viable option? (3) Can we split the '
            'work order? (4) Is there an alternate supplier who can expedite? You present 2-3 '
            'options ranked by total cost impact.'
        ),
        tools=[
            ERPWorkOrderTool(),
            ERPCapacityTool(),
            ScheduleAdjustmentTool(),
            ScheduleOptimizationTool()
        ],
        verbose=True,
        memory=True,
        max_iter=10,
        allow_delegation=False
    )

# CrewAI task definition with option generation, constraint checking, and
# guardrails

# File: /opt/supply-chain-agent/crewai/tasks/optimization_tasks.py

from crewai import Task

def create_schedule_adjustment_task(agent, context=None):
    return Task(
        description=(
            'For each impact assessment with severity HIGH or CRITICAL:\n\n'
            '1. OPTION GENERATION: Create 2-3 schedule adjustment options:\n'
            '   Option A (Conservative): Delay affected work orders by the minimum necessary '
            'time, maintaining original sequence. Calculate new dates.\n'
            '   Option B (Optimized): Resequence work orders to minimize customer impact — '
            'pull forward non-affected work to fill gaps, push delayed work to earliest feasible slot.\n'
            '   Option C (Aggressive): Propose overtime/weekend shifts or alternate sourcing '
            'to maintain original schedule. Estimate additional cost.\n\n'
            '2. CONSTRAINT CHECKING: For each option, verify:\n'
            '   - Machine/work center capacity is not exceeded (check ERP capacity tool)\n'
            '   - Required materials for resequenced work are available\n'
            '   - Labor constraints are considered (no more than 20%% overtime increase)\n'
            '   - Downstream assembly operations are not orphaned\n\n'
            '3. COST-BENEFIT: For each option estimate:\n'
            '   - Additional labor cost (overtime hours × avg rate)\n'
            '   - Customer penalty risk (if contractual penalties exist)\n'
            '   - Inventory carrying cost change\n'
            '   - Opportunity cost of displaced work\n\n'
            '4. RECOMMENDATION: Rank options and provide a clear recommendation with rationale.\n\n'
            '5. For MEDIUM severity: Generate a monitoring recommendation (no schedule change yet, '
            'but set a re-check date).\n\n'
            '6. For LOW severity: Log the event and note that no action is needed.\n\n'
            'GUARDRAILS: Never recommend changes that would delay a customer order by more than '
            'the original supplier delay. Never propose schedule changes more than 30 days out. '
            'Never modify work orders that are currently in-process (status = Started/Active).'
        ),
        expected_output=(
            'A JSON array of schedule adjustment recommendations, each containing: '
            'delay_event_id, severity, recommendation_type (reschedule/monitor/no_action), '
            'options (array of {option_label, affected_work_orders [{work_order_id, '
            'original_start, original_end, new_start, new_end, change_reason}], '
            'estimated_additional_cost, customer_delivery_impact, confidence_score}), '
            'recommended_option, recommendation_rationale, '
            'requires_human_approval (boolean — true for all CRITICAL and HIGH, '
            'configurable for MEDIUM).'
        ),
        agent=agent,
        context=context
    )

# File: /opt/supply-chain-agent/crewai/tools/erp_connector.py

import os
import json
import requests
import yaml
from crewai.tools import BaseTool
from pydantic import BaseModel, Field
from typing import Optional, List
import logging

logger = logging.getLogger(__name__)

# Load ERP configuration
def load_erp_config():
    config_path = os.path.join(os.path.dirname(__file__), '..', 'integrations', 'erp_config.yaml')
    with open(config_path, 'r') as f:
        return yaml.safe_load(f)

class ERPClient:
    """Generic ERP REST API client with adapter pattern."""
    
    def __init__(self):
        self.config = load_erp_config()
        self.base_url = os.environ.get('ERP_API_BASE_URL', self.config['base_url'])
        self.api_key = os.environ.get('ERP_API_KEY', '')
        self.company_id = self.config.get('company_id', '')
        self.session = requests.Session()
        self.session.headers.update({
            'Content-Type': 'application/json',
            'X-API-Key': self.api_key
        })
    
    def _resolve_endpoint(self, endpoint_template: str, **kwargs) -> str:
        """Replace placeholders in endpoint URLs."""
        url = endpoint_template.replace('{company}', self.company_id)
        for key, value in kwargs.items():
            url = url.replace(f'{{{key}}}', str(value))
        return f"{self.base_url}{url}"
    
    def get(self, endpoint_key: str, params: dict = None, **kwargs) -> dict:
        """Execute a GET request against the ERP API."""
        endpoints = self.config['api_endpoints']
        category, action = endpoint_key.split('.')
        url = self._resolve_endpoint(endpoints[category][action], **kwargs)
        
        resp = self.session.get(url, params=params, timeout=30)
        resp.raise_for_status()
        return resp.json()
    
    def patch(self, endpoint_key: str, data: dict, **kwargs) -> dict:
        """Execute a PATCH request to update ERP records."""
        endpoints = self.config['api_endpoints']
        category, action = endpoint_key.split('.')
        url = self._resolve_endpoint(endpoints[category][action], **kwargs)
        
        resp = self.session.patch(url, json=data, timeout=30)
        resp.raise_for_status()
        return resp.json()

erp_client = ERPClient()

class POQueryInput(BaseModel):
    status_filter: Optional[str] = Field(default='open', description='Filter: open, closed, all')
    supplier_id: Optional[str] = Field(default=None, description='Filter by supplier ID')
    days_ahead: Optional[int] = Field(default=30, description='Look-ahead window in days')

class ERPPurchaseOrderTool(BaseTool):
    name: str = 'query_purchase_orders'
    description: str = 'Queries the ERP system for Purchase Orders. Can filter by status (open/closed), supplier, and delivery date window.'
    args_schema: type[BaseModel] = POQueryInput
    
    def _run(self, status_filter: str = 'open', supplier_id: str = None, days_ahead: int = 30) -> str:
        from datetime import datetime, timedelta
        
        filters = []
        if status_filter == 'open':
            filters.append('OpenOrder eq true')
        
        cutoff = (datetime.now() + timedelta(days=days_ahead)).strftime('%Y-%m-%dT00:00:00Z')
        filters.append(f'DueDate le {cutoff}')
        
        if supplier_id:
            filters.append(f"VendorNum eq {supplier_id}")
        
        filter_str = ' and '.join(filters)
        fields = ','.join(erp_client.config['api_endpoints']['purchase_orders']['fields'])
        
        try:
            result = erp_client.get('purchase_orders.list', 
                                    params={'$filter': filter_str, '$select': fields, '$orderby': 'DueDate'})
            records = result.get('value', [])
            return json.dumps({'count': len(records), 'purchase_orders': records}, default=str)
        except Exception as e:
            logger.error(f'ERP PO query failed: {e}')
            return json.dumps({'error': str(e), 'count': 0, 'purchase_orders': []})

class WOQueryInput(BaseModel):
    job_num: Optional[str] = Field(default=None, description='Specific Work Order/Job number')
    part_number: Optional[str] = Field(default=None, description='Filter by part number')
    status: Optional[str] = Field(default='released', description='Filter: released, firm, active')

class ERPWorkOrderTool(BaseTool):
    name: str = 'query_work_orders'
    description: str = 'Queries the ERP for Work Orders/Job entries. Can filter by status, part number, or specific job number.'
    args_schema: type[BaseModel] = WOQueryInput
    
    def _run(self, job_num: str = None, part_number: str = None, status: str = 'released') -> str:
        filters = []
        if job_num:
            filters.append(f"JobNum eq '{job_num}'")
        if part_number:
            filters.append(f"PartNum eq '{part_number}'")
        if status == 'released':
            filters.append('JobReleased eq true and JobClosed eq false')
        
        filter_str = ' and '.join(filters) if filters else None
        fields = ','.join(erp_client.config['api_endpoints']['work_orders']['fields'])
        
        try:
            params = {'$select': fields, '$orderby': 'StartDate'}
            if filter_str:
                params['$filter'] = filter_str
            result = erp_client.get('work_orders.list', params=params)
            records = result.get('value', [])
            return json.dumps({'count': len(records), 'work_orders': records}, default=str)
        except Exception as e:
            logger.error(f'ERP WO query failed: {e}')
            return json.dumps({'error': str(e), 'count': 0, 'work_orders': []})

class SupplierQueryInput(BaseModel):
    vendor_id: Optional[str] = Field(default=None, description='Specific vendor ID')

class ERPSupplierTool(BaseTool):
    name: str = 'query_suppliers'
    description: str = 'Queries the ERP for supplier/vendor master data.'
    args_schema: type[BaseModel] = SupplierQueryInput
    
    def _run(self, vendor_id: str = None) -> str:
        try:
            params = {'$select': ','.join(erp_client.config['api_endpoints']['suppliers']['fields'])}
            if vendor_id:
                params['$filter'] = f"VendorNum eq {vendor_id}"
            result = erp_client.get('suppliers.list', params=params)
            return json.dumps({'suppliers': result.get('value', [])}, default=str)
        except Exception as e:
            return json.dumps({'error': str(e)})

class BOMQueryInput(BaseModel):
    part_number: str = Field(description='Part number to explode BOM for')

class ERPBOMTool(BaseTool):
    name: str = 'query_bom'
    description: str = 'Queries the Bill of Materials for a given part number, showing all parent assemblies and finished goods that depend on it.'
    args_schema: type[BaseModel] = BOMQueryInput
    
    def _run(self, part_number: str) -> str:
        try:
            result = erp_client.get('bom.list', params={'$filter': f"PartNum eq '{part_number}'"})
            return json.dumps({'bom_records': result.get('value', [])}, default=str)
        except Exception as e:
            return json.dumps({'error': str(e)})

class InventoryQueryInput(BaseModel):
    part_number: str = Field(description='Part number to check inventory for')

class ERPInventoryTool(BaseTool):
    name: str = 'query_inventory'
    description: str = 'Queries current inventory levels for a part number, including on-hand, allocated, and available quantities.'
    args_schema: type[BaseModel] = InventoryQueryInput
    
    def _run(self, part_number: str) -> str:
        try:
            # This endpoint varies significantly by ERP — adjust per client
            result = erp_client.get('purchase_orders.list', 
                                    params={'$filter': f"PartNum eq '{part_number}'", '$select': 'PartNum,OnHandQty,AllocatedQty,AvailableQty'})
            return json.dumps({'inventory': result.get('value', [])}, default=str)
        except Exception as e:
            return json.dumps({'error': str(e)})

class ERPCapacityTool(BaseTool):
    name: str = 'query_capacity'
    description: str = 'Queries work center capacity and utilization for scheduling feasibility checks.'
    
    def _run(self) -> str:
        try:
            result = erp_client.get('work_orders.list', 
                                    params={'$filter': 'JobReleased eq true and JobClosed eq false',
                                            '$select': 'JobNum,StartDate,DueDate,SchedCode'})
            return json.dumps({'capacity_data': result.get('value', [])}, default=str)
        except Exception as e:
            return json.dumps({'error': str(e)})

curl -X GET <ERP_API_URL>/odata/<company>/Erp.BO.POSvc/POs?$top=1

docker exec scm-postgres psql -U scm_admin -d supply_chain_agent -c "SELECT COUNT(*) FROM purchase_orders;"

SELECT supplier_id, supplier_name, avg_lead_time_days, on_time_delivery_rate
FROM suppliers
WHERE avg_lead_time_days IS NOT NULL
ORDER BY on_time_delivery_rate ASC
LIMIT 10;

docker exec scm-crewai python -c "from main import SupplyChainAgentCrew; c = SupplyChainAgentCrew(); c.execute_cycle()"

SELECT COUNT(*) FROM delay_events WHERE detected_at > NOW() - INTERVAL '1 hour';

curl -X POST $TEAMS_WEBHOOK_URL \
  -H "Content-Type: application/json" \
  -d "{\"@type\":\"MessageCard\",\"summary\":\"Test\",\"sections\":[{\"activityTitle\":\"Test Alert\"}]}"

SELECT agent_name, action_type, COUNT(*)
FROM agent_audit_log
GROUP BY agent_name, action_type;

# Stop the PostgreSQL container
docker stop scm-postgres

# After verifying graceful failure, restart the container
docker start scm-postgres

ls -la /opt/supply-chain-agent/.env

docker compose restart crewai-agents