# stagepfe2025/custom-etl-application architecture

> Architecture diagram of the GitHub repository stagepfe2025/custom-etl-application, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-06-04). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/stagepfe2025/custom-etl-application
- Repository: https://github.com/stagepfe2025/custom-etl-application

## Overview

##### Project type and purpose
- This is a full-stack, enterprise-grade ETL (Extract-Transform-Load) web application written in Python.
- It uses Flask to expose a RESTful API and UI, Celery for background/parallel tasks, Redis as broker and cache, and Postgres/Elasticsearch for storage.

##### File structure
- Top level:
- Dockerfile, docker-compose.dev.yml, manage.py → containerized deployment and management
- celery_app.py, config.py → Celery and application configuration
- app/ → Flask application:
– app/api → HTTP endpoints (admin/routes)
– app/main → web UI routes and template rendering
– app/tasks → Celery ETL and scheduled tasks
– app/extractors, app/transformers, app/loaders → modular ETL pipeline
– app/extensions.py → Flask extensions (DB, Redis, Celery)
– app/templates → Jinja2 templates for dashboard and monitoring
- docs/ and TROUBLESHOOTING.md provide deep dives on scaling, task management, large file uploads, etc.
- tests/ and test_*.py show the integration/ unit tests around APIs and monitoring.

##### Extract additional insights from the README
- Architecture overview shows three main containers: Web Service, Celery Workers (standard + chunked), Redis (stats + queues) and a Flower monitor.
- Processing workflow stages: Upload → Validate/Chunk → Queue → Email Discovery (4 finders in parallel) → Transform/Enrich → Load → Report.
- Data stores: Postgres for relational outputs, Elasticsearch for search/analytics, Redis for real-time stats and job queues.

a. Main components:
1. Client (Browser or API consumer)
2. Web Service (Flask App)
3. Task Broker & Cache (Redis)
4. Celery Workers (Standard + Chunked + Scheduled)
5. Extractors (File, S3, API)
6. Transformers (Company enrichment, Data cleaning)
7. Loaders (Postgres Loader, Elasticsearch Loader, CSV Loader)
8. External Systems (S3 buckets, external APIs, WHOIS services, SMTP servers)
9. Databases: Postgres, Elasticsearch
10. Monitoring: Flower, Live Dashboard (served by Flask), Logs

b. Component interactions / data flow:
1. Client → HTTP POST /api/v1/jobs → Web Service
2. Web Service
- Validates file (CSV/JSON), chunks large files
- Pushes ETL tasks into Redis queue via Celery
3. Celery Workers fetch tasks from Redis
- Extractors read from file system, S3, or external APIs
- Transformers enrich and clean data
- Email discovery sub-tasks run in parallel using multiple methods, push stats to Redis
- Loaders write results to Postgres & Elasticsearch
4. Web Service / Dashboard polls Redis & DB to show real-time stats, job status, logs
5. Flower (Celery monitor) connects to Redis to show worker status & queue health

c. Architectural patterns & design principles:
- Modular ETL pipeline: extract → transform → load components in separate modules
- Producer–Consumer (Flask produces tasks, Celery consumes)
- Parallel/Asynchronous processing to maximize throughput
- MVC for Flask (routes → templates → extensions)
- Caching & pooling for performance (Redis cache, HTTP session pooling)

d. Key technologies/frameworks:
- Flask (app/api, app/main)
- Celery for distributed tasks
- Redis as both broker and in-memory stats store
- Postgres & Elasticsearch for persistent storage
- Jinja2 templates for UI
- Docker & docker-compose for container orchestration
- Python libraries: requests (pooled HTTP), whois, smtplib

##### Detail
- Both standard and chunked worker pools if desired

## Diagram (Mermaid)

