// CASE_STUDY_01

SALES
FORECAST
AI

ROLE
Tech Lead & Product Architect
CORE STACK
Python / AWS ECS / React / LLM (Text-to-SQL)
STATUS
PRODUCTION (Core)
// 01. THE_FRICTION

THE "60-DAY LAG"

"You can't steer a ship if you're looking at where you were two months ago. C-Level executives were making critical decisions based on 60-day-old data."

The status quo was a nightmare of 'Spreadsheet Fatigue'. The previous process relied on a manual ETL (Extract, Transform, Load) where analysts copy-pasted data from ERPs into fragile Excel sheets. It was slow, prone to human error, and impossible to audit. The goal was to destroy the spreadsheets and establish a Single Source of Truth.

// 02. THE_ARCHITECTURE

DECOUPLED PREDICTION PIPELINE

[ Client ERP ] 
      |
      v
[ Normalization Worker (Python) ]
      |
      v
[ AWS RDS (Unified Data) ] <---> [ ML Prediction Service (AWS ECS) ]
      ^
      |
      v
 [ Python BFF ]
      |
      v
[ React Dashboard ]

We designed for Read-Performance. The heavy lifting (Ingestion & Prediction) happens asynchronously in isolated containers on AWS ECS.

Normalization Worker: Python scripts that sanitize the messy ERP data before it hits our clean schema.

ML Engine: Reads history, generates forecasts, and writes them back to RDS.

The BFF: The frontend never touches the raw data. It consumes a pre-computed view via a Python Backend-for-Frontend, ensuring the dashboard loads instantly even with millions of rows.

// 03. R&D_INTELLIGENCE

NATURAL LANGUAGE TO SQL (TEXT-TO-SQL)

Dashboards are great, but Directors have specific questions that filters can't answer. I developed a Text-to-SQL layer using LLMs to democratize data access.

1
SANITIZATION

User input is cleaned to prevent prompt injection.

2
SCHEMA MAPPING

The LLM receives only the relevant table definitions (not the data).

3
EXECUTION

The system executes a ReadOnly query against the RDS.

# Simplified logic for the Text-to-SQL agent
def generate_safe_query(user_question: str):
    # 1. Map natural language to strict schema
    sql_query = llm_chain.run(
        question=user_question, 
        context=db_schema_metadata
    )
    
    # 2. Security Guardrail
    if "DROP" in sql_query or "DELETE" in sql_query:
        raise SecurityException("Unsafe operation detected.")
        
    return db.execute_readonly(sql_query)
// 04. UX_LEADERSHIP

TRUST OVER AESTHETICS

"If the Director doesn't trust the number, the software is useless." As UX Lead, I mentored the team to prioritize Data Legibility over flashy UI.

  • CONFIDENCE INTERVALS

    We don't just show a number; we show the ML model's certainty (e.g., "R$ 100k ± 5%").

  • HEATMAPS

    Replacing rows of numbers with regional heatmaps for instant cognitive processing.

  • LATENCY

    By decoupling the ETL, the dashboard went from "loading..." to instant.

PLACEHOLDER: SALES_DASHBOARD_HEATMAP
70%
COST REDUCTION

Eliminated the need for a dedicated "Data Extraction Squad". The automated Python ETL handles what used to take a team of 3 analysts.

LIVE
DATA SYNC

Moved from semi-annual reports to real-time dashboards, allowing branches to adjust goals mid-month.

100%
PLUG & PLAY

Standardized data model allows us to onboard a new client's ERP in days, not months, without changing a single line of code.