OpenSPP SQL Query Eligibility Manager#

Warning

Work in Progress: This document is actively being developed and updated. Content may be incomplete or subject to change.

This document describes the OpenSPP SQL Query Eligibility Manager module. This module enhances the g2p_programs module by introducing a flexible way to define program eligibility criteria using SQL queries.

Purpose#

The OpenSPP SQL Query Eligibility Manager module enables program administrators to:

  • Define Eligibility with SQL: Use SQL queries to express complex eligibility criteria based on data stored in the OpenSPP database. This provides greater flexibility than pre-defined eligibility rules.

  • Validate SQL Queries: The module includes functionality to validate the syntax and structure of user-defined SQL queries, ensuring they are compatible with the database schema.

  • Test Eligibility Criteria: Administrators can test SQL queries against the registrant database to preview the number of beneficiaries who would be eligible based on the defined criteria.

  • Automate Enrollment: The module can automatically enroll registrants who meet the criteria specified in the SQL query, streamlining the beneficiary enrollment process.

Module Dependencies and Integration#

  1. g2p_registry_base: This module relies on the base registry for access to registrant data, which is queried against the SQL-based eligibility criteria.

  2. g2p_programs:

    • Extends the core program management functionality by providing an additional eligibility manager type specifically for SQL-based eligibility.

    • Integrates with program views to display the SQL query eligibility manager and its associated actions.

  3. spp_programs: Builds upon OpenSPP Programs features to allow SQL-based eligibility for both cash and in-kind programs.

  4. queue_job: Employs the queue job framework to handle the asynchronous execution of SQL queries and the enrollment of eligible registrants, particularly for large datasets.

Additional Functionality#

  • g2p.program_membership.manager.sql: A new eligibility manager model specifically designed to store and execute SQL queries for eligibility determination.

  • SQL Query Validation: The module validates SQL queries for:

    • Syntax Errors: Checks for any syntax errors in the query.

    • Schema Compatibility: Verifies that the query references existing tables and columns within the OpenSPP database schema.

    • Result Set: Ensures that the query returns a list of registrant IDs, which is required for determining eligibility.

  • Test Query Functionality: Administrators can execute the SQL query in a test environment to:

    • Preview Eligible Beneficiaries: See a count of how many registrants meet the defined criteria.

    • Identify Potential Issues: Detect any unexpected results or errors before applying the query to the actual program enrollment.

  • Automated Enrollment: Once the SQL query is validated, the module can:

    • Fetch Eligible Registrants: Retrieve a list of registrant IDs who match the criteria.

    • Create Program Memberships: Automatically enroll eligible registrants in the associated program.

Conclusion#

The OpenSPP SQL Query Eligibility Manager module significantly enhances the flexibility and efficiency of managing program eligibility within OpenSPP. By leveraging the power of SQL queries, administrators can define complex eligibility rules and automate the beneficiary enrollment process, saving time and ensuring accuracy.