Attendly Vault

2026-01-02 - Program Registration Audit Report

5 min read

Program Registration Audit Report

Date: 2026-01-02
District: Beaumont USD
Linear: ATT-2099
Impact: Compliance & Auditing
Status: Complete

Overview

Created a comprehensive Program Registration Audit Report in Explo that displays student enrollment information, parent signatures, and program acknowledgement tracking. This report enables administrators to verify compliance with program registration requirements and provides auditors with complete documentation of parent acknowledgements.

Business Need

Problem

Districts needed a way to:

  • Verify that parents have signed acknowledgement forms for program enrollment
  • Audit registration compliance for specific programs or students
  • Document which acknowledgement steps parents agreed to at registration
  • Track registration dates and parent signature timestamps

Use Cases

  1. Student-specific audits: Run from student card to see all program registrations for one student
  2. Program-wide audits: Filter by program and date range to review all registrations for compliance
  3. Acknowledgement verification: Confirm parent signatures and timestamps for audit purposes

Technical Implementation

Database Schema Analysis

Used Metabase tools to explore Beaumont database structure:

  • enrollment table: Contains registration records with state (‘complete’, ‘waitlist’), created_at timestamp, and metadata with parent signature info
  • program table: Has description column (text) and metadata (jsonb) containing acknowledgement steps
  • dependent table: Student information with metadata containing schoolStudentId, grade, and mainLocationId
  • location table: School information joined via mainLocationId

Key metadata fields discovered:

// enrollment.metadata
{
  "termsAcceptance": {
    "signature": "Parent Name",
    "timestamp": "2025-11-06T06:59:10Z"
  }
}
 
// program.metadata
{
  "acknowledgementStepsCheckboxes": [
    {"en": "I acknowledge...", "es": "...", "vi": "...", "zh_Hans": "..."}
  ]
}

SQL Query Development

Built query with these components:

Core joins:

FROM enrollment
  INNER JOIN dependent ON enrollment.dependent_id = dependent.id
  INNER JOIN program ON enrollment.program_id = program.id
  LEFT JOIN location ON location.id = (dependent.metadata->>'mainLocationId')::uuid

Acknowledgement steps parsing: Used subquery to extract English-only text from multilingual acknowledgement steps and format with numbering:

(
  SELECT STRING_AGG(
    step_order || '. ' || CASE 
      WHEN jsonb_typeof(step_value) = 'object' THEN step_value->>'en'
      ELSE step_value::text
    END,
    E'\n' ORDER BY step_order
  )
  FROM jsonb_array_elements(program.metadata->'acknowledgementStepsCheckboxes') 
    WITH ORDINALITY AS t(step_value, step_order)
) AS "program_acknowledgement_steps"

Key features:

  • Extracts only English text from acknowledgement steps (handles both object format with translations and plain text)
  • Numbers each step automatically (1., 2., 3., etc.)
  • Separates steps with single newlines for proper formatting
  • Uses TRIM() on program description to remove leading/trailing whitespace
  • Formats timestamps in Pacific timezone with MM/DD/YYYY HH12:MI:SS AM format
  • Includes both ‘complete’ and ‘waitlist’ enrollment states

Parameters:

  • {{student_name}} - Optional filter by student first/last name (LIKE pattern)
  • {{program}} - Required program name filter (single select)
  • {{date.startDate}} / {{date.endDate}} - Optional date range filter
  • {{sites}} - Optional school site filter (single select)
  • {{grade}} - Optional grade filter (single select)

HTML Presentation

Created two-component presentation in Explo:

Component 1: Program Information

<div style="font-family: Arial, sans-serif; max-width: 9000px; margin: 20px 0;">
  <h3 style="color: #2c3e50; border-bottom: 2px solid #3498db; padding-bottom: 10px;">
    Program Information
  </h3>
  <h4 style="color: #2c3e50; margin: 20px 0 10px 0;">Program Description</h4>
  <div style="line-height: 1.8; color: #34495e; white-space: pre-line; 
              padding: 15px; background-color: #f8f9fa; 
              border-left: 4px solid #6c757d; margin: 0;">
    {{audit_registration.program_description}}
  </div>
</div>

Component 2: Acknowledgement Steps

