Back to Portfolio
Data Engineering

Supply Chain Intelligence Center (SCIC) Manufacture Case Domain

August 2026 Manufacturing🏢 IndoPrima👤 Analytics & AI Agent Orchestrator
Supply Chain Intelligence Center (SCIC) Manufacture Case Domain

Enterprise decision intelligence platform featuring self-correcting LangGraph NL2SQL agents, MotherDuck OLAP, and real-time OEE & MRO spare-part forecasting.

1. Executive Summary & Project Mission

In large-scale automotive component manufacturing (e.g., leaf springs, coil springs, stabilizer bars), operational delays and unplanned machine downtime carry massive financial penalties. Industrial data is traditionally fragmented across legacy ERPs, Warehouse Management Systems (WMS), supplier procurement portals, and shopfloor IoT telemetry.

The Supply Chain Intelligence Center (SCIC) is an enterprise-grade AI decision intelligence platform designed to bridge these silos. Built on a Decoupled Cloud-Native Hybrid Intelligence architecture, SCIC converts multi-source industrial data into actionable, real-time insights.

Key Technical Achievements

  • Multi-Tier Stateful Agent (LangGraph): Orchestrated a 10-node state machine featuring dual self-correction loops, dynamic schema pruning across 34 tables, and multi-tier prompt specialization.
  • AST-Grounded Text-to-SQL Safety: Enforced 100% read-only AST parsing with SQLGlot and pre-execution catalog validation (CALL try_bind(?)) against MotherDuck, eliminating SQL hallucination and mutation vectors.
  • Sub-Second OLAP Query Latency: Leveraged MotherDuck (DuckDB Cloud) to execute complex multi-table aggregations (OEE 3-pillar calculations, Pareto downtime analysis, MRO lead-time simulation) in under 260 ms.
  • Human-in-the-Loop Governance: Built an immutable audit trail, explicit AI confidence scoring, and prescriptive recommendation cards with zero autonomous ERP modification.

2. Decoupled Hybrid System Architecture

+----------------------------------------------------------------------------------------------------+
|                                      FRONTEND LAYER (Next.js 15 & React 19)                         |
|   /dashboard — Executive Control Tower & OEE  |  /demand-intelligence — Smart ROP & Stock Balancing |
|   /chat — AI Copilot & NL2SQL Assistant      |  /agent-logs — Real-time Execution Audit Traces     |
+-------------------------------------------------+--------------------------------------------------+
                                                  | HTTPS REST API & SSE Token Streaming
                                                  v
+----------------------------------------------------------------------------------------------------+
|                                 APPLICATION BACKEND SERVICES (FastAPI)                             |
|   FastAPI REST Gateway • Pydantic v2 • SQLAlchemy 2.0 • ARQ Asynchronous Task Engine               |
|   Business Domain Services: OEE Engine (Avail/Perf/Qual), 3-Way Triad Matching, Dynamic Smart ROP  |
+-----------------------+----------------------------------------------------+-----------------------+
                        |                                                    |
                        v                                                    v
+-----------------------------------------------+   +------------------------------------------------+
|       OPERATIONAL STORE (Supabase PG)         |   |         ANALYTICAL STORE (MotherDuck OLAP)     |
|   - Supabase Auth (JWT & RBAC Policies)       |   |   - 34 Unified Analytical Tables               |
|   - Chat Sessions & Message Persistences      |   |   - Sub-second DuckDB Columnar Vectorized Engine|
|   - Immutable Audit Trail (Human Decisions)   |   |   - Pre-computed OEE & Reconciliation Views    |
+-----------------------------------------------+   +------------------------------------------------+
                        |                                                    |
                        +-----------------------+----------------------------+
                                                v
                        +-----------------------------------------------+
                        |          CACHE & QUEUE (Upstash Redis)        |
                        |   - Distributed Token-Bucket Rate Limiter     |
                        |   - Pre-computed KPI Cache Layer              |
                        +-----------------------------------------------+

End-to-end architectural topology of SCIC, highlighting the separation of OLAP analytics (MotherDuck) from transactional state persistence (Supabase) and async LLM orchestration.

Dual-Storage Strategy

  1. Analytical Data Warehouse (MotherDuck / DuckDB Cloud): Columnar, vectorized OLAP engine housing 34 tables (5,000+ IoT telemetry points, shift outputs, BOM mappings, logistics status). Sub-second joins and window functions without transactional lock contention.
  2. Operational & State Store (Supabase PostgreSQL): High-integrity OLTP database managing user authentication (RBAC: admin, manager, analyst, viewer), persistent chat sessions, execution traces, and audit logs.
  3. Caching & Rate Limiting (Upstash Redis): Distributed token-bucket limiter preventing API abuse and caching repeated metric aggregations.

3. Unified 34-Table Industrial Data Model

The data model unifies shopfloor manufacturing execution with end-to-end supply chain logistics across 8 domain modules (34 tables):

