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

Public Welfare understated 32-38% in legacy years with no coverage-gap signpost #9

New Issue
Closed
opened 2026-07-25 19:45:24 -04:00 by jared · 3 comments
jared commented 2026-07-25 19:45:24 -04:00
Owner
Copy Link
Copy Source

Found while scoping issue #6; filed separately so the expenditure-concept work stays purely additive.

The defect. cog_spending(category = "Public Welfare") is understated 32-38% in every legacy year (<=FY2011), and no signpost fires.

E67/E68 (welfare cash assistance) are is_aggregate = TRUE in the wide era — the same deliberate class as E05, since the wide files publish those families only as aggregates. spending_long filters AND NOT is_aggregate, so they are dropped.

National measurement (publish_cache, pipeline_commit 05b1fff; $1,000s):

year dropped (E67+E68) reported (E79+F79+G79) understatement
2000 20,426,962 53,035,446 38.5%
2005 21,332,098 63,386,647 33.7%
2011 23,468,547 73,116,462 32.1%

Why this is worse than the Corrections case. Corrections behaves correctly: E05/F05/G05 are also aggregate-flagged, the category returns no rows at all for legacy years, the coverage-gap machinery fires and names the fix —

i Coverage gap detected for the requested years; a harmonization recipe may fill it:
* corrections_combined (1967-2023): re-run with recipe = 'corrections_combined'

— and the recipe then returns a continuous 2005->2019 series. That is the system working as designed.

Public Welfare is the failure mode: E74/E75/E77/E79 still return rows, so there is no row-absence gap to detect. Verified: attr(r, "provenance")$suggestions is list() — empty — even though welfare_cash_e67_wide and welfare_cash_e68_wide exist and would fill it. The user gets a plausible number that is a third too low, silently.

Root cause. The signposting trigger is row-absence; the hazard here is component-absence inside a category that still has rows.

Suggested fix. Fire a suggestion when any recipe's components overlap the requested category and are aggregate-suppressed in the requested years.

Reproduce:

Sys.setenv(USCOGDATA_URL = "<publish_cache path>")
r <- cog_spending("010000226085", years = c(2000, 2005, 2011), category = "Public Welfare")
attr(r, "provenance")$suggestions   # list() -- nothing fires

Minor, same area: aggregate_fallback in cog_spending() output is vestigial. spending_long pre-filters NOT is_aggregate, so bool_and(is_aggregate) is structurally always FALSE (verified 10/10 rows) and the "Aggregate fallback applied; see cog_explain()" note in .notes_column() can never fire. Safe to remove alongside.

Found while scoping issue #6; filed separately so the expenditure-concept work stays purely additive. **The defect.** `cog_spending(category = "Public Welfare")` is understated **32-38%** in every legacy year (<=FY2011), and **no signpost fires**. `E67`/`E68` (welfare cash assistance) are `is_aggregate = TRUE` in the wide era — the same deliberate class as `E05`, since the wide files publish those families only as aggregates. `spending_long` filters `AND NOT is_aggregate`, so they are dropped. National measurement (publish_cache, pipeline_commit 05b1fff; $1,000s): | year | dropped (E67+E68) | reported (E79+F79+G79) | understatement | |---|---:|---:|---:| | 2000 | 20,426,962 | 53,035,446 | **38.5%** | | 2005 | 21,332,098 | 63,386,647 | **33.7%** | | 2011 | 23,468,547 | 73,116,462 | **32.1%** | **Why this is worse than the Corrections case.** Corrections behaves *correctly*: `E05`/`F05`/`G05` are also aggregate-flagged, the category returns **no rows at all** for legacy years, the coverage-gap machinery fires and names the fix — ``` i Coverage gap detected for the requested years; a harmonization recipe may fill it: * corrections_combined (1967-2023): re-run with recipe = 'corrections_combined' ``` — and the recipe then returns a continuous 2005->2019 series. That is the system working as designed. Public Welfare is the failure mode: `E74/E75/E77/E79` still return rows, so there is **no row-absence gap to detect**. Verified: `attr(r, "provenance")$suggestions` is `list()` — empty — even though `welfare_cash_e67_wide` and `welfare_cash_e68_wide` exist and would fill it. The user gets a plausible number that is a third too low, silently. **Root cause.** The signposting trigger is *row-absence*; the hazard here is *component-absence inside a category that still has rows*. **Suggested fix.** Fire a suggestion when any recipe's components overlap the requested category and are aggregate-suppressed in the requested years. **Reproduce:** ```r Sys.setenv(USCOGDATA_URL = "<publish_cache path>") r <- cog_spending("010000226085", years = c(2000, 2005, 2011), category = "Public Welfare") attr(r, "provenance")$suggestions # list() -- nothing fires ``` --- **Minor, same area:** `aggregate_fallback` in `cog_spending()` output is vestigial. `spending_long` pre-filters `NOT is_aggregate`, so `bool_and(is_aggregate)` is structurally always `FALSE` (verified 10/10 rows) and the "Aggregate fallback applied; see cog_explain()" note in `.notes_column()` can never fire. Safe to remove alongside.
jared commented 2026-07-29 00:06:38 -04:00
Author
Owner
Copy Link
Copy Source