<div style="font-family: Arial, sans-serif; max-width: 9000px; margin: 0;">
  <h4 style="color: #2c3e50; margin: 20px 0 10px 0;">Acknowledgement Steps</h4>
  <div style="line-height: 1.6; color: #34495e; white-space: pre-line; 
              padding: 15px; background-color: #f8f9fa; 
              border-left: 4px solid #6c757d; margin: 0;">
    {{audit_registration.program_acknowledgement_steps}}
  </div>
  <div style="line-height: 1.6; color: #34495e; margin: 15px 0 0 0; 
              padding: 15px; background-color: #fff3cd; 
              border-left: 4px solid #ffc107; text-indent: 0;">
    <strong>Important:</strong> All parents/guardians enrolling students in this 
    program must acknowledge and sign off on the steps listed above.
  </div>
</div>

Key styling decisions:

  • white-space: pre-line - Honors newlines but collapses other whitespace
  • line-height: 1.6 - Provides comfortable reading spacing without excessive gaps
  • Gray boxes (#f8f9fa) for program information sections
  • Yellow warning box (#fff3cd) for important compliance note
  • Blue accents (#3498db) for headers matching other reports
  • Explicit margin: 0 and text-indent: 0 to eliminate unwanted indentation

Technical Challenges & Solutions

Challenge 1: Multilingual acknowledgement steps

  • Problem: Steps stored as JSON objects with translations (en, es, vi, zh_Hans)
  • Solution: Used jsonb_typeof() to detect object format, then extracted English text with ->>'en'

Challenge 2: Inconsistent formatting

  • Problem: Some programs had acknowledgements as plain strings, others as translation objects
  • Solution: CASE statement handles both formats - checks type and extracts accordingly

Challenge 3: HTML rendering issues

  • Problem: Initially tried single text block, HTML tags rendered as literal text
  • Solution: Split into separate HTML components in Explo, each parsed independently

Challenge 4: Excessive line spacing

  • Problem: Used double newlines (\n\n) and high line-height (2.2), created huge gaps
  • Solution: Changed to single newlines (\n) and reduced line-height to 1.6

Challenge 5: Text indentation

  • Problem: Browser default styling added unwanted indentation to paragraphs
  • Solution: Added explicit text-indent: 0 and margin: 0 to all content containers

Challenge 6: Program description location

  • Problem: Initially looked for description in program.metadata->>'description'
  • Solution: Discovered description is in dedicated program.description column (text type, not jsonb)

Output Columns

ColumnSourceFormat
First Namedependent.first_nameText
Last Namedependent.last_nameText
Student IDdependent.metadata→>‘schoolStudentId’Text
School Namelocation.nameText
Program Nameprogram.nameText
program_descriptionprogram.descriptionText (trimmed)
program_acknowledgement_stepsprogram.metadataNumbered list (English only)
Registration Date/Timeenrollment.created_atMM/DD/YYYY HH12:MI:SS AM
Parent Name/Signatureenrollment.metadataText
Acknowledgment Signed Date/Timeenrollment.metadataMM/DD/YYYY HH12:MI:SS AM
Gradedependent.metadata→>‘grade’Formatted (PK, TK, K, 1st-12th)
Grade_Sortdependent.metadata→>‘grade’Integer (hidden, for sorting)

Business Impact

Value Delivered

  • Compliance verification: Admins can quickly verify all required acknowledgements are signed
  • Audit readiness: Complete documentation trail for program registrations
  • Student-specific queries: Can run from student card for individual student audits
  • Program-wide audits: Can review all registrations for a specific program within date range
  • Time savings: Eliminates manual review of individual enrollment records

Key Features

  • Includes both completed enrollments and waitlisted registrations
  • Extracts only English text from multilingual acknowledgements
  • Automatically numbers acknowledgement steps for clarity
  • Shows parent signature name and exact timestamp
  • Handles missing signatures gracefully (shows blank)
  • Professional formatting matches other district reports

Future Enhancements

Potential improvements:

  • Add filter to show only students missing acknowledgements
  • Export to PDF for auditor submission
  • Add column showing which specific steps were acknowledged (if stored in enrollment metadata)
  • Summary count of registrations with/without signatures
  • Link to student profile page from student ID
  • Attendance Recovery reporting queries
  • P-1 PADC ADA Report structure
  • Program metadata standardization across districts

Report accessible in Explo dashboard for Beaumont USD administrators and auditors

Graph View