Investigation: 2026-02-27 — Import Jobs Get Stuck in Processing

Investigation: 2026-02-27 — Import Jobs Get Stuck in Processing

Summary

Import batches that enter Processing status and then fail (due to service restart, crash, DB timeout, or unhandled exception) remain stuck in Processing forever. The strict priority ordering in ImportBatchSchedulerJob means a single stuck batch blocks ALL lower-priority import types for that entire organization. There is no timeout, staleness detection, or auto-recovery.

Field	Value
Severity	SEV3 — Recurring systemic issue
Status	Root cause identified; R102 rewrote scheduler (HM-8221) — see Release 102 Changes
Investigated by	Leon Geldenhuys
Investigation date	2026-02-27
Affected system	Job Scheduler — Import processing pipeline
Impact	Stuck imports block all subsequent imports for the affected organization
Jira	HM-8484

---

How the System Worked (Release 101)

Release 102 rewrote this

The scheduler described below was the release 101 behavior. Release 102 replaced it with a simpler FIFO approach. See Release 102 Changes for the current behavior and its trade-offs.

sequenceDiagram
    participant Scheduler as ImportBatchSchedulerJob<br/>(runs periodically)
    participant Process as ProcessImportBatchJob
    participant Service as EntityImportService
    participant DB as ImportBatch table

    Scheduler->>DB: Query batches (Submitted/Processing)
    DB-->>Scheduler: Batch list per org
    Note over Scheduler: Priority ordering:<br/>1. District<br/>2. Campus<br/>3. Student<br/>4. User/ARD/Goals...

    Scheduler->>Process: Schedule trigger (staggered delay)
    Process->>DB: Set status = Processing
    Process->>Service: ProcessImportBatch(batchId)
    Service->>Service: Process records in chunks

    alt Success
        Service-->>Process: Done
        Process->>DB: Set status = Complete
    else Crash/Restart/Exception
        Note over DB: Status stays "Processing" forever
        Note over Scheduler: Next run: sees Processing,<br/>blocks lower-priority types
    end

Key Components

Component	File	Role
ImportBatchSchedulerJob	ImportBatchSchedulerJob.cs:14	Periodic scheduler — picks up batches, enforces priority ordering
ProcessImportBatchJob	ProcessImportBatchJob.cs:15	Processes a single batch — sets status to Processing/Complete
EntityImportService	EntityImportService.cs:684	Actual record processing loop

---

Root Causes

1. No Recovery from Crash/Restart

Once a batch is set to Processing (line 818), if the service crashes or restarts, the batch stays Processing forever. There is no:

• Timeout detection
• Staleness check
• Heartbeat mechanism
• Startup recovery logic

The fundamental gap

The job scheduler has no concept of “this batch has been Processing for too long and should be considered failed.” It trusts the status column unconditionally.

2. Priority Blocking Cascade

ImportBatchSchedulerJob enforces strict priority ordering (lines 42-110):

DistrictUpload → CampusUpload → StudentUpload → UserUpload → ARD → Goals → ...

If a DistrictUpload batch is stuck in Processing, the continue statements on lines 48-49 and 63-64 cause the scheduler to skip ALL lower-priority batch types for that organization. A single stuck high-priority batch halts the entire import pipeline.

graph TD
    A[DistrictUpload<br/>STUCK in Processing] -->|blocks| B[CampusUpload]
    B -->|blocks| C[StudentUpload]
    C -->|blocks| D[UserUpload]
    D -->|blocks| E[ARD Upload]
    E -->|blocks| F[Goals Upload]
    F -->|blocks| G[Prescriptions]
    G -->|blocks| H[All other types...]

    style A fill:#ff6b6b,color:#fff
    style B fill:#ffa94d,color:#fff
    style C fill:#ffa94d,color:#fff
    style D fill:#ffa94d,color:#fff
    style E fill:#ffa94d,color:#fff
    style F fill:#ffa94d,color:#fff
    style G fill:#ffa94d,color:#fff
    style H fill:#ffa94d,color:#fff

3. waitForCurrentJobToFinish Deadlock

