2026-01-08 - Early Care Extended Care Report Generalization

2026-01-08: Generalized Early Care & Extended Care Response Report

For: Romoland Admin Impact: Report now works for any program with Early/Extended Care questions (Jan 24, Dec 13, Thanksgiving, future programs) without needing new queries each time Context: Original queries were hardcoded to “Dec 13” programs; admin needed same report for Jan 24 AR registration

Problem: The Early Care & Extended Care Response Report was date-specific, requiring new queries for each Saturday AR program event.

Solution: Generalized queries to match programs by their registration question content rather than program name patterns.

Key Technical Approach:

WHERE 
    p.metadata::text ILIKE '%Early Drop off 7am-8am%'
    AND p.metadata::text ILIKE '%Extended Day 12pm-4pm%'
    AND jsonb_array_length(p.metadata->'acknowledgementStepsQuestions') = 2

Deliverables:

1. Generalized Response Report Query - Main report with programFilter parameter for selecting which program event to display

2. Generalized Enrollment Status by Site Query - Stacked bar graph showing Enrolled vs Waitlisted counts per site

3. Program Dropdown Query - Returns distinct program names with these questions in reverse chronological order for filter selection

4. Updated HTML Blurb - Explains report is generalized, requires program selection, and only works for programs with these specific questions

Data Validation:

• Found 32 programs matching criteria across 3 question format variations (HTML styled, plain text, alt HTML)
• All have exactly 2 questions in same order: Early Drop-off first, Extended Day second
• Programs span: Jan 24 (2026), Dec 13 (2025), Thanksgiving (2025)

Related: 2025-12-15 - AR Data Sheet Update, 2025-12-30 - AR Hours Recovered Dashboard Update