cyclone/docs/superpowers/specs/2026-06-20-cyclone-multi-payer-npi-sftp-design.md

# Sub-project 9 — Multi-Payer, Multi-NPI, SFTP Stub: Design Spec

**Date:** 2026-06-20
**Status:** Draft, awaiting user sign-off
**Branch:** `sp9-multi-payer-npi`
**Aesthetic direction:** No new UI. Backend + `payers.yaml` + `providers` table + `clearhouse` config + SFTP stub. Existing Inbox/drawer surfaces show the new data with zero frontend redesign.

## 1. Scope

Replace the single hard-coded `PayerConfig` factory dict (currently in `api.py:97`) with:

1. **`providers` table** — the 3 Touch of Care billing-provider NPIs (Montrose 1881068062, Delta 1851446637, Salida 1467507269), all at the same Montrose corporate office, all `TOC, Inc.`, tax ID 721587149, taxonomy 251E00000X.
2. **`payers` table + `payer_configs` join table** — one row per (payer_id, transaction_type) so 837P and 835 can carry different config (e.g., different `BHT06`, different SBR rules, different naming).
3. **`clearhouse` single-row config** — dzinesco's identity: TPID 11525703, submitter, receiver, file-naming block, MT timezone.
4. **`payers.yaml`** — replaces the in-code `PAYER_FACTORIES` dict. Loaded once at boot; reloadable via `POST /api/admin/reload-config`.
5. **File-naming helpers** — `build_outbound_filename` and `parse_inbound_filename` per the HCPF X12 File Naming Standards Quick Guide. Mountain Time timestamps, 17-digit ms precision, "1of1" only.
6. **SFTP submit stub** — `POST /api/clearhouse/submit` accepts a batch of generated 837 files and copies them to a local `staging_dir` instead of opening an SFTP connection. The structure of the stub matches the future real call so swapping in `paramiko` in SP13 is one-file.
7. **macOS Keychain for secrets** — SFTP password/SSH key path is fetched from the Keychain by name; never written to disk or YAML. (Stubs return a fake secret until the real Keychain entry is created.)

**Out of scope** (deferred to other SPs):
- **SP10** — 277CA parser + "Payer-Rejected" lane in the Inbox.
- **SP11** — Tamper-evident hash-chained `audit_log` table.
- **SP12** — SQLCipher encryption at rest + Keychain-stored DB key.
- **SP13** — Replace the SFTP stub with real `paramiko` connection to `mft.gainwelltechnologies.com` and actually push to `/CO XIX/PROD/coxix_prod_11525703/ToHPE`.
- **Real SFTP credentials in Keychain** — schema and call sites are in place; the actual secret is created manually by the operator.

## 2. Goals

1. **One source of truth for payer logic.** Every payer-specific value (claim-frequency code, SBR09 default, allowed BHT06, file-name format) lives in `payers.yaml`, not in `api.py`.
2. **One provider per NPI.** 837 files pick the right Billing Provider by NPI; the existing `prv_billing_npi` field on `ClaimOutput` becomes a foreign key into `providers`.
3. **MT-clock filenames.** All outbound filenames use Mountain Time, 17-digit `yyyymmddhhmmssSSS` precision, and the literal "1of1".
4. **Inbound filenames parsed and routable.** 999 / TA1 / 271 / 277 / 277CA / 835 files are routed to the right handler based on the `<FileType>` tag and the `<OrigTx>` token.
5. **SFTP stub is structurally identical to the real call.** SP13 swaps `SftpClient.write()`'s implementation, nothing else.

## 3. Locked decisions

### 3.1 Approach — YAML-driven config, not code-driven

Today, `api.py` builds `PayerConfig` for CO Medicaid from a 60-line dict literal. That dict mixes:
- Static receiver identity (ISA08, GS03, NM1*40)
- BHT06 selection (CH vs RP)
- SBR09 default
- Submitter contact (PER segment)
- Payer-identifier (NM1*PR NM109)
- Filename rules

All of it lives in code. SP9 moves it to `payers.yaml` keyed by `payer_id` (e.g. `CO_TXIX`) and the `transaction_type` (`837P` or `835`). Code becomes a thin loader + validator.

