Expose cash and security holdings (category_type = balance), and keep them out of the money verbs #25

New Issue

2026-07-30T14:48:49-04:00

jared commented

2026-07-30 14:48:49 -04:00

census_of_governments_finance_pipeline#76 (PR #77) adds a third
category_type to summary_categories.csv — balance — covering the 14
cash-and-security holding codes, with a balance_subtype column. The reader
has no way to reach them and no guard against them leaking into money verbs.

Blocked on: the corpus rebuild + republish that ships pipeline PR #77.
Nothing here is testable until summary_categories.parquet carries the new
column.

Two requirements

1. Neither money verb may admit a `balance` row

cog_spending() and cog_revenue() must never return one. These are stocks
— a balance at a point in time — while the money verbs return flows over a
fiscal year. Summing a stock with a flow is meaningless, and worse, a
balance row silently included in a spending total looks plausible rather than
obviously wrong.

This should be an explicit filter on category_type, not an incidental
consequence of prefix matching. The pipeline guards its own artifacts with a
single .drop_balance() predicate for exactly this reason; the reader wants
the same shape.

Note this interacts with #11 and #12: whatever classifies codes into
expenditure/revenue concepts must treat balance as a third outcome rather
than forcing every code into one of two buckets.

2. A way to query holdings

Design TBD. The two obvious shapes:

a new verb, cog_balances(), parallel to cog_spending() / cog_revenue();
or an argument on an existing generic.

A verb is probably right given the return semantics genuinely differ (no
amt_per_capita interpretation as "spending per person"; year-over-year change
is portfolio movement, not budget growth), but that is an owner call.

Whatever the shape, balance_subtype == "general" (W01/W31/W61) must be
reachable in one filter — that is the family users mean by "fund balance".

Caveats the reader must surface, not bury

These are in docs/data_dictionary.md § Cash and security holdings and should
reach the user, because each one silently invalidates an obvious analysis:

Census holdings are NOT GAAP fund balance. Gross holdings, no
liabilities netted. A reserve ratio built from them overstates what is
actually available.
W is FY2012–2021 only — ten years, stopping two short of the corpus.
A long fund-balance-share-of-revenue series is not available.
The X family ends at FY2016, when Census moved employee retirement to
a separate survey.
X40/X41 change valuation basis mid-series at FY2002 (book → market)
while keeping the same code through FY2011 — catalogued as SB195/SB196.
Undetectable from the series alone.

The subtype map

`balance_subtype`	codes	corpus years
`general`	`W01`, `W31`, `W61`	2012–2021
`employee_retirement`	`X21`, `X30`, `X42`, `X44`, `X47`, `Z77`, `Z78`	1967–2016 (varies)
`unemployment_trust`	`Y07`, `Y08`	1967–2023
`workers_comp_trust`	`Y21`	2012–2023
`other_insurance_trust`	`Y61`	2012–2023

Downstream: the API surface follows this (cog-api).

