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

[prachi080588]

Background

beneficiary_trend_analysis.py is an AWS Lambda function for the Saayam app. It connects to an AWS RDS PostgreSQL database and returns 8 analytics statistics — beneficiary counts and help request counts across 7 days, 1 month, 1 year, and by country. The results are consumed by the frontend dashboard for charts and visualizations.

Previously the function used fake CSV files loaded into temporary tables for testing. The goal is to now query the real production database directly.

Phase 1 — Complete code changes

1.1 Remove fake data code

1. Delete the entire create_fake_table() function
2. Delete the if event["action"] == "create_tables" block including all fake CSV file paths
3. Remove unused import sqlite3 at the top of the file

1.2 Add relevant Ireland database tables (Request, Users, Country) to count beneficiaries from Ireland region

1.3 Add empty data handling

1. In get_beneficiaries_dic() — return [] if no data found
2. In get_help_requests_dic() — return [] if no data found
3. In aggregate_beneficiaries_country() — return [] if no rows returned
4. In aggregate_help_requests_country() — return [] if no rows returned

1.4 Add error handling

1. Wrap DB connection in try/except — return 500 with error message if connection fails
2. Wrap all query functions in try/except — return [] instead of crashing
3. Add finally block to always close cursor and conn after queries complete

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

Phase 2 — Test locally

1. Install required dependencies: pip install psycopg2-binary pandas
2. Run the file locally using: python beneficiary_trend_analysis.py
3. Verify output — each of the 8 keys returns either a list of data or []
4. Verify no crash occurs when DB has empty tables
5. Verify DB connection closes cleanly after each run

Phase 3 — Deploy to AWS Lambda

1. Package the code with dependencies into a .zip file
2. Upload the zip to the Lambda function in AWS Console
3. Test Lambda using the AWS Console test button with an empty event {}
4. Confirm response returns statusCode: 200 with all 8 data keys
5. Check AWS CloudWatch logs for any errors after execution

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