fix: accept corpus schema_version 7 (#80)

Reviewed-on: #29

CLAUDE.md

@@ -28,8 +28,23 @@ USCOGDATA_URL (local path or https://)
 - `R/session.R` — `cog_open()`, `cog_close()`, `.ensure_session()`, `.coerce_govid_input()`
 - `R/manifest.R` — `.fetch_or_cache_manifest()`, `.is_local_path()` (local paths bypass HTTP/cache)
 - `R/views.R` — `.register_views()` (substitutes `{url}` into SQL files at `inst/sql/`)
-- `inst/sql/` — 7 SQL view definitions: `long`, `spending_long`, `revenue_long`, `canonical_fips_xwalk`, `summary_categories`, `spending_annotated`, `revenue_annotated`
+- `inst/sql/` — **23** SQL view definitions (measured), numbered by load order
+  (`10-` through `46-`): the `*_long` layer (`long`, `spending_long`,
+  `revenue_long`, `ig_long`, `balance_long`, plus `_harmonized` variants of
+  `spending_long`/`revenue_long`/`ig_long`), the `*_annotated` layer
+  (`spending_annotated`, `revenue_annotated`, `ig_annotated`,
+  `balance_annotated`, plus `_harmonized` variants of `spending_annotated`/
+  `revenue_annotated`/`ig_annotated`), and metadata views
+  (`canonical_fips_xwalk`, `summary_categories`, `gov_population_yearly`,
+  `harmonization_map`, `harmonization_recipes`, `series_breaks_pq`,
+  `representation`, `code_set`)
 - `R/spending.R` / `R/revenue.R` — `cog_spending()` / `cog_revenue()` via shared `.verb_spendrev()`
+- `R/balances.R` — `cog_balances()`. A third money-adjacent verb, but returns a
+  **stock** (a balance at a point in time) rather than a **flow** (activity
+  over a fiscal year), so it does NOT route through `.verb_spendrev()` and has
+  no `expenditure_concept`/`revenue_concept`/`complete`/`subtype` arguments.
+  `R/balance_caveats.R` attaches `provenance$balance_caveats` (GAAP-vs-gross
+  disclosure + measured per-subtype coverage windows).
 - `R/rollup.R` — `cog_geographic_rollup()` (accepts named list of govids by layer)
 - `R/peers.R` — `cog_find_peers()` + `cog_peer_compare()`
 - `R/search.R` — `cog_gov_search()` (name pattern, state, type filters)
@@ -45,28 +60,31 @@ USCOGDATA_URL (local path or https://)
 Any value without `://` is treated as a local path by `.is_local_path()` and reads
 `manifest.json` directly from disk (no HTTP, no TTL cache).
 
-## Current State (2026-04-27)
+## Current State (2026-08-03)
 
 **Version:** 0.1.0 (pre-release)  
-**Branch:** `main`, commit `d65e9fe`  
-**Tests:** 181 PASS / 0 FAIL / 0 SKIP  
+**Branch:** `feat/cog-balances-25`, commit `fde62eb`  
+**Tests:** 788 PASS / 0 FAIL / 0 SKIP / 0 WARN (measured `testthat::test_local()`, 2026-08-03, after the final-review fix wave)  
 **CI:** Gitea Actions green (`.gitea/workflows/ci.yml`)
 
 ### Completed (Tasks 2.1–2.7)
 
-All 8 exported verbs implemented and tested:
-`cog_spending`, `cog_revenue`, `cog_explain`, `cog_geographic_rollup`,
-`cog_find_peers`, `cog_peer_compare`, `cog_gov_search`, `cog_mirror`,
-plus `cog_categories`.
+All **14** exports implemented and tested (measured from `NAMESPACE`):
+`cog_spending`, `cog_revenue`, `cog_balances`, `cog_explain`,
+`cog_geographic_rollup`, `cog_find_peers`, `cog_peer_compare`,
+`cog_gov_search`, `cog_mirror`, `cog_categories`, `cog_recipes`,
+`cog_manifest`, `cog_basket_resolution`, `cog_basket_unresolved`.
 
-Bundled fixture corpus at `inst/extdata/fixture_corpus/` (3.6 MB, years
-2019+2020, all 50 states). Tests run fully offline — no credentials needed.
+Bundled fixture corpus at `inst/extdata/fixture_corpus/` (years
+2011, 2012, 2019, 2020 — measured via DuckDB `read_parquet(hive_partitioning=1)`,
+2026-08-03; all 50 states). Tests run fully offline — no credentials needed.
 
 ### Remaining to v0.1 release
 
-1. **Task 2.8 — Docs:** roxygen `@param`/`@return`/`@examples` on all exports;
-   full `README.md`; `_pkgdown.yml`; `devtools::document()` + `pkgdown::build_site()`.
-   Vignettes can be stubbed for v0.1.
+1. **Task 2.8 — Docs:** mostly done — all 14 exports have a `man/*.Rd`,
+   `README.md` and `_pkgdown.yml` exist, and `vignettes/` carries
+   `total-spending.Rmd` + `population-denominators.Rmd`. Outstanding:
+   `pkgdown::build_site()` has never been run (no `docs/`).
 
 2. **Phase 3 — cog_explorer bridge:** create
    `cog_explorer/examples/hello_world_uscogdata.Rmd` (installs from Gitea, runs
@@ -99,6 +117,10 @@ devtools::test()
 - All verbs call `.ensure_session()` first, then query via `DBI::dbGetQuery()`
 - Return value is always a `tbl_df` with a `provenance` attribute
 - govid inputs always go through `.coerce_govid_input()` (accepts character or data frame)
-- SQL lives in `inst/sql/` — never inline SQL strings in R files
+- SQL has two layers. **View definitions** live in `inst/sql/` and are
+  registered by `.register_views()`, which globs the directory in sorted order
+  and substitutes `{url}`. **Query construction** is inline `sprintf()` in R
+  (`.build_verb_sql()`, `.run_recipe()`, `.attach_per_capita()`). Add a view as
+  a numbered `.sql` file; build a query in R.
 - No arrow dependency — DuckDB reads parquet natively
 - `withr` is a Suggests-only dep; only used in tests

NAMESPACE

@@ -1,5 +1,6 @@
 # Generated by roxygen2: do not edit by hand
 
+export(cog_balances)
 export(cog_basket_resolution)
 export(cog_basket_unresolved)
 export(cog_categories)

NEWS.md

@@ -1,5 +1,130 @@
 # uscogdata 0.1.0 (development)
 
+## New: `cog_balances()` for cash-and-security holdings
+
+* New `cog_balances()` exposes the 14 cash-and-security holding codes
+  (`category_type = "balance"`): fund balances, retirement system holdings and
+  insurance trust balances (#25). Holdings are a stock, not a flow, so the verb
+  has no `expenditure_concept` / `revenue_concept` / `complete` arguments, and
+  no `subtype` argument either -- for holdings, `category` is a strict
+  coarsening of `balance_subtype`, so `category = "Fund Balances"` is exactly
+  the `general` family (`W01`/`W31`/`W61`).
+* `cog_balances()` results carry `provenance$balance_caveats`, recording that
+  Census holdings are gross rather than GAAP fund balance, and the measured
+  coverage window of each subtype family.
+
+## Multi-government aggregates now disclose their reporting coverage
+
+* The Census of Governments is a **complete census only in years ending in 2
+  and 7**; every other year is a sample, and the sample varies enormously. On
+  the bundled fixture, Wisconsin's 608-city universe rolls up **597**
+  governments in FY2012 and **112** in FY2019 — an 18%-to-98% swing the
+  return value said nothing about, so a statewide total resting on a fifth of
+  the universe looked exactly like one resting on all of it.
+* `cog_geographic_rollup()`, `cog_peer_compare()` and `cog_find_peers()` gain
+  `coverage`:
+
+  | value | effect |
+  |---|---|
+  | `"all"` (default) | every unit that reported that year — unchanged behaviour |
+  | `"census"` | census years only; aborts if the range holds none rather than returning nothing |
+  | `"consistent"` | only units reporting in *every* requested year — a balanced panel |
+
+* **Regardless of mode**, every result now carries `provenance$coverage` with
+  per-year `n_units_reporting`, `n_units_expected` and `is_census_year`, plus
+  `provenance$coverage_mode`. `cog_explain()` prints a "Reporting coverage"
+  section. So the default mode can no longer mislead silently.
+* `is_census_year` is a statement about the **survey calendar**, never a claim
+  of completeness: FY1967 is a census year in which only 97 of Wisconsin's 608
+  cities report. `n_units_reporting` is the number that tells the truth.
+* On `cog_peer_compare()` the target is exempt from `"consistent"` balancing —
+  it is the subject of the comparison, not a member of the cohort — and the
+  `summary_*` quantiles are computed after the filter, so they describe the
+  cohort actually returned. `n_units_reporting` counts peers only, against the
+  cohort size.
+* On `cog_find_peers()`, `coverage` governs the cohort **vintage** when `year`
+  is `NULL`: `"census"` snaps to the most recent census year with an observed
+  population, so a cohort is not built from a sample year in which most of the
+  candidate universe is absent.
+
+## `complete = TRUE`: absent cells, labelled with why they are absent
+
+* `cog_spending()` and `cog_revenue()` gain `complete`, defaulting to `FALSE`
+  (today's behaviour). With `complete = TRUE` the requested grid is filled
+  from the corpus's `code_set` table and every row carries a new
+  `value_source` column:
+
+  | `value_source` | meaning | `amt_nominal` |
+  |---|---|---|
+  | `reported` | the corpus carries this cell | as published |
+  | `census_zero` | dense-source year (≤ FY2011), cell absent — Census published `$0` | `0` |
+  | `not_reported` | sparse-source year (≥ FY2012), cell absent — unknown | `NA` |
+
+  The `NA` is deliberate and is the whole point: filling a modern absence
+  with `0` would invent data, which is precisely the error the corpus's
+  representation contract exists to prevent.
+* This restores information the reader lost when the corpus was sparsified
+  (`SB194`, cog_pipeline#64) — a wide-era query whose cells were all `$0`
+  had begun returning nothing at all — and improves on what came before it,
+  since the pre-sparsification corpus could not distinguish a published zero
+  from an unreported cell either.
+* The grid is scoped to each government's **own type**, so a county is never
+  filled with cells only a state can report.
+* Needs a corpus published from 2026-07-29 onward (when `representation` and
+  `code_set` began shipping); aborts with class
+  `uscogdata_representation_unavailable` otherwise. Gated on the manifest
+  listing those tables rather than on `schema_version`, which was never
+  bumped for the change. Not available with `recipe` or
+  `expenditure_concept = "total"` — neither draws its cells from `code_set`.
+* `provenance$completion` reports `applied`, `rows_filled`, and the per-year
+  `absence_means` rule; `cog_explain()` prints a "Completion" section.
+
+## Corpus-wide series breaks now reach users (`corpus_break_refs`)
+
+* Four catalogued series breaks carry `fin_code = "ALL"` — caveats about the
+  corpus as a whole rather than about one item code. `series_break_refs` is
+  built by matching `fin_code` against the item codes in the result, and no
+  row's `item_code` is ever the literal `"ALL"`, so **none of them could ever
+  be surfaced**: `SB085` (dollar precision across the 1976/1977 boundary),
+  `SB087` (imputation exclusion from FY2002), `SB194` (the dense → sparse
+  representation change at FY2012) and `SB086` (the government id scheme
+  change at FY2017).
+* Provenance gains `corpus_break_refs`, selected on the break-year window
+  alone and disjoint from `series_break_refs` by construction, so a consumer
+  can tell a whole-result caveat from a break in one series. `cog_explain()`
+  prints them under their own "Corpus-wide caveats" heading. cog-api passes
+  provenance through verbatim, so the field appears there without an API
+  change.
+* `SB194` is the one that made this urgent: a query spanning FY2011 → FY2012
+  crosses the boundary where an absent cell stops meaning "Census published
+  `$0`" and starts meaning "not reported", and until now nothing said so.
+
+## Bundled fixture regenerated against the sparsified corpus
+
+* `inst/extdata/fixture_corpus/` now tracks the corpus published on
+  2026-07-29 (`pipeline_commit 83f9715`, schema v6). The wide era no longer
+  stores explicit zeros: FY2011 fell from 2,864,212 rows to 496,004, of
+  which none are `$0`. **Absence now means two different things** — in a
+  `dense_source` year (≤ FY2011) an absent cell means Census published `$0`;
+  in a `sparse_source` year (≥ FY2012) it means not reported. The corpus
+  carries that rule in two new tables the fixture now ships,
+  `representation.parquet` and `code_set.parquet`, alongside
+  `census_collection_coverage.parquet` and `lineage_events.parquet`
+  (all ten publish-tree metadata tables, up from six). Catalogued upstream
+  as series break `SB194`.
+* `cog_categories()` gains an `assistance` spending subtype: the J-prefix
+  aid/benefit codes (`J19`, `J67`, `J68`, `J85`) are categorised now that
+  the upstream crosswalk covers every flow code carrying dollars.
+* Two consequences worth knowing about, both visible in provenance rather
+  than in returned dollars. The harmonization block's `na_rows_excluded`
+  counts only rows that exist, so wide-era codes that were zero-padded no
+  longer appear there. Coverage-gap `suggestions` are presence-based for the
+  same reason, so a recipe whose component codes were all `$0` for a given
+  government-year is no longer suggested for it.
+* `tests/testthat/test-fixture-vintage.R` pins these structural facts, so a
+  fixture left behind by a future publish fails loudly instead of letting the
+  suite pass against a corpus that no longer exists.
+
 ## Breaking: corpus schema_version 4 (Phase P canonical ids)
 
 * The package now requires corpus `schema_version = 4` (`MinCorpusSchema` /

R/balance_caveats.R

@@ -0,0 +1,122 @@
+# R/balance_caveats.R
+#
+# The four caveats from cog_pipeline/docs/data_dictionary.md § Cash and
+# security holdings. Each one silently invalidates an obvious analysis, so
+# they travel in provenance (machine-readable, for cog-api#26) rather than
+# living only in prose.
+#
+# Two of the four are already carried by the code-driven series-break
+# builders and are deliberately NOT duplicated here:
+#   * SB195/SB196 -- X40/X41 book -> market at FY2002 -- fire via
+#     series_break_refs on the recipe path, the only path that observes those
+#     codes.
+# What remains is the GAAP distinction (a constant) and the coverage windows
+# (measured, never hardcoded, so they stay correct as the corpus grows).
+
+#' Per-subtype observed year extents, plus which requested families are
+#' truncated relative to the requested span.
+#' @noRd
+.balance_caveats <- function(con, codes_observed, years) {
+  cw <- .balance_coverage_windows(con)
+
+  observed_subtypes <- if (length(codes_observed) == 0L) {
+    character(0)
+  } else {
+    DBI::dbGetQuery(con, sprintf(
+      "SELECT DISTINCT balance_subtype FROM summary_categories
+       WHERE item_code IN (%s) AND balance_subtype IS NOT NULL",
+      .sql_lit_chr(codes_observed)
+    ))$balance_subtype
+  }
+
+  # A family is "truncated" when the caller asked for years outside the span
+  # that family actually covers -- the FY2016 employee-retirement termination
+  # and the FY2021 end of the W family are both this shape.
+  truncated <- character(0)
+  if (length(years) > 0L) {
+    for (s in observed_subtypes) {
+      w <- cw[[s]]
+      if (is.null(w)) next
+      if (max(years) > w[2] || min(years) < w[1]) truncated <- c(truncated, s)
+    }
+  }
+
+  list(
+    not_gaap = TRUE,
+    not_gaap_note = paste0(
+      "Census holdings are gross -- no liabilities are netted -- and are NOT ",
+      "GAAP fund balance. A reserve ratio built from them overstates what is ",
+      "actually available."
+    ),
+    coverage_window = cw,
+    truncated = sort(unique(truncated))
+  )
+}
+
+#' Per-subtype [min year, max year] extents for EVERY balance subtype in the
+#' mounted corpus, memoised for the session.
+#'
+#' The query carries no govid and no year predicate -- its answer is a property
+#' of the mounted corpus alone and cannot change between calls -- but it scans
+#' the whole of `balance_long`, which measured 35% of `cog_balances()` runtime
+#' on the bundled fixture and would be a per-request throughput ceiling once
+#' cog-api#26 serves this verb over HTTP. Memoised in `.uscogdata_env` and
+#' invalidated by `cog_close()`, the same pattern as `.uscogdata_env$manifest`.
+#'
+#' Scope is deliberately corpus-wide rather than query-scoped: a caller asking
+#' "is there a family I missed?" needs every window. The observed-scoped field
+#' is `truncated`. Documented as such in inst/schemas/provenance-v1.json.
+#' @noRd
+.balance_coverage_windows <- function(con) {
+  cached <- .uscogdata_env$balance_coverage_windows
+  if (!is.null(cached)) return(cached)
+
+  windows <- DBI::dbGetQuery(con,
+    "SELECT c.balance_subtype AS subtype,
+            MIN(l.year) AS year_min,
+            MAX(l.year) AS year_max
+     FROM balance_long l
+     JOIN summary_categories c USING (item_code)
+     WHERE c.balance_subtype IS NOT NULL
+     GROUP BY 1
+     ORDER BY 1"
+  )
+
+  cw <- stats::setNames(
+    lapply(seq_len(nrow(windows)),
+           function(i) as.integer(c(windows$year_min[i], windows$year_max[i]))),
+    windows$subtype
+  )
+  .uscogdata_env$balance_coverage_windows <- cw
+  cw
+}
+
+#' TRUE the first time `key` is seen this session, FALSE thereafter.
+#' Reset by cog_close().
+#' @noRd
+.balance_caveat_once <- function(key) {
+  seen <- .uscogdata_env$balance_caveats_shown
+  if (is.null(seen)) seen <- character(0)
+  if (key %in% seen) return(FALSE)
+  .uscogdata_env$balance_caveats_shown <- c(seen, key)
+  TRUE
+}
+
+#' Emit at most one message per caveat class per session.
+#' @noRd
+.emit_balance_caveats <- function(caveats) {
+  if (.balance_caveat_once("not_gaap")) {
+    cli::cli_inform(c(
+      "!" = "Census holdings are gross and are {.strong not} GAAP fund balance.",
+      "i" = "No liabilities are netted; a reserve ratio built from them overstates available funds."
+    ))
+  }
+  if (length(caveats$truncated) > 0L &&
+      .balance_caveat_once("coverage_window")) {
+    cli::cli_inform(c(
+      "!" = "Requested years extend beyond what {.val {caveats$truncated}} actually covers.",
+      "i" = "See {.code provenance$balance_caveats$coverage_window}."
+    ))
+  }
+  invisible(NULL)
+}

R/balances.R

@@ -0,0 +1,159 @@
+# R/balances.R
+#
+# Cash and security holdings. A third verb rather than an argument on a money
+# verb because holdings are a STOCK -- a balance at a point in time -- while
+# cog_spending()/cog_revenue() return FLOWS over a fiscal year. The money
+# verbs' whole argument vocabulary (expenditure_concept, revenue_concept,
+# complete=) describes flows and is meaningless here, so this deliberately
+# does NOT route through .verb_spendrev().
+
+#' Cash and security holdings for one or more governments
+#'
+#' Returns Census cash-and-security holdings (`category_type = "balance"`):
+#' fund balances, retirement system holdings and insurance trust balances.
+#'
+#' @section Holdings are not GAAP fund balance:
+#' Census holdings are **gross** -- no liabilities are netted -- so a reserve
+#' ratio built from them overstates what is actually available. They are not
+#' comparable to a GAAP fund balance from an ACFR.
+#'
+#' @param govid Canonical govid(s): a character vector, or a data frame with a
+#'   `canonical_govid` column (e.g. from [cog_gov_search()]).
+#' @param years Integer vector of fiscal years.
+#' @param category Optional character vector of categories to keep. One of
+#'   `"Fund Balances"`, `"Insurance Trust Balances"`,
+#'   `"Retirement System Holdings"`. There is deliberately no `subtype`
+#'   argument: for holdings, `category` is a strict coarsening of
+#'   `balance_subtype` (unlike the money verbs, where the two axes cross), so
+#'   every combination would be either redundant or empty.
+#'   `category = "Fund Balances"` is exactly the `general` family
+#'   (`W01`/`W31`/`W61`). `balance_subtype` is returned, so a finer split is
+#'   one `dplyr::filter()` away.
+#' @param per_capita Divide holdings by population. Note this is a **stock per
+#'   resident** (reserves per person), which is *not* comparable to
+#'   [cog_spending()]'s per-capita figures -- those are a flow per person.
+#' @param adjust_to_year Deflate to this year's dollars (CPI-U).
+#' @param basis Accepted for uniformity with the money verbs, but currently a
+#'   **no-op**: `harmonization_map` carries no balance-code rows, so harmonized
+#'   and raw space are identical for holdings. Reported in
+#'   `provenance$basis_note`.
+#' @param recipe Optional harmonization recipe id (see [cog_recipes()]).
+#'   `"cash_securities_z77_wide"` and `"cash_securities_z78_wide"` bridge the
+#'   wide era to the modern one.
+#'
+#' @return Tibble with columns `year`, `canonical_govid`, `gov_name`,
+#'   `balance_subtype`, `category`, `amt_nominal`, `codes_included`,
+#'   `aggregate_fallback`, plus optional `amt_per_capita_nominal` and
+#'   `pop_source` (when `per_capita = TRUE`), optional `amt_real` (when
+#'   `adjust_to_year` is set), and optional `amt_per_capita_real` (only when
+#'   **both** `per_capita = TRUE` and `adjust_to_year` are set -- there is no
+#'   nominal per-capita column to deflate otherwise). Amounts are full US
+#'   dollars.
+#'
+#'   Carries a `provenance` attribute matching
+#'   `inst/schemas/provenance-v1.json`, whose `balance_caveats` block reports
+#'   `not_gaap`, `not_gaap_note`, `coverage_window` (measured year extents for
+#'   every balance subtype in the mounted corpus, not only the observed ones)
+#'   and `truncated` (the observed subtypes whose coverage falls short of the
+#'   requested years). `expenditure_concept`/`revenue_concept` are `NA` --
+#'   holdings are a stock, not a flow, so neither concept vocabulary applies.
+#' @export
+cog_balances <- function(govid, years, category = NULL,
+                         per_capita = FALSE, adjust_to_year = NULL,
+                         basis = c("harmonized", "raw"), recipe = NULL) {
+  call <- match.call()
+  basis <- match.arg(basis, c("harmonized", "raw"))
+  # Coerce FIRST, validate second: .validate_verb_inputs() asserts
+  # is.character(govid), and a data-frame govid (cog_gov_search() output) has
+  # not been unwrapped yet at this point.
+  govid <- .coerce_govid_input(govid)
+  # The money verbs' validator, reused rather than re-implemented (R/spending.R).
+  # It covers the exact superset cog_balances() needs -- including the
+  # recipe/category mutual-exclusivity guard -- so a second local copy would
+  # only be a place for the two to drift apart. This is the same kind of
+  # helper reuse as .build_verb_sql()/.attach_per_capita() below; it does NOT
+  # route the verb through .verb_spendrev(), which stays deliberately unused
+  # here because its flow vocabulary is meaningless for a stock.
+  .validate_verb_inputs(govid, years, category, per_capita, adjust_to_year,
+                        recipe)
+  years <- as.integer(years)
+  if (!is.null(adjust_to_year)) adjust_to_year <- as.integer(adjust_to_year)
+
+  con <- .ensure_session()
+  .require_balance_support(con)
+  scope <- .check_govids_in_scope(govid)
+
+  basis_note <- paste0(
+    "`basis` has no effect on holdings: harmonization_map carries no ",
+    "balance-code rows, so harmonized and raw space are identical here."
+  )
+
+  manifest <- .uscogdata_env$manifest
+  recipe_block <- NULL
+  category_for_prov <- category
+
+  if (!is.null(recipe)) {
+    .require_schema_v5(con, manifest, "recipe =")
+    .validate_recipe_id(con, recipe)
+    comps <- .recipe_components(con, recipe)
+    recipe_label <- comps$label[[1]]
+    result <- .run_recipe(con, recipe, govid, years)
+    sql <- attr(result, "sql_query")
+    result <- .shape_recipe_result(result, "balance_subtype", recipe_label)
+    recipe_block <- list(
+      recipe_id = recipe, label = recipe_label,
+      components = .df_to_row_list(comps)
+    )
+    category_for_prov <- recipe_label
+  } else {
+    sql <- .build_verb_sql("balance_annotated", "balance_subtype",
+                           govid, years, category,
+                           ig_view = NULL, subtype_scope = NULL)
+    result <- tibble::as_tibble(DBI::dbGetQuery(con, sql))
+  }
+
+  # Order matters (matches .verb_spendrev()): per-capita first, so
+  # .attach_real_dollars() deflates the nominal per-capita column into
+  # amt_per_capita_real rather than needing amt_per_capita_nominal recomputed.
+  if (isTRUE(per_capita)) result <- .attach_per_capita(result, con, govid)
+  if (!is.null(adjust_to_year)) {
+    result <- .attach_real_dollars(result, adjust_to_year, per_capita)
+  }
+
+  prov <- .build_provenance(
+    verb = "cog_balances", call = call, govid = govid, years = years,
+    category = category_for_prov, per_capita = per_capita,
+    adjust_to_year = adjust_to_year, result = result, sql = sql,
+    subtype_col = "balance_subtype",
+    basis = basis, basis_note = basis_note,
+    # Neither concept vocabulary applies to a stock.
+    expenditure_concept = NA_character_,
+    revenue_concept = NA_character_,
+    recipe = recipe_block
+  )
+  prov$scope$govids_found   <- scope$found
+  prov$scope$govids_missing <- scope$missing
+
+  prov$balance_caveats <- .balance_caveats(
+    con, prov$codes_summed$observed, years
+  )
+  .emit_balance_caveats(prov$balance_caveats)
+
+  attr(result, "provenance") <- prov
+  result
+}
+
+#' Abort unless the mounted corpus classifies balance codes.
+#'
+#' `balance_subtype` arrived with cog_pipeline #76/#77 without a
+#' schema_version bump, so the check is on the column, not the version.
+#' @noRd
+.require_balance_support <- function(con) {
+  if (.corpus_has_balance_subtype(con)) return(invisible(TRUE))
+  cli::cli_abort(
+    c("This corpus does not classify cash and security holdings.",
+      i = "`summary_categories` has no {.field balance_subtype} column.",
+      i = "Republish from cog_pipeline at #76/#77 or later."),
+    class = "uscogdata_no_balance_support"
+  )
+}

R/basis.R

@@ -44,11 +44,18 @@
 
 #' Count + sum item-level rows that basis="harmonized" excludes because they
 #' carry no harmonized_code (discontinued / not-yet-ruled codes) within the
-#' requested flow type (spending or revenue), govids, and years. Only
-#' meaningful when the resolved basis is "harmonized"; returns an
-#' applied = FALSE stub otherwise (raw basis never excludes rows this way).
+#' calling verb's crosswalk scope (`subtype_col` values in `subtype_scope` --
+#' the same subtype-membership classification the verb SQL uses, never
+#' item-code prefixes), govids, and years. Only meaningful when the resolved
+#' basis is "harmonized"; returns an applied = FALSE stub otherwise (raw
+#' basis never excludes rows this way).
+#'
+#' The intergovernmental leg is deliberately outside this count even for
+#' expenditure_concept = "total": ig_long_harmonized COALESCEs rather than
+#' drops NULL-harmonized rows, so harmonization never excludes an IG row.
 #' @noRd
-.build_harmonization_block <- function(con, govid, years, resolved, flow_prefixes) {
+.build_harmonization_block <- function(con, govid, years, resolved,
+                                       subtype_col, subtype_scope) {
   if (!identical(resolved$basis, "harmonized")) {
     return(list(
       applied = FALSE,
@@ -63,9 +70,11 @@
      FROM long
      WHERE canonical_govid IN (%s) AND year IN (%s)
        AND NOT is_aggregate AND harmonized_code IS NULL
-       AND LEFT(item_code, 1) IN (%s)",
+       AND item_code IN (
+         SELECT item_code FROM summary_categories WHERE %s IN (%s)
+       )",
     .sql_lit_chr(govid), paste(as.integer(years), collapse = ","),
-    .sql_lit_chr(flow_prefixes)
+    subtype_col, .sql_lit_chr(subtype_scope)
   )
   na <- DBI::dbGetQuery(con, sql)
 

R/categories.R

@@ -5,12 +5,19 @@
 #' Returns the category taxonomy exposed by the corpus's
 #' `summary_categories` view, grouped to one row per
 #' `(category, subtype)` pair. Use this to discover valid `category`
-#' values for [cog_spending()] / [cog_revenue()] /
+#' values for [cog_spending()] / [cog_revenue()] / [cog_balances()] /
 #' [cog_geographic_rollup()] and to audit which Census item codes feed
 #' each category.
 #'
-#' @param type Either `NULL` (default, return both spending and revenue
-#'   rows), `"spending"`, or `"revenue"`.
+#' `subtype` COALESCEs the crosswalk's three subtype columns, so it carries
+#' `spend_subtype` on expenditure rows, `revenue_subtype` on revenue rows and
+#' `balance_subtype` on balance rows. Note that [cog_balances()] itself takes
+#' no `subtype` argument — for holdings, `category` is a strict coarsening of
+#' `balance_subtype` — but the value is surfaced here because it is the
+#' discovery surface downstream consumers build their vocabulary from.
+#'
+#' @param type Either `NULL` (default, every row: expenditure, revenue and
+#'   balance), `"spending"`, `"revenue"`, or `"balance"`.
 #' @param pattern Optional regex matched case-insensitively against the
 #'   `category` column (e.g. `"Police"` or `"Tax"`).
 #' @return Tibble with columns `category`, `category_type`, `subtype`,
@@ -20,8 +27,8 @@
 cog_categories <- function(type = NULL, pattern = NULL) {
   if (!is.null(type)) {
     if (!is.character(type) || length(type) != 1L ||
-        !type %in% c("spending", "revenue")) {
-      cli::cli_abort('`type` must be NULL, "spending", or "revenue".')
+        !type %in% c("spending", "revenue", "balance")) {
+      cli::cli_abort('`type` must be NULL, "spending", "revenue", or "balance".')
     }
   }
   if (!is.null(pattern) &&
@@ -48,7 +55,7 @@ cog_categories <- function(type = NULL, pattern = NULL) {
 
   sql <- paste(
     "SELECT category, category_type,
-            COALESCE(spend_subtype, revenue_subtype) AS subtype,
+            COALESCE(spend_subtype, revenue_subtype, balance_subtype) AS subtype,
             COUNT(DISTINCT item_code) AS n_codes,
             string_agg(DISTINCT item_code, ',' ORDER BY item_code) AS item_codes
      FROM summary_categories",

R/complete.R

@@ -0,0 +1,149 @@
+# R/complete.R
+#
+# `complete = TRUE` on the money verbs. Fills the requested grid so that a
+# cell the corpus does not carry still appears, labelled with WHY it is
+# missing.
+#
+# The corpus stopped storing the wide era's explicit zeros
+# (cog_pipeline#64, series break SB194), which made absence ambiguous:
+#
+#   <= FY2011  dense_source   absent => Census published $0    (census_zero)
+#   >= FY2012  sparse_source  absent => not reported, unknown  (not_reported)
+#
+# Before sparsification a wide-era query whose cells were all $0 came back as
+# explicit $0 rows; afterwards it came back empty, with nothing to say which
+# of the two meanings applied. This restores that -- and improves on it,
+# because the pre-sparsification corpus could not distinguish the two either.
+#
+# `census_zero` fills carry `amt_nominal = 0`; `not_reported` fills carry NA.
+# That difference is the entire point: writing 0 into a modern absence would
+# invent data, which is the error the representation contract exists to stop.
+
+#' @noRd
+.abort_complete_unsupported <- function(reason, alternative) {
+  cli::cli_abort(c(
+    "{.code complete = TRUE} is not supported for this query.",
+    x = reason,
+    i = alternative
+  ), class = "uscogdata_complete_unsupported")
+}
+
+#' @noRd
+.require_representation <- function(con, manifest) {
+  needed <- c("representation.parquet", "code_set.parquet")
+  missing <- needed[!vapply(needed, function(f) .corpus_has_table(manifest, f),
+                            logical(1))]
+  if (length(missing) == 0L) return(invisible(TRUE))
+  cli::cli_abort(c(
+    "This corpus does not publish the representation contract.",
+    x = "Missing: {.file {missing}}.",
+    i = "{.code complete = TRUE} needs those tables to know whether an absent cell means Census published $0 or means the government did not report.",
+    i = "They ship with corpora published from 2026-07-29 onward; re-point {.envvar USCOGDATA_URL} at a current corpus, or omit {.code complete}."
+  ), class = "uscogdata_representation_unavailable")
+}
+
+#' The cells a government-year COULD carry: every code in force for that
+#' government's own type, mapped through `summary_categories`, restricted to
+#' the calling verb's crosswalk subtype scope (the same subtype-membership
+#' classification the verb SQL itself uses -- e.g. the `primary` concept's
+#' operations/capital/assistance) and (when given) its category filter.
+#'
+#' Scoped by `govs_type` deliberately. Filling against the union of all types
+#' would invent cells that the government can never report -- a county row for
+#' "state IG transfer to school districts" -- and those inventions would then
+#' be indistinguishable from real census zeros.
+#'
+#' `NOT cs.is_aggregate` mirrors `spending_long` / `revenue_long`, which drop
+#' aggregate rows. Without it the grid would offer cells the verb structurally
+#' never returns, so every one of them would fill as a phantom $0.
+#' @noRd
+.completion_grid_sql <- function(subtype_col, govid, years, category,
+                                 subtype_scope) {
+  category_pred <- if (is.null(category)) {
+    ""
+  } else {
+    sprintf("AND c.category IN (%s)", .sql_lit_chr(category))
+  }
+  sprintf(
+    "SELECT DISTINCT
+       cs.year,
+       x.canonical_govid,
+       x.gov_name,
+       c.%1$s AS subtype_value,
+       c.category,
+       r.absence_means
+     FROM code_set cs
+     JOIN canonical_fips_xwalk x ON x.govs_type = cs.type
+     JOIN summary_categories c   ON c.item_code = cs.item_code
+     JOIN representation r       ON r.year = cs.year
+     WHERE x.canonical_govid IN (%2$s)
+       AND cs.year IN (%3$s)
+       AND NOT cs.is_aggregate
+       AND c.category IS NOT NULL
+       AND c.%1$s IN (%4$s)
+       %5$s",
+    subtype_col, .sql_lit_chr(govid),
+    paste(as.integer(years), collapse = ","),
+    .sql_lit_chr(subtype_scope), category_pred
+  )
+}
+
+#' Fill `result` out to the full grid, stamping `value_source` on every row.
+#'
+#' Returns the completed tibble with a `.completion` attribute carrying the
+#' provenance block. Reported rows are passed through untouched -- filling
+#' must never alter or drop what the corpus actually published.
+#' @noRd
+.complete_result <- function(result, con, subtype_col, govid, years, category,
+                             subtype_scope) {
+  grid <- tibble::as_tibble(DBI::dbGetQuery(
+    con, .completion_grid_sql(subtype_col, govid, years, category, subtype_scope)
+  ))
+
+  result$value_source <- rep("reported", nrow(result))
+  if (nrow(grid) == 0L) {
+    attr(result, ".completion") <- list(
+      applied = TRUE, rows_filled = 0L, absence_means = list()
+    )
+    return(result)
+  }
+
+  names(grid)[names(grid) == "subtype_value"] <- subtype_col
+  key <- function(d) {
+    paste(d$year, d$canonical_govid, d[[subtype_col]], d$category, sep = "\r")
+  }
+  missing <- grid[!key(grid) %in% key(result), , drop = FALSE]
+
+  if (nrow(missing) > 0L) {
+    filled <- tibble::tibble(
+      year            = as.integer(missing$year),
+      canonical_govid = as.character(missing$canonical_govid),
+      gov_name        = as.character(missing$gov_name),
+      category        = as.character(missing$category),
+      # census_zero is a value Census published; not_reported is unknown and
+      # must stay NA. Collapsing the two to 0 is the defect, not the fill.
+      amt_nominal     = ifelse(missing$absence_means == "census_zero",
+                               0, NA_real_),
+      codes_included  = NA_character_,
+      aggregate_fallback = NA,
+      value_source    = as.character(missing$absence_means)
+    )
+    filled[[subtype_col]] <- as.character(missing[[subtype_col]])
+    if ("notes" %in% names(result)) filled$notes <- NA_character_
+
+    result <- dplyr::bind_rows(result, filled)
+    result <- result[order(result$year, result$canonical_govid,
+                           result[[subtype_col]], result$category), ,
+                     drop = FALSE]
+  }
+
+  rules <- unique(grid[, c("year", "absence_means")])
+  attr(result, ".completion") <- list(
+    applied = TRUE,
+    rows_filled = nrow(missing),
+    absence_means = stats::setNames(
+      as.list(as.character(rules$absence_means)), as.character(rules$year)
+    )
+  )
+  result
+}

R/coverage.R

@@ -0,0 +1,107 @@
+# R/coverage.R
+#
+# Reporting-coverage disclosure for the multi-government verbs (uscogdata#13,
+# findings F-020 and F-023).
+#
+# The Census of Governments is a COMPLETE CENSUS only in years ending in 2 and
+# 7. Every other year is a sample, and the sample varies enormously: on the
+# bundled fixture, Wisconsin's 608-city universe reports 597 governments in
+# FY2012 and 112 in FY2019. Summing "whatever reported" across those years is
+# what the verbs have always done -- correctly -- but the return value said
+# nothing about it, so a statewide total resting on 18% of the universe looked
+# exactly like one resting on 98%.
+#
+# Owner's settled design: a `coverage` argument selecting WHICH units to
+# include, plus always-on metadata saying how many there were either way. The
+# principle behind it: using these verbs correctly must not require the caller
+# to know the survey calendar.
+
+# Years ending in 2 or 7 are full censuses of every government; all others are
+# samples.
+.CENSUS_YEAR_ENDINGS <- c(2L, 7L)
+
+#' @noRd
+.is_census_year <- function(years) {
+  as.integer(years) %% 10L %in% .CENSUS_YEAR_ENDINGS
+}
+
+#' @noRd
+.validate_coverage <- function(coverage) {
+  tryCatch(
+    match.arg(coverage, c("all", "census", "consistent")),
+    error = function(e) {
+      cli::cli_abort(
+        "`coverage` must be one of {.val all}, {.val census} or {.val consistent}.",
+        class = "uscogdata_invalid_coverage", parent = e
+      )
+    }
+  )
+}
+
+#' Restrict `years` to census years for `coverage = "census"`.
+#'
+#' Aborts rather than returning an empty result when the requested range holds
+#' no census year: silently handing back zero rows for a query the caller
+#' believes they made is the failure mode this whole issue is about.
+#' @noRd
+.apply_census_years <- function(years, coverage, verb) {
+  if (!identical(coverage, "census")) return(as.integer(years))
+  keep <- as.integer(years)[.is_census_year(years)]
+  if (length(keep) == 0L) {
+    cli::cli_abort(c(
+      "{.code coverage = \"census\"} leaves no years to query.",
+      x = "None of the requested years end in 2 or 7: {.val {sort(unique(as.integer(years)))}}.",
+      i = "Census of Governments years ending in 2 or 7 are complete censuses; all others are samples.",
+      i = "Use {.code coverage = \"all\"} (the default) to keep every requested year, or request a census year."
+    ), class = "uscogdata_no_census_years")
+  }
+  sort(keep)
+}
+
+#' Keep only units that report in EVERY requested year (a balanced panel).
+#'
+#' `id_col` is the government identifier; `keep_ids` are rows exempt from the
+#' filter (the peer-comparison target, which is the subject of the comparison
+#' rather than a member of the cohort being balanced).
+#' @noRd
+.filter_consistent <- function(result, years, id_col = "canonical_govid",
+                               keep_ids = character(0)) {
+  years <- unique(as.integer(years))
+  if (nrow(result) == 0L || length(years) <= 1L) return(result)
+  ids <- setdiff(unique(result[[id_col]]), c(NA, keep_ids))
+  present <- vapply(ids, function(g) {
+    all(years %in% unique(as.integer(result$year[result[[id_col]] == g])))
+  }, logical(1))
+  consistent <- c(ids[present], keep_ids)
+  result[result[[id_col]] %in% consistent | is.na(result[[id_col]]), ,
+         drop = FALSE]
+}
+
+#' Per-year coverage metadata, always attached regardless of mode.
+#'
+#' Built from the REQUESTED years rather than the years present in the result,
+#' so a year in which nothing reported still appears -- with
+#' `n_units_reporting = 0`, which is precisely the disclosure a silently
+#' missing year fails to make.
+#'
+#' `n_units_reporting` describes the result the caller actually received, so
+#' under `coverage = "consistent"` it reports the balanced count. `is_census_year`
+#' is a statement about the SURVEY CALENDAR, never a claim of completeness:
+#' FY1967 is a census year in which only 97 of Wisconsin's 608 cities report.
+#' `n_units_reporting` is the number that tells the truth.
+#' @noRd
+.coverage_table <- function(result, years, n_expected,
+                            id_col = "canonical_govid", rows = NULL) {
+  years <- sort(unique(as.integer(years)))
+  src <- if (is.null(rows)) result else rows
+  reporting <- vapply(years, function(y) {
+    ids <- src[[id_col]][as.integer(src$year) == y]
+    length(unique(ids[!is.na(ids)]))
+  }, integer(1))
+  tibble::tibble(
+    year              = years,
+    n_units_reporting = as.integer(reporting),
+    n_units_expected  = rep(as.integer(n_expected), length(years)),
+    is_census_year    = .is_census_year(years)
+  )
+}

R/explain.R

@@ -60,7 +60,20 @@ cog_explain <- function(result, format = c("print", "list")) {
     cli::cli_text("Basis: {prov$basis}{note}")
   }
 
-  if (!is.null(prov$expenditure_concept)) {
+  # Each verb reports its OWN concept. Both fields are always present (each
+  # defaults to its concept's default), so printing `expenditure_concept`
+  # unconditionally would tell a cog_revenue() caller "Concept: primary",
+  # which names a spending concept their result has nothing to do with.
+  if (identical(prov$verb, "cog_revenue")) {
+    if (!is.null(prov$revenue_concept)) {
+      cli::cli_text("Concept: {prov$revenue_concept} revenue")
+    }
+  } else if (identical(prov$verb, "cog_balances")) {
+    # Both concept fields are deliberately NA here (a stock has no flow
+    # concept). Printing the raw NA reads as a missing value rather than an
+    # intentional one, so say what it means instead.
+    cli::cli_text("Concept: not applicable (holdings are a stock, not a flow)")
+  } else if (!is.null(prov$expenditure_concept)) {
     concept_note <- if (!is.null(prov$expenditure_concept_note) &&
                          !is.na(prov$expenditure_concept_note)) {
       sprintf(" (%s)", prov$expenditure_concept_note)
@@ -118,11 +131,79 @@ cog_explain <- function(result, format = c("print", "list")) {
     cli::cli_ul(sugg_lines)
   }
 
+  if (!is.null(prov$coverage) && nrow(prov$coverage) > 0L) {
+    cli::cli_h2("Reporting coverage")
+    cli::cli_text("Mode: {prov$coverage_mode %||% 'all'}")
+    cov <- prov$coverage
+    cli::cli_ul(sprintf(
+      "%d: %d of %d units reporting (%.0f%%) -- %s year",
+      cov$year, cov$n_units_reporting, cov$n_units_expected,
+      100 * cov$n_units_reporting / pmax(cov$n_units_expected, 1L),
+      ifelse(cov$is_census_year, "census", "sample")
+    ))
+    if (any(!cov$is_census_year)) {
+      cli::cli_text(
+        "Note: the Census of Governments is a complete census only in years ending in 2 or 7; every other year is a sample."
+      )
+    }
+  }
+
+  if (isTRUE(prov$completion$applied)) {
+    cli::cli_h2("Completion")
+    cli::cli_text(
+      "Filled {prov$completion$rows_filled} absent cell(s) from the corpus code set."
+    )
+    rules <- prov$completion$absence_means
+    if (length(rules) > 0L) {
+      cli::cli_ul(vapply(names(rules), function(y) {
+        sprintf("%s: an absent cell means %s", y,
+                if (identical(rules[[y]], "census_zero")) {
+                  "Census published $0 (filled as 0)"
+                } else {
+                  "the government did not report (filled as NA, not 0)"
+                })
+      }, character(1)))
+    }
+  }
+
   if (length(prov$series_break_refs) > 0L) {
     cli::cli_h2("Series breaks")
     cli::cli_ul(.series_break_story_lines(prov$series_break_refs))
   }
 
+  # Kept in a section of its own: these qualify the whole result, so folding
+  # them in with the per-code breaks above would invite reading them as a
+  # caveat about one series.
+  if (length(prov$corpus_break_refs) > 0L) {
+    cli::cli_h2("Corpus-wide caveats")
+    cli::cli_ul(.series_break_story_lines(prov$corpus_break_refs))
+  }
+
+  # Balance results only (NULL on money-verb provenance, so they are
+  # unaffected). This is the ONLY on-demand surface for the GAAP disclosure:
+  # .emit_balance_caveats() fires at most once per session, and is routinely
+  # consumed by a suppressMessages() call or by a knitted chunk nobody reads,
+  # so a caller who deliberately audits a result with cog_explain() must still
+  # be told.
+  bc <- prov$balance_caveats
+  if (!is.null(bc)) {
+    cli::cli_h2("Holdings caveats")
+    if (!is.null(bc$not_gaap_note)) cli::cli_alert_warning(bc$not_gaap_note)
+    if (length(bc$truncated) > 0L) {
+      cli::cli_text(
+        "Requested years extend beyond what these families actually cover:"
+      )
+      cli::cli_ul(vapply(bc$truncated, function(s) {
+        w <- bc$coverage_window[[s]]
+        if (length(w) == 2L) {
+          sprintf("%s: covered %s-%s in this corpus", s, w[1], w[2])
+        } else {
+          s
+        }
+      }, character(1)))
+    }
+  }
+
   cli::cli_h2("Transformations")
   uc <- prov$transformations$units_conversion
   if (isTRUE(uc$applied)) {

R/manifest.R

@@ -138,7 +138,7 @@
 #' year, matching canonical_fips_xwalk) rather than as-of-year; as-of-year
 #' moved to the *_asof columns. This package's own geography always came from
 #' the xwalk (already present-based), so behaviour is unchanged.
-.validate_schema <- function(manifest, supported = c(4L, 5L, 6L)) {
+.validate_schema <- function(manifest, supported = c(4L, 5L, 6L, 7L)) {
   if (!manifest$schema_version %in% supported) {
     cli::cli_abort(c(
       "Corpus schema version mismatch.",

R/peers.R

@@ -19,6 +19,13 @@
 #'   target's population at `year` to produce absolute bounds. If `FALSE`,
 #'   `pop_range` is interpreted as absolute population counts.
 #' @param max_peers Integer cap on the number of peers returned.
+#' @param coverage Survey-cycle handling; see [cog_peer_compare()]. Here it
+#'   governs the cohort VINTAGE when `year` is `NULL`: `"census"` snaps to the
+#'   most recent census year with an observed population, so a cohort is not
+#'   built from a sample year in which most of the candidate universe is
+#'   absent. `"consistent"` needs a year range, which cohort selection does not
+#'   have, so it selects like `"all"` and is carried on the result as
+#'   `attr(x, "coverage")` for [cog_peer_compare()].
 #' @return Tibble with columns `canonical_govid`, `gov_name`, `fips_state`,
 #'   `population`, `pop_ratio`, `rank`. The cohort year is attached as
 #'   `attr(x, "cohort_year")`.
@@ -29,7 +36,9 @@ cog_find_peers <- function(target_govid,
                            same_state = FALSE,
                            pop_range = c(0.7, 1.3),
                            is_ratio = TRUE,
-                           max_peers = 10L) {
+                           max_peers = 10L,
+                           coverage = c("all", "census", "consistent")) {
+  coverage <- .validate_coverage(coverage)
   if (!is.character(target_govid) || length(target_govid) != 1L) {
     cli::cli_abort("`target_govid` must be a length-1 character string.")
   }
@@ -59,7 +68,7 @@ cog_find_peers <- function(target_govid,
     ))
   }
 
-  cohort_year <- .resolve_cohort_year(con, target_govid, year)
+  cohort_year <- .resolve_cohort_year(con, target_govid, year, coverage)
 
   pop_sql <- sprintf(
     "SELECT population FROM gov_population_yearly
@@ -107,12 +116,34 @@ cog_find_peers <- function(target_govid,
   attr(peers, "cohort_year") <- as.integer(cohort_year)
   attr(peers, "pop_range")   <- as.numeric(pop_range)
   attr(peers, "is_ratio")    <- isTRUE(is_ratio)
+  attr(peers, "coverage")    <- coverage
+  attr(peers, "is_census_year") <- .is_census_year(cohort_year)
   peers
 }
 
+# `coverage` picks the cohort vintage when the caller did not name one.
+# "census" snaps to the most recent CENSUS year with an observed population,
+# so a cohort is not silently built from a sample year in which most of the
+# candidate universe is absent. "consistent" is a comparison-time concept --
+# it needs a year RANGE, which cohort selection does not have -- so it selects
+# like "all" here and is carried on the result for cog_peer_compare().
 #' @noRd
-.resolve_cohort_year <- function(con, target_govid, year) {
+.resolve_cohort_year <- function(con, target_govid, year,
+                                 coverage = "all") {
   if (!is.null(year)) return(as.integer(year))
+  if (identical(coverage, "census")) {
+    sql <- sprintf(
+      "SELECT MAX(year) AS y FROM gov_population_yearly
+       WHERE canonical_govid = %s AND year %% 10 IN (2, 7)",
+      .sql_lit_chr(target_govid)
+    )
+    y <- DBI::dbGetQuery(con, sql)$y
+    if (length(y) > 0L && !is.na(y)) return(as.integer(y))
+    cli::cli_abort(c(
+      "{.code coverage = \"census\"} found no census year with an observed population for {target_govid}.",
+      i = "Pass an explicit {.arg year}, or use {.code coverage = \"all\"}."
+    ), class = "uscogdata_no_census_years")
+  }
   sql <- sprintf(
     "SELECT MAX(year) AS y FROM gov_population_yearly
      WHERE canonical_govid = %s",
@@ -133,7 +164,9 @@ cog_find_peers <- function(target_govid,
 #' [cog_find_peers()] result or a character vector of `canonical_govid`) and
 #' appends peer-distribution summary rows (`summary_p25`, `summary_p50`,
 #' `summary_p75`) so the result can be faceted by `role` in a single ggplot
-#' call.
+#' call. Those summary rows are quantiles **within each category**, not
+#' quantiles of each peer's total — see the `@return` section before summing
+#' them.
 #'
 #' @param target_govid Character scalar.
 #' @param peers A tibble from [cog_find_peers()] or a character vector of
@@ -143,10 +176,36 @@ cog_find_peers <- function(target_govid,
 #' @param per_capita Default `TRUE` — peer compare usually normalizes by
 #'   population.
 #' @param adjust_to_year Integer base year for CPI-U conversion or `NULL`.
-#' @param expenditure_concept `"direct"` (default) or `"total"`. Currently only
-#'   `"direct"` is accepted; the `"total"` option exists in [cog_spending()] for
-#'   single-government queries but cannot be used here because combining Total
-#'   across peer sets counts intergovernmental transfers twice.
+#' @param expenditure_concept `"primary"` (default), `"direct"`, or
+#'   `"total"` -- see [cog_spending()] for the three concepts. `"total"` is
+#'   refused here because combining Total across peer sets counts
+#'   intergovernmental transfers twice; `"primary"` and `"direct"` combine
+#'   safely.
+#' @param coverage How to handle the Census of Governments survey cycle,
+#'   which is a **complete census only in years ending in 2 and 7** -- every
+#'   other year is a sample, and the sample varies enormously (on the bundled
+#'   fixture, Wisconsin's 608-city universe reports 597 governments in FY2012
+#'   and 112 in FY2019).
+#'
+#'   * `"all"` (default) -- every unit that reported that year. Unchanged
+#'     behaviour, so existing code keeps working.
+#'   * `"census"` -- census years only. Aborts if the requested range holds
+#'     none, rather than silently returning nothing.
+#'   * `"consistent"` -- only units reporting in *every* requested year, giving
+#'     a balanced panel.
+#'
+#'   Regardless of mode, `provenance$coverage` always carries per-year
+#'   `n_units_reporting`, `n_units_expected` and `is_census_year`, and
+#'   `provenance$coverage_mode` records the mode. `is_census_year` is a
+#'   statement about the **survey calendar**, never a claim of completeness:
+#'   FY1967 is a census year in which only 97 of Wisconsin's 608 cities
+#'   report. `n_units_reporting` is the number that tells the truth.
+#'
+#'   The comparison target is exempt from `"consistent"` balancing -- it is the
+#'   subject of the comparison, not a member of the cohort -- and the
+#'   `summary_*` quantiles are computed AFTER the filter, so they describe the
+#'   cohort actually returned. `n_units_reporting` counts peers only, against
+#'   the cohort size: "3 of your 15 peers reported in FY2019".
 #' @return Tibble matching [cog_spending()]'s columns, plus a `role`
 #'   column taking values `"target"`, `"peer"`, `"summary_p25"`,
 #'   `"summary_p50"`, or `"summary_p75"`, `target_rank` (target's rank
@@ -155,12 +214,40 @@ cog_find_peers <- function(target_govid,
 #'   `attr(peers, "cohort_year")`; `NA` when `peers` was a bare character
 #'   vector). Provenance reports `verb = "cog_peer_compare"`, `peer_count`,
 #'   `cohort_year`, and `cohort_govids`.
+#'
+#'   **The `summary_*` rows are per-category quantiles: they are not additive.**
+#'   Each one is computed **within each `(year, spend_subtype,
+#'   category)` cell** across the peer set, so a `summary_p50` row is *the
+#'   median peer's value in that one category*, not *the value of the median
+#'   peer's total*. The median peer for Police and the median peer for Fire
+#'   are usually different governments, so summing `summary_*` rows across
+#'   categories does not give any peer's total and misstates the band it
+#'   appears to describe — measured at −32.7% to +251.0% across 24 years on
+#'   one cohort, with a sign flip at FY2012.
+#'
+#'   Facet by `role` **and** `category` (the documented use, and what the
+#'   rows are built for). For a genuine "median peer's total spending" line,
+#'   sum each peer's own categories first and take the quantile of those
+#'   per-government totals:
+#'
+#'   ```r
+#'   library(dplyr)
+#'   cmp |>
+#'     filter(role %in% c("target", "peer")) |>
+#'     group_by(year, role, canonical_govid) |>
+#'     summarise(total = sum(amt_per_capita_real, na.rm = TRUE), .groups = "drop") |>
+#'     filter(role == "peer") |>
+#'     group_by(year) |>
+#'     summarise(p50 = quantile(total, 0.5, na.rm = TRUE))
+#'   ```
 #' @export
 cog_peer_compare <- function(target_govid, peers, category, years,
                              per_capita = TRUE, adjust_to_year = NULL,
-                             expenditure_concept = c("direct", "total")) {
+                             expenditure_concept = c("primary", "direct", "total"),
+                             coverage = c("all", "census", "consistent")) {
   call <- match.call()
   expenditure_concept <- match.arg(expenditure_concept)
+  coverage <- .validate_coverage(coverage)
   if (identical(expenditure_concept, "total")) {
     .abort_concept_not_aggregatable("cog_peer_compare")
   }
@@ -183,9 +270,21 @@ cog_peer_compare <- function(target_govid, peers, category, years,
   peer_govids <- peer_govids[!is.na(peer_govids) & nzchar(peer_govids)]
   all_govids <- unique(c(target_govid, peer_govids))
 
-  r <- cog_spending(all_govids, years, category, per_capita, adjust_to_year)
+  years <- .apply_census_years(years, coverage, "cog_peer_compare")
+
+  r <- cog_spending(all_govids, years, category, per_capita, adjust_to_year,
+                    expenditure_concept = expenditure_concept)
   r$role <- ifelse(r$canonical_govid == target_govid, "target", "peer")
 
+  # The target is exempt from balancing: it is the subject of the comparison,
+  # not a member of the cohort being balanced, and dropping it would leave a
+  # peer comparison with nothing to compare. Filtering happens BEFORE the
+  # quantiles below, so a "consistent" cohort's summary rows describe that
+  # cohort rather than the unbalanced one.
+  if (identical(coverage, "consistent")) {
+    r <- .filter_consistent(r, years, keep_ids = target_govid)
+  }
+
   value_col <- .peer_value_col(per_capita, adjust_to_year)
 
   summary_rows <- .peer_summary_rows(r, value_col)
@@ -206,6 +305,14 @@ cog_peer_compare <- function(target_govid, peers, category, years,
     canonical_govid = target_govid,
     gov_name        = unique(r$gov_name[r$role == "target"])
   )
+  # Counted over PEER rows only, against the cohort size: "3 of your 15 peers
+  # reported in FY2019". Including the target would inflate every count by one
+  # and make a cohort that has entirely stopped reporting look non-empty.
+  prov$coverage_mode <- coverage
+  prov$coverage <- .coverage_table(
+    out, years, length(peer_govids),
+    rows = r[r$role == "peer", , drop = FALSE]
+  )
   attr(out, "provenance") <- prov
   out
 }

R/provenance.R

@@ -6,11 +6,13 @@
                               per_capita, adjust_to_year, result, sql,
                               subtype_col, basis = NA_character_,
                               basis_note = NA_character_,
-                              expenditure_concept = "direct",
+                              expenditure_concept = "primary",
                               expenditure_concept_note = NA_character_,
                               expenditure_concept_direct_suppressed = FALSE,
+                              revenue_concept = "general",
                               harmonization = NULL, recipe = NULL,
-                              suggestions = list()) {
+                              suggestions = list(),
+                              completion = NULL) {
   manifest <- .uscogdata_env$manifest
 
   codes <- result[["codes_included"]]
@@ -37,11 +39,20 @@
 
   schema_version <- suppressWarnings(as.integer(manifest$schema_version %||% 0L))
   con <- .uscogdata_env$con
-  break_refs <- if (!is.null(con) && DBI::dbIsValid(con)) {
+  have_con <- !is.null(con) && DBI::dbIsValid(con)
+  break_refs <- if (have_con) {
     .build_series_break_refs(con, codes_observed, years, schema_version)
   } else {
     character(0)
   }
+  # Corpus-wide caveats travel separately: they qualify the whole result
+  # rather than one series, and they do not depend on codes_observed (see
+  # .build_corpus_break_refs()).
+  corpus_refs <- if (have_con) {
+    .build_corpus_break_refs(con, years, schema_version)
+  } else {
+    character(0)
+  }
 
   list(
     verb = verb,
@@ -57,6 +68,7 @@
     expenditure_concept = expenditure_concept,
     expenditure_concept_note = expenditure_concept_note,
     expenditure_concept_direct_suppressed = isTRUE(expenditure_concept_direct_suppressed),
+    revenue_concept = revenue_concept,
     harmonization = harmonization %||% list(
       applied = FALSE, na_rows_excluded = 0L, na_amount_excluded = 0,
       note = NA_character_
@@ -116,6 +128,14 @@
       )
     ),
     series_break_refs = break_refs,
+    corpus_break_refs = corpus_refs,
+    # What `complete = TRUE` filled, and the rule it filled by. Always
+    # present so a consumer can read `completion$applied` without testing
+    # for the key -- an absent block and applied = FALSE would otherwise be
+    # indistinguishable from an older reader version.
+    completion = completion %||% list(
+      applied = FALSE, rows_filled = 0L, absence_means = list()
+    ),
     manifest = list(
       schema_version = as.integer(manifest$schema_version),
       pipeline_commit = manifest$pipeline_commit %||% NA_character_,

R/revenue.R

@@ -8,14 +8,46 @@
 #' multiplies by 1000 and records the conversion in `provenance`).
 #'
 #' @inheritParams cog_spending
+#' @param revenue_concept Which of Census's two published revenue concepts to
+#'   return. Concepts are defined as sets of the crosswalk's `revenue_subtype`
+#'   values -- never as item-code first letters, which cannot classify
+#'   correctly (prefix `Y` spans revenue, expenditure and balance codes, and
+#'   prefix `X` does the same):
+#'
+#'   * `"general"` (default) -- Census General Revenue: `own_source` +
+#'     `federal` + `state` + `local_aid`. The manual defines this concept by
+#'     subtraction (section 4.3: *"General revenue comprises all revenue
+#'     except that classified as liquor store, utility, or insurance trust
+#'     revenue"*), so utility (`A91`-`A94`), liquor store (`A90`) and
+#'     insurance trust revenue are all excluded.
+#'   * `"total"` -- Census Total Revenue: every revenue subtype, i.e.
+#'     `general` plus utility, liquor store, and insurance trust revenue
+#'     (`Y01`/`Y02`/`Y04`/`Y11`/`Y12`/`Y51`/`Y52` and the employee-retirement
+#'     `X01`/`X02`/`X05`/`X08`).
+#'
+#'   The two are related by Census's own identity, `Total Revenue = General +
+#'   Utility + Liquor Store + Insurance Trust`.
+#'
+#'   Note that the employee-retirement (`X`) codes stop at FY2016, when those
+#'   systems moved out of the annual finance file into the separate Annual
+#'   Survey of Public Pensions, so a `"total"` series steps down at the
+#'   FY2016/FY2017 seam for reasons that are about collection scope rather
+#'   than revenue (series breaks `SB197`-`SB202`).
 #' @return Tibble with columns `year`, `canonical_govid`, `gov_name`,
 #'   `revenue_subtype`, `category`, `amt_nominal`, optional `amt_real`,
 #'   optional `amt_per_capita_nominal`, optional `amt_per_capita_real`,
-#'   optional `pop_source`, `codes_included`, `aggregate_fallback`, `notes`.
+#'   optional `pop_source`, `codes_included`, `aggregate_fallback`, `notes`,
+#'   and `value_source` when `complete = TRUE`.
 #' @export
 cog_revenue <- function(govid, years, category = NULL,
                         per_capita = FALSE, adjust_to_year = NULL,
-                        basis = c("harmonized", "raw"), recipe = NULL) {
+                        basis = c("harmonized", "raw"), recipe = NULL,
+                        revenue_concept = c("general", "total"),
+                        complete = FALSE) {
+  # flow_prefixes no longer classifies rows (crosswalk revenue_subtype
+  # membership does -- General Revenue, i.e. everything except
+  # insurance_trust) -- it only scopes the recipe-suggestion machinery to
+  # this verb's recipe families (see R/suggestions.R).
   .verb_spendrev(
     verb           = "cog_revenue",
     view_base      = "revenue_annotated",
@@ -28,6 +60,8 @@ cog_revenue <- function(govid, years, category = NULL,
     per_capita     = per_capita,
     adjust_to_year = adjust_to_year,
     basis          = basis,
-    recipe         = recipe
+    recipe         = recipe,
+    revenue_concept = revenue_concept,
+    complete       = complete
   )
 }

R/rollup.R

@@ -25,12 +25,31 @@
 #'   population from `gov_population_yearly`. Govs with missing population
 #'   are excluded from the result.
 #' @param adjust_to_year Integer base year for CPI-U conversion, or `NULL`.
-#' @param expenditure_concept `"direct"` (default) or `"total"`. Currently only
-#'   `"direct"` is accepted; the `"total"` option exists in [cog_spending()] for
-#'   single-government queries but cannot be used here because combining Total
-#'   across multiple layers of government double-counts intergovernmental
-#'   transfers (a state's payment to a school district is the same dollar the
-#'   district reports as its own Direct spending).
+#' @param expenditure_concept `"primary"` (default), `"direct"`, or
+#'   `"total"` -- see [cog_spending()] for the three concepts. `"total"` is
+#'   refused here because combining Total across multiple layers of
+#'   government double-counts intergovernmental transfers (a state's payment
+#'   to a school district is the same dollar the district reports as its own
+#'   Direct spending); `"primary"` and `"direct"` combine safely.
+#' @param coverage How to handle the Census of Governments survey cycle,
+#'   which is a **complete census only in years ending in 2 and 7** -- every
+#'   other year is a sample, and the sample varies enormously (on the bundled
+#'   fixture, Wisconsin's 608-city universe reports 597 governments in FY2012
+#'   and 112 in FY2019).
+#'
+#'   * `"all"` (default) -- every unit that reported that year. Unchanged
+#'     behaviour, so existing code keeps working.
+#'   * `"census"` -- census years only. Aborts if the requested range holds
+#'     none, rather than silently returning nothing.
+#'   * `"consistent"` -- only units reporting in *every* requested year, giving
+#'     a balanced panel.
+#'
+#'   Regardless of mode, `provenance$coverage` always carries per-year
+#'   `n_units_reporting`, `n_units_expected` and `is_census_year`, and
+#'   `provenance$coverage_mode` records the mode. `is_census_year` is a
+#'   statement about the **survey calendar**, never a claim of completeness:
+#'   FY1967 is a census year in which only 97 of Wisconsin's 608 cities
+#'   report. `n_units_reporting` is the number that tells the truth.
 #' @return Tibble with columns `year`, `layer`, `canonical_govid`, `gov_name`,
 #'   `spend_subtype`, `category`, `amt_nominal`, optional `amt_real` /
 #'   `amt_per_capita_nominal` / `amt_per_capita_real`, optional `pop_source`,
@@ -40,9 +59,11 @@
 #' @export
 cog_geographic_rollup <- function(govids, category, years,
                                   per_capita = FALSE, adjust_to_year = NULL,
-                                  expenditure_concept = c("direct", "total")) {
+                                  expenditure_concept = c("primary", "direct", "total"),
+                                  coverage = c("all", "census", "consistent")) {
   call <- match.call()
   expenditure_concept <- match.arg(expenditure_concept)
+  coverage <- .validate_coverage(coverage)
   if (identical(expenditure_concept, "total")) {
     .abort_concept_not_aggregatable("cog_geographic_rollup")
   }
@@ -59,11 +80,21 @@ cog_geographic_rollup <- function(govids, category, years,
     layer           = rep(layer_names, lengths(govids))
   )
 
-  r <- cog_spending(all_govids, years, category, per_capita, adjust_to_year)
+  # coverage = "census" drops non-census years BEFORE the query rather than
+  # after: a sample year's rows are not wanted at all, and fetching them only
+  # to discard them would also let them into the coverage table.
+  years <- .apply_census_years(years, coverage, "cog_geographic_rollup")
+
+  r <- cog_spending(all_govids, years, category, per_capita, adjust_to_year,
+                    expenditure_concept = expenditure_concept)
   r <- dplyr::left_join(r, layer_map, by = "canonical_govid",
                         relationship = "many-to-many")
   r$scope_note <- .rollup_scope_note(r$layer)
 
+  if (identical(coverage, "consistent")) {
+    r <- .filter_consistent(r, years)
+  }
+
   excluded <- character(0)
   if (isTRUE(per_capita) && "pop_source" %in% names(r)) {
     drop <- r$pop_source == "unavailable"
@@ -82,6 +113,11 @@ cog_geographic_rollup <- function(govids, category, years,
     included_govids = included,
     excluded_govids = excluded
   )
+  # n_units_expected is the universe the CALLER named -- the govids passed in
+  # -- not the national universe. That is what makes the ratio meaningful:
+  # "597 of the 608 Wisconsin cities you asked about reported in FY2012".
+  prov$coverage_mode <- coverage
+  prov$coverage <- .coverage_table(r, years, length(unique(all_govids)))
   attr(r, "provenance") <- prov
 
   r

R/search.R

@@ -6,8 +6,11 @@
 #' the cross-vintage canonical-government registry. Operates in two modes:
 #'
 #' * **Utility mode** (single `name`, the original behavior): returns all
-#'   rows whose `gov_name` matches the regex case-insensitively, sorted by
-#'   `population_acs` descending. Useful for exploratory lookups.
+#'   rows whose `gov_name` contains `name` as a **literal, case-insensitive
+#'   substring**, sorted by `population_acs` descending. Useful for
+#'   exploratory lookups. Regex metacharacters in `name` are escaped, so a
+#'   government is findable by its own complete name even when that name
+#'   contains parentheses or a period.
 #' * **Basket mode** (`length(name) > 1`): resolves each input row to a
 #'   single canonical govid and returns a tibble in input order, suitable
 #'   for piping straight into [cog_spending()] / [cog_revenue()] /
@@ -19,7 +22,8 @@
 #' 1. Filter `canonical_fips_xwalk` by `state` and (if non-NA) `type`.
 #' 2. **Exact pass:** case-insensitive equality against `gov_name`.
 #'    Single hit -> resolved. Multiple -> step 4.
-#' 3. **Substring fallback:** case-insensitive regex against `gov_name`.
+#' 3. **Substring fallback:** case-insensitive literal substring against
+#'    `gov_name` (metacharacters escaped).
 #'    Single hit -> resolved (`match_method = "substring"`). Zero hits ->
 #'    `status = "no_match"`. Multiple hits -> step 4.
 #' 4. **Disambiguation:** if matches share one `govs_type`, pick the
@@ -48,7 +52,7 @@
 #'   [cog_spending()], [cog_revenue()].
 #' @examples
 #' \dontrun{
-#' # Utility mode — exploratory regex lookup
+#' # Utility mode — exploratory substring lookup
 #' cog_gov_search("broward", state = "FL")
 #'
 #' # Basket mode — resolve a known cohort
@@ -98,9 +102,16 @@ cog_gov_search <- function(name = NULL, state = NULL, type = NULL) {
     if (!is.character(name) || length(name) != 1L) {
       cli::cli_abort("`name` must be a length-1 character string.")
     }
+    # Escaped, so `name` is a literal case-insensitive substring -- the same
+    # treatment basket mode has always given it. Interpolating it raw made a
+    # government unfindable by its own name whenever that name contains a
+    # metacharacter (FREDONIA (BRISCOE) CITY), turned a bare "." into a
+    # match-everything wildcard, and let malformed pattern text reach the
+    # engine as an error -- which cog-api surfaced as a 500, reachable by
+    # typing a real name one character at a time (uscogdata#16, F-025).
     preds <- c(preds,
                sprintf("regexp_matches(gov_name, %s, 'i')",
-                       .sql_lit_chr(name)))
+                       .sql_lit_chr(.escape_regex(name))))
   }
   if (!is.null(state)) {
     st_fips <- .coerce_state_to_fips(state)
@@ -136,8 +147,9 @@ cog_gov_search <- function(name = NULL, state = NULL, type = NULL) {
 #' @noRd
 .escape_regex <- function(x) {
   # Backslash-escape POSIX regex metacharacters so `name` is treated as a
-  # literal substring in the DuckDB regexp_matches call (substring fallback
-  # only; utility-mode intentionally preserves regex behavior).
+  # literal substring in the DuckDB regexp_matches call. Used by BOTH modes:
+  # utility mode used to interpolate raw, which was a defect rather than a
+  # feature -- see the call site and uscogdata#16.
   gsub("([\\^$.|?*+(){}\\[\\]])", "\\\\\\1", x, perl = TRUE)
 }
 

R/series_breaks.R

@@ -14,9 +14,41 @@
   sql <- sprintf(
     "SELECT DISTINCT break_id
      FROM series_breaks_pq
-     WHERE fin_code IN (%s) AND break_year BETWEEN %d AND %d
+     WHERE fin_code IN (%s) AND fin_code <> 'ALL'
+       AND break_year BETWEEN %d AND %d
      ORDER BY break_id",
     .sql_lit_chr(codes_observed), min(as.integer(years)), max(as.integer(years))
   )
   DBI::dbGetQuery(con, sql)$break_id
 }
+
+#' Corpus-wide caveats: catalogued breaks whose `fin_code` is the literal
+#' `"ALL"` rather than an item code. They qualify the whole result, so they
+#' cannot be matched the way `.build_series_break_refs()` matches -- no row's
+#' `item_code` is ever `"ALL"`, which is exactly why they reached no user
+#' before uscogdata#19. Selection is on the break_year window alone: which
+#' codes a result happens to contain is irrelevant to a caveat about the
+#' corpus.
+#'
+#' All four catalogued entries are *boundary* caveats (dollar precision
+#' across 1976/1977, imputation exclusion from 2002, the dense -> sparse
+#' representation change at 2012, the id scheme change at 2017), so the same
+#' `break_year BETWEEN min(years) AND max(years)` rule the code-specific
+#' path uses is the right one -- a request that never crosses the boundary
+#' is not affected by it.
+#'
+#' Returned separately from `series_break_refs` so a consumer can tell a
+#' whole-result caveat from a break in one series; the two are disjoint by
+#' construction.
+#' @noRd
+.build_corpus_break_refs <- function(con, years, schema_version) {
+  if (schema_version < 5L || length(years) == 0L) return(character(0))
+  sql <- sprintf(
+    "SELECT DISTINCT break_id
+     FROM series_breaks_pq
+     WHERE fin_code = 'ALL' AND break_year BETWEEN %d AND %d
+     ORDER BY break_id",
+    min(as.integer(years)), max(as.integer(years))
+  )
+  DBI::dbGetQuery(con, sql)$break_id
+}

R/session.R

@@ -12,7 +12,7 @@ cog_open <- function(url = .resolve_url(),
   DBI::dbExecute(con, "INSTALL httpfs; LOAD httpfs;")
 
   manifest <- .fetch_or_cache_manifest(url, cache_dir)
-  .validate_schema(manifest, supported = c(4L, 5L, 6L))
+  .validate_schema(manifest, supported = c(4L, 5L, 6L, 7L))
   .validate_scope(manifest)
 
   .register_views(con, url, manifest)
@@ -95,4 +95,7 @@ cog_close <- function() {
   }
   .uscogdata_env$con      <- NULL
   .uscogdata_env$manifest <- NULL
+  .uscogdata_env$balance_caveats_shown <- NULL
+  # Memoised corpus-constant; a different corpus may be mounted next.
+  .uscogdata_env$balance_coverage_windows <- NULL
 }

R/spending.R

@@ -1,5 +1,57 @@
 # R/spending.R
 
+# The three expenditure concepts (uscogdata#11), as sets of the crosswalk's
+# `spend_subtype` values. Classification is crosswalk membership, never
+# item-code first letters: prefix Y alone spans revenue (Y01/Y02),
+# expenditure (Y05/Y06) and balance codes, so no first-letter allowlist can
+# route it (finding F-018).
+#
+#   primary = operations + capital + assistance           (the default)
+#   direct  = primary + interest + insurance_benefits     (Census Direct Expenditure)
+#   total   = direct + intergovernmental                  (via the ig_* views)
+#
+# Census manual section 5.2.2.1: Direct Expenditure is ALL expenditure other
+# than intergovernmental -- including payments to retirees, i.e. insurance
+# trust benefits. Verified against Census's own published FY2020 state
+# aggregates (20statetypepu.txt): `total` reproduces the published
+# expenditure sum to the dollar; omitting insurance benefits understates
+# California's Direct by 10.9%.
+.spend_subtypes_primary <- c("operations", "capital", "assistance")
+.spend_subtypes_direct  <- c(.spend_subtypes_primary, "interest", "insurance_benefits")
+
+#' @noRd
+.expenditure_concept_subtypes <- function(concept) {
+  switch(concept,
+    primary = .spend_subtypes_primary,
+    # "total" = the direct subtypes here PLUS the intergovernmental leg,
+    # which travels through the ig_* views rather than this scope (see
+    # .build_verb_sql()).
+    direct  = ,
+    total   = .spend_subtypes_direct
+  )
+}
+
+# The two revenue concepts (uscogdata#12), again as crosswalk subtype sets.
+# Census's manual section 4.3 defines the first by SUBTRACTING from the second
+# -- "General revenue comprises all revenue except that classified as liquor
+# store, utility, or insurance trust revenue" -- giving the identity
+#
+#   Total Revenue = General + Utility + Liquor Store + Insurance Trust
+#
+# Verified against Census's own computed concept fields (IndFin FY2012,
+# Wisconsin state): 31,410,686 + 0 + 0 + 4,469,906 = 35,880,592, exact.
+.revenue_subtypes_general <- c("own_source", "federal", "state", "local_aid")
+.revenue_subtypes_total   <- c(.revenue_subtypes_general, "utility",
+                               "liquor_store", "insurance_trust")
+
+#' @noRd
+.revenue_concept_subtypes <- function(concept) {
+  switch(concept,
+    general = .revenue_subtypes_general,
+    total   = .revenue_subtypes_total
+  )
+}
+
 #' Summarized spending by category
 #'
 #' One row per `(year, canonical_govid, spend_subtype, category)`. Amounts are
@@ -42,22 +94,34 @@
 #'   `basis = "recipe"` with an inert `harmonization` block (`applied =
 #'   FALSE`, pointing at the `recipe` block instead) rather than a
 #'   possibly-misleading `"harmonized"`/`"raw"` value.
-#' @param expenditure_concept `"direct"` (default) returns only the
-#'   government's own direct spending (item codes `E`/`F`/`G`), unchanged
-#'   from prior releases. `"total"` additionally UNIONs in the
-#'   intergovernmental leg -- payments to local governments (`M` codes) and
-#'   to the state government (`L` codes, excluding the `L--` family-total
-#'   rollup) -- so results gain rows with `spend_subtype ==
-#'   "intergovernmental"`. Requires the active corpus's `summary_categories`
-#'   to carry M/L rows (added by cog_pipeline PR #59); aborts with class
-#'   `uscogdata_ig_categories_unsupported` on an older corpus rather than
-#'   silently under-reporting. Mutually exclusive with `recipe` (a recipe
-#'   already defines its own component codes). **Do not sum `"total"`
-#'   results across levels of government** (e.g. state + county + city):
-#'   a state's `M12` payment to a school district is the same dollar the
-#'   district reports as its own direct `E12`, so summing both double-counts
-#'   it. This matters in particular with [cog_geographic_rollup()], which
-#'   sums across exactly that kind of multi-layer government set.
+#' @param expenditure_concept Which spending concept to return. Concepts are
+#'   defined as sets of the crosswalk's `spend_subtype` values -- never as
+#'   item-code first letters, which cannot classify correctly (prefix `Y`
+#'   alone spans revenue, expenditure, and balance codes):
+#'
+#'   * `"primary"` (default) -- the government's own service provision:
+#'     `operations` + `capital` + `assistance` subtypes.
+#'   * `"direct"` -- Census's published Direct Expenditure: `primary` plus
+#'     `interest` (interest on debt) and `insurance_benefits` (insurance
+#'     trust benefit payments, e.g. pensions -- Census manual section
+#'     5.2.2.1 includes payments to retirees in Direct).
+#'   * `"total"` -- `direct` plus the intergovernmental leg: payments to
+#'     local governments (`M` codes), to the state government (`L` codes,
+#'     excluding the `L--` family-total rollup), and state payments to
+#'     school systems (`Q11`/`Q12`/`Q18`), so results gain rows with
+#'     `spend_subtype == "intergovernmental"`. Requires the active corpus's
+#'     `summary_categories` to carry M/L rows (added by cog_pipeline PR
+#'     #59); aborts with class `uscogdata_ig_categories_unsupported` on an
+#'     older corpus rather than silently under-reporting. Mutually
+#'     exclusive with `recipe` (a recipe already defines its own component
+#'     codes).
+#'
+#'   **Do not sum `"total"` results across levels of government** (e.g.
+#'   state + county + city): a state's `M12` payment to a school district is
+#'   the same dollar the district reports as its own direct `E12`, so
+#'   summing both double-counts it. This matters in particular with
+#'   [cog_geographic_rollup()], which sums across exactly that kind of
+#'   multi-layer government set.
 #'
 #'   In the legacy wide era (<= FY2011), some functions are published ONLY
 #'   as an aggregate-flagged family total (e.g. Corrections' `E04`/`E05`
@@ -70,16 +134,46 @@
 #'   `provenance$expenditure_concept_direct_suppressed` is `TRUE` -- the
 #'   figure in those rows is the intergovernmental leg alone, not Direct +
 #'   IG.
+#' @param complete If `TRUE`, fill the requested grid so that a cell the
+#'   corpus does not carry still appears, labelled with **why** it is
+#'   missing, and add a `value_source` column to every row:
+#'
+#'   * `"reported"` — the corpus carries this cell.
+#'   * `"census_zero"` — dense-source year (`<= FY2011`), cell absent:
+#'     Census published `$0`. `amt_nominal` is `0`.
+#'   * `"not_reported"` — sparse-source year (`>= FY2012`), cell absent: the
+#'     government did not report, and the value is unknown. `amt_nominal` is
+#'     `NA`, **not** `0` — writing a zero there would invent data.
+#'
+#'   The grid comes from the corpus's `code_set` table, scoped to each
+#'   government's own type, so a county is never filled with cells only a
+#'   state can report. Reported rows are passed through untouched.
+#'
+#'   Defaults to `FALSE` (the historical behaviour: absent cells simply do
+#'   not appear). Needs a corpus published from 2026-07-29 onward, which is
+#'   when `representation`/`code_set` began shipping; aborts with class
+#'   `uscogdata_representation_unavailable` otherwise. Not available with
+#'   `recipe` or with `expenditure_concept = "total"` (class
+#'   `uscogdata_complete_unsupported`) — neither draws its cells from
+#'   `code_set`.
 #' @return Tibble with columns `year`, `canonical_govid`, `gov_name`,
 #'   `spend_subtype`, `category`, `amt_nominal`, optional `amt_real`,
 #'   optional `amt_per_capita_nominal`, optional `amt_per_capita_real`,
-#'   optional `pop_source`, `codes_included`, `aggregate_fallback`, `notes`.
-#'   Carries a `provenance` attribute matching `inst/schemas/provenance-v1.json`.
+#'   optional `pop_source`, `codes_included`, `aggregate_fallback`, `notes`,
+#'   and `value_source` when `complete = TRUE`.
+#'   Carries a `provenance` attribute matching `inst/schemas/provenance-v1.json`,
+#'   whose `completion` block reports `applied`, `rows_filled`, and the
+#'   per-year `absence_means` rule that was applied.
 #' @export
 cog_spending <- function(govid, years, category = NULL,
                          per_capita = FALSE, adjust_to_year = NULL,
                          basis = c("harmonized", "raw"), recipe = NULL,
-                         expenditure_concept = c("direct", "total")) {
+                         expenditure_concept = c("primary", "direct", "total"),
+                         complete = FALSE) {
+  # flow_prefixes no longer classifies rows (crosswalk subtype membership
+  # does, per expenditure_concept) -- it only scopes the recipe-suggestion
+  # machinery to this verb's recipe families (see R/suggestions.R; the
+  # catalog only has E/F/G-component direct-expenditure recipes).
   .verb_spendrev(
     verb          = "cog_spending",
     view_base     = "spending_annotated",
@@ -93,7 +187,8 @@ cog_spending <- function(govid, years, category = NULL,
     adjust_to_year = adjust_to_year,
     basis         = basis,
     recipe        = recipe,
-    expenditure_concept = expenditure_concept
+    expenditure_concept = expenditure_concept,
+    complete      = complete
   )
 }
 
@@ -101,8 +196,9 @@ cog_spending <- function(govid, years, category = NULL,
 .abort_concept_not_aggregatable <- function(verb) {
   cli::cli_abort(c(
     "{.code expenditure_concept = \"total\"} cannot be used in {.fn {verb}}.",
-    "*" = "Use {.code expenditure_concept = \"direct\"} (the default) for any \\
-           comparison or sum that spans more than one government.",
+    "*" = "Use {.code expenditure_concept = \"primary\"} (the default) or \\
+           {.code \"direct\"} for any comparison or sum that spans more than \\
+           one government.",
     "i" = "Why: Census \"Total\" is a government's own Direct spending PLUS the \\
            money it hands to other governments. The receiving government reports \\
            that same dollar again as its own Direct when it actually spends it, \\
@@ -118,23 +214,48 @@ cog_spending <- function(govid, years, category = NULL,
                            govid, years, category,
                            per_capita, adjust_to_year,
                            basis = c("harmonized", "raw"), recipe = NULL,
-                           expenditure_concept = c("direct", "total")) {
+                           expenditure_concept = c("primary", "direct", "total"),
+                           revenue_concept = c("general", "total"),
+                           complete = FALSE) {
   basis_explicit <- length(basis) == 1L
   basis <- match.arg(basis, c("harmonized", "raw"))
   # match.arg() itself throws a base `simpleError`, not an rlang-classed
   # condition; wrap it so an invalid expenditure_concept aborts consistently
   # with the rest of this package's validation (cli::cli_abort -> rlang_error).
   expenditure_concept <- tryCatch(
-    match.arg(expenditure_concept, c("direct", "total")),
+    match.arg(expenditure_concept, c("primary", "direct", "total")),
     error = function(e) {
       cli::cli_abort(
-        "`expenditure_concept` must be one of {.val direct} or {.val total}.",
+        "`expenditure_concept` must be one of {.val primary}, {.val direct}, or {.val total}.",
         class = "uscogdata_invalid_expenditure_concept",
         parent = e
       )
     }
   )
 
+  revenue_concept <- tryCatch(
+    match.arg(revenue_concept, c("general", "total")),
+    error = function(e) {
+      cli::cli_abort(
+        "`revenue_concept` must be one of {.val general} or {.val total}.",
+        class = "uscogdata_invalid_revenue_concept",
+        parent = e
+      )
+    }
+  )
+
+  # The concept's subtype scope. Every code path below -- the verb SQL, the
+  # harmonization exclusion count, and the complete = TRUE grid -- is scoped
+  # by crosswalk subtype membership, never by item-code prefix. The
+  # expenditure "total" concept's extra intergovernmental leg is the one
+  # exception: it travels through the ig_* views rather than this scope,
+  # because its legacy rows are aggregate-flagged.
+  subtype_scope <- if (identical(subtype_col, "spend_subtype")) {
+    .expenditure_concept_subtypes(expenditure_concept)
+  } else {
+    .revenue_concept_subtypes(revenue_concept)
+  }
+
   govid <- .coerce_govid_input(govid, arg = "govid")
   .validate_verb_inputs(govid, years, category, per_capita, adjust_to_year,
                         recipe)
@@ -148,10 +269,10 @@ cog_spending <- function(govid, years, category = NULL,
   }
 
   # .verb_spendrev() is shared with cog_revenue(), which never exposes
-  # expenditure_concept and always resolves it to "direct" -- so nothing on
-  # the public API can reach this today. But it's a cheap guard against a
+  # expenditure_concept and always resolves it to the default -- so nothing
+  # on the public API can reach this today. But it's a cheap guard against a
   # future call (direct or via a modified cog_revenue()) that would UNION
-  # the IG leg's expenditure M/L rows into a revenue result, which has no
+  # the IG leg's expenditure M/L/Q rows into a revenue result, which has no
   # matching IG view and no sensible meaning.
   if (identical(expenditure_concept, "total") &&
       !identical(view_base, "spending_annotated")) {
@@ -165,12 +286,27 @@ cog_spending <- function(govid, years, category = NULL,
     )
   }
 
+  complete <- isTRUE(complete)
+  if (complete && !is.null(recipe)) {
+    .abort_complete_unsupported(
+      "A recipe defines its own component codes and never goes through `summary_categories`, so there is no grid to fill from.",
+      "Query the recipe without `complete`, or use a category query with `complete = TRUE`."
+    )
+  }
+  if (complete && identical(expenditure_concept, "total")) {
+    .abort_complete_unsupported(
+      "The intergovernmental leg deliberately keeps aggregate-flagged rows (see `inst/sql/24-ig_long.sql`), so its cells are not the ones `code_set` describes.",
+      "Use `expenditure_concept = \"direct\"` with `complete = TRUE`, or drop `complete`."
+    )
+  }
+
   years   <- as.integer(years)
   if (!is.null(adjust_to_year)) adjust_to_year <- as.integer(adjust_to_year)
 
   con <- .ensure_session()
   manifest <- .uscogdata_env$manifest
   scope <- .check_govids_in_scope(govid)
+  if (complete) .require_representation(con, manifest)
 
   resolved <- .resolve_basis(basis, basis_explicit, manifest)
 
@@ -197,10 +333,23 @@ cog_spending <- function(govid, years, category = NULL,
     } else {
       NULL
     }
-    sql <- .build_verb_sql(view, subtype_col, govid, years, category, ig_view)
+    sql <- .build_verb_sql(view, subtype_col, govid, years, category, ig_view,
+                           subtype_scope)
     result <- tibble::as_tibble(DBI::dbGetQuery(con, sql))
   }
 
+  # Fill BEFORE per_capita / inflation so the added cells get the same
+  # treatment as reported ones: a census_zero stays $0 per capita and in real
+  # dollars, and a not_reported stays NA through both rather than becoming a
+  # spurious 0.
+  completion <- list(applied = FALSE, rows_filled = 0L, absence_means = list())
+  if (complete) {
+    result <- .complete_result(result, con, subtype_col, govid, years,
+                               category, subtype_scope)
+    completion <- attr(result, ".completion")
+    attr(result, ".completion") <- NULL
+  }
+
   if (per_capita) result <- .attach_per_capita(result, con, govid)
   if (!is.null(adjust_to_year)) {
     result <- .attach_real_dollars(result, adjust_to_year, per_capita)
@@ -229,7 +378,7 @@ cog_spending <- function(govid, years, category = NULL,
     basis_for_prov <- resolved$basis
     basis_note_for_prov <- resolved$note
     harmonization <- .build_harmonization_block(
-      con, govid, years, resolved, flow_prefixes
+      con, govid, years, resolved, subtype_col, subtype_scope
     )
     # C1(a): gap detection must run against the Direct leg alone. `result`
     # can also carry UNION'd intergovernmental rows (expenditure_concept =
@@ -307,9 +456,11 @@ cog_spending <- function(govid, years, category = NULL,
     expenditure_concept = expenditure_concept,
     expenditure_concept_note = expenditure_concept_note_for_prov,
     expenditure_concept_direct_suppressed = direct_suppressed_flag,
+    revenue_concept = revenue_concept,
     harmonization  = harmonization,
     recipe         = recipe_block,
-    suggestions    = suggestions
+    suggestions    = suggestions,
+    completion     = completion
   )
   prov$scope$govids_found   <- scope$found
   prov$scope$govids_missing <- scope$missing
@@ -406,7 +557,7 @@ cog_spending <- function(govid, years, category = NULL,
 
 #' @noRd
 .build_verb_sql <- function(view, subtype_col, govid, years, category,
-                            ig_view = NULL) {
+                            ig_view = NULL, subtype_scope = NULL) {
   govid_lit <- .sql_lit_chr(govid)
   years_lit <- paste(as.integer(years), collapse = ",")
   category_pred <- if (is.null(category)) {
@@ -415,9 +566,22 @@ cog_spending <- function(govid, years, category = NULL,
     sprintf("AND category IN (%s)", .sql_lit_chr(category))
   }
 
+  # The concept's subtype allowlist (see .expenditure_concept_subtypes()).
+  # The base views carry every subtype of their flow (spending_annotated has
+  # all five non-IG expenditure subtypes); the concept narrows here. For
+  # "total", the IG leg's rows are 'intergovernmental', so that value joins
+  # the allowlist exactly when ig_view is present.
+  subtype_pred <- if (is.null(subtype_scope)) {
+    ""
+  } else {
+    scope <- if (is.null(ig_view)) subtype_scope else c(subtype_scope, "intergovernmental")
+    sprintf("AND %s IN (%s)", subtype_col, .sql_lit_chr(scope))
+  }
+
   # expenditure_concept = "total" adds the intergovernmental leg. UNION ALL,
-  # never UNION: the two legs are disjoint by item_code prefix (E/F/G vs M/L),
-  # so de-duplication would be pure cost, and a silent row-drop if two
+  # never UNION: the two legs are disjoint by crosswalk subtype (the direct
+  # view excludes 'intergovernmental'; the IG view is only that), so
+  # de-duplication would be pure cost, and a silent row-drop if two
   # governments ever reported identical values.
   source_expr <- if (is.null(ig_view)) {
     view
@@ -449,9 +613,10 @@ cog_spending <- function(govid, years, category = NULL,
      WHERE canonical_govid IN (%3$s)
        AND year IN (%4$s)
        %5$s
+       %6$s
      GROUP BY year, canonical_govid, gov_name, xwalk_gov_name, %1$s, category
      ORDER BY year, canonical_govid, %1$s, category",
-    subtype_col, source_expr, govid_lit, years_lit, category_pred
+    subtype_col, source_expr, govid_lit, years_lit, category_pred, subtype_pred
   )
 }
 

R/views.R

@@ -32,6 +32,52 @@
   "45-ig_annotated_harmonized.sql"
 )
 
+# The representation contract (cog_pipeline#64): two parquet tables that say
+# what an ABSENT cell means in a given year. Gated on manifest PRESENCE, not
+# on schema_version, because the sparsification that introduced them did not
+# bump the version -- the pre-sparsification corpus this package shipped
+# against until 2026-07-30 was already schema v6 and carried neither table.
+# Keying off the version number would therefore register a view over a file
+# that does not exist and fail at CREATE VIEW time on exactly the corpora this
+# check exists to tolerate.
+.representation_view_files <- c(
+  "36-representation.sql" = "representation.parquet",
+  "37-code_set.sql"       = "code_set.parquet"
+)
+
+# Cash and security holdings (uscogdata#25). 46- selects
+# `c.balance_subtype`, a column that arrived with cog_pipeline #76/#77 and
+# WITHOUT a schema_version bump -- so neither existing gate applies:
+# .harmonization_view_files keys on schema_version, .representation_view_files
+# on the presence of a FILE. Here the discriminator is a COLUMN on a table
+# that exists either way. CREATE VIEW resolves its source schema eagerly, so
+# on an older corpus 46- would fail at registration with "Binder Error:
+# Referenced column balance_subtype not found" rather than at query time.
+.balance_view_files <- c("26-balance_long.sql", "46-balance_annotated.sql")
+
+#' Does the mounted corpus's `summary_categories` carry `balance_subtype`?
+#' Probed against the live connection rather than the manifest, because the
+#' manifest describes files, not columns.
+#' @noRd
+.corpus_has_balance_subtype <- function(con) {
+  n <- DBI::dbGetQuery(con,
+    "SELECT COUNT(*) AS n FROM information_schema.columns
+     WHERE table_name = 'summary_categories'
+       AND column_name = 'balance_subtype'"
+  )$n
+  isTRUE(as.integer(n) > 0L)
+}
+
+#' Does the mounted corpus publish `file` (e.g. "code_set.parquet")?
+#' Reads the manifest's metadata list rather than stat-ing the URL, so it
+#' works identically for a local fixture and a remote share.
+#' @noRd
+.corpus_has_table <- function(manifest, file) {
+  paths <- vapply(manifest$files$metadata %||% list(),
+                  function(f) as.character(f$path %||% ""), character(1))
+  file %in% basename(paths)
+}
+
 #' Register DuckDB views from inst/sql/ SQL files
 #' @noRd
 .register_views <- function(con, url, manifest) {
@@ -39,7 +85,11 @@
   files   <- sort(list.files(sql_dir, pattern = "\\.sql$", full.names = TRUE))
   schema_version <- suppressWarnings(as.integer(manifest$schema_version %||% 0L))
   for (f in files) {
-    if (basename(f) %in% .harmonization_view_files && schema_version < 5L) next
+    base <- basename(f)
+    if (base %in% .harmonization_view_files && schema_version < 5L) next
+    if (base %in% names(.representation_view_files) &&
+        !.corpus_has_table(manifest, .representation_view_files[[base]])) next
+    if (base %in% .balance_view_files && !.corpus_has_balance_subtype(con)) next
     sql <- paste(readLines(f, warn = FALSE), collapse = "\n")
     sql <- gsub("\\{url\\}", url, sql, fixed = FALSE)
     DBI::dbExecute(con, sql)

README.md

@@ -19,29 +19,90 @@ package implements.
 # pak::pkg_install("gitea.civilytics.org/Civilytics/uscogdata")
 ```
 
+## Amounts are in full US dollars
+
+Every amount column this package returns — `amt_nominal`, `amt_real`,
+`amt_per_capita_nominal`, `amt_per_capita_real` — is in **full US dollars**.
+
+The raw Census source files report **thousands of dollars**, and the corpus's
+own `amt` column preserves that. The verbs multiply by 1000 on the way out, so
+you never have to. The conversion is recorded in every result:
+
+```r
+r <- cog_spending("552025209777", 2020L)
+attr(r, "provenance")$transformations$units_conversion
+#> $applied TRUE  $source_unit "$1,000s (raw Census)"  $target_unit "$USD"  $multiplier 1000
+```
+
+**Do not multiply again.** If you have read elsewhere that COG amounts are in
+`$1,000s` — true of the raw corpus, and of `cog_explorer`'s conventions doc —
+that rule does not apply to anything a `cog_*()` verb hands you. Applying it
+twice overstates every figure by 1000x, and the result looks plausible rather
+than obviously wrong.
+
 ## Configuration
 
 - `USCOGDATA_URL` — corpus root URL (public Nextcloud share, trailing slash)
 - `USCOGDATA_CACHE_DIR` — optional override for the manifest cache directory
 - `USCOGDATA_MANIFEST_TTL_SECS` — optional manifest re-fetch TTL (default 3600)
 
-## Direct vs Total spending
+## Primary vs Direct vs Total spending
 
-`cog_spending(..., expenditure_concept = c("direct", "total"))` controls
-whose spending a result counts. `"direct"` (the default) is a government's
-own current operations, capital outlay, and other direct spending. `"total"`
-additionally adds in the intergovernmental legs — money it hands to other
-governments to spend on its behalf — which is meaningful for describing one
+`cog_spending(..., expenditure_concept = c("primary", "direct", "total"))`
+controls whose spending a result counts. Concepts are defined as sets of the
+crosswalk's `spend_subtype` values — never item-code first letters, which
+cannot classify correctly (the letter `Y` alone spans revenue, expenditure,
+and balance codes):
+
+- `"primary"` (the default) is the government's own service provision:
+  current operations, capital outlay, and assistance payments.
+- `"direct"` is Census's published Direct Expenditure: `primary` plus
+  interest on debt and insurance trust benefit payments (e.g. pensions).
+- `"total"` additionally adds the intergovernmental leg — money handed to
+  other governments to spend (`M`/`L` codes plus `Q11`/`Q12`/`Q18` state
+  payments to school systems) — which is meaningful for describing one
   government's own budget over time, but double-counts when summed across
   governments (a state's payment to a county is the same dollar the county
   reports as its own direct spending).
 
 **Rule of thumb: any figure that spans more than one government uses
-`direct`.** `cog_geographic_rollup()` and `cog_peer_compare()` enforce this
-by refusing `expenditure_concept = "total"`. See
+`primary` or `direct`.** `cog_geographic_rollup()` and `cog_peer_compare()`
+enforce this by refusing `expenditure_concept = "total"`. See
 `vignette("total-spending", package = "uscogdata")` for the full
 explanation with worked examples.
 
+## General vs Total revenue
+
+`cog_revenue(..., revenue_concept = c("general", "total"))` selects between
+Census's two published revenue concepts, again defined as crosswalk
+`revenue_subtype` sets rather than item-code prefixes:
+
+- `"general"` (the default) is Census **General Revenue**: own-source
+  (taxes, charges, miscellaneous) plus federal, state and local
+  intergovernmental aid.
+- `"total"` is Census **Total Revenue**: `general` plus utility revenue
+  (`A91`–`A94`), liquor store revenue (`A90`), and insurance trust revenue
+  (unemployment and workers' compensation `Y` codes plus the
+  employee-retirement `X` codes).
+
+The manual defines the first by subtracting the other three from the second,
+so the two are related by Census's own identity:
+
+```
+Total Revenue = General + Utility + Liquor Store + Insurance Trust
+```
+
+Two things worth knowing before switching to `"total"`:
+
+- **Utility revenue is large for cities.** Measured on the bundled fixture,
+  utility plus liquor store revenue is 15.9% of city (type 2) revenue, versus
+  1.2% for states and 1.7% for counties. `general` excludes it by definition.
+- **The employee-retirement (`X`) codes stop at FY2016**, when those systems
+  moved out of the annual finance file into the separate Annual Survey of
+  Public Pensions. A `"total"` series therefore steps down at the
+  FY2016/FY2017 seam for reasons of collection scope, not revenue (series
+  breaks `SB197`–`SB202`, in the corpus's `series_breaks` table).
+
 ## Developer notes
 
 ### Testing

_pkgdown.yml

@@ -3,6 +3,12 @@ template:
   bootstrap: 5
 
 reference:
+  - title: Financial data
+    desc: Spending, revenue and balance-sheet holdings for one or more governments.
+    contents:
+      - cog_spending
+      - cog_revenue
+      - cog_balances
   - title: Search & basket
     desc: Resolve place names into canonical govids.
     contents:

data-raw/regenerate_fixture_corpus.R

@@ -11,12 +11,14 @@
 #      Each partition is a full year (all states/govs) as published, so
 #      Broward County FL and every other previously-pinned government stay
 #      covered without any per-gov slicing logic.
-#   2. Copies the full canonical_fips_xwalk.parquet, canonical_alias.parquet,
-#      summary_categories.parquet, harmonization_map.parquet,
-#      harmonization_recipes.parquet, and series_breaks.parquet metadata
-#      tables as-is (these are small cross-vintage registries, not
-#      partitioned by year, so the fixture ships the complete tables rather
-#      than a year-scoped subset).
+#   2. Copies every metadata parquet the publish tree ships (see
+#      .FIXTURE_METADATA_FILES) as-is. These are small cross-vintage
+#      registries, not partitioned by year, so the fixture ships the complete
+#      tables rather than a year-scoped subset. representation.parquet and
+#      code_set.parquet are what make the sparse wide era interpretable --
+#      absence means "$0" in a dense_source year and "not reported" in a
+#      sparse_source one -- so a fixture without them cannot represent the
+#      published corpus.
 #   3. Resyncs the four reference docs (data_dictionary.md,
 #      reader-specification.md, README.md, series_breaks.md) from the
 #      publish tree's docs/.
@@ -38,6 +40,22 @@
 #   source("data-raw/regenerate_fixture_corpus.R")
 #   regenerate_fixture_corpus(publish_cache_dir = "/path/to/publish_cache")
 
+# Every metadata parquet the publish tree ships, in the order they appear in
+# the corpus manifest. Single source of truth for both the copy step and the
+# fixture manifest, so the two can never drift apart.
+.FIXTURE_METADATA_FILES <- c(
+  "canonical_alias.parquet",
+  "canonical_fips_xwalk.parquet",
+  "census_collection_coverage.parquet",
+  "code_set.parquet",
+  "harmonization_map.parquet",
+  "harmonization_recipes.parquet",
+  "lineage_events.parquet",
+  "representation.parquet",
+  "series_breaks.parquet",
+  "summary_categories.parquet"
+)
+
 regenerate_fixture_corpus <- function(
     publish_cache_dir = file.path(
       "..", "cog_pipeline", "_targets", "publish_cache"
@@ -100,20 +118,11 @@ regenerate_fixture_corpus <- function(
   invisible(NULL)
 }
 
-# Copy the full (not year-scoped) canonical_fips_xwalk, canonical_alias,
-# summary_categories, and (schema v5+) harmonization_map/
-# harmonization_recipes/series_breaks parquet tables.
+# Copy the full (not year-scoped) metadata tables listed in
+# .FIXTURE_METADATA_FILES.
 #' @noRd
 .copy_metadata_parquets <- function(publish_cache_dir, fixture_dir) {
-  files <- c(
-    "canonical_fips_xwalk.parquet",
-    "canonical_alias.parquet",
-    "summary_categories.parquet",
-    "harmonization_map.parquet",
-    "harmonization_recipes.parquet",
-    "series_breaks.parquet"
-  )
-  for (f in files) {
+  for (f in .FIXTURE_METADATA_FILES) {
     src <- file.path(publish_cache_dir, "data", f)
     dst <- file.path(fixture_dir, "data", f)
     if (!file.exists(src)) {
@@ -179,15 +188,7 @@ regenerate_fixture_corpus <- function(
     )
   })
 
-  metadata_files <- c(
-    "canonical_alias.parquet",
-    "canonical_fips_xwalk.parquet",
-    "summary_categories.parquet",
-    "harmonization_map.parquet",
-    "harmonization_recipes.parquet",
-    "series_breaks.parquet"
-  )
-  metadata <- lapply(metadata_files, function(f) {
+  metadata <- lapply(.FIXTURE_METADATA_FILES, function(f) {
     rel <- file.path("data", f)
     path <- file.path(fixture_dir, rel)
     list(
@@ -203,13 +204,16 @@ regenerate_fixture_corpus <- function(
     pipeline_commit = source_manifest$pipeline_commit,
     fixture_note = paste(
       "Four-year (2011, 2012, 2019, 2020) fixture for uscogdata tests. Full",
-      "corpus available via USCOGDATA_URL. Regenerated for Phase R2",
-      "(schema_version 5, harmonization_map/harmonization_recipes/",
-      "series_breaks parquet tables added). 2011/2012 straddle the",
-      "wide-aggregate -> modern-leaf format boundary exercised by basis=",
-      "\"harmonized\" and recipe= queries; 2019/2020 retain the prior",
-      "per-capita/CPI regression anchors. Full canonical_fips_xwalk master",
-      "and canonical_alias lookup table included via",
+      "corpus available via USCOGDATA_URL. Regenerated from the sparsified",
+      "schema-v6 corpus: the wide era (<= FY2011) no longer stores explicit",
+      "zeros, so FY2011 absence means Census published $0 while FY2012+",
+      "absence means not reported. representation.parquet and",
+      "code_set.parquet carry that rule and ship in full, as do every other",
+      "metadata table in the publish tree. 2011/2012 straddle both the",
+      "wide-aggregate -> modern-leaf format boundary (exercised by",
+      "basis=\"harmonized\" and recipe= queries) and the dense -> sparse",
+      "representation boundary (SB194); 2019/2020 retain the prior",
+      "per-capita/CPI regression anchors. Regenerated via",
       "data-raw/regenerate_fixture_corpus.R."
     ),
     data_vintage    = source_manifest$data_vintage,

inst/extdata/fixture_corpus/manifest.json

@@ -1,8 +1,8 @@
 {
   "schema_version": 6,
-  "built_at": "2026-07-27T13:04:05Z",
-  "pipeline_commit": "6098baf",
-  "fixture_note": "Four-year (2011, 2012, 2019, 2020) fixture for uscogdata tests. Full corpus available via USCOGDATA_URL. Regenerated for Phase R2 (schema_version 5, harmonization_map/harmonization_recipes/ series_breaks parquet tables added). 2011/2012 straddle the wide-aggregate -> modern-leaf format boundary exercised by basis= \"harmonized\" and recipe= queries; 2019/2020 retain the prior per-capita/CPI regression anchors. Full canonical_fips_xwalk master and canonical_alias lookup table included via data-raw/regenerate_fixture_corpus.R.",
+  "built_at": "2026-07-31T00:47:27Z",
+  "pipeline_commit": "aadb46b",
+  "fixture_note": "Four-year (2011, 2012, 2019, 2020) fixture for uscogdata tests. Full corpus available via USCOGDATA_URL. Regenerated from the sparsified schema-v6 corpus: the wide era (<= FY2011) no longer stores explicit zeros, so FY2011 absence means Census published $0 while FY2012+ absence means not reported. representation.parquet and code_set.parquet carry that rule and ship in full, as do every other metadata table in the publish tree. 2011/2012 straddle both the wide-aggregate -> modern-leaf format boundary (exercised by basis=\"harmonized\" and recipe= queries) and the dense -> sparse representation boundary (SB194); 2019/2020 retain the prior per-capita/CPI regression anchors. Regenerated via data-raw/regenerate_fixture_corpus.R.",
   "data_vintage": {
     "source_vintages": {
       "2012": "10162019",
@@ -36,9 +36,9 @@
       {
         "year": 2011,
         "path": "data/long/year=2011/part-0.parquet",
-        "sha256": "84302ab364dc9fc3b3fbbc3c3f8b826e3508b4d73ff7c42d094d3863cd1e37b5",
-        "row_count": 2864212,
-        "size_bytes": 3845911
+        "sha256": "7848e18497080c8980a4f89c5b386205b2c5bc90db6773827ea01ab3943d16b1",
+        "row_count": 496004,
+        "size_bytes": 2202455
       },
       {
         "year": 2012,
@@ -74,9 +74,14 @@
         "description": "canonical_fips_xwalk.parquet"
       },
       {
-        "path": "data/summary_categories.parquet",
-        "sha256": "0985b607f3f35a8dff62c0561261ab6922423b81d11c07b03bcb3e3461f85e33",
-        "description": "summary_categories.parquet"
+        "path": "data/census_collection_coverage.parquet",
+        "sha256": "143e025616cde684da7c4442bc00d07fbd1556fabb0ea96223931b737e5d10a4",
+        "description": "census_collection_coverage.parquet"
+      },
+      {
+        "path": "data/code_set.parquet",
+        "sha256": "4cffcb0198dd51e4ff2b694050bb371a5f9965cdac12f25521cb628fb8e118a9",
+        "description": "code_set.parquet"
       },
       {
         "path": "data/harmonization_map.parquet",
@@ -88,10 +93,25 @@
         "sha256": "1133e9a0b02f8f34f5f936e55c5ecd596bb8a55d8425dcce76767f0f3203581c",
         "description": "harmonization_recipes.parquet"
       },
+      {
+        "path": "data/lineage_events.parquet",
+        "sha256": "36c16acfbe621d61010984767f1c566993b8a5f481a2c1e134c4c0a600e4502f",
+        "description": "lineage_events.parquet"
+      },
+      {
+        "path": "data/representation.parquet",
+        "sha256": "31ec328a7dd505a321b45f97aafff12e53d68a1a986f63509863035b22a4360d",
+        "description": "representation.parquet"
+      },
       {
         "path": "data/series_breaks.parquet",
-        "sha256": "b0b6794b6887a4f300079adfa10029c2a77109faa4952fbff1c5a270793cc02b",
+        "sha256": "06dcc995ff533e57cc65fa25086cc9bf83ba592c58bf7cc99269dc2576f69944",
         "description": "series_breaks.parquet"
+      },
+      {
+        "path": "data/summary_categories.parquet",
+        "sha256": "e3b0efa00ce713b8f45829b89cfde24b55333f26101f0495df82d85997d18d8e",
+        "description": "summary_categories.parquet"
       }
     ]
   },

inst/schemas/provenance-v1.json

@@ -14,16 +14,22 @@
     "basis_note":     { "type": ["string", "null"] },
     "expenditure_concept": {
       "type": "string",
-      "enum": ["direct", "total"],
-      "description": "Which spending concept produced this result. 'direct' is the government's own E/F/G spending; 'total' adds its intergovernmental payments (M to local governments, L to state governments). Only 'direct' is valid for results combined across governments."
+      "enum": ["primary", "direct", "total"],
+      "description": "Which spending concept produced this result, defined as crosswalk spend_subtype sets (never item-code prefixes). 'primary' (the default) is the government's own service provision: operations + capital + assistance. 'direct' adds interest on debt and insurance trust benefit payments (Census's published Direct Expenditure). 'total' adds intergovernmental payments (M to local governments, L to state government, Q11/Q12/Q18 to school systems). Only 'primary' and 'direct' are valid for results combined across governments."
     },
     "expenditure_concept_note": {
       "type": ["string", "null"],
-      "description": "How the intergovernmental leg was assembled; null for 'direct'."
+      "description": "How the intergovernmental leg was assembled; null for 'primary' and 'direct'."
     },
     "expenditure_concept_direct_suppressed": {
       "type": "boolean",
-      "description": "TRUE when expenditure_concept = 'total' and at least one requested (year, category) has intergovernmental rows but NO Direct rows in this corpus (typically a legacy aggregate-only family) -- those result rows report the intergovernmental leg alone, not Direct + IG. Always FALSE for expenditure_concept = 'direct'. See the affected rows' `notes` for the recovering recipe, if any."
+      "description": "TRUE when expenditure_concept = 'total' and at least one requested (year, category) has intergovernmental rows but NO Direct rows in this corpus (typically a legacy aggregate-only family) -- those result rows report the intergovernmental leg alone, not Direct + IG. Always FALSE for expenditure_concept = 'primary' or 'direct'. See the affected rows' `notes` for the recovering recipe, if any."
+    },
+    "revenue_concept": {
+      "type": "string",
+      "enum": ["general", "total"],
+      "description": "Which revenue concept produced this result, defined as crosswalk revenue_subtype sets (never item-code prefixes). 'general' (the default) is Census General Revenue: own_source + federal + state + local_aid. 'total' is Census Total Revenue: general plus utility, liquor store and insurance trust revenue. Census defines the first by subtracting the other three from the second (manual section 4.3). Meaningful for cog_revenue() results; spending results carry the default.",
+      "$comment": "The employee-retirement (X) codes inside insurance_trust stop at FY2016, so a 'total' series steps at the FY2016/FY2017 seam for collection-scope reasons (series breaks SB197-SB202)."
     },
     "harmonization":  { "type": "object" },
     "recipe":         { "type": ["object", "null"] },
@@ -33,6 +39,30 @@
     "aggregate_fallback": { "type": ["object", "null"] },
     "transformations":{ "type": "object" },
     "series_break_refs": { "type": "array", "items": { "type": "string" } },
+    "completion": {
+      "type": "object",
+      "description": "What `complete = TRUE` filled. `applied` is FALSE on an ordinary query. `rows_filled` counts cells added to the requested grid, and `absence_means` maps each requested year to the meaning of an absent cell there ('census_zero' in a dense_source year, 'not_reported' in a sparse_source one). Filled rows carry `value_source` in the result: 'reported', 'census_zero' (amount 0 -- Census published $0), or 'not_reported' (amount NA -- unknown).",
+      "properties": {
+        "applied": { "type": "boolean" },
+        "rows_filled": { "type": "integer" },
+        "absence_means": { "type": "object" }
+      }
+    },
+    "corpus_break_refs": {
+      "type": "array",
+      "items": { "type": "string" },
+      "description": "Ids of catalogued series breaks whose fin_code is the literal 'ALL' -- caveats about the corpus as a whole (dollar precision across 1976/1977, imputation exclusion from 2002, the dense -> sparse representation change at 2012, the government id scheme change at 2017) rather than about one item code. Selected on the break_year window alone, so they do not depend on which codes a result contains. Disjoint from series_break_refs by construction: an entry qualifies the whole result, not one series."
+    },
+    "balance_caveats": {
+      "type": ["object", "null"],
+      "description": "Present only on cog_balances() results (null/absent for cog_spending()/cog_revenue()). `not_gaap` is always TRUE and `not_gaap_note` explains that Census holdings are gross -- no liabilities are netted -- so they are NOT comparable to a GAAP fund balance. `coverage_window` maps EVERY balance_subtype present in the mounted corpus -- not only the ones this query observed -- to its measured [min year, max year] there (never hardcoded), so a caller can see which families exist and over what span before deciding they missed one. `truncated` is the query-scoped field: it lists only the subtypes this result actually observed whose coverage_window does not fully span the requested years.",
+      "properties": {
+        "not_gaap": { "type": "boolean" },
+        "not_gaap_note": { "type": "string" },
+        "coverage_window": { "type": "object" },
+        "truncated": { "type": "array", "items": { "type": "string" } }
+      }
+    },
     "manifest":       { "type": "object" },
     "sql_query":      { "type": "string" }
   }

inst/sql/11-summary_categories.sql

@@ -0,0 +1,7 @@
+-- Category crosswalk. Numbered 11 (not with the other reference tables at
+-- 30+) because the flow views (20-25) classify by MEMBERSHIP in this table
+-- and DuckDB binds a view's sources eagerly at CREATE VIEW time, so it must
+-- already exist when they register.
+CREATE OR REPLACE VIEW summary_categories AS
+SELECT *
+FROM read_parquet('{url}data/summary_categories.parquet');

inst/sql/20-spending_long.sql

@@ -1,5 +1,22 @@
+-- Direct-side expenditure rows, classified by crosswalk MEMBERSHIP
+-- (summary_categories.category_type = 'expenditure'), never by item-code
+-- first letter: prefix Y alone spans revenue (Y01/Y02), expenditure
+-- (Y05/Y06) and balance codes, so no first-letter allowlist can route it
+-- (uscogdata#11, finding F-018). Which subtypes a query actually returns is
+-- decided per expenditure_concept in R (.verb_spendrev); this view carries
+-- every non-intergovernmental expenditure subtype: operations, capital,
+-- assistance, interest, insurance_benefits.
+--
+-- The intergovernmental subtype (M/L/Q codes) is deliberately carved out
+-- into ig_long: its legacy-era rows are published ONLY as aggregate-flagged
+-- rows, so it cannot live behind this view's NOT is_aggregate filter (see
+-- 24-ig_long.sql).
 CREATE OR REPLACE VIEW spending_long AS
 SELECT *
 FROM long
-WHERE LEFT(item_code, 1) IN ('E', 'F', 'G')
+WHERE item_code IN (
+    SELECT item_code FROM summary_categories
+    WHERE category_type = 'expenditure'
+      AND spend_subtype <> 'intergovernmental'
+  )
   AND NOT is_aggregate;

inst/sql/21-revenue_long.sql

@@ -1,5 +1,18 @@
+-- Revenue rows, classified by crosswalk MEMBERSHIP rather than item-code
+-- first letter (see 20-spending_long.sql for why prefixes cannot work).
+--
+-- Carries EVERY revenue subtype. Which of Census's two published concepts a
+-- query actually returns is decided per revenue_concept in R
+-- (.verb_spendrev), exactly as expenditure_concept narrows spending_long:
+--   general = own_source + federal + state + local_aid   (the default)
+--   total   = general + utility + liquor_store + insurance_trust
+-- Census defines the first by subtracting the other three from the second
+-- (manual section 4.3), so both concepts need all four families present here.
 CREATE OR REPLACE VIEW revenue_long AS
 SELECT *
 FROM long
-WHERE LEFT(item_code, 1) IN ('T', 'A', 'U', 'B', 'C', 'D')
+WHERE item_code IN (
+    SELECT item_code FROM summary_categories
+    WHERE category_type = 'revenue'
+  )
   AND NOT is_aggregate;

inst/sql/22-spending_long_harmonized.sql

@@ -1,6 +1,16 @@
+-- Harmonized-basis twin of 20-spending_long.sql: same crosswalk-membership
+-- classification, applied to harmonized_code (the code the row is folded
+-- onto) rather than the published item_code. Safe because the harmonized
+-- space is leaf-only and every harmonized_code in the corpus is a
+-- summary_categories member (verified at fixture regen; a code the
+-- crosswalk cannot classify would be silently dropped here).
 CREATE OR REPLACE VIEW spending_long_harmonized AS
 SELECT * REPLACE (harmonized_code AS item_code)
 FROM long
 WHERE NOT is_aggregate
   AND harmonized_code IS NOT NULL
-  AND LEFT(harmonized_code, 1) IN ('E', 'F', 'G');
+  AND harmonized_code IN (
+    SELECT item_code FROM summary_categories
+    WHERE category_type = 'expenditure'
+      AND spend_subtype <> 'intergovernmental'
+  );

inst/sql/23-revenue_long_harmonized.sql

@@ -1,6 +1,12 @@
+-- Harmonized-basis twin of 21-revenue_long.sql: same crosswalk-membership
+-- classification (every revenue subtype; the concept narrows in R), applied
+-- to harmonized_code rather than the published item_code.
 CREATE OR REPLACE VIEW revenue_long_harmonized AS
 SELECT * REPLACE (harmonized_code AS item_code)
 FROM long
 WHERE NOT is_aggregate
   AND harmonized_code IS NOT NULL
-  AND LEFT(harmonized_code, 1) IN ('T', 'A', 'U', 'B', 'C', 'D');
+  AND harmonized_code IN (
+    SELECT item_code FROM summary_categories
+    WHERE category_type = 'revenue'
+  );

inst/sql/24-ig_long.sql

@@ -1,4 +1,6 @@
--- Intergovernmental expenditure rows (M = to local govts, L = to state govts).
+-- Intergovernmental expenditure rows: crosswalk spend_subtype =
+-- 'intergovernmental' (M = to local govts, L = to state govts, Q11/Q12/Q18
+-- = state payments to school systems -- uscogdata#11, finding F-017).
 --
 -- Deliberately does NOT filter `NOT is_aggregate`, unlike spending_long. In the
 -- wide era (<= FY2011) the IG families M05/M12/M47/M89/L47/L89 are published
@@ -9,10 +11,15 @@
 -- from 2012 alongside M91-93), so no row is ever counted twice. Same argument
 -- the pipeline's recipe joins use.
 --
--- `L--` IS excluded: it is the IG-to-state FAMILY TOTAL and genuinely rolls up
--- the L-NN codes, so including it would double-count.
+-- `L--` stays excluded: it is the IG-to-state FAMILY TOTAL and genuinely
+-- rolls up the L-NN codes, so including it would double-count. The crosswalk
+-- deliberately carries no `--` family-total codes, so membership excludes it
+-- (guarded by "the IG leg never includes the L-- family total" in
+-- tests/testthat/test-expenditure-concept.R).
 CREATE OR REPLACE VIEW ig_long AS
 SELECT *
 FROM long
-WHERE LEFT(item_code, 1) IN ('M', 'L')
-  AND item_code NOT LIKE '%--';
+WHERE item_code IN (
+    SELECT item_code FROM summary_categories
+    WHERE spend_subtype = 'intergovernmental'
+  );

inst/sql/25-ig_long_harmonized.sql

@@ -8,8 +8,15 @@
 -- WHERE harmonized_code IS NULL GROUP BY 1, 2`). COALESCE keeps the one real
 -- IG collapse rule (M38 -> M36, SB012, year-disjoint 1967-2011 vs 2012+)
 -- while never dropping a row.
+--
+-- Membership is checked on the published item_code (mirroring 24-ig_long.sql)
+-- rather than the COALESCEd code: every IG harmonization target (M36) is
+-- itself an IG crosswalk member, so the two are equivalent, and item_code is
+-- the column that exists on every row.
 CREATE OR REPLACE VIEW ig_long_harmonized AS
 SELECT * REPLACE (COALESCE(harmonized_code, item_code) AS item_code)
 FROM long
-WHERE LEFT(item_code, 1) IN ('M', 'L')
-  AND item_code NOT LIKE '%--';
+WHERE item_code IN (
+    SELECT item_code FROM summary_categories
+    WHERE spend_subtype = 'intergovernmental'
+  );

inst/sql/26-balance_long.sql

@@ -0,0 +1,22 @@
+-- Cash and security holdings, classified by crosswalk MEMBERSHIP on
+-- category_type (see 21-revenue_long.sql for why first-letter prefixes cannot
+-- do this job -- the X and Y families each span revenue, expenditure AND
+-- balance).
+--
+-- These rows are STOCKS: a balance at a point in time, not a flow over a
+-- fiscal year. Summing a stock with a flow is meaningless, which is why they
+-- live behind a third view rather than as a subtype of either money view, and
+-- why neither spending_long nor revenue_long can reach them.
+--
+-- `NOT is_aggregate` mirrors spending_long / revenue_long. The wide-era
+-- aggregate-only holdings codes (X40/X41) are deliberately outside this view;
+-- they are reachable only through the recipe path, which bypasses this filter
+-- by design (cog_pipeline/docs/phase_r_harmonization_review.md § 0.2).
+CREATE OR REPLACE VIEW balance_long AS
+SELECT *
+FROM long
+WHERE item_code IN (
+    SELECT item_code FROM summary_categories
+    WHERE category_type = 'balance'
+  )
+  AND NOT is_aggregate;

inst/sql/31-summary_categories.sql

@@ -1,3 +0,0 @@
-CREATE OR REPLACE VIEW summary_categories AS
-SELECT *
-FROM read_parquet('{url}data/summary_categories.parquet');

inst/sql/36-representation.sql

@@ -0,0 +1,3 @@
+CREATE OR REPLACE VIEW representation AS
+SELECT *
+FROM read_parquet('{url}data/representation.parquet');

inst/sql/37-code_set.sql

@@ -0,0 +1,3 @@
+CREATE OR REPLACE VIEW code_set AS
+SELECT *
+FROM read_parquet('{url}data/code_set.parquet');

inst/sql/46-balance_annotated.sql

@@ -0,0 +1,16 @@
+CREATE OR REPLACE VIEW balance_annotated AS
+SELECT
+  s.*,
+  x.gov_name       AS xwalk_gov_name,
+  x.govs_type,
+  x.type_label,
+  x.fips_state     AS xwalk_fips_state,
+  x.fips_county    AS xwalk_fips_county,
+  x.fips_place,
+  x.population_acs,
+  c.category,
+  c.category_type,
+  c.balance_subtype
+FROM balance_long s
+LEFT JOIN canonical_fips_xwalk x USING (canonical_govid)
+LEFT JOIN summary_categories   c USING (item_code);

man/cog_balances.Rd

@@ -0,0 +1,76 @@
+% Generated by roxygen2: do not edit by hand
+% Please edit documentation in R/balances.R
+\name{cog_balances}
+\alias{cog_balances}
+\title{Cash and security holdings for one or more governments}
+\usage{
+cog_balances(
+  govid,
+  years,
+  category = NULL,
+  per_capita = FALSE,
+  adjust_to_year = NULL,
+  basis = c("harmonized", "raw"),
+  recipe = NULL
+)
+}
+\arguments{
+\item{govid}{Canonical govid(s): a character vector, or a data frame with a
+`canonical_govid` column (e.g. from [cog_gov_search()]).}
+
+\item{years}{Integer vector of fiscal years.}
+
+\item{category}{Optional character vector of categories to keep. One of
+`"Fund Balances"`, `"Insurance Trust Balances"`,
+`"Retirement System Holdings"`. There is deliberately no `subtype`
+argument: for holdings, `category` is a strict coarsening of
+`balance_subtype` (unlike the money verbs, where the two axes cross), so
+every combination would be either redundant or empty.
+`category = "Fund Balances"` is exactly the `general` family
+(`W01`/`W31`/`W61`). `balance_subtype` is returned, so a finer split is
+one `dplyr::filter()` away.}
+
+\item{per_capita}{Divide holdings by population. Note this is a **stock per
+resident** (reserves per person), which is *not* comparable to
+[cog_spending()]'s per-capita figures -- those are a flow per person.}
+
+\item{adjust_to_year}{Deflate to this year's dollars (CPI-U).}
+
+\item{basis}{Accepted for uniformity with the money verbs, but currently a
+**no-op**: `harmonization_map` carries no balance-code rows, so harmonized
+and raw space are identical for holdings. Reported in
+`provenance$basis_note`.}
+
+\item{recipe}{Optional harmonization recipe id (see [cog_recipes()]).
+`"cash_securities_z77_wide"` and `"cash_securities_z78_wide"` bridge the
+wide era to the modern one.}
+}
+\value{
+Tibble with columns `year`, `canonical_govid`, `gov_name`,
+  `balance_subtype`, `category`, `amt_nominal`, `codes_included`,
+  `aggregate_fallback`, plus optional `amt_per_capita_nominal` and
+  `pop_source` (when `per_capita = TRUE`), optional `amt_real` (when
+  `adjust_to_year` is set), and optional `amt_per_capita_real` (only when
+  **both** `per_capita = TRUE` and `adjust_to_year` are set -- there is no
+  nominal per-capita column to deflate otherwise). Amounts are full US
+  dollars.
+
+  Carries a `provenance` attribute matching
+  `inst/schemas/provenance-v1.json`, whose `balance_caveats` block reports
+  `not_gaap`, `not_gaap_note`, `coverage_window` (measured year extents for
+  every balance subtype in the mounted corpus, not only the observed ones)
+  and `truncated` (the observed subtypes whose coverage falls short of the
+  requested years). `expenditure_concept`/`revenue_concept` are `NA` --
+  holdings are a stock, not a flow, so neither concept vocabulary applies.
+}
+\description{
+Returns Census cash-and-security holdings (`category_type = "balance"`):
+fund balances, retirement system holdings and insurance trust balances.
+}
+\section{Holdings are not GAAP fund balance}{
+
+Census holdings are **gross** -- no liabilities are netted -- so a reserve
+ratio built from them overstates what is actually available. They are not
+comparable to a GAAP fund balance from an ACFR.
+}
+

man/cog_categories.Rd

@@ -7,8 +7,8 @@
 cog_categories(type = NULL, pattern = NULL)
 }
 \arguments{
-\item{type}{Either `NULL` (default, return both spending and revenue
-rows), `"spending"`, or `"revenue"`.}
+\item{type}{Either `NULL` (default, every row: expenditure, revenue and
+balance), `"spending"`, `"revenue"`, or `"balance"`.}
 
 \item{pattern}{Optional regex matched case-insensitively against the
 `category` column (e.g. `"Police"` or `"Tax"`).}
@@ -22,7 +22,15 @@ Tibble with columns `category`, `category_type`, `subtype`,
 Returns the category taxonomy exposed by the corpus's
 `summary_categories` view, grouped to one row per
 `(category, subtype)` pair. Use this to discover valid `category`
-values for [cog_spending()] / [cog_revenue()] /
+values for [cog_spending()] / [cog_revenue()] / [cog_balances()] /
 [cog_geographic_rollup()] and to audit which Census item codes feed
 each category.
 }
+\details{
+`subtype` COALESCEs the crosswalk's three subtype columns, so it carries
+`spend_subtype` on expenditure rows, `revenue_subtype` on revenue rows and
+`balance_subtype` on balance rows. Note that [cog_balances()] itself takes
+no `subtype` argument — for holdings, `category` is a strict coarsening of
+`balance_subtype` — but the value is surfaced here because it is the
+discovery surface downstream consumers build their vocabulary from.
+}

man/cog_find_peers.Rd

@@ -11,7 +11,8 @@ cog_find_peers(
   same_state = FALSE,
   pop_range = c(0.7, 1.3),
   is_ratio = TRUE,
-  max_peers = 10L
+  max_peers = 10L,
+  coverage = c("all", "census", "consistent")
 )
 }
 \arguments{
@@ -34,6 +35,14 @@ target's population at `year` to produce absolute bounds. If `FALSE`,
 `pop_range` is interpreted as absolute population counts.}
 
 \item{max_peers}{Integer cap on the number of peers returned.}
+
+\item{coverage}{Survey-cycle handling; see [cog_peer_compare()]. Here it
+governs the cohort VINTAGE when `year` is `NULL`: `"census"` snaps to the
+most recent census year with an observed population, so a cohort is not
+built from a sample year in which most of the candidate universe is
+absent. `"consistent"` needs a year range, which cohort selection does not
+have, so it selects like `"all"` and is carried on the result as
+`attr(x, "coverage")` for [cog_peer_compare()].}
 }
 \value{
 Tibble with columns `canonical_govid`, `gov_name`, `fips_state`,

man/cog_geographic_rollup.Rd

@@ -10,7 +10,8 @@ cog_geographic_rollup(
   years,
   per_capita = FALSE,
   adjust_to_year = NULL,
-  expenditure_concept = c("direct", "total")
+  expenditure_concept = c("primary", "direct", "total"),
+  coverage = c("all", "census", "consistent")
 )
 }
 \arguments{
@@ -29,12 +30,32 @@ are excluded from the result.}
 
 \item{adjust_to_year}{Integer base year for CPI-U conversion, or `NULL`.}
 
-\item{expenditure_concept}{`"direct"` (default) or `"total"`. Currently only
-`"direct"` is accepted; the `"total"` option exists in [cog_spending()] for
-single-government queries but cannot be used here because combining Total
-across multiple layers of government double-counts intergovernmental
-transfers (a state's payment to a school district is the same dollar the
-district reports as its own Direct spending).}
+\item{expenditure_concept}{`"primary"` (default), `"direct"`, or
+`"total"` -- see [cog_spending()] for the three concepts. `"total"` is
+refused here because combining Total across multiple layers of
+government double-counts intergovernmental transfers (a state's payment
+to a school district is the same dollar the district reports as its own
+Direct spending); `"primary"` and `"direct"` combine safely.}
+
+\item{coverage}{How to handle the Census of Governments survey cycle,
+  which is a **complete census only in years ending in 2 and 7** -- every
+  other year is a sample, and the sample varies enormously (on the bundled
+  fixture, Wisconsin's 608-city universe reports 597 governments in FY2012
+  and 112 in FY2019).
+
+  * `"all"` (default) -- every unit that reported that year. Unchanged
+    behaviour, so existing code keeps working.
+  * `"census"` -- census years only. Aborts if the requested range holds
+    none, rather than silently returning nothing.
+  * `"consistent"` -- only units reporting in *every* requested year, giving
+    a balanced panel.
+
+  Regardless of mode, `provenance$coverage` always carries per-year
+  `n_units_reporting`, `n_units_expected` and `is_census_year`, and
+  `provenance$coverage_mode` records the mode. `is_census_year` is a
+  statement about the **survey calendar**, never a claim of completeness:
+  FY1967 is a census year in which only 97 of Wisconsin's 608 cities
+  report. `n_units_reporting` is the number that tells the truth.}
 }
 \value{
 Tibble with columns `year`, `layer`, `canonical_govid`, `gov_name`,

man/cog_gov_search.Rd

@@ -32,8 +32,11 @@ the cross-vintage canonical-government registry. Operates in two modes:
 }
 \details{
 * **Utility mode** (single `name`, the original behavior): returns all
-  rows whose `gov_name` matches the regex case-insensitively, sorted by
-  `population_acs` descending. Useful for exploratory lookups.
+  rows whose `gov_name` contains `name` as a **literal, case-insensitive
+  substring**, sorted by `population_acs` descending. Useful for
+  exploratory lookups. Regex metacharacters in `name` are escaped, so a
+  government is findable by its own complete name even when that name
+  contains parentheses or a period.
 * **Basket mode** (`length(name) > 1`): resolves each input row to a
   single canonical govid and returns a tibble in input order, suitable
   for piping straight into [cog_spending()] / [cog_revenue()] /
@@ -45,7 +48,8 @@ the cross-vintage canonical-government registry. Operates in two modes:
 1. Filter `canonical_fips_xwalk` by `state` and (if non-NA) `type`.
 2. **Exact pass:** case-insensitive equality against `gov_name`.
    Single hit -> resolved. Multiple -> step 4.
-3. **Substring fallback:** case-insensitive regex against `gov_name`.
+3. **Substring fallback:** case-insensitive literal substring against
+   `gov_name` (metacharacters escaped).
    Single hit -> resolved (`match_method = "substring"`). Zero hits ->
    `status = "no_match"`. Multiple hits -> step 4.
 4. **Disambiguation:** if matches share one `govs_type`, pick the
@@ -58,7 +62,7 @@ inputs (`ambiguous` / `no_match`) appear only in the sidecar.
 }
 \examples{
 \dontrun{
-# Utility mode — exploratory regex lookup
+# Utility mode — exploratory substring lookup
 cog_gov_search("broward", state = "FL")
 
 # Basket mode — resolve a known cohort

man/cog_peer_compare.Rd

@@ -11,7 +11,8 @@ cog_peer_compare(
   years,
   per_capita = TRUE,
   adjust_to_year = NULL,
-  expenditure_concept = c("direct", "total")
+  expenditure_concept = c("primary", "direct", "total"),
+  coverage = c("all", "census", "consistent")
 )
 }
 \arguments{
@@ -29,10 +30,37 @@ population.}
 
 \item{adjust_to_year}{Integer base year for CPI-U conversion or `NULL`.}
 
-\item{expenditure_concept}{`"direct"` (default) or `"total"`. Currently only
-`"direct"` is accepted; the `"total"` option exists in [cog_spending()] for
-single-government queries but cannot be used here because combining Total
-across peer sets counts intergovernmental transfers twice.}
+\item{expenditure_concept}{`"primary"` (default), `"direct"`, or
+`"total"` -- see [cog_spending()] for the three concepts. `"total"` is
+refused here because combining Total across peer sets counts
+intergovernmental transfers twice; `"primary"` and `"direct"` combine
+safely.}
+
+\item{coverage}{How to handle the Census of Governments survey cycle,
+  which is a **complete census only in years ending in 2 and 7** -- every
+  other year is a sample, and the sample varies enormously (on the bundled
+  fixture, Wisconsin's 608-city universe reports 597 governments in FY2012
+  and 112 in FY2019).
+
+  * `"all"` (default) -- every unit that reported that year. Unchanged
+    behaviour, so existing code keeps working.
+  * `"census"` -- census years only. Aborts if the requested range holds
+    none, rather than silently returning nothing.
+  * `"consistent"` -- only units reporting in *every* requested year, giving
+    a balanced panel.
+
+  Regardless of mode, `provenance$coverage` always carries per-year
+  `n_units_reporting`, `n_units_expected` and `is_census_year`, and
+  `provenance$coverage_mode` records the mode. `is_census_year` is a
+  statement about the **survey calendar**, never a claim of completeness:
+  FY1967 is a census year in which only 97 of Wisconsin's 608 cities
+  report. `n_units_reporting` is the number that tells the truth.
+
+  The comparison target is exempt from `"consistent"` balancing -- it is the
+  subject of the comparison, not a member of the cohort -- and the
+  `summary_*` quantiles are computed AFTER the filter, so they describe the
+  cohort actually returned. `n_units_reporting` counts peers only, against
+  the cohort size: "3 of your 15 peers reported in FY2019".}
 }
 \value{
 Tibble matching [cog_spending()]'s columns, plus a `role`
@@ -43,11 +71,39 @@ Tibble matching [cog_spending()]'s columns, plus a `role`
   `attr(peers, "cohort_year")`; `NA` when `peers` was a bare character
   vector). Provenance reports `verb = "cog_peer_compare"`, `peer_count`,
   `cohort_year`, and `cohort_govids`.
+
+  **The `summary_*` rows are per-category quantiles: they are not additive.**
+  Each one is computed **within each `(year, spend_subtype,
+  category)` cell** across the peer set, so a `summary_p50` row is *the
+  median peer's value in that one category*, not *the value of the median
+  peer's total*. The median peer for Police and the median peer for Fire
+  are usually different governments, so summing `summary_*` rows across
+  categories does not give any peer's total and misstates the band it
+  appears to describe — measured at −32.7% to +251.0% across 24 years on
+  one cohort, with a sign flip at FY2012.
+
+  Facet by `role` **and** `category` (the documented use, and what the
+  rows are built for). For a genuine "median peer's total spending" line,
+  sum each peer's own categories first and take the quantile of those
+  per-government totals:
+
+  ```r
+  library(dplyr)
+  cmp |>
+    filter(role %in% c("target", "peer")) |>
+    group_by(year, role, canonical_govid) |>
+    summarise(total = sum(amt_per_capita_real, na.rm = TRUE), .groups = "drop") |>
+    filter(role == "peer") |>
+    group_by(year) |>
+    summarise(p50 = quantile(total, 0.5, na.rm = TRUE))
+  ```
 }
 \description{
 Pulls spending for the target plus a peer set (either a
 [cog_find_peers()] result or a character vector of `canonical_govid`) and
 appends peer-distribution summary rows (`summary_p25`, `summary_p50`,
 `summary_p75`) so the result can be faceted by `role` in a single ggplot
-call.
+call. Those summary rows are quantiles **within each category**, not
+quantiles of each peer's total — see the `@return` section before summing
+them.
 }

man/cog_revenue.Rd

@@ -11,7 +11,9 @@ cog_revenue(
   per_capita = FALSE,
   adjust_to_year = NULL,
   basis = c("harmonized", "raw"),
-  recipe = NULL
+  recipe = NULL,
+  revenue_concept = c("general", "total"),
+  complete = FALSE
 )
 }
 \arguments{
@@ -55,12 +57,62 @@ argument is ignored and the result's provenance reports
 `basis = "recipe"` with an inert `harmonization` block (`applied =
 FALSE`, pointing at the `recipe` block instead) rather than a
 possibly-misleading `"harmonized"`/`"raw"` value.}
+
+\item{revenue_concept}{Which of Census's two published revenue concepts to
+  return. Concepts are defined as sets of the crosswalk's `revenue_subtype`
+  values -- never as item-code first letters, which cannot classify
+  correctly (prefix `Y` spans revenue, expenditure and balance codes, and
+  prefix `X` does the same):
+
+  * `"general"` (default) -- Census General Revenue: `own_source` +
+    `federal` + `state` + `local_aid`. The manual defines this concept by
+    subtraction (section 4.3: *"General revenue comprises all revenue
+    except that classified as liquor store, utility, or insurance trust
+    revenue"*), so utility (`A91`-`A94`), liquor store (`A90`) and
+    insurance trust revenue are all excluded.
+  * `"total"` -- Census Total Revenue: every revenue subtype, i.e.
+    `general` plus utility, liquor store, and insurance trust revenue
+    (`Y01`/`Y02`/`Y04`/`Y11`/`Y12`/`Y51`/`Y52` and the employee-retirement
+    `X01`/`X02`/`X05`/`X08`).
+
+  The two are related by Census's own identity, `Total Revenue = General +
+  Utility + Liquor Store + Insurance Trust`.
+
+  Note that the employee-retirement (`X`) codes stop at FY2016, when those
+  systems moved out of the annual finance file into the separate Annual
+  Survey of Public Pensions, so a `"total"` series steps down at the
+  FY2016/FY2017 seam for reasons that are about collection scope rather
+  than revenue (series breaks `SB197`-`SB202`).}
+
+\item{complete}{If `TRUE`, fill the requested grid so that a cell the
+  corpus does not carry still appears, labelled with **why** it is
+  missing, and add a `value_source` column to every row:
+
+  * `"reported"` — the corpus carries this cell.
+  * `"census_zero"` — dense-source year (`<= FY2011`), cell absent:
+    Census published `$0`. `amt_nominal` is `0`.
+  * `"not_reported"` — sparse-source year (`>= FY2012`), cell absent: the
+    government did not report, and the value is unknown. `amt_nominal` is
+    `NA`, **not** `0` — writing a zero there would invent data.
+
+  The grid comes from the corpus's `code_set` table, scoped to each
+  government's own type, so a county is never filled with cells only a
+  state can report. Reported rows are passed through untouched.
+
+  Defaults to `FALSE` (the historical behaviour: absent cells simply do
+  not appear). Needs a corpus published from 2026-07-29 onward, which is
+  when `representation`/`code_set` began shipping; aborts with class
+  `uscogdata_representation_unavailable` otherwise. Not available with
+  `recipe` or with `expenditure_concept = "total"` (class
+  `uscogdata_complete_unsupported`) — neither draws its cells from
+  `code_set`.}
 }
 \value{
 Tibble with columns `year`, `canonical_govid`, `gov_name`,
   `revenue_subtype`, `category`, `amt_nominal`, optional `amt_real`,
   optional `amt_per_capita_nominal`, optional `amt_per_capita_real`,
-  optional `pop_source`, `codes_included`, `aggregate_fallback`, `notes`.
+  optional `pop_source`, `codes_included`, `aggregate_fallback`, `notes`,
+  and `value_source` when `complete = TRUE`.
 }
 \description{
 Mirror of [cog_spending()] for revenue categories. One row per

man/cog_spending.Rd

@@ -12,7 +12,8 @@ cog_spending(
   adjust_to_year = NULL,
   basis = c("harmonized", "raw"),
   recipe = NULL,
-  expenditure_concept = c("direct", "total")
+  expenditure_concept = c("primary", "direct", "total"),
+  complete = FALSE
 )
 }
 \arguments{
@@ -57,22 +58,34 @@ argument is ignored and the result's provenance reports
 FALSE`, pointing at the `recipe` block instead) rather than a
 possibly-misleading `"harmonized"`/`"raw"` value.}
 
-\item{expenditure_concept}{`"direct"` (default) returns only the
-government's own direct spending (item codes `E`/`F`/`G`), unchanged
-from prior releases. `"total"` additionally UNIONs in the
-intergovernmental leg -- payments to local governments (`M` codes) and
-to the state government (`L` codes, excluding the `L--` family-total
-rollup) -- so results gain rows with `spend_subtype ==
-"intergovernmental"`. Requires the active corpus's `summary_categories`
-to carry M/L rows (added by cog_pipeline PR #59); aborts with class
-`uscogdata_ig_categories_unsupported` on an older corpus rather than
-silently under-reporting. Mutually exclusive with `recipe` (a recipe
-already defines its own component codes). **Do not sum `"total"`
-results across levels of government** (e.g. state + county + city):
-a state's `M12` payment to a school district is the same dollar the
-district reports as its own direct `E12`, so summing both double-counts
-it. This matters in particular with [cog_geographic_rollup()], which
-sums across exactly that kind of multi-layer government set.
+\item{expenditure_concept}{Which spending concept to return. Concepts are
+  defined as sets of the crosswalk's `spend_subtype` values -- never as
+  item-code first letters, which cannot classify correctly (prefix `Y`
+  alone spans revenue, expenditure, and balance codes):
+
+  * `"primary"` (default) -- the government's own service provision:
+    `operations` + `capital` + `assistance` subtypes.
+  * `"direct"` -- Census's published Direct Expenditure: `primary` plus
+    `interest` (interest on debt) and `insurance_benefits` (insurance
+    trust benefit payments, e.g. pensions -- Census manual section
+    5.2.2.1 includes payments to retirees in Direct).
+  * `"total"` -- `direct` plus the intergovernmental leg: payments to
+    local governments (`M` codes), to the state government (`L` codes,
+    excluding the `L--` family-total rollup), and state payments to
+    school systems (`Q11`/`Q12`/`Q18`), so results gain rows with
+    `spend_subtype == "intergovernmental"`. Requires the active corpus's
+    `summary_categories` to carry M/L rows (added by cog_pipeline PR
+    #59); aborts with class `uscogdata_ig_categories_unsupported` on an
+    older corpus rather than silently under-reporting. Mutually
+    exclusive with `recipe` (a recipe already defines its own component
+    codes).
+
+  **Do not sum `"total"` results across levels of government** (e.g.
+  state + county + city): a state's `M12` payment to a school district is
+  the same dollar the district reports as its own direct `E12`, so
+  summing both double-counts it. This matters in particular with
+  [cog_geographic_rollup()], which sums across exactly that kind of
+  multi-layer government set.
 
   In the legacy wide era (<= FY2011), some functions are published ONLY
   as an aggregate-flagged family total (e.g. Corrections' `E04`/`E05`
@@ -85,13 +98,39 @@ component (when one exists), and
   `provenance$expenditure_concept_direct_suppressed` is `TRUE` -- the
   figure in those rows is the intergovernmental leg alone, not Direct +
   IG.}
+
+\item{complete}{If `TRUE`, fill the requested grid so that a cell the
+  corpus does not carry still appears, labelled with **why** it is
+  missing, and add a `value_source` column to every row:
+
+  * `"reported"` — the corpus carries this cell.
+  * `"census_zero"` — dense-source year (`<= FY2011`), cell absent:
+    Census published `$0`. `amt_nominal` is `0`.
+  * `"not_reported"` — sparse-source year (`>= FY2012`), cell absent: the
+    government did not report, and the value is unknown. `amt_nominal` is
+    `NA`, **not** `0` — writing a zero there would invent data.
+
+  The grid comes from the corpus's `code_set` table, scoped to each
+  government's own type, so a county is never filled with cells only a
+  state can report. Reported rows are passed through untouched.
+
+  Defaults to `FALSE` (the historical behaviour: absent cells simply do
+  not appear). Needs a corpus published from 2026-07-29 onward, which is
+  when `representation`/`code_set` began shipping; aborts with class
+  `uscogdata_representation_unavailable` otherwise. Not available with
+  `recipe` or with `expenditure_concept = "total"` (class
+  `uscogdata_complete_unsupported`) — neither draws its cells from
+  `code_set`.}
 }
 \value{
 Tibble with columns `year`, `canonical_govid`, `gov_name`,
   `spend_subtype`, `category`, `amt_nominal`, optional `amt_real`,
   optional `amt_per_capita_nominal`, optional `amt_per_capita_real`,
-  optional `pop_source`, `codes_included`, `aggregate_fallback`, `notes`.
-  Carries a `provenance` attribute matching `inst/schemas/provenance-v1.json`.
+  optional `pop_source`, `codes_included`, `aggregate_fallback`, `notes`,
+  and `value_source` when `complete = TRUE`.
+  Carries a `provenance` attribute matching `inst/schemas/provenance-v1.json`,
+  whose `completion` block reports `applied`, `rows_filled`, and the
+  per-year `absence_means` rule that was applied.
 }
 \description{
 One row per `(year, canonical_govid, spend_subtype, category)`. Amounts are

specs/2026-08-03-cog-balances-design.md

@@ -0,0 +1,281 @@
+# `cog_balances()` — a reader surface for cash and security holdings
+
+**Issue:** `uscogdata#25` requirement 2 · **Downstream:** `cog-api#26`
+**Date:** 2026-08-03 · **Status:** design, awaiting approval
+
+Requirement 1 of `uscogdata#25` (no `balance` row may reach a money verb) shipped
+with `#11`/`#12` and is asserted at both view and verb level. This spec covers
+requirement 2 only: a way to query holdings.
+
+## Decision: a verb, not an argument
+
+`cog_balances()`, parallel to `cog_spending()` / `cog_revenue()`.
+
+Holdings are a **stock** — a balance at a point in time — while the money verbs
+return **flows** over a fiscal year. The flow verbs' whole argument vocabulary
+is meaningless for a stock: `expenditure_concept` / `revenue_concept` describe
+which flows Census aggregates into a published total, and `complete=` fills a
+grid of fiscal-year cells. Overloading a money verb would put a stock behind
+arguments that all assume a flow.
+
+## The 14 codes
+
+Measured against the published corpus 2026-08-03, not transcribed from the
+issue. `year_min`/`year_max` are observed row extents.
+
+| `balance_subtype` | `category` | codes | observed years |
+|---|---|---|---|
+| `general` | Fund Balances | `W01`, `W31`, `W61` | 2012–2021 |
+| `employee_retirement` | Retirement System Holdings | `X21`, `X42`, `X44` | 1967–2016 |
+| | | `X47` | 1988–2016 |
+| | | `X30`, `Z77`, `Z78` | 2012–2016 |
+| `unemployment_trust` | Insurance Trust Balances | `Y07`, `Y08` | 1967–2023 |
+| `workers_comp_trust` | Insurance Trust Balances | `Y21` | 2012–2023 |
+| `other_insurance_trust` | Insurance Trust Balances | `Y61` | 2012–2023 |
+
+## Architecture
+
+### Two new views
+
+Mirroring the `revenue_long` / `revenue_annotated` pair exactly:
+
+- `inst/sql/26-balance_long.sql` — `category_type = 'balance' AND NOT is_aggregate`
+- `inst/sql/46-balance_annotated.sql` — joins `canonical_fips_xwalk` and
+  `summary_categories`, exposing `category`, `category_type`, `balance_subtype`
+
+`.register_views()` globs `inst/sql/*.sql` in sorted order, so both register
+with no new registration code.
+
+### A third gate list in `R/views.R`
+
+`CREATE VIEW` resolves its source schema eagerly, so a missing **column** fails
+at registration time, not at query time. `46-balance_annotated.sql` selects
+`c.balance_subtype`, which exists only on corpora built after pipeline `#76`/`#77`.
+That arrived without a `schema_version` bump, so neither existing gate applies:
+`.harmonization_view_files` keys on `schema_version`, `.representation_view_files`
+on the presence of a *file*. The discriminator here is a **column on an existing
+table**.
+
+```r
+.balance_view_files <- c("26-balance_long.sql", "46-balance_annotated.sql")
+```
+
+gated by probing `summary_categories` for `balance_subtype`, with
+`cog_balances()` erroring cleanly via `.require_balance_support()` on an older
+corpus — mirroring how `.require_schema_v5()` gates the harmonized views.
+
+### `R/balances.R` — a dedicated path, not `.verb_spendrev()`
+
+`.verb_spendrev()` is 825 lines whose concept scoping, intergovernmental leg and
+`complete=` grid are all flow-specific, and four verbs depend on it. Threading a
+third mode through it adds branching to shared code for no reuse benefit.
+
+Reused unchanged: `.build_provenance()`, `.build_series_break_refs()`,
+`.build_corpus_break_refs()`, the population join, `.inflate()`, and
+`.coerce_govid_input()`.
+
+Following the package's real two-layer convention: **view definitions** live in
+`inst/sql/`; **query construction** is inline `sprintf()` in R, as in
+`.verb_spendrev()`. (`CLAUDE.md` currently states "never inline SQL strings in R
+files", which the verb layer has never obeyed. Corrected in a separate commit —
+see Out of scope.)
+
+## Signature
+
+```r
+cog_balances(govid, years,
+             category       = NULL,   # Fund Balances | Insurance Trust Balances |
+                                      # Retirement System Holdings
+             per_capita     = FALSE,
+             adjust_to_year = NULL,
+             basis          = c("harmonized", "raw"),
+             recipe         = NULL)
+```
+
+Returns a `tbl_df` with a `provenance` attribute, like every other verb.
+
+**Absent by design:** `expenditure_concept`, `revenue_concept`, `complete`,
+and `subtype` — see below.
+
+**`per_capita` is offered.** Holdings per resident is a real measure (pension
+assets per capita, fund balance per resident). The roxygen `@param` states
+plainly that this is a *stock per resident* and is **not** comparable to
+`cog_spending()`'s per-capita figures.
+
+**`basis` is currently a no-op** — `harmonization_map` has zero balance-code
+rows, so harmonized and raw are identical for holdings. Kept for uniformity
+with the money verbs (the API would otherwise special-case), and
+`provenance$basis_note` says so outright rather than letting it look meaningful.
+
+**`recipe` ships in v1 and works.** The two holdings recipes bridge the wide era
+to the modern one:
+
+```
+cash_securities_z77_wide = X40 (1967-2011) + Z77 (2012-2023)
+cash_securities_z78_wide = X41 (1967-2011) + Z78 (2012-2023)
+```
+
+`X40`/`X41` carry ~42,700 rows that are **100% `is_aggregate = TRUE`**, so they
+are invisible to `balance_long`, which filters `NOT is_aggregate` like every
+other basis view. That is by design, not a defect:
+`cog_pipeline/docs/phase_r_harmonization_review.md` § 0.2 records that the wide
+era exposes these split families *only* as aggregates, and that the recipe join
+must therefore **not** filter `is_aggregate` — safe by construction, because
+wide rows (≤2011) are aggregate-only, modern rows (2012+) are leaf-only, and
+every component is year-scoped, so no double-count is possible. § 1 records the
+matching decision that the planned `X40→Z77` harmonization *map* rows were
+dropped and the continuity ships as recipes instead, which is why
+`harmonization_map` has no balance-code rows.
+
+The reader already implements this (`R/recipes.R`, `R/spending.R`), and it is
+verified rather than assumed: `corrections_combined` for FY2007 — a recipe whose
+wide leg `E05` is likewise aggregate-only — returns $906,743,000 against the
+live corpus. So a recipe query reaches rows the verb's own view cannot, exactly
+as intended.
+
+### No `subtype` argument: `category` is a strict coarsening
+
+`balance` is the only `category_type` in which `category` and the subtype column
+are **not** orthogonal. Measured against the published crosswalk:
+
+| `category_type` | subtypes spanning more than one category |
+|---|---|
+| expenditure | 5 of 6 (`operations`, `capital`, `interest`, `assistance`, `intergovernmental`) |
+| revenue | 1 of 7 (`own_source`) |
+| **balance** | **0 of 5** |
+
+For expenditure the two axes are a genuine cross-tab — *function* (Police, Fire)
+× *economic character* (operations, capital) — so both earn their place. For
+balance the relation is a strict tree:
+
+```
+Fund Balances              = {general}                                W01 W31 W61
+Retirement System Holdings = {employee_retirement}                    X21 X30 X42 X44 X47 Z77 Z78
+Insurance Trust Balances   = {unemployment_trust,
+                              workers_comp_trust,
+                              other_insurance_trust}                  Y07 Y08 Y21 Y61
+```
+
+Exposing both would therefore admit no useful combination. Of the 15 possible
+pairs, 3 are redundant (the subtype already implies its category) and **12 are
+guaranteed empty for every government in every year** — and an impossible query
+would fail by returning an empty tibble, which reads as "this government holds
+none" rather than "you asked a contradiction."
+
+Dropping `subtype` also keeps the verb aligned with the rest of the package: no
+uscogdata verb exposes a subtype argument. `subtype_col` is internal plumbing in
+`.verb_spendrev()`, and the API layers its own `subtype` row filter on top
+(`api/R/handlers_governments.R`). `cog-api#26` can do exactly that for
+`/balances`.
+
+`#25`'s hard requirement is still met — `category = "Fund Balances"` *is* the
+`general` family, precisely `W01`/`W31`/`W61`, in one filter. The only loss is
+isolating one of the three insurance funds in a single argument;
+`balance_subtype` remains a returned column, so that is one `dplyr::filter()`
+away.
+
+## Caveat surfacing
+
+`provenance$balance_caveats`, always present, plus one `cli_inform()` per
+session per caveat class when a query actually touches an affected family or
+year. Structured so `cog-api#26` can forward the fields verbatim.
+
+Verified against `series_breaks.csv`, not assumed:
+
+| # | Caveat | Covered by existing machinery? |
+|---|---|---|
+| 1 | Gross holdings, **not GAAP fund balance**; no liabilities netted | No — a constant, new field `not_gaap = TRUE` |
+| 2 | `W` is FY2012–2021 only | No — new `coverage_window`, **computed** from the corpus |
+| 3 | `X`/`Z` holdings end FY2016 | **Not yet.** No `series_breaks` row exists at 2016/2017 for `Z77`/`Z78`/`X30`. Reader surfaces it via `coverage_window`; flows through `series_break_refs` once the upstream entry lands (see Out of scope) |
+| 4 | `X40`/`X41` book → market at FY2002 | **Yes**, via `SB195`/`SB196` on `fin_code` `X40`/`X41`, under **two** conditions: a `recipe` query (the only path that observes those codes) **and** a year span that crosses FY2002. Asserted in the tests rather than assumed |
+
+On caveat 4's second condition: `.build_series_break_refs()` matches
+`break_year BETWEEN min(years) AND max(years)`, so a request spanning only
+2011–2012 does **not** surface `SB195`. That is correct, not a gap — such a
+series sits entirely after the change, on one consistent basis, and flagging a
+break it never crosses would be noise. The same rule is applied deliberately in
+`.build_corpus_break_refs()`. An earlier draft of this row omitted the span
+condition and overclaimed.
+
+`coverage_window` is derived per observed subtype family from the corpus, never
+hardcoded, so it stays correct as the corpus grows.
+
+`series_break_refs` and `corpus_break_refs` are otherwise populated by the
+existing code-driven builders and need no change.
+
+## Testing
+
+New `tests/testthat/test-balances.R`. The bundled fixture covers all four
+fixture years — `W` in 2012/2019/2020, the `X`/`Z` family in 2011/2012, `Y`
+throughout — so every test below runs offline.
+
+- **Inverse guard.** No flow code ever appears in `cog_balances()`, complementing
+  the already-asserted forward guard. Absence is verified against the raw corpus
+  via `read_parquet` on `data/long`, never through the verb that creates it.
+- **FY2016 seam.** The `X`/`Z` family is present in 2012 and absent in 2019;
+  `coverage_window` reports the termination and the console message fires once.
+- **Caveats.** `not_gaap` is always `TRUE`; `coverage_window` matches the
+  measured table above; the FY2002 valuation caveat fires only when the year
+  range crosses 2002 *and* touches `employee_retirement`.
+- **`per_capita`.** `amt_per_capita_nominal == amt_nominal / population`.
+- **`category = "Fund Balances"` is the `general` family.** Returns exactly
+  `W01`/`W31`/`W61` and nothing else — `#25`'s one-filter requirement, asserted
+  rather than assumed.
+- **The hierarchy holds.** Every `balance_subtype` in the crosswalk maps to
+  exactly one `category`. Asserted against the crosswalk so that an upstream
+  change breaking the tree — which would silently make `category` lossy —
+  fails here rather than in a user's analysis.
+- **`recipe` bridges the wide era.** `cash_securities_z77_wide` returns the
+  `X40` leg for a pre-2012 year, proving the aggregate-only wide rows are
+  reached — the property `phase_r_harmonization_review.md` § 0.2 depends on. A
+  regression here would silently truncate a 45-year series to five.
+- **`SB195`/`SB196` reach the user on that path.** A `recipe` query spanning
+  FY2002 carries both in `provenance$series_break_refs`, so the book → market
+  basis change is disclosed wherever `X40`/`X41` are actually observed.
+- **Gating.** `.require_balance_support()` errors cleanly on a corpus whose
+  `summary_categories` lacks `balance_subtype`.
+
+## Out of scope, tracked separately
+
+1. **Pipeline issue (new), non-blocking.** Catalogue the FY2016 termination of
+   the seven holdings codes in `series_breaks.csv`. There is currently **no**
+   entry at 2016/2017 for `Z77`/`Z78`/`X30`, although
+   `docs/phase_r_harmonization_review.md` § 2 identified the gap and recommended
+   exactly this — *"candidate new `series_breaks.csv` entries (recommend
+   `with_caution` documentation rows, no map action)"*. The follow-through never
+   happened. `SB197`–`SB202` set the precedent, giving the analogous X-flow
+   codes `coverage_restricted` + `with_caution` at 2017; `with_caution` is also
+   what keeps this out of the `joinable = "no"` identity-change rule, which
+   would otherwise oblige a harmonization-map row.
+
+   Verify the break corpus-wide and census-to-census before writing the rows.
+   `cog_balances()` does not wait on this — caveat 3 is covered reader-side by
+   `coverage_window` meanwhile, and the entry simply adds a second, catalogued
+   signpost when it lands.
+
+   **Superseded:** an earlier draft of this spec proposed adding
+   `summary_categories` rows for `X40`/`X41` and treated `recipe=` as blocked.
+   Both were wrong. `X40`/`X41` are deliberately aggregate-only per
+   `phase_r_harmonization_review.md` § 0.2, the dropped harmonization-map rows
+   are the documented § 1 decision, and the recipe path reaches them by design.
+2. **`cog-api#26`.** Adds `/balances` in all three required places — handler,
+   `param_contract`, and the `plumber.R` route signature. Lands after this.
+
+   **Two contract facts the API must carry forward**, both settled during
+   implementation and easy to get wrong from the outside:
+
+   - `provenance$balance_caveats$coverage_window` is **corpus-scoped, not
+     result-scoped**. It reports the observed year extent of *every* balance
+     subtype in the corpus, not only the subtypes a given query returned — so a
+     `category = "Fund Balances"` query still returns all five windows. That is
+     deliberate: the windows describe what the corpus holds, which is what a
+     consumer needs in order to know what it did *not* ask for. The sibling
+     field `truncated` is the result-scoped one. Documented in
+     `inst/schemas/provenance-v1.json` and mutation-guarded against silent
+     inversion.
+   - `balance_caveats` appears **only** on `cog_balances()` results. It is
+     absent from `cog_spending()`/`cog_revenue()` provenance, and the schema
+     says so — an API layer that assumes it is universal will read `NULL`.
+3. **`uscogdata/CLAUDE.md` refresh.** Separate commit. It is stale: it claims 7
+   SQL views (there are 21), 181 tests (716), a two-year fixture (four years),
+   and a "never inline SQL" rule the verb layer does not follow.

tests/testthat/helper-fixture.R

@@ -6,6 +6,33 @@ fixture_corpus_path <- function() {
   if (nzchar(p)) paste0(p, "/") else ""
 }
 
+# Path to a file in the SOURCE tree (README.md, man/*.Rd, vignettes/*.Rmd),
+# or "" when it isn't there.
+#
+# Tests that assert on documentation content have to read the sources, and the
+# sources only exist when the suite runs from a checkout. Under R CMD check the
+# suite runs from the INSTALLED package, where man/ and vignettes/ are not
+# shipped and `../../README.md` does not resolve -- so those tests must skip
+# rather than error. CI runs testthat::test_local() from the checkout BEFORE
+# rcmdcheck, so the assertions are still enforced on every push; this only
+# stops them from failing a context that structurally cannot satisfy them.
+source_tree_path <- function(...) {
+  p <- testthat::test_path("..", "..", ...)
+  if (file.exists(p)) p else ""
+}
+
+# Skip unless every named source file is present (see source_tree_path()).
+skip_if_no_source_tree <- function(...) {
+  paths <- vapply(list(...), function(rel) do.call(source_tree_path, as.list(rel)),
+                  character(1))
+  missing <- vapply(paths, function(p) !nzchar(p), logical(1))
+  testthat::skip_if(
+    any(missing),
+    "package source tree not available (running against the installed package)"
+  )
+  invisible(paths)
+}
+
 # Skip a test if no corpus is reachable (bundled fixture or explicit remote URL).
 skip_if_no_corpus <- function() {
   p <- fixture_corpus_path()
@@ -58,6 +85,42 @@ with_doctored_schema_version <- function(version, code) {
   force(code)
 }
 
+# Copy the bundled fixture to a temp dir with representation.parquet and
+# code_set.parquet removed (and dropped from the manifest's metadata list),
+# then run `code` against it. Models a corpus published BEFORE sparsification:
+# schema_version is left alone deliberately, because it was never bumped for
+# that change -- the pre-sparsification fixture this package shipped until
+# 2026-07-30 was schema v6 and carried neither table. Presence in the manifest
+# is therefore the only honest signal, and this helper is what proves the
+# package keys off it rather than off the version number.
+with_corpus_missing_representation <- function(code) {
+  src <- fixture_corpus_path()
+  tmp <- withr::local_tempdir(.local_envir = parent.frame())
+  file.copy(list.files(src, full.names = TRUE), tmp, recursive = TRUE)
+
+  dropped <- c("representation.parquet", "code_set.parquet")
+  file.remove(file.path(tmp, "data", dropped))
+
+  manifest_path <- file.path(tmp, "manifest.json")
+  m <- jsonlite::fromJSON(manifest_path, simplifyVector = FALSE)
+  m$files$metadata <- Filter(
+    function(f) !basename(f$path) %in% dropped, m$files$metadata
+  )
+  writeLines(
+    jsonlite::toJSON(m, auto_unbox = TRUE, pretty = TRUE, null = "null"),
+    manifest_path
+  )
+
+  old_url <- Sys.getenv("USCOGDATA_URL", unset = NA)
+  uscogdata:::cog_close()
+  Sys.setenv(USCOGDATA_URL = paste0(tmp, "/"))
+  on.exit({
+    uscogdata:::cog_close()
+    if (is.na(old_url)) Sys.unsetenv("USCOGDATA_URL") else Sys.setenv(USCOGDATA_URL = old_url)
+  }, add = TRUE)
+  force(code)
+}
+
 # Copy the bundled fixture to a temp dir with summary_categories.parquet
 # rewritten to drop every M/L (intergovernmental) row, then run `code`
 # against it with a clean session (mirrors with_fixture_corpus()/
@@ -91,3 +154,36 @@ with_corpus_missing_ig_categories <- function(code) {
   }, add = TRUE)
   force(code)
 }
+
+# Copy the bundled fixture to a temp dir with summary_categories.parquet
+# rewritten to DROP the balance_subtype column, then run `code` against it.
+# Models a corpus published before cog_pipeline #76/#77. schema_version is
+# left untouched deliberately: that change shipped without a version bump, so
+# column presence is the only honest signal -- this helper is what proves the
+# package keys off it. Mirrors with_corpus_missing_ig_categories().
+with_corpus_missing_balance_subtype <- function(code) {
+  src <- fixture_corpus_path()
+  tmp <- withr::local_tempdir(.local_envir = parent.frame())
+  file.copy(list.files(src, full.names = TRUE), tmp, recursive = TRUE)
+
+  cats_path <- file.path(tmp, "data", "summary_categories.parquet")
+  filtered_path <- file.path(tmp, "data", "summary_categories_filtered.parquet")
+  write_con <- DBI::dbConnect(duckdb::duckdb())
+  on.exit(DBI::dbDisconnect(write_con, shutdown = TRUE), add = TRUE)
+  DBI::dbExecute(write_con, sprintf(
+    "COPY (SELECT * EXCLUDE (balance_subtype) FROM read_parquet(%s))
+     TO %s (FORMAT PARQUET)",
+    uscogdata:::.sql_lit_chr(cats_path), uscogdata:::.sql_lit_chr(filtered_path)
+  ))
+  file.remove(cats_path)
+  file.rename(filtered_path, cats_path)
+
+  old_url <- Sys.getenv("USCOGDATA_URL", unset = NA)
+  uscogdata:::cog_close()
+  Sys.setenv(USCOGDATA_URL = paste0(tmp, "/"))
+  on.exit({
+    uscogdata:::cog_close()
+    if (is.na(old_url)) Sys.unsetenv("USCOGDATA_URL") else Sys.setenv(USCOGDATA_URL = old_url)
+  }, add = TRUE)
+  force(code)
+}

tests/testthat/helper-walkthrough-raw.R

@@ -0,0 +1,44 @@
+# Helper for the Madison-walkthrough finding tests (uscogdata #11-#16).
+#
+# Those tests all assert something about what a `cog_*` verb includes or
+# excludes. The expected amounts must therefore come from the RAW corpus, never
+# from the verb under test: verifying an absence through the filter that creates
+# it proves nothing. `wt_raw_*()` opens its own DuckDB connection straight onto
+# the corpus's `long` parquet partitions, bypassing uscogdata's SQL views (and
+# therefore its `flow_prefixes` filtering) entirely.
+
+wt_corpus_glob <- function() {
+  url <- Sys.getenv("USCOGDATA_URL")
+  if (!nzchar(url)) testthat::skip("USCOGDATA_URL is not set")
+  paste0(sub("/$", "", url), "/data/long/**/*.parquet")
+}
+
+wt_raw_query <- function(sql) {
+  con <- DBI::dbConnect(duckdb::duckdb())
+  on.exit(DBI::dbDisconnect(con, shutdown = TRUE), add = TRUE)
+  DBI::dbGetQuery(con, sql)
+}
+
+# Sum of `amt` (in $1,000s, as the corpus stores it) for one government-year,
+# restricted either to an explicit set of item codes or to a set of first-letter
+# prefixes. Aggregate rows are excluded, matching every published verb.
+wt_raw_amt <- function(govid, year, codes = NULL, prefixes = NULL) {
+  stopifnot(xor(is.null(codes), is.null(prefixes)))
+  filter_sql <- if (!is.null(codes)) {
+    paste0("item_code IN (", paste0("'", codes, "'", collapse = ", "), ")")
+  } else {
+    paste0("LEFT(item_code, 1) IN (", paste0("'", prefixes, "'", collapse = ", "), ")")
+  }
+  out <- wt_raw_query(paste0(
+    "SELECT COALESCE(SUM(amt), 0) AS amt FROM read_parquet('", wt_corpus_glob(), "') ",
+    "WHERE canonical_govid = '", govid, "' AND year = ", year,
+    " AND NOT is_aggregate AND ", filter_sql
+  ))
+  out$amt[[1]]
+}
+
+# The item codes a verb reports having summed, flattened out of the
+# comma-separated `codes_included` column.
+wt_codes_included <- function(df) {
+  sort(unique(trimws(unlist(strsplit(stats::na.omit(df$codes_included), ",")))))
+}

tests/testthat/test-amount-units-documented.R

@@ -0,0 +1,52 @@
+# Madison walkthrough audit -- finding F-004. Tracked as uscogdata#15.
+# See docs/walkthroughs/FINDINGS.md in cog_explorer.
+#
+# The raw Census files report thousands of dollars; this package multiplies by
+# 1000 and returns full US dollars. That is the friendlier choice and is not
+# wrong -- but cog_explorer's CLAUDE.md states "All raw `amt` values are in
+# $1,000s", so a reader who applies that rule to amt_nominal overstates every
+# figure by 1000x, and gets a plausible-looking number rather than an obvious
+# error. The audit rates this the highest-consequence definitional gap it found.
+#
+# Deliberately NOT asserted here: man/cog_spending.Rd and man/cog_revenue.Rd,
+# which ALREADY carry the statement in their @return sections (verified
+# 2026-07-29), as does cog-api's data-dictionary.md (since 2b71b41). The gap is
+# in the surfaces a reader meets first and in cog_explorer's own conventions
+# doc -- see uscogdata#15 for the full surface-by-surface table and for the two
+# secondary tasks (cog_explorer/CLAUDE.md, which has no git remote, and
+# cog-api's llms.txt, which is silent on units).
+
+test_that("returned amounts are documented as full US dollars where readers meet the package", {
+
+  # README and vignettes ship only in the source tree, not in the installed
+  # package, so these assertions cannot run under R CMD check -- CI's earlier
+  # testthat::test_local() step is what enforces them. See
+  # skip_if_no_source_tree() in helper-fixture.R.
+  docs <- skip_if_no_source_tree(
+    "README.md",
+    c("vignettes", "total-spending.Rmd"),
+    c("vignettes", "population-denominators.Rmd")
+  )
+
+  says_units <- function(path) {
+    txt <- paste(readLines(path, warn = FALSE), collapse = " ")
+    grepl("full US dollars|full U\\.S\\. dollars", txt, ignore.case = TRUE) &&
+      grepl("\\$1,000s|thousands of dollars", txt, ignore.case = TRUE)
+  }
+
+  for (path in docs) expect_true(says_units(path))
+
+  # Pin the documented claim to the actual behaviour, so the two cannot drift.
+  # The expected raw amount is read straight from the corpus's parquet
+  # partitions -- never through cog_spending(), which is the thing being
+  # described. Madison FY2020: E/F/G = 623,347 ($1,000s) -> $623,347,000.
+  raw_thousands <- wt_raw_amt("552025209777", 2020L, prefixes = c("E", "F", "G"))
+  expect_equal(raw_thousands, 623347)
+
+  returned <- cog_spending(govid = "552025209777", years = 2020L)
+  expect_equal(sum(returned$amt_nominal), raw_thousands * 1000)
+
+  units <- attr(returned, "provenance")$transformations$units_conversion
+  expect_true(units$applied)
+  expect_equal(units$multiplier, 1000)
+})

tests/testthat/test-balances.R

@@ -0,0 +1,539 @@
+test_that("balance views register and carry only balance codes", {
+  skip_if_no_corpus()
+  con <- cog_open()
+  on.exit(cog_close())
+
+  views <- DBI::dbGetQuery(con,
+    "SELECT table_name FROM information_schema.tables
+     WHERE table_schema = 'main' AND table_type = 'VIEW'"
+  )$table_name
+  expect_true(all(c("balance_long", "balance_annotated") %in% views))
+
+  # Every item_code in balance_long is a category_type = 'balance' member.
+  leak <- DBI::dbGetQuery(con,
+    "SELECT COUNT(*) AS n FROM balance_long
+     WHERE item_code NOT IN (
+       SELECT item_code FROM summary_categories WHERE category_type = 'balance')"
+  )$n
+  expect_identical(as.integer(leak), 0L)
+
+  # balance_annotated exposes the subtype column the verb groups on.
+  cols <- DBI::dbGetQuery(con,
+    "SELECT column_name FROM information_schema.columns
+     WHERE table_name = 'balance_annotated'"
+  )$column_name
+  expect_true(all(c("category", "category_type", "balance_subtype") %in% cols))
+})
+
+test_that("inst/sql/26-balance_long.sql enforces NOT is_aggregate (real SQL text, synthetic parquet)", {
+  # Every category_type = 'balance' item_code in the bundled fixture has
+  # is_aggregate = FALSE for every row of every year -- there is no real row
+  # that would be excluded ONLY by the `AND NOT is_aggregate` predicate. An
+  # assertion against the live fixture (`WHERE is_aggregate` returns 0) is
+  # therefore vacuous: it passes identically whether or not the view's
+  # predicate is present. As with the 22-/23- and 24-/25- tests above, this
+  # reads the real inst/sql/26-balance_long.sql text off disk and executes it
+  # -- plus its 10-long.sql / 11-summary_categories.sql dependencies -- against
+  # a synthetic hive-partitioned parquet tree that DOES contain an aggregate
+  # row under a real balance item_code (W01), so a regression that drops the
+  # predicate changes which rows survive.
+  skip_if_no_corpus()
+
+  tmp <- withr::local_tempdir()
+  part_dir <- file.path(tmp, "data", "long", "year=2004")
+  dir.create(part_dir, recursive = TRUE)
+  part_path <- file.path(part_dir, "part-0.parquet")
+
+  write_con <- DBI::dbConnect(duckdb::duckdb())
+  on.exit(DBI::dbDisconnect(write_con, shutdown = TRUE), add = TRUE)
+  DBI::dbExecute(write_con, sprintf("
+    COPY (
+      SELECT * FROM (VALUES
+        ('bal-A', 'W01', 100,    false), -- control: ordinary balance row, survives
+        ('bal-B', 'W01', 999999, true)   -- excluded ONLY by `NOT is_aggregate`
+      ) AS t(canonical_govid, item_code, amt, is_aggregate)
+    ) TO %s (FORMAT PARQUET)
+  ", uscogdata:::.sql_lit_chr(part_path)))
+
+  DBI::dbExecute(write_con, sprintf("
+    COPY (
+      SELECT * FROM (VALUES
+        ('W01', 'Fund Balances', 'balance', NULL, NULL, 'general')
+      ) AS t(item_code, category, category_type, spend_subtype, revenue_subtype, balance_subtype)
+    ) TO %s (FORMAT PARQUET)
+  ", uscogdata:::.sql_lit_chr(file.path(tmp, "data", "summary_categories.parquet"))))
+
+  sql_dir <- system.file("sql", package = "uscogdata")
+  .read_view_sql <- function(filename) {
+    txt <- paste(readLines(file.path(sql_dir, filename), warn = FALSE), collapse = "\n")
+    gsub("\\{url\\}", paste0(tmp, "/"), txt, fixed = FALSE)
+  }
+
+  con <- DBI::dbConnect(duckdb::duckdb())
+  on.exit(DBI::dbDisconnect(con, shutdown = TRUE), add = TRUE)
+  DBI::dbExecute(con, .read_view_sql("10-long.sql"))
+  DBI::dbExecute(con, .read_view_sql("11-summary_categories.sql"))
+  DBI::dbExecute(con, .read_view_sql("26-balance_long.sql"))
+
+  rows <- DBI::dbGetQuery(con,
+    "SELECT canonical_govid, item_code, amt FROM balance_long ORDER BY canonical_govid"
+  )
+  expect_equal(nrow(rows), 1L)
+  expect_equal(rows$canonical_govid, "bal-A")
+  expect_equal(rows$amt, 100)
+})
+
+test_that("balance views are skipped on a corpus without balance_subtype", {
+  skip_if_no_corpus()
+  with_corpus_missing_balance_subtype({
+    con <- cog_open()
+    on.exit(cog_close())
+    views <- DBI::dbGetQuery(con,
+      "SELECT table_name FROM information_schema.tables
+       WHERE table_schema = 'main' AND table_type = 'VIEW'"
+    )$table_name
+    # Registration must SKIP them, not error -- an older corpus stays usable.
+    expect_false(any(c("balance_long", "balance_annotated") %in% views))
+    expect_true("revenue_long" %in% views)
+
+    # ...and calling the verb on such a corpus must hit
+    # .require_balance_support()'s curated abort (spec § Testing: "Gating"),
+    # not a DuckDB binder error naming a view that was never registered.
+    # Asserted on the CLASS: removing the guard still errors, so a bare
+    # expect_error() would pass on the regression.
+    expect_error(
+      cog_balances("550000227544", 2019),
+      class = "uscogdata_no_balance_support"
+    )
+  })
+})
+
+test_that("cog_balances returns holdings for a government that has them", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    r <- cog_balances("550000227544", 2019)
+    expect_s3_class(r, "tbl_df")
+    expect_true(nrow(r) > 0L)
+    expect_true(all(c("year", "canonical_govid", "gov_name", "balance_subtype",
+                      "category", "amt_nominal") %in% names(r)))
+    expect_identical(sort(unique(r$category)),
+                     c("Fund Balances", "Insurance Trust Balances"))
+    expect_false(is.null(attr(r, "provenance")))
+    expect_identical(attr(r, "provenance")$verb, "cog_balances")
+  })
+})
+
+test_that('category = "Fund Balances" is exactly the general family', {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    r <- cog_balances("550000227544", 2019, category = "Fund Balances")
+    expect_identical(unique(r$balance_subtype), "general")
+    codes <- sort(unlist(strsplit(paste(r$codes_included, collapse = ","), ",")))
+    expect_identical(codes, c("W01", "W31", "W61"))
+  })
+})
+
+test_that("no flow code can reach cog_balances", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    r <- cog_balances("550000227544", c(2011, 2012, 2019, 2020))
+    got <- unique(unlist(strsplit(paste(r$codes_included, collapse = ","), ",")))
+
+    # The expected set is read from the RAW corpus, never from the verb --
+    # verifying an absence through the filter that creates it proves nothing.
+    # A fresh, direct DuckDB connection against the raw parquet files (never
+    # cog_open()'s session, never balance_long/balance_annotated) reads
+    # parquet natively -- no arrow dependency needed (see CLAUDE.md).
+    con2 <- DBI::dbConnect(duckdb::duckdb())
+    on.exit(DBI::dbDisconnect(con2, shutdown = TRUE), add = TRUE)
+    cats_path <- file.path(fixture_corpus_path(), "data", "summary_categories.parquet")
+    balance_codes <- DBI::dbGetQuery(con2, sprintf(
+      "SELECT item_code FROM read_parquet(%s) WHERE category_type = 'balance'",
+      uscogdata:::.sql_lit_chr(cats_path)
+    ))$item_code
+
+    expect_true(length(got) > 0L)
+    expect_true(all(got %in% balance_codes))
+  })
+})
+
+test_that("every balance_subtype maps to exactly one category", {
+  skip_if_no_corpus()
+  # Dropping the `subtype` argument is only safe while this tree holds. If the
+  # pipeline ever gives a balance subtype a second category, `category` becomes
+  # a lossy filter -- fail HERE rather than in a user's analysis. Read via a
+  # fresh direct DuckDB connection against the raw parquet file, not through
+  # any registered view.
+  con2 <- DBI::dbConnect(duckdb::duckdb())
+  on.exit(DBI::dbDisconnect(con2, shutdown = TRUE), add = TRUE)
+  cats_path <- file.path(fixture_corpus_path(), "data", "summary_categories.parquet")
+  b <- DBI::dbGetQuery(con2, sprintf(
+    "SELECT category, balance_subtype FROM read_parquet(%s) WHERE category_type = 'balance'",
+    uscogdata:::.sql_lit_chr(cats_path)
+  ))
+  per_subtype <- tapply(b$category, b$balance_subtype,
+                        function(x) length(unique(x)))
+  expect_true(all(per_subtype == 1L))
+})
+
+test_that("cog_balances records found + missing govids in provenance", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    suppressMessages(
+      r <- cog_balances(c("550000227544", "XXXINVALID"), 2019)
+    )
+    prov <- attr(r, "provenance")
+    expect_equal(sort(prov$scope$govids_found), "550000227544")
+    expect_equal(sort(prov$scope$govids_missing), "XXXINVALID")
+  })
+})
+
+test_that("per_capita divides holdings by population", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    plain <- cog_balances("550000227544", 2019, category = "Fund Balances")
+    pc    <- cog_balances("550000227544", 2019, category = "Fund Balances",
+                          per_capita = TRUE)
+    expect_true("amt_per_capita_nominal" %in% names(pc))
+    expect_true("pop_source" %in% names(pc))
+    expect_identical(pc$amt_nominal, plain$amt_nominal)
+
+    # Assert against the denominator read from the corpus, NOT against a
+    # quantity derived from amt_per_capita_nominal itself -- dividing the
+    # column back out would be tautological and would pass on any value.
+    pop <- DBI::dbGetQuery(cog_open(), sprintf(
+      "SELECT population FROM gov_population_yearly
+       WHERE canonical_govid = %s AND year = 2019",
+      uscogdata:::.sql_lit_chr("550000227544")
+    ))$population
+    expect_length(pop, 1L)
+    expect_equal(pc$amt_per_capita_nominal, pc$amt_nominal / pop,
+                 tolerance = 1e-8)
+
+    prov <- attr(pc, "provenance")
+    expect_true(prov$transformations$per_capita$applied)
+  })
+})
+
+test_that("adjust_to_year adds real dollars", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    r <- cog_balances("550000227544", 2012, category = "Fund Balances",
+                      adjust_to_year = 2020)
+    expect_true("amt_real" %in% names(r))
+    # 2012 dollars inflated to 2020 must exceed nominal.
+    expect_true(all(r$amt_real > r$amt_nominal))
+    prov <- attr(r, "provenance")
+    expect_true(prov$transformations$inflation$applied)
+    expect_identical(prov$transformations$inflation$base_year, 2020L)
+  })
+})
+
+test_that("per_capita and adjust_to_year compose", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    r <- cog_balances("550000227544", 2012, category = "Fund Balances",
+                      per_capita = TRUE, adjust_to_year = 2020)
+    expect_true("amt_per_capita_real" %in% names(r))
+    # The per-capita column must be deflated by the SAME factor as the level
+    # column -- this is what the ordering at R/balances.R:101-103 guarantees.
+    # .attach_real_dollars() silently no-ops on the per-capita leg when
+    # amt_per_capita_nominal does not exist yet (R/spending.R:664), so
+    # reversing those two calls drops this column with no error at all.
+    expect_equal(r$amt_per_capita_real / r$amt_per_capita_nominal,
+                 r$amt_real / r$amt_nominal, tolerance = 1e-8)
+
+    # And the documented condition is a conjunction: adjust_to_year ALONE
+    # must not produce amt_per_capita_real (pins the @return wording).
+    r2 <- cog_balances("550000227544", 2012, category = "Fund Balances",
+                       adjust_to_year = 2020)
+    expect_true("amt_real" %in% names(r2))
+    expect_false("amt_per_capita_real" %in% names(r2))
+  })
+})
+
+# --- input validation ------------------------------------------------------
+
+test_that("cog_balances validates its inputs like the money verbs", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    G <- "550000227544"
+    # Pinned to the message, not bare expect_error(): every one of these
+    # already produces *some* error or *some* quiet wrong answer today --
+    # years = integer(0) leaks `Parser Error ... AND year IN ()` with the
+    # generated SQL, recipe = c("a","b") throws "the condition has length > 1",
+    # and the govid/category cases return 0 rows with no error at all.
+    expect_error(cog_balances(G, integer(0)), "non-empty integer vector")
+    expect_error(cog_balances(character(0), 2019), "non-empty character vector")
+    expect_error(cog_balances(G, 2019, category = 5), "must be character or NULL")
+    expect_error(cog_balances(G, 2019, recipe = c("a", "b")),
+                 "length-1 character string")
+  })
+})
+
+test_that("validation runs after govid coercion, so a data frame still works", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    # .validate_verb_inputs() asserts is.character(govid); it must therefore
+    # run AFTER .coerce_govid_input(), never before, or the documented
+    # data-frame input (cog_gov_search() output) would abort.
+    df <- data.frame(canonical_govid = "550000227544", stringsAsFactors = FALSE)
+    r <- suppressMessages(cog_balances(df, 2019))
+    expect_true(nrow(r) > 0L)
+    expect_identical(unique(r$canonical_govid), "550000227544")
+  })
+})
+
+test_that("recipe and category are mutually exclusive", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    expect_error(
+      cog_balances("550000227544", c(2011, 2012),
+                   category = "Fund Balances",
+                   recipe   = "cash_securities_z77_wide"),
+      class = "uscogdata_recipe_category_conflict"
+    )
+  })
+})
+
+# --- recipe = : the wide-era holdings bridge -------------------------------
+
+test_that("recipe bridges the wide era into the modern one", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    r <- cog_balances("550000227544", c(2011, 2012),
+                      recipe = "cash_securities_z77_wide")
+    # .run_recipe()'s SQL returns `long.year` as a DOUBLE (a corpus-wide trait,
+    # not specific to this recipe -- see the money-verb recipe tests, which
+    # only ever assert on it with expect_equal), so compare numerically rather
+    # than with expect_identical()'s type-strict comparison.
+    expect_equal(sort(r$year), c(2011, 2012))
+
+    # The 2011 leg can ONLY come from X40, which is 100% is_aggregate = TRUE
+    # and therefore invisible to balance_long. If the recipe path ever starts
+    # filtering aggregates, a 45-year series silently truncates to five --
+    # this is the regression guard for phase_r_harmonization_review.md § 0.2.
+    codes <- attr(r, "provenance")$codes_summed$observed
+    expect_true("X40" %in% codes)
+    expect_true("Z77" %in% codes)
+    expect_true(all(r$amt_nominal > 0))
+
+    prov <- attr(r, "provenance")
+    expect_identical(prov$recipe$recipe_id, "cash_securities_z77_wide")
+  })
+})
+
+test_that("the FY2002 book-to-market basis change is disclosed on the recipe path", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    # 2002 is in the year vector deliberately, and must stay -- do not
+    # "simplify" this back to c(2011, 2012).
+    #
+    # .build_series_break_refs() (R/series_breaks.R, shared with every verb)
+    # matches breaks with `break_year BETWEEN min(years) AND max(years)`, and
+    # SB195's break_year is 2002. A c(2011, 2012) span never crosses the
+    # FY2002 book -> market change -- that whole span sits after it, on one
+    # consistent basis -- so NOT disclosing SB195 there is correct behaviour,
+    # not a gap (same reasoning as the "a request that never crosses the
+    # boundary is not affected by it" comment on .build_corpus_break_refs()).
+    #
+    # The property actually worth testing is: a recipe query that observes
+    # X40 AND spans FY2002 discloses SB195. This fixture has no 2002
+    # partition data for X40/Z77 (confirmed: only 2011/2012/2019/2020
+    # partitions exist), so including 2002 in `years` widens the
+    # break-matching window without changing which rows the recipe join
+    # returns -- verified empirically: r$year below is exactly {2011, 2012}
+    # whether or not 2002 is in the request (see task-4-report.md).
+    # Removing 2002 would silently turn this back into the non-crossing case
+    # above and destroy the test's purpose.
+    r <- cog_balances("550000227544", c(2002, 2011, 2012),
+                      recipe = "cash_securities_z77_wide")
+    expect_equal(sort(r$year), c(2011, 2012))
+    refs <- attr(r, "provenance")$series_break_refs
+    # SB195 sits on fin_code X40; it can only fire where X40 is observed,
+    # which is exactly the recipe path.
+    expect_true("SB195" %in% refs)
+  })
+})
+
+test_that("the second holdings bridge works too", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    # X41 -> Z78, the securities counterpart. Wisconsin carries X41 in 2011
+    # and Z78 in 2012, so both legs are exercised.
+    r <- cog_balances("550000227544", c(2011, 2012),
+                      recipe = "cash_securities_z78_wide")
+    codes <- attr(r, "provenance")$codes_summed$observed
+    expect_true(all(c("X41", "Z78") %in% codes))
+    expect_equal(sort(r$year), c(2011, 2012))
+  })
+})
+
+test_that("an unknown recipe id is rejected", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    # Asserted on the CLASS .validate_recipe_id() sets (R/recipes.R:84).
+    # Without it the test is non-discriminating: deleting the validation call
+    # leaves .recipe_components() returning 0 rows and comps$label[[1]]
+    # throwing "subscript out of bounds", which a bare expect_error() accepts
+    # while the user loses the curated "valid recipe ids are ..." message.
+    expect_error(
+      cog_balances("550000227544", 2019, recipe = "no_such_recipe"),
+      class = "uscogdata_unknown_recipe"
+    )
+  })
+})
+
+# --- balance_caveats: GAAP disclosure + measured coverage windows ----------
+
+test_that("balance_caveats is always present and flags the GAAP distinction", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    r <- cog_balances("550000227544", 2019)
+    cav <- attr(r, "provenance")$balance_caveats
+    expect_false(is.null(cav))
+    expect_true(cav$not_gaap)
+  })
+})
+
+test_that("coverage_window is computed from the corpus, not hardcoded", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    r <- cog_balances("550000227544", c(2011, 2012, 2019, 2020))
+    cav <- attr(r, "provenance")$balance_caveats
+
+    # Read the "general" family's true year extent independently, via a
+    # fresh DuckDB connection against the raw parquet files (never through
+    # balance_long/.balance_caveats() itself, and never via arrow -- this
+    # package reads parquet through DuckDB only, see CLAUDE.md). Replicates
+    # the same predicates 26-balance_long.sql applies (category_type =
+    # 'balance', NOT is_aggregate) so this is a faithful, independent
+    # measurement rather than a re-statement of the view under test.
+    con2 <- DBI::dbConnect(duckdb::duckdb())
+    on.exit(DBI::dbDisconnect(con2, shutdown = TRUE), add = TRUE)
+    long_glob <- file.path(fixture_corpus_path(), "data", "long", "**", "*.parquet")
+    cats_path <- file.path(fixture_corpus_path(), "data", "summary_categories.parquet")
+    obs <- DBI::dbGetQuery(con2, sprintf(
+      "SELECT MIN(l.year) AS y0, MAX(l.year) AS y1
+       FROM read_parquet(%s, hive_partitioning = true) l
+       JOIN read_parquet(%s) c USING (item_code)
+       WHERE c.balance_subtype = 'general' AND NOT l.is_aggregate",
+      uscogdata:::.sql_lit_chr(long_glob), uscogdata:::.sql_lit_chr(cats_path)
+    ))
+
+    expect_identical(as.integer(cav$coverage_window$general),
+                     c(as.integer(obs$y0), as.integer(obs$y1)))
+  })
+})
+
+test_that("coverage_window covers every corpus subtype, not just observed ones", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    # Deliberate contract (provenance-v1.json): the window block is corpus-
+    # scoped so a caller can ask "is there a family I missed?", while
+    # `truncated` is the observed-scoped field. A single-category query must
+    # therefore still report every balance family in the mounted corpus.
+    r <- cog_balances("550000227544", 2019, category = "Fund Balances")
+    expect_identical(unique(r$balance_subtype), "general")
+
+    con2 <- DBI::dbConnect(duckdb::duckdb())
+    on.exit(DBI::dbDisconnect(con2, shutdown = TRUE), add = TRUE)
+    cats_path <- file.path(fixture_corpus_path(), "data", "summary_categories.parquet")
+    all_subtypes <- DBI::dbGetQuery(con2, sprintf(
+      "SELECT DISTINCT balance_subtype FROM read_parquet(%s)
+       WHERE balance_subtype IS NOT NULL",
+      uscogdata:::.sql_lit_chr(cats_path)
+    ))$balance_subtype
+
+    cav <- attr(r, "provenance")$balance_caveats
+    expect_setequal(names(cav$coverage_window), all_subtypes)
+    expect_true(length(all_subtypes) > 1L)
+    # ...while `truncated` stays scoped to what this query actually observed.
+    expect_true(all(cav$truncated %in% unique(r$balance_subtype)))
+  })
+})
+
+test_that("the corpus-constant coverage windows are memoised per session", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    # The windows query has no govid/year predicate: its answer depends only
+    # on which corpus is mounted, so re-running the full balance_long scan on
+    # every call is pure waste (35% of verb runtime on the fixture). Same
+    # memoise-and-invalidate pattern as .uscogdata_env$manifest.
+    expect_null(uscogdata:::.uscogdata_env$balance_coverage_windows)
+    suppressMessages(cog_balances("550000227544", 2019))
+    memo <- uscogdata:::.uscogdata_env$balance_coverage_windows
+    expect_false(is.null(memo))
+    expect_true("general" %in% names(memo))
+
+    uscogdata:::cog_close()
+    expect_null(uscogdata:::.uscogdata_env$balance_coverage_windows)
+  })
+})
+
+test_that("a request past a family's coverage window is flagged", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    # employee_retirement (X21/X30/X47/Z77/Z78) genuinely ends at FY2016 in
+    # the LIVE corpus -- Census moved employee retirement reporting to the
+    # Annual Survey of Public Pensions after that year. This bundled FIXTURE
+    # doesn't carry 2013-2016 at all (only 2011/2012/2019/2020 are present),
+    # so the family's *observed* max here is 2012, not 2016. Either way the
+    # requested span (2012, 2019) reaches past what the family covers in
+    # THIS corpus, which is what makes .balance_caveats() flag it -- the
+    # assertion below is about the fixture's measured window, not the FY2016
+    # live-corpus cutoff.
+    r <- cog_balances("550000227544", c(2012, 2019))
+    cav <- attr(r, "provenance")$balance_caveats
+    expect_true("employee_retirement" %in% cav$truncated)
+  })
+})
+
+test_that("the provenance schema documents balance_caveats", {
+  sch <- jsonlite::fromJSON(
+    system.file("schemas", "provenance-v1.json", package = "uscogdata"),
+    simplifyVector = FALSE
+  )
+  expect_true("balance_caveats" %in% names(sch$properties))
+})
+
+test_that("cog_explain surfaces the balance caveats", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    # Asserted on the RENDERED text, not on prov$balance_caveats: the field
+    # is already covered above, and the once-per-session cli_inform() means
+    # cog_explain() is the only surface a caller who missed (or suppressed)
+    # the first message can still audit.
+    r <- suppressMessages(cog_balances("550000227544", c(2012, 2019)))
+    # Both streams: cli routes most of its output through conditions that
+    # land on stderr, so a stdout-only capture would be empty (the pattern
+    # used throughout test-explain.R).
+    out <- paste(c(capture.output(cog_explain(r)),
+                   capture.output(cog_explain(r), type = "message")),
+                 collapse = "\n")
+    expect_match(out, "GAAP")
+    expect_match(out, "employee_retirement")
+  })
+})
+
+test_that("cog_explain on a money-verb result has no balance caveat section", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    r <- suppressMessages(cog_spending("550000227544", 2019))
+    out <- paste(c(capture.output(cog_explain(r)),
+                   capture.output(cog_explain(r), type = "message")),
+                 collapse = "\n")
+    # Guard against the capture itself being vacuous: the section must be
+    # absent from output that demonstrably contains the rest of the report.
+    expect_match(out, "Data vintage")
+    expect_false(grepl("GAAP", out))
+  })
+})
+
+test_that("the caveat message fires once per session", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    expect_message(cog_balances("550000227544", 2019), "not.*GAAP")
+    expect_no_message(cog_balances("550000227544", 2020))
+  })
+})

