cyclone/docs/superpowers/specs/2026-07-02-cyclone-rendering-npi-extraction-design.md

Status: Draft, awaiting user sign-off.

SP32 — 835/837P rendering & service-provider NPI extraction

Scope

In scope

• Extract NM1*82 (rendering provider, Loop 2420A) from 837p files into a typed field on Claim.
• Extract NM1*1P (service provider, Loop 2100) from 835 files into a typed field on ClaimPayment and an aggregated column on Remittance.
• Persist all three new fields in their typed columns AND mirror to raw_json for audit and fallback reads.
• Backfill existing rows by re-parsing on-disk files via a new CLI subcommand.
• Re-reconcile open claim/remit pairs after backfill so the NPI arm of SP31's 2-of-3 content-match rule can actually fire on historical data.
• Widen _content_keys_match in reconcile.py to read the new NPI sources (typed-column primary, raw_json fallback).

Out of scope

• NPI Luhn validation or any other validation of format/length. Plain string, like the existing Claim.provider_npi.
• UI surface — no changes to drawer/kpi/activity widgets. The new columns are exposed via the existing raw_json API paths; rendering them in the UI is a separate SP.
• Multi-payer payer_id filtering — still deferred (SP31 documented deviation stands).
• Spec-stricter lineage fields (CLP09 frequency, NM1*82 last-name vs organization-name semantics) beyond what the spec requires for matching.

Decisions

D1 — Both sides extracted (apples-to-apples)

The 837p parser today only captures the billing provider NPI (NM1*85). The 835 parser today discards NM1*1P entirely. SP32 extracts both: 837p NM1*82 → rendering NPI; 835 NM1*1P → service provider NPI. They are compared against each other in the matcher's NPI arm, not against Claim.provider_npi (billing).

D2 — Per-segment typed field on ClaimPayment

Add ClaimPayment.service_provider_npi: str | None to backend/src/cyclone/parsers/models_835.py. Length-10 string (NPI is 10 digits; no Luhn enforced). Empty/missing stored as None, not "".

D3 — Per-Claim typed column on Claim (837p)

Add Claim.rendering_provider_npi: Mapped[Optional[str]] = mapped_column(String(16), nullable=True) to backend/src/cyclone/db.py. Mirrors the existing Claim.provider_npi column. Schema migration 0016_add_rendering_and_service_provider_npis.sql adds both this and the next column.

D4 — Per-Remit column for the matcher

Add Remittance.rendering_provider_npi: Mapped[Optional[str]] = mapped_column(String(16), nullable=True). Today, each Remittance row corresponds to exactly one ClaimPayment (the write layer keys Remittance by cp.payer_claim_control_number; see store/write.py:134), so the column holds the single NPI for that segment (or None). The column is left unaggregated to keep the schema simple — if multi-CLP-per-Remit support is added in a future SP, the schema is large enough (String(16)) to upgrade to comma-joined storage without re-migration. The matcher's NPI arm reads this column directly.

D5 — raw_json mirrors

ClaimPayment is a parser wire-format model only — there is no claim_payments SQL table. The typed per-segment NPI persists through these mirrors:

• Remittance.raw_json["service_provider_npis"]: list of every ClaimPayment.service_provider_npi, written verbatim at parse time. Source of truth for audit.
• Claim.raw_json["rendering_provider_npi"]: mirror of the typed column.

These keep the existing raw_json fallback chain in _content_keys_match functional and provide a structured audit trail.

D6 — _content_keys_match NPI arm

Extend the existing matcher's NPI branch (currently a raw_json fallback chain) with a typed-column primary path:

1. Read Claim.rendering_provider_npi (typed) and Remittance.rendering_provider_npi (typed).
2. Split Remittance.rendering_provider_npi on "," and treat as a set (single-CLP produces a one-element set; multi-CLP produces N elements).
3. If the claim's rendering NPI is present in that set, count rendering_provider_npi as a matched key.
4. Fall back to the raw_json chain only when the typed columns are None or empty.

Effective algorithm unchanged: 2-of-3 {PCN, charge, NPI} still fires when any two keys agree. The NPI arm now actually contributes when the data is present.

D7 — Backfill via CLI

New subcommand: python -m cyclone.cli backfill-rendering-npi.

