State rate code reference UI

Outcome

State-defined rate codes are managed via a UI with diff preview before commit. Rate refreshes happen without DB migrations.

Prerequisites

• admin.manage for write/import.
• config.read to browse.

What this owns

rcm_master.state_rate_code is master reference data — a per-state catalog that CodeResolver consults as the tier-2 pricing fallback when no fee schedule matches.

Pricing tier	Source
Tier 1	Contract-scoped fee schedule entry
Tier 2	state_rate_code (managed here)
Tier 3	Catalogue fallback

Routes

• GET /api/v1/state-rate-codes — paginated list. Filters: state (comma list), serviceCategory, isActive, q (ILIKE on rate_code/description), effectiveOn (point-in-time), page, pageSize (capped at 200).
• POST /api/v1/state-rate-codes/import/preview — body { csv }; returns { added, changed, retired, totalRows, errors } without writing.
• POST /api/v1/state-rate-codes/import — body { csv, retire? }. Upserts on (state, rate_code, effective_from). With retire=true, deactivates active rows in the affected states that are missing from the CSV (sets is_active=false; data is preserved for audit, never deleted).

All three routes require admin.manage.

Operator playbook — refreshing a state's rates

1. Open /admin/state-rate-codes → set the State filter to TX. Review the current snapshot. Inactive rows surface via the Status filter.

2. Import CSV → drag-drop the new sheet (or paste CSV text). The header row must be:

   state,rate_code,description,service_category,unit_type,rate_amount_cents,effective_from,effective_to

3. Preview diff → review Added / Changed / Retired sections. Field-level diffs are shown for each changed row.

4. Decide on retirement. If this CSV is the authoritative source for the state, tick Deactivate the N retired rows on commit. Otherwise leave the box unchecked — Commit will then upsert without retiring anything.

5. Commit → toast confirms {imported, updated, retired}. The list view refreshes.

Break-glass

If the UI is unavailable, the same CSV can be sent directly:

curl -X POST -H "content-type: application/json" \
  -d "$(jq -Rn --rawfile csv /tmp/tx-rates.csv '{csv:$csv,retire:true}')" \
  $RCM_CORE_URL/api/v1/state-rate-codes/import

/import/preview is also available as a no-op safety check before the write.

Schema reference

Column	Type	Notes
state_rate_code_id	uuid PK
state	varchar(2)
rate_code	varchar(20)
description	varchar(255) NULL
service_category	varchar(50) NULL	e.g. BH, OTP, ABA.
unit_type	varchar(20) NULL	e.g. 15MIN, DAY, VISIT.
rate_amount_cents	int NULL
effective_from	date NOT NULL
effective_to	date NULL
is_active	boolean	UI sets false via retire=true.

Unique constraint: (state, rate_code, effective_from) — same key the import upserts on. Lookup index: (state, rate_code, effective_from, effective_to).

Five seeded states ship with the container (TX/CA/NY/FL/PA from master migration 023).

Validation

Check	Expected
Preview matches expected diff	Counts agree with manual eyeball
Commit inserts/updates rows	Match preview exactly
Retired rows	is_active=false, data preserved
CodeResolver for an existing rate code	Picks state row when no fee schedule matches

Troubleshooting

Symptom	Cause	Fix
Preview returns errors with row numbers	Bad header or row schema	Fix the CSV; preview is non-destructive.
Retire count high unexpectedly	CSV missing rate codes that exist in DB	Confirm the CSV is authoritative — uncheck retire to keep originals.
Pricing still uses fee schedule, not state code	Tier-1 fee schedule still wins	Expected when contract-scoped pricing applies.

Cross-references

• Managing fee schedules for tier-1 pricing.
• State configuration navigator.

Next

5.1 — Ingestion mapping editor