Attendly Vault

Program Acknowledgement Steps Backfill - Multi-District

5 min read

Summary

Completed comprehensive backfill of program acknowledgement steps across 5 school districts (Winters Joint USD, Beaumont USD, Romoland USD, Oakland USD, and SAUSD), ensuring all programs have proper parent consent checkboxes in their metadata. Total of ~4,500+ programs updated across all districts.

Impact: All programs now have correct acknowledgement steps in the database, enabling proper parent consent collection during enrollment. Each district has appropriate acknowledgements based on their specific program types and requirements.

Districts Covered

1. Winters Joint USD

  • Programs: ~8 programs
  • Categories: Intersession programs, Running Club, Default
  • Special Logic: Intersession programs get custom policy acknowledgements
  • Files: program-ack-backfill/winters/

2. Beaumont USD

  • Programs: ~168 programs
  • Categories: Clinic Programs (11 checkboxes), Opportunity Programs (10 checkboxes), Default (10 checkboxes)
  • Special Logic: Clinic programs require extensive liability waivers for sports activities
  • Files: program-ack-backfill/beaumont/

3. Romoland USD

  • Programs: ~50+ programs
  • Categories: Fall 2025 BSP (custom), Fall 2025 ASP (policy only), ELOP-AR programs (excluded - use special component)
  • Special Logic: ELOP-AR programs use dedicated React component, not standard checkboxes
  • Files: program-ack-backfill/romoland/

4. Oakland USD

  • Programs: ~17 programs
  • Categories: All use default (3 checkboxes)
  • Special Logic: No custom logic - all programs use standard default acknowledgements
  • Files: program-ack-backfill/oakland/

5. SAUSD (Santa Ana Unified)

  • Programs: 3,794 programs
  • Categories: High School Programs (4 checkboxes), Regular Programs (3 checkboxes)
  • Special Logic: HS programs identified by name patterns (HSAE, HSE, HSCP, etc.) - ~452 programs
  • Files: program-ack-backfill/sausd/
  • See: Program Acknowledgement Steps Backfill - SAUSD

Common Patterns Across Districts

Problem Statement

All districts had the same core issue:

  • Programs created before acknowledgement steps feature was implemented
  • Empty or missing acknowledgementStepsCheckboxes metadata
  • Frontend fallback logic fails when metadata is empty (not just missing)

Solution Pattern

  1. Analyze Frontend Logic: Review AcknowledgementSection.tsx to understand classification rules
  2. Query Database: Identify programs with missing/empty acknowledgements
  3. Classify Programs: Map program characteristics (tenant ID, tags, names, IDs) to acknowledgement types
  4. Generate SQL: Create backfill script with proper translations
  5. Preserve Data: Only update NULL/empty, preserve existing valid data

Technical Approach

Metadata Structure:

{
  "acknowledgementStepsCheckboxes": [
    {
      "en": "English text",
      "es": "Spanish text",
      "vi": "Vietnamese text",
      "zh_Hans": "Chinese text"
    }
  ]
}

Update Strategy:

  • Use jsonb_set to preserve existing metadata
  • Only update acknowledgementStepsCheckboxes field
  • Include all 4 language translations
  • Filter to only update NULL or empty arrays

SQL Pattern:

UPDATE program
SET metadata = jsonb_set(
  COALESCE(metadata, '{}'::jsonb),
  '{acknowledgementStepsCheckboxes}',
  '[...]'::jsonb
)
WHERE organization_id = '<tenant-id>'
  AND (
    metadata->'acknowledgementStepsCheckboxes' IS NULL
    OR jsonb_array_length(COALESCE(metadata->'acknowledgementStepsCheckboxes', '[]'::jsonb)) = 0
  )
  AND (program classification logic);

District-Specific Variations

Winters Joint USD

  • Tenant ID: 34165a11-c6de-4ae7-b53c-fd6e885f3ee5
  • Special Cases:
    • Intersession programs by name pattern
    • Running Club by specific program ID
  • Acknowledgements: Custom Winters policy acknowledgements