The original dict stays as the **fallback default** if a payer is missing from `payers.yaml` (allows ad-hoc testing). On boot, the loader:
1. Reads `config/payers.yaml` from the repo root
2. Validates each `(payer_id, transaction_type)` block against a Pydantic schema
3. If validation fails, **fails the boot** with a precise error
4. If a block is missing for a payer that the DB has claims for, the existing dict is used for that payer (warn)

### 3.2 Schema — 3 new tables, 1 new config

```sql
-- Migration 0007
CREATE TABLE providers (
  npi              TEXT PRIMARY KEY,           -- 10 digits
  label            TEXT NOT NULL,              -- 'Montrose' | 'Delta' | 'Salida'
  legal_name       TEXT NOT NULL,              -- 'TOC, Inc.'
  tax_id           TEXT NOT NULL,              -- '721587149'
  taxonomy_code    TEXT NOT NULL,              -- '251E00000X'
  address_line1    TEXT NOT NULL,
  address_line2    TEXT,                       -- nullable (e.g. 'Suite A')
  city             TEXT NOT NULL,
  state            TEXT NOT NULL,              -- 'CO'
  zip              TEXT NOT NULL,              -- '814014063'
  is_active        INTEGER NOT NULL DEFAULT 1,
  created_at       TEXT NOT NULL,
  updated_at       TEXT NOT NULL
);

CREATE TABLE payers (
  payer_id         TEXT PRIMARY KEY,           -- 'CO_TXIX'
  name             TEXT NOT NULL,              -- 'Colorado Medicaid'
  receiver_name    TEXT NOT NULL,              -- 'COLORADO MEDICAL ASSISTANCE PROGRAM' (NM1*40)
  receiver_id      TEXT NOT NULL,              -- 'COMEDASSISTPROG' (ISA08/GS03)
  is_active        INTEGER NOT NULL DEFAULT 1,
  created_at       TEXT NOT NULL,
  updated_at       TEXT NOT NULL
);

CREATE TABLE payer_configs (
  payer_id         TEXT NOT NULL,
  transaction_type TEXT NOT NULL,              -- '837P' | '835' | '277CA' | '999' | 'TA1'
  config_json      TEXT NOT NULL,              -- see Section 3.3
  PRIMARY KEY (payer_id, transaction_type),
  FOREIGN KEY (payer_id) REFERENCES payers(payer_id)
);

CREATE TABLE clearhouse (
  id                 INTEGER PRIMARY KEY CHECK (id = 1),  -- singleton
  name               TEXT NOT NULL,            -- 'dzinesco'
  tpid               TEXT NOT NULL,            -- '11525703'
  submitter_name     TEXT NOT NULL,            -- 'Dzinesco'
  submitter_id_qual  TEXT NOT NULL DEFAULT '46',-- '46' for TPID
  submitter_contact_name TEXT NOT NULL,        -- 'Tyler Martinez'
  submitter_contact_email TEXT NOT NULL,       -- 'tyler@dzinesco.com'
  filename_block_json TEXT NOT NULL,           -- see Section 3.5
  sftp_block_json   TEXT NOT NULL,             -- see Section 3.6
  updated_at         TEXT NOT NULL
);
INSERT INTO clearhouse (id, name, tpid, submitter_name, submitter_contact_name,
  submitter_contact_email, filename_block_json, sftp_block_json, updated_at)
VALUES (1, 'dzinesco', '11525703', 'Dzinesco', 'Tyler Martinez', 'tyler@dzinesco.com',
  '{"tz":"America/Denver","outbound_template":"{tpid}-{tx}-{ts_mt}-1of1.{ext}",'
  '"inbound_template":"TP{tpid}-{orig_tx}_M{tracking}-{ts}-1of1_{file_type}.x12"}',
  '{"host":"mft.gainwelltechnologies.com","port":22,'
  '"username":"colorado-fts\\coxix_prod_11525703",'
  '"paths":{"outbound":"/CO XIX/PROD/coxix_prod_11525703/ToHPE",'
  '"inbound":"/CO XIX/PROD/coxix_prod_11525703/FromHPE"},'
  '"stub":true,"staging_dir":"./var/sftp/staging","poll_seconds":300}',
  '2026-06-20T00:00:00Z');
```

### 3.3 `payer_configs.config_json` shape

