Program Registration Audit Report
This document describes the Program Registration Audit Report built in Explo for Beaumont USD. The report enables district administrators and auditors to verify program registration compliance, including parent acknowledgement signatures and program-specific terms acceptance.
Purpose
Districts face recurring audit requirements to demonstrate that parents have acknowledged and signed off on program enrollment terms. Previously, this required manual review of individual enrollment records scattered across the database. This report consolidates all registration compliance data into a single, filterable view.
Primary use cases:
- Student-specific audits run from the student card
- Program-wide compliance reviews filtered by date range
- Auditor documentation of parent signature verification
Data Sources
The report joins four core tables in the Beaumont database:
| Table | Key Fields Used |
|---|---|
| enrollment | state, created_at, metadata (termsAcceptance) |
| dependent | first_name, last_name, metadata (schoolStudentId, grade, mainLocationId) |
| program | name, description, metadata (acknowledgementStepsCheckboxes) |
| location | name (joined via mainLocationId) |
Parent signatures and timestamps are stored in enrollment.metadata.termsAcceptance. Program acknowledgement steps are stored as multilingual arrays in program.metadata.acknowledgementStepsCheckboxes, with each step containing translations for English, Spanish, Vietnamese, and Chinese.
Report Columns
| Column | Description |
|---|---|
| First Name / Last Name | Student name from dependent table |
| Student ID | School student ID from dependent metadata |
| School Name | Location name via mainLocationId join |
| Program Name | Program being enrolled in |
| Program Description | Full program description text |
| Acknowledgement Steps | Numbered list of program-specific terms (English only) |
| Registration Date/Time | Enrollment creation timestamp (Pacific timezone) |
| Parent Name/Signature | Parent signature from termsAcceptance |
| Acknowledgment Signed Date/Time | Signature timestamp (Pacific timezone) |
| Grade | Student grade level (formatted: PK, TK, K, 1st-12th) |
Available Filters
- Student Name - Optional LIKE pattern search
- Program - Required single-select program filter
- Date Range - Optional start/end date filter
- Site - Optional school site filter
- Grade - Optional grade level filter
Technical Notes
Acknowledgement step extraction: The query uses jsonb_array_elements with WITH ORDINALITY to parse the acknowledgement steps array and automatically number each step. A CASE statement handles both object format (with translations) and plain string format, extracting only the English text.
Enrollment states included: Both complete and waitlist states are included, as waitlisted students have also completed the registration acknowledgement process.
Timestamp formatting: All timestamps display in Pacific timezone with MM/DD/YYYY HH12:MI:SS AM format for auditor readability.
HTML presentation: The report uses two HTML components in Explo—one for program description and one for acknowledgement steps—with professional styling including gray content boxes and a yellow compliance reminder note.
Related
- Linear: ATT-2099
- Prior work: ATT-1645 (Internal Audit Report for Registration Compliance)
- Location: Explo dashboard for Beaumont USD