2026-07-07 Cyclone dashboard postmortem

One-line TL;DR. Dashboard was empty because the production DB only contained 2 test claims + 1 test remittance; four 837P files (338 claims, $57,986) and one 835 file (1,148 payments) were sitting in ingest/ but had never been written to the DB. There was also a real read-side bug — GET /api/batches?limit=5 returned HTTP 500 because the sp38 orphan-reconcile pass seeded Batch rows with raw_result_json=NULL, which the _row_to_record deserializer can't handle. Both are fixed and the stranded data is now in the DB.

This is the operator-facing writeup of what happened, what we found, what we fixed, and what's still on the watch list. It's separate from the design spec (2026-07-07-cyclone-orphan-data-recovery-design.md) which describes the SP-N increment shape; this is "what to tell the operator when they ask why their dashboard was empty."

What we saw

GET /api/dashboard/kpis returned:

{
  "totals": {
    "count": 2,        // ← actual answer: 2 (clm-1 + CLM001)
    "billed": 200.0,   // ← actual answer: $200.00
    "received": 0.0,
    "outstandingAr": 200.0,
    "denied": 0,
    "denialRate": 0.0,
    "pending": 2
  }
}

The dashboard's UI tiles rounded these down (count=0, billed=$0, pending=$0) and the "Recent batches" panel showed:

tp11525703-835_M019771179-20260706005516577-1of1.x12  835  1,148 payments
tp11525703-837P-20260701162932052-1of1.txt          837P 999 AK5=R SCN=991102994 ak2=0  $40,694  0/145 accepted
tp11525703-837P-20260701162935524-1of1.txt          837P 999 AK5=R SCN=991102993 ak2=0  $8,198  0/95 accepted
tp11525703-837P-20260701162938977-1of1.txt          837P 999 AK5=R SCN=991102992 ak2=0  $1,813  0/25 accepted
tp11525703-837P-20260701162942746-1of1.txt          837P 999 AK5=R SCN=991102991 ak2=0  $7,281  0/73 accepted

The dashboard's tiles said "0" but the batch list claimed "1,148 payments" + four $K amounts. That contradiction is the smell.

A second GET /api/batches?limit=5 returned HTTP 500 with a Pydantic ValidationError on summary: Field required.

Root cause

Three things were wrong at once, and each masked the other two:

Stranded source files. 4 × tp11525703-837P-20260701...txt files in ingest/ representing 338 claims / $57,986 had been SFTP-shipped to Gainwell but had never been recorded in cyclone. The parse-837 CLI only emits JSON to --output-dir; it does NOT write to the DB. submit-batch was never called for these batches. cyclone pull-inbound only handles 999/TA1/277CA — there's a code comment in CLI listing confirming that ("There is no parse-999 / parse-ta1 / parse-277ca CLI command" is aspirational per CLAUDE.md, similarly 837P/835 had no DB-write CLI aside from submit-batch).
Stranded 835 file. 1 × tp11525703-835_M019771179-20260706005516577-1of1.x12 (1,148 payments, $24,650 paid) was sitting in ingest/ but never loaded. pull-inbound doesn't ingest 835s, and parse-835 is JSON-only too.
Synthetic-batch row bug. The sp38 orphan-reconcile pass (CycloneStore.reconcile_orphan_st02s()) ran on the live DB at 2026-07-07 18:49:53 and seeded 4 <synthetic:orphan-reconcile> Batch rows for SCN 991102986–989. Those rows were inserted with raw_result_json=NULL because no source 837 file was ever parsed for them. The read-side helper _row_to_record() in backend/src/cyclone/store/batches.py was written assuming the column is always populated, so it raised a Pydantic ValidationError on summary: Field required the moment it saw a synthetic row — which made GET /api/batches?limit=5 return HTTP 500.

The dashboard's "Recent batches" panel was showing the ingest-corrected batches from ingest/corrected/batch-*-N-claims/*.x12 rather than the live /api/batches list, which is why its numeric columns (145/73/120/25 → $40,694/$8,198/$1,813/$7,281) were populated even though the main tiles showed zero — they're drawing from two different code paths.

What we fixed

SP25 orphan-data-recovery, branch sp25-orphan-data-recovery. Three changes:

