Plan: CMS-64 Expenditure Aggregation (Issue #379)

On this page

Status

Step Description Status

1

canopy-medicaid schema. New migration services/canopy-medicaid/migrations/20260506000002_create_medicaid_expenditures.sql adding medicaid_expenditures(id UUID PK, person_id UUID NOT NULL, claim_id TEXT NOT NULL, paid_amount_cents BIGINT NOT NULL, ffp_rate NUMERIC(5,4) NOT NULL, waiver_code TEXT, coa_category TEXT NOT NULL, service_date DATE NOT NULL, paid_at TIMESTAMPTZ NOT NULL DEFAULT now()). Indexes on (service_date, coa_category) and (waiver_code). Forward-only per ADR-016.

Not started

2

canopy-medicaid store + API. New services/canopy-medicaid/src/store/expenditures.rs (record, list_by_quarter, aggregate_by_ffp_and_category). New services/canopy-medicaid/src/api/expenditures.rs exposing POST /v1/medicaid/expenditures (record), GET /v1/medicaid/expenditures/aggregations?fy=…&q=…. Register routes.

Not started

3

canopy-reporting CMS-64 rewrite. Replace the proxy at services/canopy-reporting/src/reporting/medicaid.rs:184-255 with real aggregation: read from canopy-medicaid’s expenditures aggregation endpoint, group by FFP rate × waiver × COA category, emit one CSV row per CMS-64 line. The existing enrolled-count code path becomes a #[cfg(test)] fixture or is removed entirely.

Not started

4

FY26 zero-output disclaimer. Until the upstream claim-adjudication wiring lands (separate plan, not in scope here), medicaid_expenditures is empty for all of FY26. The CMS-64 generator returns a CSV of zeros with a row count equal to the line schema length. Document in CHANGELOG that the report is structurally complete but reports zero expenditures pending claims integration.

Not started

5

Tests + docs. Unit tests in services/canopy-reporting/src/reporting/medicaid.rs: zero rows produce zero-CSV, multiple FFP rates split into separate rows, waiver split into per-waiver rows, CSV column ordering matches CMS-64 line numbers. Update the Service Catalog canopy-medicaid + canopy-reporting tables. CHANGELOG === Changed (proxy → real aggregation). Plan archives.

Not started

6

OpenAPI sync. cargo xtask api-docs regenerates snapshots for canopy-medicaid + canopy-reporting.

Not started

Issue: #379
Branch: feat/cms-64-expenditure-aggregation
Labels: type::feature, priority::medium, service::reporting, service::medicaid, program::medicaid, federal-partner::cms, workflow::ready

Context

services/canopy-reporting/src/reporting/medicaid.rs:184-255 is documented as the CMS-64 path but counts enrolled members and labels them as expenditure. The CMS-64 schema demands actual paid amounts grouped by Federal Financial Participation (FFP) rate, waiver code, and category-of-aid. The enrolled-count proxy is structurally wrong; the file annotates this as a known gap, but the report cannot be submitted until real expenditures flow.

Similar to ACF-196 for TANF, the upstream data does not yet exist in canopy. This plan creates the table + ingest endpoint and rewrites the aggregator. The actual claim-adjudication bridge (where rows arrive from MMIS / claims processors) remains a separate concern.

Code references

  • services/canopy-reporting/src/reporting/medicaid.rs:184-255 — current CMS-64 proxy.

  • services/canopy-medicaid/migrations/ — migration directory to extend.

  • ADR-001 — expenditures live in canopy-medicaid’s DB; canopy-reporting reads via HTTP.

  • ADR-016 — Forward-only migrations

Scope

In scope:

  • medicaid_expenditures table + CRUD endpoints in canopy-medicaid.

  • CMS-64 rewrite in canopy-reporting using the new data path.

  • Unit tests on aggregation correctness.

Out of scope:

  • Real claim-adjudication wiring (where the rows come from). Separate plan when external claims integration scope arrives.

  • CMS-37 (financial managment standard) — separate report, separate plan.

  • Quarterly amendment/correction workflow.

  • T-MSIS expenditures (already covered separately by canopy-reporting’s existing T-MSIS path).

Dependencies

  • Predecessor medicaid-federal-reporting plan (already archived).

  • No prerequisite plans on disk.

Design

medicaid_expenditures schema:

CREATE TABLE medicaid_expenditures (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    person_id UUID NOT NULL,
    claim_id TEXT NOT NULL,
    paid_amount_cents BIGINT NOT NULL,
    ffp_rate NUMERIC(5,4) NOT NULL,
    waiver_code TEXT,
    coa_category TEXT NOT NULL,
    service_date DATE NOT NULL,
    paid_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX medicaid_expenditures_by_service
    ON medicaid_expenditures (service_date, coa_category);

CREATE INDEX medicaid_expenditures_by_waiver
    ON medicaid_expenditures (waiver_code);

Aggregation API:

#[derive(Serialize, Deserialize, sqlx::FromRow, ToSchema)]
pub struct ExpenditureAggregate {
    pub fiscal_quarter: String,
    pub ffp_rate: Decimal,
    pub waiver_code: Option<String>,
    pub coa_category: String,
    pub total_cents: i64,
    pub claim_count: i64,
}

pub async fn aggregate_by_ffp_and_category(
    pool: &PgPool,
    fiscal_quarter: &str,
) -> sqlx::Result<Vec<ExpenditureAggregate>>;

CSV generator emits CMS-64 lines in their canonical order. Each row: line number, FFP rate, total federal share, total state share, total computable.

Files Touched

File Change

services/canopy-medicaid/migrations/20260506000002_create_medicaid_expenditures.sql

New migration

services/canopy-medicaid/src/store/expenditures.rs

New store module

services/canopy-medicaid/src/api/expenditures.rs

New API module

services/canopy-medicaid/src/api/mod.rs

Register routes

services/canopy-reporting/src/reporting/medicaid.rs

Replace lines 184-255 with real aggregation

services/canopy-reporting/src/reporting/medicaid.rs (test module)

Replace enrolled-count tests with aggregation tests

docs/modules/ROOT/openapi/canopy-medicaid.json

Regenerated

docs/modules/ROOT/openapi/canopy-reporting.json

Regenerated

Service Catalog

Route + table updates

CHANGELOG.adoc

=== Changed (proxy → real aggregation; document FY26 zero-output)

Verification

  1. cargo nextest run -p canopy-medicaid -p canopy-reporting --lib — unit tests pass.

  2. cargo xtask api-docs — snapshots regenerate clean.

  3. cargo xtask dev start && cargo nextest run -p canopy-reporting --test cms64_test --run-ignored only — integration test passes (zero-rows expected pre-claims).

  4. Manual smoke: POST 3 expenditure rows with different FFP rates, GET /v1/reporting/cms-64?fy=2026q3, confirm CSV groups correctly.

  5. cargo xtask validate — full battery green.

Documentation Updates

  • Service Catalog — canopy-medicaid + canopy-reporting routes + tables

  • CHANGELOG.adoc — entry under == Unreleased / === Changed, noting FY26 zero-expenditure expected

  • docs/modules/ROOT/pages/services/canopy-reporting.adoc — CMS-64 path documentation

  • docs/modules/ROOT/pages/federal-requirements.adoc — CMS-64 row update

  • Plan archive: move to plans/archive/ post-merge

Edit this page · default