Module Breakdown

  • Master Data Harmonization (Golden Record): Resolves heterogeneous entity keys across ERP, WMS, and vendor portals (supplier_master, product_master, dim_machines, dim_skus, dim_spare_parts, dim_bom_compatibility).
  • Manufacturing Execution & OEE: Real-time logging of the 6 Big Losses (fact_downtime_logs, fact_production_outputs, fact_quality_inspections, fact_shift_manpower).
  • MRO Spare Part Readiness & IoT Condition Monitoring: High-frequency vibration, temperature, current, and RPM metrics (fact_machine_telemetry, fact_anomaly_events, fact_part_consumptions).
  • Supply Chain Fulfillment & 3-Way Triad: Purchase Orders, Delivery Orders, Invoices, and Port Logistics Telemetry.

4. Stateful Multi-Tier LangGraph Agent (Production NL2SQL)

To provide plant managers and supply chain directors with an autonomous yet deterministic analytics copilot, we implemented a custom state machine using LangGraph StateGraph.


Complete LangGraph workflow graph illustrating the 10 nodes, deterministic branch routing, and dual self-correction feedback loops.

4.1 State Schema Definition (AgentState)

class AgentState(TypedDict):
    # Session & Conversation Context
    messages: Annotated[List[BaseMessage], operator.add]
    session_id: str
    user_query: str
    augmented_history: Optional[str]
    last_executed_sql: Optional[str]
    
    # Tier 1: Router & Planner
    intent: Literal["ANALYTICS_QUERY", "FOLLOW_UP_INTERPRETATION", "CLARIFICATION_NEEDED", "OUT_OF_SCOPE", "CHIT_CHAT"]
    selected_domain: Literal["manufacturing", "supply_chain", "inventory_mro", "demand_balancing", "finance", "general"]
    normalized_query: Optional[str]
    
    # Tier 2: Domain Context & Pruning
    relevant_tables: List[str]
    schema_context: str
    business_glossary: str
    
    # Tier 3: SQL Generation & Sandboxed OLAP Execution
    generated_sql: Optional[str]
    sql_is_valid: bool
    sql_validation_error: Optional[str]
    sql_retry_count: int
    sql_result_records: Optional[List[Dict[str, Any]]]
    sql_execution_time_ms: float
    draft_answer: Optional[str]
    
    # Tier 4: Senior Expert Critic & Refinement
    expert_critique_score: float
    expert_critique_status: Literal["PASSED", "NEEDS_REFINEMENT"]
    expert_critique_feedback: Optional[str]
    expert_recommendations: List[str]
    
    # Telemetry & UI Payload
    action_steps_trace: Annotated[List[str], operator.add]
    final_payload: Optional[Dict[str, Any]]

4.2 Dual Self-Correction Feedback Loops

  1. Loop 1: AST Safety & Catalog Binder Loop (sql_validator →→ sql_generator)
    • Query is parsed into an Abstract Syntax Tree (AST) using SQLGlot.
    • Any mutation node (Insert, Update, Delete, Drop, Alter, Pragma) is immediately rejected.
    • The query is tested with MotherDuck's CALL try_bind(?). If a column or table is invalid, the exact compiler diagnostic is routed back to sql_generator for an automatic retry (capped at 3 iterations).
  2. Loop 2: Senior Manufacturing Expert Critic (senior_expert_validator →→ expert_refinement)
    • Evaluates whether the initial synthesis accurately links root causes (e.g., correlating an OEE Availability drop with specific changeover or hydraulic calibration downtime).
    • If the quality score falls below 80.0, the critic injects operational domain feedback and triggers Kaizen recommendations before UI rendering.

5. Core Platform Features & User Experience

Feature 1: Supply Chain & Manufacturing Control Tower (/dashboard)

The executive dashboard serves as the central command cockpit for Operations Directors and Plant GMs, continuously calculating a composite health index across manufacturing and delivery nodes.

Control Tower UI showing the Composite Health Index (95.2/100 - Healthy) with its 4-factor contribution breakdown and AI Executive Prescriptive Summary.

Composite Health Score Formulation

Health Index=0.35×OTD+0.30×Prod Achievement+0.20×(100−Risk Penalty)+0.15×Forecast Accuracy

  • On-Time Delivery (OTD): 91.7% (+32.1 pts contribution)
  • Production Achievement: 106.1% (+31.8 pts contribution)
  • Risk Penalty Reduction: 12 pts penalty (+17.6 pts contribution)
  • Demand Forecast Accuracy (100 - MAPE): 91.2% (+13.7 pts contribution)
  • AI Executive Summary: Synthesizes multi-source events (e.g., 14.5 hours hydraulic calibration downtime at Line 3 Karawang + 42h Tanjung Perak port customs delay) and prescribes actionable mitigation (emergency DO batch rerouting via land logistics + preventive hydraulic maintenance).

Feature 2: Demand & Spare Part Decision Intelligence (/demand-intelligence)

Unplanned downtime in automotive stamping and heat-treatment is predominantly caused by missing MRO spare parts. This module combines predictive consumption forecasting with dynamic stock balancing.

Demand Intelligence module displaying stockout risks, 91.8% forecast accuracy, MRO readiness levels (90.0%), and interactive Reorder Point (ROP) tabular controls.

