Civilytics/uscogdata
3
0
Fork 0
Code Issues 8 Pull Requests Actions Packages Projects Releases Wiki Activity

flow_prefixes cannot classify item codes correctly: implement the three-concept expenditure model on the crosswalk spend_type #11

New Issue
Closed
opened 2026-07-29 00:05:28 -04:00 by jared · 2 comments
jared commented 2026-07-29 00:05:28 -04:00
Owner
Copy Link
Copy Source

Filed from the Madison walkthrough audit (cog_explorer/docs/walkthroughs/FINDINGS.md, 2026-07-28).

Root cause

cog_spending() and cog_revenue() classify item codes with a fixed first-letter allowlist — flow_prefixes = c("E","F","G") and c("T","A","U","B","C","D") respectively, wired through the spending_long/revenue_long SQL views (WHERE LEFT(item_code, 1) IN (...)). That architecture cannot be patched code-by-code, because at least one prefix carries both revenue and expenditure codes under a single letter:

Y01,Unemployment-Contribution,Insurance Trust,01,Insurance Trust      <- revenue
Y02,Unemployment-Interest Revenue,Insurance Trust,02,Insurance Trust  <- revenue
Y05,Unemployment-Benefit Payments,Insurance Trust,05,Insurance Trust  <- expenditure
Y06,Unemployment-Ext & Spec Pmts,Insurance Trust,06,Insurance Trust   <- expenditure

No single-letter allowlist routes those four correctly. The crosswalk's spend_type column, assigned per code rather than per prefix, does.

Three findings are three consequences of that one architecture.

Findings resolved

Finding Severity Verdict Summary
F-018 high definitional Item-code prefix Y mixes revenue and expenditure codes under one first letter, which is why a prefix-based filter cannot classify it correctly by construction — the architectural root cause
F-012 high definitional cog_spending(..., expenditure_concept = "direct") excludes interest on long-term debt from every total, ~7.1% below Census's own published "Direct Expenditure" for Madison FY2022
F-017 medium defect Item codes Q12/Q18 (state intergovernmental transfers to school districts) are missing from both verbs, so total — the concept that is supposed to include intergovernmental transfers — is understated for state governments too

Reproduction (verbatim from FINDINGS.md, verified against the live corpus)

F-018

grep -E "^Y0[1256]," data/item_code_xwalk.csv
# Y01,Unemployment-Contribution,Insurance Trust,01,Insurance Trust
# Y02,Unemployment-Interest Revenue,Insurance Trust,02,Insurance Trust
# Y05,Unemployment-Benefit Payments,Insurance Trust,05,Insurance Trust
# Y06,Unemployment-Ext & Spec Pmts,Insurance Trust,06,Insurance Trust

F-012

con <- uscogdata:::.ensure_session()

