2026-01-11 - Romoland Attendance Mirror Sync Truncation Issue

Romoland Attendance Mirror Sync Truncation

Summary

The RomolandAeriesAttendanceMirror sync is failing partway through for certain schools, leaving incomplete attendance data in aeries_attendance_mirror. School 235 (Ethan A. Chase Middle School) is most affected—only 2,000 of 7,576 expected records sync for 2025-2026, a 74% data loss affecting 846 students.

The root cause is PrismaClientUnknownRequestError during batch inserts in mirrorAttendance(), likely triggered by query size limits when inserting 500 records with large JSONB payloads containing nested period-level attendance data.

Impact

Direct: AR credit/debit calculations are incorrect for students with StudentID > 1003641 at Ethan Chase Middle School. These students show zero absences in the dashboard when they actually have attendance records in Aeries.

Scope: 5,576 missing records across 846 students. The truncation is consistent—school 235’s 2025-2026 sync has failed at the same point on Dec 30 and Jan 10.

Other schools affected intermittently: Error logs show failures for schools 160 and 348 on various dates, though their data appears to recover on subsequent syncs. School 235’s failure is persistent.

Technical Details

Where It Fails

The sync processes schools sequentially, and for each school processes 2025-2026 first, then 2024-2025. The mirrorAttendance() function batches records in groups of 500 for insertion. School 235’s 2025-2026 sync consistently fails around batch 5 (records 2000-2499).

Database State (2025-2026)

School	Records	Expected	Max StudentID	Status
120	7,322	~7,322	1,017,309	OK
160	9,924	~9,924	1,017,304	OK
235	2,000	7,576	1,003,641	TRUNCATED
250	9,234	~9,234	1,017,325	OK
346	5,408	~5,408	1,017,312	OK
348	9,098	~9,098	1,017,269	OK

Error Log Evidence

From Cloud Logging (jsonPayload.event="RomolandAeriesAttendanceYearError"):

2026-01-10 21:16:42.998 PST
{
  error: { clientVersion: "6.8.2", name: "PrismaClientUnknownRequestError" },
  event: "RomolandAeriesAttendanceYearError",
  msg: "Failed to sync 2025-2026 attendance for school 235",
  schoolCode: 235,
  schoolYear: "2025-2026"
}

Historical failures across multiple schools:

• Dec 22: School 160 (2025-2026)
• Dec 25: School 235 (2024-2025)
• Dec 26: School 160 (2024-2025)
• Dec 28: School 348 (2024-2025)
• Dec 30: School 235 (2025-2026)
• Jan 03: School 348 (2024-2025)
• Jan 10: School 235 (2025-2026)
• Jan 10: School 348 (2024-2025)

Why School 235 Is Persistently Affected

School 235 is the only middle school among the primary schools. Middle schools use period-based attendance tracking with 7 classes per day in the Classes array. Each attendance record stores the full OriginalData including this array (~515 bytes per record). Elementary schools use daily tracking without the Classes array, resulting in smaller records.

The aeries_data JSONB field in mirrorAttendance() stores the entire record including OriginalData.Classes. With 500 records × ~600 bytes = ~300KB of JSONB per batch, the query likely exceeds Prisma or PostgreSQL limits.

Key Code Locations

mirrorAttendance() in mirror.ts:

• Batch size: 500 records
• Stores full aeries_data JSONB including nested OriginalData.Classes
• No per-batch error handling or logging

startAeriesAttendanceMirror() in RomolandAeriesService.ts:

• Clears entire table with DELETE FROM aeries_attendance_mirror at start
• Processes 2025-2026 first, then 2024-2025
• Catches errors at school/year level and continues (explains why 2024-2025 data is complete)

Proposed Fixes

Option 1: Reduce Batch Size (Quick Fix)

// mirror.ts
const batchSize = 100; // Was 500

Reduces query size by 5x, likely avoiding the limit. Trade-off: more database round-trips.

Option 2: Strip Unnecessary JSONB Data (Better Fix)

The OriginalData.Classes array isn’t needed for AR tracking—only the attendance code matters.

// In processAttendanceData() or mirrorAttendance()
aeries_data: {
  StudentID: record.StudentID,
  CalendarDate: record.CalendarDate,
  AttendanceCode: record.AttendanceCode,
  SchoolYear: record.SchoolYear,
  // Omit OriginalData.Classes
},

Option 3: Add Per-Batch Logging (Diagnostic)

const batchNum = Math.floor(start / batchSize) + 1;
const totalBatches = Math.ceil(attendanceData.length / batchSize);
 
logger.info({
  event: "MirrorAttendanceBatch",
  schoolCode,
  batch: batchNum,
  total: totalBatches,
  records: batch.length
});
 
try {
  await prisma.$executeRaw`INSERT...`;
} catch (batchError) {
  logger.error({
    event: "MirrorAttendanceBatchError",
    schoolCode,
    batch: batchNum,
    errorMessage: batchError?.message,
    errorCode: batchError?.code,
    errorStack: batchError?.stack,
  });
  throw batchError;
}

Recommended Approach

Implement Options 1 + 3 immediately (reduce batch size to 100, add logging), then evaluate Option 2 for a cleaner long-term fix. The reduced batch size should resolve the immediate data loss while logging confirms the root cause.

Related

• Cloud Logging filter: jsonPayload.event="RomolandAeriesAttendanceYearError"
• Affected dashboard: Romoland AR Credit/Debit Tracker
• Code files: RomolandAeriesService.ts, mirror.ts