Municipal and academic scholarship programs rarely share homogeneous selection requirements: academic tracks emphasize GPA thresholds and university accreditations, affirmative programs prioritize income brackets and regional quotas, while athletic grants weigh certified competition achievements.

Hardcoding table columns for every parameter produces fragile databases that demand code deployments on every policy update.

1. Dynamic Qualification Builder via JSONB

To provide complete autonomy for program administrators without altering the SQL database schema, we paired PostgreSQL JSONB fields with dynamic evaluation rules:

graph TD
    A[Program Admin] -->|Define Weights & Criteria Matrix| B[Dynamic Qualification Schema]
    B -->|Persisted as JSONB Document| DB[(PostgreSQL 16)]
    C[Student Applicant] -->|Submit Multi-Step Dossier| D[Applicant Answer Payload]
    D -->|Evaluate Payload vs Criteria| E[Scoring Rule Engine]
    E -->|Computed Scores & Audit Breakdown| F[(Application Scores Table)]

Example Qualification Schema (JSONB):

{
  "criteria_key": "gpa_score",
  "type": "numeric_range",
  "weight_percentage": 40,
  "rules": [
    { "min": 3.75, "max": 4.00, "score": 100 },
    { "min": 3.50, "max": 3.74, "score": 85 },
    { "min": 3.00, "max": 3.49, "score": 70 }
  ],
  "tiebreaker_priority": 1
}

2. Two-Phase Batch Selection Pipeline

When an application period closes, thousands of submissions are processed asynchronously via Laravel Queue Workers through two distinct evaluation phases:

  1. Phase 1 (Renewal Priority Validation): Existing awardees requesting renewal are evaluated first. If their academic standing satisfies continuity criteria, their quota is reserved automatically.
  2. Phase 2 (Scoring & Quota Ranking): New applicants are ranked against weighted qualification metrics. When identical scores emerge, the system invokes deterministic multi-tiered tiebreaker algorithms (e.g. comparing semester progression, then lowest household income).
use App\Jobs\ProcessBatchScoring;

ProcessBatchScoring::dispatch($scholarshipProgramId)
    ->onQueue('scoring')
    ->afterCommit();

3. Architectural Advantages

  • Zero-Downtime Rule Reconfigurations: Administrators can instantiate new scholarship programs with custom criteria in minutes.
  • Granular Audit Logs: Every computed score maintains an immutable JSON breakdown ensuring transparent selection auditability.

Official Documentation References: