cyclone/docs/superpowers/plans/2026-07-07-cyclone-orphan-data-recovery.md

# Orphan Data Recovery Implementation Plan

> **For agentic workers:** REQUIRED SUB-SKILL: Use
> superpowers:subagent-driven-development (recommended) or
> superpowers:executing-plans to implement this plan task-by-task. Steps
> use checkbox (`- [ ]`) syntax for tracking.

 **Goal:** Recover stranded billing data from `ingest/`, fix the synthetic-batch read-side bomb, and verify the dashboard KPI tiles come back to life after the recovery run.

 **Architecture:** Three surgical changes — one defensive read-side fallback in `store/batches.py`, one new offline CLI `cyclone recover-ingest`, one verification script. No new HTTP endpoints, no schema changes, no SFTP interaction. Uses `CycloneStore.add()` as the canonical write path so live-tail pages light up immediately.

 **Tech Stack:** Python 3.11+, FastAPI/uvicorn (for FastAPI bootstrap helper to share `EventBus`), SQLAlchemy (existing models), Click (existing CLI), Pydantic v2 (existing models).

 **Spec:** [`docs/superpowers/specs/2026-07-07-cyclone-orphan-data-recovery-design.md`](../specs/2026-07-07-cyclone-orphan-data-recovery-design.md)

---

## File structure

```
backend/src/cyclone/
  store/batches.py            # A: defensive stub in _row_to_record
  cli.py                      # B: add `cyclone recover-ingest`
  submission/
    recover.py                # B: shared parse-then-store logic (new, small)
backend/tests/
  test_store_batches_synthetic.py        # A: unit (the defensive stub needs synthetic rows with NULL raw_result_json — these are ORM-level test fixtures for the read path, not EDI samples)
  test_api_batches_synthetic.py          # A: integration
  test_cli_recover_ingest.py             # B: cli + integration (uses existing co_medicaid_* fixtures; no new minimal_* EDI mirrors)
backend/scripts/
  verify_dashboard_recovery.py           # post-recovery sanity check (operator-only)
docs/superpowers/specs/
  2026-07-07-cyclone-dashboard-mess-postmortem.md  # deep-dive writeup
```

## Task 0: Setup — branch + verify

- [ ] Confirm `main` is the working branch and is clean: `git status -sb`.
- [ ] Create the branch: `git checkout -b sp25-orphan-data-recovery`.
- [ ] Confirm DB file location: `sqlite3 ~/.local/share/cyclone/cyclone.db ".tables"` returns the 22 tables.

## Task 1: A — failing test for `_row_to_record` defensive stub

- [ ] Add `backend/tests/test_store_batches_synthetic.py` with two cases:
  1. `test_row_to_record_handles_null_raw_json` — insert a Batch with `raw_result_json=None, kind='837p', input_filename='<synthetic:orphan-reconcile>'`, call `store.list_batches()`, assert the stub returns a `BatchRecord` with `result.summary.total_claims==0` and empty `result.claims`.
  2. `test_row_to_record_handles_null_raw_json_835` — same but `kind='835'`, plus assert `result.claims==[]`.
- [ ] Run `cd backend && .venv/bin/pytest tests/test_store_batches_synthetic.py -v` — both tests must fail (`ValidationError` on `summary`).

## Task 2: A — implement stub in `_row_to_record`

- [ ] In `backend/src/cyclone/store/batches.py`, replace the body of `_row_to_record` (lines ~47-79) with a defensive version:
  - If `row.raw_result_json` is None or `{}`, build a stub `ParseResult` (837p) with `envelope=None, claims=[], summary=BatchSummary(input_file=row.input_filename, control_number=row.transaction_set_control_number, total_claims=0, passed=0, failed=0)` and a `Date.today()`-based `transaction_date`, **or** a stub `ParseResult835` (835) with `envelope=Envelope(sender_id="",receiver_id="",control_number=row.transaction_set_control_number,transaction_date=date.today()), financial_info=FinancialInfo(... zeros ...), trace=ReassociationTrace(...), payer=Payer835(...), payee=Payee835(...), claims=[], summary=<minimal>`.
  - Otherwise behave as today.
- [ ] Re-run `tests/test_store_batches_synthetic.py` — both tests now pass.
- [ ] Run the full backend suite: `cd backend && .venv/bin/pytest -q`. Must stay green.

## Task 3: A — verify against the live DB

- [ ] Restart-free smoke: `curl -sS 'http://127.0.0.1:8000/api/batches?limit=5' | python -m json.tool` — expect 200, `items[].claimCount==0` for the 4 synthetic rows, `inputFilename=="<synthetic:orphan-reconcile>"`.

## Task 4: B — failing test for `recover-ingest` against real co_medicaid fixtures