Beaumont USD

  • Tenant ID: 7ff4f69f-faa4-49c6-95d4-1b2ba5ac09ec
  • Special Cases:
    • Clinic programs by specific IDs (sports clinics with liability waivers)
    • Opportunity programs by “opportunity” tag
  • Acknowledgements: Tenant-specific with extensive clinic waivers

Romoland USD

  • Tenant ID: 65e1f9de-bc6c-4195-af3b-0cb965f4398e
  • Special Cases:
    • Fall 2025 BSP programs (custom + policy)
    • Fall 2025 ASP programs (policy only)
    • ELOP-AR programs excluded (use ElopArAcknowledgment component)
  • Acknowledgements: Custom Romoland fall BSP acknowledgements

Oakland USD

  • Tenant ID: 22c17bbd-e77c-4a12-aa65-6e6cc5a54af4
  • Special Cases: None - all programs use default
  • Acknowledgements: Standard default (3 checkboxes)

SAUSD

  • Tenant ID: 8c1eb747-fc57-4183-b2f7-b25c4dc540b7
  • Special Cases:
    • High School programs by name patterns (HSAE, HSE, HSCP, HSCPE, “High School”)
    • ~452 HS programs get 4 checkboxes (includes campus-wide access)
    • ~3,342 regular programs get 3 checkboxes
  • Acknowledgements: Default HS programs vs Default regular

Key Learnings

1. Empty Arrays vs NULL

  • Empty arrays [] block fallback logic just like NULL
  • Must check for both: IS NULL OR jsonb_array_length(...) = 0
  • Frontend treats empty arrays as “has data” so fallback doesn’t trigger

2. Frontend Fallback Logic

  • Checks custom metadata first
  • Then checks hardcoded rules (tenant ID, tags, program IDs)
  • Finally falls back to dictionary defaults
  • Empty metadata fields prevent fallback from working

3. Program Identification Strategies

  • By Program ID: Most precise but requires maintenance
  • By Name Pattern: More flexible, catches variations
  • By Tags: Good for program categories
  • By Tenant ID: Fallback for district-wide defaults

4. Translation Requirements

All acknowledgements must include:

  • English (en) - Primary
  • Spanish (es) - Required for most districts
  • Vietnamese (vi) - Common in California districts
  • Chinese Simplified (zh_Hans) - For diverse communities

Files Structure

program-ack-backfill/
├── winters/
│   ├── backfill.sql
│   └── README.md
├── beaumont/
│   ├── backfill.sql
│   └── README.md
├── romoland/
│   ├── backfill.sql
│   └── README.md
├── oakland/
│   ├── backfill.sql
│   └── README.md
└── sausd/
    ├── backfill.sql
    ├── README.md
    └── ACKNOWLEDGEMENT_EXPLANATION.md

Verification Process

For each district:

  1. Count programs by classification
  2. Verify checkbox counts match expected
  3. Check that existing valid data was preserved
  4. Sample a few programs to verify content

Example Verification Query:

SELECT 
  classification,
  COUNT(*) as total,
  COUNT(CASE WHEN jsonb_array_length(COALESCE(metadata->'acknowledgementStepsCheckboxes', '[]'::jsonb)) = 0 THEN 1 END) as empty
FROM program
WHERE organization_id = '<tenant-id>'
GROUP BY classification;
  • Program Acknowledgement Steps Backfill - SAUSD - Detailed SAUSD documentation
  • Frontend logic: frontend-user/components/ProgramPage/AcknowledgementSection.tsx
  • Dictionary definitions: frontend-user/dictionaries/en.json
  • Constants: frontend-user/lib/constants.tsx

Next Steps

  1. Frontend Updates: Update AcknowledgementSection.tsx to use correct identification logic (especially for SAUSD HS programs)
  2. Testing: Verify acknowledgements display correctly in UI for each district
  3. Monitoring: Track enrollment completion rates to ensure acknowledgements aren’t blocking enrollment

Related: Hub, backfill program-acknowledgements database multi-district

Graph View

Backlinks