tests/testthat/test-categories.R

@@ -8,14 +8,30 @@ test_that("cog_categories returns all categories grouped by subtype", {
   expect_gt(nrow(r), 10L)
   # corpus preserves Census-native "expenditure" vocabulary; the API takes
   # "spending" as a friendlier alias.
-  expect_setequal(unique(r$category_type), c("expenditure", "revenue"))
+  #
+  # `balance` joined as a third category_type with the cash-and-security
+  # holding codes (pipeline#76). `cog_categories()` is a CATALOGUE verb, not a
+  # money verb, so it surfaces every category_type the corpus carries -- the
+  # stock/flow guard belongs on cog_spending()/cog_revenue(), which must never
+  # return a balance row.
+  expect_setequal(unique(r$category_type),
+                  c("expenditure", "revenue", "balance"))
 })
 
 test_that("cog_categories(type = 'spending') returns only expenditure rows", {
   skip_if_no_corpus()
   r <- cog_categories(type = "spending")
   expect_true(all(r$category_type == "expenditure"))
-  expect_true(all(r$subtype %in% c("operations", "capital", "intergovernmental")))
+  # "assistance" (the J-prefix aid/benefit codes) joined the vocabulary with
+  # the crosswalk completion in cog_pipeline#60/#65 -- every flow code
+  # carrying dollars now maps to a category.
+  # `interest` (I89, I91-I94) and `insurance_benefits` (Y05/Y06/Y14/Y53)
+  # joined with the I/Q/Y flow batch -- the last two characters of Census's
+  # expenditure taxonomy. `interest` is what makes the three-concept model
+  # computable: primary = direct minus debt service.
+  expect_true(all(r$subtype %in%
+                  c("operations", "capital", "intergovernmental", "assistance",
+                    "interest", "insurance_benefits")))
 })
 
 test_that("cog_categories surfaces the intergovernmental spending subtype", {
@@ -33,8 +49,14 @@ test_that("cog_categories(type = 'revenue') returns only revenue rows", {
   skip_if_no_corpus()
   r <- cog_categories(type = "revenue")
   expect_true(all(r$category_type == "revenue"))
+  # The four non-general subtypes are deliberately NOT own_source: Census's
+  # General Revenue excludes insurance trust (Y01 alone is $1.31T corpus-wide,
+  # plus the employee-retirement X codes), utility (A91-A94) and liquor store
+  # (A90) revenue by definition, which is what makes both of its published
+  # revenue concepts computable -- see `revenue_concept` in `?cog_revenue`.
   expect_true(all(r$subtype %in%
-                  c("own_source", "federal", "state", "local_aid")))
+                  c("own_source", "federal", "state", "local_aid",
+                    "insurance_trust", "utility", "liquor_store")))
 })
 
 test_that("cog_categories(pattern = ...) filters case-insensitively", {
@@ -71,3 +93,40 @@ test_that("cog_categories sorted by category_type, category, subtype", {
 test_that("cog_categories rejects invalid type", {
   expect_error(cog_categories(type = "both"), "type")
 })
+
+test_that("cog_categories() surfaces balance subtypes", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    cc <- cog_categories()
+    b <- cc[cc$category_type == "balance", ]
+    expect_true(nrow(b) > 0L)
+
+    # Every balance row must carry its subtype. Before the COALESCE included
+    # balance_subtype these were all NA, which silently made the balance
+    # taxonomy undiscoverable -- cog-api derives its subtype vocabulary from
+    # this function, so an NA here becomes an unusable API parameter.
+    expect_false(any(is.na(b$subtype)))
+
+    # The exact set, read independently from the crosswalk rather than from
+    # the function under test.
+    con2 <- DBI::dbConnect(duckdb::duckdb())
+    on.exit(DBI::dbDisconnect(con2, shutdown = TRUE), add = TRUE)
+    p <- file.path(fixture_corpus_path(), "data", "summary_categories.parquet")
+    want <- DBI::dbGetQuery(con2, sprintf(
+      "SELECT DISTINCT balance_subtype FROM read_parquet(%s)
+       WHERE category_type = 'balance' AND balance_subtype IS NOT NULL
+       ORDER BY 1", uscogdata:::.sql_lit_chr(p)))$balance_subtype
+    expect_true(length(want) > 1L)
+    expect_identical(sort(unique(b$subtype)), sort(want))
+  })
+})
+
+test_that('cog_categories(type = "balance") filters to holdings', {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    b <- cog_categories(type = "balance")
+    expect_true(nrow(b) > 0L)
+    expect_identical(unique(b$category_type), "balance")
+    expect_false(any(is.na(b$subtype)))
+  })
+})

tests/testthat/test-complete.R

@@ -0,0 +1,193 @@
+# tests/testthat/test-complete.R
+#
+# uscogdata#18. The published corpus no longer stores the wide era's explicit
+# zeros (cog_pipeline#64, series break SB194), so absence means two different
+# things:
+#
+#   <= FY2011 (dense_source) : cell absent => Census published $0
+#   >= FY2012 (sparse_source): cell absent => not reported, unknown
+#
+# `complete = TRUE` fills the requested grid from `code_set` and stamps every
+# row's `value_source` so the two are distinguishable. Expected row sets here
+# are built from the corpus parquet directly, never from the verb under test --
+# verifying what a filter does through that same filter proves nothing.
+
+# The (subtype, category) cells that SHOULD exist for one government-year:
+# every code in force for that government's type, mapped through
+# summary_categories, matching the verb's crosswalk subtype scope (the
+# default concept, `primary`, is operations/capital/assistance -- see
+# uscogdata#11) and excluding aggregate-flagged codes (which
+# spending_long/revenue_long drop).
+raw_expected_cells <- function(govid, year, subtypes, subtype_col) {
+  fx <- sub("/$", "", Sys.getenv("USCOGDATA_URL"))
+  q <- function(f) sprintf("read_parquet('%s/data/%s')", fx, f)
+  wt_raw_query(sprintf(
+    "SELECT DISTINCT c.%s AS subtype, c.category
+     FROM %s cs
+     JOIN %s x ON x.govs_type = cs.type
+     JOIN %s c ON c.item_code = cs.item_code
+     WHERE x.canonical_govid = '%s'
+       AND cs.year = %d
+       AND NOT cs.is_aggregate
+       AND c.category IS NOT NULL
+       AND c.%s IN (%s)",
+    subtype_col, q("code_set.parquet"), q("canonical_fips_xwalk.parquet"),
+    q("summary_categories.parquet"), govid, year,
+    subtype_col, paste0("'", subtypes, "'", collapse = ",")
+  ))
+}
+
+# The default expenditure concept's subtype scope, mirrored from
+# R/spending.R's .spend_subtypes_primary.
+primary_subtypes <- c("operations", "capital", "assistance")
+
+test_that("complete = FALSE is the default and changes nothing", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    plain <- cog_spending("121011212191", 2011L)
+    explicit <- cog_spending("121011212191", 2011L, complete = FALSE)
+    expect_equal(nrow(plain), nrow(explicit))
+    expect_false("value_source" %in% names(plain))
+  })
+})
+
+test_that("complete = TRUE round-trips a dense-source year to the pre-sparsification cells", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    # FY2011 is dense_source: before sparsification this government carried a
+    # row for every code in force, most of them $0. complete = TRUE must
+    # reproduce that cell set exactly.
+    r <- cog_spending("121011212191", 2011L, complete = TRUE)
+    expected <- raw_expected_cells("121011212191", 2011L,
+                                   primary_subtypes, "spend_subtype")
+
+    key <- function(sub, cat) paste(sub, cat, sep = "|")
+    expect_setequal(key(r$spend_subtype, r$category),
+                    key(expected$subtype, expected$category))
+    expect_gt(nrow(expected), 0L)
+
+    # Every filled cell in a dense-source year is a Census-published $0 --
+    # never "unknown", which is what the modern era's absences mean.
+    expect_setequal(unique(r$value_source), c("reported", "census_zero"))
+    expect_true(all(r$amt_nominal[r$value_source == "census_zero"] == 0))
+    expect_true(all(r$amt_nominal[r$value_source == "reported"] != 0))
+  })
+})
+
+test_that("complete = TRUE preserves the reported rows and their amounts exactly", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    plain <- cog_spending("121011212191", 2011L)
+    full  <- cog_spending("121011212191", 2011L, complete = TRUE)
+
+    # Filling adds rows; it must never alter or drop one.
+    expect_gt(nrow(full), nrow(plain))
+    reported <- full[full$value_source == "reported", ]
+    expect_equal(nrow(reported), nrow(plain))
+    expect_equal(sum(reported$amt_nominal), sum(plain$amt_nominal))
+    # ... and the total is unchanged, because every added cell is $0.
+    expect_equal(sum(full$amt_nominal, na.rm = TRUE), sum(plain$amt_nominal))
+  })
+})
+
+test_that("a sparse-source year's absences are unknown, not zero", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    # FY2019 is sparse_source: an absent cell means the government did not
+    # report, which is NOT a zero. Filling those with 0 would invent data --
+    # the exact error the representation contract exists to prevent.
+    r <- cog_spending("121011212191", 2019L, complete = TRUE)
+    filled <- r[r$value_source != "reported", ]
+    expect_gt(nrow(filled), 0L)
+    expect_true(all(filled$value_source == "not_reported"))
+    expect_true(all(is.na(filled$amt_nominal)))
+    expect_false(any(r$value_source == "census_zero"))
+  })
+})
+
+test_that("the fill is scoped to each government's own type", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    # Filling against the union of all types would invent cells for codes a
+    # county can never report. Every filled category must be one that
+    # code_set puts in force for type 1 (county) specifically.
+    r <- cog_spending("121011212191", 2011L, complete = TRUE)
+    county_cells <- raw_expected_cells("121011212191", 2011L,
+                                       primary_subtypes, "spend_subtype")
+    expect_true(all(r$category %in% county_cells$category))
+  })
+})
+
+test_that("complete = TRUE respects the category filter", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    r <- cog_spending("121011212191", 2011L, category = "Police",
+                      complete = TRUE)
+    expect_true(all(r$category == "Police"))
+    expect_true("value_source" %in% names(r))
+  })
+})
+
+test_that("cog_revenue() completes on its own flow", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    r <- cog_revenue("121011212191", 2011L, complete = TRUE)
+    expected <- raw_expected_cells("121011212191", 2011L,
+                                   c("own_source", "federal", "state", "local_aid"),
+                                   "revenue_subtype")
+    key <- function(sub, cat) paste(sub, cat, sep = "|")
+    expect_setequal(key(r$revenue_subtype, r$category),
+                    key(expected$subtype, expected$category))
+    expect_setequal(unique(r$value_source), c("reported", "census_zero"))
+  })
+})
+
+test_that("provenance records the completion and its absence rule", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    prov <- attr(cog_spending("121011212191", 2011L, complete = TRUE),
+                 "provenance")
+    expect_true(prov$completion$applied)
+    expect_equal(prov$completion$absence_means$`2011`, "census_zero")
+    expect_gt(prov$completion$rows_filled, 0L)
+
+    off <- attr(cog_spending("121011212191", 2011L), "provenance")
+    expect_false(off$completion$applied)
+    expect_equal(off$completion$rows_filled, 0L)
+  })
+})
+
+test_that("complete = TRUE is refused where the fill would be guesswork", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    # A recipe defines its own component codes and does not go through
+    # summary_categories at all, so there is no grid to fill from.
+    expect_error(
+      cog_spending("121011212191", 2011L, recipe = "corrections_combined",
+                   complete = TRUE),
+      class = "uscogdata_complete_unsupported"
+    )
+    # The intergovernmental leg keeps aggregate rows by design
+    # (inst/sql/24-ig_long.sql), so its grid is not code_set's grid.
+    expect_error(
+      cog_spending("121011212191", 2011L, expenditure_concept = "total",
+                   complete = TRUE),
+      class = "uscogdata_complete_unsupported"
+    )
+  })
+})
+
+test_that("complete = TRUE aborts on a corpus with no representation contract", {
+  skip_if_no_corpus()
+  # A corpus published before sparsification carries neither table, so there
+  # is nothing to fill from and no rule saying what an absence means. That
+  # must abort rather than guess.
+  with_corpus_missing_representation({
+    expect_error(
+      cog_spending("121011212191", 2011L, complete = TRUE),
+      class = "uscogdata_representation_unavailable"
+    )
+    # ... while an ordinary query on the same corpus still works.
+    expect_gt(nrow(cog_spending("121011212191", 2011L)), 0L)
+  })
+})

