How akshatgoel92/welfare-monitor works
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.