`census_of_governments_finance_pipeline#76` (PR #77) adds a third `category_type` to `summary_categories.csv` — **`balance`** — covering the 14 cash-and-security holding codes, with a `balance_subtype` column. The reader has no way to reach them and no guard against them leaking into money verbs. **Blocked on:** the corpus rebuild + republish that ships pipeline PR #77. Nothing here is testable until `summary_categories.parquet` carries the new column. ## Two requirements ### 1. Neither money verb may admit a `balance` row `cog_spending()` and `cog_revenue()` must never return one. These are **stocks** — a balance at a point in time — while the money verbs return **flows** over a fiscal year. Summing a stock with a flow is meaningless, and worse, a `balance` row silently included in a spending total looks plausible rather than obviously wrong. This should be an explicit filter on `category_type`, not an incidental consequence of prefix matching. The pipeline guards its own artifacts with a single `.drop_balance()` predicate for exactly this reason; the reader wants the same shape. Note this interacts with `#11` and `#12`: whatever classifies codes into expenditure/revenue concepts must treat `balance` as a third outcome rather than forcing every code into one of two buckets. ### 2. A way to query holdings Design TBD. The two obvious shapes: - a new verb, `cog_balances()`, parallel to `cog_spending()` / `cog_revenue()`; - or an argument on an existing generic. A verb is probably right given the return semantics genuinely differ (no `amt_per_capita` interpretation as "spending per person"; year-over-year change is portfolio movement, not budget growth), but that is an owner call. Whatever the shape, `balance_subtype == "general"` (`W01`/`W31`/`W61`) must be reachable in one filter — that is the family users mean by "fund balance". ## Caveats the reader must surface, not bury These are in `docs/data_dictionary.md` § Cash and security holdings and should reach the user, because each one silently invalidates an obvious analysis: 1. **Census holdings are NOT GAAP fund balance.** Gross holdings, no liabilities netted. A reserve ratio built from them overstates what is actually available. 2. **`W` is FY2012–2021 only** — ten years, stopping two short of the corpus. A long fund-balance-share-of-revenue series is not available. 3. **The `X` family ends at FY2016**, when Census moved employee retirement to a separate survey. 4. **`X40`/`X41` change valuation basis mid-series at FY2002** (book → market) while keeping the same code through FY2011 — catalogued as `SB195`/`SB196`. Undetectable from the series alone. ## The subtype map | `balance_subtype` | codes | corpus years | |---|---|---| | `general` | `W01`, `W31`, `W61` | 2012–2021 | | `employee_retirement` | `X21`, `X30`, `X42`, `X44`, `X47`, `Z77`, `Z78` | 1967–2016 (varies) | | `unemployment_trust` | `Y07`, `Y08` | 1967–2023 | | `workers_comp_trust` | `Y21` | 2012–2023 | | `other_insurance_trust` | `Y61` | 2012–2023 | Downstream: the API surface follows this (`cog-api`).

jared referenced this issue

2026-07-30 16:12:48 -04:00

chore: regenerate fixture corpus at pipeline_commit e64a046 (#11 groundwork) #26

jared referenced this issue

2026-07-30 16:57:20 -04:00

chore: regenerate fixture corpus at pipeline_commit e64a046 (#11 groundwork) #26

jared commented

2026-07-30 22:25:43 -04:00

Requirement 1 is shipped and asserted. Requirement 2 is untouched and still needs an owner design call, so this stays open — re-scoped to the query surface only.

Requirement 1 — no `balance` row in a money verb: DONE

Delivered by uscogdata#11 (merged, 93300ae) and extended by #12 (4b23dbd). It is an explicit consequence of category_type, exactly as this issue asked, not an incidental effect of prefix matching — the flow views now select on crosswalk membership, so a balance code cannot reach either verb by construction:

-- inst/sql/20-spending_long.sql
WHERE item_code IN (
    SELECT item_code FROM summary_categories
    WHERE category_type = 'expenditure' AND spend_subtype <> 'intergovernmental')

This issue's note that "whatever classifies codes into expenditure/revenue concepts must treat balance as a third outcome rather than forcing every code into one of two buckets" is precisely what shipped: category_type is the classifier, and balance is one of its three values.

Guarded at both levels so a regression fails loudly:

View level (test-views.R) — spending_long and revenue_long each assert zero rows whose item_code is a category_type = 'balance' member.
Verb level (test-expenditure-concepts.R) — asserted on Wisconsin FY2019, a government-year that genuinely carries Y07/Y08/Y21 balance rows, against both expenditure_concept = "total" and (as of #12) revenue_concept = "total", the two widest concepts and therefore the ones most able to over-admit.

The Y family is the case that makes this non-trivial and it is covered: one letter spanning revenue (Y01), expenditure (Y05) and balance (Y07). #12 added X as a second such letter — X01 revenue, X11 expenditure, X21 balance — so there are now two independent proofs that prefix logic could not have delivered this.

Requirement 2 — a way to query holdings: NOT STARTED

Still an owner call, and still the whole remaining scope of this issue. The design question from the original post is unchanged:

a new cog_balances() verb, parallel to the money verbs; or
an argument on an existing generic.

My read is that the issue's own instinct is right — a separate verb. The return semantics genuinely differ: amt_per_capita does not mean "holdings per person" in any useful sense, year-over-year change is portfolio movement rather than budget growth, and the flow verbs' whole expenditure_concept/revenue_concept vocabulary is meaningless for a stock. Overloading a money verb would put a stock behind arguments that all assume a flow.

Two things worth deciding at the same time:

The X/Z holdings stop at FY2016 (series breaks SB197-SB202, shipped with #12) when employee retirement moved to the Annual Survey of Public Pensions. Any holdings verb needs to surface that seam rather than let a series appear to collapse.
X40/X41 switch from book value to market value at FY2002 inside a surviving code (SB195/SB196), so a holdings series is continuous in identity but not in basis.

cog-api#26 is blocked on this half.

**Requirement 1 is shipped and asserted. Requirement 2 is untouched and still needs an owner design call, so this stays open — re-scoped to the query surface only.** ## Requirement 1 — no `balance` row in a money verb: DONE Delivered by uscogdata#11 (merged, `93300ae`) and extended by #12 (`4b23dbd`). It is an explicit consequence of `category_type`, exactly as this issue asked, not an incidental effect of prefix matching — the flow views now select on crosswalk membership, so a `balance` code cannot reach either verb by construction: ```sql -- inst/sql/20-spending_long.sql WHERE item_code IN ( SELECT item_code FROM summary_categories WHERE category_type = 'expenditure' AND spend_subtype <> 'intergovernmental') ``` This issue's note that "whatever classifies codes into expenditure/revenue concepts must treat `balance` as a third outcome rather than forcing every code into one of two buckets" is precisely what shipped: `category_type` is the classifier, and `balance` is one of its three values. Guarded at **both** levels so a regression fails loudly: - *View level* (`test-views.R`) — `spending_long` and `revenue_long` each assert zero rows whose `item_code` is a `category_type = 'balance'` member. - *Verb level* (`test-expenditure-concepts.R`) — asserted on Wisconsin FY2019, a government-year that genuinely carries `Y07`/`Y08`/`Y21` balance rows, against both `expenditure_concept = "total"` and (as of #12) `revenue_concept = "total"`, the two widest concepts and therefore the ones most able to over-admit. The `Y` family is the case that makes this non-trivial and it is covered: one letter spanning revenue (`Y01`), expenditure (`Y05`) and balance (`Y07`). #12 added `X` as a second such letter — `X01` revenue, `X11` expenditure, `X21` balance — so there are now two independent proofs that prefix logic could not have delivered this. ## Requirement 2 — a way to query holdings: NOT STARTED Still an owner call, and still the whole remaining scope of this issue. The design question from the original post is unchanged: - a new `cog_balances()` verb, parallel to the money verbs; **or** - an argument on an existing generic. My read is that the issue's own instinct is right — a **separate verb**. The return semantics genuinely differ: `amt_per_capita` does not mean "holdings per person" in any useful sense, year-over-year change is portfolio movement rather than budget growth, and the flow verbs' whole `expenditure_concept`/`revenue_concept` vocabulary is meaningless for a stock. Overloading a money verb would put a stock behind arguments that all assume a flow. Two things worth deciding at the same time: 1. **The `X`/`Z` holdings stop at FY2016** (series breaks `SB197`-`SB202`, shipped with #12) when employee retirement moved to the Annual Survey of Public Pensions. Any holdings verb needs to surface that seam rather than let a series appear to collapse. 2. **`X40`/`X41` switch from book value to market value at FY2002** inside a surviving code (`SB195`/`SB196`), so a holdings series is continuous in identity but not in basis. cog-api#26 is blocked on this half.

jared referenced this issue from a commit

2026-08-03 11:47:53 -04:00

feat: balance_caveats provenance + once-per-session disclosure (#25)

jared referenced this issue from a commit

2026-08-03 11:47:53 -04:00

docs: disambiguate live-corpus vs fixture year claim in balances test comment (#25)

jared referenced this issue from a commit

2026-08-03 11:47:53 -04:00

docs: document cog_balances() and correct stale CLAUDE.md claims (#25)

jared referenced this issue from a commit

2026-08-03 11:47:53 -04:00

docs: fix stale test count and phantom notes column in cog_balances docs (#25)