System Architecture¶

Overview¶

graph TB
    subgraph External
        CIN7[Cin7 ERP<br/>Source of Truth]
    end

    subgraph Data Pipeline
        AIRFLOW[Apache Airflow<br/>Nightly ETL]
    end

    subgraph Azure Cloud
        DB[(Azure SQL Database<br/>Cin7_PO_Headers<br/>Cin7_PO_Lines)]
        API[Azure Functions<br/>Node.js API]
        SWA[Azure Static Web Apps<br/>React Frontend]
    end

    subgraph Auth
        AAD[Microsoft Entra ID<br/>Azure AD B2C]
    end

    CIN7 -->|SP-API / Export| AIRFLOW
    AIRFLOW -->|MERGE Upsert| DB
    DB --> API
    API --> SWA
    AAD -->|MSAL Auth| SWA
    AAD -->|JWT Validation| API

Technology Stack¶

Layer	Technology	Purpose
Frontend	React + TypeScript + Tailwind CSS	User interface
API	Azure Functions (Node.js)	Backend REST API
Database	Azure SQL Database	Data storage
Auth	Microsoft Entra ID (Azure AD B2C) + MSAL	Authentication
Hosting	Azure Static Web Apps	Frontend + API hosting
Data Pipeline	Apache Airflow	Nightly Cin7 → SQL sync
Source Control	GitHub	Code repository

Data Flow¶

Nightly Sync (Cin7 → Database)¶

Airflow DAG triggers at scheduled time
Extracts PO data from Cin7 via SP-API
Transforms and loads into Azure SQL using MERGE upsert
Only updates Cin7-sourced columns — preserves app-owned columns (vendor replies, status, etc.)

User Interactions (Browser → API → Database)¶

User authenticates via MSAL → receives JWT token
Frontend sends API requests with JWT in X-User-Token header
API validates token, extracts user role and vendor code
API executes SQL queries with role-based filtering
Response returned to frontend for rendering

Key Design Decisions¶

Dual-Column Pattern¶

Each editable field has two columns: the original Cin7 value and the vendor's reply value. Example: xfd (original) and vendor_reply_xfd (vendor edit). This preserves the source of truth while tracking changes.

State Machine Engine¶

All status transitions go through a single executeTransition() function that validates against the Ref_PO_Transitions table. This ensures no invalid status changes can occur.

Role-Based Access Control¶

Three roles (Vendor, Manager, Admin) with hierarchy: Admin > Manager > Vendor. Vendors are scoped to their own company's POs via billing_company filtering.

AI Chat Architecture¶

The Data Assistant strictly operates over the Azure Functions API utilizing OpenAI's models (gpt-4o-mini). It includes comprehensive PII masking, an Agentic SQL fallback engine, and an observability loop to continually capture structured analytical data that guides improvement.

sequenceDiagram
    participant User as Frontend (User)
    participant API as Azure Functions (/api/chat)
    participant DB as Azure SQL Database
    participant LLM as OpenAI (gpt-4o-mini)

    User->>API: 1. Send Question
    Note over API: 2. Enforce RBAC & Mask PII
    API->>LLM: 3. Pass 1: Request tool calls
    LLM-->>API: Returns standard function or 'run_analysis'

    alt Standard Tool (e.g., get_po_count)
        API->>DB: 4a. Execute parameterized query (scoped)
    else Agentic SQL Fallback (run_analysis)
        Note over API: 4b. Validate SQL Safety & Scope
        API->>DB: 4c. Execute dynamically generated SQL
    end

    DB-->>API: Returns records
    Note over API: 5. Backend Rule Engine <br/>generates Custom ECharts config
    API->>LLM: 6a. Pass 2: Send data for summarization
    LLM-->>API: 6b. Returns natural language summary
    Note over API: 7. Zod Output Guardrails & Unmask PII
    API-->>User: 8. Returns Response (Typewriter Text + Chart Data)
    Note over API: 9. Telemetry logging saves to DB <br/>with auto_score evaluation