```mermaid
flowchart TB
    %% Frontend/UI & API Layer
    subgraph "Web Service (Flask)" 
        direction TB
        WS_Init["__init__.py"]:::compute
        WS_API["routes.py (API)"]:::compute
        WS_Admin["admin.py (API Admin)"]:::compute
        WS_Main["routes.py (UI)"]:::compute
        WS_Manage["manage.py"]:::compute
    end

    %% Asynchronous Processing Layer
    subgraph "Task Broker & Workers"
        direction TB
        Redis["Redis (Broker & Cache)"]:::data
        subgraph "Celery Workers"
            direction TB
            CW_Standard["etl_tasks.py"]:::compute
            CW_Scheduled["scheduled_tasks.py"]:::compute
        end
    end

    %% ETL Pipeline Components
    subgraph "ETL Pipeline"
        direction TB
        subgraph "Extractors"
            direction TB
            Ext_File["file_extractor.py"]:::compute
            Ext_S3["s3_extractor.py"]:::compute
            Ext_API["api_extractor.py"]:::compute
            Ext_Feed["feed_source.py"]:::compute
        end
        subgraph "Transformers"
            direction TB
            Trans_Company["company_transformer.py"]:::compute
            Trans_Customer["customer_data_cleaner.py"]:::compute
            Trans_Sensitive["sensitive_data_cleaner.py"]:::compute
            Trans_Map["french_city_to_dep_to_reg.json"]:::compute
        end
        subgraph "Loaders"
            direction TB
            Load_PG["postgres_loader.py"]:::compute
            Load_ES["elasticsearch_loader.py"]:::compute
            Load_CSV["csv_loader.py"]:::compute
        end
    end

    %% Storage Layer
    subgraph "Data Stores"
        direction TB
        Postgres["Postgres"]:::data
        Elasticsearch["Elasticsearch"]:::data
    end

    %% Monitoring & Dashboard
    subgraph "Monitoring"
        direction TB
        Flower["Flower"]:::monitor
        Dashboard["Live Dashboard"]:::monitor
    end

    %% External Systems
    subgraph "External Services"
        direction TB
        S3["S3 Buckets"]:::external
        ExtAPI["External APIs"]:::external
        WHOIS["WHOIS Service"]:::external
        SMTP["SMTP Server"]:::external
    end

    %% Connections
    Client["Client (Browser/API)"]:::external -->|"POST /api/v1/jobs"| WS_API
    WS_API -->|"validate & chunk"| Redis
    WS_API -->|"enqueue tasks"| Redis
    Redis -->|"fetch tasks"| CW_Standard
    Redis -->|"fetch tasks"| CW_Scheduled
    CW_Standard -->|"extract"| Ext_File
    CW_Standard -->|"extract"| Ext_S3
    CW_Standard -->|"extract"| Ext_API
    CW_Standard -->|"extract"| Ext_Feed
    CW_Standard -->|"transform"| Trans_Company
    CW_Standard -->|"transform"| Trans_Customer
    CW_Standard -->|"transform"| Trans_Sensitive
    CW_Standard -->|"load"| Load_PG
    CW_Standard -->|"load"| Load_ES
    CW_Standard -->|"load"| Load_CSV
    Load_PG -->|"write"| Postgres
    Load_ES -->|"write"| Elasticsearch
    WS_Main -->|"poll stats"| Redis
    WS_Main -->|"read/job status"| Postgres
    Flower -->|"monitor queues"| Redis
    Dashboard -->|"display stats"| Redis
    Dashboard -->|"display logs"| Postgres
    CW_Standard -->|"discover email"| WHOIS
    CW_Standard -->|"notify"| SMTP

    %% Click Events
    click WS_Init "https://github.com/stagepfe2025/custom-etl-application/blob/master/app/__init__.py"
    click WS_API "https://github.com/stagepfe2025/custom-etl-application/blob/master/app/api/routes.py"
    click WS_Admin "https://github.com/stagepfe2025/custom-etl-application/blob/master/app/api/admin.py"
    click WS_Main "https://github.com/stagepfe2025/custom-etl-application/blob/master/app/main/routes.py"
    click WS_Manage "https://github.com/stagepfe2025/custom-etl-application/blob/master/manage.py"
    click Redis "https://github.com/stagepfe2025/custom-etl-application/blob/master/config.py"
    click Redis "https://github.com/stagepfe2025/custom-etl-application/blob/master/app/extensions.py"
    click Redis "https://github.com/stagepfe2025/custom-etl-application/blob/master/celery_app.py"
    click CW_Standard "https://github.com/stagepfe2025/custom-etl-application/blob/master/app/tasks/etl_tasks.py"
    click CW_Scheduled "https://github.com/stagepfe2025/custom-etl-application/blob/master/app/tasks/scheduled_tasks.py"
    click Ext_File "https://github.com/stagepfe2025/custom-etl-application/blob/master/app/extractors/file_extractor.py"
    click Ext_S3 "https://github.com/stagepfe2025/custom-etl-application/blob/master/app/extractors/s3_extractor.py"
    click Ext_API "https://github.com/stagepfe2025/custom-etl-application/blob/master/app/extractors/api_extractor.py"
    click Ext_Feed "https://github.com/stagepfe2025/custom-etl-application/blob/master/app/extractors/feed_source.py"
    click Trans_Company "https://github.com/stagepfe2025/custom-etl-application/blob/master/app/transformers/company_transformer.py"
    click Trans_Customer "https://github.com/stagepfe2025/custom-etl-application/blob/master/app/transformers/customer_data_cleaner.py"
    click Trans_Sensitive "https://github.com/stagepfe2025/custom-etl-application/blob/master/app/transformers/sensitive_data_cleaner.py"
    click Trans_Map "https://github.com/stagepfe2025/custom-etl-application/blob/master/app/transformers/french_city_to_dep_to_reg.json"
    click Load_PG "https://github.com/stagepfe2025/custom-etl-application/blob/master/app/loaders/postgres_loader.py"
    click Load_ES "https://github.com/stagepfe2025/custom-etl-application/blob/master/app/loaders/elasticsearch_loader.py"
    click Load_CSV "https://github.com/stagepfe2025/custom-etl-application/blob/master/app/loaders/csv_loader.py"
    click Dashboard "https://github.com/stagepfe2025/custom-etl-application/tree/master/app/templates/"
    click docs "https://github.com/stagepfe2025/custom-etl-application/tree/master/docs/"

    %% Styles
    classDef compute fill:#d0e1f9,stroke:#1f77b4,stroke-width:1px
    classDef data fill:#d5f5e3,stroke:#28a745,stroke-width:1px
    classDef external fill:#f0f0f0,stroke:#7f8c8d,stroke-width:1px,stroke-dasharray: 5 5
    classDef monitor fill:#fcf3cf,stroke:#f1c40f,stroke-width:1px,stroke-dasharray: 2 2
```