Cross-reference from the Madison walkthrough audit (cog_explorer/docs/walkthroughs/FINDINGS.md, 2026-07-28), which hit an adjacent Public Welfare problem and filed it in census_of_governments_finance_pipeline#61 rather than here — recording the relationship so whoever picks either one reads both.

This issue is the ≤FY2011 window: E67/E68 are is_aggregate = TRUE in the wide era, spending_long filters them out, and the category still returns rows so no coverage-gap suggestion fires. Understatement 32-38%.

Finding F-007 is a different window and a different mechanism: for Madison, category == "Public Welfare" has no rows at all — not $0 rows, no rows — for FY2012 through FY2021, then reappears in FY2022 ($17,965,000) and FY2023 ($48,996,000) under item code E79 alone, against a pre-2012 all-time peak across any subtype of $3.9M.

mad_all |> dplyr::filter(category == "Public Welfare") |>
  dplyr::arrange(year) |>
  dplyr::select(year, spend_subtype, amt_nominal, codes_included)
# 1967-1986: real, noisy (peak $3,882,000, operations, 1986), codes
#   E74/E75/E77/E79 (op) + F77/F79/G77/G79 (cap)
# 1988-2011: operations = $0 every year present; no rows 2012-2021
# 2022: operations = $17,965,000, codes_included = "E79" (alone)
# 2023: operations = $48,996,000, codes_included = "E79" (alone)

The FY2022 four-code-to-one-code narrowing is already explained corpus-side: cog_explain() on that query surfaces SB171 ("FY2022 absorbs E74+E75 and J67+J68 per 2022 State Govt Finances Technical Documentation pp.4-5"), which is the coverage machinery working exactly as designed and is why F-007 is rated definitional. What is not catalogued is the FY2012–FY2021 absence itself, so that is the part tracked in pipeline#61.

