Program Acknowledgement Steps Backfill - SAUSD

Summary

Backfilled acknowledgement steps for all 3,794 SAUSD programs, ensuring proper parent consent checkboxes are displayed during enrollment. Identified and resolved discrepancy between frontend logic and database state, updating ~3,647 programs with missing/empty acknowledgements and preserving 147 programs with existing valid data.

Impact: All SAUSD programs now have correct acknowledgement steps in the database, enabling proper parent consent collection during enrollment. High school programs (452) receive 4 checkboxes including campus-wide access permissions, while regular programs (3,342) receive 3 standard checkboxes.

Problem Statement

SAUSD programs were created before acknowledgement steps were fully implemented, resulting in:

• 3,416 programs with NULL or missing acknowledgementStepsCheckboxes metadata
• 231 programs with empty arrays [] in the metadata
• 147 programs with existing valid data (preserved)

When metadata fields are empty or missing, the frontend’s fallback logic fails to display the correct default acknowledgements, blocking proper parent consent collection.

Solution Approach

1. Program Classification Analysis

Identified two categories of SAUSD programs requiring different acknowledgement steps:

High School Programs (~452 programs):

• Identified by name patterns: “High School”, “HSAE”, “HSE”, “HSCP”, “HSCPE”, or specific program IDs
• Require 4 checkboxes including campus-wide access permission
• Use acknowledgements.default_hs_programs from dictionary

Regular Programs (~3,342 programs):

• All other SAUSD programs (CPE, ASP, BSP, AE, etc.)
• Require 3 standard checkboxes
• Use acknowledgements.default from dictionary

2. Frontend Logic Investigation

Key Finding: Discovered critical bug in frontend-user/components/ProgramPage/AcknowledgementSection.tsx:

• Component defines local HS_PROGRAMS_IDS array with 7 IDs that don’t exist in SAUSD database
• Correct IDs are in frontend-user/lib/constants.tsx (8 SAUSD-specific HS program IDs)
• This means most HS programs were falling back to default (3 checkboxes) instead of HS-specific (4 checkboxes)

Frontend Logic Flow:

1. Checks for custom metadata (if present, uses it)
2. Checks for ELOP-AR tags (Romoland-specific)
3. Checks hardcoded tenant/program rules
4. Falls back to dictionary defaults based on isHsProgram flag
5. Problem: isHsProgram uses wrong IDs, so SAUSD HS programs get wrong acknowledgements

3. Backfill Strategy

Created SQL backfill script that:

• Uses name patterns to identify ALL HS programs (not just 8 IDs)
• Updates only programs with NULL or empty arrays (preserves existing valid data)
• Includes full translations (English, Spanish, Vietnamese, Chinese Simplified)
• Separates HS programs (4 checkboxes) from Regular programs (3 checkboxes)

Technical Implementation

SQL Backfill Script

Location: program-ack-backfill/sausd/backfill.sql

Key Features:

1. High School Program Identification:

   WHERE (
     name ILIKE '%High School%'
     OR name ILIKE '% HS %'
     OR name ILIKE 'HSAE%'
     OR name ILIKE 'HSE%'
     OR name ILIKE 'HSCP%'
     OR name ILIKE 'HSCPE%'
     OR id IN (8 specific IDs from constants.tsx)
   )
   AND (
     metadata->'acknowledgementStepsCheckboxes' IS NULL
     OR jsonb_array_length(...) = 0
   )

2. Regular Program Identification:

   WHERE NOT (HS program patterns)
   AND (NULL or empty array check)

3. Metadata Update:

   • Uses jsonb_set to preserve existing metadata
   • Updates only acknowledgementStepsCheckboxes field
   • Includes all 4 language translations

Acknowledgement Content

High School Programs (4 checkboxes):

1. Expanded Learning enrollment & lottery system
2. Access to Expanded Learning Parent Handbooks (with link to https://www.sausd.us/domain/8369)
3. Medical treatment authorization (911, no coverage)
4. Campus-wide access & self sign-in permission (unique to HS)

Regular Programs (3 checkboxes):

1. Engage 360 enrollment & lottery system
2. Access to Engage 360 Parent Handbook (with link)
3. Medical treatment authorization

Key Difference: HS programs include 4th checkbox granting students permission to:

• Participate in any available program on campus
• Sign themselves in/out
• Attend multiple programs with flexible schedules

Files Created

1. program-ack-backfill/sausd/backfill.sql

   • SQL script to update all SAUSD programs
   • Includes verification query
   • Preserves existing valid data

2. program-ack-backfill/sausd/README.md

   • Complete documentation of backfill strategy
   • Explains program classification logic
   • Documents frontend bug and recommended fix

3. program-ack-backfill/sausd/ACKNOWLEDGEMENT_EXPLANATION.md

   • Detailed explanation of each acknowledgement checkbox
   • Purpose and legal requirements
   • Differences between HS and Regular programs

Database State After Backfill

Category	Count	Checkboxes	Status
High School Programs	~452	4	Updated
Regular Programs	~3,342	3	Updated
Programs with Existing Data	147	Various	Preserved
Total	3,794	-	Complete

Frontend Bug Documentation

Issue: AcknowledgementSection.tsx uses incorrect HS_PROGRAMS_IDS array

• Local definition: 7 IDs that don’t exist in SAUSD
• Correct definition: 8 IDs in constants.tsx that do exist

Impact: SAUSD HS programs currently get wrong acknowledgements in UI (3 instead of 4)

Recommendation: Update frontend to either:

1. Use name patterns (like backfill does) to identify HS programs
2. Use correct IDs from constants.tsx for SAUSD tenant

Related Work

This backfill follows the same pattern used for:

• Winters Joint USD backfill
• Beaumont USD backfill
• Romoland USD backfill
• Oakland USD backfill

Verification

After running backfill, verify with:

SELECT 
  CASE 
    WHEN (HS patterns) THEN 'HS Program (4 checkboxes)'
    ELSE 'Regular Program (3 checkboxes)'
  END as program_type,
  COUNT(*) as count,
  COUNT(CASE WHEN jsonb_array_length(COALESCE(metadata->'acknowledgementStepsCheckboxes', '[]'::jsonb)) = 0 THEN 1 END) as empty_count
FROM program
WHERE organization_id = '8c1eb747-fc57-4183-b2f7-b25c4dc540b7'
GROUP BY program_type;

Expected Results:

• HS Programs: ~452 with 4 checkboxes each, 0 empty
• Regular Programs: ~3,342 with 3 checkboxes each, 0 empty

Context

SAUSD is the largest district in this backfill effort (3,794 programs). The complexity came from:

1. Identifying HS programs by name patterns (not just IDs)
2. Ensuring we don’t overwrite existing valid data
3. Handling empty arrays vs NULL vs missing fields
4. Discovering and documenting frontend bug

The backfill ensures database consistency, but frontend code should be updated to match the correct identification logic.

Related: Hub, sausd program-acknowledgements database metadata