# Query the RAW `long` table directly -- NOT through cog_spending(), which
# has already filtered to E/F/G by the time it returns anything, and so
# cannot be used as evidence about what the corpus itself contains.
DBI::dbGetQuery(con, "
  SELECT canonical_govid, year, item_code, amt FROM long
  WHERE canonical_govid = '552025209777' AND year = 2022 AND item_code = 'I89'
")
#>   canonical_govid year item_code   amt
#> 1    552025209777 2022       I89 46609      -- present: the corpus ingests it

# Same filter against the VIEW cog_spending() actually reads from:
DBI::dbGetQuery(con, "
  SELECT * FROM spending_long
  WHERE canonical_govid = '552025209777' AND year = 2022 AND item_code = 'I89'
")
#> <0 rows>                                    -- filtered out by the view itself

# Reconciled against the Census Bureau's own 2022 Individual Unit File
# (2022FinEstDAT_06052025modp_pu.txt), records for govid 552025209777
# (Madison) and 551025177056 (Dane County), summed by item-code prefix:
#                E            F           I          J/Y   E+F          E+F+I+J+Y
# Madison  563,735,000  44,549,000  46,609,000    0    608,284,000  654,893,000
# Dane Co. 586,078,000  60,632,000   9,843,000    0    646,710,000  656,553,000
# cog_spending(..., expenditure_concept = "direct") for both governments
# reproduces the E+F column exactly: 608,284,000 and 646,710,000, to the
# dollar. cog_spending(..., expenditure_concept = "total") returns the
# same 608,284,000 for Madison with zero intergovernmental rows.

F-017

con <- uscogdata:::.ensure_session()

# Raw `long` table -- NOT through cog_spending()/cog_revenue(), which have
# already filtered by prefix before returning anything, so cannot be used
# as evidence about what the corpus itself contains.
DBI::dbGetQuery(con, "
  SELECT canonical_govid, year, item_code, amt FROM long
  WHERE canonical_govid = '550000227544' AND year = 2022
    AND item_code IN ('Q12','Q18')
")
#>   canonical_govid year item_code     amt
#> 1    550000227544 2022       Q12 7702787
#> 2    550000227544 2022       Q18  576567

# Neither cog_spending()'s flow_prefixes = c("E","F","G") nor
# cog_revenue()'s flow_prefixes = c("T","A","U","B","C","D") include "Q".

# Government-type check, corpus-wide: which types ever carry Q12/Q18?
DBI::dbGetQuery(con, "
  SELECT type, item_code, COUNT(*) n_rows, SUM(amt) total_amt
  FROM long WHERE item_code IN ('Q12','Q18') AND NOT is_aggregate
  GROUP BY type, item_code ORDER BY type, item_code
")
#>   type item_code n_rows  total_amt
#> 1    0       Q12    540 3706954885
#> 2    0       Q18    230  142513723   -- type 0 (state) only

Wisconsin state FY2022: Q12 + Q18 = $8,279,354,000 absent from total.

Why it matters

Every "direct spending" total this package produces — for all 55 years, for every government in the corpus — is below Census's own published concept of the same name by exactly the excluded interest. For Madison FY2022 that is -7.1%. Unlike a units question, it is not reversible through the public interface: the I/J/Y rows exist in the corpus but no argument or column returns them, so recovering the broader figure requires a code change here. And none of the mechanisms this same function already has for flagging correct-but-surprising results (aggregate_fallback, pop_source == "unavailable", provenance$expenditure_concept_direct_suppressed) fires. For state governments, total — the concept explicitly meant to include intergovernmental transfers — is missing $8.3B of them for a single state in a single year.

The agreed design (settled 2026-07-28 by the project owner — not open for re-litigation)

Three named concepts:

  • total = primary + interest + intergovernmental transfers
  • direct = primary + interest — matching Census's published Direct Expenditure exactly (the concept today's direct falls short of by the excluded interest)
  • primary = direct minus debt service — the new default expenditure_concept, replacing today's direct

primary is not a Census term; it is the established fiscal-policy term for spending excluding interest (IMF/CBO/OECD "primary balance"/"primary spending"), chosen because once direct is redefined to include interest, primary is precisely what today's direct already computes.

Implementation reclassifies item codes onto these three concepts using the crosswalk's spend_type column, not item-code first-letter prefixes — because F-018 shows a prefix-only scheme cannot be patched to handle every code correctly.

Definition of done

  1. expenditure_concept = c("primary", "direct", "total") with primary the default; classification driven by spend_type, and flow_prefixes retired as the classification mechanism.

  2. Q12/Q18 (spend_type == "IG Transfer to School Districts") are inside total.

  3. Y05/Y06 classify as expenditure and Y01/Y02 as revenue, from the same spend_type-driven mechanism — the concrete proof the prefix architecture is gone.

  4. Test goes green: tests/testthat/test-expenditure-concepts.R → test_that("expenditure concepts classify on spend_type, not item-code prefix", ...). Against the bundled fixture it asserts, with amounts read from the raw corpus (never through the verb under test):

    • Madison FY2020 primary (the default) = $623,347,000;
    • Madison FY2020 direct = $651,051,000 = primary + I89 (27,704 thousands);
    • Wisconsin state FY2019 total = $36,191,455,000 = today's total ($29,226,534,000) + Q12 (6,431,530) + Q18 (533,391) thousands;
    • a state-year's spending codes_included contains Y05 and its revenue codes_included contains Y01, with neither verb claiming the other's codes.

    Remove the skip() on line 1 of the test body to activate.

(Madison FY2022's I89 = 46,609 thousands — the figure reconciled against Census's published Individual Unit File — is outside the fixture's year window (2011/2012/2019/2020), so the test asserts the same invariant on FY2020. The FY2022 expectation is recorded in the test file as a comment for whoever runs it against the full corpus.)

Cross-references

  • cog-api must expose the new vocabulary once this ships — finding F-026, tracked there. provenance.verb/provenance.call confirm the API calls cog_spending() directly rather than reimplementing the filter.
  • census_of_governments_finance_pipeline#58 (open) covers the J prefix — missing from both the category crosswalk and the flow prefixes. Same mechanism, different codes; that crosswalk work is a prerequisite for J landing correctly in these concepts, since Census's Direct Expenditure is E + F + I + J + Y.
  • Also depends on the pipeline's crosswalk-coverage work: summary_categories currently has zero rows for prefixes I, Q, X, and Y, so admitting them here without a category mapping would surface rows with category = NA.
Filed from the **Madison walkthrough audit** (`cog_explorer/docs/walkthroughs/FINDINGS.md`, 2026-07-28). ## Root cause `cog_spending()` and `cog_revenue()` classify item codes with a fixed **first-letter allowlist** — `flow_prefixes = c("E","F","G")` and `c("T","A","U","B","C","D")` respectively, wired through the `spending_long`/`revenue_long` SQL views (`WHERE LEFT(item_code, 1) IN (...)`). That architecture cannot be patched code-by-code, because at least one prefix carries **both** revenue and expenditure codes under a single letter: ``` Y01,Unemployment-Contribution,Insurance Trust,01,Insurance Trust <- revenue Y02,Unemployment-Interest Revenue,Insurance Trust,02,Insurance Trust <- revenue Y05,Unemployment-Benefit Payments,Insurance Trust,05,Insurance Trust <- expenditure Y06,Unemployment-Ext & Spec Pmts,Insurance Trust,06,Insurance Trust <- expenditure ``` No single-letter allowlist routes those four correctly. The crosswalk's **`spend_type`** column, assigned per code rather than per prefix, does. Three findings are three consequences of that one architecture. ## Findings resolved | Finding | Severity | Verdict | Summary | |---|---|---|---| | **F-018** | high | definitional | Item-code prefix `Y` mixes revenue and expenditure codes under one first letter, which is why a prefix-based filter cannot classify it correctly by construction — the architectural root cause | | **F-012** | high | definitional | `cog_spending(..., expenditure_concept = "direct")` excludes interest on long-term debt from every total, ~7.1% below Census's own published "Direct Expenditure" for Madison FY2022 | | **F-017** | medium | defect | Item codes `Q12`/`Q18` (state intergovernmental transfers to school districts) are missing from **both** verbs, so `total` — the concept that is supposed to include intergovernmental transfers — is understated for state governments too | ## Reproduction (verbatim from FINDINGS.md, verified against the live corpus) **F-018** ```bash grep -E "^Y0[1256]," data/item_code_xwalk.csv # Y01,Unemployment-Contribution,Insurance Trust,01,Insurance Trust # Y02,Unemployment-Interest Revenue,Insurance Trust,02,Insurance Trust # Y05,Unemployment-Benefit Payments,Insurance Trust,05,Insurance Trust # Y06,Unemployment-Ext & Spec Pmts,Insurance Trust,06,Insurance Trust ``` **F-012** ```r con <- uscogdata:::.ensure_session() # Query the RAW `long` table directly -- NOT through cog_spending(), which # has already filtered to E/F/G by the time it returns anything, and so # cannot be used as evidence about what the corpus itself contains. DBI::dbGetQuery(con, " SELECT canonical_govid, year, item_code, amt FROM long WHERE canonical_govid = '552025209777' AND year = 2022 AND item_code = 'I89' ") #> canonical_govid year item_code amt #> 1 552025209777 2022 I89 46609 -- present: the corpus ingests it # Same filter against the VIEW cog_spending() actually reads from: DBI::dbGetQuery(con, " SELECT * FROM spending_long WHERE canonical_govid = '552025209777' AND year = 2022 AND item_code = 'I89' ") #> <0 rows> -- filtered out by the view itself # Reconciled against the Census Bureau's own 2022 Individual Unit File # (2022FinEstDAT_06052025modp_pu.txt), records for govid 552025209777 # (Madison) and 551025177056 (Dane County), summed by item-code prefix: # E F I J/Y E+F E+F+I+J+Y # Madison 563,735,000 44,549,000 46,609,000 0 608,284,000 654,893,000 # Dane Co. 586,078,000 60,632,000 9,843,000 0 646,710,000 656,553,000 # cog_spending(..., expenditure_concept = "direct") for both governments # reproduces the E+F column exactly: 608,284,000 and 646,710,000, to the # dollar. cog_spending(..., expenditure_concept = "total") returns the # same 608,284,000 for Madison with zero intergovernmental rows. ``` **F-017** ```r con <- uscogdata:::.ensure_session() # Raw `long` table -- NOT through cog_spending()/cog_revenue(), which have # already filtered by prefix before returning anything, so cannot be used # as evidence about what the corpus itself contains. DBI::dbGetQuery(con, " SELECT canonical_govid, year, item_code, amt FROM long WHERE canonical_govid = '550000227544' AND year = 2022 AND item_code IN ('Q12','Q18') ") #> canonical_govid year item_code amt #> 1 550000227544 2022 Q12 7702787 #> 2 550000227544 2022 Q18 576567 # Neither cog_spending()'s flow_prefixes = c("E","F","G") nor # cog_revenue()'s flow_prefixes = c("T","A","U","B","C","D") include "Q". # Government-type check, corpus-wide: which types ever carry Q12/Q18? DBI::dbGetQuery(con, " SELECT type, item_code, COUNT(*) n_rows, SUM(amt) total_amt FROM long WHERE item_code IN ('Q12','Q18') AND NOT is_aggregate GROUP BY type, item_code ORDER BY type, item_code ") #> type item_code n_rows total_amt #> 1 0 Q12 540 3706954885 #> 2 0 Q18 230 142513723 -- type 0 (state) only ``` Wisconsin state FY2022: `Q12` + `Q18` = **$8,279,354,000** absent from `total`. ## Why it matters Every "direct spending" total this package produces — for all 55 years, for every government in the corpus — is below Census's own published concept of the same name by exactly the excluded interest. For Madison FY2022 that is **-7.1%**. Unlike a units question, it is **not reversible through the public interface**: the `I`/`J`/`Y` rows exist in the corpus but no argument or column returns them, so recovering the broader figure requires a code change here. And none of the mechanisms this same function already has for flagging correct-but-surprising results (`aggregate_fallback`, `pop_source == "unavailable"`, `provenance$expenditure_concept_direct_suppressed`) fires. For state governments, `total` — the concept explicitly meant to include intergovernmental transfers — is missing $8.3B of them for a single state in a single year. ## The agreed design (settled 2026-07-28 by the project owner — not open for re-litigation) Three named concepts: * **`total`** = primary + interest + intergovernmental transfers * **`direct`** = primary + interest — matching Census's published **Direct Expenditure** exactly (the concept today's `direct` falls short of by the excluded interest) * **`primary`** = direct minus debt service — the **new default** `expenditure_concept`, replacing today's `direct` `primary` is not a Census term; it is the established fiscal-policy term for spending excluding interest (IMF/CBO/OECD "primary balance"/"primary spending"), chosen because once `direct` is redefined to include interest, `primary` is precisely what today's `direct` already computes. Implementation **reclassifies item codes onto these three concepts using the crosswalk's `spend_type` column, not item-code first-letter prefixes** — because F-018 shows a prefix-only scheme cannot be patched to handle every code correctly. ## Definition of done 1. `expenditure_concept = c("primary", "direct", "total")` with `primary` the default; classification driven by `spend_type`, and `flow_prefixes` retired as the classification mechanism. 2. `Q12`/`Q18` (`spend_type == "IG Transfer to School Districts"`) are inside `total`. 3. `Y05`/`Y06` classify as expenditure and `Y01`/`Y02` as revenue, from the same `spend_type`-driven mechanism — the concrete proof the prefix architecture is gone. 4. Test goes green: **`tests/testthat/test-expenditure-concepts.R`** → `test_that("expenditure concepts classify on spend_type, not item-code prefix", ...)`. Against the bundled fixture it asserts, with amounts read from the raw corpus (never through the verb under test): * Madison FY2020 `primary` (the default) = **$623,347,000**; * Madison FY2020 `direct` = **$651,051,000** = primary + `I89` (27,704 thousands); * Wisconsin state FY2019 `total` = **$36,191,455,000** = today's total ($29,226,534,000) + `Q12` (6,431,530) + `Q18` (533,391) thousands; * a state-year's spending `codes_included` contains `Y05` and its revenue `codes_included` contains `Y01`, with neither verb claiming the other's codes. Remove the `skip()` on line 1 of the test body to activate. *(Madison FY2022's `I89 = 46,609` thousands — the figure reconciled against Census's published Individual Unit File — is outside the fixture's year window (2011/2012/2019/2020), so the test asserts the same invariant on FY2020. The FY2022 expectation is recorded in the test file as a comment for whoever runs it against the full corpus.)* ## Cross-references * **`cog-api`** must expose the new vocabulary once this ships — finding F-026, tracked there. `provenance.verb`/`provenance.call` confirm the API calls `cog_spending()` directly rather than reimplementing the filter. * **`census_of_governments_finance_pipeline#58`** (open) covers the `J` prefix — missing from both the category crosswalk and the flow prefixes. Same mechanism, different codes; that crosswalk work is a prerequisite for `J` landing correctly in these concepts, since Census's Direct Expenditure is `E + F + I + J + Y`. * Also depends on the pipeline's crosswalk-coverage work: `summary_categories` currently has **zero rows** for prefixes `I`, `Q`, `X`, and `Y`, so admitting them here without a category mapping would surface rows with `category = NA`.
jared commented 2026-07-29 11:39:47 -04:00
Author
Owner
Copy Link
Copy Source

Blocked: the classification column this issue names does not exist in the corpus

Started implementation and stopped at a hard prerequisite. This issue's own final cross-reference predicted it; the situation is worse than that note implies, so recording the measurements before anyone else picks this up.

1. spend_type is not a corpus column

The design says classification is "driven by the crosswalk's spend_type column". That column lives in cog_explorer/data/item_code_xwalk.csv — a local analysis file in a directory with no git remote, not part of uscogdata and not part of the published corpus.

What the corpus actually publishes is summary_categories, whose columns are:

item_code, category, category_type, spend_subtype, revenue_subtype

There is no spend_type. The good news: category_type (expenditure | revenue) is already a per-code classification, so it is the right mechanism — it just needs rows.

2. summary_categories has zero rows for I, Q, X and Y

Published corpus (290 rows, post-#65):

prefix A B C D E F G J L M T U
rows 24 17 14 14 41 38 38 3 32 34 25 10

I, Q, X, Y: 0 rows. And 40-spending_annotated.sql uses LEFT JOIN summary_categories, so admitting those prefixes today yields rows with category = NULL — the outcome this issue explicitly rules out.

3. The bundled fixture is stale on top of that

The fixture corpus every test in this package runs against has no J rows either — it predates the #65 crosswalk work already shipped:

published fixture
A 24 22
E 41 37
J 3 0
I/Q/X/Y 0 0

So even the crosswalk work that has already landed is not reflected in this package's tests. The fixture needs regenerating regardless of this issue.

The underlying rows are all present in the fixture's raw long (I: 5 codes / 76,811 rows; Q: 3 / 6,614; X: 16 / 104,999; Y: 15 / 46,358) — only the category mapping is missing.

4. The mapping is bigger and more interesting than 33 codes

The corpus carries codes the explorer crosswalk does not: Q11, X04, X06, X09, X14, X35. More importantly, X and Y are not all flows. Sorting by what Census actually treats them as:

revenue expenditure balance / holdings
X (Employee Retirement) X01, X02, X05, X08 X11, X12 X04, X06, X09, X14, X21, X30, X35, X42, X44, X47
Y (Insurance Trust) Y01, Y02, Y04, Y11, Y12, Y51, Y52 Y05, Y06, Y14, Y53 Y07, Y08, Y21, Y61

X21/X30/X44 are asset holdings (cash, federal securities, other securities); Y07 is a balance in the US Treasury. They are neither revenue nor expenditure, and category_type today has only those two values. Admitting X/Y therefore needs a third category_type (or an explicit decision to leave the balance codes uncategorised and out of both verbs).

Two more facts that bear on the design:

  • The X series ends at FY2016 (X01/X05/X08/X11/X21/X42/X44/X47 all stop; X02/X30 run 2012–2016 only). Census moved employee retirement to a separate survey. That is an uncatalogued series break in its own right.
  • Trust treatment is already in scope for the pipeline's Wave 5 (docs/STATUS.md: "109 discontinued codes, G/K→F collapse, trust-treatment break"), so deciding it here risks contradicting that work.

What unblocks this

A chain across two repos, in order:

  1. cog_pipeline — extend data/summary_categories.csv to cover I / Q / X / Y (~39 codes), with category_type per code and a ruling on the balance codes.
  2. Rebuild + republish the corpus (owner approval required).
  3. Regenerate the bundled fixture here (needed anyway, see §3).
  4. uscogdata — replace the flow_prefixes allowlists in inst/sql/20-spending_long.sql / 21-revenue_long.sql with a join on summary_categories.category_type, and map the three concepts onto spend_subtype.

Step 4 — the actual content of this issue — is small and well-specified. Steps 1–3 are the work, and step 1 carries decisions (the third category_type, the X/Y trust treatment) that overlap Wave 5 and are not mine to settle.

Nothing in the three-concept model itself is in question — total / direct / primary with primary as the new default is settled and is not what is blocking. Note also that subtype=assistance returning 0 rows everywhere is a separate and now-shallower problem: J is in the published crosswalk (3 rows) since #65, so that one only needs step 4's J admission, not the I/Q/X/Y work.

## Blocked: the classification column this issue names does not exist in the corpus Started implementation and stopped at a hard prerequisite. This issue's own final cross-reference predicted it; the situation is worse than that note implies, so recording the measurements before anyone else picks this up. ### 1. `spend_type` is not a corpus column The design says classification is "driven by the crosswalk's `spend_type` column". That column lives in **`cog_explorer/data/item_code_xwalk.csv`** — a local analysis file in a directory with **no git remote**, not part of `uscogdata` and not part of the published corpus. What the corpus actually publishes is `summary_categories`, whose columns are: ``` item_code, category, category_type, spend_subtype, revenue_subtype ``` There is no `spend_type`. The good news: **`category_type` (`expenditure` | `revenue`) is already a per-code classification**, so it is the right mechanism — it just needs rows. ### 2. `summary_categories` has zero rows for I, Q, X and Y Published corpus (290 rows, post-#65): | prefix | A | B | C | D | E | F | G | J | L | M | T | U | |---|--:|--:|--:|--:|--:|--:|--:|--:|--:|--:|--:|--:| | rows | 24 | 17 | 14 | 14 | 41 | 38 | 38 | 3 | 32 | 34 | 25 | 10 | `I`, `Q`, `X`, `Y`: **0 rows.** And `40-spending_annotated.sql` uses `LEFT JOIN summary_categories`, so admitting those prefixes today yields rows with `category = NULL` — the outcome this issue explicitly rules out. ### 3. The bundled fixture is stale on top of that The fixture corpus every test in this package runs against has **no `J` rows either** — it predates the #65 crosswalk work already shipped: | | published | fixture | |---|--:|--:| | A | 24 | 22 | | E | 41 | 37 | | J | 3 | **0** | | I/Q/X/Y | 0 | 0 | So even the crosswalk work that has already landed is not reflected in this package's tests. **The fixture needs regenerating regardless of this issue.** The underlying rows are all present in the fixture's raw `long` (I: 5 codes / 76,811 rows; Q: 3 / 6,614; X: 16 / 104,999; Y: 15 / 46,358) — only the category mapping is missing. ### 4. The mapping is bigger and more interesting than 33 codes The corpus carries codes the explorer crosswalk does not: `Q11`, `X04`, `X06`, `X09`, `X14`, `X35`. More importantly, **X and Y are not all flows.** Sorting by what Census actually treats them as: | | revenue | expenditure | **balance / holdings** | |---|---|---|---| | X (Employee Retirement) | X01, X02, X05, X08 | X11, X12 | X04, X06, X09, X14, X21, X30, X35, X42, X44, X47 | | Y (Insurance Trust) | Y01, Y02, Y04, Y11, Y12, Y51, Y52 | Y05, Y06, Y14, Y53 | Y07, Y08, Y21, Y61 | `X21`/`X30`/`X44` are *asset holdings* (cash, federal securities, other securities); `Y07` is a balance in the US Treasury. They are neither revenue nor expenditure, and `category_type` today has only those two values. Admitting X/Y therefore needs **a third `category_type`** (or an explicit decision to leave the balance codes uncategorised and out of both verbs). Two more facts that bear on the design: - **The X series ends at FY2016** (X01/X05/X08/X11/X21/X42/X44/X47 all stop; X02/X30 run 2012–2016 only). Census moved employee retirement to a separate survey. That is an uncatalogued series break in its own right. - Trust treatment is already in scope for the pipeline's **Wave 5** (`docs/STATUS.md`: "109 discontinued codes, G/K→F collapse, **trust-treatment break**"), so deciding it here risks contradicting that work. ### What unblocks this A chain across two repos, in order: 1. **cog_pipeline** — extend `data/summary_categories.csv` to cover I / Q / X / Y (~39 codes), with `category_type` per code and a ruling on the balance codes. 2. **Rebuild + republish** the corpus (owner approval required). 3. **Regenerate the bundled fixture** here (needed anyway, see §3). 4. **uscogdata** — replace the `flow_prefixes` allowlists in `inst/sql/20-spending_long.sql` / `21-revenue_long.sql` with a join on `summary_categories.category_type`, and map the three concepts onto `spend_subtype`. Step 4 — the actual content of this issue — is small and well-specified. Steps 1–3 are the work, and step 1 carries decisions (the third `category_type`, the X/Y trust treatment) that overlap Wave 5 and are not mine to settle. **Nothing in the three-concept model itself is in question** — `total` / `direct` / `primary` with `primary` as the new default is settled and is not what is blocking. Note also that `subtype=assistance` returning 0 rows everywhere is a *separate* and now-shallower problem: `J` **is** in the published crosswalk (3 rows) since #65, so that one only needs step 4's `J` admission, not the I/Q/X/Y work.
jared commented 2026-07-30 16:10:30 -04:00
Author
Owner
Copy Link
Copy Source

Phase 1 (crosswalk) is done and published. The corpus at pipeline_commit e64a046 (published 2026-07-30) now carries every code this issue needs:

prefix codes classification
I I89, I91-I94 expenditure / spend_subtype = interest
Q Q11, Q12, Q18 expenditure / intergovernmental
Y flow Y05, Y06, Y14, Y53 expenditure / insurance_benefits
Y flow Y01, Y02, Y04, Y11, Y12, Y51, Y52 revenue / revenue_subtype = insurance_trust
X/Y/W/Z 14 codes category_type = balance (pipeline#76)

Prefix Y now spans all three category_types, which is F-018 made concrete in the crosswalk. Pipeline PRs #77 and #78. The fixture is regenerated at the same commit.

Finding 1: direct must include insurance benefits, not just interest

This issue's model says direct = primary + interest. Validated against Census and that is incomplete. The 2006 manual §5.2.2.1:

Direct expenditure comprises all final expenditures paid to current employees, former employees (retirees) and to private sector entities outside of the government itself (e.g. all expenditure other than intergovernmental expenditure).

Social insurance trust is a sector (§5.3.4), not a character, so its benefit payments are direct expenditure. Reconciled numerically against Census's own published state aggregates (20statetypepu.txt, level 2 — independent of our corpus), FY2020:

Wisconsin California
codes matching Census exactly 168/176 166/174
Census expenditure-code dollar coverage 100.0000% 100.0000%
primary $26.97B $231.02B
+ interest $27.56B (+2.17%) $236.37B (+2.32%)
+ interest + insurance_benefits $28.61B (+6.09%) $261.63B (+13.25%)
total (= Census's published expenditure sum) $40,460,441k ✓ $375,553,177k ✓

total reproduces Census's published sum to the dollar for both states, and the crosswalk covers 100% of their published expenditure codes — so "all expenditure except intergovernmental" is exactly primary + interest + insurance_benefits. Every one of the 8 mismatches is a retirement-holdings code (Z01, X01, X30, X40, X50, X71, X80), i.e. the documented "X family ends FY2016" gap, not a flow discrepancy.

Omitting insurance benefits would understate California's Direct Expenditure by 10.9% — larger than the 7.1% error F-012 was filed for. Owner confirmed 2026-07-30.

This does not change this issue's fixture assertion: Madison is a city and carries no Y rows, so direct = primary + I89 still holds there.

Finding 2: DoD item 4's Y01-in-default assertion conflicts with #12

DoD item 4 and the test at line 73 require Y01 in the default cog_revenue() output. #12 requires the default to be general = $31,338,293,000 for WI FY2012, with insurance trust reachable only via revenue_concept = "total".

Measured on the regenerated fixture, WI FY2012:

general (revenue minus insurance_trust)  = 31,338,293,000   <- matches #12 exactly
Y insurance-trust revenue (Y01, Y11)     =      1,259,785 k
X retirement revenue (X01, X05, X08)     =      2,038,800 k

Y01 (unemployment contributions) and X01 (retirement contributions) are both Insurance Trust Revenue. A default holding one but not the other matches no Census concept. Manual §4.3:

General revenue comprises all revenue except that classified as liquor store, utility, or insurance trust revenue.

Resolution (mirroring Census, per the owner): the default stays general, and DoD item 4's Y01 proof moves off the default call — assert it via the crosswalk / an explicit concept argument instead. The F-018 point is that Y01 classifies as revenue while Y05 classifies as expenditure from one mechanism; that is fully provable without requiring Y01 in the default result, and it is now visible directly in summary_categories.

Still to do

Phase 2 (reader) only: rewrite inst/sql/20-spending_long.sql off LEFT(item_code,1) IN ('E','F','G') onto a join to summary_categories, add expenditure_concept = c("primary","direct","total") with primary default, and delete the skip().

**Phase 1 (crosswalk) is done and published.** The corpus at `pipeline_commit e64a046` (published 2026-07-30) now carries every code this issue needs: | prefix | codes | classification | |---|---|---| | `I` | I89, I91-I94 | `expenditure` / `spend_subtype = interest` | | `Q` | Q11, Q12, Q18 | `expenditure` / `intergovernmental` | | `Y` flow | Y05, Y06, Y14, Y53 | `expenditure` / `insurance_benefits` | | `Y` flow | Y01, Y02, Y04, Y11, Y12, Y51, Y52 | `revenue` / `revenue_subtype = insurance_trust` | | `X`/`Y`/`W`/`Z` | 14 codes | `category_type = balance` (pipeline#76) | Prefix `Y` now spans **all three** `category_type`s, which is F-018 made concrete in the crosswalk. Pipeline PRs #77 and #78. The fixture is regenerated at the same commit. --- ## Finding 1: `direct` must include insurance benefits, not just interest This issue's model says `direct = primary + interest`. Validated against Census and that is **incomplete**. The 2006 manual §5.2.2.1: > Direct expenditure comprises all final expenditures paid to current employees, **former employees (retirees)** and to private sector entities outside of the government itself (**e.g. all expenditure other than intergovernmental expenditure**). Social insurance trust is a *sector* (§5.3.4), not a character, so its benefit payments are direct expenditure. Reconciled numerically against Census's **own published state aggregates** (`20statetypepu.txt`, level 2 — independent of our corpus), FY2020: | | Wisconsin | California | |---|---:|---:| | codes matching Census exactly | 168/176 | 166/174 | | Census expenditure-code dollar coverage | **100.0000%** | **100.0000%** | | primary | $26.97B | $231.02B | | + interest | $27.56B (+2.17%) | $236.37B (+2.32%) | | + interest + insurance_benefits | $28.61B (+6.09%) | $261.63B (+13.25%) | | total (= Census's published expenditure sum) | **$40,460,441k** ✓ | **$375,553,177k** ✓ | `total` reproduces Census's published sum **to the dollar** for both states, and the crosswalk covers 100% of their published expenditure codes — so "all expenditure except intergovernmental" is exactly `primary + interest + insurance_benefits`. Every one of the 8 mismatches is a retirement-holdings code (`Z01`, `X01`, `X30`, `X40`, `X50`, `X71`, `X80`), i.e. the documented "X family ends FY2016" gap, not a flow discrepancy. **Omitting insurance benefits would understate California's Direct Expenditure by 10.9% — larger than the 7.1% error F-012 was filed for.** Owner confirmed 2026-07-30. This does not change this issue's fixture assertion: Madison is a city and carries no `Y` rows, so `direct = primary + I89` still holds there. ## Finding 2: DoD item 4's `Y01`-in-default assertion conflicts with #12 DoD item 4 and [the test at line 73](tests/testthat/test-expenditure-concepts.R) require `Y01` in the **default** `cog_revenue()` output. #12 requires the default to be `general` = $31,338,293,000 for WI FY2012, with insurance trust reachable only via `revenue_concept = "total"`. Measured on the regenerated fixture, WI FY2012: ``` general (revenue minus insurance_trust) = 31,338,293,000 <- matches #12 exactly Y insurance-trust revenue (Y01, Y11) = 1,259,785 k X retirement revenue (X01, X05, X08) = 2,038,800 k ``` `Y01` (unemployment contributions) and `X01` (retirement contributions) are both Insurance Trust Revenue. A default holding one but not the other matches **no Census concept**. Manual §4.3: > General revenue comprises all revenue except that classified as liquor store, utility, or **insurance trust** revenue. **Resolution (mirroring Census, per the owner):** the default stays `general`, and DoD item 4's `Y01` proof moves off the default call — assert it via the crosswalk / an explicit concept argument instead. The F-018 point is that `Y01` classifies as *revenue* while `Y05` classifies as *expenditure* from one mechanism; that is fully provable without requiring `Y01` in the default result, and it is now visible directly in `summary_categories`. ## Still to do Phase 2 (reader) only: rewrite `inst/sql/20-spending_long.sql` off `LEFT(item_code,1) IN ('E','F','G')` onto a join to `summary_categories`, add `expenditure_concept = c("primary","direct","total")` with `primary` default, and delete the `skip()`.
jared closed this issue 2026-07-30 22:09:45 -04:00
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
kodor
Kodor should process this issue
 kodor/feature-proposal
Kodor has written a feature proposal
 kodor/fix
Kodor should implement a fix (assigned to Kodor)
 kodor/needs-review
Kodor's work or failure needs Jared's review
 kodor/triaged
Kodor has already triaged this issue (skip)
 madison-walkthrough
severity/high
severity/low
severity/medium
south-guide
Surfaced while building the client-facing Southern API guide
 verdict/defect
verdict/definitional
needs
human
Cannot move without a person -- a decision, a check an agent cannot make, something outside the repo
origin
client
Came from a client ask
origin
obligation
Created by a change elsewhere
origin
review
Came from human review
origin
roborev
Promoted from a roborev finding
type
chore
Maintenance with no behaviour change
type
debt
Owed work -- docs, tests, cleanup a change obligated
type
decision
Needs a decision before work can proceed
type
defect
Something is wrong
type
feature
New capability
ws
api
Query verbs and results
ws
corpus
Corpus, mirror, provenance
ws
docs
Vignettes and guides
kodor
Assign a task to kodor
 kodor/feature-proposal
Kodor thinks this needs a feature.
 kodor/fix
Kodor should fix this
 kodor/needs-review
Kodor thinks the user is ready to review this.
 kodor/triaged
Kodor is done with this issue.
No labels madison-walkthrough severity/high verdict/defect verdict/definitional
Milestone
No items
No Milestone
Projects
Clear projects
No projects
Assignees
Clear assignees
hannah (Hannah Miller) jared (Jared Knowles) kodor
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: Civilytics/uscogdata#11