Why explicit file args. The Batch ORM does not retain a column for the original on-disk ingest path — the live ingest pipeline reads the upload body, parses, writes rows, and never persists the bytes. So there is no claim_batches.inbound_path / remittances.inbound_path to "look up" and re-parse from. The operator must point the CLI at the files (or the directory that holds them) that need backfill. Re-running ingest end-to-end would be the alternative, but it would re-INSERT claim rows and is a destructive operation; backfill is intentionally a read-reparse-targeted-UPDATE.

Flags:

• --file PATH (repeatable) — one or more specific X12 files to re-parse. Each path is validated to exist before parsing.
• --input-dir DIR — directory to scan one level deep for *.txt / *.edi / *.x12 files. Falls back to $CYCLONE_BACKFILL_INPUT_DIR if neither --file nor --input-dir is passed.
• --type {837p,835} (optional) — pin the parser. When omitted, each file's transaction kind is auto-sniffed (filename hint + ISA/ST prefix) so a mixed directory can be processed in one pass.

Behavior:

1. For each file resolved by --file / --input-dir, re-parse with the new parser (T4 wiring).
2. 837p: for every claim in the batch, write Claim.rendering_provider_npi = parsed_value only when the column is currently NULL (idempotent — already-populated rows are left alone).
3. 835: for every remit, write Remittance.rendering_provider_npi (typed column) only when NULL, and persist the per-segment service_provider_npi values into Remittance.raw_json["service_provider_npis"] (D5).
4. After all files are processed, run reconcile.run() once across every 835 Batch so the D6 NPI arm can fire retroactively on newly-populated pairs.
5. Log per-file counts (parsed, updated, skipped). Emit one-line summary to stdout: claims_updated=N remits_updated=M files_processed=K files_skipped=J.
6. Exit code 0 on success (zero populated rows is still exit 0), 2 on file-level failure (no file processed), 1 on unexpected exception.

Idempotent: re-running on a fully-populated DB is a no-op (typed columns not overwritten; reconcile skips already-matched pairs via Claim.matched_remittance_id IS NOT NULL).

D8 — Backfill event emission

Each new score-auto match produced during post-backfill reconcile emits an auto_matched_835 ActivityEvent with payload {new_state, strategy, keys_matched, candidate_count} — same shape as SP31 D8. The audit trail explicitly captures backfill work; nothing is silent.

D9 — Migration is forward-only, additive

Migration 0019_add_rendering_and_service_provider_npis.sql adds:

ALTER TABLE claims ADD COLUMN rendering_provider_npi TEXT;
ALTER TABLE remittances ADD COLUMN rendering_provider_npi TEXT;

ClaimPayment.service_provider_npi has no SQL column because claim_payments has no SQL table — the typed value lives in Remittance.raw_json["service_provider_npis"].

No UPDATE statements in the migration — backfill is a separate, deliberate step via the CLI. No indexes — both columns are nullable, used for set-equality at match time, not range queries. Scale (Colorado Medicaid single-payer) doesn't justify index write cost.

D10 — File paths and code shape

• backend/src/cyclone/parsers/parse_835.py — extend _consume_claim_payment to handle the NM1 child segment: parse NM1*1P, capture NM109 as service_provider_npi for the enclosing CLP. Other NM1 qualifiers (QC, IL, PR, 85, 87) within the CLP loop are kept in raw_segments (unchanged behavior).
• backend/src/cyclone/parsers/parse_837.py — add a new _consume_rendering_provider (or analogous) function for Loop 2420A; wire into the existing claim assembly path so the rendering NPI lands on Claim.
• backend/src/cyclone/parsers/models_835.py — extend ClaimPayment with service_provider_npi.
• backend/src/cyclone/db.py — Claim.rendering_provider_npi, Remittance.rendering_provider_npi. No claim_payments ORM — service_provider_npi persists only via Remittance.raw_json["service_provider_npis"] (D5).
• backend/src/cyclone/writer.py / writer_835.py — set the new typed columns and the raw_json mirrors at write time. writer_835.py writes the per-CLP service_provider_npi into Remittance.rendering_provider_npi (D4, single value) and into raw_json["service_provider_npis"] (D5).
• backend/src/cyclone/reconcile.py — extend _content_keys_match (D6).
• backend/src/cyclone/store/backfill.py — new helper module owning backfill_rendering_provider_npi(files, input_dir, transaction_type): file/CLI argument resolution, auto-sniffing (ISA/ST + filename hint), re-parse dispatch, idempotent typed-column writes, and the post-backfill reconcile.run() sweep. Returns a small summary dataclass for the CLI summary line.
• backend/src/cyclone/cli.py — new backfill-rendering-npi subcommand (Click). Wires --file / --input-dir / --type / --log-level, delegates the heavy lifting to store.backfill.backfill_rendering_provider_npi, and prints the one-line summary.
• backend/src/cyclone/migrations/0019_add_rendering_and_service_provider_npis.sql — D9.
• backend/tests/fixtures/ — new 837p fixture(s) with NM1*82 and new 835 fixture(s) with NM1*1P.
• backend/tests/test_reconcile.py — new tests for D6 (typed-column primary path, typed-column beats raw_json fallback, NPI arm fires when both sides populated).
• backend/tests/test_parse_835.py — new test for _consume_claim_payment NM1*1P extraction.
• backend/tests/test_parse_837.py — new test for rendering-provider loop extraction.