tests/testthat/test-corpus-breaks.R

@@ -0,0 +1,94 @@
+# tests/testthat/test-corpus-breaks.R
+#
+# uscogdata#19. Four catalogued series breaks carry fin_code = "ALL" -- they
+# are caveats about the corpus itself rather than about one item code:
+#
+#   SB085  1977  dollar precision across the 1976/1977 boundary
+#   SB087  2002  imputation exclusion FY2002-2006
+#   SB194  2012  dense -> sparse representation change
+#   SB086  2017  government ID scheme change
+#
+# .build_series_break_refs() matches `fin_code IN (<codes in the result>)`,
+# and no row's item_code is ever the literal "ALL", so none of them could
+# ever reach a user. They now travel in their own provenance field,
+# `corpus_break_refs`, which keeps them distinguishable from the
+# code-specific `series_break_refs` (an ALL caveat qualifies the whole
+# result, not one series).
+
+test_that("corpus_break_refs surfaces an ALL-scoped break the year range spans", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    # SB194 sits at FY2012 -- the dense/sparse boundary. A query spanning
+    # 2011 -> 2012 straddles it, and this is the case cog_pipeline#64's
+    # DoD 4 intended to reach users.
+    r <- cog_spending("121011212191", 2011:2012, "Police")
+    prov <- attr(r, "provenance")
+    expect_true("SB194" %in% prov$corpus_break_refs)
+  })
+})
+
+test_that("corpus_break_refs stays empty when no ALL break falls in the range", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    # 2019-2020 spans no catalogued corpus-wide break.
+    r <- cog_spending("121011212191", 2019:2020, "Police")
+    expect_equal(attr(r, "provenance")$corpus_break_refs, character(0))
+  })
+})
+
+test_that("corpus_break_refs and series_break_refs stay disjoint", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    r <- cog_spending("121011212191", 2011:2012, "Police")
+    prov <- attr(r, "provenance")
+    expect_type(prov$series_break_refs, "character")
+    expect_type(prov$corpus_break_refs, "character")
+    # An ALL caveat must never masquerade as a break in a specific series.
+    expect_length(intersect(prov$series_break_refs, prov$corpus_break_refs), 0L)
+    expect_false("SB194" %in% prov$series_break_refs)
+  })
+})
+
+test_that(".build_corpus_break_refs matches on the break_year window alone", {
+  skip_if_no_corpus()
+  con <- cog_open()
+  on.exit(cog_close())
+
+  # SB085's boundary is 1976/1977, outside the fixture's partitions -- the
+  # series_breaks table is a full cross-vintage registry, so the matching
+  # logic is testable there even though no long partition covers it.
+  expect_true("SB085" %in% uscogdata:::.build_corpus_break_refs(
+    con, years = 1975:1980, schema_version = 6L
+  ))
+  # ... and does not fire for a range that misses it, unlike a filter keyed
+  # on the era rather than the boundary.
+  expect_false("SB085" %in% uscogdata:::.build_corpus_break_refs(
+    con, years = 1978:1980, schema_version = 6L
+  ))
+
+  # Unlike code-specific refs, these do not depend on which codes a result
+  # happens to contain -- that dependency is the whole defect.
+  expect_setequal(
+    uscogdata:::.build_corpus_break_refs(con, years = 2001:2003, schema_version = 6L),
+    "SB087"
+  )
+
+  # Gated on schema_version >= 5: series_breaks_pq is not registered below it.
+  expect_equal(
+    uscogdata:::.build_corpus_break_refs(con, years = 2011:2012, schema_version = 4L),
+    character(0)
+  )
+})
+
+test_that("cog_explain() prints corpus-wide caveats under their own heading", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    r <- cog_spending("121011212191", 2011:2012, "Police")
+    out <- paste(c(
+      capture.output(cog_explain(r)),
+      capture.output(cog_explain(r), type = "message")
+    ), collapse = "\n")
+    expect_match(out, "Corpus-wide caveats", fixed = TRUE)
+    expect_match(out, "SB194", fixed = TRUE)
+  })
+})

