# akshatgoel92/welfare-monitor architecture

> Architecture diagram of the GitHub repository akshatgoel92/welfare-monitor, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-05-21). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/akshatgoel92/welfare-monitor
- Repository: https://github.com/akshatgoel92/welfare-monitor

## Overview

##### Project type and purpose
- This is an ETL‐oriented Python project (“ETL for Gates Mobile Access”) whose goal is to scrape weekly Fund Transfer Order (FTO) data from the NREGA MIS web system, load it into a MySQL database and S3, generate IVRS call scripts (static & dynamic), and drive a push‐call program for SKY beneficiaries in Raipur.
- It uses Scrapy spiders, custom Python scripts (pandas, SQLAlchemy, Alembic), shell scripts under cron on an AWS EC2 host, and AWS RDS/S3 plus external services (IVRS, Dropbox, email).

##### File structure analysis
Top‐level directories & roles:
- alembic/ – DB migration environment + versioned migration scripts
- common/ – shared helpers (email, cleaning, SQL builder) & error utilities
- db/ – SQLAlchemy schema (create/update), JSON metadata (stage_names/table_keys)
- scrape/ – Scrapy project: spiders (fto_branch, fto_urls, fto_content, fto_material), items.py, pipelines.py, settings.py, data & logs subfolders
- script/ – Python utilities to build static & dynamic call scripts, put them in DB/S3; includes script/utils formatters & getters
- tracker/ – FTO queue tracker: make.py (build pandas queue), update.py (daily progress + email), download.py (dump to S3/Cyberduck)
- shell (implicit) – shell scripts (fto_etl.sh, fto_stage.sh, fto_etl_branch.sh, fto_match_field.sh, fto_setup.sh) invoked via cron on EC2
- logs/ – process_log.py to upload logs to Dropbox
- archive/ – legacy scripts & tests
- top‐level scripts: fto_etl.sh variants, fto_stage.sh, db copy/create/update scripts, requirements.txt, RHistory, .editorconfig, .gitignore

##### Key README insights
- Three main Scrapy spiders—fto_branch, fto_content, fto_urls—produce raw transaction data & FTO URLs.
- Pipelines write scraped items to MySQL RDS (via SQLAlchemy) and optionally S3.
- Shell scripts orchestrate the workflow, triggered by cron on AWS EC2 (nightly and early morning).
- After scraping, pandas‐based scripts build queues and call scripts, which are inserted back into DB or uploaded to S3 for the IVRS platform.
- Logging and notifications use Dropbox API and email (common/helpers).
- Alembic handles schema migrations.

##### Main components
a. External systems
– NREGA MIS web interface (source of FTO data)
– IVRS calling platform (consumer of generated call scripts)
– AWS RDS MySQL (persistent ETL database)
– AWS S3 (raw data dumps & script storage)
– Dropbox API (log archive)
– Email/SNS (error & progress notifications)
b. EC2‐hosted orchestrator
– Cron scheduler → invokes shell scripts
– Shell scripts (fto_etl.sh, fto_stage.sh, etc.)
– Scrapy runners → scrape spiders → items → pipelines
– Python modules (tracker, script, common, db)
c. Database layer
– Alembic migrations → db/schema.py → MySQL tables (fto_queue, transactions, blocks, panchayats, enrolment_record, scripts)
d. Data processing layer
– tracker/make.py, update.py, download.py (pandas workflows + email + S3/Cyberduck)
– script/get_static_script.py & get_dynamic_script.py → generate CSVs for IVRS → put_script.py & put_camp.py → DB/S3 writes
e. Logging & notification
– scrape logs → process_log.py → Dropbox
– common/errors.py & common/helpers.send_email → notifications

##### Relationships and data flows
1) Cron job on EC2
→ shell scripts start Scrapy spiders (scrape/spiders)
→ spiders fetch pages from NREGA MIS
→ pipelines write raw data to AWS RDS & S3
2) fto_urls spider & fto_stage.sh
→ generate FTO queues CSVs → tracker/make.py → insert into fto_queue table
3) Daily or on‐demand: script/get_dynamic_script.py & get_static_script.py
→ pull data from RDS, merge with camp data → output CSVs → upload to S3 & insert to scripts table
→ IVRS platform consumes these scripts to make push calls
4) tracker/update.py & download.py
→ monitor queue status, send email, dump data to S3/Cyberduck
5) process_log.py
→ collect Scrapy logs → upload to Dropbox
6) Alembic
→ manage DB schema changes

