# Cyclone DB + Reconciliation — Design
**Date:** 2026-06-19
**Status:** Draft (pending user review of this doc)
**Scope:** Second sub-project of the four-part Cyclone roadmap. Replaces the in-memory `InMemoryStore` with SQLite persistence via SQLAlchemy 2.0; adds automatic 837P ↔ 835 claim reconciliation on every successful 835 parse; ships a manual Reconciliation page for unmatched claims and remittances; extends the claim lifecycle to a 7-state model with reversal handling. Out of scope: additional 837P/835 validation rules (sub-project 3), 999 ACK / 270/271 (sub-project 3), per-claim detail drawer / batch diff / live NDJSON UI / advanced filters (sub-project 4).
---
## 1. Overview
Sub-project 1 (production-readiness, merged) gave Cyclone a thread-safe in-memory store + 6 GET endpoints + react-query wiring + 4 reference notes + a root README rewrite. The store is process-local and ephemeral: a uvicorn restart wipes every parsed batch, and there is no concept of "this remittance paid that claim."
This sub-project closes two real gaps:
1. **Persistence across restarts.** The same data must survive `Ctrl-C` and reappear when the backend comes back. We use SQLite via SQLAlchemy 2.0 (sync ORM). One file at `~/.local/share/cyclone/cyclone.db`, overridable via `CYCLONE_DB_URL`. A lightweight migration runner (`cyclone.db_migrate`) keeps the schema forward-compatible; the env var is the escape hatch to Postgres if SQLite ever becomes a real constraint.
2. **837P ↔ 835 reconciliation.** When an 835 parses, we attempt to auto-match each CLP back to a Claim we already have from a prior 837P parse. Matches update the Claim's lifecycle state and create `Match` + `ActivityEvent` rows. Unmatched CLPs surface on a new `/reconciliation` page where the operator can manually pair them. Reversals (CLP02=21/22) flip prior state, preserve history on the Match row, and emit an audit-trail activity event.
After this sub-project, a user can:
1. Start the backend (`python -m cyclone serve`); the SQLite file is auto-created.
2. Upload `co_medicaid_837p.txt` → 2 Claims appear with state `submitted`.
3. Upload `co_medicaid_835.txt` → the matched Claim flips to `paid`, the reversal (if present) flips back, the orphan CLP shows up in `/reconciliation`.
4. Open `/reconciliation` → manually pair the orphan → Claim state updates, orphan disappears from the bucket.
5. Restart the backend → everything still there.
## 2. Goals
1. **SQLite-backed persistence.** Replace `InMemoryStore` internals with SQLAlchemy 2.0 ORM models in `cyclone.db`. Public store API stays byte-compatible so `api.py` and the mappers change minimally.
2. **Automatic reconciliation on every 835 parse.** Match each CLP to a Claim via `claim_id` (CLM01 ↔ CLP01) within ±7 days of `service_date`. Apply the result to the Claim row, write a `Match` row, write one `ActivityEvent` per state transition. Failures are fail-soft (logged + activity event) and never block the parse response.
3. **7-state claim lifecycle.** `submitted → received → (paid | partial | denied) → reconciled → reversed`. Reversals of `paid`/`partial` Claims set the Claim to `reversed` and preserve the pre-reversal state on the `Match` row; other reversals log a no-op activity event.
4. **Manual reconciliation UI.** New `/reconciliation` page with two columns (unmatched Claims left, unmatched Remittances right). Click-to-select, "Match" / "Unmatch" buttons. State badge updates immediately via react-query invalidation.
5. **Real adjustment amounts.** `Remittance.adjustmentAmount` (currently hardcoded to `0.0` per `store.py:376`) aggregates from CAS segments, populated by the reconciliation step.
6. **Forward-compatible schema.** A ~40-LOC migration runner (`cyclone.db_migrate`) using SQLite's `PRAGMA user_version`. Migrations are idempotent SQL files in `backend/src/cyclone/migrations/NNNN_*.sql`.
## 3. Non-goals (this sub-project)
- More 837P validation rules (REF*G1, BHT06, etc.) — sub-project 3.
- 999 ACK / 270/271 / 277CA — sub-project 3.
- Deep CAS reason-code explanations surfaced to the operator — sub-project 3.
- Real-time streaming of NDJSON GET responses into the UI — sub-project 4.
- Per-claim detail drawer, batch diff, advanced filters, keyboard nav — sub-project 4.
- Multi-user / multi-machine support — explicitly local-only.
- Alembic — too much ceremony for one operator + one machine; `user_version` runner is sufficient.
- Renaming the 835 `ClaimPayment.payer_claim_control_number` field to its semantically correct name (`patient_control_number`) — left as a follow-up tech-debt note. We match against the existing field in this sub-project.
## 4. Stack
**Backend additions:**
- `sqlalchemy>=2.0,<3` in `backend/pyproject.toml`. No Alembic. No other Python deps.
- New module `backend/src/cyclone/db.py` — engine factory, `SessionLocal`, declarative `Base`, 6 ORM model classes.
- New module `backend/src/cyclone/db_migrate.py` — `PRAGMA user_version` runner, ~40 LOC.
- New module `backend/src/cyclone/reconcile.py` — pure-function match + apply (no DB session inside; receives lists of ORM objects, returns intent objects).
- New directory `backend/src/cyclone/migrations/` — `0001_initial.sql`, future `NNNN_*.sql`.
**Backend modifications:**
- `backend/src/cyclone/store.py` — internal rewrite from `list[BatchRecord]` + `RLock` to SQLAlchemy-backed. Public method signatures preserved.
- `backend/src/cyclone/api.py` — add 3 new endpoints (`GET /api/reconciliation/unmatched`, `POST /api/reconciliation/match`, `POST /api/reconciliation/unmatch`). Existing 6 endpoints continue to work; their handlers now read from SQLAlchemy via the store facade.
- `backend/src/cyclone/__main__.py` — call `db.init()` (engine + `create_all` + `db_migrate.run`) before uvicorn starts.
- `backend/tests/` — extend with ~32 new tests across `test_reconcile.py`, `test_db_migrate.py`, `test_store_reconcile.py`, plus API tests for the 3 new endpoints.
**Frontend additions:**
- New page `src/pages/Reconciliation.tsx`.
- New hook `src/hooks/useReconciliation.ts` (queries + mutations, react-query invalidation cascade).
- New primitive `src/components/ui/claim-state-badge.tsx` (replaces 4-state logic with 7-state, same palette).
- Sidebar entry in `src/components/Sidebar.tsx` between Claims and Remittances.
- `src/types/index.ts` — add `ClaimState` enum (7 values) and `Match`/`UnmatchedClaim`/`UnmatchedRemittance` shapes.
- `src/lib/api.ts` — add 3 new methods (`listUnmatched`, `matchRemit`, `unmatchClaim`).
**Frontend modifications:**
- `src/components/StatusBadge.tsx` — delegate the 7-state subset to the new primitive; keep the 4-token palette (success / accent / warning / destructive / muted) unchanged.
- `src/pages/Claims.tsx` — render the new 7-state badge; accept `claim.state` directly from API instead of inferring from status text.
- `src/pages/Remittances.tsx` — render `adjustmentAmount` from the API (was hardcoded `0.0`).
- `vitest.config.ts` — exclude `.worktrees/` from test discovery (current worktree leftover from sub-project 1 causes duplicate test runs).
**No new npm deps.** All existing deps (`@tanstack/react-query`, `sonner`, `lucide-react`, etc.) cover the new surface.
## 5. Architecture
```
┌──────────────────────┐ POST /api/parse-{837,835} ┌─────────────────────────┐
│ Vite/React UI │ ────────────────────────────▶ │ FastAPI backend │
│ │ GET /api/{batches,claims, │ (uvicorn 127.0.0.1) │
│ ┌────────────────┐ │ remittances,providers, │ ┌────────────────────┐ │
│ │ react-query v5 │ │ activity,batches/{id}} │ │ store (facade) │ │
│ │ QueryClient │ │ GET /api/reconciliation/ │ │ add/get/iter_* │ │
│ │ useQuery / │ │ unmatched │ │ list_unmatched │ │
│ │ useMutation │ │ POST /api/reconciliation/ │ │ manual_match │ │
│ └────────────────┘ │ {match,unmatch} │ │ manual_unmatch │ │
│ ┌────────────────┐ │ ◀──────────────────────────── │ └────────┬───────────┘ │
│ │ zustand store │ │ (JSON; NDJSON on list GETs) │ │ │
│ │ (sample data) │ │ │ ┌────────▼───────────┐ │
│ └────────────────┘ │ │ │ cyclone.db │ │
│ │ │ │ SQLAlchemy 2.0 ORM │ │
│ │ │ │ 6 model classes │ │
│ │ │ │ SessionLocal() │ │
│ │ │ └────────┬───────────┘ │
│ │ │ │ │
│ │ │ ┌────────▼───────────┐ │
│ │ │ │ cyclone.reconcile │ │
│ │ │ │ pure functions: │ │
│ │ │ │ match() │ │
│ │ │ │ apply_payment() │ │
│ │ │ │ apply_reversal() │ │
│ │ │ │ split_unmatched() │ │
│ │ │ └────────────────────┘ │
│ │ │ ┌────────────────────┐ │
│ │ │ │ cyclone.db_migrate │ │
│ │ │ │ PRAGMA user_version │ │
│ │ │ │ migrations/*.sql │ │
│ │ │ └────────────────────┘ │
└──────────────────────┘ └─────────────────────────┘
▲
│ CORS allow
│ http://localhost:5173
│ only
```
**Boundary contracts:**
| Module | Public API | Pure? |
|---|---|---|
| `cyclone.db` | `init_db()`, `SessionLocal()`, model classes | No (engine + schema) |
| `cyclone.db_migrate` | `run(engine)` | Yes (modulo engine) |
| `cyclone.reconcile` | `match()`, `apply_payment()`, `apply_reversal()`, `split_unmatched()` | **Yes** (no DB session inside) |
| `cyclone.store` | `add()`, `get_batch()`, `iter_*()`, `list_unmatched()`, `manual_match()`, `manual_unmatch()` | No (owns sessions, calls reconcile) |
| `cyclone.api` | HTTP routes | No |
| React page/hook | HTTP shapes | No |
`reconcile.*` functions receive lists of ORM objects (or fabricated equivalents in tests) and return **intent objects** describing what should change. The caller (`store`) is responsible for persisting the intent inside a session. This keeps reconciliation unit-testable without a database.
## 6. Data model
`cyclone.db` declares 6 ORM model classes. SQLAlchemy 2.0 `DeclarativeBase` + `Mapped[...]` + `mapped_column(...)` typed syntax (no legacy `Column()`).
### 6.1 `Batch`
| Column | Type | Notes |
|---|---|---|
| `id` | `String(32)` PK | uuid4 hex (preserved from sub-project 1) |
| `kind` | `String(8)` | `"837p"` or `"835"` |
| `input_filename` | `String(512)` | original upload name |
| `parsed_at` | `DateTime(timezone=True)` | tz-aware UTC |
| `totals_json` | `JSON` | `{"claims": N, "paid": M, "denied": K, ...}` from the parser summary |
| `validation_json` | `JSON` | `{"passed": bool, "warnings": [...], "errors": [...]}` |
| `raw_result_json` | `JSON` | the full `ParseResult` / `ParseResult835` for `GET /api/batches/{id}` round-trip |
Indexed on `parsed_at DESC`.
### 6.2 `Claim`
| Column | Type | Notes |
|---|---|---|
| `id` | `String(64)` PK | 837P `claim_id` (CLM01); unique per (batch, claim_id) |
| `batch_id` | `String(32)` FK→`Batch.id` | ON DELETE CASCADE |
| `patient_control_number` | `String(64)` | CLM01; the match key |
| `service_date_from` | `Date` | CLM05 date range start |
| `service_date_to` | `Date` | CLM05 date range end |
| `charge_amount` | `Numeric(12,2)` | CLM02 |
| `provider_npi` | `String(16)` | 2010AA NM109 |
| `payer_id` | `String(32)` | NM1*PR N104 |
| `state` | `Enum(ClaimState)` | 7 values (see §6.7) |
| `state_before_reversal` | `Enum(ClaimState)?` | nullable; populated only when a reversal flips state back |
| `matched_remittance_id` | `String(64)?` FK→`Remittance.id` | nullable; current match (replaced on unmatch / re-match) |
| `raw_json` | `JSON` | full `ClaimOutput` for drill-down |
Unique constraint on `batch_id, patient_control_number`. Indexed on `state`, `patient_control_number`, `service_date_from`.
### 6.3 `Remittance`
| Column | Type | Notes |
|---|---|---|
| `id` | `String(64)` PK | 835 `payer_claim_control_number` (CLP01) + ERA batch suffix to disambiguate within a batch; unique per (batch, pcn, suffix) |
| `batch_id` | `String(32)` FK→`Batch.id` | ON DELETE CASCADE |
| `payer_claim_control_number` | `String(64)` | CLP01 — the match key against `Claim.patient_control_number` |
| `claim_id` | `String(64)?` FK→`Claim.id` | nullable until matched; nullable if no match exists |
| `status_code` | `String(4)` | CLP02 |
| `status_label` | `String(32)?` | decoded by `claim_status_label` |
| `total_charge` | `Numeric(12,2)` | CLP03 |
| `total_paid` | `Numeric(12,2)` | CLP04 |
| `patient_responsibility` | `Numeric(12,2)?` | CLP05 |
| `adjustment_amount` | `Numeric(12,2)` | **NEW** — sum of `CasAdjustment.amount` for this CLP; populated by reconciliation |
| `received_at` | `DateTime(timezone=True)` | parsed_at of the parent batch |
| `service_date` | `Date?` | first SVC's `service_date` from the 835 (used by match for ±7d window) |
| `is_reversal` | `Boolean` | True if CLP02 ∈ {21, 22} |
| `raw_json` | `JSON` | full `ClaimPayment` for drill-down |
Indexed on `claim_id`, `payer_claim_control_number`, `status_code`.
### 6.4 `CasAdjustment`
| Column | Type | Notes |
|---|---|---|
| `id` | `Integer` PK autoincrement | |
| `remittance_id` | `String(64)` FK→`Remittance.id` | ON DELETE CASCADE |
| `group_code` | `String(4)` | CAS01 (CO, PR, OA, PI, CR) |
| `reason_code` | `String(8)` | CAS02 (e.g., "97", "16") |
| `amount` | `Numeric(12,2)` | CAS03 |
| `quantity` | `Numeric(10,2)?` | CAS04 (for unit-based adjustments) |
Indexed on `remittance_id`.
### 6.5 `Match`
| Column | Type | Notes |
|---|---|---|
| `id` | `Integer` PK autoincrement | |
| `claim_id` | `String(64)` FK→`Claim.id` | UNIQUE — one current match per Claim |
| `remittance_id` | `String(64)` FK→`Remittance.id` | ON DELETE CASCADE |
| `strategy` | `String(16)` | `"auto"` or `"manual"` |
| `matched_at` | `DateTime(timezone=True)` | |
| `prior_claim_state` | `Enum(ClaimState)?` | nullable; populated only when a reversal is applied, so we can recover the pre-reversal state |
| `is_reversal` | `Boolean` | denormalized for cheap filtering |
Indexed on `remittance_id`, `matched_at`.
### 6.6 `ActivityEvent`
| Column | Type | Notes |
|---|---|---|
| `id` | `Integer` PK autoincrement | |
| `ts` | `DateTime(timezone=True)` | default `utcnow` |
| `kind` | `String(32)` | `"parse_837"`, `"parse_835"`, `"reconcile"`, `"reconcile_failed"`, `"reversal"`, `"reversal_skipped"`, `"orphan_reversal"`, `"manual_match"`, `"manual_unmatch"`, `"match_conflict"`, `"invalid_state"` |
| `batch_id` | `String(32)?` | nullable; populated when the event ties to a batch |
| `claim_id` | `String(64)?` | nullable; populated when the event ties to a claim |
| `remittance_id` | `String(64)?` | nullable; populated when the event ties to a remittance |
| `payload_json` | `JSON` | event-specific (e.g., `{"count": 3, "matched": 2, "unmatched": 1}` for `reconcile`) |
Indexed on `ts DESC`, `kind`.
### 6.7 `ClaimState` enum
```python
import enum
class ClaimState(str, enum.Enum):
SUBMITTED = "submitted" # 837P parsed, no remittance yet
RECEIVED = "received" # 835 CLP matched; awaiting payment application
PAID = "paid" # matched + CLP04 >= CLM02 (full payment)
PARTIAL = "partial" # matched + 0 < CLP04 < CLM02
DENIED = "denied" # matched + CLP02=4
RECONCILED = "reconciled" # matched + applied; final state on a non-reversal payment
REVERSED = "reversed" # matched + later CLP02=21/22 hit; prior state recoverable via Match.prior_claim_state
```
**Transition table:**
| From | Trigger | To | Side effects |
|---|---|---|---|
| (new) | `store.add(837)` | `submitted` | `ActivityEvent(kind="parse_837", claim_id=...)` |
| `submitted` | `reconcile.apply_payment` (CLP02 ∈ {1,2,3,19,20}, CLP04 ≥ CLM02) | `paid` → `reconciled` | `Match(strategy="auto")`, `ActivityEvent(kind="reconcile")` |
| `submitted` | `reconcile.apply_payment` (CLP04 < CLM02) | `partial` → `reconciled` | same |
| `submitted` | `reconcile.apply_payment` (CLP02 = 4) | `denied` → `reconciled` | same |
| `submitted` | `reconcile.apply_payment` (CLP02 ∉ {1,2,3,4,19,20}) | `received` → `reconciled` | same |
| `paid` or `partial` | `reconcile.apply_reversal` (CLP02 = 21 or 22) | `reversed` | `Match(strategy="auto", is_reversal=True, prior_claim_state=…)`, `ActivityEvent(kind="reversal")`. The original `paid`/`partial` Match row is preserved unchanged. |
| `reversed` | `reconcile.apply_reversal` again | no-op | `ActivityEvent(kind="reversal_skipped")` |
| any | manual `store.manual_match` | same as auto | `Match(strategy="manual")`, `ActivityEvent(kind="manual_match")` |
| any | manual `store.manual_unmatch` | `submitted` (if Claim is otherwise orphaned) | Match row deleted, `ActivityEvent(kind="manual_unmatch")` |
Transitions not in this table are **no-ops with a logged activity event** (e.g., `apply_payment` on a `denied` Claim is a no-op + `ActivityEvent(kind="invalid_state", payload={"current_state": "denied"})`).
## 7. Reconciliation algorithm
### 7.1 Match strategy — "strict auto-match"
```
reconcile.match(unmatched_claims, new_remittances, *, window_days=7):
matches = []
used_remits = set()
# Group claims by patient_control_number for O(1) lookup
by_pcn: dict[str, list[Claim]] = {}
for c in unmatched_claims:
if c.patient_control_number and c.state in (SUBMITTED, RECEIVED, REVERSED):
by_pcn.setdefault(c.patient_control_number, []).append(c)
# Iterate remittances in batch order (preserves CLP ordering)
for r in new_remittances:
candidates = by_pcn.get(r.payer_claim_control_number, [])
if not candidates:
continue # → unmatched bucket
# Pick the claim whose service_date_from is closest to r.service_date
# and within ±window_days. If r.service_date is None (rare), fall
# back to "first claim in pcn bucket".
chosen = None
if r.service_date is not None:
best_delta = None
for c in candidates:
if c.service_date_from is None:
continue
delta = abs((c.service_date_from - r.service_date).days)
if delta <= window_days and (best_delta is None or delta < best_delta):
chosen = c
best_delta = delta
if chosen is None and candidates:
chosen = candidates[0] # no date info → first claim for that PCN
if chosen is not None:
matches.append(Match(
claim=chosen,
remittance=r,
strategy="auto",
is_reversal=r.is_reversal,
))
used_remits.add(r.id)
return matches
```
**Properties:**
- Deterministic: same inputs → same matches.
- O(C + R) where C = unmatched claims, R = new remittances; O(C) memory for the by_pcn index.
- Multiple Claims for the same PCN (e.g., resubmissions with frequency_code=7) → picks the closest by date; falls back to first if no dates.
- Multiple Remittances for the same PCN (e.g., split payments) → each Remittance gets its own Claim in the order they appear.
- Reversal CLPs are matched the same way; the `is_reversal` flag drives the apply step.
### 7.2 Apply — `apply_payment(claim, remit) → intent`
```python
def apply_payment(claim: Claim, remit: Remittance) -> ApplyIntent:
if claim.state in (PAID, PARTIAL, DENIED, RECONCILED):
# Already settled — refuse double-payment
return ApplyIntent.noop(invalid_state=claim.state)
if remit.status_code == "4":
new_state = ClaimState.DENIED
elif remit.total_paid is None or remit.total_paid <= 0:
new_state = ClaimState.RECEIVED
elif remit.total_paid >= claim.charge_amount:
new_state = ClaimState.PAID
else:
new_state = ClaimState.PARTIAL
return ApplyIntent(
new_state=new_state,
match_strategy="auto",
activity_kind="reconcile",
)
```
Caller (in `store`) persists the new state, creates the Match row, writes the ActivityEvent, and commits — all inside one session.
### 7.3 Apply — `apply_reversal(claim, remit) → intent`
```python
def apply_reversal(claim: Claim, remit: Remittance) -> ApplyIntent:
if claim.state in (PAID, PARTIAL):
return ApplyIntent(
new_state=claim.state, # no actual state change on the claim
match_strategy="auto",
is_reversal=True,
prior_claim_state=claim.state, # recorded on the Match row
activity_kind="reversal",
)
# Already reversed, denied, or never paid — log and skip
return ApplyIntent.noop(
skipped=True,
reason=f"reversal on state {claim.state.value}",
activity_kind="reversal_skipped",
)
```
The Claim's own `state` **changes to `REVERSED`** on a reversal of `paid`/`partial` (the operator-facing view is "this payment was reversed"). The pre-reversal state lives on the new `Match` row as `prior_claim_state`, so the audit trail is preserved. A reversed Claim's `matched_remittance_id` points at the reversal remittance, and a separate `Match` row from the original payment also still exists — the audit history is queryable via `Claim.id` JOIN `Match.claim_id`.
### 7.4 Split unmatched
```python
def split_unmatched(claims, remits, matches) -> tuple[list[Claim], list[Remittance]]:
matched_claim_ids = {m.claim_id for m in matches}
matched_remit_ids = {m.remittance_id for m in matches}
return (
[c for c in claims if c.id not in matched_claim_ids],
[r for r in remits if r.id not in matched_remit_ids],
)
```
A Claim that has at least one match is **not** in the unmatched bucket, even if it's still in `submitted` (e.g., the matched Remit was a denial and the operator wants to see other matching Remits). Manual unmatch moves it back to the unmatched bucket.
### 7.5 Reconciliation trigger — `reconcile.run(session, batch_id)`
Called from `store.add()` after the 835's Batch + Remittances + CasAdjustments are persisted:
```python
def reconcile.run(session: Session, batch_id: str) -> ReconcileResult:
try:
new_remits = session.execute(
select(Remittance).where(Remittance.batch_id == batch_id)
).scalars().all()
unmatched_claims = session.execute(
select(Claim).where(Claim.matched_remittance_id.is_(None))
).scalars().all()
matches = reconcile.match(unmatched_claims, new_remits)
intents = []
for m in matches:
if m.is_reversal:
intent = reconcile.apply_reversal(m.claim, m.remittance)
else:
intent = reconcile.apply_payment(m.claim, m.remittance)
intents.append((m, intent))
for m, intent in intents:
if intent.skipped:
session.add(ActivityEvent(
kind=intent.activity_kind,
batch_id=batch_id,
claim_id=m.claim.id,
payload={"reason": intent.reason, "current_state": m.claim.state.value},
))
continue
# Persist the Match + state transition + activity event atomically
session.add(Match(
claim_id=m.claim.id,
remittance_id=m.remittance.id,
strategy=m.strategy,
is_reversal=m.is_reversal,
prior_claim_state=intent.prior_claim_state,
matched_at=utcnow(),
))
if not m.is_reversal:
m.claim.state = intent.new_state
m.claim.matched_remittance_id = m.remittance.id
else:
# Reversal of a paid/partial Claim: set state to REVERSED on
# the Claim so the operator sees it; the original state is
# recoverable via Match.prior_claim_state.
m.claim.state = ClaimState.REVERSED
m.claim.matched_remittance_id = m.remittance.id
session.add(ActivityEvent(
kind=intent.activity_kind,
batch_id=batch_id,
claim_id=m.claim.id,
payload={"new_state": intent.new_state.value if intent.new_state else None},
))
# Aggregate CAS adjustments into each Remittance's adjustment_amount
for r in new_remits:
total = session.execute(
select(func.sum(CasAdjustment.amount)).where(CasAdjustment.remittance_id == r.id)
).scalar_one() or Decimal("0.00")
r.adjustment_amount = total
session.commit()
unmatched_claims_after, unmatched_remits_after = reconcile.split_unmatched(
unmatched_claims, new_remits, [m for m, _ in intents if not _.skipped]
)
return ReconcileResult(
matched=len([i for i in intents if not i[1].skipped]),
unmatched_claims=len(unmatched_claims_after),
unmatched_remittances=len(unmatched_remits_after),
skipped=len([i for i in intents if i[1].skipped]),
)
except Exception as e:
session.rollback()
log.exception("reconciliation failed for batch %s", batch_id)
# In a fresh session (the original is rolled back), record the failure
with SessionLocal() as s2:
s2.add(ActivityEvent(
kind="reconcile_failed",
batch_id=batch_id,
payload={"error": str(e)},
))
s2.commit()
return ReconcileResult(failed=True, error=str(e))
```
**Critical invariant:** the Batch + Remittances + CasAdjustments are persisted **before** `reconcile.run` is called. A reconciliation crash never rolls back the parse.
## 8. Migration runner — `cyclone.db_migrate`
```python
# cyclonedb_migrate.py (~40 LOC)
from pathlib import Path
import re
import sqlalchemy as sa
MIGRATIONS_DIR = Path(__file__).parent / "migrations"
VERSION_RE = re.compile(r"^--\s*version:\s*(\d+)", re.MULTILINE)
def run(engine: sa.Engine) -> None:
with engine.begin() as conn:
current = conn.exec_driver_sql("PRAGMA user_version").scalar() or 0
# Ensure user_version PRAGMA exists (no-op on SQLite 3.x)
migrations = sorted(MIGRATIONS_DIR.glob("*.sql"))
for path in migrations:
sql = path.read_text()
m = VERSION_RE.search(sql)
if not m:
raise RuntimeError(f"{path.name}: missing '-- version: N' header")
version = int(m.group(1))
if version <= current:
continue
# Each migration is its own transaction so a failure doesn't
# leave the DB half-upgraded.
with engine.begin() as conn2:
# Strip the -- comment lines before executing
statements = [s.strip() for s in sql.split(";") if s.strip() and not s.strip().startswith("--")]
for stmt in statements:
conn2.exec_driver_sql(stmt)
conn2.exec_driver_sql(f"PRAGMA user_version = {version}")
current = version
```
Initial migration `backend/src/cyclone/migrations/0001_initial.sql`:
```sql
-- version: 1
CREATE TABLE batches (...);
CREATE TABLE claims (...);
-- ... all 6 tables
```
The runner is idempotent (re-running on an up-to-date DB is a no-op) and atomic per migration (each file in its own transaction).
## 9. Store facade
`cyclone.store` keeps its current public surface, but the internals are SQLAlchemy-backed. Public methods accept and return the same UI-shaped dicts as today; the SQLAlchemy ORM objects stay internal.
```python
class CycloneStore: # renamed from InMemoryStore; old name kept as a compat alias
def add(self, record: BatchRecord) -> str:
"""Persist the BatchRecord; for 835 batches, run reconciliation."""
...
def get_batch(self, batch_id: str) -> dict | None: ...
def iter_claims(self, *, batch_id=None, status=None, payer=None,
date_from=None, date_to=None, sort="service_date_from",
order="desc", limit=100, offset=0) -> list[dict]: ...
def iter_remittances(self, *, batch_id=None, status=None, payer=None,
claim_id=None, date_from=None, date_to=None,
sort="received_at", order="desc", limit=100,
offset=0) -> list[dict]: ...
def distinct_providers(self) -> list[dict]: ...
def recent_activity(self, *, limit=200) -> list[dict]: ...
# NEW in sub-project 2
def list_unmatched(self, *, kind: Literal["claim", "remit", "both"] = "both") -> dict:
"""Returns {claims: [...], remittances: [...]}; only unmatched rows."""
def manual_match(self, claim_id: str, remittance_id: str) -> Match:
"""Pair a Claim with a Remittance. Raises MatchConflict if either is already matched."""
def manual_unmatch(self, claim_id: str) -> None:
"""Remove the Claim's current Match and reset to submitted state."""
store = CycloneStore() # module-level singleton; first .add() lazy-inits the engine
```
**Session handling:** each public method opens its own `with SessionLocal() as s:` and commits/rolls back before returning. No long-lived sessions, no cross-request transaction leakage. The exception is `add()` for 835 batches, which uses one session for parse persistence and a second (or the same) session for `reconcile.run`.
## 10. API additions
| Method | Path | Body | Response | Notes |
|---|---|---|---|---|
| GET | `/api/reconciliation/unmatched` | — | `{claims: [...], remittances: [...]}` | Each Claim/Remittance carries the same UI shape as `/api/claims` / `/api/remittances` plus `id` (the FK). Sorted oldest-first so the operator works the backlog top-down. |
| POST | `/api/reconciliation/match` | `{claim_id, remit_id}` | `{claim: Claim, match: Match}` | 200 on success. 409 on conflict (already_matched, invalid_state). |
| POST | `/api/reconciliation/unmatch` | `{claim_id}` | `{claim: Claim}` | 200 on success. 404 if no current match. |
Existing endpoints continue to work with the same shapes. Internal mapping changes:
| Endpoint | Change |
|---|---|
| `GET /api/claims` | `status` filter accepts the 7-state enum; response `Claim` now includes `state` directly (no longer inferred from a separate status field). The `status` field on the response is kept for backward-compat but equals `state`. |
| `GET /api/remittances` | `adjustmentAmount` now reflects the CAS-aggregated value (was hardcoded `0.0`). |
| `POST /api/parse-835` | Response includes `reconciliation: {matched, unmatched_claims, unmatched_remittances, skipped}` so the UI can show a one-line summary toast. |
## 11. Frontend additions
### 11.1 `/reconciliation` page
Two-column layout, hairline chrome matching the rest of the UI. Both columns are independently scrollable. Each row is click-to-select; the "Match" button is disabled until exactly one row from each column is selected.
```
┌────────────────────────────────────────────────────────────────────────────┐
│ RECONCILIATION │
│ │
│ Unmatched claims (3) Unmatched remits (1) │
│ ───────────────────── ─────────────────────│
│ ▸ CLM-001 · 2026-06-01 · $124.00 ▸ CLP-001 · $62.00 │
│ Billed: $124.00 · NPI 1234567890 Status 22 (reversal)│
│ Service 2026-06-02 │
│ ▸ CLM-007 · 2026-05-29 · $88.50 │
│ Billed: $88.50 · NPI 1234567890 │
│ │
│ ▸ CLM-014 · 2026-05-15 · $240.00 │
│ Billed: $240.00 · NPI 1234567890 │
│ │
│ [ Match selected ] [ Clear selection ] │
└────────────────────────────────────────────────────────────────────────────┘
```
States:
- Empty (both columns empty) → ``.
- Loading → `` × 2 columns.
- Error → `` at the top.
- Match success → both rows fade out (250ms accent-tinted), toast "Matched CLM-001 ↔ CLP-001".
- Match conflict (409) → `` with `{error: "already_matched", existing_match_id}` and a "View existing match" affordance that focuses the matched row in the Claims page (route push to `/claims?focus={id}`).
### 11.2 `useReconciliation` hook
```ts
// src/hooks/useReconciliation.ts
export function useReconciliation() {
const queryClient = useQueryClient();
const unmatched = useQuery({
queryKey: ['reconciliation', 'unmatched'],
queryFn: () => api.listUnmatched(),
refetchInterval: 30_000,
});
const match = useMutation({
mutationFn: (args: { claimId: string; remitId: string }) =>
api.matchRemit(args.claimId, args.remitId),
onSuccess: () => {
queryClient.invalidateQueries({ queryKey: ['reconciliation'] });
queryClient.invalidateQueries({ queryKey: ['claims'] });
queryClient.invalidateQueries({ queryKey: ['remittances'] });
queryClient.invalidateQueries({ queryKey: ['activity'] });
},
});
const unmatch = useMutation({
mutationFn: (args: { claimId: string }) => api.unmatchClaim(args.claimId),
onSuccess: () => {
queryClient.invalidateQueries({ queryKey: ['reconciliation'] });
queryClient.invalidateQueries({ queryKey: ['claims'] });
queryClient.invalidateQueries({ queryKey: ['remittances'] });
queryClient.invalidateQueries({ queryKey: ['activity'] });
},
});
return { unmatched, match, unmatch };
}
```
### 11.3 `claim-state-badge` primitive
Replaces the 4-state logic in `StatusBadge.tsx` with the 7-state enum. Same color palette (success / accent / warning / destructive / muted) — new states reuse existing tokens:
| State | Token |
|---|---|
| `submitted` | accent (blue) |
| `received` | accent (blue, slightly muted) |
| `paid` | success (green) |
| `partial` | warning (amber) |
| `denied` | destructive (red) |
| `reconciled` | success (green, with checkmark icon) |
| `reversed` | warning (amber, with rotate-ccw icon) |
| `draft` (legacy) | muted |
### 11.4 Sidebar entry
```tsx
}>
Reconciliation
{unmatchedCount > 0 ? (
{unmatchedCount}
) : null}
```
The badge count comes from `useReconciliation().unmatched.data?.claims.length + .remittances.length`. Capped at "99+".
### 11.5 Vitest config cleanup
```ts
// vitest.config.ts (excerpt)
export default defineConfig({
test: {
exclude: [...defaultExclude, '.worktrees/**'],
},
});
```
This stops vitest from picking up `.worktrees/prod-readiness/` (leftover from sub-project 1; gitignored but on disk).
## 12. Data flow
**Worked example — parse-835 with 3 CLPs: one paid, one reversal, one orphan.**
```
POST /api/parse-835 (multipart .txt)
↓
parse_835.orchestrate() → ParseResult835(claims=[R_A, R_B, R_C])
R_A: CLP02=1, total_paid=124.00, total_charge=124.00
R_B: CLP02=22 (reversal), total_paid=-88.50
R_C: CLP02=1, total_paid=62.00 (no matching Claim in DB)
↓
api.parse_835_route() builds BatchRecord835 + 3 ClaimPayment shapes
↓
store.add(batch) inside `with SessionLocal() as s:`
s.add(Batch row, id=batch_id)
s.add(Remittance rows: R_A, R_B, R_C; CasAdjustments for each)
s.commit() ← ERA persisted, no matter what
↓
reconcile.run(s, batch_id):
new_remits = [R_A, R_B, R_C]
unmatched_claims = [Claim_A (state=submitted, PCN=CLM-001),
Claim_B (state=paid, PCN=CLM-007)]
matches = match(...)
→ R_A matches Claim_A (PCN=CLM-001, dates within window) → Match(A, R_A, auto)
→ R_B matches Claim_B (PCN=CLM-007) → Match(B, R_B, auto, is_reversal=True)
→ R_C: no Claim with PCN=CLM-099 → unmatched_remits += 1
intents:
Match(A, R_A) → apply_payment → new_state=paid (124 ≥ 124)
Match(B, R_B) → apply_reversal → no state change; prior_claim_state=paid
s.add(Match rows)
Claim_A.state = paid
Claim_A.matched_remittance_id = R_A.id
Claim_B.state = REVERSED (was paid; original paid-Match row preserved)
Claim_B.matched_remittance_id = R_B.id
s.add(ActivityEvents: "reconcile" for A, "reversal" for B)
for each Remit: s.add CAS-aggregated adjustment_amount
s.commit()
on exception:
log.exception(); s.rollback() (original session)
s2 = new session; s2.add(ActivityEvent(kind="reconcile_failed")); s2.commit()
↓
returns 200 with {parsed_at, totals, reconciliation: {matched: 2, unmatched_claims: 0, unmatched_remittances: 1, skipped: 0}}
↓
Frontend useParse('835').onSuccess invalidates
['batches', 'claims', 'remittances', 'activity', 'reconciliation/unmatched', 'providers']
↓
react-query refetches 6 queries in parallel
↓
Claims table: Claim_A row flashes green (state paid), Claim_B row gets a "reversed" sub-badge
Remittances table: R_A, R_B, R_C all show real adjustmentAmount
Reconciliation page: R_C appears in the right column with a badge count of 1
Sidebar: "Reconciliation" nav item shows "1"
```
**Invariants:**
- ERA is persisted before reconciliation runs. A reconciliation crash never loses the 835.
- An unmatched CLP creates a `Remittance` with `claim_id=NULL` and `matched_remittance_id` unset on no Claim. No orphan `Claim` row is fabricated.
- A reversal of a `paid`/`partial` Claim sets `Claim.state = REVERSED`; the pre-reversal state is preserved on the new `Match.prior_claim_state`. The original `paid`/`partial` Match row is preserved unchanged. UI shows the Claim state as "reversed" with the prior state recoverable from the Match row.
- 837P parse never triggers reconciliation — Claims stay `submitted` until an 835 arrives.
- `manual_unmatch` deletes the Match row, clears `Claim.matched_remittance_id`, resets `Claim.state` to `submitted`, and writes an `ActivityEvent(kind="manual_unmatch")`.
## 13. Error handling
| Failure | Behavior |
|---|---|
| Reconciliation crashes mid-run | Logged at `ERROR` with batch_id + traceback. `ActivityEvent(kind="reconcile_failed")` written in a fresh session. Batch + Remittances + CasAdjustments remain persisted. CLPs from this batch that weren't matched yet sit in the unmatched bucket awaiting manual fix or a future ERA. Operator sees the failure in the Activity feed. |
| DB unreachable / corrupt file on startup | `db.init()` raises. `python -m cyclone serve` exits non-zero. Operator sees the traceback in the terminal. No half-started server. |
| Migration fails (bad SQL, disk full) | Transaction rolled back, `user_version` unchanged, exit non-zero. Next startup retries the same migration. No partial schema. |
| Manual match conflict (claim already matched, or remit already matched) | API returns `409 Conflict` with body `{error: "already_matched", existing_match_id}`. Frontend `ErrorState` with a "View existing match" affordance. |
| State transition invalid (e.g. `apply_payment` on a `denied` claim) | `apply_payment` no-ops and writes `ActivityEvent(kind="invalid_state", payload={current_state})`. Claim row unchanged. Operator must unmatch first. |
| Orphan reversal (CLP02=21/22 with no prior Claim) | Remittance stored with `claim_id=NULL`, `is_reversal=True`. Surfaces in the unmatched-remits bucket with a red badge. `ActivityEvent(kind="orphan_reversal")` written. No state mutation. |
| Reversal of a Claim whose state isn't `paid`/`partial` | `apply_reversal` no-ops. `ActivityEvent(kind="reversal_skipped", payload={claim_id, current_state})` written. |
| Concurrent parse-837 + parse-835 | Single SQLite writer + `busy_timeout=5000ms` queues the second write. FastAPI handlers are short so contention is theoretical. If timeout exceeded, returns `503` with body `{error: "db_busy"}`. |
| Bad CAS segment | Parser's existing validation surfaces as a warning on the parse response; `CasAdjustment` only created for well-formed rows. If an insert fails (e.g., NULL amount), reconcile crashes → fail-soft path triggers. |
| `manual_unmatch` on an already-unmatched Claim | 404 from the API. Frontend button disabled when no match exists. |
**No silent failures.** Every anomaly produces an `ActivityEvent` so the operator sees it in the feed.
## 14. Testing
### 14.1 Backend (`backend/tests/`)
**`test_reconcile.py` (~12 tests, pure-function):**
- Match: same PCN, dates within window → matched.
- Match: same PCN, dates outside ±7d → unmatched.
- Match: different NPI but same PCN → still matches (PCN is the key; NPI is metadata).
- Match: multiple Claims for same PCN (resubmissions) → picks closest by date.
- Match: multiple Remittances for same PCN (split payments) → each gets its own Match.
- Match: Remittance with no PCN match → unmatched.
- `apply_payment`: CLP04 ≥ CLM02 → `paid`.
- `apply_payment`: 0 < CLP04 < CLM02 → `partial`.
- `apply_payment`: CLP02=4 → `denied`.
- `apply_payment`: CLP02=other valid code → `received`.
- `apply_reversal`: Claim state=`paid` → no state change, `prior_claim_state=paid` recorded.
- `apply_reversal`: Claim state=`denied` → no-op + reason logged.
- `split_unmatched`: correctly partitions claims and remits by match set.
**`test_db_migrate.py` (~3 tests):**
- Fresh DB: applies all migrations in order, bumps `user_version` to latest.
- Upgrading an old DB (user_version=0): applies 0001 only.
- Re-running on up-to-date DB: no-op (idempotent).
- Migration missing `-- version:` header: raises loudly.
**`test_store_reconcile.py` (~10 tests, integration, in-memory SQLite via `StaticPool`):**
- add(837) → Claims persisted with state=submitted.
- add(835) with auto-match → Claim state updated, Match row created, ActivityEvent written.
- add(835) with reversal → Claim state preserved, Match.prior_claim_state set, ActivityEvent(kind="reversal").
- add(835) with orphan CLP → Remittance stored with claim_id=NULL, list_unmatched("remit") returns it.
- `list_unmatched("both")` shape correctness.
- `manual_match` happy path.
- `manual_match` on already-matched Claim → raises MatchConflict.
- `manual_unmatch` happy path.
- Persistence across `SessionLocal()` instances (simulates restart — data still present).
- Adjustment amounts aggregated from CasAdjustment rows.
**API tests (extend existing `test_api_gets.py` and `test_api_parse_persists.py`, ~7 new):**
- `GET /api/reconciliation/unmatched` shape.
- `POST /api/reconciliation/match` happy path (200 + Claim/Match in response).
- `POST /api/reconciliation/match` conflict (409 with `already_matched`).
- `POST /api/reconciliation/unmatch` happy path (200).
- `POST /api/reconciliation/unmatch` on unmatched Claim (404).
- parse-835 fixture → reconciliation runs → `/api/claims` reflects new state.
- Failed reconcile still persists the batch (fault injection via monkeypatched reconcile).
**Total new backend tests: ~32.** Target: 178 + 32 = **210 passing**.
### 14.2 Frontend (`src/`)
**`src/hooks/useReconciliation.test.ts` (~2 tests):**
- Returns `{unmatched, match, unmatch}` with the right query keys.
- `match.onSuccess` invalidates `['reconciliation', 'claims', 'remittances', 'activity']`.
**`src/pages/Reconciliation.test.tsx` (~2 tests):**
- Renders both columns with unmatched rows; Match button disabled until both selections made.
- Clicking Match calls `api.matchRemit(claimId, remitId)` and renders `ErrorState` on 409.
**Total new frontend tests: ~4.** Target: 3 + 4 = **7 passing**.
### 14.3 End-to-end smoke (manual, documented in the plan)
1. Delete `~/.local/share/cyclone/cyclone.db` if it exists.
2. Start backend on clean DB → file auto-created at `~/.local/share/cyclone/cyclone.db`.
3. `sqlite3 ~/.local/share/cyclone/cyclone.db ".tables"` → 6 tables present.
4. Upload `co_medicaid_837p.txt` → 2 Claims appear with state `submitted` on `/claims`.
5. Upload `co_medicaid_835.txt` → matched Claims flip to `paid`, reversal (if present) flips one back, orphan CLP sits in `/reconciliation`.
6. Open `/reconciliation` → match the orphan → row disappears from the bucket, Claim state updates.
7. `Ctrl-C` the backend; restart → all data still present.
8. `pytest` still passes (210 tests).
9. `npm run build` clean.
10. `vitest` runs 7 tests once (no `.worktrees/` duplicates).
## 15. Migration / rollout
- **No production data to migrate.** Sub-project 1 used an in-memory store; restarting wipes everything by design.
- The default `~/.local/share/cyclone/cyclone.db` is the home directory of the current user. Override with `CYCLONE_DB_URL=postgresql://...` to swap engines without code changes.
- `python -m cyclone serve` calls `db.init()` before uvicorn starts. If `init()` raises, the process exits non-zero. The previous in-memory store is gone; this is a one-way upgrade.
- Backups are a manual `sqlite3 ~/.local/share/cyclone/cyclone.db ".backup /path/to/backup.db"` — documented in the README.
## 16. Out of scope (explicitly)
- Alembic migrations (overkill for one operator + one machine).
- Real-time streaming of NDJSON GET responses into the UI.
- Per-claim detail drawer, batch diff view.
- Multi-status filters, date-range filters, saved filter sets.
- Keyboard-driven navigation.
- 999 ACK, 270/271, 277CA transaction types.
- More 837P validation rules (REF*G1 enforcement, BHT06).
- 835 CAS deep-parsing with reason-code explanations surfaced to the operator.
- Renaming the 835 `ClaimPayment.payer_claim_control_number` field to its semantically correct name (`patient_control_number`).
- Multi-user / multi-machine / auth (any kind).
- Rate limiting, request size limits beyond FastAPI defaults.
- Structured logging, log levels via env, JSON logs.
- Health-check enhancements.
- Docker / docker-compose / any deploy artifact.
- Pre-commit hooks, Makefile, .editorconfig, CONTRIBUTING.md, lint config.
- E2E browser tests (Playwright/Cypress).
## 17. Acceptance checklist
### 17.1 Functional
- [ ] `pytest` passes; total ≥ 178 + 32 = **210**.
- [ ] `npm run build` clean; `npm run typecheck` clean.
- [ ] `npm test` runs 7 tests once (no `.worktrees/` duplicates).
- [ ] Backend startup creates `~/.local/share/cyclone/cyclone.db` with 6 tables.
- [ ] Parse-837 → Claims appear with state `submitted` on `/claims`.
- [ ] Parse-835 → matched Claims transition through the state machine; orphan CLPs land in `/reconciliation`.
- [ ] Reversal of a `paid` Claim sets `Claim.state = REVERSED`, sets `Match.prior_claim_state=paid`, writes `reversal` activity event. The original `paid` Match row is preserved.
- [ ] Manual match from `/reconciliation` removes both rows from the bucket and updates the Claim state.
- [ ] Manual unmatch from `/claims` (or via API) reverses the pairing.
- [ ] Backend restart preserves all data.
- [ ] `CYCLONE_DB_URL=postgresql://...` switches the engine (smoke-tested against a throwaway SQLite file at a custom path).
- [ ] Reconciliation crash on a malformed 835 fixture still persists the ERA and writes a `reconcile_failed` activity event.
### 17.2 Visual / aesthetic
- [ ] `/reconciliation` uses the existing surface / hairline chrome; no new colors, no new typeface, no new layout primitive.
- [ ] Empty state on `/reconciliation` uses the `EmptyState` primitive with an instrument-label eyebrow ("Reconciliation · nothing pending").
- [ ] Match button echoes the sidebar `nav-active` 1px accent line treatment.
- [ ] Matched rows fade out (250ms accent-tinted, not destructive — they succeeded, not errored).
- [ ] Conflict (409) renders `ErrorState` with a "View existing match" affordance, not a destructive takeover.
- [ ] Sidebar nav badge for unmatched count uses the existing `text-warning` + monospace tabular-nums treatment; capped at "99+".
- [ ] `claim-state-badge` reuses the existing 4-token palette — no new color tokens introduced.
- [ ] `adjustmentAmount` shown in the Remittances table is the CAS-aggregated value (real dollar figure from the 835), not `0.0`.
### 17.3 Operational
- [ ] Migrations live in `backend/src/cyclone/migrations/0001_initial.sql` with a `-- version: 1` header.
- [ ] `cyclone.db_migrate.run(engine)` is idempotent and atomic per migration.
- [ ] Failed migration rolls back `user_version` to its prior value.
- [ ] README documents `CYCLONE_DB_URL` and the default DB file path; documents the `sqlite3 .backup` recipe.
- [ ] Vitest config excludes `.worktrees/`.
### 17.4 Audit
- [ ] Every reconciliation anomaly (crash, skip, conflict, orphan reversal) writes an `ActivityEvent` so the operator sees it in the feed.
- [ ] The 1 outstanding TODO marker in `backend/src/cyclone/store.py:376` (`adjustmentAmount: 0.0`) is removed.
- [ ] The 107 unchecked checkboxes in `docs/superpowers/plans/2026-06-19-cyclone-production-readiness.md` are ticked off in a single housekeeping commit.