Get list of available volunteers for Dashboard #289

Summary

Develop a Volunteer Microservice API to retrieve volunteers who are eligible and available for assignment to a help request.

The API should accept a request_id and return volunteers who satisfy the applicable matching rules for that request.

This API provides reusable volunteer-matching data for request assignment, reassignment, review, and other workflows that need eligible volunteers.

Availability / Matching Rules

A volunteer is considered eligible for a request when the applicable matching criteria are satisfied.

1. Skill Match

• The volunteer must have skills that match the request category.
• The request category should be identified using req_cat_id from the requests table.
• Volunteer skills should be retrieved using the existing user_skills table.
• requests.req_cat_id references help_categories.cat_id.
• user_skills.cat_id also references help_categories.cat_id.

For direct category matching:

requests.req_cat_id = user_skills.cat_id

• Volunteers whose skills do not match the applicable request category must not be returned.
• Existing category relationships should be reused rather than introducing hardcoded category-to-skill mappings.
• If parent/child category matching is required, use the existing help_category_map table.

2. Location Match for In-Person Requests

If the request type is In person:

• The volunteer must be located near the beneficiary/request location.
• Request type should be determined using requests.req_type_id and the existing request_types lookup table.
• The request/beneficiary location should be obtained from the existing request/user location data as applicable.
• Volunteer location should be obtained from the volunteer_locations table.

Relevant volunteer_locations fields:

• user_id
• curr_loc
• prev_loc
• last_updated_at

Details:

• volunteer_locations.user_id identifies the volunteer.
• curr_loc represents the volunteer's current location.
• prev_loc stores the previous location.
• last_updated_at identifies when the volunteer location was last updated.
• curr_loc is stored as geography(Point, 4326).
• user_id is the primary key of volunteer_locations, so one current location row is maintained per volunteer.
• Existing PostGIS/geospatial matching logic should be reused where available.
• The approved distance/radius should not be hardcoded unless it is defined by an existing business rule or configuration.

For requests that do not require in-person service, location filtering should not be applied unless required by existing matching rules.

3. Volunteer Status

Volunteer status should be considered when determining eligibility.

Volunteer status is associated with users.user_status_id and the corresponding user-status lookup table.

Current understanding is that eligible volunteers should have Active status.

The implementation should resolve the status through the existing lookup relationship rather than hardcoding a numeric status ID.

The exact eligible status value(s) should be confirmed before finalizing the status-filtering logic.

4. Existing Assignment Check

Existing request-to-volunteer assignment data is available in the volunteers_assigned table.

Relevant fields:

• vol_assigned_id
• req_id
• volunteer_id
• volunteer_type
• last_updated_at

If the confirmed business rule requires volunteers already assigned to the same request to be excluded, this table should be used.

Suggested Endpoint

POST /volunteers/available

The final endpoint name should follow the existing Volunteer Microservice API conventions.

Input

The API should accept a request identifier:

{
"request_id": "REQ-00-000-000-001"
}

Using request_id, the API should determine:

• Request category
• Request type
• Beneficiary/request location when location matching is required

Data Sources / Tables

The implementation should use the existing Saayam database schema and reuse existing request, category, volunteer, skill, status, assignment, and geolocation data.

Note: The database has undergone pluralization changes. The implementation should use the current deployed table names in the target environment.

Request Data

Use the existing requests table.

Relevant fields:

Request Category

Use the help_categories table.

Relevant fields:

The request category relationship is:

requests.req_cat_id
↓
help_categories.cat_id

If hierarchical category relationships are required, use the help_category_map table.

Relevant fields:

• parent_id
• child_id

Existing category relationships should be reused instead of introducing hardcoded category mappings.

Volunteer Skills

Use the user_skills table.

Relevant fields:

The direct relationship is:

requests.req_cat_id
↓
help_categories.cat_id
↑
user_skills.cat_id
↓
user_skills.user_id

For direct matching:

user_skills.cat_id = requests.req_cat_id

Volunteer Profile

Use the existing users and volunteer_details tables.

volunteer_details.user_id identifies a volunteer and references users.user_id.

These tables should be used to retrieve the volunteer information required in the API response.

Request Type

Use:

requests.req_type_id
↓
request_types.req_type_id

to determine whether the request is an In person request.

The exact lookup value representing an in-person request should be verified against the current data.

Volunteer Status

Use:

users.user_status_id
↓
user status lookup

to determine volunteer status.

Do not hardcode a numeric status ID.

Volunteer Geolocation

For In person requests, use the volunteer_locations table.