For StudentUpload, StudentParentalConsent, and StudentPrescription, the waitForCurrentJobToFinish=true flag means:

• If a batch is stuck in Processing, the scheduler re-schedules that same stuck batch instead of processing new ones (lines 144-149)
• New batches for these types are permanently blocked until the stuck batch is manually fixed

4. Silent Error Handling

ProcessImportBatchJob wraps everything in Task.Run().Wait() (lines 31-63). The catch block on line 59 only logs — it does NOT reset the batch status:

catch (Exception ex)
{
    Log.Error(ex.Message, ex);
    // MISSING: batch.ImportBatchStatus = ImportBatchStatus.Failed;
}

After any unhandled exception, the batch remains in Processing forever.

---

Existing Monitoring Gaps

SystemHealthNotificationJob is designed to alert on delayed imports, but has several issues:

Gap	Detail
Notification only	Alerts but takes no corrective action
Webhook URL	Line 48 initializes with "" — may not be configured in production
Feature flag	Requires SystemHealthNotificationFlagImports = true in app settings
Silent failures	Line 209 catches all exceptions without logging

---

Diagnostic Queries

Find Stuck Batches

SELECT Id, FileName, ImportBatchType, ImportBatchStatus,
       ReceivedDate, ProcessedDate, ProcessedCount, RowCount,
       OrganizationId
FROM ImportBatch
WHERE ImportBatchStatus = 6  -- Processing
ORDER BY ReceivedDate;

Find Batches Stuck Longer Than N Hours

SELECT Id, ImportBatchType, OrganizationId,
       ReceivedDate, ProcessedDate,
       DATEDIFF(HOUR, ISNULL(ProcessedDate, ReceivedDate), GETUTCDATE()) AS HoursStuck
FROM ImportBatch
WHERE ImportBatchStatus = 6
  AND DATEDIFF(HOUR, ISNULL(ProcessedDate, ReceivedDate), GETUTCDATE()) > 4
ORDER BY HoursStuck DESC;

Quick Fix: Reset Stuck Batch

-- Reset a stuck Processing batch back to Submitted
UPDATE ImportBatch
SET ImportBatchStatus = 5  -- Submitted
WHERE Id = @stuck_batch_id
  AND ImportBatchStatus = 6;  -- Only if still Processing

Caution

Verify the batch is actually stuck (not legitimately processing) before resetting. Check JobExecutionLog for recent activity on that batch ID.

---

Fix Priority

Priority	Ticket	Fix	R102 Status	Risk
High	HM-8485	Add staleness detection and auto-reset for stuck batches	Not fixed — still no staleness detection	Medium
Medium	HM-8486	Fix priority blocking cascade in ImportBatchSchedulerJob	Changed — cascade removed, but replaced with total-block-on-any-Processing	Medium
Medium	HM-8487	Fix ProcessImportBatchJob error handling to reset status on failure	Not fixed — catch block still only logs	Low
Low	HM-8488	Verify and fix Teams alerting for stuck imports	Not fixed	Low

Parent story: HM-8484 under epic HM-8477 — eSHARS Application Performance & Reliability

---

Recurrences

2026-03-02 — East Central ISD & Northeast ISD (ARD Imports)

Same districts, same root cause, different import IDs. Luis re-uploaded ARD files for East Central ISD (Job ID 102416) and Northeast ISD (Job ID 102420) on Friday 2026-02-27 and processed them. The files contained bad student IDs that were not fixed from the previous upload. Every row hit the Student not found for StudentNumber exception in StudentArdImportService.ProcessBatchRecord() (line 64), causing the batch to get stuck.

What happened:

1. Luis uploaded ARD files containing invalid student numbers
2. He processed them without fixing the bad data from the previous attempt
3. Each row threw ApplicationException("Student not found for StudentNumber '...'") in StudentArdImportService.cs:64
4. The batches stayed in Processing / Submitted status, blocking the import pipeline for both districts
5. These are the same files that had to be terminated on Friday (2026-02-27)

Resolution: Sharad set the import batches to CompleteWithErrors (status 3) directly, unblocking the pipeline so Luis could re-upload with corrected files.

