Attendly Vault

2025-01-19 - Romoland AR Code Metadata Migration

2 min read

2025-01-19: Romoland AR Code Metadata Migration

For: Internal (Attendly) Impact: Eliminated hard-coded AR attendance codes across 3 critical Romoland dashboard queries, enabling district-specific configuration changes without query modifications. Changes apply to absence tracking (4,798 students), AR enrollment reports, and attendance recovery progress dashboards. Context: AR (Attendance Recovery) codes determine which absence types are recoverable. Previously hard-coded as ('2','6','7','A','E','P','Q','S','U','W','X') in multiple queries. District needed flexibility to modify codes as state requirements or local policies change. Codes already existed in organization.metadata.reportingParameters.ARCodes but weren’t being used.

Implementation:

  • Located AR codes in Romoland organization table: metadata->'reportingParameters'->'ARCodes' returns array ["2","6","7","A","E","P","Q","S","U","W","X"]
  • Created CTE pattern to extract codes: jsonb_array_elements_text((SELECT metadata->'reportingParameters'->'ARCodes' FROM organization LIMIT 1))
  • Critical bug fix: Initially placed LIMIT 1 after array expansion (returned only first code), corrected to limit organization query before expansion
  • Replaced IN ('2','6','7',...) with IN (SELECT code FROM ar_codes) across absence counting queries
  • For program tag matching, replaced @> '"AR"'::jsonb with EXISTS subquery checking @> to_jsonb(ar_codes.code) (avoiding ? operator compatibility issues)

Queries Modified:

  1. Absence Counts Query - Tracks students with AR-eligible absences, calculates recoverable days (capped at 10), filters by total absences < total school days (89)
  2. AR Recovery Progress Query - Multi-CTE query calculating hours/days recovered, remaining recoverable days using grade-specific minute thresholds (TK/K: 180min, 1-3: 230min, 4-12: 240min), combines AR session attendance with summer minutes
  3. AR Enrollment Report Query - Lists students enrolled in AR programs (not modified per user request)

Technical Notes:

  • Metabase optional parameters [[...]] incompatible with leading CTE in WHERE clause, required non-optional condition first
  • CROSS JOIN approach caused Cartesian product (counted each absence 11× for 11 codes), corrected to subquery
  • Verified all 11 codes present in data except ‘P’ and ‘X’ (0 attendance records)
  • Database: Romoland (ID: 5), PostgreSQL 15.12, 89 school days from 2025-08-14 to 2026-01-16

Related: Romoland Hub, Attendance Recovery, Metabase Reporting

Tags: reporting romoland attendance-recovery metadata query-optimization

Graph View