- [ ] Add `backend/tests/test_cli_recover_ingest.py` with three cases. Test inputs come from existing `backend/tests/fixtures/co_medicaid_837p.txt` + `co_medicaid_835.txt` — real production EDI already in the repo. **No synthetic EDI fixtures are introduced.** The ingest/ files are reserved for the operator-only recovery run (Task 6), not unit-tested.
  1. `test_ingest_835_persists_remittance` — invoke `recover-ingest` via Click's `CliRunner` with `co_medicaid_835.txt`; assert a Remittance row was created with `total_paid > 0`, plus a `processed_inbound_files` row with `sftp_block_name='manual-recover'`.
  2. `test_ingest_837p_persists_claims` — invoke with `co_medicaid_837p.txt`; assert ≥1 Claim row + a Batch row that references it.
  3. `test_ingest_idempotent` — invoke twice with the same file; second run is a no-op (skip messages, no extra rows).
- [ ] Run the test file: must fail (no `recover-ingest` command yet).

## Task 5: B — implement `cyclone recover-ingest`

- [ ] Add `backend/src/cyclone/submission/recover.py` with one function:
  - `def recover_file(path: Path, *, sftp_block_name: str = "manual-recover") -> dict` — detects kind from `ST01*` (837 → '837p'; 835 → '835'), calls the matching parser, constructs a `BatchRecord`, calls `CycloneStore.add(record, event_bus=local_bus)`, returns `{"file": str(path), "status": "ok"|"duplicate"|"failed", "batch_id": str|None, "error": str|None}`.
  - Wraps the dedup check around `processed_inbound_files` `(sftp_block_name, path.name)`.
- [ ] In `backend/src/cyclone/cli.py`, add the Click command `recover-ingest`:
  - Options: `--file PATH` (repeatable), `--sftp-block-name` (default `manual-recover`).
  - Validates that each `--file` exists.
  - Calls `recover_file` per file; exits 0 if at least one ingested OK, 2 if a file parse failed, 1 otherwise.
- [ ] Re-run `tests/test_cli_recover_ingest.py` — all three pass.
- [ ] Run the full backend suite: `cd backend && .venv/bin/pytest -q`. Stays green.

## Task 6: C — line-reconciliation is automatic; verify with the live DB

- [ ] Capture the pre-recovery `/api/dashboard/kpis` snapshot into a file:
  - `curl -sS 'http://127.0.0.1:8000/api/dashboard/kpis' > /tmp/recovery_pre.json`.
- [ ] Run the recovery against the 5 real files:
  - `.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`.
  - Expect: 4× 837P → 4 new `Batch` rows + 338 `Claim` rows; 1× 835 → 1 new `Batch` + 1 `Remittance` + ~1,148 `service_line_payments` rows + N `Match` rows (auto-reconciled).
- [ ] Capture the post-recovery `/api/dashboard/kpis` snapshot: `curl ... > /tmp/recovery_post.json`.
- [ ] Open `backend/scripts/verify_dashboard_recovery.py` (write the script during this step):
  - Parses both JSON files; asserts `count_post >= count_pre + 338`, `billed_post >= $57,986`, `pending_post >= 339`.
  - Optionally prints the top-provisioners delta so the operator eyeballs the result.

## Task 7: write the postmortem doc

- [ ] Create `docs/superpowers/specs/2026-07-07-cyclone-dashboard-mess-postmortem.md`:
  - Header: `# 2026-07-07 Cyclone Dashboard postmortem`.
  - Sections: `## What we saw / ## Root cause / ## What we fixed / ## KPI values before & after / ## Open watch-items`.
  - Embed the before/after KPI JSON snapshots as fenced code blocks under `## KPI values before & after`.

## Task 8: final verification + merge

- [ ] `cd backend && .venv/bin/pytest -q` — green.
- [ ] `npm test` in repo root — green.
- [ ] `git status -sb` — clean working tree.
- [ ] `git log --oneline -10` — at minimum: docs(spec), docs(plan), `feat(sp25):`, postmortem commit.
- [ ] Commit the chain (the merge commit is the last one):
  - `git commit -m 'docs(spec): design for SP25 orphan data recovery (Step 1)'`
  - `git commit -m 'docs(plan): implementation plan for SP25 orphan data recovery (Step 1)'`
  - `git commit -am 'feat(sp25): defensive read-side stub for synthetic-batch rows + recover-ingest CLI (Steps 2-5)'`
  - `git commit -m 'docs: dashboard mess postmortem (Step 7)'`
- [ ] Merge into `main` with `--no-ff`:
  - `git checkout main && git merge --no-ff sp25-orphan-data-recovery -m 'merge: SP25 orphan data recovery into main'`
  - Confirm a single atomic merge commit with all the above in its `git log`.