tests/testthat/test-coverage-disclosure.R

@@ -0,0 +1,103 @@
+# Madison walkthrough audit -- findings F-020 and F-023. Tracked as uscogdata#13.
+# See docs/walkthroughs/FINDINGS.md in cog_explorer.
+#
+# The owner's settled design (2026-07-28): a `coverage` argument on
+# cog_geographic_rollup(), cog_find_peers()/cog_peer_compare() and their
+# cog-api equivalents --
+#   "all"        every unit that reported that year (today's behaviour, DEFAULT)
+#   "census"     census years only (years ending 2 or 7)
+#   "consistent" only units reporting in every requested year (balanced panel)
+# -- PLUS always-on coverage metadata on every result regardless of mode:
+# n_units_reporting, n_units_expected, is_census_year.
+#
+# Motivating principle: using these verbs correctly must not require the user to
+# know that the Census of Governments is a complete census only in years ending
+# in 2 and 7.
+#
+# The helper below accepts that metadata either as columns on the returned
+# tibble or as a per-year table in provenance$coverage -- the design fixes the
+# three field names and that they reach the caller, not the container.
+
+wt_coverage <- function(x) {
+  prov <- attr(x, "provenance")
+  cov <- prov$coverage
+  if (is.null(cov)) {
+    needed <- c("year", "n_units_reporting", "n_units_expected", "is_census_year")
+    expect_true(all(needed %in% names(x)))
+    cov <- unique(x[, needed])
+  }
+  cov[order(cov$year), ]
+}
+
+test_that("multi-government aggregates disclose reporting coverage on every result", {
+
+  # -- F-020: geographic rollups -------------------------------------------
+  # Wisconsin's city/village universe is 608 governments. On the bundled
+  # fixture, FY2012 (a census year) has 597 of them reporting while FY2019 and
+  # FY2020 (sample years) have 112 and 114 -- an 18%-98% swing that today's
+  # return value says nothing about. Counts cross-checked against the raw
+  # corpus, not through cog_geographic_rollup(), which is under test.
+  wi <- cog_gov_search(name = NULL, state = "WI", type = "city")
+  expect_equal(nrow(wi), 608L)
+
+  roll <- cog_geographic_rollup(govids = list(city = wi$canonical_govid),
+                                category = NULL, years = c(2011L, 2012L, 2019L, 2020L))
+  cov <- wt_coverage(roll)
+
+  expect_equal(cov$n_units_expected, rep(608L, 4L))
+  expect_equal(cov$n_units_reporting, c(152L, 597L, 112L, 114L))
+  expect_equal(cov$is_census_year, c(FALSE, TRUE, FALSE, FALSE))
+
+  # Cross-check against the raw partitions, scoped to the SAME universe the
+  # rollup was given -- the 608 govids above. Scoping instead on the long
+  # table's own `type`/`fips_state` asks a different question and answers 595:
+  # VERNON VILLAGE and WAUKESHA VILLAGE carry type = 3 there (their as-of-year
+  # identity, when they were townships) while the xwalk lists them as
+  # govs_type = 2 (their present identity, as villages). Schema v6 made the
+  # long table's geography present-harmonized and moved as-of-year to the
+  # *_asof columns, but `type` still reads as-of-year -- see .validate_schema()
+  # in R/manifest.R. n_units_reporting counts against the requested universe,
+  # so 597 is the number that answers "how many of the governments I asked
+  # about reported".
+  raw_2012 <- wt_raw_query(paste0(
+    "SELECT COUNT(DISTINCT canonical_govid) n FROM read_parquet('", wt_corpus_glob(), "') ",
+    "WHERE year = 2012 AND LEFT(item_code, 1) IN ('E','F','G') AND NOT is_aggregate ",
+    "AND canonical_govid IN (",
+    paste0("'", wi$canonical_govid, "'", collapse = ","), ")"))
+  expect_equal(cov$n_units_reporting[cov$year == 2012], as.integer(raw_2012$n[[1]]))
+
+  # -- F-023: peer cohorts --------------------------------------------------
+  # CHILTON CITY, WI (ACS population 4,017): a 15-peer cohort fixed at FY2012
+  # reports 15 of 15 in FY2012 and only 3 of 15 in FY2019 and FY2020. Nothing
+  # in cog_peer_compare()'s return distinguishes those years today.
+  chilton <- "552015177095"
+  peers <- cog_find_peers(chilton, year = 2012L, max_peers = 15L)
+  expect_equal(nrow(peers), 15L)
+
+  cmp <- cog_peer_compare(target_govid = chilton, peers = peers, category = NULL,
+                          years = c(2012L, 2019L, 2020L), per_capita = TRUE)
+  cov_peers <- wt_coverage(cmp)
+  expect_equal(cov_peers$n_units_expected, rep(15L, 3L))
+  expect_equal(cov_peers$n_units_reporting, c(15L, 3L, 3L))
+  expect_equal(cov_peers$is_census_year, c(TRUE, FALSE, FALSE))
+
+  # -- the three coverage modes --------------------------------------------
+  expect_equal(attr(cog_peer_compare(target_govid = chilton, peers = peers,
+                                     category = NULL, years = c(2012L, 2019L, 2020L),
+                                     per_capita = TRUE),
+                    "provenance")$coverage_mode, "all")     # unchanged default
+
+  consistent <- cog_peer_compare(target_govid = chilton, peers = peers,
+                                 category = NULL, years = c(2012L, 2019L, 2020L),
+                                 per_capita = TRUE, coverage = "consistent")
+  n_by_year <- tapply(consistent$canonical_govid[consistent$role == "peer"],
+                      consistent$year[consistent$role == "peer"],
+                      function(g) length(unique(g)))
+  expect_equal(unname(as.integer(n_by_year)), c(3L, 3L, 3L))  # balanced panel
+
+  census_only <- cog_geographic_rollup(govids = list(city = wi$canonical_govid),
+                                       category = NULL,
+                                       years = c(2011L, 2012L, 2019L, 2020L),
+                                       coverage = "census")
+  expect_equal(sort(unique(census_only$year)), 2012)
+})