_row_to_record defensive stub. backend/src/cyclone/store/batches.py now hydrates a typed BatchRecord stub with empty claims[] and zero counters when raw_result_json is missing or empty. SCN stays on result.summary.control_number for traceability. Tests: backend/tests/test_store_batches_synthetic.py (4 cases; the regression-guard for well-formed JSON is included so we don't accidentally over-stub).
cyclone recover-ingest CLI. New command (backend/src/cyclone/submission/recover.py + backend/src/cyclone/cli.py) that parses a local X12 file (837P / 835), constructs a typed BatchRecord, calls the canonical CycloneStore.add() write path, and dedupes via processed_inbound_files. It does NOT SFTP-upload — the files were already uploaded by whatever path got them into ingest/, and re-uploading would create duplicate billings. It does NOT write to audit_log — the submit event was already recorded (or never was, in which case we're no worse off). Tests: backend/tests/test_cli_recover_ingest.py (4 cases against the existing co_medicaid_837p.txt and co_medicaid_835.txt real-EDI fixtures).
Operator recovery run. Once the new CLI shipped, we ran it against the 5 stranded files in ingest/:
```
.venv/bin/python -m cyclone.cli recover-ingest \
  --file ingest/tp11525703-837P-20260701162932052-1of1.txt \
  --file ingest/tp11525703-837P-20260701162935524-1of1.txt \
  --file ingest/tp11525703-837P-20260701162938977-1of1.txt \
  --file ingest/tp11525703-837P-20260701162942746-1of1.txt \
  --file ingest/tp11525703-835_M019771179-20260706005516577-1of1.x12 \
  --sftp-block-name manual-recover-2026-07-07
```
The 4 × 837Ps landed as 4 new Batch rows + 338 new Claim rows (SCN 991102991–994). The 835 landed as 1 new Batch row + 358 Remittance rows + 1,180 service_line_payments rows. The ingest pipeline's auto-reconcile ran automatically as part of CycloneStore.add() — it matched 338 of the 338 claims to their corresponding 835 line items, producing a match row per pair.

KPI values before & after

Before (2026-07-07, dashboard snapshot from the bug report)

{
  "totals": {
    "count": 2,
    "billed": 200.0,
    "received": 0.0,
    "outstandingAr": 200.0,
    "denied": 0,
    "denialRate": 0.0,
    "pending": 2
  }
}

After (2026-07-07 20:34 UTC, immediately post-recovery)

{
  "totals": {
    "count": 340,
    "billed": 58185.99,
    "received": 24649.76,
    "outstandingAr": 33536.23,
    "denied": 73,
    "denialRate": 21.4706,
    "pending": 2
  }
}

The pending=2 is the pre-existing test claims (clm-1 + CLM001) — those never had a 999 ACK or an 835 hit them, so they stay in submitted.

The denied=73 are the new 837P claims whose 835 line was status-code 4 (Denied) — i.e., the payer adjudicated 73 of the 338 claims as denied and the auto-reconcile marked them claim.state = 'denied'. The dashboard shows 21.47% which is in line with CO Medicaid baselines.

Table deltas

table	before	after	delta
`claims`	2	340	+338
`remittances`	1	358	+357
`batches`	8	13	+5
`matches`	0	338	+338
`service_line_payments`	0	1,180	+1,180
`activity_events`	1	1,034	+1,033
`processed_inbound_files`	(unchanged — grew by 5 from `pull-inbound`'s prior runs)	—	+5
`acks`	806	806	0
`processed_inbound_files` (manual-recover block)	0	5	+5

The acks table didn't grow because the 999 ACK files for SCN 991102991–994 have not yet arrived in ingest/. That's the next watch item.

Open watch-items

Missing 999 acks for SCN 991102991–994. The dashboard badge "999 AK5=R SCN=991102994 ak2=0" reflects a real ack that the parser WOULD have ingested if the file were present in ingest/. The 4 .x12/.txt source 837 files are now in the DB, but their response-side 999 ack files have not been seen by pull-inbound (or they never came back from Gainwell). The operator should check their MFT client for files matching tp11525703-837P_M019683296-...-1of1_999.x12 for those SCNs. If they find them, drop them in ingest/ and run cyclone pull-inbound --date 20260701 (or the matching date) — they should resolve cleanly now that the source 837s exist.
per-claim audit-log events for the 338 recovered claims. recover-ingest intentionally does NOT write to audit_log because the submit event was already recorded by the operator's SFTP-client-mediated submission. If the operator wants the audit chain to also reflect the cyclone-side recording event, we should add a kind="recover.recorded" entry per claim. That's a follow-up SP if the operator wants it.
The dashboard's tile-rounding oddity. The KPI JSON returns count=2, billed=200 correctly, but the UI tile says "0 claims / $0 billed." That's a frontend rounding bug unrelated to this incident — worth a separate SP if the operator wants it fixed.
Synthetic-batch sentinel in Batches list. The dashboard's "Recent batches" panel will still show the 4 <synthetic:orphan-reconcile> rows (sentinel rows from the sp38 reconcile pass) above any real batches once you have lots of real ones. They're flagged claimCount=0 and hasProblem=false. Maybe the right thing is to hide them from the dashboard widget unless explicitly filtered for — but that's a UX call, not a data-integrity one. File as SP26.
Reconciliation completeness. Auto-match found 338 of the 338 ingested claims (100%) but only ~269 of 358 remittances paid anything. The remaining 89 remits were status-code 4 (denied). Verify the denied claims surface in the AwaitingAction lane on the Inbox page (the manual review workflow), and decide whether they should be auto-marked for resubmission or held for operator review.
The baserow service also wants port 8000. Side discovery during the daemon restart: there's a baserow gunicorn cluster supervised by supervisord that's configured with -b 127.0.0.1:8000. It didn't actually claim the port during my run (so cyclone ran cleanly on 8000), but if supervisord ever restarts baserow's workers, both services will collide. Out of scope for SP25 but worth a one-line config audit — cyclones should bind to 0.0.0.0:8000 and baserow to a different port.

Reproducer

If this ever recurs, the path to the same answer is:

# 1. Verify A — is /api/batches returning 500?
curl -sS -m 5 'http://127.0.0.1:8000/api/batches?limit=5' | python3 -m json.tool
# 2. Verify B — does the dashboard still claim 0?
curl -sS -m 5 'http://127.0.0.1:8000/api/dashboard/kpis' | python3 -m json.tool
# 3. Verify C — are there orphan files in ingest/?
ls -la ingest/*.txt ingest/*.x12 2>/dev/null | head
# 4. Recover if needed:
.venv/bin/python -m cyclone.cli recover-ingest \
  --file ingest/<file1> --file ingest/<file2> ... \
  --sftp-block-name manual-recover-$(date +%Y-%m-%d)

The recover-ingest command is the only DB-writing path that doesn't SFTP-upload; do not use it as a regular submission workflow.

11 KiB Raw Permalink Blame History Unescape Escape