2025-12-08 to 2025-12-12 - Reporting Memo

Reporting Memo: December 8-12, 2025

CALPADS STAS Multi-Tenant Export

Built a single SQL query that generates California state-mandated STAS (Student Absence Summary) files across all 5 Attendly tenant databases. This involved:

• Writing CTEs that detect which tenant database the query is running against and automatically apply the correct CDS school codes for that district
• Mapping 20 individual schools to their state CDS codes across Romoland (8 schools), Oakland (7 schools), and Winters (5 schools)
• Handling a data type mismatch where SAUSD stores grades as strings (“9th”, “KG”, “TK”) while other districts use integers - wrote a normalization layer that converts all formats
• Embedding the attendance recovery calculation directly in the query but only for Romoland (the only district currently running AR programs) while returning zeros for the others
• Setting period-by-period attendance indicators for middle and high schools vs elementary

ATT-2047, ATT-2048, ATT-2049, ATT-2050, ATT-2051

Romoland AR Credit/Debit Tracker

Delivered a Flask web application that tracks attendance recovery effectiveness for Romoland. The core challenge was building an algorithm that correctly attributes when AR credits get “used.”

The problem: A student might earn AR credits in November but have absences in September. When do you count that recovery - September (when they were absent) or November (when they earned the credit)?

Built a “running balance” system that processes events chronologically:

• Tracks a per-student balance that goes negative when they’re absent
• When they earn AR credits while “in debt,” those credits immediately pay off the debt and get attributed to that month
• When they earn credits while not in debt, credits get banked in a FIFO queue by program type
• When a student with banked credits is absent later, credits are consumed from the queue and attributed to the absence month
• Enforces the 10-day annual cap per student
• Handles four different program types (Summer, Tuesday/Thursday, Saturday, Intersession) each with different minute thresholds by grade level

Also met with Romoland APs to review the dashboard and collected feedback on needed changes.

ATT-1928, ATT-1923

Winters AR Projections Dashboard

Built an interactive projection tool for Nicole Jordan to present to Winters district leadership. Created an HTML dashboard with:

• Tab navigation between different views
• LCFF funding calculator showing projected recovery amounts
• “How It Works” explainer section for non-technical stakeholders
• Deployed to Vercel so Nicole can share a link directly

Also worked with Nicole on aligning program tagging across their 26 programs - mapping which ones are funded by ASES, 21st CCLC, and ELO-P since they report differently. Configured the 21st CCLC APR demographics setup for their federal grant compliance.

ATT-2075

Oakland YES! Sports Data Correction

Fixed corrupted attendance records across Oakland’s YES! Sports Fall programs. The root cause: staff were checking students in retroactively (sometimes days after the session), so the system captured the check-in timestamp as start_time but the intended checkout as end_time - resulting in negative durations or durations spanning multiple days.

To fix Brookfield Soccer specifically, I had to:

• Parse Oakland’s reconciliation spreadsheet that maps which schools attend which time slots
• Account for gender-based time slot assignments (some slots were girls-only, some boys-only, some coed)
• Handle students from schools not in the reconciliation file by falling back to a default time slot
• Write SQL that deletes duplicate records (where a student had both a corrupted and valid record for the same session)
• Update the remaining records to use the correct session start/end times based on the school/gender mapping

For the non-Brookfield programs (Flag Football, Highland Soccer, Basketball, Skateboarding, YES! We Play Day), wrote a simpler fix that just sets attendance times to match the session times.

ATT-1990

Oakland YES! Sports Demographics Dashboard

Built a 9-query Explo dashboard for Chen showing participant demographics and program engagement. This connects to OUSD’s equity work - they’re trying to show how YES! Sports adds to student physical activity minutes (an OUSD study found only 14% of Oakland kids get 60 minutes daily, below the 23% national average). The dashboard shows multi-program engagement so they can see which students participate across multiple sports.

ATT-1987

Beaumont Winter Scholars ID Cards

Wrote a Python script that generates printable ID cards for Winter Scholars. The process:

• Query the database for all students enrolled in Winter Scholars programs
• For each student, look up their parent/guardian and emergency contact info
• Apply logic to determine which contacts should appear on the card (primary contacts first, then emergency contacts, up to 4 total)
• Handle the phone number matching issue - Aeries syncs contacts by phone number, so if two people share a phone number it can create incorrect relationships (this caused the Aria Brown bug where the wrong parent was showing)
• Populate the card template with student name, grade, pickup time, and contact info
• Format for Avery 5911 card stock (2x3.5”)

Also generalized the parent response report query to handle Winter Scholars’ two-question format (transportation needs + AR consent per Ed Code 37223) vs Fall Scholars’ single-question format. The query now dynamically extracts responses based on question text rather than hardcoded question IDs.

Fixed a text bug where the registration still said “Fall Scholars Camp” - updated it to “Winter Scholars Camp” across all 4 languages (EN, ES, VI, ZH) using jsonb_set operations.

ATT-1949, ATT-1950

SAUSD Report Standardization

Cleaned up enrollment, waitlist, and ADA reports for Santa Ana. The reports were including Engage 360 programs (which should be separate) and showing waitlist entries for programs that had already ended. Updated the queries to:

• Filter out Engage 360 programs from standard ASP/BSP reports
• Only show waitlist entries for current or future programs
• This removed over a thousand stale entries that were inflating their capacity numbers

Infrastructure Cleanup

Removed the ReportReorgAlert banner component from the frontend-admin reporting views. The banner was added to announce the November 28 report reorganization - it served its purpose and was cluttering the UI.

ATT-2108

Tickets Completed

ID	Title	District	Type
ATT-2047	Create Romoland STAS file	Romoland	Compliance
ATT-2048	Create Beaumont STAS file	Beaumont	Compliance
ATT-2049	Create Santa Ana STAS file	SAUSD	Compliance
ATT-2050	Create Winters JUSD STAS file	Winters	Compliance
ATT-2051	Create Oakland STAS file	Oakland	Compliance
ATT-1987	Demographic program report	Oakland	Reporting
ATT-1990	Attendance data corrections	Oakland	Data Quality
ATT-1949	Winter Scholars Parent Response	Beaumont	Reporting
ATT-1950	Winter Scholars ID Cards	Beaumont	Deliverable
ATT-2075	AR projections for WJUSD	Winters	Reporting
ATT-1928	Month attribution logic	Romoland	Development
ATT-1923	AR eligibility investigation	Romoland	Analysis
ATT-2108	Remove Transition Banner	Internal	Cleanup