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

cog_revenue() has no revenue concept and permanently excludes Insurance Trust revenue (prefix X/Y) #12

New Issue
Closed
opened 2026-07-29 00:05:28 -04:00 by jared · 1 comment
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). Verdict: definitional.

Root cause

cog_revenue()'s flow_prefixes = c("T", "A", "U", "B", "C", "D") (R/revenue.R:23, via the revenue_long view) never returns item-code prefix X (Employee Retirement) or Y (other Insurance Trust). Per Census's standard accounting identity, Total Revenue = General Revenue + Utility Revenue + Liquor Store Revenue + Insurance Trust Revenue, and Employee Retirement System contributions/earnings are the Insurance Trust Revenue component — so prefix X sits inside a published Census revenue concept the same way I89 sits inside Census's Direct Expenditure concept.

Filed separately from the expenditure-concept issue for one reason: unlike cog_spending(), cog_revenue() has no argument that names a Census concept — no revenue_concept mirroring expenditure_concept. Its documentation promises "revenue by category," not a named Census total, so there is no explicit contradiction between a documented claim and the corpus's broader concept. Closing this therefore needs a design decision the owner has not yet made (the settled three-concept resolution covers expenditure only), which is why it is not folded into that work.

Findings resolved

Finding Severity Summary
F-014 medium cog_revenue() permanently excludes item-code prefix X (Employee Retirement contributions and earnings), which is real revenue for Madison FY1970-FY1986 and part of Census's Total Revenue concept

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

con <- uscogdata:::.ensure_session()