```jsonc
{
  "submitter": {
    "name": "Dzinesco",
    "contact_name": "Tyler Martinez",
    "contact_email": "tyler@dzinesco.com"
  },
  "receiver": {
    "name": "COLORADO MEDICAL ASSISTANCE PROGRAM",
    "id_qualifier": "46",
    "id": "COMEDASSISTPROG"
  },
  "bht06_allowed": ["CH", "RP"],          // claim-frequency codes; separate batches required
  "bht06_default": "CH",
  "sbr09_default": "MC",                  // MC=Medicaid; 16/MA/MB=crossover; ZZ=MCO encounter
  "sbr09_allowed": ["MC", "16", "MA", "MB", "ZZ"],
  "payer_id_qualifier": "PI",
  "payer_id": "CO_TXIX",                  // production reality = SKCO0; spec = CO_TXIX
  "pwk_supported": false,                 // CO MAP does not support PWK
  "cas_2320_group_allowed": false,        // 2320 CAS PI group only for MCO encounter denials
  "claim_type_codes": {                   // CLM05 place-of-service values accepted
    "11": "Office",
    "12": "Home",
    "99": "Other"
  }
}
```

For 835, the same `payer_id` row gets a separate config block keyed `835` with a smaller schema (BPR, payer-name on N1*PR, etc.).

### 3.4 `providers` seed data (from production files)

Self-served from 136 real prod 837P files in `docs/prodfiles/837p-from-axiscare/` and `docs/prodfiles/FromHPE/`. All 3 NPIs:

| NPI | Label | Legal name | Address | Taxonomy | Tax ID |
|-----|-------|-----------|---------|----------|--------|
| 1881068062 | Montrose | TOC, Inc. | 1100 East Main St, Suite A, Montrose, CO 814014063 | 251E00000X | 721587149 |
| 1851446637 | Delta    | TOC, Inc. | 1100 East Main St, Suite A, Montrose, CO 814014063 | 251E00000X | 721587149 |
| 1467507269 | Salida   | TOC, Inc. | 1100 East Main St, Suite A, Montrose, CO 814014063 | 251E00000X | 721587149 |

All 3 share the same address because all 3 are registered to the Montrose corporate office (per user confirmation 2026-06-20). Production files confirm `NM1*85*2*TOC, Inc.` + `N3*1100 East Main St*Suite A` + `N4*Montrose*CO*814014063` + `PRV*BI*PXC*251E00000X` + `REF*EI*721587149` is byte-identical across all 3 NPIs.

### 3.5 File-naming spec