Relevant fields:

Use volunteer_locations.curr_loc for proximity matching.

curr_loc is stored as geography(Point, 4326).

Existing GiST indexes and PostGIS functionality should be used where applicable.

Existing Volunteer Assignments

Use the volunteers_assigned table.

Relevant fields:

Relationships:

volunteers_assigned.req_id
↓
requests.req_id

volunteers_assigned.volunteer_id
↓
users.user_id

This table should be used if already-assigned volunteers must be excluded.

Expected Data Flow

Expected Data Flow

request_id
    |
    v
requests
    |
    +--------------------+
    |                    |
    v                    v
 req_cat_id          req_type_id
    |                    |
    v                    v
help_categories      request_types
    |                    |
    v                    v
user_skills          In person?
    |                  /     \
    v                Yes      No
Skill-Matched          |       |
Volunteers             |       +----> Skip location filtering
    |                  |
    |                  v
    |          Request/Beneficiary Location
    |                  +
    |          volunteer_locations.curr_loc
    |                  |
    |                  v
    |          Geospatial Matching
    |                  |
    |                  v
    |          Nearby Volunteers
    |                  |
    +------------------+
                       |
                       v
              Volunteer Status Check
                       |
                       v
              Eligible Volunteers
                       |
                       v
           Existing Assignment Check
            (if business rule applies)
                       |
                       v
              Available Volunteers
                       |
                       v
          Return API Response

Processing Logic

Step 1 - Validate request_id

Verify that request_id is present in the request payload.

If it is missing, return the appropriate validation error according to the existing Volunteer Microservice error-handling convention.

Step 2 - Retrieve Request Details

Using request_id, retrieve the request from the requests table.

Retrieve at minimum:

• req_cat_id
• req_type_id
• Request/beneficiary location where applicable

If the request does not exist, follow the existing Volunteer Microservice invalid-request error convention.

Resolve request category and request type using the existing lookup relationships.

Step 3 - Find Volunteers With Matching Skills

Use the request category and existing user_skills relationship to identify candidate volunteers.

Conceptually:

Request
↓
req_cat_id
↓
help_categories
↓
user_skills.cat_id
↓
Skill-Matched Volunteers

For direct-category matching:

requests.req_cat_id = user_skills.cat_id

Volunteers without the required skill/category match should be excluded.

If hierarchical category matching is required, reuse help_category_map.

Step 4 - Apply Volunteer Status

Resolve candidate volunteer status using the existing user-status relationship.

Current expectation is to return Active volunteers.

Exclude volunteers that do not satisfy the confirmed eligibility rule.

Step 5 - Apply Location Matching

Resolve the request type.

If the request is In person:

1. Retrieve the applicable beneficiary/request location.
2. Retrieve candidate volunteer locations from volunteer_locations.curr_loc.
3. Apply the approved PostGIS/geospatial distance-matching logic.
4. Exclude volunteers outside the approved matching radius.

Conceptually:

Skill-Matched Volunteers
+
Volunteer Current Location
+
Beneficiary/Request Location
↓
Existing Geospatial Matching
↓
Nearby Volunteers

For non-in-person requests, location filtering should not be applied unless required by existing matching rules.

Missing volunteer geolocation should not cause the entire API request to fail.

Step 6 - Apply Existing Assignment Rule

If confirmed by the business requirement, use volunteers_assigned to exclude volunteers who are already assigned to the same request.

Step 7 - Return Available Volunteers

Return the volunteers remaining after all applicable matching rules have been applied.

Suggested Response

{
"requestId": "REQ-00-000-000-001",
"availableVolunteers": [
{
"volunteerId": "SID-00-000-000-000-000-001",
"name": "Example Volunteer",
"skills": [
"Food Assistance"
],
"status": "Active"
}
]
}

The exact volunteer attributes returned should be finalized based on the requirements of the consuming application/API flow.

No Available Volunteers

If the request is valid but no volunteers satisfy the matching criteria, return HTTP 200:

{
"requestId": "REQ-00-000-000-001",
"availableVolunteers": []
}

An empty matching result is not an API error.

Query / Data Engineering Requirements

• Use parameterized SQL queries.
• Database credentials must not be hardcoded.
• Use current deployed/pluralized database table names.
• Reuse existing request, category, volunteer, skill, status, assignment, and geolocation data.
• Reuse existing PostGIS/geospatial matching logic where available.
• Avoid N+1 queries when evaluating candidate volunteers.
• Prefer joins/set-based filtering for category, skill, and status matching.
• Do not hardcode category-to-skill relationships.
• Do not hardcode numeric volunteer status IDs.
• Do not hardcode the in-person matching radius unless defined by an existing configuration/business rule.
• Handle NULL/missing optional data safely.
• Missing or unavailable volunteer geolocation must not cause the entire API request to fail.