# Raw `long` table, Madison FY2022, every item-code prefix -- NOT through
# cog_revenue(), which has already filtered by the time it returns rows.
DBI::dbGetQuery(con, "
  SELECT LEFT(item_code,1) AS prefix, COUNT(*) n, SUM(amt) amt_thousands
  FROM long WHERE canonical_govid = '552025209777' AND year = 2022
    AND NOT is_aggregate GROUP BY prefix ORDER BY prefix
")
#> 13 prefixes: 1,2,3,4 (Debt), A,B,C,D,T,U (Revenue, all covered by
#> flow_prefixes), E,F (spending), I (interest). No X or Y prefix present
#> for Madison FY2022 -- so for this one government-year, no gap.

# Full 1967-2023 history for prefixes X and Y:
xy_hist <- DBI::dbGetQuery(con, "
  SELECT year, item_code, amt FROM long
  WHERE canonical_govid = '552025209777' AND LEFT(item_code,1) IN ('X','Y')
    AND NOT is_aggregate AND amt != 0 ORDER BY year, item_code
")
#> Y: zero rows, every year -- no gap.
#> X01/X04/X05/X08 (revenue-shaped: contributions + investment earnings):
#>   nonzero every year 1970-1986, total $15,098,000; $0/absent 1987-2023.
#> X11 (benefit payments, expenditure) and X21/X30/X42/X44/X47 (cash and
#>   securities, balance-sheet stock) excluded from that revenue-shaped sum.

Why it matters

Materiality for Madison specifically is modest — roughly 1-2% of revenue in its highest years, zero after 1986 — and this document's own FY2022 checkpoint is unaffected. It is named because it is the same class of gap as the interest exclusion in cog_spending(): a published Census revenue concept that this package's public interface cannot reach at all. Anyone reconciling this corpus against Census's published Total Revenue for a state government or a large retirement-system-operating city will be short by the entire Insurance Trust Revenue component with no signal in the return value.

A second, independent gap compounds it: summary_categories has zero rows for prefix X or Y at all, so relaxing flow_prefixes alone would produce rows with category = NA rather than a categorised result — the same fix the pipeline crosswalk-coverage issue already needs for a different code family.

Definition of done

  1. A design decision on a revenue_concept parameter mirroring the settled expenditure_concept model — plausibly general (today's behaviour) vs. total (adding Insurance Trust and any other component of Census's Total Revenue identity). Whatever the names, today's behaviour stays reachable and stays the default unless the owner rules otherwise.
  2. Classification driven by the crosswalk's spend_type column, not first-letter prefixes — X11/X12 (benefit payments, an expenditure) and X21/X30/X42/X44/X47 (cash and securities, a balance-sheet stock) must not be swept in with X01/X04/X05/X08, and prefix Y splits revenue (Y01/Y02) from expenditure (Y05/Y06) under one letter. Same root cause as the expenditure-concept issue.
  3. Until then, cog_revenue()'s documentation states that it returns General/Utility/Liquor-Store revenue only and never Insurance Trust revenue, so the returned figure is not Census's Total Revenue.
  4. Test goes green: tests/testthat/test-revenue-concept-insurance-trust.R → test_that("cog_revenue() can return Census Total Revenue including Insurance Trust (prefix X)", ...). Against the bundled fixture it asserts Wisconsin state FY2012 revenue under the new concept equals $33,377,093,000 — today's $31,338,293,000 plus X01 (615,835) + X05 (560,382) + X08 (862,583) thousands, each read from the raw corpus rather than through the verb under test — and that X11/X21/X30/X47 are not included. Remove the skip() on line 1 of the test body to activate.

(Madison's own FY1970-FY1986 X-prefix revenue, $15,098,000 nominal, is outside the fixture's year window — 2011/2012/2019/2020 — so the test asserts the same invariant on Wisconsin state FY2012, where the fixture carries nonzero X01/X05/X08. Recorded in the test file as a comment.)

Cross-references

  • Same architectural root cause as the three-concept expenditure issue (findings F-012, F-017, F-018) — resolve the spend_type classification there first.
  • The summary_categories gap for prefixes X/Y belongs to census_of_governments_finance_pipeline — see the crosswalk-coverage issue there.

Severity: medium. Verdict: definitional.

Filed from the **Madison walkthrough audit** (`cog_explorer/docs/walkthroughs/FINDINGS.md`, 2026-07-28). Verdict: **definitional**. ## Root cause `cog_revenue()`'s `flow_prefixes = c("T", "A", "U", "B", "C", "D")` (`R/revenue.R:23`, via the `revenue_long` view) never returns item-code prefix `X` (Employee Retirement) or `Y` (other Insurance Trust). Per Census's standard accounting identity, **Total Revenue = General Revenue + Utility Revenue + Liquor Store Revenue + Insurance Trust Revenue**, and Employee Retirement System contributions/earnings are the Insurance Trust Revenue component — so prefix `X` sits inside a published Census revenue concept the same way `I89` sits inside Census's Direct Expenditure concept. Filed separately from the expenditure-concept issue for one reason: unlike `cog_spending()`, `cog_revenue()` has **no argument that names a Census concept** — no `revenue_concept` mirroring `expenditure_concept`. Its documentation promises "revenue by category," not a named Census total, so there is no explicit contradiction between a documented claim and the corpus's broader concept. Closing this therefore needs a design decision the owner has **not** yet made (the settled three-concept resolution covers expenditure only), which is why it is not folded into that work. ## Findings resolved | Finding | Severity | Summary | |---|---|---| | **F-014** | medium | `cog_revenue()` permanently excludes item-code prefix `X` (Employee Retirement contributions and earnings), which is real revenue for Madison FY1970-FY1986 and part of Census's Total Revenue concept | ## Reproduction (verbatim from FINDINGS.md, verified against the live corpus) ```r con <- uscogdata:::.ensure_session() # Raw `long` table, Madison FY2022, every item-code prefix -- NOT through # cog_revenue(), which has already filtered by the time it returns rows. DBI::dbGetQuery(con, " SELECT LEFT(item_code,1) AS prefix, COUNT(*) n, SUM(amt) amt_thousands FROM long WHERE canonical_govid = '552025209777' AND year = 2022 AND NOT is_aggregate GROUP BY prefix ORDER BY prefix ") #> 13 prefixes: 1,2,3,4 (Debt), A,B,C,D,T,U (Revenue, all covered by #> flow_prefixes), E,F (spending), I (interest). No X or Y prefix present #> for Madison FY2022 -- so for this one government-year, no gap. # Full 1967-2023 history for prefixes X and Y: xy_hist <- DBI::dbGetQuery(con, " SELECT year, item_code, amt FROM long WHERE canonical_govid = '552025209777' AND LEFT(item_code,1) IN ('X','Y') AND NOT is_aggregate AND amt != 0 ORDER BY year, item_code ") #> Y: zero rows, every year -- no gap. #> X01/X04/X05/X08 (revenue-shaped: contributions + investment earnings): #> nonzero every year 1970-1986, total $15,098,000; $0/absent 1987-2023. #> X11 (benefit payments, expenditure) and X21/X30/X42/X44/X47 (cash and #> securities, balance-sheet stock) excluded from that revenue-shaped sum. ``` ## Why it matters Materiality for Madison specifically is modest — roughly 1-2% of revenue in its highest years, zero after 1986 — and this document's own FY2022 checkpoint is unaffected. It is named because it is the **same class of gap** as the interest exclusion in `cog_spending()`: a published Census revenue concept that this package's public interface cannot reach at all. Anyone reconciling this corpus against Census's published Total Revenue for a state government or a large retirement-system-operating city will be short by the entire Insurance Trust Revenue component with no signal in the return value. A second, independent gap compounds it: **`summary_categories` has zero rows for prefix `X` or `Y` at all**, so relaxing `flow_prefixes` alone would produce rows with `category = NA` rather than a categorised result — the same fix the pipeline crosswalk-coverage issue already needs for a different code family. ## Definition of done 1. A design decision on a `revenue_concept` parameter mirroring the settled `expenditure_concept` model — plausibly `general` (today's behaviour) vs. `total` (adding Insurance Trust and any other component of Census's Total Revenue identity). Whatever the names, today's behaviour stays reachable and stays the default unless the owner rules otherwise. 2. Classification driven by the crosswalk's **`spend_type`** column, not first-letter prefixes — `X11`/`X12` (benefit payments, an expenditure) and `X21`/`X30`/`X42`/`X44`/`X47` (cash and securities, a balance-sheet stock) must not be swept in with `X01`/`X04`/`X05`/`X08`, and prefix `Y` splits revenue (`Y01`/`Y02`) from expenditure (`Y05`/`Y06`) under one letter. Same root cause as the expenditure-concept issue. 3. Until then, `cog_revenue()`'s documentation states that it returns General/Utility/Liquor-Store revenue only and never Insurance Trust revenue, so the returned figure is not Census's Total Revenue. 4. Test goes green: **`tests/testthat/test-revenue-concept-insurance-trust.R`** → `test_that("cog_revenue() can return Census Total Revenue including Insurance Trust (prefix X)", ...)`. Against the bundled fixture it asserts Wisconsin state FY2012 revenue under the new concept equals **$33,377,093,000** — today's `$31,338,293,000` plus `X01` (615,835) + `X05` (560,382) + `X08` (862,583) thousands, each read from the raw corpus rather than through the verb under test — and that `X11`/`X21`/`X30`/`X47` are **not** included. Remove the `skip()` on line 1 of the test body to activate. *(Madison's own FY1970-FY1986 X-prefix revenue, $15,098,000 nominal, is outside the fixture's year window — 2011/2012/2019/2020 — so the test asserts the same invariant on Wisconsin state FY2012, where the fixture carries nonzero X01/X05/X08. Recorded in the test file as a comment.)* ## Cross-references * Same architectural root cause as the three-concept expenditure issue (findings F-012, F-017, F-018) — resolve the `spend_type` classification there first. * The `summary_categories` gap for prefixes `X`/`Y` belongs to `census_of_governments_finance_pipeline` — see the crosswalk-coverage issue there. **Severity: medium. Verdict: definitional.**
jared added the verdict/definitionalmadison-walkthroughseverity/medium labels 2026-07-29 00:05:28 -04:00
jared commented 2026-07-30 16:12:00 -04:00
Author
Owner
Copy Link
Copy Source

Two findings from the pipeline crosswalk work published 2026-07-30 (pipeline_commit e64a046), both of which change this issue's numbers.

1. The crosswalk now carves out insurance trust for you

summary_categories gained revenue_subtype = "insurance_trust" for the Y revenue codes (Y01, Y02, Y04, Y11, Y12, Y51, Y52), plus category_type = "balance" for the holdings codes (pipeline#76/#78). So the general-vs-total split no longer needs a prefix rule at all.

Measured on the regenerated fixture, Wisconsin state FY2012:

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

Filtering revenue_subtype != 'insurance_trust' reproduces this issue's general figure to the dollar, which is good evidence the subtype boundary is drawn where Census draws it.

Note the balance codes are now separable too, which is what line 49's assertion needs: X21/X30/X47 are category_type = 'balance', and X11/X12 are benefit payments. None of them can leak into a revenue concept once classification keys on the crosswalk instead of the letter X.

2. This issue's expected total is now incomplete

Line 41 asserts total = 33,377,093,000, which is general + X-retirement (2,038,800k) only. That was correct when written, because prefix Y had no crosswalk rows and so wasn't visible as revenue. It now is.

A complete Census Total Revenue for WI FY2012 is:

31,338,293  general
+ 2,038,800  X  employee-retirement insurance trust
+ 1,259,785  Y  unemployment + workers-comp insurance trust
= 34,636,878  ($1,000s)  ->  34,636,878,000

So both the total expectation and the delta on line 40 need updating when this is implemented.

3. Caveat: today's default is not strictly Census General Revenue

Manual §4.3: "General revenue comprises all revenue except that classified as liquor store, utility, or insurance trust revenue."

Today's flow_prefixes = c("T","A","U","B","C","D") admits A90 (liquor store) and A91–A94 (utility current charges), so the current default is nearer General + Utility + Liquor Store than Census's General Revenue. It happens not to matter for the Wisconsin state example above — WI state carries $0 for A90–A94 in FY2012, which is why this issue's 31,338,293,000 baseline is unaffected — but it will matter for cities, where utility charges are often material.

Worth deciding explicitly whether general means Census's General Revenue (excluding utility and liquor store) or "everything that isn't insurance trust". The crosswalk supports either: utility codes carry the Water/Electric/Gas/Transit Utilities categories, and A90 is Liquor Stores.

4. The naming caveat in the test header is now half-resolved

The owner ruled on 2026-07-30 (see uscogdata#11) that the default stays general — insurance trust opt-in, not opt-out — on the grounds that it mirrors Census's published concepts and moves no existing caller's numbers. The argument name (revenue_concept) is still unruled.

This also resolved a conflict with uscogdata#11, whose DoD item 4 required Y01 in the default cog_revenue() output. A default holding Y01 but not X01 matches no Census concept, so #11's proof moves off the default call instead.

Two findings from the pipeline crosswalk work published 2026-07-30 (`pipeline_commit e64a046`), both of which change this issue's numbers. ## 1. The crosswalk now carves out insurance trust for you `summary_categories` gained `revenue_subtype = "insurance_trust"` for the `Y` revenue codes (`Y01`, `Y02`, `Y04`, `Y11`, `Y12`, `Y51`, `Y52`), plus `category_type = "balance"` for the holdings codes (pipeline#76/#78). So the general-vs-total split no longer needs a prefix rule at all. Measured on the regenerated fixture, Wisconsin state FY2012: ``` general (revenue minus insurance_trust) = 31,338,293,000 <- this issue's baseline, exactly Y insurance-trust revenue (Y01, Y11) = 1,259,785 k X retirement revenue (X01, X05, X08) = 2,038,800 k ``` Filtering `revenue_subtype != 'insurance_trust'` reproduces this issue's `general` figure **to the dollar**, which is good evidence the subtype boundary is drawn where Census draws it. Note the balance codes are now separable too, which is what line 49's assertion needs: `X21`/`X30`/`X47` are `category_type = 'balance'`, and `X11`/`X12` are benefit payments. None of them can leak into a revenue concept once classification keys on the crosswalk instead of the letter `X`. ## 2. This issue's expected `total` is now incomplete Line 41 asserts `total = 33,377,093,000`, which is `general + X-retirement (2,038,800k)` only. That was correct when written, because prefix `Y` had **no** crosswalk rows and so wasn't visible as revenue. It now is. A complete Census Total Revenue for WI FY2012 is: ``` 31,338,293 general + 2,038,800 X employee-retirement insurance trust + 1,259,785 Y unemployment + workers-comp insurance trust = 34,636,878 ($1,000s) -> 34,636,878,000 ``` So both the `total` expectation and the delta on line 40 need updating when this is implemented. ## 3. Caveat: today's default is not strictly Census General Revenue Manual §4.3: *"General revenue comprises all revenue except that classified as **liquor store, utility, or insurance trust** revenue."* Today's `flow_prefixes = c("T","A","U","B","C","D")` admits `A90` (liquor store) and `A91`–`A94` (utility current charges), so the current default is nearer *General + Utility + Liquor Store* than Census's General Revenue. It happens not to matter for the Wisconsin state example above — WI state carries `$0` for `A90`–`A94` in FY2012, which is why this issue's `31,338,293,000` baseline is unaffected — but it will matter for cities, where utility charges are often material. Worth deciding explicitly whether `general` means Census's General Revenue (excluding utility and liquor store) or "everything that isn't insurance trust". The crosswalk supports either: utility codes carry the `Water/Electric/Gas/Transit Utilities` categories, and `A90` is `Liquor Stores`. ## 4. The naming caveat in the test header is now half-resolved The owner ruled on 2026-07-30 (see uscogdata#11) that the **default stays `general`** — insurance trust opt-in, not opt-out — on the grounds that it mirrors Census's published concepts and moves no existing caller's numbers. The argument *name* (`revenue_concept`) is still unruled. This also resolved a conflict with uscogdata#11, whose DoD item 4 required `Y01` in the **default** `cog_revenue()` output. A default holding `Y01` but not `X01` matches no Census concept, so #11's proof moves off the default call instead.
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/medium 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#12