Per the **HCPF X12 File Naming Standards Quick Guide** (https://hcpf.colorado.gov/tp-x12-filenaming):

**Outbound** (we send to `/CO XIX/PROD/coxix_prod_11525703/ToHPE`):
```
{tpid}-{transaction_type}-{yyyymmddhhmmssSSS_MT}-1of1.{ext}
```
Example: `11525703-837P-20260620132243505-1of1.x12`

**Inbound** (HPE sends to `/CO XIX/PROD/coxix_prod_11525703/FromHPE`):
```
TP{tpid}-{orig_tx}_M{tracking}-{ts}-1of1_{file_type}.x12
```
Examples:
- `TP11525703-837P_M019048402-20260520231513488-1of1_999.x12` (matches a real prod file)
- `TP11525703-837P_M019044969-20260520180505477-1of1_TA1.x12` (matches a real prod file)
- `TP11525703-837P_M<icn>-<ts>-1of1_277.x12` (future 277CA, per user decision to use 277 suffix)

`{file_type}` is one of `999`, `TA1`, `271`, `277`, `277CA`, `820`, `834`, `835`, `ENCR`. The 277CA variant is distinguished by content (ST*277CA), not the filename.

Helpers in `cyclone/edi/filenames.py`:
```python
def build_outbound_filename(tpid: str, tx: str, *, ext: str = "x12", now_mt: datetime | None = None) -> str
def parse_inbound_filename(name: str) -> InboundFilename   # NamedTuple(tpid, orig_tx, tracking, ts, file_type)
```

MT timestamps: `datetime.now(ZoneInfo("America/Denver"))` with millisecond precision, formatted `%Y%m%d%H%M%S%f` and truncated to 17 digits (millis, not micros).

### 3.6 SFTP stub

`POST /api/clearhouse/submit` body:
```json
{
  "claim_ids": ["CLM-001", "CLM-002"],
  "payer_id": "CO_TXIX"
}
```

Handler:
1. Load `clearhouse.sftp_block_json`. If `stub == true`, run the stub path.
2. For each claim, generate the 837 text via the SP7 serializer.
3. Compute the outbound filename via `build_outbound_filename(...)`.
4. Write to `{staging_dir}/{outbound_path_prefix}/{filename}` (preserves the full MFT path under staging for easy review).
5. Return a JSON receipt:
   ```json
   {
     "ok": true,
     "submitted": [
       {"claim_id": "CLM-001", "filename": "11525703-837P-20260620132243505-1of1.x12",
        "staging_path": "./var/sftp/staging/CO XIX/PROD/coxix_prod_11525703/ToHPE/11525703-..."}
     ],
     "stub": true
   }
   ```

When `stub == false` (SP13), the only change is the body of `SftpClient.write_file(host, user, keychain_ref, remote_path, local_bytes)` — it calls `paramiko.SSHClient().open_sftp().open(remote_path, "wb")` instead of writing to the staging dir. Everything else (filename build, claim serialization, error handling, pubsub event) stays the same.

### 3.7 macOS Keychain for SFTP secret

A new module `cyclone/secrets.py`:
```python
def get_secret(name: str) -> str | None:
    """Fetch a secret from macOS Keychain by service name. Returns None if absent."""
    # Uses the `keyring` library. Service = "cyclone", username = name.
    return keyring.get_password("cyclone", name)
```

The SFTP block references secrets by **name** (`"sftp.gainwell.password"`), never value. SP9 ships:
- A `secrets.py` that calls `keyring.get_password("cyclone", "sftp.gainwell.password")` and **falls back to `"<stub-secret>"`** if the Keychain entry doesn't exist.
- A `scripts/setup_keychain.sh` that the operator runs once to create the entry: `security add-generic-password -s cyclone -a sftp.gainwell.password -w '<password>'`.
- A `README.md` section in `docs/` that documents the manual setup.

No real credential is ever stored in the repo, the DB, or `payers.yaml`.

## 4. Migration + file layout

**New files:**
- `backend/src/cyclone/secrets.py` — Keychain wrapper
- `backend/src/cyclone/edi/filenames.py` — `build_outbound_filename`, `parse_inbound_filename`
- `backend/src/cyclone/edi/filenames.pyi` — type stubs
- `backend/src/cyclone/clearhouse/sftp.py` — `SftpClient.write_file`, `SftpClient.list_inbound` (stub)
- `config/payers.yaml` — payer config blocks
- `scripts/setup_keychain.sh` — manual Keychain setup helper
- `backend/tests/test_secrets.py` — Keychain wrapper tests
- `backend/tests/test_filenames.py` — filename helpers
- `backend/tests/test_sftp_stub.py` — SFTP stub behavior
- `backend/tests/test_payer_config_loading.py` — YAML loader + schema
- `backend/tests/fixtures/payers.yaml` — test fixture

**Modified files:**
- `backend/src/cyclone/api.py` — replace `PAYER_FACTORIES` lookup with `payers.get_config(payer_id, tx)`; add `POST /api/clearhouse/submit`, `POST /api/admin/reload-config`, `GET /api/clearhouse`
- `backend/src/cyclone/store.py` — add `providers`, `payers`, `payer_configs`, `clearhouse` ORM models and CRUD helpers
- `backend/src/cyclone/edi/serialize_837.py` — read provider NPI from `providers` table, not from `claim.raw_segments` alone; emit submitter + receiver from `clearhouse` + `payer_configs`
- `backend/src/cyclone/edi/parse_837.py` — extract provider NPI into a `parsed.provider_npi` field that joins to `providers.npi`
- `backend/src/cyclone/models.py` — add `Provider`, `Payer`, `PayerConfig`, `Clearhouse` Pydantic models
- `docs/reference/co-medicaid.md` — add section "SFTP submission paths" linking to `clearhouse.sftp_block_json`

**New migration:**
- `backend/src/cyclone/migrations/0007_providers_payers_clearhouse.sql` — the 4-table schema from §3.2

## 5. API surface

| Method | Path | Body | Returns |
|--------|------|------|---------|
| `GET` | `/api/clearhouse` | — | `Clearhouse` (name, tpid, sftp block, filename block) |
| `POST` | `/api/clearhouse/submit` | `{claim_ids, payer_id}` | `{ok, submitted[], stub}` |
| `GET` | `/api/config/providers` | — | `Provider[]` (filtered `?is_active=true`) |
| `GET` | `/api/config/providers/{npi}` | — | `Provider` |
| `POST` | `/api/config/providers` | `Provider` | `Provider` (creates new) |
| `PATCH` | `/api/config/providers/{npi}` | `Partial<Provider>` | `Provider` |
| `GET` | `/api/config/payers` | — | `Payer[]` |
| `GET` | `/api/config/payers/{payer_id}/configs` | — | `PayerConfig[]` (one per tx) |
| `PATCH` | `/api/config/payers/{payer_id}/configs/{tx}` | `Partial<PayerConfig>` | `PayerConfig` |
| `POST` | `/api/admin/reload-config` | — | `{ok, loaded, errors[]}` |

> **Note on path naming:** The path prefix `/api/config/*` is used for the
> SP9 config-driven providers/payers to avoid clashing with the existing
> `/api/providers` endpoint (which returns a paginated list of providers
> seen in actual claim data via `store.distinct_providers()`). The two
> endpoints serve different purposes: the existing one powers the Inbox
> UI; the new one powers the clearhouse admin view.

The clearhouse submit is the only auth-sensitive endpoint (in production: an admin key). The provider/payer CRUD are read-only for the operator during this SP — no UI form for editing.

## 6. Validation rules (new for CO MAP spec)

Added to `R_*` enum in `cyclone/validation/rules.py`:

| Rule | Description | Severity |
|------|-------------|----------|
| `R200` | `BHT06` must be in `payer_configs.bht06_allowed` for the claim's payer. | Error |
| `R201` | A single 837 envelope must not mix `BHT06=CH` and `BHT06=RP` claims. | Error |
| `R202` | `SBR09` must be in `payer_configs.sbr09_allowed` for the claim's payer. | Error |
| `R203` | `PRV*BI*PXC*<code>` must equal the provider's `taxonomy_code` from the `providers` table. | Error |
| `R204` | `NM1*85*XX*<npi>` must match an active row in `providers`. | Error |
| `R205` | `REF*EI*<tax_id>` must equal the provider's `tax_id` from the `providers` table. | Error |
| `R206` | `NM1*PR*PI*<payer_id>` must equal `payer_configs.payer_id` for the claim's payer. | Error |
| `R207` | `PWK` segment must not appear for this payer (`pwk_supported == false`). | Error |
| `R208` | 2320 loop `CAS*PI*` group only allowed for MCO encounter (`SBR09=ZZ`) denials. | Error |
| `R209` | Outbound filename must match the HCPF regex `^\d+-[A-Z0-9]+-\d{17}-1of1\.[A-Za-z0-9]+$` and use MT time. | Error |
| `R210` | Inbound filename must match the HCPF regex `^TP\d+-[A-Z0-9]+_M[A-Z0-9]+-\d{17}-1of1_[A-Z0-9]+\.x12$`. | Error |

All R200-R210 run on parse AND on serialize, so a 837 file with an unknown NPI fails the inbox lane.

## 7. Testing plan

1. **`test_filenames.py`** — 14 cases:
   - build_outbound_filename with explicit `now_mt=datetime(2026,6,20,13,22,43,505000, tzinfo=MT)` → `11525703-837P-20260620132243505-1of1.x12` (17-digit, MT, "1of1")
   - build_outbound_filename with `now_mt=None` defaults to `datetime.now(America/Denver)` (snapshot test)
   - build_outbound_filename for all 8 transaction types in the HCPF doc
   - parse_inbound_filename on a real prod 999 filename → `InboundFilename(tpid='11525703', orig_tx='837P', tracking='M019048402', ts='20260520231513488', file_type='999')`
   - parse_inbound_filename on a real prod TA1 filename
   - parse_inbound_filename rejects: missing TP prefix, wrong segment count, wrong file_type, non-numeric TPID
   - Round-trip: build → parse returns the same tpid/orig_tx/file_type

2. **`test_payer_config_loading.py`** — 9 cases:
   - Load `config/payers.yaml` → dict of `(payer_id, tx)` → config blocks
   - Missing required key in a block → boot fails with precise error
   - Pydantic schema rejects unknown `bht06_allowed` value
   - Reload endpoint re-reads the file and re-validates
   - Fallback to hard-coded `PAYER_FACTORIES` when a payer in the DB is missing from YAML (warn logged)

3. **`test_sftp_stub.py`** — 7 cases:
   - Submit 2 claims → 2 files written to `staging_dir` at the full nested MFT path
   - Submit with `stub=false` in YAML → calls `SftpClient.write_file` which is the stub function (records the call)
   - Filename matches HCPF regex (R209)
   - Pubsub emits a `clearhouse.submitted` event per claim
   - `keyring` missing or Keychain empty → stub secret returned, no error
   - `POST /api/clearhouse/submit` returns 200 + receipt with `stub: true`
   - Payer_id not in DB → 404

4. **`test_secrets.py`** — 5 cases:
   - `get_secret("sftp.gainwell.password")` returns the Keychain value (mocked)
   - Keychain entry absent → returns None
   - `keyring` library missing (Linux dev box) → returns None, no exception

5. **`test_providers_seed.py`** — 3 cases:
   - Migration 0007 runs cleanly on a fresh DB
   - Seed inserts the 3 NPIs with correct fields
   - Constraint: `tax_id` and `taxonomy_code` are consistent across all 3 providers (asserts the 251E00000X / 721587149 invariant)

6. **`test_validation_r200_r210.py`** — 11 cases (one per new rule):
   - R200: BHT06=CH works, BHT06=XX fails
   - R201: mixed-batch envelope fails
   - R202: SBR09=ZZ works (MCO encounter), SBR09=AB fails
   - R203: PRV mismatch fails
   - R204: unknown NPI fails
   - R205: tax_id mismatch fails
   - R206: wrong payer_id fails
   - R207: PWK segment present fails
   - R208: 2320 CAS*PI* on SBR09=MC fails
   - R209: filename with UTC timestamp fails (must be MT)
   - R210: inbound filename with wrong number of segments fails

**Target backend test count after SP9: 574 + 14 + 9 + 7 + 5 + 3 + 11 = 623 tests.**

## 8. Out of scope (future SPs)

- **SP10** — 277CA parser: a new `parse_277ca` module, a new 277CA lane in the Inbox, and the matching `(payer_id, "277CA")` config block in `payer_configs`.
- **SP11** — `audit_log` table with `prev_hash` chaining (hash-chained, append-only, 6-year retention per HIPAA §164.316(b)(2)).
- **SP12** — SQLCipher-backed SQLite; DB key stored in Keychain via `secrets.get_secret("cyclone.db.key")`.
- **SP13** — `paramiko`-backed `SftpClient.write_file`/`list_inbound`/`read_file`, with real Keychain secret, manual fail-over to a second MFT path, and a retry queue for transient network failures.

## 9. Open questions resolved this session

| # | Question | Resolution |
|---|----------|-----------|
| 1 | SFTP outbound + inbound paths on `mft.gainwelltechnologies.com` | `/CO XIX/PROD/coxix_prod_11525703/ToHPE` (out), `/CO XIX/PROD/coxix_prod_11525703/FromHPE` (in) — user-provided 2026-06-20 |
| 2 | 3 NPI street addresses + ZIPs | All 3 NPIs share Montrose corporate address (user confirmed 2026-06-20) — seed from prod files |
| 3 | 3 NPI taxonomy codes | `251E00000X` for all 3, self-served from 136 prod files |
| 4 | 277CA filename suffix | Use `277` per HCPF doc; distinguish 277CA by `ST*277CA` content (user confirmed 2026-06-20) |
| 5 | Payer ID: SKCO0 (current) vs CO_TXIX (spec) | Use `CO_TXIX` per spec (user confirmed 2026-06-20) |

## 10. Open questions still pending (blockers for SP10-SP13 only)

- **None for SP9.** Ready to implement.