**The bug.** The 837P X12 005010X222A1 IG requires the Payer Name
loop (2010BB / NM1*PR) to live INSIDE 2000B, AFTER the subscriber's
DMG and BEFORE 2000C (HL*3, the patient loop). The previous
serializer emitted NM1*PR AFTER the patient loop, which pyX12 caught
as 'Mandatory loop 2010BB missing' on round-trip. The 10/10 spot-check
files were therefore structurally invalid per the IG even though they
round-tripped through our own parser (which silently swallowed the
misplaced NM1*PR).
**The fix.**
1. Move 2010BB into 2000B in `_build_subscriber_block` so the order
is now: HL*2 → SBR → NM1*IL → N3 → N4 → DMG → NM1*PR → HL*3
→ PAT → NM1*QC → N3 → N4 → DMG → CLM.
2. Add `_consume_patient_loop` to `parse_837.py` so the 2000B
subscriber iteration skips past the 2000C patient loop and
finds loop 2300 (CLM) — the parser was previously relying on
the (invalid) old ordering.
3. Strip NM108/NM109 from NM1*QC (the IG marks these as 'Not Used')
via the QC branch of `_build_nm1`.
4. Add PAT*01 segment (PAT is required whenever 2000C is emitted).
5. Add `include_patient_loop` flag so callers can opt out (e.g.
Patient==Subscriber cases that don't need a 2000C loop at all).
6. Bump HL*2 child count to 1 when 2000C is emitted (was 0 or 1
inconsistently).
**Visits table (gap #3).** Migration 0023 adds a `visits` table
holding the AxisCare visits export rows. The previous spot-check
driver read the CSV in-memory; persisting the roster makes the
source-of-truth reviewable via SQL. `cyclone.rebill.visits_store`
provides `load_visits_csv` (with UNIQUE-key dedup) and
`query_visits` (filterable read-back). 8 new unit tests pin the
contract.
**Misc.**
- guard `s.svc_date is not None` in `reconcile.run` against 835
rows that lack DTP*472.
- `.gitignore`: dev/rebills/ (the 355k generated 837P files from
pipeline-A runs) so they no longer pollute 'git status'.
- Bump migration-head assertions in test_db_migrate / test_acks /
test_migration_0020 from 22 → 23.
**Verification (post-fix).**
- 1568/1568 unit tests pass (10s)
- 20/20 spot-check files pass pyX12 v4.0.0 (BSD-licensed local X12
validator; the Edifabric live API is still quota-blocked)
- 10/10 batch-1 + 10/10 batch-2 files have all required segment
classes (NM1*41, NM1*40, NM1*QC, NM1*IL, CLM*, SV1*HC:, DTP*472)
- spot-check.json + spot-check-summary.txt refreshed from
pyX12 (was 10/10 stale 403 errors; now 20/20 PASS)
Refs: SP41 inwindow-rebill-pipeline, plan step 3 (10 well-formed 837P
files with NM1*QC patient/subscriber loop) + plan step 4 (Edifabric /
v2/x12/validate).
Migration 0021_resubmissions.sql (SP39 Task 2) advances the head from
20 to 21. Both test_migration_0020.migrated_engine and
test_acks.test_migration_latest_idempotent_on_fresh_db pinned the old
head; update both. No production behavior change.
Followup #4 from the SP37 final-state tracker. The previous
BACKFILL_SQL constant in test_migration_0020.py was a hand-copied
duplicate of the migration's UPDATE statement. A future contributor
could edit one without the other and the test would silently
replay a different SQL than production — defeating the regression.
Fix: tests now load the migration file at test time and extract
its UPDATE via the same splitter db_migrate.run() uses (strip
'--' comments, split on ';'). The test can never disagree with
what production runs.
Changes:
* test_migration_0020.py:
- Remove the hand-copied BACKFILL_SQL constant
- Add _migration_0020_path(), _extract_update_statements(),
and _load_migration_0020_backfill_sql() helpers
- Replace 3 BACKFILL_SQL references with helper calls
* test_migration_0020_no_drift.py (new, 3 tests):
- test_migration_0020_backfill_sql_uses_migration_file
(asserts the extracted SQL targets the right column + path)
- test_migration_0020_backfill_sql_is_non_empty_single_statement
- test_migration_0020_has_exactly_one_update (guardrail against
future contributors adding a second UPDATE — the extraction
fails loudly so the test author can decide which is the
backfill)
Tests: 43/43 pass in 1.46s (full SP37 followup chain).
Imports across test files match the existing pattern (test_store.py
imports from test_store_reconcile.py).
Migration 0020 adds the column additively (nullable, no default) and
backfills from raw_result_json.envelope.transaction_set_control_number
for any existing batch rows that already carry it. Required for the
SP37 join-key update so 999 acks can resolve by ST02 (the source 837's
transaction set control number) instead of just ISA13.
Nora
sp37 bot