Contributing factor — no pre-validation: The system accepts ARD files with invalid student numbers at upload time. Validation only happens during processing (ProcessBatchRecord), meaning bad files consume pipeline resources and block other imports before failing. See Prevention below.

---

Release 102 Changes (HM-8221)

Release 102 (hm-1.3.102.0-sprint1) rewrote ImportBatchSchedulerJob and made several other changes to jobs/imports. The sections above describe the release 101 behavior. This section documents what changed and what new risks were introduced.

ImportBatchSchedulerJob — complete rewrite

The type-based priority scheduler was replaced with a simple FIFO queue:

Release 101 (before)

Per org, in strict order:
DistrictUpload (30s delay) → CampusUpload (30s) → [if either scheduled, stop]
StudentUpload (120s, wait) → StudentTrRoute (120s) → User (60s) → Calendar (30s) → [if any, stop]
ARDUpload (120s) → [if scheduled, stop]
PaidVisitData → Goals → ParentalConsent (wait) → Prescription (wait) → DNQ →
EasyIep → Nursing → PersonalCare → ProviderCert → SmartTag → EvalVisit

- Staggered delays between batches (30-120 seconds)
- Type priority ordering with cascading `continue` statements
- waitForCurrentJobToFinish re-schedules Processing batches
- Multiple batches of same type scheduled in one pass

Release 102 (after)

// Per org: simple FIFO, one at a time
private async Task<bool> ScheduleNextImportForOrg(context, batchesForAnOrg)
{
    // If ANY batch is Processing → wait
    if (batchesForAnOrg.Any(b => b.Status == Processing))
        return false;

    // Pick next Submitted batch by Id (FIFO)
    var next = batchesForAnOrg
        .Where(b => b.Status == Submitted)
        .OrderBy(b => b.Id)
        .FirstOrDefault();

    if (next == null) return false;
    await ScheduleProcessImportBatchJob(next.Id, next.Type, context);
    return true;
}

What 102 fixed

