2025-12-16 - AR Audit Simulator Dashboard

2025-12-16: AR Audit Simulator Dashboard

For: Adam/Surya (Modesto demonstration) Impact: Interactive compliance dashboard that walks districts through state AR audit procedures, enabling proactive audit readiness and reducing compliance risk before state auditors arrive Context: Created to demonstrate AR compliance capabilities to Modesto; built using Romoland data as proof-of-concept. Addresses the 2025-26 Guide for Annual Audits of K-12 Local Education Agencies and State Compliance Reporting procedures.

Implementation

Dashboard Components:

• Step-by-step audit procedure walkthrough matching state audit guide structure
• HTML text blurbs for each audit section (a-h) with proper formatting for Explo text boxes
• Sample selection interface for pupil-level compliance verification

Audit Procedures Covered:

• a. Charter School Verification (classroom-based program enrollment)
• b. Independent Study Limitation (15-day IS enrollment check)
• c. Separate Attendance Tracking (AR register separation)
• d. Whole Day ADA Requirement (whole days only)
• e. Annual Attendance Cap (10-day or absences cap, whichever fewer)
• f. Minimum Daily Minute Requirement (grade-band thresholds: TK-K 180min, 1-3 230min, 4-6+ 240min)
• g. Participation Time Procedures (teacher certification, 10:1 TK-K ratio, 20:1 other grades, grade-aligned content)
• h. Daily/Weekly Attendance Limits (1 day per calendar day, 5 days per school week for districts/COEs)

Supporting Queries Built:

1. AR Attendance Report with Minutes - Filters to AR-tagged programs, calculates participation minutes from check-in/out delta, uses @> operator instead of ? for Metabase JSONB compatibility
2. Hours Recovered with Compliance Checks - Implements daily caps (1 day max per calendar day), weekly caps (5 days per school week), bad data exclusion (end_time > start_time)
3. IS Student Identification Query - Pulls Independent Study students from aeries_students_mirror table using attendance_program_code_primary = 'I' for 15-day IS compliance check

Reference Documentation:

• Source: 2025-26 Annual Audit Guide

Technical Notes:

• Explo text boxes have character limits; split audit procedures into multiple text components
• Used simple HTML/markdown formatting (h3, h4, p, ul) rather than complex div styling for Explo compatibility
• JSONB array check syntax: program.metadata->'tags' @> '"AR"'::jsonb (Metabase interprets ? as parameter placeholder)

Related: 2025-12-15 - AR Funding Projections, 2025-W51 - Impact

Tags: reporting ar-compliance audit modesto romoland explo dashboard