Winter Scholars Transportation Report Generalization

2025-12-12: Winter Scholars Transportation Report Generalization

For: Beaumont USD Impact: Unified reporting for Fall and Winter SOV programs - single query now handles both program types with different question structures, reducing maintenance and enabling consistent tracking of transportation needs and attendance recovery consent across seasonal programs. Context: Original query was hardcoded for Fall SOV programs which had 1 question (transportation). Winter SOV programs have 2 questions (attendance recovery consent + transportation) stored in different array positions. Needed generalized query that works for both.

Implementation:

• Changed filter from %Fall SOV% to %SOV% to match both Fall and Winter programs
• Replaced hardcoded array index access (->0->>'answer') with subqueries using jsonb_array_elements to search by question text
• Added “Attendance Recovery Consent” column (shows NULL for Fall programs that don’t have this question)
• Renamed “School Attending” to “Site Name” per ticket requirements
• Questions stored in program.metadata.acknowledgementStepsQuestions as multilingual JSON objects

Query extracts by searching question text:

• Transportation: WHERE LOWER(elem->>'question') LIKE '%transportation%'
• Attendance Recovery: WHERE LOWER(elem->>'question') LIKE '%attendance recovery%' OR '%education code%'

Related: ATT-1949, Hub

---

2025-12-12: Winter SOV Program Question Text Fix

For: Beaumont USD Impact: Corrected user-facing registration question text in all 4 languages for Winter SOV programs - parents now see “Winter Scholars Camp” instead of incorrectly copied “Fall Scholars Camp” text. Context: Winter SOV programs were created by copying Fall SOV, leaving the transportation question with “Fall” references in all translations.

Implementation:

• Updated program.metadata.acknowledgementStepsQuestions[1] for all 4 Winter SOV programs
• Fixed all 4 languages using nested jsonb_set UPDATE:
  • EN: “Fall Scholars Camp” → “Winter Scholars Camp”
  • ES: “Campamento de Estudiantes de Otoño” → “Campamento de Estudiantes de Invierno”
  • VI: “Trại Học sinh Mùa Thu” → “Trại Học sinh Mùa Đông”
  • ZH_Hans: “秋季学者营” → “冬季学者营”

Related: ATT-1949, Hub

---

2025-12-12: Aria Brown ID Badge Contact Fix

For: Beaumont USD (Veronica - customer support) Impact: Resolved incorrect parent name displaying on student ID badge - Stephanie Calderon was incorrectly linked to Aria Brown due to phone number collision in Aeries contact sync. Context: Customer reported wrong parent (Stephanie Calderon) on Aria Brown’s ID badge. Investigation revealed Brianna Gross’s work phone in Aeries (951-769-0711) matched Stephanie Calderon’s personal phone in Attendly, creating an erroneous user_dependent_relationship record.

Implementation:

• Investigated user_dependent_relationship and aeries_contacts_mirror tables
• Found root cause: phone number collision between Brianna Gross’s work phone and Stephanie Calderon’s personal phone
• Deleted erroneous relationship record (id: 38ffcfdf-fd2a-475d-ac04-636372d20b7c)
• Regenerated ID cards PDF with corrected contacts (Cory Brown + Brianna Gross only)

Related: Hub

Tags: beaumont reporting scholars-programs database-query data-fix