Background*
Create a new AWS Lambda API for the Saayam Super Admin KPI dashboard. This Lambda will connect to the Virginia AWS RDS PostgreSQL database and return chart-ready analytics for the KPI tab.
This task covers two KPI widgets:
-Request Status Distribution
-Average Resolution Time by Category
Note- Please refer to beneficiary_trend_analysis.py. The scope of this task is restricted to the Virginia database only. and refer https://github.com/saayam-for-all/data/blob/main/database/Saayam_Table.column.names_data.xlsx for table structure.
Create kpi_api_analytics.py to fetch real request KPI data from Virginia RDS and return dashboard-ready JSON.
Phase 1 — Complete Code Changes
1.1 Create new Lambda file
Create:
kpi_api_analytics.py
The Lambda should:
Connect to Virginia AWS RDS PostgreSQL
Use real database tables
Return JSON data for KPI dashboard charts
Avoid fake/mock CSV data
Handle empty data safely
1.2 Add required database tables
Use Virginia DB tables related to requests and categories.
Expected tables:
request
request_status
help_categories
Important: Confirm exact table and column names in Virginia DB before finalizing queries.
Part 1 — Request Status Distribution
1.3 Add request status distribution query
Implement logic to count requests by current status.
Expected statuses:
CREATED
IN_PROGRESS
RESOLVED
Expected join:
request.req_status_id = request_status.req_status_id
Expected response item:
{ "status": "CREATED", "count": 120 }
Also return total request count:
"total_requests": 500
1.4 Empty data handling for status distribution
If no request status data exists:
"request_status_distribution": []
If no requests exist:
"total_requests": 0
Part 2 — Average Resolution Time by Category
1.5 Add average resolution time query
Implement logic to calculate average resolution time grouped by request category.
Use date columns:
request.submission_date
request.serviced_date
Use request_status.req_status_id to confirm request resolution status (Needs to be completed or resolved)
Formula:
resolution_time_hours = submission_date - serviced_date
Expected response item:
{ "category": "Shelter", "avg_hours": 260 }
- Only include records where both dates are available.
1.6 Add SLA metadata
Use dashboard SLA values:
Target: 10 days
Warning: 8.33 days
Return:
"sla": {
"target_days": 10,
"target_hours": 240,
"warning_days": 8.33,
"warning_hours": 200
}
1.7 Empty data handling for resolution-time widget
If no resolved requests exist:
"average_resolution_time_by_category": []
Ensure sla is always present.
Phase 2 — Response Structure
Return:
{
"request_status_distribution": [],
"total_requests": 0,
"average_resolution_time_by_category": [],
"sla": {
"target_days": 10,
"target_hours": 240,
"warning_days": 8.33,
"warning_hours": 200
}
}
Phase 3 — 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 lists
0 for counts
Add finally block to:
close cursor
close DB connection
Phase 4 — Local Testing
Install dependencies:
pip install psycopg2-binary pandas
Run:
python kpi_analytics.py
Test with:
{}
Verify:
statusCode = 200
request_status_distribution present
total_requests present
average_resolution_time_by_category present
sla present
No crash on empty DB
DB connection closes properly
Background*
Create a new AWS Lambda API for the Saayam Super Admin KPI dashboard. This Lambda will connect to the Virginia AWS RDS PostgreSQL database and return chart-ready analytics for the KPI tab.
This task covers two KPI widgets:
-Request Status Distribution
-Average Resolution Time by Category
Note- Please refer to beneficiary_trend_analysis.py. The scope of this task is restricted to the Virginia database only. and refer https://github.com/saayam-for-all/data/blob/main/database/Saayam_Table.column.names_data.xlsx for table structure.
Create kpi_api_analytics.py to fetch real request KPI data from Virginia RDS and return dashboard-ready JSON.
Phase 1 — Complete Code Changes
1.1 Create new Lambda file
Create:
kpi_api_analytics.py
The Lambda should:
Connect to Virginia AWS RDS PostgreSQL
Use real database tables
Return JSON data for KPI dashboard charts
Avoid fake/mock CSV data
Handle empty data safely
1.2 Add required database tables
Use Virginia DB tables related to requests and categories.
Expected tables:
request
request_status
help_categories
Important: Confirm exact table and column names in Virginia DB before finalizing queries.
Part 1 — Request Status Distribution
1.3 Add request status distribution query
Implement logic to count requests by current status.
Expected statuses:
CREATED
IN_PROGRESS
RESOLVED
Expected join:
request.req_status_id = request_status.req_status_id
Expected response item:
{ "status": "CREATED", "count": 120 }
Also return total request count:
"total_requests": 500
1.4 Empty data handling for status distribution
If no request status data exists:
"request_status_distribution": []
If no requests exist:
"total_requests": 0
Part 2 — Average Resolution Time by Category
1.5 Add average resolution time query
Implement logic to calculate average resolution time grouped by request category.
Use date columns:
request.submission_date
request.serviced_date
Use request_status.req_status_id to confirm request resolution status (Needs to be completed or resolved)
Formula:
resolution_time_hours = submission_date - serviced_date
Expected response item:
{ "category": "Shelter", "avg_hours": 260 }
1.6 Add SLA metadata
Use dashboard SLA values:
Target: 10 days
Warning: 8.33 days
Return:
"sla": {
"target_days": 10,
"target_hours": 240,
"warning_days": 8.33,
"warning_hours": 200
}
1.7 Empty data handling for resolution-time widget
If no resolved requests exist:
"average_resolution_time_by_category": []
Ensure sla is always present.
Phase 2 — Response Structure
Return:
{
"request_status_distribution": [],
"total_requests": 0,
"average_resolution_time_by_category": [],
"sla": {
"target_days": 10,
"target_hours": 240,
"warning_days": 8.33,
"warning_hours": 200
}
}
Phase 3 — 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 lists
0 for counts
Add finally block to:
close cursor
close DB connection
Phase 4 — Local Testing
Install dependencies:
pip install psycopg2-binary pandas
Run:
python kpi_analytics.py
Test with:
{}
Verify:
statusCode = 200
request_status_distribution present
total_requests present
average_resolution_time_by_category present
sla present
No crash on empty DB
DB connection closes properly