• Priority blocking cascade (Root Cause #2 above): No more continue statements skipping lower-priority types
• waitForCurrentJobToFinish deadlock (Root Cause #3): Removed entirely — no more re-scheduling of stuck batches

What 102 may have broken

Issue	Detail	Severity
Stuck batch blocks all	If ANY batch is Processing, the entire org waits. Old code at least processed other types. The old re-scheduling logic was a crude form of “checking on” stuck batches — that’s gone now.	HIGH
No type priority	A low-priority EvalVisitUpload submitted before a critical DistrictUpload will process first. The old priority ordering ensured foundational data (districts, campuses, students) was imported before dependent data (visits, goals, prescriptions).	MEDIUM
No staggered delays	Triggers fire immediately. Old code spaced batches 30-120 seconds apart to prevent resource contention.	MEDIUM
One batch at a time	Old code could schedule multiple batches of different types in parallel. New code processes strictly one at a time per org, potentially much slower throughput.	MEDIUM

The stuck batch problem is worse in 102

In release 101, a stuck DistrictUpload blocked lower-priority types but other types could still process if no higher-priority was stuck. In release 102, a stuck batch of any type blocks all types for that org, because the check is type-agnostic: batchesForAnOrg.FirstOrDefault(b => b.Status == Processing).

Recommended investigation

1. Query for stuck Processing batches in production (see Diagnostic Queries)
2. Compare import throughput before/after 102 deployment
3. Check if the FIFO ordering is causing dependency issues (e.g., visit imports running before student imports)

---

Other release 102 changes affecting jobs/imports

BillTransactionJob — Transportation billing (HM-8204, HM-8240)

New: TR billing ignore list. A BillingTransactionForTRVisit table allows bypassing the transportation validation for specific billing transactions (3 newly onboarded districts):

_billingTransactionIdToIgnoreForTr = _ediUnitOfWork
    .Repository<BillingTransactionForTRVisit>()
    .Get()
    .Select(t => t.BillingTransactionId).ToList();

Changed: “Is there a billed visit” check for Special Transportation. The validation was rewritten from a simple status check to a 3-table join (Visit → AppVisitToTmhpVisitMap → TmhpVisitDataExtract) requiring SUM(PaidAmount) > 0. This is a heavier query and changes the billing semantics — now requires actual reimbursement, not just billed status.

Non-billable transportation visits now get VisitStatus.NotBillable / VisitStatusReason.NoMatchingServiceFound (previously MaximumTripsExceeded).

ProcessTmhpToAppVisitMapping.sql — Visit status updates (HM-8170)

New SQL logic updates Visit.StatusReason based on TMHP paid amounts:

• TotalPaidAmount > 0 → Reimbursed
• AdjustmentIndicator <= 1 → No Reimbursement
• Else → Fully Recouped

This runs as part of the TMHP-to-app visit mapping job and now modifies Visit.StatusReason for billed/partially-billed visits.

ProcessEligibilityResponseStudents.sql — Duplicate matching fix

• Added ROW_NUMBER() PARTITION BY StudentId to prevent one student matching multiple eligibility responses
• Changed NOT IN to NOT EXISTS with OR sm.StudentId = s.Id to prevent duplicate matching across match types

Goal lookups decoupled from ARD (EasyIepVisitImportService, PersonalCareVisitImportService)

Goal lookup changed from GetStudentGoalByNameAndStudentArdDetailId(ardDetailId, ...) to GetStudentGoalByNameAndStudentDetails(studentId, serviceCategoryId, ...). Same change in VisitSchedulingHelper.cs. Goals are now matched by student + service category instead of through ARD records.

Visit copy logic in VisitSchedulingHelper.cs removed ~45 lines of ARD-based goal validation when copying visits to a different date.

EntityImportService — Error reclassification (HM-8221)

Goal reactivation exceptions ("Reactivated the goal named") are now treated as successful imports (ImportBatchStatus.Complete) instead of errors. This changes error counting and could mask legitimate failures if the message matching is too broad.

CaseloadGroupJob SP (HM-8265)

sp_FixCaseLoadGroupForServiceCategoryMismatch now also checks “Universal Documentation” service category (ID 45) and clinician qualifications when deciding whether to remove students from caseload groups.

TMHPProcess.cs — Full refactor

The TMHP VPN/FTP process was restructured into smaller methods with better error handling. Key behavioral changes:

• Execute() returns false on failure instead of throwing (callers may not expect this)
• VPN disconnect in finally block (more reliable cleanup)
• Individual file download failures no longer abort the entire batch
• Servers are always added to the results dictionary even if not sending files (fixes missing result records)

---

Lessons Learned

1. Batch processing needs crash recovery by design. Any system that sets a “processing” flag must handle the case where the process dies before clearing it. Options: heartbeats, lease-based locks, startup recovery scans, or staleness timeouts.

2. Strict priority ordering creates fragile cascading failures. A single stuck item blocks everything behind it. Consider timeout-based skip logic or independent processing per batch type.

3. Error handlers must clean up state, not just log. A catch block that only calls Log.Error() is incomplete — the batch status is now inconsistent with reality.

4. Monitoring that can’t act is half a solution. SystemHealthNotificationJob detects the problem but can’t fix it, and its alerting may not even be working. Monitoring should at minimum provide actionable links and ideally auto-remediate simple cases.

5. Fail-fast validation prevents pipeline waste. Files with bad reference data (student IDs, campus codes, etc.) should be rejected at upload — not after they’ve entered the processing queue and blocked other imports.

---

Prevention

Two complementary approaches address this issue:

Auto-recovery (pipeline resilience)

Covered by HM-8484 and sub-tasks. Adds staleness detection, auto-reset for stuck batches, cascade fix, and error handling improvements. This prevents stuck batches from blocking the pipeline indefinitely.

Pre-validation at upload time (fail-fast)

Validate reference data (student numbers, campus codes, etc.) during StageImportFile before the batch enters the processing queue. For ARD imports, check that all StudentNumber values exist in the Student table for the target district. Reject the file immediately with a clear error listing the invalid entries.

Code location: EntityImportService.cs — StageImportFile() method and ValidateFieldsByImportType() (currently only validates EasyIep, PersonalCare, and Eval imports — ARD is not covered).