Smart Reorder Point (ROP) Formula

Smart ROP=(Avg Daily Part Consumption×Supplier Lead Time)+Dynamic Safety Stock BufferSmart ROP=(Avg Daily Part Consumption×Supplier Lead Time)+Dynamic Safety Stock Buffer

  • Criticality Categorization: Highlights MRO items needing replenishment (e.g., High-Temperature Conveyor Mesh Belts, Tungsten Carbide Nozzles, Inductive Proximity Sensors).
  • Inter-Warehouse Stock Balancing: Evaluates Days of Supply (DOS) imbalances (identifying overstocked warehouses with DOS>90 daysDOS>90 days vs. starved warehouses with DOS<30 daysDOS<30 days) to generate stock transfer orders before triggering new external procurement.

Feature 3: Self-Correcting NL2SQL AI Assistant Copilot (/chat)

The AI Assistant Copilot provides natural language querying over 34 OLAP tables with complete transparency over execution logic and data lineage.

AI Copilot answering an OEE breakdown query, displaying the 7-step Execution Action Trace, AST/try_bind validation pass, 259.1ms MotherDuck execution, Senior Expert Critic score (84.0/100), and structured markdown table.

Real-Time Execution Trace Walkthrough

  1. Tier 1 Router: Intent identified as [ANALYTICS_QUERY] in Domain [manufacturing]. Query normalized: "Berapa OEE per lini produksi beserta penyebab downtime..."
  2. Tier 2 Domain Specialist: Pruned schema context to 13 relevant manufacturing tables and KPI definitions.
  3. Tier 3 SQL Generator: Generated targeted DuckDB analytical aggregation with zero hallucinated joins.
  4. Tier 3 Validator: Passed SQLGlot AST read-only validation & MotherDuck try_bind() with 100% compliance.
  5. MotherDuck Engine: Executed sub-second scan returning 4 rows in 259.1 ms.
  6. Synthesis Engine: Generated adaptive narrative breakdown (Lini Leaf Spring Light Duty 2: OEE 88.2%, Changeover 333.5m; Lini Hot Coiling Spring 1: OEE 87.7%, Changeover 1,304.1m).
  7. Tier 4 Senior Expert Critic: Evaluated grounding and domain sanity, issuing a 84.0 / 100 [PASSED] quality score.

6. Safety, Reliability & Human-in-the-Loop Governance

Enterprise AI deployment requires strict operational boundaries to prevent autonomous errors:

[ Natural Language Query ]
            │
            ▼
┌───────────────────────────────────────┐
│   Layer 1: SQLGlot AST Safety Parse   │ ──► [ Mutation Blocked ] (DROP / UPDATE / INSERT)
└───────────────────────────────────────┘
            │  (SELECT / CTE Only)
            ▼
┌───────────────────────────────────────┐
│ Layer 2: MotherDuck CALL try_bind(?)  │ ──► [ Catalog Error ] ──► (Self-Correction Retry)
└───────────────────────────────────────┘
            │  (Valid Schema Binding)
            ▼
┌───────────────────────────────────────┐
│  Layer 3: Read-Only Connection Pool   │ ──► Sub-second Aggregation
└───────────────────────────────────────┘
            │
            ▼
┌───────────────────────────────────────┐
│ Layer 4: Senior Expert Critic (LLM)   │ ──► Quality Score >= 80
└───────────────────────────────────────┘
            │
            ▼
┌───────────────────────────────────────┐
│ Layer 5: Human Review & Approval Gate │ ──► Writes to Supabase `audit_log`
└───────────────────────────────────────┘
  1. Strict Read-Only Execution Boundary: Backend database connectors use read-only session configurations; SQLGlot AST walker blocks any DDL/DML before transmission.
  2. Confidence & Explainability Metadata: Every response returns explicit data lineage, list of queried physical tables, execution latency, and confidence scores.
  3. Human-in-the-Loop Approval: No procurement orders or warehouse transfers are executed automatically. Managers approve, adjust, or reject proposals via dedicated UI modals, recording user ID, timestamp, original AI proposal, and decision notes into an immutable audit_log.

7. Engineering Takeaways

  • Specialized StateGraph beats Monolithic ReAct: Multi-tier separation of concerns (Routing →→ Pruning →→ Generation →→ AST Validation →→ Synthesis →→ Critic) drastically improves deterministic query execution compared to generic single-agent loops.
  • AST Parsing as a First-Class Guardrail: Using SQLGlot to parse queries into semantic AST nodes before database binding guarantees safety far beyond regex or prompt instructions.
  • Server-Sent Events (SSE) for Real-Time UX: Streaming the multi-step execution trace token-by-token eliminates user perception of latency during complex analytical workflows.
Business IntelligenceData AnalyticsAnalytics EngineeringData EngineeringAI AgentNLP & Text AnalyticsData Pipeline
SQL AgentManufactureSupply ChainOLAP
Tech Stack
NextJSFastAPIPydanticLanggraphShadcn
View Live