Relevant lookup/join/filter fields should be reviewed for appropriate indexing, particularly:

• requests.req_id
• requests.req_cat_id
• requests.req_type_id
• user_skills.user_id
• user_skills.cat_id
• users.user_status_id
• volunteer_locations.user_id
• volunteer_locations.curr_loc
• volunteers_assigned.req_id
• volunteers_assigned.volunteer_id

Error Handling

Missing request_id

Return HTTP 400 following the existing Volunteer Microservice error conventions.

Example:

{
"error": "request_id is required"
}

Invalid or Nonexistent request_id

Follow the existing Volunteer Microservice error-code and response conventions.

Database Connection Failure

Return HTTP 500 using the project's standard error response structure.

Query Execution Failure

Return HTTP 500 using the project's standard error response structure.

No Matching Volunteers

Return HTTP 200:

{
"requestId": "REQ-00-000-000-001",
"availableVolunteers": []
}

Local Testing

Test at minimum:

Request Validation

• Valid request_id
• Missing request_id
• Invalid/nonexistent request_id

Skill Matching

• Request with multiple skill-matched volunteers
• Request with one skill-matched volunteer
• Request with no skill-matched volunteers
• Volunteer with matching cat_id
• Volunteer without matching cat_id
• Parent/child category matching once the business rule is confirmed

Location Matching

• In person request with nearby volunteer
• In person request with volunteer outside the permitted distance
• In person request with missing volunteer geolocation
• In person request with multiple candidate volunteers at different distances
• Non-in-person request to verify that location filtering is not incorrectly applied

Volunteer Status

• Eligible/Active volunteer
• Non-active volunteer
• Missing/NULL status where applicable

Existing Assignment

Once the business rule is confirmed:

• Volunteer already assigned to the request
• Volunteer not assigned to the request

General / Failure Scenarios

• No available volunteers
• Empty result set
• Database connection exception
• Query execution exception
• NULL/missing optional volunteer fields
• Response structure validation

Acceptance Criteria

• API accepts a valid request_id.
• API retrieves the request from the existing requests table.
• API retrieves the request category using req_cat_id.
• Existing user_skills data is used for volunteer skill matching.
• Direct skill matching uses requests.req_cat_id and user_skills.cat_id.
• Volunteers without an applicable skill match are excluded.
• Volunteer status is resolved through the existing user-status relationship.
• Request type is resolved using req_type_id and the existing request_types lookup.
• For In person requests, volunteer location is retrieved from volunteer_locations.curr_loc.
• Existing PostGIS/geospatial matching logic is reused where available.
• Volunteers outside the approved proximity rule are excluded for In person requests.
• Non-in-person requests are not incorrectly filtered by location.
• Existing assignments are handled according to the confirmed business rule using volunteers_assigned.
• A valid request with no matching volunteers returns HTTP 200 with an empty availableVolunteers list.
• SQL queries are parameterized.
• Database credentials are not hardcoded.
• Existing database tables and relationships are reused.
• Existing Volunteer Microservice coding and error-handling conventions are followed.
• Local tests cover request validation, skill matching, location matching, status filtering, empty results, and failure scenarios.
• Implementation goes through Pull Request review before deployment.

Open Questions / To Be Confirmed

The primary database tables and relationships have now been identified. The following business rules still need confirmation:

1. Volunteer Status

Volunteer status is available through users.user_status_id and the corresponding status lookup.

Which status value(s) should qualify a volunteer as available?

Should eligibility be limited to Active?

2. In-Person Matching Radius

What distance/radius should be used to determine whether a volunteer is nearby for an In person request?

Is this radius already configured in an existing application setting, service, or geospatial implementation?

3. Category Hierarchy

Is direct matching using:

requests.req_cat_id = user_skills.cat_id

sufficient, or should parent/child category relationships from help_category_map also qualify a volunteer?

4. Already Assigned Volunteers

Should volunteers already present in volunteers_assigned for the same request be excluded from the available-volunteer result?

5. Schedule Availability

Should this API also check volunteer scheduling availability using:

• volunteer_details.availability_days
• volunteer_details.availability_times
• user_availability

or should availability for this API be based only on skill, status, and location?

6. Response Fields

Which volunteer fields are required in the final API response?