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_purchase_orders_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)

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

User Interactions (Browser → API → Database)

1. User authenticates via MSAL → receives JWT token
2. Frontend sends API requests with JWT in X-User-Token header
3. API validates token, extracts user role and vendor code
4. API executes SQL queries with role-based filtering
5. 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, PO_Manager, Admin) with hierarchy: Admin > PO_Manager > Vendor. Vendors are scoped to their own company's POs via billing_company filtering.