cyclone/docs/superpowers/specs/2026-06-19-cyclone-db-reconciliation-design.md

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

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

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

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

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:

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

# 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:

-- 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.

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) → <EmptyState eyebrow="Reconciliation · nothing pending" message="Every claim and remittance is paired." />.
• Loading → <Skeleton variant="card" /> × 2 columns.
• Error → <ErrorState error onRetry={refetch} /> at the top.
• Match success → both rows fade out (250ms accent-tinted), toast "Matched CLM-001 ↔ CLP-001".
• Match conflict (409) → <ErrorState> 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

// 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

<NavItem to="/reconciliation" icon={<GitMerge />}>
  Reconciliation
  {unmatchedCount > 0 ? (
    <span className="ml-auto text-[10px] font-mono tabular-nums text-warning">
      {unmatchedCount}
    </span>
  ) : null}
</NavItem>

The badge count comes from useReconciliation().unmatched.data?.claims.length + .remittances.length. Capped at "99+".

11.5 Vitest config cleanup

// 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.