Two consequences worth noting for the work in this issue:

  1. Anyone rebuilding a continuous Public Welfare series needs both halves — the aggregate-suppressed E67/E68 for ≤FY2011 (this issue) and the FY2012–FY2021 row absence plus the FY2022 SB171 pivot (pipeline#61). SB171's own advice is to compare FY2022 against E79+E74+E75+J67+J68 for pre-2022 years, which is the same bundle this issue's recipe work touches.
  2. The suggested fix here — "fire a suggestion when any recipe's components overlap the requested category and are aggregate-suppressed in the requested years" — would not fire for F-007's window, because there the components are absent rather than suppressed. If the trigger is being generalised anyway, "component absent for the requested years" is worth covering in the same pass.

Also related: census_of_governments_finance_pipeline#58 (J-prefix crosswalk gap) names J67/J68 as the modern successors in this same family.

No change requested here — filed as context only.

Cross-reference from the **Madison walkthrough audit** (`cog_explorer/docs/walkthroughs/FINDINGS.md`, 2026-07-28), which hit an adjacent Public Welfare problem and filed it in `census_of_governments_finance_pipeline#61` rather than here — recording the relationship so whoever picks either one reads both. **This issue** is the ≤FY2011 window: `E67`/`E68` are `is_aggregate = TRUE` in the wide era, `spending_long` filters them out, and the category still returns rows so no coverage-gap suggestion fires. Understatement 32-38%. **Finding F-007** is a different window and a different mechanism: for Madison, `category == "Public Welfare"` has **no rows at all** — not $0 rows, no rows — for FY2012 through FY2021, then reappears in FY2022 ($17,965,000) and FY2023 ($48,996,000) under item code `E79` alone, against a pre-2012 all-time peak across any subtype of $3.9M. ```r mad_all |> dplyr::filter(category == "Public Welfare") |> dplyr::arrange(year) |> dplyr::select(year, spend_subtype, amt_nominal, codes_included) # 1967-1986: real, noisy (peak $3,882,000, operations, 1986), codes # E74/E75/E77/E79 (op) + F77/F79/G77/G79 (cap) # 1988-2011: operations = $0 every year present; no rows 2012-2021 # 2022: operations = $17,965,000, codes_included = "E79" (alone) # 2023: operations = $48,996,000, codes_included = "E79" (alone) ``` The FY2022 four-code-to-one-code narrowing is already explained corpus-side: `cog_explain()` on that query surfaces `SB171` ("FY2022 absorbs E74+E75 and J67+J68 per 2022 State Govt Finances Technical Documentation pp.4-5"), which is the coverage machinery working exactly as designed and is why F-007 is rated `definitional`. What is **not** catalogued is the FY2012–FY2021 absence itself, so that is the part tracked in pipeline#61. Two consequences worth noting for the work in this issue: 1. Anyone rebuilding a continuous Public Welfare series needs both halves — the aggregate-suppressed `E67`/`E68` for ≤FY2011 (this issue) and the FY2012–FY2021 row absence plus the FY2022 `SB171` pivot (pipeline#61). `SB171`'s own advice is to compare FY2022 against `E79+E74+E75+J67+J68` for pre-2022 years, which is the same bundle this issue's recipe work touches. 2. The suggested fix here — *"fire a suggestion when any recipe's components overlap the requested category and are aggregate-suppressed in the requested years"* — would not fire for F-007's window, because there the components are absent rather than suppressed. If the trigger is being generalised anyway, "component absent for the requested years" is worth covering in the same pass. Also related: `census_of_governments_finance_pipeline#58` (J-prefix crosswalk gap) names `J67`/`J68` as the modern successors in this same family. No change requested here — filed as context only.
jared commented 2026-07-30 22:25:19 -04:00
Author
Owner
Copy Link
Copy Source

Re-measured against the freshly published corpus (pipeline_commit aadb46b, 2026-07-31). The defect is real and unchanged in dollars, but the headline severity in the title is wrong by roughly 5x — it should read ~5-10%, not 32-38%.

Keeping this open, with a corrected scope.

What changed, and what did not

The dropped dollars are exactly as filed — E67/E68 are still is_aggregate = TRUE for 1967-2011 ($624.6B + $126.0B lifetime) and spending_long still filters NOT is_aggregate, so they are still absent:

year dropped (E67+E68) filed as reported now understated now filed as
2000 20,426,962 20,426,962 211,297,262 9.7% 38.5%
2005 21,332,098 21,332,098 338,734,473 6.3% 33.7%
2011 23,468,547 23,468,547 463,915,985 5.1% 32.1%

The numerator matches the original measurement to the dollar. The denominator is what moved. The original compared against E79+F79+G79 alone; since then the #58/#60 crosswalk batch mapped E74/E75/E77 and F77/G77 into Public Welfare, and uscogdata#11 admitted the J assistance codes (J67/J68, 2012+). Public Welfare is now a 15-code category, so the same missing dollars are a much smaller share of a much larger base.

The root cause is narrower than the issue implies

E67/E68 are not merely filtered out — they are not in summary_categories at all, so they belong to no category. That matters for the fix: simply mapping them would not work, because spending_long's NOT is_aggregate would still drop them. Under the crosswalk-membership architecture uscogdata#11 introduced, an aggregate-flagged code needs the recipe path, not a category row.

The real complaint still stands

The valuable part of this issue is unchanged and still true: no signpost fires. E74/E75/E77/E79 return rows, so there is no row-absence gap for .build_suggestions() to detect, and welfare_cash_e67_wide / welfare_cash_e68_wide are never surfaced even though they would fill it. The Corrections case works only because that category returns zero rows in legacy years.

So the fix is in the suggestion machinery, not the crosswalk: teach it to fire on partial coverage (a category whose recipe-reachable codes carry dollars the result does not), not just on empty years. That is a real design question — a naive version would fire on every category in every legacy year — and it is the thing worth scoping next.

Suggested retitle

Public Welfare understated ~5-10% in legacy years (E67/E68 aggregates) with no coverage-gap signpost

**Re-measured against the freshly published corpus (`pipeline_commit aadb46b`, 2026-07-31). The defect is real and unchanged in dollars, but the headline severity in the title is wrong by roughly 5x — it should read ~5-10%, not 32-38%.** Keeping this open, with a corrected scope. ## What changed, and what did not The **dropped dollars are exactly as filed** — `E67`/`E68` are still `is_aggregate = TRUE` for 1967-2011 ($624.6B + $126.0B lifetime) and `spending_long` still filters `NOT is_aggregate`, so they are still absent: | year | dropped (E67+E68) | filed as | reported **now** | understated **now** | filed as | |---|---:|---:|---:|---:|---:| | 2000 | 20,426,962 | 20,426,962 | 211,297,262 | **9.7%** | 38.5% | | 2005 | 21,332,098 | 21,332,098 | 338,734,473 | **6.3%** | 33.7% | | 2011 | 23,468,547 | 23,468,547 | 463,915,985 | **5.1%** | 32.1% | The numerator matches the original measurement to the dollar. **The denominator is what moved.** The original compared against `E79+F79+G79` alone; since then the #58/#60 crosswalk batch mapped `E74`/`E75`/`E77` and `F77`/`G77` into Public Welfare, and uscogdata#11 admitted the `J` assistance codes (`J67`/`J68`, 2012+). Public Welfare is now a 15-code category, so the same missing dollars are a much smaller share of a much larger base. ## The root cause is narrower than the issue implies `E67`/`E68` are not merely *filtered out* — they are **not in `summary_categories` at all**, so they belong to no category. That matters for the fix: simply mapping them would **not** work, because `spending_long`'s `NOT is_aggregate` would still drop them. Under the crosswalk-membership architecture uscogdata#11 introduced, an aggregate-flagged code needs the recipe path, not a category row. ## The real complaint still stands The valuable part of this issue is unchanged and still true: **no signpost fires.** `E74/E75/E77/E79` return rows, so there is no row-absence gap for `.build_suggestions()` to detect, and `welfare_cash_e67_wide` / `welfare_cash_e68_wide` are never surfaced even though they would fill it. The Corrections case works only because that category returns *zero* rows in legacy years. So the fix is in the **suggestion machinery**, not the crosswalk: teach it to fire on *partial* coverage (a category whose recipe-reachable codes carry dollars the result does not), not just on empty years. That is a real design question — a naive version would fire on every category in every legacy year — and it is the thing worth scoping next. ## Suggested retitle `Public Welfare understated ~5-10% in legacy years (E67/E68 aggregates) with no coverage-gap signpost`
jared closed this issue 2026-08-05 08:15:47 -04:00
jared commented 2026-08-05 08:32:11 -04:00
Author
Owner
Copy Link
Copy Source

Fixed in PR #32 (merged 2fc9e758). The trigger was the problem, not the crosswalk.

.build_suggestions() already identified welfare_cash_e67_wide and welfare_cash_e68_wide as candidates — J67/J68 are Public Welfare members, so the candidate query matched. It then threw them away at the gap_years early return, because E74/E79 returned rows and there was no empty year to detect.

A recipe now also qualifies when a component code carries dollars the verb's own long view structurally excludes — aggregate-published, or absent from summary_categories. Reachability is decided by anti-joining the real view rather than restating its WHERE clause, which is sound because no recipe component is ever renamed by harmonization (now asserted as a test; a reviewer separately verified the converse direction too — nothing harmonizes onto a recipe component).

Each suggestion carries trigger, suppressed_amount, suppressed_years, suppressed_codes. cog_revenue() inherits the fix through the shared verb path: Alaska FY2011 Miscellaneous Revenue reported $943,842,000 while dropping $1,899,995,000 of aggregate-published U4- rents and royalties — an omission larger than the reported figure.

On the "would fire on every category in every legacy year" worry — measured, it does not. Candidates stay gated on the 24-recipe catalog, so it only fires where an actionable recipe exists. higher_ed_e18_wide and general_gov_e89_wide never fire, because E18/E89 are ordinary classified leaves even pre-2012.

Three things the review caught that are worth recording

1. The first cut fabricated dollar claims across flow families. Candidates were filtered for M/L but never for flow family, so a component belonging to the other verb was always absent from this verb's view and got reported as suppressed — cog_revenue(category = "Corrections") claimed "$3,631,945,000 excluded (E04, E05)" for dollars fully reachable via cog_spending(). Fixed by scoping the measurement to the calling verb's own flow_prefixes. The deeper fix (scoping the candidate query by category_type, which would also stop the mis-scoped empty_year fire) is uscogdata#34.

2. The published schema described a different quantity than the code computes. suppressed_amount said "what the result excludes"; it measures what the long view excludes. Those differ three ways — it under-reports to zero (a component present in the view under a different category contributes 0), it over-reports (item 1), and it can be negative where Census publishes negative amounts. provenance-v1.json now says what the code actually does.

3. The anti-join defeated year-partition pruning. The NOT EXISTS side got no file filter and scanned every partition — on a 1967–2023 corpus read over HTTP. Restating the year/govid literals inside the subquery fixed it. A follow-on attempt to also skip the query entirely on healthy paths was measured, found net-negative (2.7% skip rate, 0% on the batch shape it targeted), and reverted; the real optimization is uscogdata#35.

The "minor" sub-item is withdrawn, not implemented

aggregate_fallback is no longer vestigial. .build_verb_sql() uses bool_or(is_aggregate) (R/spending.R), and ig_long deliberately keeps aggregate rows, so the flag is live for the intergovernmental leg — test-expenditure-concept.R asserts aggregate-sourced IG dollars report TRUE. Removing it would break that disclosure.

Reproduction, now signposted

r <- cog_spending("061037123085", years = 2011, category = "Public Welfare")
attr(r, "provenance")$suggestions[[1]]$suppressed_amount  # 1803872000

Suite 796 → 843. Also live on the API (cog-api#33).

Follow-ups: uscogdata#33 (decompose .build_suggestions()), #34 (category_type scoping), #35 (batch-aware suppression skip).

**Fixed in PR #32 (merged `2fc9e758`).** The trigger was the problem, not the crosswalk. `.build_suggestions()` already identified `welfare_cash_e67_wide` and `welfare_cash_e68_wide` as candidates — `J67`/`J68` are Public Welfare members, so the candidate query matched. It then threw them away at the `gap_years` early return, because E74/E79 returned rows and there was no empty year to detect. A recipe now also qualifies when a component code carries dollars the verb's own long view **structurally excludes** — aggregate-published, or absent from `summary_categories`. Reachability is decided by anti-joining the real view rather than restating its WHERE clause, which is sound because no recipe component is ever renamed by harmonization (now asserted as a test; a reviewer separately verified the converse direction too — nothing harmonizes *onto* a recipe component). Each suggestion carries `trigger`, `suppressed_amount`, `suppressed_years`, `suppressed_codes`. `cog_revenue()` inherits the fix through the shared verb path: Alaska FY2011 `Miscellaneous Revenue` reported $943,842,000 while dropping **$1,899,995,000** of aggregate-published `U4-` rents and royalties — an omission *larger than the reported figure*. **On the "would fire on every category in every legacy year" worry** — measured, it does not. Candidates stay gated on the 24-recipe catalog, so it only fires where an actionable recipe exists. `higher_ed_e18_wide` and `general_gov_e89_wide` never fire, because E18/E89 are ordinary classified leaves even pre-2012. ## Three things the review caught that are worth recording **1. The first cut fabricated dollar claims across flow families.** Candidates were filtered for M/L but never for flow family, so a component belonging to the *other* verb was always absent from this verb's view and got reported as suppressed — `cog_revenue(category = "Corrections")` claimed "$3,631,945,000 excluded (E04, E05)" for dollars fully reachable via `cog_spending()`. Fixed by scoping the measurement to the calling verb's own `flow_prefixes`. The deeper fix (scoping the *candidate* query by `category_type`, which would also stop the mis-scoped `empty_year` fire) is uscogdata#34. **2. The published schema described a different quantity than the code computes.** `suppressed_amount` said "what the result excludes"; it measures what the *long view* excludes. Those differ three ways — it under-reports to zero (a component present in the view under a different category contributes 0), it over-reports (item 1), and **it can be negative** where Census publishes negative amounts. `provenance-v1.json` now says what the code actually does. **3. The anti-join defeated year-partition pruning.** The `NOT EXISTS` side got no file filter and scanned every partition — on a 1967–2023 corpus read over HTTP. Restating the year/govid literals inside the subquery fixed it. A follow-on attempt to also skip the query entirely on healthy paths was measured, found net-negative (2.7% skip rate, 0% on the batch shape it targeted), and reverted; the real optimization is uscogdata#35. ## The "minor" sub-item is withdrawn, not implemented `aggregate_fallback` is no longer vestigial. `.build_verb_sql()` uses `bool_or(is_aggregate)` (`R/spending.R`), and `ig_long` deliberately keeps aggregate rows, so the flag is live for the intergovernmental leg — `test-expenditure-concept.R` asserts aggregate-sourced IG dollars report TRUE. Removing it would break that disclosure. ## Reproduction, now signposted ```r r <- cog_spending("061037123085", years = 2011, category = "Public Welfare") attr(r, "provenance")$suggestions[[1]]$suppressed_amount # 1803872000 ``` Suite 796 → 843. Also live on the API (cog-api#33). Follow-ups: uscogdata#33 (decompose `.build_suggestions()`), #34 (`category_type` scoping), #35 (batch-aware suppression skip).
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
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#9