tests/testthat/test-expenditure-concept.R

@@ -13,9 +13,13 @@ test_that("the corpus contains no K-prefix rows, so the Direct leg omits K", {
   }
 })
 
-test_that("expenditure_concept defaults to direct and preserves today's numbers", {
+test_that("expenditure_concept defaults to primary; direct matches it on a pure operations/capital category", {
   gov <- "010000226085"                      # Alabama state government
   base <- cog_spending(gov, years = 2019, category = "Police")
+  expect_equal(attr(base, "provenance")$expenditure_concept, "primary")
+  # Police maps only to operations/capital codes (E62/F62/G62), so the
+  # direct concept's extra subtypes (interest, insurance_benefits) cannot
+  # contribute and the two concepts must agree exactly here.
   expl <- cog_spending(gov, years = 2019, category = "Police",
                        expenditure_concept = "direct")
   expect_equal(base$amt_nominal, expl$amt_nominal)
@@ -59,7 +63,9 @@ test_that("the IG leg never includes the L-- family total", {
   codes <- DBI::dbGetQuery(con,
     "SELECT DISTINCT item_code FROM ig_long")$item_code
   expect_false(any(grepl("--$", codes)))
-  expect_true(all(substr(codes, 1, 1) %in% c("M", "L")))
+  # Q joined the IG family with the crosswalk-membership rewrite
+  # (uscogdata#11 / F-017: Q11/Q12/Q18 are state payments to school systems).
+  expect_true(all(substr(codes, 1, 1) %in% c("M", "L", "Q")))
 })
 
 test_that("expenditure_concept rejects unknown values", {
@@ -246,9 +252,12 @@ test_that("both cross-government verbs still accept the direct default", {
 })
 
 test_that("provenance always records the expenditure concept", {
-  d <- cog_spending("010000226085", years = 2019, category = "Police")
+  p <- cog_spending("010000226085", years = 2019, category = "Police")
+  d <- cog_spending("010000226085", years = 2019, category = "Police",
+                    expenditure_concept = "direct")
   t <- cog_spending("010000226085", years = 2019, category = "Police",
                     expenditure_concept = "total")
+  expect_equal(attr(p, "provenance")$expenditure_concept, "primary")
   expect_equal(attr(d, "provenance")$expenditure_concept, "direct")
   expect_equal(attr(t, "provenance")$expenditure_concept, "total")
   # The note explains the non-obvious part: how legacy IG was assembled.
@@ -298,8 +307,16 @@ test_that("a mis-scoped cog_spending() call never attaches an M/L counterpart to
   # (M47/M94, same suffixes) -- a coincidence of reused digits, not a real
   # Direct/Total pairing. The flow-family gate in
   # .attach_ig_counterparts() must keep ig_recipe_id NULL here.
+  #
+  # Anchored on FL state government, not AL. Coverage is presence-based: a
+  # recipe is only suggested when its component codes have rows for the
+  # requested government-year. AL state's only FY2011 B47 cell was an
+  # explicit zero, which the corpus no longer stores after sparsification
+  # (SB194, cog_pipeline#64), so the recipe stopped being a candidate there.
+  # FL state carries a real FY2011 B47 amount, so this exercises the guard
+  # against a suggestion that genuinely fires.
   r <- suppressMessages(
-    cog_spending("010000226085", years = c(2005, 2011), category = "IG Federal")
+    cog_spending("120000226351", years = c(2005, 2011), category = "IG Federal")
   )
   sugg <- attr(r, "provenance")$suggestions
   expect_gt(length(sugg), 0L)

tests/testthat/test-expenditure-concepts.R

@@ -0,0 +1,112 @@
+# Madison walkthrough audit -- findings F-012, F-017, F-018.
+# Tracked as uscogdata#11. See docs/walkthroughs/FINDINGS.md in cog_explorer.
+#
+# The owner's settled three-concept model (2026-07-28):
+#   total   = primary + interest + intergovernmental transfers
+#   direct  = primary + interest          (Census's published Direct Expenditure)
+#   primary = direct minus debt service   (the NEW DEFAULT)
+# implemented by reclassifying on the crosswalk's `spend_type` column, NOT on
+# item-code first letters -- F-018 shows prefix `Y` carries both revenue
+# (Y01/Y02) and expenditure (Y05/Y06) codes, so no first-letter allowlist can
+# route them correctly.
+#
+# Fixture reproducibility: the finding's headline reconciliation is Madison
+# FY2022, where the corpus carries I89 = 46,609 (thousands) and Census's
+# published Direct Expenditure is $654,893,000 against cog_spending()'s
+# $608,284,000 (-7.1%). FY2022 is outside the bundled fixture's year window
+# (2011/2012/2019/2020), so the same invariant is asserted on FY2020, where the
+# fixture carries I89 = 27,704. Anyone running against the full corpus should
+# also check the FY2022 numbers above.
+
+test_that("expenditure concepts classify on spend_type, not item-code prefix", {
+  mad      <- "552025209777"   # MADISON CITY, WI
+  wi_state <- "550000227544"   # WISCONSIN (state government)
+
+  # -- F-012: `primary` is the new default, and equals today's E/F/G figure ---
+  primary <- cog_spending(govid = mad, years = 2020L)
+  expect_equal(attr(primary, "provenance")$expenditure_concept, "primary")
+  expect_equal(sum(primary$amt_nominal), 623347000)
+
+  # -- F-012: `direct` adds interest on long-term debt ------------------------
+  # Expected interest read from the RAW corpus, never through cog_spending(),
+  # which is the filter under test.
+  interest <- wt_raw_amt(mad, 2020L, prefixes = "I")
+  expect_equal(interest, 27704)                       # I89, in $1,000s
+
+  direct <- cog_spending(govid = mad, years = 2020L, expenditure_concept = "direct")
+  expect_equal(sum(direct$amt_nominal), 651051000)    # 623,347 + 27,704 thousands
+  expect_equal(sum(direct$amt_nominal) - sum(primary$amt_nominal), interest * 1000)
+  expect_true("I89" %in% wt_codes_included(direct))
+
+  # -- F-017: `total` carries Q12/Q18, state IG transfers to school districts --
+  # Wisconsin FY2019: Q12 = 6,431,530 and Q18 = 533,391 (thousands). Today
+  # neither verb's flow_prefixes contains "Q", so both are dropped from the one
+  # concept that is supposed to include intergovernmental transfers.
+  ig_expected <- wt_raw_amt(wi_state, 2019L, prefixes = c("M", "L", "Q"))
+  expect_equal(ig_expected, 11609814)                 # M 4,644,893 + Q 6,964,921
+
+  wi_direct <- cog_spending(govid = wi_state, years = 2019L,
+                            expenditure_concept = "direct")
+  wi_total  <- cog_spending(govid = wi_state, years = 2019L,
+                            expenditure_concept = "total")
+
+  # total - direct is exactly the intergovernmental component. Asserted as a
+  # delta rather than a grand total so this stays correct however the J and Y
+  # families land inside `primary`.
+  expect_equal(sum(wi_total$amt_nominal) - sum(wi_direct$amt_nominal),
+               ig_expected * 1000)
+  expect_true(all(c("Q12", "Q18") %in% wt_codes_included(wi_total)))
+
+  # -- F-018: prefix Y splits revenue from expenditure, by spend_type ---------
+  # Y01/Y02 are Insurance Trust revenue; Y05/Y06 are Insurance Trust benefit
+  # payments. All four share the first letter `Y`, so no first-letter allowlist
+  # can route them. The proof that classification is crosswalk-keyed:
+  # Y05 lands in `total` spending (insurance_benefits is inside `direct`),
+  # while Y01 -- same prefix -- is classified `revenue` by the crosswalk and
+  # therefore can never appear in a spending result.
+  #
+  # Per the owner's 2026-07-30 ruling (#11 DoD item 4 vs #12), cog_revenue()'s
+  # DEFAULT stays Census General Revenue and so excludes insurance-trust
+  # revenue; Y01's revenue-side classification is asserted against the
+  # crosswalk itself, not the default call. Surfacing Y01 through an explicit
+  # revenue concept argument is uscogdata#12.
+  wi_revenue <- cog_revenue(govid = wi_state, years = 2019L)
+  spend_codes <- wt_codes_included(wi_total)
+  rev_codes   <- wt_codes_included(wi_revenue)
+
+  expect_true("Y05" %in% spend_codes)
+  expect_false("Y05" %in% rev_codes)
+  expect_false("Y01" %in% spend_codes)
+  expect_false("Y01" %in% rev_codes)   # default = general revenue (#12 ruling)
+
+  con <- uscogdata:::.ensure_session()
+  y_class <- DBI::dbGetQuery(con,
+    "SELECT item_code, category_type, spend_subtype, revenue_subtype
+     FROM summary_categories WHERE item_code IN ('Y01', 'Y05')")
+  expect_equal(y_class$category_type[y_class$item_code == "Y01"], "revenue")
+  expect_equal(y_class$revenue_subtype[y_class$item_code == "Y01"], "insurance_trust")
+  expect_equal(y_class$category_type[y_class$item_code == "Y05"], "expenditure")
+  expect_equal(y_class$spend_subtype[y_class$item_code == "Y05"], "insurance_benefits")
+})
+
+test_that("no balance code or category ever reaches a spending or revenue result (uscogdata#25)", {
+  # Stocks are not flows. The crosswalk's balance codes (W/X/Y/Z fund
+  # balances) share first letters with flow codes, so this could never be
+  # guaranteed under prefix classification; under crosswalk membership it
+  # falls out structurally -- asserted here at the verb level, on a
+  # government-year the fixture gives real balance rows (Wisconsin carries
+  # Y07/Y08/Y21/Y61-type balances in FY2019).
+  wi_state <- "550000227544"
+  con <- uscogdata:::.ensure_session()
+  balance <- DBI::dbGetQuery(con,
+    "SELECT item_code, category FROM summary_categories WHERE category_type = 'balance'")
+  expect_gt(nrow(balance), 0L)
+
+  spend <- cog_spending(wi_state, 2019L, expenditure_concept = "total")
+  rev   <- cog_revenue(wi_state, 2019L)
+
+  expect_false(any(spend$category %in% balance$category))
+  expect_false(any(rev$category %in% balance$category))
+  expect_length(intersect(wt_codes_included(spend), balance$item_code), 0L)
+  expect_length(intersect(wt_codes_included(rev), balance$item_code), 0L)
+})

tests/testthat/test-explain.R

@@ -83,7 +83,7 @@ test_that("cog_explain prints the expenditure concept (I1)", {
     capture.output(cog_explain(t)),
     capture.output(cog_explain(t), type = "message")
   ), collapse = "\n")
-  expect_true(grepl("Concept: direct", txt_d))
+  expect_true(grepl("Concept: primary", txt_d))
   expect_true(grepl("Concept: total", txt_t))
 })
 

tests/testthat/test-fixture-vintage.R

@@ -0,0 +1,112 @@
+# tests/testthat/test-fixture-vintage.R
+#
+# The bundled fixture is a slice of a real cog_pipeline publish tree, and
+# every test in this package -- plus the whole cog-api suite -- runs against
+# it. When the published corpus changes shape and the fixture does not, both
+# suites stay green against a corpus that no longer exists (uscogdata#18).
+#
+# These tests pin the structural facts that distinguish the current published
+# vintage from its predecessor, so a stale fixture fails loudly instead of
+# passing quietly. They assert shape, never dollar values: re-running
+# data-raw/regenerate_fixture_corpus.R against a newer publish tree should
+# keep them green.
+
+# Open a bare DuckDB connection on the fixture's parquet files. Deliberately
+# not the package session: these assertions are about what the fixture
+# CONTAINS, and routing them through the reader's own views would let a
+# filter hide the very absence being checked.
+fixture_query <- function(sql, ...) {
+  con <- DBI::dbConnect(duckdb::duckdb())
+  on.exit(DBI::dbDisconnect(con, shutdown = TRUE), add = TRUE)
+  path <- function(rel) {
+    sprintf("read_parquet(%s)",
+            DBI::dbQuoteString(con, file.path(fixture_corpus_path(), rel)))
+  }
+  DBI::dbGetQuery(con, do.call(sprintf, c(list(sql), lapply(c(...), path))))
+}
+
+test_that("fixture ships every metadata table the publish tree does", {
+  skip_if_no_corpus()
+  # representation/code_set are what make a sparse corpus interpretable; a
+  # fixture without them predates sparsification (cog_pipeline#64).
+  expected <- c(
+    "canonical_alias.parquet", "canonical_fips_xwalk.parquet",
+    "census_collection_coverage.parquet", "code_set.parquet",
+    "harmonization_map.parquet", "harmonization_recipes.parquet",
+    "lineage_events.parquet", "representation.parquet",
+    "series_breaks.parquet", "summary_categories.parquet"
+  )
+  on_disk <- basename(list.files(
+    file.path(fixture_corpus_path(), "data"), pattern = "\\.parquet$"
+  ))
+  expect_true(all(expected %in% on_disk))
+
+  # The manifest must list them too -- consumers read the manifest, not ls().
+  in_manifest <- with_fixture_corpus(
+    basename(vapply(cog_manifest()$files$metadata, function(f) f$path, character(1)))
+  )
+  expect_true(all(expected %in% in_manifest))
+})
+
+test_that("fixture carries the dense/sparse representation contract", {
+  skip_if_no_corpus()
+  rep <- fixture_query(
+    "SELECT year, representation, absence_means FROM %s
+     WHERE year IN (2011, 2012, 2019, 2020) ORDER BY year",
+    "data/representation.parquet"
+  )
+  expect_equal(nrow(rep), 4L)
+  expect_equal(rep$representation, c("dense_source", rep("sparse_source", 3L)))
+  expect_equal(rep$absence_means, c("census_zero", rep("not_reported", 3L)))
+})
+
+test_that("the fixture's wide era is sparse, not zero-padded", {
+  skip_if_no_corpus()
+  # FY2011 is a dense_source year: the corpus publishes only the cells Census
+  # reported non-zero, and an absent cell means Census published $0. Before
+  # sparsification this partition was 2,864,212 rows, ~83% of them explicit
+  # zeros. A single explicit zero here means the fixture predates the change.
+  zeros_2011 <- fixture_query(
+    "SELECT COUNT(*) AS n FROM %s WHERE amt = 0",
+    "data/long/year=2011/part-0.parquet"
+  )$n
+  expect_equal(zeros_2011, 0L)
+
+  # The modern era is a different regime: a reported zero there is real data
+  # (the government filed $0), so zeros legitimately survive and must not be
+  # asserted away.
+  expect_gt(
+    fixture_query("SELECT COUNT(*) AS n FROM %s", "data/long/year=2012/part-0.parquet")$n,
+    0L
+  )
+})
+
+test_that("code_set covers every fixture year with the reader-spec columns", {
+  skip_if_no_corpus()
+  cs <- fixture_query(
+    "SELECT * FROM %s WHERE year IN (2011, 2012, 2019, 2020)",
+    "data/code_set.parquet"
+  )
+  expect_true(all(
+    c("code_set_id", "year", "type", "item_code", "is_aggregate", "n_units")
+    %in% names(cs)
+  ))
+  expect_setequal(unique(cs$year), c(2011L, 2012L, 2019L, 2020L))
+})
+
+test_that("every flow code carrying dollars has a category, J-prefix included", {
+  skip_if_no_corpus()
+  # The J (assistance/benefit) codes were uncategorised until the crosswalk
+  # completion shipped (cog_pipeline#60/#65, J19 held back until #64's
+  # duplication fix landed). Their absence is how a pre-crosswalk fixture
+  # gives itself away.
+  j <- fixture_query(
+    "SELECT item_code, category, category_type, spend_subtype FROM %s
+     WHERE LEFT(item_code, 1) = 'J' ORDER BY item_code",
+    "data/summary_categories.parquet"
+  )
+  expect_true("J19" %in% j$item_code)
+  expect_true(all(j$category_type == "expenditure"))
+  expect_true(all(j$spend_subtype == "assistance"))
+  expect_false(any(is.na(j$category)))
+})

tests/testthat/test-gov-search-literal-match.R

@@ -0,0 +1,57 @@
+# Madison walkthrough audit -- finding F-025. Tracked as uscogdata#16.
+# See docs/walkthroughs/FINDINGS.md in cog_explorer.
+#
+# cog_gov_search()'s UTILITY mode interpolates `name` into
+#   regexp_matches(gov_name, <name>, 'i')
+# unescaped (R/search.R:102), while BASKET mode in the same file already routes
+# it through .escape_regex() (R/search.R:307) with the comment "so `name` is
+# treated as a literal substring". Two failure modes result:
+#   correctness -- a real government cannot be found by its own exact name, and
+#                  a single "." matches everything (HTTP 200 both ways via the API);
+#   robustness  -- malformed regex reaches the engine and errors, which cog-api
+#                  surfaces as a 500, reachable by typing a real name one
+#                  character at a time.
+#
+# NOT asserted here: the finding's `q=St. Louis` example. Under correct literal
+# matching that search still returns 0 rows, because the stored name is
+# "ST LOUIS CITY" with no period -- it demonstrates today's over-matching
+# semantics, not a row the fix makes findable.
+
+test_that("cog_gov_search() matches name literally, not as an unescaped regex", {
+
+  # -- correctness (1): a government must be findable by its own exact name ---
+  # FREDONIA (BRISCOE) CITY is real; today the parentheses are read as regex
+  # grouping, so its own complete name matches nothing.
+  fredonia <- cog_gov_search(name = "FREDONIA (BRISCOE) CITY")
+  expect_equal(nrow(fredonia), 1L)
+  expect_equal(fredonia$canonical_govid, "052117184386")
+  expect_equal(cog_gov_search(name = "FREDONIA (BRISCOE)")$canonical_govid,
+               "052117184386")
+
+  # -- correctness (2): a metacharacter must not become a wildcard ------------
+  # No Wisconsin city or village name contains a literal period -- established
+  # against the raw registry below, NOT through the verb under test. A literal
+  # search for "." must therefore return nothing; today it returns all 608.
+  con <- DBI::dbConnect(duckdb::duckdb())
+  on.exit(DBI::dbDisconnect(con, shutdown = TRUE), add = TRUE)
+  xwalk <- paste0(sub("/$", "", Sys.getenv("USCOGDATA_URL")),
+                  "/data/canonical_fips_xwalk.parquet")
+  with_dot <- DBI::dbGetQuery(con, paste0(
+    "SELECT COUNT(*) n FROM read_parquet('", xwalk, "') ",
+    "WHERE fips_state = '55' AND govs_type = 2 AND gov_name LIKE '%.%'"))
+  expect_equal(as.integer(with_dot$n[[1]]), 0L)
+
+  expect_equal(nrow(cog_gov_search(name = ".", state = "WI", type = "city")), 0L)
+  expect_equal(nrow(cog_gov_search(name = "M.dison", state = "WI", type = "city")), 0L)
+  expect_equal(nrow(cog_gov_search(name = "Mad(i|o)son", state = "WI", type = "city")), 0L)
+
+  # A metacharacter-free name still resolves exactly as before.
+  expect_equal(nrow(cog_gov_search(name = "Madison", state = "WI", type = "city")), 1L)
+
+  # -- robustness: malformed pattern text returns no rows, and does not error --
+  # "[" alone, and "Athens-Clarke County (bal" -- an in-progress substring of
+  # ATHENS-CLARKE COUNTY (BALANCE), a real government -- both currently raise
+  # (DuckDB: "Invalid Input Error: missing ]").
+  expect_equal(nrow(cog_gov_search(name = "[")), 0L)
+  expect_equal(nrow(cog_gov_search(name = "Athens-Clarke County (bal")), 0L)
+})

tests/testthat/test-peer-summary-scope.R

@@ -0,0 +1,51 @@
+# Madison walkthrough audit -- finding F-021. Tracked as uscogdata#14.
+# See docs/walkthroughs/FINDINGS.md in cog_explorer.
+#
+# .peer_summary_rows() computes stats::quantile() separately INSIDE each
+# (year, spend_subtype, category) cell. A summary_p50 row is therefore "the
+# median peer's value in that one category", not "the value of the median
+# peer's total". Summing those rows across categories -- the obvious move for a
+# caller who wants one peer-median total line and reads only the column names --
+# misstated a total-spending band by -32.7% to +251.0% across the 24 years the
+# audit tested, with a sign flip at FY2012.
+#
+# The verb is not wrong and its documented use (faceting by role AND category)
+# is unaffected, so the fix is documentation: one sentence in @return.
+
+test_that("cog_peer_compare() documents that summary_* rows are per-category quantiles", {
+
+  # man/ ships only in the source tree (the installed package carries a
+  # compiled help database instead), so the prose assertions below cannot run
+  # under R CMD check -- CI's earlier testthat::test_local() step enforces
+  # them. The numeric pin further down needs only the corpus, but it lives in
+  # the same test_that() as the sentence it protects, deliberately: they are
+  # one claim, and splitting them would let the prose drift while a separate
+  # test kept passing.
+  rd_path <- skip_if_no_source_tree(c("man", "cog_peer_compare.Rd"))
+  rd <- paste(readLines(rd_path, warn = FALSE), collapse = " ")
+
+  # The @return section must say the quantile is computed within each cell...
+  expect_match(rd, "within each|per-category|per category", ignore.case = TRUE)
+  # ...and must warn that the rows are not additive across category.
+  expect_match(rd, "not additive|do(es)? not sum|cannot be summed", ignore.case = TRUE)
+  # ...naming the grouping explicitly.
+  expect_match(rd, "spend_subtype", fixed = TRUE)
+
+  # Pin the mechanism numerically so a future refactor that quietly changes the
+  # quantile grouping fails here rather than silently invalidating the sentence
+  # above. Fixture: Madison, 10 peers found at FY2020, category = NULL.
+  peers <- cog_find_peers("552025209777", year = 2020L, max_peers = 10L)
+  cmp <- cog_peer_compare(target_govid = "552025209777", peers = peers,
+                          category = NULL, years = 2020L, per_capita = TRUE)
+
+  naive <- sum(cmp$amt_per_capita_nominal[cmp$role == "summary_p50"], na.rm = TRUE)
+
+  peer_rows <- cmp[cmp$role == "peer", ]
+  per_gov <- tapply(peer_rows$amt_per_capita_nominal, peer_rows$canonical_govid,
+                    sum, na.rm = TRUE)
+  correct <- unname(stats::quantile(per_gov, 0.5, na.rm = TRUE))
+
+  expect_equal(round(naive), 6180)     # summing the built-in summary rows
+  expect_equal(round(correct), 2043)   # quantile of each peer's OWN total
+  expect_gt(naive / correct, 2)        # a +200% misstatement on this cohort
+})

tests/testthat/test-revenue-concept-insurance-trust.R

@@ -0,0 +1,139 @@
+# Madison walkthrough audit -- finding F-014. Tracked as uscogdata#12.
+# See docs/walkthroughs/FINDINGS.md in cog_explorer.
+#
+# cog_revenue()'s flow_prefixes = c("T","A","U","B","C","D") never returns
+# item-code prefix X (Employee Retirement) or Y (other Insurance Trust). Per
+# Census's standard identity, Total Revenue = General + Utility + Liquor Store +
+# Insurance Trust Revenue, and Employee Retirement System contributions and
+# earnings ARE the Insurance Trust Revenue component -- so prefix X sits inside
+# a published Census revenue concept exactly the way I89 sits inside Census's
+# Direct Expenditure concept (finding F-012).
+#
+# RULED 2026-07-30. `revenue_concept = c("general", "total")` mirrors
+# `expenditure_concept`, and the two values are Census's two published revenue
+# concepts, related by the manual's own identity (section 4.3, which defines
+# the first by SUBTRACTING from the second):
+#
+#   Total Revenue = General + Utility + Liquor Store + Insurance Trust
+#
+# so `general` is the four general subtypes (own_source/federal/state/
+# local_aid) and `total` is every revenue subtype. Naming utility (A91-A94)
+# and liquor store (A90) separately is what makes BOTH computable -- before
+# cog_pipeline#79 they sat in own_source, so the default was really
+# "General + Utility + Liquor", a concept Census does not publish.
+#
+# Fixture reproducibility: Madison's own X-prefix revenue (FY1970-FY1986,
+# $15,098,000 nominal, $0 thereafter) is outside the bundled fixture's year
+# window (2011/2012/2019/2020), so the same invariant is asserted on Wisconsin
+# state government FY2012, where the fixture carries nonzero X01/X02/X05/X08.
+
+test_that("cog_revenue() can return Census Total Revenue including Insurance Trust (prefix X)", {
+  wi_state <- "550000227544"   # WISCONSIN (state government)
+
+  # Revenue-shaped Employee Retirement codes, read from the RAW corpus rather
+  # than through cog_revenue(), which is the filter under test:
+  #   X01/X02 employee contributions, X05 contributions from other governments,
+  #   X08 total earnings on investments.
+  #
+  # X04 and X06 are deliberately NOT in this set, though an earlier draft of
+  # this test included X04. Both are exhibit codes for INTRAgovernmental
+  # transfers (the administering government paying into its own fund), which
+  # X05's own definition excludes by name. Census agrees: its computed "Total
+  # Emp Ret Rev" for this government-year is exactly the four codes below.
+  x_revenue <- wt_raw_amt(wi_state, 2012L, codes = c("X01", "X02", "X05", "X08"))
+  expect_equal(x_revenue, 2283883)   # 615,835 + 245,083 + 560,382 + 862,583
+
+  # The Y-prefix insurance trust revenue (unemployment + workers comp), which
+  # is the other half of the same Census concept.
+  y_revenue <- wt_raw_amt(wi_state, 2012L, codes = c("Y01", "Y11"))
+  expect_equal(y_revenue, 1259785)
+
+  general <- cog_revenue(govid = wi_state, years = 2012L)
+  expect_equal(attr(general, "provenance")$revenue_concept, "general")
+  expect_equal(sum(general$amt_nominal), 31338293000)
+
+  total <- cog_revenue(govid = wi_state, years = 2012L, revenue_concept = "total")
+  expect_equal(attr(total, "provenance")$revenue_concept, "total")
+
+  # total - general is the whole insurance trust leg, X and Y together.
+  # Asserted as a delta as well as a level so this stays correct however the
+  # utility/liquor families land (both are $0 for WI state in FY2012).
+  expect_equal(sum(total$amt_nominal) - sum(general$amt_nominal),
+               (x_revenue + y_revenue) * 1000)
+  expect_equal(sum(total$amt_nominal), 34881961000)
+  expect_true(all(c("X01", "X02", "X05", "X08") %in% wt_codes_included(total)))
+
+  # Sibling codes under the SAME first letter must stay out: X11/X12 are
+  # benefit payments (an expenditure) and X21/X30/X47 are cash and securities
+  # holdings (a balance-sheet stock). This is the F-018 point restated on the
+  # revenue side -- the split comes from the crosswalk, not from the letter X.
+  expect_false(any(c("X11", "X12", "X21", "X30", "X47") %in% wt_codes_included(total)))
+
+  # Every returned row still resolves to a category (cog_pipeline#79 added the
+  # X crosswalk rows; relaxing a prefix filter alone would have produced
+  # category = NA rows).
+  expect_false(any(is.na(total$category)))
+})
+
+test_that("revenue_concept = 'general' is the default and is strict Census General Revenue", {
+  wi_state <- "550000227544"
+  default  <- cog_revenue(govid = wi_state, years = 2012L)
+  explicit <- cog_revenue(govid = wi_state, years = 2012L,
+                          revenue_concept = "general")
+  expect_equal(sum(default$amt_nominal), sum(explicit$amt_nominal))
+
+  # General Revenue excludes utility, liquor store AND insurance trust
+  # revenue. WI state carries $0 of utility/liquor in FY2012, so the level
+  # assertion above cannot see those two -- assert the subtype scope directly.
+  #
+  # A subset, not setequal: `state` means "intergovernmental revenue FROM the
+  # state government" (the C codes), which a STATE government does not receive
+  # from itself, so it is legitimately absent here.
+  expect_true(all(default$revenue_subtype %in%
+                    c("own_source", "federal", "state", "local_aid")))
+  expect_false(any(c("utility", "liquor_store", "insurance_trust") %in%
+                     default$revenue_subtype))
+})
+
+test_that("utility and liquor store revenue are inside `total` and outside `general`", {
+  # A city, where utility revenue is material: this is the case the WI state
+  # baseline structurally cannot exercise. Measured on the fixture, utility +
+  # liquor is 15.9% of what cog_revenue() returned for type-2 governments
+  # before the general/total split, so this is the largest behaviour change
+  # the concept split introduces.
+  con <- uscogdata:::.ensure_session()
+  gov <- DBI::dbGetQuery(con,
+    "SELECT canonical_govid, SUM(amt) amt FROM long
+     WHERE year = 2012 AND type = 2 AND NOT is_aggregate
+       AND item_code IN ('A91','A92','A93','A94')
+     GROUP BY 1 ORDER BY amt DESC LIMIT 1")$canonical_govid
+
+  util_raw <- wt_raw_amt(gov, 2012L, codes = c("A90", "A91", "A92", "A93", "A94"))
+  expect_gt(util_raw, 0)
+
+  general <- cog_revenue(govid = gov, years = 2012L)
+  total   <- cog_revenue(govid = gov, years = 2012L, revenue_concept = "total")
+
+  expect_false(any(c("utility", "liquor_store") %in% general$revenue_subtype))
+  expect_true("utility" %in% total$revenue_subtype)
+  expect_equal(sum(total$amt_nominal) - sum(general$amt_nominal),
+               util_raw * 1000 +
+                 wt_raw_amt(gov, 2012L, codes = c("Y01", "Y11", "X01", "X02",
+                                                   "X05", "X08")) * 1000)
+})
+
+test_that("revenue_concept rejects unknown values and never returns a balance row", {
+  expect_error(
+    cog_revenue("550000227544", years = 2012L, revenue_concept = "gross"),
+    class = "uscogdata_invalid_revenue_concept"
+  )
+
+  # uscogdata#25 restated for the widest revenue concept: stocks are not
+  # flows, and `total` must not quietly admit the X/Y/W/Z balance families.
+  con <- uscogdata:::.ensure_session()
+  balance <- DBI::dbGetQuery(con,
+    "SELECT item_code, category FROM summary_categories WHERE category_type = 'balance'")
+  total <- cog_revenue("550000227544", years = 2012L, revenue_concept = "total")
+  expect_false(any(total$category %in% balance$category))
+  expect_length(intersect(wt_codes_included(total), balance$item_code), 0L)
+})

tests/testthat/test-rollup.R

@@ -80,8 +80,11 @@ test_that("cog_geographic_rollup provenance reports the outer verb", {
 
 test_that("cog_geographic_rollup accepts data.frames per layer", {
   skip_if_no_corpus()
-  fl_state  <- cog_gov_search("^FLORIDA$", type = "state")
-  broward   <- cog_gov_search("^BROWARD COUNTY$", state = "FL", type = "county")
+  # Unanchored: utility mode matches literally now, so "^...$" would be
+  # searched for as characters rather than read as anchors (uscogdata#16).
+  # Both still resolve to exactly one row once scoped by type/state.
+  fl_state  <- cog_gov_search("FLORIDA", type = "state")
+  broward   <- cog_gov_search("BROWARD COUNTY", state = "FL", type = "county")
   r <- cog_geographic_rollup(
     govids = list(state = fl_state, county = broward),
     category = "Police", years = 2020L

tests/testthat/test-spending.R

@@ -98,7 +98,11 @@ test_that("cog_spending rejects invalid inputs", {
 
 test_that("cog_spending accepts a cog_gov_search result directly", {
   skip_if_no_corpus()
-  picks <- cog_gov_search("^BROWARD COUNTY$", state = "FL", type = "county")
+  # Unanchored: utility mode matches `name` as a literal substring now, so
+  # "^...$" would be searched for as those characters rather than read as
+  # anchors (uscogdata#16). Scoped by state and type, the bare name still
+  # resolves to exactly one row.
+  picks <- cog_gov_search("BROWARD COUNTY", state = "FL", type = "county")
   expect_gt(nrow(picks), 0L)
   r <- cog_spending(picks, 2020L, "Corrections")
   expect_equal(unique(r$canonical_govid), "121011212191")
@@ -244,24 +248,42 @@ test_that("basis defaults to 'harmonized' when not passed", {
 test_that("provenance carries basis + harmonization block with na_rows_excluded", {
   skip_if_no_corpus()
   with_fixture_corpus({
-    r <- cog_spending("121011212191", 2011:2012, "Corrections")
+    # FL state government. The harmonization block is scoped by government,
+    # year and flow prefix -- NOT by category -- so a Corrections query still
+    # counts every E/F/G-prefixed row the harmonized basis drops for having
+    # no harmonized_code. The three that apply here are E21/F21/G21
+    # (Education NEC, SB184-186, "discontinued_na", wide-era window ending
+    # FY2011); the other discontinued_na rulings live outside E/F/G.
+    # See docs/phase_r_harmonization_review.md § 1.3/1.4 and cog_pipeline
+    # data/harmonization_map.csv.
+    r <- cog_spending("120000226351", 2011:2012, "Corrections")
     prov <- attr(r, "provenance")
     expect_equal(prov$basis, "harmonized")
     expect_true(prov$harmonization$applied)
-    expect_true(prov$harmonization$na_rows_excluded >= 0L)
-    expect_true(prov$harmonization$na_amount_excluded >= 0)
-    # Data-verified for the v6 fixture (corpus 2026-07-22). The Task 18 map
-    # extension added E/F/G-prefix discontinued_na rulings the earlier pin's
-    # comment predated: E21/F21/G21 (Education NEC local, SB184-186,
-    # "trivial; explicit-NA, full wide-era window"). Broward's 2011 legacy
-    # partition zero-pads exactly those three codes, so this query now
-    # excludes 3 NA-harmonized rows -- all with amt = 0, hence the excluded
-    # AMOUNT stays exactly zero. (The other discontinued_na rulings -- S74,
-    # Z61, X04, X06, the debt-detail family, L24 -- remain outside the
-    # E/F/G/K prefixes.) See docs/phase_r_harmonization_review.md § 1.3/1.4
-    # and cog_pipeline data/harmonization_map.csv E21/F21/G21 rows.
     expect_equal(prov$harmonization$na_rows_excluded, 3L)
-    expect_equal(prov$harmonization$na_amount_excluded, 0)
+    # $2,825,439 thousands of FY2011 E21 + F21 + G21, reported in full USD.
+    # Pinning a non-zero amount is the point: the earlier Broward anchor's
+    # three rows were all explicit zeros, so the AMOUNT accounting was
+    # asserted only against 0 and could not have caught a bug.
+    expect_equal(prov$harmonization$na_amount_excluded, 2825439 * 1000)
+  })
+})
+
+test_that("sparsification removed the wide era's zero-pads from the exclusion count", {
+  skip_if_no_corpus()
+  with_fixture_corpus({
+    # Broward County FY2011 used to carry E21/F21/G21 rows of exactly $0 --
+    # the wide era stored every government x every code, zeros included. The
+    # published corpus no longer does (SB194, cog_pipeline#64), so there is
+    # now nothing for the harmonized basis to exclude. Absence in a
+    # dense_source year means Census published $0; it does not mean the
+    # exclusion machinery stopped working, which the FL state anchor above
+    # proves independently.
+    r <- cog_spending("121011212191", 2011:2012, "Corrections")
+    h <- attr(r, "provenance")$harmonization
+    expect_true(h$applied)
+    expect_equal(h$na_rows_excluded, 0L)
+    expect_equal(h$na_amount_excluded, 0)
   })
 })
 
@@ -303,11 +325,13 @@ test_that("provenance$series_break_refs is a populated-when-applicable character
     r <- cog_spending("121011212191", 2020L, "Corrections")
     refs <- attr(r, "provenance")$series_break_refs
     expect_type(refs, "character")
-    # No catalogued series_breaks_pq row falls inside this fixture's
-    # 2011/2012/2019/2020 window for the codes this query touches (E04/G04)
-    # -- data-verified; the mechanism itself is what's under test here, via
-    # a query-shaped unit test in test-views.R since the fixture has no
-    # positive case to pin against.
+    # No catalogued code-specific series_breaks_pq row falls inside this
+    # fixture's 2011/2012/2019/2020 window for the codes this query touches
+    # (E04/G04) -- data-verified; the mechanism itself is what's under test
+    # here, via a query-shaped unit test in test-views.R since the fixture
+    # has no positive case to pin against. Corpus-wide ("ALL") entries never
+    # appear in this field by construction -- they travel in
+    # corpus_break_refs; see test-corpus-breaks.R.
     expect_equal(refs, character(0))
   })
 })

tests/testthat/test-views.R

@@ -36,8 +36,8 @@ test_that("inst/sql/22- and 23- harmonized views enforce every WHERE predicate (
   # {url} exactly as .register_views() does, and executes them -- plus
   # their 10-long.sql dependency -- against a synthetic hive-partitioned
   # parquet tree written to a temp dir. A regression in any predicate (e.g.
-  # `NOT is_aggregate` dropped, the prefix list changed, the NULL guard
-  # removed) would change which of the rows below survive.
+  # `NOT is_aggregate` dropped, the crosswalk-membership subquery changed,
+  # the NULL guard removed) would change which of the rows below survive.
   #
   # The synthetic parquet is written with DuckDB's own COPY ... TO (FORMAT
   # PARQUET) rather than the arrow package: this package has no arrow
@@ -61,25 +61,45 @@ test_that("inst/sql/22- and 23- harmonized views enforce every WHERE predicate (
         ('spend-B', 'E38', 50,     false, 'E36'), -- collapse-fold: passes every predicate, renamed to E36
         ('spend-C', 'E05', 999999, true,  'E05'), -- excluded ONLY by `NOT is_aggregate`
         ('spend-D', 'E99', 888888, false, NULL),  -- excluded by `harmonized_code IS NOT NULL`
-        -- 'S74' is outside BOTH flow families (E/F/G/K spending and
-        -- T/A/U/B/C/D revenue -- it mirrors the real corpus's own
-        -- non-flow-type codes like S74/Z61), so it can only leak into
-        -- EITHER view via the E/F/G/K or T/A/U/B/C/D prefix filter, never
-        -- both at once -- a prefix drawn from the other view's own family
-        -- (e.g. a real T-code for the spending row) would incorrectly
-        -- leak into the other view's assertion below and not discriminate
-        -- the predicate under test.
-        ('spend-E', 'S74', 777777, false, 'S74'), -- excluded ONLY by the E/F/G/K prefix filter
-        -- Revenue (T/A/U/B/C/D) rows, exercised against revenue_long_harmonized:
+        -- 'S74' and 'Z61' are classified `balance` in the synthetic
+        -- crosswalk below (mirroring the real corpus's own non-flow codes),
+        -- so each is excluded from its view ONLY by the crosswalk-membership
+        -- subquery -- the mechanism that replaced the prefix allowlists
+        -- (uscogdata#11) and keeps balance stocks out of both flows
+        -- (uscogdata#25).
+        ('spend-E', 'S74', 777777, false, 'S74'), -- excluded ONLY by crosswalk membership (balance)
+        -- Revenue rows, exercised against revenue_long_harmonized:
         ('rev-A',   'U11', 200,    false, 'U11'), -- control: passes every predicate as-is
         ('rev-B',   'U10', 25,     false, 'U11'), -- collapse-fold: passes every predicate, renamed to U11
         ('rev-C',   'T29', 555555, true,  'T29'), -- excluded ONLY by `NOT is_aggregate`
         ('rev-D',   'T88', 444444, false, NULL),  -- excluded by `harmonized_code IS NOT NULL`
-        ('rev-E',   'Z61', 333333, false, 'Z61')  -- excluded ONLY by the T/A/U/B/C/D prefix filter
+        ('rev-E',   'Z61', 333333, false, 'Z61')  -- excluded ONLY by crosswalk membership (balance)
       ) AS t(canonical_govid, item_code, amt, is_aggregate, harmonized_code)
     ) TO %s (FORMAT PARQUET)
   ", uscogdata:::.sql_lit_chr(part_path)))
 
+  # The flow views classify by membership in summary_categories, so the
+  # synthetic corpus needs one too. Every flow code above is a member of its
+  # own flow (so is_aggregate / NULL-harmonized exclusions stay the SOLE
+  # excluder for those rows); S74/Z61 are members but classified balance, so
+  # membership itself is what excludes them.
+  DBI::dbExecute(write_con, sprintf("
+    COPY (
+      SELECT * FROM (VALUES
+        ('E36', 'Water Utilities',  'expenditure', 'operations', NULL),
+        ('E38', 'Water Utilities',  'expenditure', 'operations', NULL),
+        ('E05', 'Corrections',      'expenditure', 'operations', NULL),
+        ('E99', 'Other',            'expenditure', 'operations', NULL),
+        ('S74', 'Fund Balances',    'balance',     NULL,         NULL),
+        ('U11', 'Interest Earnings','revenue',     NULL,         'own_source'),
+        ('U10', 'Interest Earnings','revenue',     NULL,         'own_source'),
+        ('T29', 'Other Taxes',      'revenue',     NULL,         'own_source'),
+        ('T88', 'Other Taxes',      'revenue',     NULL,         'own_source'),
+        ('Z61', 'Fund Balances',    'balance',     NULL,         NULL)
+      ) AS t(item_code, category, category_type, spend_subtype, revenue_subtype)
+    ) TO %s (FORMAT PARQUET)
+  ", uscogdata:::.sql_lit_chr(file.path(tmp, "data", "summary_categories.parquet"))))
+
   sql_dir <- system.file("sql", package = "uscogdata")
   .read_view_sql <- function(filename) {
     txt <- paste(readLines(file.path(sql_dir, filename), warn = FALSE), collapse = "\n")
@@ -89,6 +109,7 @@ test_that("inst/sql/22- and 23- harmonized views enforce every WHERE predicate (
   con <- DBI::dbConnect(duckdb::duckdb())
   on.exit(DBI::dbDisconnect(con, shutdown = TRUE), add = TRUE)
   DBI::dbExecute(con, .read_view_sql("10-long.sql"))
+  DBI::dbExecute(con, .read_view_sql("11-summary_categories.sql"))
   DBI::dbExecute(con, .read_view_sql("22-spending_long_harmonized.sql"))
   DBI::dbExecute(con, .read_view_sql("23-revenue_long_harmonized.sql"))
 
@@ -97,8 +118,8 @@ test_that("inst/sql/22- and 23- harmonized views enforce every WHERE predicate (
      GROUP BY item_code ORDER BY item_code"
   )
   # Exactly one surviving row: spend-C (aggregate), spend-D (NULL
-  # harmonized_code), and spend-E (wrong prefix family) must all be gone,
-  # and spend-A + spend-B must be folded together under E36.
+  # harmonized_code), and spend-E (balance, not an expenditure member) must
+  # all be gone, and spend-A + spend-B must be folded together under E36.
   expect_equal(nrow(spend), 1L)
   expect_equal(spend$item_code, "E36")
   expect_equal(spend$amt, 150)
@@ -138,12 +159,27 @@ test_that("inst/sql/24- and 25- IG views retain aggregates, COALESCE NULL harmon
         ('ig-A', 'M04', 100,   false, 'M04'), -- control: passes through as-is
         ('ig-B', 'M38', 50,    false, 'M36'), -- fold control: real SB012 rule, renamed to M36 under harmonized basis
         ('ig-C', 'M47', 99999, true,  NULL),  -- legacy aggregate, NO harmonized_code: must survive BOTH views
-        ('ig-D', 'L--', 55555, false, 'L--'), -- family total: excluded from BOTH views
-        ('ig-E', 'T29', 44444, false, 'T29')  -- wrong prefix (revenue, not M/L): excluded from BOTH views
+        ('ig-D', 'L--', 55555, false, 'L--'), -- family total: deliberately NOT a crosswalk member, excluded from BOTH views
+        ('ig-E', 'T29', 44444, false, 'T29')  -- revenue member, not intergovernmental: excluded from BOTH views
       ) AS t(canonical_govid, item_code, amt, is_aggregate, harmonized_code)
     ) TO %s (FORMAT PARQUET)
   ", uscogdata:::.sql_lit_chr(part_path)))
 
+  # The IG views classify by summary_categories membership
+  # (spend_subtype = 'intergovernmental'). L-- is deliberately absent --
+  # exactly as it is from the real crosswalk -- which is what excludes it.
+  DBI::dbExecute(write_con, sprintf("
+    COPY (
+      SELECT * FROM (VALUES
+        ('M04', 'Corrections', 'expenditure', 'intergovernmental', NULL),
+        ('M38', 'Health',      'expenditure', 'intergovernmental', NULL),
+        ('M36', 'Health',      'expenditure', 'intergovernmental', NULL),
+        ('M47', 'IG Other',    'expenditure', 'intergovernmental', NULL),
+        ('T29', 'Other Taxes', 'revenue',     NULL,                'own_source')
+      ) AS t(item_code, category, category_type, spend_subtype, revenue_subtype)
+    ) TO %s (FORMAT PARQUET)
+  ", uscogdata:::.sql_lit_chr(file.path(tmp, "data", "summary_categories.parquet"))))
+
   sql_dir <- system.file("sql", package = "uscogdata")
   .read_view_sql <- function(filename) {
     txt <- paste(readLines(file.path(sql_dir, filename), warn = FALSE), collapse = "\n")
@@ -153,6 +189,7 @@ test_that("inst/sql/24- and 25- IG views retain aggregates, COALESCE NULL harmon
   con <- DBI::dbConnect(duckdb::duckdb())
   on.exit(DBI::dbDisconnect(con, shutdown = TRUE), add = TRUE)
   DBI::dbExecute(con, .read_view_sql("10-long.sql"))
+  DBI::dbExecute(con, .read_view_sql("11-summary_categories.sql"))
   DBI::dbExecute(con, .read_view_sql("24-ig_long.sql"))
   DBI::dbExecute(con, .read_view_sql("25-ig_long_harmonized.sql"))
 
@@ -160,8 +197,9 @@ test_that("inst/sql/24- and 25- IG views retain aggregates, COALESCE NULL harmon
     "SELECT item_code, SUM(amt) AS amt FROM ig_long
      GROUP BY item_code ORDER BY item_code"
   )
-  # L-- (family total) and T29 (wrong prefix) are gone; the aggregate row
-  # M47 survives -- proof `NOT is_aggregate` is absent from ig_long.
+  # L-- (family total, not a member) and T29 (revenue, not IG) are gone; the
+  # aggregate row M47 survives -- proof `NOT is_aggregate` is absent from
+  # ig_long.
   expect_equal(raw$item_code, c("M04", "M38", "M47"))
   expect_equal(raw$amt, c(100, 50, 99999))
 
@@ -177,11 +215,13 @@ test_that("inst/sql/24- and 25- IG views retain aggregates, COALESCE NULL harmon
 })
 
 test_that(".build_series_break_refs matches fin_code + break_year window", {
-  # No series_breaks_pq row falls inside the bundled fixture's 2011-2020
-  # window (data-verified; see the "series_break_refs" test in
+  # No CODE-SPECIFIC series_breaks_pq row falls inside the bundled fixture's
+  # 2011-2020 window (data-verified; see the "series_break_refs" test in
   # test-spending.R), so this proves the matching logic itself against the
   # live view + a synthetic year window that DOES hit a cataloged break
-  # (SB075, fin_code E62, break_year 2005).
+  # (SB075, fin_code E62, break_year 2005). The corpus-wide entries are a
+  # separate path with its own coverage -- SB194 does sit at 2012, inside
+  # the fixture window; see test-corpus-breaks.R.
   skip_if_no_corpus()
   con <- cog_open()
   on.exit(cog_close())
@@ -320,14 +360,32 @@ test_that(".harmonization_view_files guard is necessary: registration against a
   )
 })
 
-test_that("spending_long filters to E/F/G/K prefixes and excludes aggregates", {
+test_that("spending_long carries exactly the non-IG expenditure crosswalk codes and excludes aggregates", {
   skip_if_no_corpus()
   con <- cog_open()
   on.exit(cog_close())
-  prefixes <- DBI::dbGetQuery(con,
-    "SELECT DISTINCT LEFT(item_code, 1) AS pfx FROM spending_long"
-  )$pfx
-  expect_true(all(prefixes %in% c("E", "F", "G", "K")))
+
+  # Classification is crosswalk membership, not prefixes (uscogdata#11):
+  # every row's code must classify as expenditure and never as
+  # intergovernmental (which lives in ig_long).
+  stray <- DBI::dbGetQuery(con,
+    "SELECT DISTINCT s.item_code
+     FROM spending_long s
+     LEFT JOIN summary_categories c USING (item_code)
+     WHERE c.category_type IS DISTINCT FROM 'expenditure'
+        OR c.spend_subtype = 'intergovernmental'"
+  )$item_code
+  expect_length(stray, 0L)
+
+  # Balance codes are stocks, not flows -- they must never appear in a
+  # spending result (uscogdata#25). Prefix filtering could not guarantee
+  # this (W/X/Y/Z balance codes share letters with flow codes).
+  balance_n <- DBI::dbGetQuery(con,
+    "SELECT count(*) AS n FROM spending_long WHERE item_code IN (
+       SELECT item_code FROM summary_categories WHERE category_type = 'balance'
+     )"
+  )$n
+  expect_equal(balance_n, 0)
 
   agg_count <- DBI::dbGetQuery(con,
     "SELECT count(*) AS n FROM spending_long WHERE is_aggregate"
@@ -335,14 +393,29 @@ test_that("spending_long filters to E/F/G/K prefixes and excludes aggregates", {
   expect_equal(agg_count, 0)
 })
 
-test_that("revenue_long filters to T/A/U/B/C/D prefixes and excludes aggregates", {
+test_that("revenue_long carries exactly the revenue crosswalk codes and excludes aggregates", {
   skip_if_no_corpus()
   con <- cog_open()
   on.exit(cog_close())
-  prefixes <- DBI::dbGetQuery(con,
-    "SELECT DISTINCT LEFT(item_code, 1) AS pfx FROM revenue_long"
-  )$pfx
-  expect_true(all(prefixes %in% c("T", "A", "U", "B", "C", "D")))
+
+  # The view carries EVERY revenue subtype; which of Census's two published
+  # concepts a query returns is decided per `revenue_concept` in R
+  # (uscogdata#12), exactly as `expenditure_concept` narrows spending_long.
+  stray <- DBI::dbGetQuery(con,
+    "SELECT DISTINCT s.item_code
+     FROM revenue_long s
+     LEFT JOIN summary_categories c USING (item_code)
+     WHERE c.category_type IS DISTINCT FROM 'revenue'"
+  )$item_code
+  expect_length(stray, 0L)
+
+  # No balance stock ever appears in a revenue result (uscogdata#25).
+  balance_n <- DBI::dbGetQuery(con,
+    "SELECT count(*) AS n FROM revenue_long WHERE item_code IN (
+       SELECT item_code FROM summary_categories WHERE category_type = 'balance'
+     )"
+  )$n
+  expect_equal(balance_n, 0)
 
   agg_count <- DBI::dbGetQuery(con,
     "SELECT count(*) AS n FROM revenue_long WHERE is_aggregate"

vignettes/population-denominators.Rmd

@@ -13,6 +13,8 @@ knitr::opts_chunk$set(eval = FALSE, collapse = TRUE, comment = "#>")
 
 # Why per-year population matters
 
+A note on units first, since every figure below is a rate: the numerator is in **full US dollars**. The raw Census files report **thousands of dollars** and the corpus keeps them that way in its own `amt` column, but `cog_spending()` and `cog_revenue()` multiply by 1000 on the way out, so `amt_per_capita_nominal` is already dollars per person. Do not scale it again.
+
 Per-capita finance numbers divide each year's spending or revenue by a population denominator. The choice of denominator is a research decision, not an implementation detail: a 24-year corpus paired with a single 5-year ACS estimate produces biased per-capita values whose magnitude scales with each government's population change.
 
 `uscogdata` defaults to the **Census F-33 population value Census itself uses to compute its published per-capita tables.** That value is recorded on every COG row as `population`, with `popyear` indicating the vintage. For a city that grew from 200,000 to 300,000 between 2000 and 2023, this default reproduces the per-capita value Census published. A static ACS denominator would have understated 2000 per-capita by ~33%.

vignettes/total-spending.Rmd

@@ -1,8 +1,8 @@
 ---
-title: "Total spending: Direct, Total, and when each is right"
+title: "Total spending: Primary, Direct, Total, and when each is right"
 output: rmarkdown::html_vignette
 vignette: >
-  %\VignetteIndexEntry{Total spending: Direct, Total, and when each is right}
+  %\VignetteIndexEntry{Total spending: Primary, Direct, Total, and when each is right}
   %\VignetteEngine{knitr::rmarkdown}
   %\VignetteEncoding{UTF-8}
 ---
@@ -17,17 +17,35 @@ knitr::opts_chunk$set(collapse = TRUE, comment = "#>")
 is about one government or several:
 
 1. **"What did my county spend in total, a decade ago vs today?"** — one
-   government, tracked over time. Either `direct` or `total` spending answers
-   this correctly, as long as the same concept is used for both years.
+   government, tracked over time. Any concept answers this correctly, as
+   long as the same concept is used for both years.
 2. **"How do all the counties in my state compare, a decade ago vs today,
    against the neighboring state?"** — several governments, summed together.
-   Here only `direct` gives the right answer; summing `total` across
-   governments double-counts money that passes between them.
+   Here only a non-intergovernmental concept (`primary` or `direct`) gives
+   the right answer; summing `total` across governments double-counts money
+   that passes between them.
 
-`cog_spending()`'s `expenditure_concept` argument (`"direct"` or `"total"`)
-controls which of these a query answers. This vignette walks through both
-questions with code that actually runs against the package's bundled fixture
-corpus, then explains why the second question refuses `"total"` outright.
+`cog_spending()`'s `expenditure_concept` argument controls which of these a
+query answers, via three nested concepts defined as sets of the crosswalk's
+`spend_subtype` values (never item-code first letters — the letter `Y` alone
+spans revenue, expenditure, and balance codes):
+
+- `"primary"` (the default) — the government's own service provision:
+  `operations` + `capital` + `assistance`.
+- `"direct"` — Census's published Direct Expenditure: `primary` plus
+  `interest` on debt and `insurance_benefits` (e.g. pension payments).
+- `"total"` — `direct` plus the `intergovernmental` leg.
+
+This vignette walks through both questions with code that actually runs
+against the package's bundled fixture corpus, then explains why the second
+question refuses `"total"` outright.
+
+Before any of the numbers below: every amount column here — `amt_nominal`,
+`amt_real`, and their `amt_per_capita_*` counterparts — is in **full US
+dollars**. The raw Census files report **thousands of dollars** and the
+corpus preserves that in its own `amt` column, but the verbs multiply by 1000
+on the way out. So `amt_nominal = 1317000` means $1.317 million, not $1.317
+billion. Do not scale it again.
 
 ```{r}
 library(uscogdata)
@@ -61,20 +79,23 @@ al_total <- cog_spending(
 al_total
 ```
 
-The `intergovernmental` rows are what `"total"` adds on top of `"direct"`
-(`capital` + `operations`): Alabama's own payments out to counties and
-cities for highway work. Because this query only ever concerns Alabama,
-including that piece is safe -- there's no other government's number it
-could be double-counted against.
+The `intergovernmental` rows are what `"total"` adds on top of the
+non-intergovernmental subtypes (here `capital` + `operations`): Alabama's
+own payments out to counties and cities for highway work. Because this
+query only ever concerns Alabama, including that piece is safe -- there's
+no other government's number it could be double-counted against.
 
-`"direct"` (the default) answers the same trend question just as validly:
+`"primary"` (the default) answers the same trend question just as validly
+(for Highways, which maps only to operations/capital codes, `"primary"` and
+`"direct"` coincide -- there is no highway-specific interest or insurance
+benefit to add):
 
 ```{r}
-al_direct <- cog_spending(
+al_primary <- cog_spending(
   "010000226085", years = c(2012, 2020), category = "Highways"
-  # expenditure_concept = "direct" is the default; shown here for contrast
+  # expenditure_concept = "primary" is the default; shown here for contrast
 )
-al_direct
+al_primary
 ```
 
 Both are internally consistent series. What breaks the comparison is
@@ -86,8 +107,8 @@ every year in the series.
 # Archetype 2: a cross-government rollup
 
 `cog_geographic_rollup()` sums spending across state/county/city layers for
-a place. Its default -- and, as shown below, its *only* accepted value for
-`expenditure_concept` -- is `"direct"`:
+a place. Its default is `"primary"`, and (as shown below) it accepts only
+the non-intergovernmental concepts, `"primary"` and `"direct"`:
 
 ```{r}
 fl_rollup <- cog_geographic_rollup(
@@ -138,15 +159,15 @@ shows up **twice** in the underlying corpus:
   the county is the government that actually lets the contract and pays the
   paving crew.
 
-`direct` (item codes `E`/`F`/`G`) only ever counts the second of those --
-the government that actually did the spending. `total` (Direct plus the
-`M`/`L` intergovernmental legs) counts the first one *as well*, which is
-exactly right for describing Alabama's own budget: Alabama's `total`
-genuinely includes the $10M it committed to highways, whether it built the
-road itself or paid the county to. But sum `total` across Alabama **and**
-the county, and that $10M is counted twice -- once as Alabama's payment out,
-once as the county's spending in -- reporting $20M of highway work for $10M
-actually spent.
+`primary` and `direct` (the crosswalk's non-intergovernmental expenditure
+subtypes) only ever count the second of those -- the government that
+actually did the spending. `total` (Direct plus the intergovernmental leg)
+counts the first one *as well*, which is exactly right for describing
+Alabama's own budget: Alabama's `total` genuinely includes the $10M it
+committed to highways, whether it built the road itself or paid the county
+to. But sum `total` across Alabama **and** the county, and that $10M is
+counted twice -- once as Alabama's payment out, once as the county's
+spending in -- reporting $20M of highway work for $10M actually spent.
 
 This is exactly the shape of query `cog_geographic_rollup()` exists to run
 (summing across layers of government), so it refuses `"total"` rather than
@@ -161,48 +182,51 @@ share of a government's own Direct spending is:
 
 | Government type | Intergovernmental / Direct |
 |---|---|
-| State  | 16.7%-48.4% (varies by year; 24.0% pooled across all four) |
-| County | 3.4%-5.1% (varies by year) |
-| City   | 2.6%-3.1% (varies by year) |
+| State  | 33.1%-40.5% (varies by year; 36.2% pooled across all four) |
+| County | 3.3%-4.8% (varies by year) |
+| City   | 2.4%-2.9% (varies by year) |
 
-So the Direct/Total choice matters overwhelmingly for **state** governments
--- a state's Total genuinely differs from its Direct by a meaningful margin,
-while for a county or city the two are close. The state range is also far
-wider than a single flat figure would suggest: legacy wide-era years (2011:
-48.4%) carry proportionally more intergovernmental spending than the modern
-era (2019-2020: 16.7%-17.0%), so a state's Direct/Total gap can be nearly
-3x larger a decade earlier than it is today. That's also why the mistake
-this vignette warns about is easy to make unnoticed at the county/city level
-and costly at the state level: rolling up every government in a state using
-`total` instead of `direct` overstates the true figure -- measured at 7.6%
-for Alabama in FY2019, and 11.6% nationally.
+So the Direct/Total choice matters overwhelmingly for **state**
+governments -- a state's Total genuinely differs from its Direct by more
+than a third, while for a county or city the two are close. (The state
+share is much larger than pre-#11 measurements suggested, because the
+intergovernmental leg now correctly includes the `Q11`/`Q12`/`Q18` state
+payments to school systems -- for most states the single largest transfer
+they make.) That's also why the mistake this vignette warns about is easy
+to make unnoticed at the county/city level and costly at the state level:
+rolling up every government using `total` instead of `primary`/`direct`
+overstates the FY2019 figure by 24.1% for Alabama and 23.2% nationally.
 
-# Why Total = Direct + M + L, not Direct + M
+# Why Total = Direct + M + L + Q, not Direct + M
 
-It's tempting to assume `total` only needs to add `M`. But `M` and `L` are
-both money the queried government itself pays **out** -- they're not two
-different accounts of a receiving government's revenue. `M` is what it
-pays to other **local** governments (e.g. a county paying a city for a
-shared paving contract); `L` is what it pays **up** to its **state**
-government (e.g. a county's contribution to a state-administered program).
-A local government's Total genuinely includes both legs, because both are
-its own spending, just routed to a different kind of recipient. On the
-bundled fixture corpus (all 50 states, 2011/2012/2019/2020), `L` is 0 for
-state governments (a state has no "payments to the state government" leg of
-its own) but is 43%-51% the size of `M` for counties (varies by year) and
-144%-189% the size of `M` for cities (varies by year; 166% pooled across
-all four) -- so a `total` that omitted `L` would silently undercount Total
-specifically for local governments, and for cities `L` is often the
-*larger* of the two legs.
-`cog_spending(expenditure_concept = "total")` includes both legs (excluding
-the `L--` family-total rollup row, which would double-count its own
-components).
+It's tempting to assume `total` only needs to add `M`. But the
+intergovernmental leg has three families, all money the queried government
+itself pays **out** -- they're not different accounts of a receiving
+government's revenue. `M` is what it pays to other **local** governments
+(e.g. a county paying a city for a shared paving contract); `L` is what it
+pays **up** to its **state** government (e.g. a county's contribution to a
+state-administered program); and `Q11`/`Q12`/`Q18` are a state's payments
+to **school systems** (K-12 and higher-ed aid -- for most states the
+single largest transfer they make, and the piece the pre-#11 prefix
+allowlist silently dropped, finding F-017). A government's Total genuinely
+includes every leg it pays, because each is its own spending, just routed
+to a different kind of recipient. On the bundled fixture corpus (all 50
+states, 2011/2012/2019/2020), `L` is 0 for state governments (a state has
+no "payments to the state government" leg of its own) but is 43%-51% the
+size of `M` for counties (varies by year) and 144%-189% the size of `M`
+for cities (varies by year; 166% pooled across all four) -- so a `total`
+that omitted `L` would silently undercount Total specifically for local
+governments, and for cities `L` is often the *larger* of the two legs.
+`cog_spending(expenditure_concept = "total")` includes every leg
+(excluding the `L--` family-total rollup row, which would double-count its
+own components).
 
 # Composition rules
 
-- `expenditure_concept` (whose spending counts -- Direct vs Direct plus
-  intergovernmental) is **orthogonal** to `basis` (which vintage of the
-  item-code space a query resolves against -- `"harmonized"` vs `"raw"`).
+- `expenditure_concept` (whose spending counts -- Primary, Direct, or
+  Direct plus intergovernmental) is **orthogonal** to `basis` (which
+  vintage of the item-code space a query resolves against --
+  `"harmonized"` vs `"raw"`).
   They combine freely: `expenditure_concept = "total", basis = "raw"` is a
   valid, meaningful query, and so is every other pairing.
 - `expenditure_concept = "total"` is **mutually exclusive** with `recipe`: a
@@ -218,12 +242,15 @@ components).
 
 # Summary
 
-- Comparing one government to itself over time: `"direct"` or `"total"`
-  both work -- pick one and hold it fixed across every year compared.
+- Comparing one government to itself over time: any concept works -- pick
+  one and hold it fixed across every year compared.
 - Comparing or summing across governments -- counties within a state, a
-  state against its neighbor, cities against counties: use `"direct"`.
-  `cog_geographic_rollup()` and `cog_peer_compare()` enforce this by
-  refusing `"total"`.
-- `"total"` = Direct (`E`/`F`/`G`) + intergovernmental (`M` to local
-  governments + `L` to the state government, excluding the `L--`
-  family-total row).
+  state against its neighbor, cities against counties: use `"primary"`
+  (the default) or `"direct"`. `cog_geographic_rollup()` and
+  `cog_peer_compare()` enforce this by refusing `"total"`.
+- `"primary"` = operations + capital + assistance. `"direct"` = primary +
+  interest on debt + insurance trust benefits (Census's published Direct
+  Expenditure). `"total"` = direct + intergovernmental (`M` to local
+  governments, `L` to the state government excluding the `L--`
+  family-total row, and `Q11`/`Q12`/`Q18` state payments to school
+  systems).