2025-12-22: P-1 PADC ADA Reporting System

For: Elias (Romoland School District) Impact: Automated P-1 PADC reporting covering 2,387.23 Regular ADA + 13.49 AR ADA across 6 schools and 4 grade spans; enables state compliance for first semester (Aug 14 - Dec 19, 84 instructional days) with auditable calculations ready for P-2 and Annual reporting cycles.

Context: California’s Principal Apportionment Data Collection (PADC) requires districts to report Average Daily Attendance by grade span for state funding. Romoland uses negative attendance tracking (recording absences rather than presence) and needed automated reporting combining Regular ADA with Attendance Recovery (AR) program impact.

Implementation

Three deliverables built:

1. District-Level ADA Summary Report - 5 rows (Regular ADA, AR ADA, + 3 placeholder rows for Extended Year Special Ed, Special Ed, Community Day School) × 6 columns (label + TK/K-3, 4-6, 7-8, 9-12, Total). District totals: 2,387.23 Regular ADA + 13.49 AR ADA = 2,400.72 total ADA.

2. School-Level Pivot Table Report - 12 rows (6 schools × 2 ADA types) × 7 columns showing per-school breakdown. Example: Boulder Ridge Elementary shows 304.45 Regular ADA + 2.39 AR ADA; Ethan A. Chase Middle (only middle school) shows 599.62 Regular + 1.86 AR.

3. HTML Report Documentation - Professional header explaining report scope, unpopulated Special Ed categories (due to API permission limitations), and validation disclaimer.

Regular ADA calculation:

• Negative attendance model: present_days = 84 - COUNT(absence_records with codes 2, 6, 7, A, E, P, Q, S, U, W, X)
• ADA = present_days ÷ 180 (California standard denominator)
• Data sources: aeries_students_mirror, aeries_attendance_mirror, joined to dependent → location for school names

AR ADA calculation:

• Grade-specific minute thresholds: TK/K: 180min, Grades 1-3: 230min, Grades 4-12: 240min
• Includes summer 2025 minutes + session-based AR minutes from July 1, 2025 onward
• 10-day recovery cap per student
• Complex UNION structure handling session-based attendance + summer-only students
• Result: 2,429 days recovered = 13.49 ADA

Key technical challenges resolved:

1. AR ADA discrepancy - Initial simplified calculation showed inflated numbers. Root cause: didn’t replicate complex UNION logic from existing AR query (session aggregation + summer-only students with no session attendance). Solution: Full replication of existing AR query including proper UNION ALL handling, grade thresholds, and 10-day cap.

2. School name resolution - aeries_students_mirror lacks school names (only codes). Joined dependent table via student_id → location via mainLocationId to get names. Handles “Unknown Location” for unmapped students.

3. Absence code alignment - Initial AR query used codes 2, 5, 6, 7, A, E, I, P, Q, S, U, W, X. Regular ADA used 2, 6, 7, A, E, P, Q, S, U, W, X. Aligned both to same codes (excluded 5 and I) for consistency.

4. SQL ORDER BY limitation - PostgreSQL doesn’t allow CASE expressions in ORDER BY for UNION queries. Added explicit sort_order column to each UNION segment.

5. Date range adjustment - Updated from initial 90 days to 84 instructional days per Elias’s request to match district calendar.

Data sources:

• aeries_students_mirror (demographics, enrollment)
• aeries_attendance_mirror (daily attendance, negative tracking)
• dependent (Attendly student records with location mappings)
• location (school/site names)
• attendance/session/program (AR participation data)

Production notes:

• Active students only: filter inactive_status_code IS NULL OR = ”
• Regular ADA period: Aug 14 - Dec 19, 2025 (84 days)
• AR recovery period: July 1, 2025 - present (includes summer program)
• Queries ready for Metabase scheduling for P-2 and Annual cycles
• Special Ed categories unpopulated: Extended Year Special Ed, Special Ed, Community Day School (requires API permission adjustments or access to specialized school codes)

Related: ATT-2122, Romoland Hub, 2025-W52 - Impact