Implement real API for volunteer_application_analytics.py — complete, test & deploy

[sana-desai]

Background
volunteer_application_analytics.py is an AWS Lambda function for the Saayam app. It connects to the Virginia AWS RDS PostgreSQL database and returns analytics for the Super Admin dashboard.
This task covers two volunteer analytics widgets:
-Volunteer Activity Trend
-Volunteers by Location
These results are consumed by the frontend dashboard for charts and filters.
Note- Please refer to beneficiary_trend_analysis.py. The scope of this task is for the Virginia & Ireland databases. and refer https://github.com/saayam-for-all/data/blob/main/database/Saayam_Table.column.names_data.xlsx for table structure.

Phase 1 — Complete code changes
1.1 Remove unnecessary logic
Remove any fake/mock CSV loading logic if present
Remove unused imports such as sqlite3
Remove unrelated request/beneficiary analytics logic if reusing an older file
Keep the Lambda focused only on volunteer analytics

1.2 Add relevant Virginia database tables
Use the real Virginia database tables required for volunteer analytics.
Confirmed tables
-users
-volunteer_details
-country
-state
-city
-user_skills
-help_categories
Confirmed primary key columns
-users.user_id
-volunteer_details.user_id
-country.country_id
Base join
-users.user_id = volunteer_details.user_id
Important note
Exact non-PK fields such as volunteer registration date, active/inactive status, location mapping columns, and skill mapping columns should be validated against the Virginia DB before finalizing queries.

Part 1 — Volunteer Activity Trend
1.3 Add volunteer trend queries
Implement logic to return trend data for:
-New Volunteers
-Active Volunteers
-Total Volunteers
Use
-users.user_id
-volunteer_details.user_id
Requirements
-Group by month
-Return chart-ready data
-Keep the monthly sequence sorted in ascending order
Important-Confirm the exact date column used for volunteer onboarding/creation in the Virginia DB before implementation.

1.4 Add empty data handling for Volunteer Activity Trend
In get_volunteer_activity_trend() — return safe empty lists if no data is found
Ensure all response keys are always present even if data is empty
Example:
{
"new_volunteers": [],
"active_volunteers": [],
"total_volunteers": []
}

Part 2 — Volunteers by Location
1.5 Add location aggregation queries
Implement logic to return volunteer counts grouped by location.
The dashboard currently shows a country-level chart, but backend logic should be structured to support future hierarchical filtering for:
-country
-state
-city
Use tables
-users
-volunteer_details
-country
-state
-city
-help_categories
-user_skills
Requirements
-Return grouped volunteer counts by country for the current chart
-Keep the query structure flexible for future state/city drill-down

1.6 Add filters for location widget
Support input parameters for:
-country
-chart_type
-skill
Expected example values:
All Countries
Bar Chart
All Skills
The backend should dynamically apply these filters when building the location analytics query.

1.7 Add skill filtering
Use:

• user_skills.cat_id
  -help_categories.cat_id

• If 'skill' is 'All Skills' or empty, return all volunteers.

• Confirm the exact JSON structure in the Virginia DB before finalizing the SQL filter.

1.8 Add empty data handling for Volunteers by Location
-In get_volunteers_by_location() — return [] if no data found
-Ensure volunteers_by_location is always present in the final response, even if empty

1.9 Add error handling
Wrap DB connection in try/except
return statusCode: 500 if connection fails
Wrap query execution in try/except
return safe values instead of crashing:
[] for list responses
Add finally block to always:
close cursor
close database connection

1.10 Response structure
Return a response like this:
{
"volunteer_activity_trend": {
"new_volunteers": [],
"active_volunteers": [],
"total_volunteers": []
},
"volunteers_by_location": []
}
Example trend item
{ "month": "2025-01", "count": 35 }
Example location item
{ "country": "United Kingdom", "count": 111 }

1.11 Local test block
Add if name == "main" block at the bottom of the file for local testing

Phase 2 — Test locally
Install required dependencies:
pip install psycopg2-binary pandas
Run locally:
python volunteer_application_analytics.py
Verify:
-volunteer_activity_trend is present
-volunteers_by_location is present
-all keys return safe values when tables are empty
-no crash occurs on empty DB
-DB connection closes cleanly after each run
-filter behavior works correctly for country and skill inputs

Phase 3 — Deploy to AWS Lambda
Package the code with dependencies into a .zip file
Upload the zip to the Lambda function in AWS Console
Test Lambda
Use the AWS Console test button with an empty event:
{}
Also test with filters:
{
"country": "All Countries",
"chart_type": "Bar Chart",
"skill": "All Skills"
}
Verify
-Response returns statusCode: 200
-All required keys are present:
volunteer_activity_trend
volunteers_by_location
-Data format matches dashboard requirements
-Filters work correctly
-No runtime errors occur

Monitoring
Check AWS CloudWatch logs for:
connection errors
query failures
unexpected exceptions

Phase 4 — Security (before merging)
Move DB password out of source code into AWS Lambda environment variables or AWS Secrets Manager
Confirm the updated code does not have any credentials hardcoded