##### Architectural patterns & design principles
- Modular ETL steps separated into ingestion (scrape), transformation (tracker & script modules), and load (DB/S3).
- Clear data ownership: MySQL for structured tables, S3 for raw CSVs and scripts.
- Cron‐based orchestration with shell wrappers around Python modules.
- Reusable common libraries for error handling, email, cleaning.
- Versioned DB migrations via Alembic.
- Separation of legacy code (archive) from active modules.

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% EC2 Orchestrator
    subgraph "EC2 Cron & Shell Orchestrator"
        Cron["Cron Scheduler"]:::orchestrator
        subgraph "Shell Scripts"
            ETLsh["fto_etl.sh"]:::script
            BranchSh["fto_etl_branch.sh"]:::script
            StageSh["fto_stage.sh"]:::script
            SetupSh["fto_setup.sh"]:::script
            ScriptSh["fto_script.sh"]:::script
        end
        Cron --> ShellScripts(("Shell Scripts"))
        ShellScripts -->|invokes| ETLsh
        ShellScripts -->|invokes| BranchSh
        ShellScripts -->|invokes| StageSh
        ShellScripts -->|invokes| SetupSh
        ShellScripts -->|invokes| ScriptSh
    end

    %% Ingestion Layer
    subgraph "Scrapy Ingestion" 
        direction TB
        Spiders["Spiders"]:::ingestion
        subgraph "spiders"
            FTOBranch["fto_branch.py"]:::spider
            FTOUrls["fto_urls.py"]:::spider
            FTOContent["fto_content.py"]:::spider
            FTOMaterial["fto_material.py"]:::spider
        end
        Items["items.py"]:::ingestion
        Pipelines["pipelines.py"]:::ingestion
        Settings["settings.py"]:::ingestion
        Middlewares["middlewares.py"]:::ingestion
        Spiders -->|yields items| Items
        Items -->|to pipelines| Pipelines
        Pipelines -->|writes| RDS[(AWS RDS MySQL)]:::storage
        Pipelines -->|uploads| S3[(AWS S3)]:::storage
    end

    %% Database & Migrations
    subgraph "Database Layer"
        Alembic["Alembic Migrations"]:::migration
        DBSchema["db/schema.py"]:::database
        Alembic -->|manages| DBSchema
        DBSchema -->|defines tables| RDS
    end

    %% Processing Layer
    subgraph "Tracker & Script Modules"
        direction TB
        subgraph "Tracker Module"
            Make["make.py"]:::processing
            Update["update.py"]:::processing
            Download["download.py"]:::processing
        end
        subgraph "Script Generation"
            GetStatic["get_static_script.py"]:::processing
            GetDynamic["get_dynamic_script.py"]:::processing
            PutScript["put_script.py"]:::processing
            PutDynamic["put_dynamic_script.py"]:::processing
            PutCamp["put_camp.py"]:::processing
        end
        Pipelines -->|FTO URLs queue| Make
        Make -->|inserts into| RDS
        GetStatic -->|reads from| RDS
        GetDynamic -->|reads from| RDS
        GetStatic -->|writes CSV & DB| S3
        GetStatic -->|inserts scripts| RDS
        GetDynamic -->|writes CSV & DB| S3
        GetDynamic -->|inserts scripts| RDS
        RDS -->|consumed by| IVRS[(IVRS Platform)]:::external
        Update -->|email status| Email[(Email/SNS)]:::external
        Update -->|dumps to| S3
        Download -->|uploads to| S3
        Download -->|via Cyberduck| S3
    end

    %% Logging & Notifications
    ProcessLog["process_log.py"]:::notification
    Pipelines -->|logs| ProcessLog
    ProcessLog -->|archives| Dropbox[(Dropbox API)]:::external

    %% Top-Level Config
    subgraph "Configuration & Dependencies"
        README["README.md"]:::config
        Requirements["requirements.txt"]:::config
        ScrapyCfg["scrapy.cfg"]:::config
        Gitignore[".gitignore"]:::config
        Editorconfig[".editorconfig"]:::config
        Rhistory[".Rhistory"]:::config
    end

    %% Relationships from Orchestrator to Ingestion
    ETLsh -->|starts| Spiders
    BranchSh -->|starts| Spiders
    StageSh -->|starts fto_urls| FTOUrls

    %% Common Utilities
    subgraph "Common Utilities"
        Errors["errors.py"]:::common
        Helpers["helpers.py"]:::common
        ErrMsgs["error_messages.json"]:::common
    end
    Pipelines -->|uses| Helpers
    Make -->|uses| Helpers
    GetStatic -->|uses| Helpers
    ProcessLog -->|uses| Helpers
    any -->|errors via| Errors

    %% Styles
    classDef orchestrator fill:#DDEEFF,stroke:#000,stroke-width:1px;
    classDef script fill:#CCEEFF,stroke:#000,stroke-width:1px,stroke-dasharray: 5 5;
    classDef ingestion fill:#CCE5FF,stroke:#000,stroke-width:1px;
    classDef spider fill:#99CCFF,stroke:#000,stroke-width:1px;
    classDef storage fill:#CCFFCC,stroke:#000,stroke-width:1px;
    classDef migration fill:#FFEECC,stroke:#000,stroke-width:1px;
    classDef database fill:#CCFF99,stroke:#000,stroke-width:1px;
    classDef processing fill:#FFD699,stroke:#000,stroke-width:1px;
    classDef notification fill:#E0CCFF,stroke:#000,stroke-width:1px;
    classDef external fill:#FFFFFF,stroke:#333,stroke-width:1px,stroke-dasharray: 2 2;
    classDef common fill:#FFCCCC,stroke:#000,stroke-width:1px;
    classDef config fill:#EEEEEE,stroke:#666,stroke-width:1px;

    %% Click Events
    click Alembic "https://github.com/akshatgoel92/welfare-monitor/tree/master/alembic/"
    click Errors "https://github.com/akshatgoel92/welfare-monitor/blob/master/common/errors.py"
    click Helpers "https://github.com/akshatgoel92/welfare-monitor/blob/master/common/helpers.py"
    click ErrMsgs "https://github.com/akshatgoel92/welfare-monitor/blob/master/common/error_messages.json"
    click DBSchema "https://github.com/akshatgoel92/welfare-monitor/blob/master/db/schema.py"
    click Make "https://github.com/akshatgoel92/welfare-monitor/blob/master/tracker/make.py"
    click Update "https://github.com/akshatgoel92/welfare-monitor/blob/master/tracker/update.py"
    click Download "https://github.com/akshatgoel92/welfare-monitor/blob/master/tracker/download.py"
    click ETLsh "https://github.com/akshatgoel92/welfare-monitor/blob/master/fto_etl.sh"
    click BranchSh "https://github.com/akshatgoel92/welfare-monitor/blob/master/fto_etl_branch.sh"
    click StageSh "https://github.com/akshatgoel92/welfare-monitor/blob/master/fto_stage.sh"
    click SetupSh "https://github.com/akshatgoel92/welfare-monitor/blob/master/fto_setup.sh"
    click ScriptSh "https://github.com/akshatgoel92/welfare-monitor/blob/master/fto_script.sh"
    click FTOBranch "https://github.com/akshatgoel92/welfare-monitor/blob/master/scrape/spiders/fto_branch.py"
    click FTOUrls "https://github.com/akshatgoel92/welfare-monitor/blob/master/scrape/spiders/fto_urls.py"
    click FTOContent "https://github.com/akshatgoel92/welfare-monitor/blob/master/scrape/spiders/fto_content.py"
    click FTOMaterial "https://github.com/akshatgoel92/welfare-monitor/blob/master/scrape/spiders/fto_material.py"
    click Items "https://github.com/akshatgoel92/welfare-monitor/blob/master/scrape/items.py"
    click Pipelines "https://github.com/akshatgoel92/welfare-monitor/blob/master/scrape/pipelines.py"
    click Settings "https://github.com/akshatgoel92/welfare-monitor/blob/master/scrape/settings.py"
    click Middlewares "https://github.com/akshatgoel92/welfare-monitor/blob/master/scrape/middlewares.py"
    click ProcessLog "https://github.com/akshatgoel92/welfare-monitor/blob/master/scrape/logs/process.py"
    click GetStatic "https://github.com/akshatgoel92/welfare-monitor/blob/master/script/get_static_script.py"
    click GetDynamic "https://github.com/akshatgoel92/welfare-monitor/blob/master/script/get_dynamic_script.py"
    click PutScript "https://github.com/akshatgoel92/welfare-monitor/blob/master/script/put_script.py"
    click PutDynamic "https://github.com/akshatgoel92/welfare-monitor/blob/master/script/put_dynamic_script.py"
    click PutCamp "https://github.com/akshatgoel92/welfare-monitor/blob/master/script/put_camp.py"
```