Edge cases

Case	Behavior
NM1*1P missing or 1P qualifier absent in a CLP segment	ClaimPayment.service_provider_npi = None. Matcher's NPI arm simply doesn't fire for that key.
NM1*82 missing or empty in an 837p claim	Claim.rendering_provider_npi = None. Same graceful degrade.
NM1*1P with NM109 empty	Treat as missing — store None, never empty string.
Multi-CLP per Remit in a future SP	Schema-size forward note (D4). Today, each Remit = one CLP = single NPI, no aggregation needed.
837p rendering NPI == billing NPI (solo practice)	Both columns populated independently. Matcher compares against the service-provider NPI; identity is coincidence in this case.
Backfill on a deleted-on-disk file	CLI logs a WARNING, skips the batch. Typed columns stay NULL for unmatched rows. Existing match state untouched.
Re-reconcile creates new score-auto matches	Each emits an auto_matched_835 ActivityEvent per SP31 D8 (D8). Audit trail is explicit.
Idempotent re-run	Typed columns written only when currently NULL; reconcile skips already-matched pairs. backfill-rendering-npi exit code 0.
Existing 115 SP31 tests	All pass; widened matcher signature preserves the boolean-return path used today.

Testing

Unit tests (parser-level)

• test_parse_835.py: NM1*1P extraction — claim with NM1*1P*2*...*XX*1234567890 populates ClaimPayment.service_provider_npi == "1234567890". Multi-CLP with mixed NPIs produces per-segment values correctly.
• test_parse_835.py: NM1*1P absent — service_provider_npi is None, no exception.
• test_parse_837.py: NM1*82 extraction — Loop 2420A rendering provider populates Claim.rendering_provider_npi.
• test_parse_837.py: 837p without NM1*82 — column None.

Unit tests (matcher level)

• test_reconcile.py: typed-column primary path — Remittance.rendering_provider_npi = "1234567890" and Claim.rendering_provider_npi = "1234567890" counts NPI as a matched key.
• test_reconcile.py: multi-key fallback — NM1*1P NPI absent (None) on one side, present on the other → NPI arm does not fire, PCN+charge still matches (1-of-2 effective, but per SP31 the rule is 2-of-3 of {PCN, charge, NPI}, so PCN+charge alone does NOT auto-link — must stay 2 of 3 with NPI also matching or charge differing).
• test_reconcile.py: typed-column primary path beats raw_json fallback — fixture sets both, matcher uses typed column.
• test_reconcile.py: existing 22+ SP31 tests still pass without modification.

Integration tests

• test_backfill_cli.py: synthetic batch + remit in DB, fixture files on disk, run backfill-rendering-npi — assert columns populated, exit code 0, ActivityEvents emitted for any new matches.
• test_backfill_idempotent.py: run twice, second run is no-op.
• test_backfill_missing_file.py: deleted file path → WARNING log, no crash, other batches still processed.

Regression

• Full backend pytest (target: 115 existing + ~8 new = ~123 total, all passing).
• Frontend typecheck: no new errors (no new types; matcher's typed return is internal).
• Manual smoke: ingest a real 835 via CLI, query the DB, confirm Remittance.rendering_provider_npi populated and any retroactive score-auto Match row exists.

Out of scope (deferred to follow-up SPs)

• NPI Luhn validation — separately enforced if/when claim submission rejects malformed NPIs upstream.
• UI rendering of rendering/service-provider NPI in ClaimDrawer / RemitDrawer — separate SP.
• Multi-payer payer_id filter restoration — SP31 documented deviation; needs schema migration or raw_json read.
• Per-segment NPI display in RemitDrawer's CLP rows — separate SP.