fix: push limit/offset into the query instead of materializing then slicing

cog-api's paginate() sliced an already-fully-materialized result: every
page of a deep sweep re-ran the whole cog_spending()/cog_revenue() query
and re-listified every row, just to keep up to 1000 and discard the
rest. A 193,105-row/194-page fleet-wide sweep (cog_explorer's Southern
guide, corpus summary build) repeated that full cost 194 times and
wedged the production server for hours on 2026-08-06 -- single request,
CPU-bound, single-threaded plumber process, no other request could get
through, not even /health.

cog_spending()/cog_revenue() gain optional limit/offset, pushed into
.build_verb_sql() as SQL LIMIT/OFFSET behind the existing (already
deterministic) ORDER BY. The full unpaginated row count rides along via
COUNT(*) OVER() in the same scan -- exposed as a total_rows attribute --
so a caller walking pages never needs a second round trip to ask how
many there are. A page now costs O(limit), not O(full result).

Mutually exclusive with complete = TRUE (which fills a grid over the
FULL requested (year, category) space -- pagination over a partial slice
of already-grouped rows has no defined meaning for the cells it would
fill) and with recipe (whose result comes from a separate, not-yet-wired
query path). Both abort with a clear classed condition rather than
silently ignoring the parameter.

limit/offset default to NULL; every existing call site is unaffected.

.gitea/workflows/ci.yml

@@ -11,6 +11,21 @@ jobs:
     steps:
       - name: Install system libraries and Node.js (required by actions/checkout)
         run: |
+          # Switch apt to HTTPS mirrors. Measured from this runner on
+          # 2026-08-04: the SAME index file takes 20.1s over http:// and 3.1s
+          # over https://. apt fetches many indexes serially, so http:// does
+          # not read as "slow" -- it reads as a hang (zero bytes in
+          # /var/cache/apt/archives after 3+ minutes, apt's http workers parked
+          # in S state). rocker/r-ver:4.4 already ships ca-certificates and
+          # apt 2.8.3 has the https method built in, so nothing needs to be
+          # installed over http first to bootstrap this.
+          # `|| true` because the step runs under `sh -e`: on an image whose
+          # sources live in the other location, the missing-file sed must not
+          # kill the job.
+          sed -i -E 's#http://(archive|security)\.ubuntu\.com#https://\1.ubuntu.com#g' \
+            /etc/apt/sources.list.d/ubuntu.sources 2>/dev/null || true
+          sed -i -E 's#http://(archive|security)\.ubuntu\.com#https://\1.ubuntu.com#g' \
+            /etc/apt/sources.list 2>/dev/null || true
           apt-get update -qq
           apt-get install -y --no-install-recommends \
             nodejs git \

DESCRIPTION

@@ -1,7 +1,7 @@
 Package: uscogdata
 Type: Package
 Title: Curated Reader for the Civilytics US Census of Governments Finance Corpus
-Version: 0.1.0
+Version: 0.2.0
 Authors@R:
     person("Civilytics", , , "jknowles@gmail.com", role = c("aut", "cre"))
 Description: Curated R verbs over the Civilytics US Census of Governments

NEWS.md

@@ -1,5 +1,80 @@
+# uscogdata 0.2.0
+
+## New features
+
+* `cog_spending()` and `cog_revenue()` accept the reserved category
+  `"All Categories"`, returning one summed row per
+  `(year, canonical_govid, subtype)` across every category inside the
+  requested concept's subtype scope. Filtering the result to
+  `spend_subtype == "operations"` gives an operating-expenditure total.
+  `cog_geographic_rollup()` inherits it,
+  which is the efficient way to build a geographic total — previously a
+  caller had to issue one rollup per category and sum the results
+  (cog-api#37).
+
+  `"All Categories"` is not the same thing as `expenditure_concept = "total"`.
+  The concept chooses which subtypes are in scope; `"All Categories"` chooses
+  whether the rows inside that scope are broken out or summed.
+
+* `cog_categories()` advertises `"All Categories"` for the expenditure and
+  revenue vocabularies, so the reserved value is discoverable.
+
+* Coverage signposting (see "Signposting now catches partially-suppressed
+  categories" below) now also works in `category = "All Categories"` mode.
+  The recipe-suggestion candidate query used to be scoped by `category`,
+  which is never a match for the reserved `"All Categories"` value, so
+  `provenance$suggestions` always came back empty there — the one mode whose
+  whole point is "you cannot sum the wrong scope" was silently unable to
+  signal a wrong scope. The candidate query is now scoped by the concept's
+  subtype allowlist instead, symmetric with how `.build_verb_sql()` itself
+  scopes the summed total: Los Angeles County FY2011, `category = "All
+  Categories"` still excludes $271,589,000 of aggregate-published Public
+  Welfare (`E68`), but now names `recipe = "welfare_cash_e68_wide"` to
+  recover it instead of reporting zero suggestions.
+
+## Documentation
+
+* `cog_geographic_rollup()` and `cog_peer_compare()` now document that
+  `provenance$coverage`'s `n_units_reporting` is **category-conditional** and
+  is not a response rate: a government that was surveyed and genuinely spends
+  nothing in the requested category is indistinguishable from one never
+  surveyed (uscogdata#36).
+
 # uscogdata 0.1.0 (development)
 
+## Signposting now catches partially-suppressed categories
+
+* A coverage suggestion used to fire only when a category returned **no rows
+  at all** in a requested year. That missed the more dangerous case: a
+  category that still returns rows while silently dropping component codes
+  the wide era publishes only as aggregates (#9). `cog_spending(category =
+  "Public Welfare")` for FY2011 returned a plausible figure that omitted
+  `E67`/`E68` entirely -- for Los Angeles County, $2,075,461,000 of a true
+  $5,261,404,000, a 39% understatement, with `provenance$suggestions` empty.
+* Suggestions now also fire on **partial** coverage, and every suggestion
+  carries `trigger` (`"empty_year"` or `"suppressed_component"`),
+  `suppressed_amount`, `suppressed_years` and `suppressed_codes`, so a caller
+  can see how much is missing and decide whether to re-run with the recipe.
+* `cog_revenue()` gets the same fix through the shared verb path. Alaska's
+  FY2011 `Miscellaneous Revenue` reported $943,842,000 while dropping
+  $1,899,995,000 of aggregate-published `U4-` rents and royalties.
+* The trigger stays recipe-driven, so it only fires where a harmonization
+  recipe actually exists to name the fix. `higher_ed_e18_wide` and
+  `general_gov_e89_wide` stay silent in every year measured on the bundled
+  fixture, because their components are ordinary classified leaves even
+  pre-2012.
+* The `suppressed_component` trigger (and any `suppressed_amount`/
+  `suppressed_codes` an `empty_year` fire also carries) is scoped to the
+  calling verb's own flow family: `cog_spending()` only ever measures E/F/G
+  component dollars, `cog_revenue()` only T/A/U/B/C/D. A component from the
+  OTHER flow family reports `suppressed_amount = 0` rather than a fabricated
+  claim. The `empty_year` trigger itself is not flow-scoped -- a category
+  belonging to the other flow (e.g. `cog_spending(category = "IG Local")`)
+  still returns zero rows and can still fire, in any year including modern
+  ones, naming the recipe whose own generic join finds real data for this
+  government. That is a mis-scoped query, not a corpus-format gap, so its
+  `suppressed_amount` is correctly 0.
+
 ## New: `cog_balances()` for cash-and-security holdings
 
 * New `cog_balances()` exposes the 14 cash-and-security holding codes

R/balances.R

@@ -28,7 +28,12 @@
 #'   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.
+#'   one `dplyr::filter()` away. The reserved pseudo-category
+#'   `"All Categories"` (see [cog_spending()]) is **not** supported here and
+#'   errors with class `uscogdata_all_categories_unsupported`: it sums a
+#'   concept's subtype scope, and holdings are a stock with no concept
+#'   vocabulary to sum across. Omit `category` to get every category broken
+#'   out instead.
 #' @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.
@@ -74,6 +79,13 @@ cog_balances <- function(govid, years, category = NULL,
   # 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.
+  #
+  # allow_all_categories is left at its FALSE default (contrast
+  # .verb_spendrev(), which passes TRUE): the all-categories mode's "sum"
+  # only means something in terms of a concept's subtype scope, and holdings
+  # have no concept vocabulary. The reuse above is exactly why this can be a
+  # one-line default rather than a second bespoke check -- see the
+  # validator's own doc comment for the incident that made that matter.
   .validate_verb_inputs(govid, years, category, per_capita, adjust_to_year,
                         recipe)
   years <- as.integer(years)

R/categories.R

@@ -5,23 +5,33 @@
 #' 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`,
 #'   `n_codes`, `item_codes` (comma-separated, alphabetical). Sorted by
-#'   `category_type`, `category`, `subtype`.
+#'   `category_type`, `category`, `subtype`. Includes one row per flow for the
+#'   reserved pseudo-category `"All Categories"`, which carries `NA` for
+#'   `subtype`, `n_codes` and `item_codes` because it is a query mode rather
+#'   than a crosswalk entry — see [cog_spending()]'s `category` argument.
 #' @export
 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 +58,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",
@@ -56,5 +66,28 @@ cog_categories <- function(type = NULL, pattern = NULL) {
     "GROUP BY category, category_type, subtype
      ORDER BY category_type, category, subtype"
   )
-  tibble::as_tibble(DBI::dbGetQuery(con, sql))
+  out <- tibble::as_tibble(DBI::dbGetQuery(con, sql))
+
+  # The reserved pseudo-category is a query mode, not a crosswalk row, so it
+  # has no item codes to report -- hence NA rather than 0 for n_codes. It is
+  # emitted for the two FLOW vocabularies only: cog_balances() returns a stock
+  # and has no concept argument to sum within.
+  pseudo <- tibble::tibble(
+    category      = .ALL_CATEGORIES,
+    category_type = c("expenditure", "revenue"),
+    subtype       = NA_character_,
+    n_codes       = NA_integer_,
+    item_codes    = NA_character_
+  )
+  if (!is.null(type)) {
+    db_type <- if (type == "spending") "expenditure" else type
+    pseudo <- pseudo[pseudo$category_type == db_type, , drop = FALSE]
+  }
+  if (!is.null(pattern) && nrow(pseudo) > 0L) {
+    keep <- grepl(pattern, pseudo$category, ignore.case = TRUE)
+    pseudo <- pseudo[keep, , drop = FALSE]
+  }
+  if (nrow(pseudo) == 0L) return(out)
+  out <- rbind(out, pseudo)
+  out[order(out$category_type, out$category, out$subtype), , drop = FALSE]
 }

R/explain.R

@@ -125,8 +125,15 @@ cog_explain <- function(result, format = c("print", "list")) {
   if (length(prov$suggestions) > 0L) {
     cli::cli_h2("Suggestions")
     sugg_lines <- vapply(prov$suggestions, function(s) {
-      sprintf("%s -- %s (years %s-%s): %s", s$recipe_id, s$label,
+      line <- sprintf("%s -- %s (years %s-%s): %s", s$recipe_id, s$label,
               s$available_years[1], s$available_years[2], s$hint)
+      if (isTRUE(s$suppressed_amount > 0)) {
+        line <- paste0(line, sprintf(" [$%s excluded from %s: %s]",
+          formatC(s$suppressed_amount, format = "f", digits = 0, big.mark = ","),
+          paste0("FY", s$suppressed_years, collapse = ", "),
+          paste(s$suppressed_codes, collapse = ", ")))
+      }
+      line
     }, character(1))
     cli::cli_ul(sugg_lines)
   }

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

@@ -240,6 +240,23 @@ cog_find_peers <- function(target_govid,
 #'     group_by(year) |>
 #'     summarise(p50 = quantile(total, 0.5, na.rm = TRUE))
 #'   ```
+#' @section Reading `coverage`:
+#' `provenance$coverage` reports `n_units_reporting` against
+#' `n_units_expected` per year. **`n_units_reporting` is category-conditional:
+#' it counts cohort members with rows for the category you asked for, not
+#' cohort members collected that year.** A government that was surveyed and
+#' genuinely spends nothing in that category is indistinguishable here from one
+#' that was never surveyed.
+#'
+#' The ratio is therefore **not a response rate** and must not be used as one.
+#' In FY2022 — a complete census year — Georgia reports 393 of 567 cities for
+#' `category = "Police"`; the 174-city gap is overwhelmingly cities that
+#' contract policing to the county sheriff, not non-response.
+#'
+#' The comparison that *is* valid is the same category across a census year
+#' (ending in 2 or 7) and a sample year, where the real-zero component is
+#' roughly constant and the difference reflects the survey cycle. `is_census_year`
+#' marks which is which.
 #' @export
 cog_peer_compare <- function(target_govid, peers, category, years,
                              per_capita = TRUE, adjust_to_year = NULL,

R/provenance.R

@@ -67,7 +67,15 @@
     basis_note = basis_note,
     expenditure_concept = expenditure_concept,
     expenditure_concept_note = expenditure_concept_note,
-    expenditure_concept_direct_suppressed = isTRUE(expenditure_concept_direct_suppressed),
+    # isTRUE() alone would collapse a deliberate NA (all-categories mode,
+    # where suppression detection cannot run -- see .verb_spendrev()) down to
+    # FALSE, turning "we don't know" back into the false claim this field
+    # exists to avoid. Preserve NA; otherwise normalize to a strict logical.
+    expenditure_concept_direct_suppressed = if (isTRUE(is.na(expenditure_concept_direct_suppressed))) {
+      NA
+    } else {
+      isTRUE(expenditure_concept_direct_suppressed)
+    },
     revenue_concept = revenue_concept,
     harmonization = harmonization %||% list(
       applied = FALSE, na_rows_excluded = 0L, na_amount_excluded = 0,

R/revenue.R

@@ -8,6 +8,17 @@
 #' multiplies by 1000 and records the conversion in `provenance`).
 #'
 #' @inheritParams cog_spending
+#' @param category Character vector of category names (from
+#'   `summary_categories.category`), or `NULL` for all categories broken out
+#'   one row each. The reserved value `"All Categories"` instead returns a
+#'   single summed row per `(year, canonical_govid, subtype)`, covering every
+#'   category inside the requested concept's subtype scope. It cannot be
+#'   combined with other category names, and it is not the same thing as
+#'   `revenue_concept = "total"`: the concept chooses which subtypes are in
+#'   scope, `"All Categories"` chooses whether rows inside that scope are
+#'   broken out or summed. Because the result keeps one row per
+#'   `revenue_subtype`, filtering the returned frame to
+#'   `revenue_subtype == "own_source"` gives an own-source revenue total.
 #' @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
@@ -43,7 +54,7 @@ cog_revenue <- function(govid, years, category = NULL,
                         per_capita = FALSE, adjust_to_year = NULL,
                         basis = c("harmonized", "raw"), recipe = NULL,
                         revenue_concept = c("general", "total"),
-                        complete = FALSE) {
+                        complete = FALSE, limit = NULL, offset = NULL) {
   # 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
@@ -62,6 +73,8 @@ cog_revenue <- function(govid, years, category = NULL,
     basis          = basis,
     recipe         = recipe,
     revenue_concept = revenue_concept,
-    complete       = complete
+    complete       = complete,
+    limit          = limit,
+    offset         = offset
   )
 }

R/rollup.R

@@ -19,7 +19,11 @@
 #'   `state`, `county`, `city`. Each element is a character vector of
 #'   `canonical_govid` values. At least one layer required.
 #' @param category Single category name or character vector (passed through
-#'   to [cog_spending()]).
+#'   to [cog_spending()]), or the reserved `"All Categories"` for one summed
+#'   row per `(year, canonical_govid, subtype)` covering every category in the
+#'   concept's scope. `"All Categories"` is the efficient way to build a
+#'   geographic total: without it a caller must issue one rollup per category
+#'   and sum the results themselves.
 #' @param years Integer vector of years.
 #' @param per_capita If `TRUE`, per-capita uses each gov's own per-year
 #'   population from `gov_population_yearly`. Govs with missing population
@@ -56,6 +60,23 @@
 #'   `codes_included`, `aggregate_fallback`, `scope_note`, `notes`. Carries a
 #'   `provenance` attribute with `verb = "cog_geographic_rollup"`, `layers`,
 #'   and `rollup$included_govids` / `rollup$excluded_govids`.
+#' @section Reading `coverage`:
+#' `provenance$coverage` reports `n_units_reporting` against
+#' `n_units_expected` per year. **`n_units_reporting` is category-conditional:
+#' it counts governments with rows for the category you asked for, not
+#' governments collected that year.** A government that was surveyed and
+#' genuinely spends nothing in that category is indistinguishable here from one
+#' that was never surveyed.
+#'
+#' The ratio is therefore **not a response rate** and must not be used as one.
+#' In FY2022 — a complete census year — Georgia reports 393 of 567 cities for
+#' `category = "Police"`; the 174-city gap is overwhelmingly cities that
+#' contract policing to the county sheriff, not non-response.
+#'
+#' The comparison that *is* valid is the same category across a census year
+#' (ending in 2 or 7) and a sample year, where the real-zero component is
+#' roughly constant and the difference reflects the survey cycle. `is_census_year`
+#' marks which is which.
 #' @export
 cog_geographic_rollup <- function(govids, category, years,
                                   per_capita = FALSE, adjust_to_year = NULL,

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)

R/spending.R

@@ -19,6 +19,14 @@
 .spend_subtypes_primary <- c("operations", "capital", "assistance")
 .spend_subtypes_direct  <- c(.spend_subtypes_primary, "interest", "insurance_benefits")
 
+# The reserved pseudo-category. Deliberately NOT "Total": `category = "Total"`
+# would sit one argument away from `expenditure_concept = "total"` and mean
+# something different -- the concept selects WHICH SUBTYPES are in scope, this
+# selects whether the rows inside that scope are broken out by category or
+# summed. "All Categories" states the operation and cannot be misread as the
+# concept.
+.ALL_CATEGORIES <- "All Categories"
+
 #' @noRd
 .expenditure_concept_subtypes <- function(concept) {
   switch(concept,
@@ -63,7 +71,16 @@
 #' @param govid Character vector of `canonical_govid` values.
 #' @param years Integer vector of years.
 #' @param category Character vector of category names (from
-#'   `summary_categories.category`), or `NULL` for all categories.
+#'   `summary_categories.category`), or `NULL` for all categories broken out
+#'   one row each. The reserved value `"All Categories"` instead returns a
+#'   single summed row per `(year, canonical_govid, subtype)`, covering every
+#'   category inside the requested concept's subtype scope. It cannot be
+#'   combined with other category names, and it is not the same thing as
+#'   `expenditure_concept = "total"`: the concept chooses which subtypes are in
+#'   scope, `"All Categories"` chooses whether rows inside that scope are
+#'   broken out or summed. Because the result keeps one row per
+#'   `spend_subtype`, filtering the returned frame to
+#'   `spend_subtype == "operations"` gives an operating-expenditure total.
 #' @param per_capita If `TRUE`, adds `amt_per_capita_nominal` (and
 #'   `amt_per_capita_real` when `adjust_to_year` is set) using the per-year
 #'   Census F-33 population from `gov_population_yearly`. Result also gains
@@ -133,7 +150,12 @@
 #'   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.
+#'   IG. When `category = "All Categories"` is combined with
+#'   `expenditure_concept = "total"`, this detection cannot run (it keys on
+#'   per-category rows, which all-categories mode collapses to one literal
+#'   value), so `expenditure_concept_direct_suppressed` is `NA` rather than a
+#'   possibly-false `FALSE`; query an explicit `category` to get a real
+#'   answer.
 #' @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:
@@ -156,6 +178,13 @@
 #'   `recipe` or with `expenditure_concept = "total"` (class
 #'   `uscogdata_complete_unsupported`) — neither draws its cells from
 #'   `code_set`.
+#' @param limit Maximum number of result rows to return, pushed into the SQL
+#'   query itself (`LIMIT`/`OFFSET`) rather than applied after the full
+#'   result is materialized. `NULL` (the default) returns every matching row,
+#'   exactly as before this parameter existed. Mutually exclusive with
+#'   `recipe` and with `complete = TRUE` -- see `offset` and `total_rows`.
+#' @param offset Rows to skip before `limit` starts counting (0-based).
+#'   Ignored if `limit` is `NULL`; defaults to `0L` when `limit` is 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`,
@@ -163,13 +192,17 @@
 #'   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.
+#'   per-year `absence_means` rule that was applied. When `limit` is set,
+#'   also carries a `total_rows` attribute: the full unpaginated row count,
+#'   computed by the same query (`COUNT(*) OVER()`) rather than a second
+#'   round trip -- so a caller walking pages never has to ask "how many are
+#'   there" separately.
 #' @export
 cog_spending <- function(govid, years, category = NULL,
                          per_capita = FALSE, adjust_to_year = NULL,
                          basis = c("harmonized", "raw"), recipe = NULL,
                          expenditure_concept = c("primary", "direct", "total"),
-                         complete = FALSE) {
+                         complete = FALSE, limit = NULL, offset = NULL) {
   # 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
@@ -188,7 +221,9 @@ cog_spending <- function(govid, years, category = NULL,
     basis         = basis,
     recipe        = recipe,
     expenditure_concept = expenditure_concept,
-    complete      = complete
+    complete      = complete,
+    limit         = limit,
+    offset        = offset
   )
 }
 
@@ -216,7 +251,7 @@ cog_spending <- function(govid, years, category = NULL,
                            basis = c("harmonized", "raw"), recipe = NULL,
                            expenditure_concept = c("primary", "direct", "total"),
                            revenue_concept = c("general", "total"),
-                           complete = FALSE) {
+                           complete = FALSE, limit = NULL, offset = NULL) {
   basis_explicit <- length(basis) == 1L
   basis <- match.arg(basis, c("harmonized", "raw"))
   # match.arg() itself throws a base `simpleError`, not an rlang-classed
@@ -257,8 +292,24 @@ cog_spending <- function(govid, years, category = NULL,
   }
 
   govid <- .coerce_govid_input(govid, arg = "govid")
+  # allow_all_categories = TRUE: cog_spending()/cog_revenue() are the two
+  # verbs the reserved pseudo-category is defined for. cog_balances() shares
+  # this validator but leaves the argument at its FALSE default, so it
+  # rejects "All Categories" instead of silently returning zero rows
+  # (finding 3, all-categories review).
   .validate_verb_inputs(govid, years, category, per_capita, adjust_to_year,
-                        recipe)
+                        recipe, allow_all_categories = TRUE)
+
+  # Recognize the reserved pseudo-category. Detected after type validation so a
+  # non-character `category` still fails with the ordinary type error.
+  all_categories <- !is.null(category) && .ALL_CATEGORIES %in% category
+  if (all_categories && length(category) > 1L) {
+    cli::cli_abort(c(
+      "{.val {(.ALL_CATEGORIES)}} cannot be combined with other categories.",
+      "i" = "It already sums every category in the requested concept's scope.",
+      "*" = "Ask for it alone, or list the specific categories you want."
+    ), class = "uscogdata_all_categories_not_combinable")
+  }
 
   if (!is.null(recipe) && identical(expenditure_concept, "total")) {
     cli::cli_abort(c(
@@ -299,6 +350,44 @@ cog_spending <- function(govid, years, category = NULL,
       "Use `expenditure_concept = \"direct\"` with `complete = TRUE`, or drop `complete`."
     )
   }
+  if (complete && all_categories) {
+    .abort_complete_unsupported(
+      "`category = \"All Categories\"` collapses the category dimension that `code_set` grids over (see `.completion_grid_sql()`), so there is no per-category grid left to fill -- filling a summed row has no defined semantics.",
+      "Drop `complete`, or use `complete = TRUE` with an explicit `category` (or `category = NULL` for every category)."
+    )
+  }
+
+  # limit/offset push the page into the SQL itself (see .build_verb_sql()),
+  # so the two things that would make "a page of what" ambiguous are refused
+  # up front rather than silently ignored: complete = TRUE fills a grid over
+  # the FULL requested (year, category) space, and a recipe's result comes
+  # from .run_recipe()'s own query, which this function does not touch.
+  if (!is.null(limit)) {
+    limit <- as.integer(limit)
+    if (length(limit) != 1L || is.na(limit) || limit < 0L) {
+      cli::cli_abort("`limit` must be a single non-negative integer.",
+                     class = "uscogdata_invalid_pagination")
+    }
+    offset <- if (is.null(offset)) 0L else as.integer(offset)
+    if (length(offset) != 1L || is.na(offset) || offset < 0L) {
+      cli::cli_abort("`offset` must be a single non-negative integer.",
+                     class = "uscogdata_invalid_pagination")
+    }
+    if (complete) {
+      cli::cli_abort(c(
+        "`limit`/`offset` cannot be combined with `complete = TRUE`.",
+        "i" = "`complete` fills a grid over the FULL requested (year, category) space; paginating a slice of already-grouped rows has no defined meaning for the cells it would fill.",
+        "*" = "Drop `limit`/`offset`, or drop `complete`."
+      ), class = "uscogdata_complete_pagination_conflict")
+    }
+    if (!is.null(recipe)) {
+      cli::cli_abort(c(
+        "`limit`/`offset` cannot be combined with `recipe`.",
+        "i" = "A recipe's result comes from a separate query (`.run_recipe()`) that pagination is not wired into yet.",
+        "*" = "Drop `limit`/`offset`, or drop `recipe`."
+      ), class = "uscogdata_recipe_pagination_conflict")
+    }
+  }
 
   years   <- as.integer(years)
   if (!is.null(adjust_to_year)) adjust_to_year <- as.integer(adjust_to_year)
@@ -312,6 +401,7 @@ cog_spending <- function(govid, years, category = NULL,
 
   recipe_block <- NULL
   category_for_prov <- category
+  total_rows <- NULL  # set below only when limit is non-NULL (non-recipe path)
   if (!is.null(recipe)) {
     .require_schema_v5(con, manifest, "recipe =")
     .validate_recipe_id(con, recipe)
@@ -333,9 +423,32 @@ cog_spending <- function(govid, years, category = NULL,
     } else {
       NULL
     }
-    sql <- .build_verb_sql(view, subtype_col, govid, years, category, ig_view,
-                           subtype_scope)
+    sql <- .build_verb_sql(view, subtype_col, govid, years,
+                           if (all_categories) NULL else category,
+                           ig_view, subtype_scope,
+                           all_categories = all_categories,
+                           limit = limit, offset = offset)
     result <- tibble::as_tibble(DBI::dbGetQuery(con, sql))
+    if (!is.null(limit)) {
+      # COUNT(*) OVER() rides along as an ordinary column so the total comes
+      # from the same scan when this page has any rows -- see
+      # .build_verb_sql(). An empty page (offset past the end) carries no
+      # such row to read it from, so that one case falls back to a second,
+      # unpaginated COUNT(*) query rather than reporting a wrong zero.
+      if (nrow(result) > 0L) {
+        total_rows <- result$pagination_total_rows[[1]]
+        result$pagination_total_rows <- NULL
+      } else {
+        count_sql <- sprintf(
+          "SELECT COUNT(*) AS n FROM (%s) AS _uncounted",
+          .build_verb_sql(view, subtype_col, govid, years,
+                          if (all_categories) NULL else category,
+                          ig_view, subtype_scope,
+                          all_categories = all_categories)
+        )
+        total_rows <- as.integer(DBI::dbGetQuery(con, count_sql)$n[[1]])
+      }
+    }
   }
 
   # Fill BEFORE per_capita / inflation so the added cells get the same
@@ -395,7 +508,11 @@ cog_spending <- function(govid, years, category = NULL,
     }
     suggestions <- .build_suggestions(con, govid, years, category,
                                        direct_leg_result,
-                                       resolved$basis, flow_prefixes)
+                                       resolved$basis, flow_prefixes,
+                                       .select_long_view(view_base, resolved$basis),
+                                       all_categories = all_categories,
+                                       subtype_col = subtype_col,
+                                       subtype_scope = subtype_scope)
   }
 
   # C1(b): when expenditure_concept = "total", flag any row where the IG
@@ -407,13 +524,32 @@ cog_spending <- function(govid, years, category = NULL,
   # direct spending in that category, which is correct, ordinary data). When
   # a covering recipe is found, both the row-level notes and the provenance
   # say so rather than pass silently as a plausible Total.
-  direct_suppressed_info <- if (identical(expenditure_concept, "total")) {
+  #
+  # In all-categories mode this cannot run at all: .detect_direct_suppressed()
+  # keys on (year, canonical_govid, category), and every row shares the same
+  # literal "All Categories" value, so the key collides across every real
+  # category for that (year, govid) -- an IG-only row for a suppressed
+  # category becomes indistinguishable from one sharing a key with an
+  # unrelated category's ordinary Direct row. `has_direct` would then read
+  # TRUE whenever the government has ANY direct spending at all, and the
+  # detector could never fire. Rather than run it and report a false FALSE,
+  # skip it and record NA -- the provenance must stop making a claim it
+  # cannot support (finding 1, all-categories review).
+  suppression_unavailable <- all_categories &&
+    identical(expenditure_concept, "total")
+  direct_suppressed_info <- if (suppression_unavailable) {
+    list(flag = rep(NA, nrow(result)), notes = rep(NA_character_, nrow(result)))
+  } else if (identical(expenditure_concept, "total")) {
     .detect_direct_suppressed(con, result, subtype_col)
   } else {
     list(flag = rep(FALSE, nrow(result)), notes = rep(NA_character_, nrow(result)))
   }
   direct_suppressed <- direct_suppressed_info$flag
-  direct_suppressed_flag <- isTRUE(any(direct_suppressed))
+  direct_suppressed_flag <- if (suppression_unavailable) {
+    NA
+  } else {
+    isTRUE(any(direct_suppressed))
+  }
 
   result$notes <- .notes_column(result, direct_suppressed_info$notes)
 
@@ -422,9 +558,20 @@ cog_spending <- function(govid, years, category = NULL,
   # leg is suppressed for at least one requested (year, category), append an
   # explicit warning rather than let the base note's "Total = Direct + IG"
   # framing stand unqualified for rows where that arithmetic didn't happen.
+  # When suppression detection itself is unavailable (all-categories mode),
+  # say so instead of silently reusing the unqualified base note.
   expenditure_concept_note_for_prov <- if (identical(expenditure_concept, "total")) {
     base_note <- "Total = Direct + intergovernmental (M to local govts + L to state govts). Legacy-era IG is assembled from aggregate-flagged rows, which are year-disjoint from their modern leaf components; the L-- family total is excluded."
-    if (direct_suppressed_flag) {
+    if (suppression_unavailable) {
+      paste0(
+        base_note,
+        " NOTE: direct-leg-suppression detection is unavailable when ",
+        "`category = \"All Categories\"` -- it keys on per-category rows, ",
+        "which this mode collapses. `expenditure_concept_direct_suppressed` ",
+        "is NA here rather than a possibly-false FALSE; query an explicit ",
+        "`category` (or `category = NULL`) to get a real answer."
+      )
+    } else if (isTRUE(direct_suppressed_flag)) {
       paste0(
         base_note,
         " NOTE: for at least one requested (year, category) the Direct leg ",
@@ -466,15 +613,32 @@ cog_spending <- function(govid, years, category = NULL,
   prov$scope$govids_missing <- scope$missing
   attr(result, "provenance") <- prov
   attr(result, ".popyear_range") <- NULL
+  # Attached here, after every downstream transform (per_capita/real-dollar
+  # joins, notes, subtype filtering), the same way provenance is -- an
+  # attribute set before those runs is not guaranteed to survive them.
+  if (!is.null(limit)) attr(result, "total_rows") <- total_rows
 
   if (length(suggestions) > 0L) .inform_suggestions(suggestions)
 
   result
 }
 
+#' Shared input validation for the money/holdings verbs.
+#'
+#' `allow_all_categories` gates the reserved pseudo-category
+#' `.ALL_CATEGORIES` ("All Categories"). It is meaningful only where a
+#' concept's subtype scope defines what "all" sums over --
+#' `cog_spending()`/`cog_revenue()`, via `.verb_spendrev()`, pass `TRUE`.
+#' `cog_balances()` leaves it at the `FALSE` default: holdings are a stock
+#' with no concept vocabulary to sum across (see R/balances.R), and before
+#' this guard existed `cog_balances(category = "All Categories")` silently
+#' matched zero crosswalk rows and returned an empty result with no error
+#' (finding 3, all-categories review). This validator is shared specifically
+#' so the three verbs cannot drift apart on this again.
 #' @noRd
 .validate_verb_inputs <- function(govid, years, category,
-                                  per_capita, adjust_to_year, recipe = NULL) {
+                                  per_capita, adjust_to_year, recipe = NULL,
+                                  allow_all_categories = FALSE) {
   if (!is.character(govid) || length(govid) == 0L) {
     cli::cli_abort("`govid` must be a non-empty character vector.")
   }
@@ -484,6 +648,14 @@ cog_spending <- function(govid, years, category = NULL,
   if (!is.null(category) && !is.character(category)) {
     cli::cli_abort("`category` must be character or NULL.")
   }
+  if (!allow_all_categories && !is.null(category) &&
+      .ALL_CATEGORIES %in% category) {
+    cli::cli_abort(c(
+      "{.val {(.ALL_CATEGORIES)}} is not supported here.",
+      i = "It sums a spending or revenue concept's subtype scope; this verb has no concept vocabulary to sum across.",
+      i = "Use {.fn cog_spending} or {.fn cog_revenue} for an all-categories total."
+    ), class = "uscogdata_all_categories_unsupported")
+  }
   if (!is.logical(per_capita) || length(per_capita) != 1L) {
     cli::cli_abort("`per_capita` must be a length-1 logical.")
   }
@@ -512,6 +684,18 @@ cog_spending <- function(govid, years, category = NULL,
   if (identical(basis, "harmonized")) paste0(view_base, "_harmonized") else view_base
 }
 
+#' The `*_long`/`*_long_harmonized` view behind an annotated view base --
+#' `"spending_annotated"` -> `"spending_long_harmonized"`. `.build_suggestions()`
+#' anti-joins the LONG view rather than the annotated one: they have identical
+#' row membership (the annotated views are the long views plus LEFT JOINs, see
+#' inst/sql/42-spending_annotated_harmonized.sql), but the long view is the
+#' one that actually owns the `NOT is_aggregate` + crosswalk-membership rule
+#' the suppression test is asking about.
+#' @noRd
+.select_long_view <- function(view_base, basis) {
+  .select_view(sub("_annotated$", "_long", view_base), basis)
+}
+
 #' @noRd
 .select_ig_view <- function(basis) {
   if (identical(basis, "harmonized")) "ig_annotated_harmonized" else "ig_annotated"
@@ -557,10 +741,16 @@ cog_spending <- function(govid, years, category = NULL,
 
 #' @noRd
 .build_verb_sql <- function(view, subtype_col, govid, years, category,
-                            ig_view = NULL, subtype_scope = NULL) {
+                            ig_view = NULL, subtype_scope = NULL,
+                            all_categories = FALSE, limit = NULL, offset = NULL) {
   govid_lit <- .sql_lit_chr(govid)
   years_lit <- paste(as.integer(years), collapse = ",")
-  category_pred <- if (is.null(category)) {
+  # In all-categories mode there is no category filter: the sum is defined by
+  # the concept's SUBTYPE allowlist (subtype_pred below), which is the real
+  # concept boundary. Filtering by category as well would be a no-op at best
+  # and, if the crosswalk ever gained an uncategorized code, a silent
+  # under-count of the very total this mode exists to guarantee.
+  category_pred <- if (all_categories || is.null(category)) {
     ""
   } else {
     sprintf("AND category IN (%s)", .sql_lit_chr(category))
@@ -599,13 +789,26 @@ cog_spending <- function(govid, years, category = NULL,
   # though its dollars came entirely from an aggregate row, silently
   # suppressing the "Aggregate fallback applied" note on exactly the rows
   # this feature exists to surface.
-  sprintf(
+
+  # Collapse the category dimension. subtype is deliberately KEPT: it is what
+  # lets a caller filter the result to `spend_subtype == "operations"` and
+  # get an operating-expenditure total, the measure a fiscal comparison
+  # actually wants. (There is no `subtype` argument -- this is a post-hoc
+  # filter on the returned column, not a query parameter.)
+  category_select <- if (all_categories) {
+    sprintf("%s AS category", .sql_lit_chr(.ALL_CATEGORIES))
+  } else {
+    "category"
+  }
+  category_group <- if (all_categories) "" else ", category"
+
+  base_sql <- sprintf(
     "SELECT
        year,
        canonical_govid,
        COALESCE(xwalk_gov_name, gov_name) AS gov_name,
        %1$s,
-       category,
+       %7$s,
        SUM(amt) * 1000.0 AS amt_nominal,
        string_agg(DISTINCT item_code, ',' ORDER BY item_code) AS codes_included,
        bool_or(is_aggregate) AS aggregate_fallback
@@ -614,10 +817,32 @@ cog_spending <- function(govid, years, category = NULL,
        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_pred
+     GROUP BY year, canonical_govid, gov_name, xwalk_gov_name, %1$s%8$s
+     ORDER BY year, canonical_govid, %1$s%8$s",
+    subtype_col, source_expr, govid_lit, years_lit, category_pred, subtype_pred,
+    category_select, category_group
   )
+
+  # limit/offset push the page into the query itself instead of pulling every
+  # matching row across the network only to slice and discard most of it
+  # afterward (the pattern behind the 2026-08-06 production incident: a
+  # 193,105-row/194-page sweep re-ran the full query and re-listified every
+  # row on EVERY page). COUNT(*) OVER() rides along as an ordinary column so
+  # the caller gets the true total from this same scan -- see the call site
+  # in .verb_spendrev(), which reads it off row 1 and strips it back out.
+  # The outer SELECT * wrapping (rather than appending LIMIT/OFFSET directly
+  # to base_sql) is what makes COUNT(*) OVER() see the post-GROUP-BY row
+  # count, not the pre-aggregation one.
+  if (is.null(limit)) {
+    base_sql
+  } else {
+    sprintf(
+      "SELECT *, COUNT(*) OVER() AS pagination_total_rows
+       FROM (%s) AS _paged
+       LIMIT %d OFFSET %d",
+      base_sql, limit, offset
+    )
+  }
 }
 
 #' @noRd

R/suggestions.R

@@ -1,8 +1,16 @@
 # R/suggestions.R
-# Recipe-component-driven signposting: when a basis = "harmonized" query for
-# a category comes back with a coverage gap in some requested years (the
-# result has no rows at all in that year) that a harmonization recipe would
-# actually fill for this government, surface that recipe as a suggestion.
+# Recipe-component-driven signposting. When a basis = "harmonized" query for
+# a category comes back incomplete in some requested year -- and a
+# harmonization recipe would actually fill it for this government -- surface
+# that recipe as a suggestion. "Incomplete" has two forms, and a recipe
+# qualifies on either:
+#   1. empty_year          -- the result has no rows at all in that year.
+#   2. suppressed_component -- the result HAS rows, but a component code
+#      carries dollars the verb's own long view structurally excludes
+#      (aggregate-published, or absent from summary_categories). This is
+#      uscogdata#9: Public Welfare kept returning E74/E79 rows while dropping
+#      aggregate-only E67/E68, so form 1 never fired and the caller got a
+#      number a third too low with no signpost at all.
 #
 # This is deliberately keyed off the recipe catalog's component codes, not
 # off harmonization_map rows: no live map row carries a non-blank
@@ -48,11 +56,39 @@
 #'   "D")` for `cog_revenue()` -- see `.verb_spendrev()`). Passed through to
 #'   `.attach_ig_counterparts()` to keep the intergovernmental-counterpart
 #'   lookup scoped to the calling verb's own flow family.
+#' @param long_view Name of the verb's own long view (from
+#'   `.select_long_view()`), passed through to `.suppressed_components()` to
+#'   measure the second qualifying path (uscogdata#9).
+#' @param all_categories `TRUE` when the caller's `category` is the reserved
+#'   pseudo-category (`.ALL_CATEGORIES`). Defaults to `FALSE` so no other
+#'   caller's behaviour changes. When `TRUE`, the candidate-recipe sub-select
+#'   is scoped by `subtype_col`/`subtype_scope` instead of by `category` --
+#'   symmetric with `.build_verb_sql()`'s own all-categories branch (see
+#'   R/spending.R): the concept's subtype allowlist is the real scope
+#'   boundary, not any literal category value, and
+#'   `.ALL_CATEGORIES` ("All Categories") is never itself a row in
+#'   `summary_categories.category`, so leaving the category-keyed sub-select
+#'   in place here always returned zero candidates and silently disabled
+#'   signposting in all-categories mode (final whole-branch review, finding
+#'   6).
+#' @param subtype_col Name of the `summary_categories` subtype column to
+#'   scope by when `all_categories = TRUE` (`"spend_subtype"` or
+#'   `"revenue_subtype"` -- the same value `.build_verb_sql()` already
+#'   receives as its own `subtype_col`). Ignored when `all_categories =
+#'   FALSE`. `NULL` by default.
+#' @param subtype_scope Character vector of subtype values to scope by when
+#'   `all_categories = TRUE` (the same value `.build_verb_sql()` already
+#'   receives as its own `subtype_scope` -- the concept's subtype allowlist,
+#'   e.g. `.expenditure_concept_subtypes(expenditure_concept)`). Ignored when
+#'   `all_categories = FALSE`. `NULL` by default.
 #' @return List of `list(recipe_id, label, available_years, hint,
-#'   ig_recipe_id)`, possibly empty.
+#'   ig_recipe_id, trigger, suppressed_amount, suppressed_years,
+#'   suppressed_codes)`, possibly empty.
 #' @noRd
 .build_suggestions <- function(con, govid, years, category, result, basis,
-                                flow_prefixes) {
+                                flow_prefixes, long_view,
+                                all_categories = FALSE,
+                                subtype_col = NULL, subtype_scope = NULL) {
   if (!identical(basis, "harmonized") || is.null(category)) return(list())
 
   # Exclude any recipe that is ITSELF an intergovernmental (M/L) recipe --
@@ -67,16 +103,35 @@
   # flow-prefix gate below/in `.attach_ig_counterparts()`: an M/L recipe
   # should never be suggested as a coverage-gap filler for EITHER verb, not
   # just kept from being named as the *counterpart* of another suggestion.
+  #
+  # The inner sub-select is the concept boundary (finding 6, final
+  # whole-branch review): in all-categories mode it is scoped by
+  # `subtype_col`/`subtype_scope` -- the same allowlist `.build_verb_sql()`
+  # applies as a WHERE predicate to make the summed result a *concept*, not
+  # by `category` (`.ALL_CATEGORIES` is never a row in
+  # `summary_categories.category`, so a category-keyed sub-select always
+  # came back empty here). The M/L exclusion below is unchanged either way.
+  candidate_scope_sql <- if (isTRUE(all_categories)) {
+    sprintf(
+      "SELECT DISTINCT item_code FROM summary_categories WHERE %s IN (%s)",
+      subtype_col, .sql_lit_chr(subtype_scope)
+    )
+  } else {
+    sprintf(
+      "SELECT DISTINCT item_code FROM summary_categories WHERE category IN (%s)",
+      .sql_lit_chr(category)
+    )
+  }
   candidates <- DBI::dbGetQuery(con, sprintf(
     "SELECT DISTINCT recipe_id FROM harmonization_recipes
      WHERE component_code IN (
-       SELECT DISTINCT item_code FROM summary_categories WHERE category IN (%s)
+       %s
      )
      AND recipe_id NOT IN (
        SELECT DISTINCT recipe_id FROM harmonization_recipes
        WHERE LEFT(component_code, 1) IN ('M', 'L')
      )",
-    .sql_lit_chr(category)
+    candidate_scope_sql
   ))$recipe_id
   if (length(candidates) == 0L) return(list())
 
@@ -86,7 +141,28 @@
     unique(as.integer(result$year))
   }
   gap_years <- setdiff(as.integer(years), result_years)
-  if (length(gap_years) == 0L) return(list())
+
+  # Path 2 (uscogdata#9): component dollars this government holds that the
+  # verb's own view structurally excludes. Measured across ALL requested
+  # years, not just gap years -- the whole point is that a year with rows can
+  # still be missing dollars. Scoped to the calling verb's own flow_prefixes
+  # (I1) -- see `.suppressed_components()`'s own roxygen for why.
+  #
+  # This runs unconditionally whenever there are candidates -- an earlier
+  # revision of this fix wave tried a free, in-memory pre-check
+  # (`.needs_suppression_query()`) to skip the round trip on an already-
+  # covered path, but a scoped re-review measured it against the fixture and
+  # found it didn't pay for itself (it skipped ~3% of healthy calls, ~0% of
+  # the multi-govid batch shape it was meant to help, at a net cost increase
+  # once its own always-run metadata query was counted) while adding an
+  # untested exactness invariant -- that `result$codes_included` and this
+  # anti-join share the harmonized `item_code` space -- whose silent
+  # violation would kill signposting, the exact failure class uscogdata#9
+  # exists to prevent. Owner's call: keep this simple; a batch-aware
+  # optimization, if one is worth building, is a separate issue.
+  supp <- .suppressed_components(con, candidates, govid, years, long_view, flow_prefixes)
+
+  if (length(gap_years) == 0L && nrow(supp) == 0L) return(list())
 
   meta <- tibble::as_tibble(DBI::dbGetQuery(con, sprintf(
     "SELECT recipe_id, any_value(label) AS label,
@@ -97,11 +173,12 @@
     .sql_lit_chr(candidates)
   )))
 
-  # Which (recipe_id, year) pairs the recipe's own generic join actually
-  # covers for this government, restricted to the gap years -- the same
-  # join .run_recipe() uses (component year_min/year_max + gov_type_scope,
-  # no is_aggregate filter), just checking existence instead of summing.
-  covered <- DBI::dbGetQuery(con, sprintf(
+  # Path 1 (unchanged): (recipe, year) pairs the recipe's own generic join
+  # covers for this government, restricted to the gap years.
+  covered <- if (length(gap_years) == 0L) {
+    data.frame(recipe_id = character(0), year = integer(0))
+  } else {
+    DBI::dbGetQuery(con, sprintf(
       "SELECT DISTINCT r.recipe_id, l.year
        FROM long l
        JOIN harmonization_recipes r
@@ -116,16 +193,35 @@
       .sql_lit_chr(candidates), .sql_lit_chr(govid),
       paste(gap_years, collapse = ",")
     ))
+  }
 
   suggestions <- list()
   for (rid in candidates) {
-    if (!rid %in% covered$recipe_id) next
+    empty_hit <- rid %in% covered$recipe_id
+    s_rows <- supp[supp$recipe_id == rid, , drop = FALSE]
+    supp_hit <- nrow(s_rows) > 0L
+    if (!empty_hit && !supp_hit) next
     m <- meta[meta$recipe_id == rid, ]
     suggestions[[length(suggestions) + 1L]] <- list(
       recipe_id = rid,
       label = m$label[[1]],
       available_years = c(as.integer(m$year_min), as.integer(m$year_max)),
-      hint = sprintf("re-run with recipe = '%s'", rid)
+      hint = sprintf("re-run with recipe = '%s'", rid),
+      # An empty year is the stronger claim -- the category returned nothing
+      # at all -- so it wins when both paths qualify. The suppressed_* fields
+      # are still populated, so an empty_year fire also reports its dollars.
+      trigger = if (empty_hit) "empty_year" else "suppressed_component",
+      suppressed_amount = if (supp_hit) sum(s_rows$suppressed_amount) else 0,
+      suppressed_years = if (supp_hit) {
+        sort(unique(as.integer(s_rows$year)))
+      } else {
+        integer(0)
+      },
+      suppressed_codes = if (supp_hit) {
+        sort(unique(unlist(strsplit(s_rows$suppressed_codes, ",", fixed = TRUE))))
+      } else {
+        character(0)
+      }
     )
   }
   .attach_ig_counterparts(con, suggestions, flow_prefixes)
@@ -232,12 +328,25 @@
 #' expressions. When a suggestion has an `ig_recipe_id`, one indented
 #' continuation line is appended naming the intergovernmental counterpart
 #' recipe (embedded `\n` renders as a hanging-indent continuation of the
-#' same bullet under cli, not a new bullet).
+#' same bullet under cli, not a new bullet). Same treatment for
+#' `suppressed_amount` (uscogdata#9): only present when dollars were
+#' actually measured as excluded (an `empty_year` fire can carry them too --
+#' see `.build_suggestions()` -- so this keys off the amount, not `trigger`).
 #' @noRd
 .inform_suggestions <- function(suggestions) {
   bullets <- vapply(suggestions, function(s) {
     bullet <- sprintf("%s (%d-%d): %s", s$recipe_id,
             s$available_years[1], s$available_years[2], s$hint)
+    # Only present when dollars were actually measured as excluded. An
+    # empty_year fire can carry them too -- the year had no rows AND the
+    # component was suppressed -- which is strictly more informative.
+    if (isTRUE(s$suppressed_amount > 0)) {
+      bullet <- paste0(bullet, sprintf(
+        "\n  $%s excluded from %s (%s), published as an aggregate or outside the crosswalk",
+        formatC(s$suppressed_amount, format = "f", digits = 0, big.mark = ","),
+        paste0("FY", s$suppressed_years, collapse = ", "),
+        paste(s$suppressed_codes, collapse = ", ")))
+    }
     if (!is.null(s$ig_recipe_id)) {
       bullet <- paste0(bullet, sprintf(
         "\n  intergovernmental counterpart: recipe = '%s'", s$ig_recipe_id))
@@ -245,7 +354,7 @@
     bullet
   }, character(1))
   cli::cli_inform(c(
-    i = "Coverage gap detected for the requested years; a harmonization recipe may fill it:",
+    i = "Incomplete coverage for the requested years; a harmonization recipe may fill it:",
     stats::setNames(bullets, rep("*", length(bullets)))
   ))
 }

R/suppression.R

@@ -0,0 +1,115 @@
+# R/suppression.R
+# Split out of R/suggestions.R (2026-08-05) to keep files under the project's
+# 400-line limit. Owns the second qualifying path for coverage signposting
+# (uscogdata#9): measuring, per government, the component dollars the
+# calling verb's own long view structurally excludes (aggregate-published,
+# or absent from summary_categories). See R/suggestions.R for the
+# orchestrator (`.build_suggestions()`) that calls this and the full
+# uscogdata#9 background.
+
+#' Measure, per (recipe, year), the component dollars this government holds
+#' that the calling verb's own long view structurally excludes.
+#'
+#' This is the second qualifying path for a suggestion (uscogdata#9). The
+#' first -- row absence -- only fires when a category returns NOTHING in a
+#' requested year, which is how Corrections behaves in the wide era. Public
+#' Welfare is the failure mode it misses: E74/E75/E77/E79 still return rows,
+#' so there is no absence to detect, while E67/E68 (aggregate-flagged 1967-
+#' 2011, and absent from `summary_categories` entirely) are dropped. The
+#' caller gets a plausible number a third too low, silently.
+#'
+#' "Structurally excluded" is decided by anti-joining the verb's REAL long
+#' view rather than restating its WHERE clause, so this stays correct if
+#' `spending_long_harmonized` / `revenue_long_harmonized` ever change. That
+#' anti-join is keyed on `item_code`, which is sound only because
+#' harmonization never renames a recipe component -- asserted by the "no
+#' recipe component is ever renamed by harmonization" test in
+#' tests/testthat/test-recipes.R.
+#'
+#' Note what this deliberately does NOT count as suppressed: a component
+#' excluded from the RESULT for scoping reasons -- because it belongs to a
+#' different `category`, or because `expenditure_concept` narrowed the
+#' subtypes -- is still present in the view, so it never fires. Suggesting a
+#' recipe is a coverage fix, not a category redefinition.
+#'
+#' `flow_prefixes` (uscogdata#9 review, finding I1) restricts the measured
+#' components to the CALLING VERB's own flow family (`c("E","F","G")` for
+#' spending, `c("T","A","U","B","C","D")` for revenue). Without this, a
+#' candidate recipe belonging to the OTHER flow family is always absent from
+#' this verb's view (by construction -- `cog_revenue()`'s view never carries
+#' an E-coded row) and so was always reported as "suppressed", fabricating a
+#' dollar claim across flow families (`cog_revenue(category = "Corrections")`
+#' claimed $3.63B excluded that `cog_spending()` reports and fully accounts
+#' for). Filtering on `LEFT(r.component_code, 1)` also drops M/L-prefixed
+#' components from measurement under `cog_spending()` (`flow_prefixes` never
+#' includes "M"/"L") -- harmless today, because a recipe's own M/L components
+#' (e.g. `corrections_ig_local_combined`'s M04/M05) are present in the view
+#' in every year they exist and so never fired as suppressed anyway, but
+#' worth recording since this filter is now the thing relied on to prevent
+#' it.
+#'
+#' @param con Active DuckDB connection.
+#' @param candidates Character vector of recipe ids to measure.
+#' @param govid Character vector of canonical_govid values.
+#' @param years Integer vector of requested years.
+#' @param long_view Name of the verb's long view, from `.select_long_view()`.
+#' @param flow_prefixes The calling verb's own flow-type prefixes (see
+#'   `.build_suggestions()`). Only recipe components whose first character is
+#'   in this set are measured.
+#' @return Tibble of `recipe_id`, `year`, `suppressed_amount` (full US
+#'   dollars), `suppressed_codes` (comma-joined, sorted). Zero rows when
+#'   nothing is suppressed.
+#' @noRd
+.suppressed_components <- function(con, candidates, govid, years, long_view,
+                                    flow_prefixes) {
+  empty <- tibble::tibble(
+    recipe_id = character(0), year = numeric(0),
+    suppressed_amount = numeric(0), suppressed_codes = character(0)
+  )
+  if (length(candidates) == 0L) return(empty)
+
+  # long_view is interpolated as a SQL IDENTIFIER, not a literal, so it can
+  # never be quoted safely. It is always internally derived from a fixed
+  # view_base, so an off-allowlist value is a programming error, not input.
+  if (!long_view %in% c("spending_long", "spending_long_harmonized",
+                        "revenue_long", "revenue_long_harmonized")) {
+    cli::cli_abort(
+      "Internal error: unexpected `long_view` {.val {long_view}}.",
+      class = "uscogdata_internal_error"
+    )
+  }
+
+  sql <- sprintf(
+    "SELECT r.recipe_id,
+            l.year,
+            SUM(l.amt) * 1000.0 AS suppressed_amount,
+            string_agg(DISTINCT l.item_code, ',' ORDER BY l.item_code)
+              AS suppressed_codes
+     FROM long l
+     JOIN harmonization_recipes r
+       ON l.item_code = r.component_code
+      AND l.year BETWEEN r.year_min AND r.year_max
+      AND (r.gov_type_scope = 'all'
+           OR (r.gov_type_scope = 'state' AND l.type = 0)
+           OR (r.gov_type_scope = 'local' AND l.type BETWEEN 1 AND 3))
+     WHERE r.recipe_id IN (%1$s)
+       AND l.canonical_govid IN (%2$s)
+       AND l.year IN (%3$s)
+       AND l.amt <> 0
+       AND LEFT(r.component_code, 1) IN (%5$s)
+       AND NOT EXISTS (
+         SELECT 1 FROM %4$s v
+         WHERE v.canonical_govid = l.canonical_govid
+           AND v.year = l.year
+           AND v.item_code = l.item_code
+           AND v.year IN (%3$s)              -- restated: enables partition pruning (I3a)
+           AND v.canonical_govid IN (%2$s)   -- restated: pushes the govid filter (I3a)
+       )
+     GROUP BY 1, 2
+     ORDER BY 1, 2",
+    .sql_lit_chr(candidates), .sql_lit_chr(govid),
+    paste(as.integer(years), collapse = ","), long_view,
+    .sql_lit_chr(flow_prefixes)
+  )
+  tibble::as_tibble(DBI::dbGetQuery(con, sql))
+}

inst/schemas/provenance-v1.json

@@ -22,8 +22,8 @@
       "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 = 'primary' or 'direct'. See the affected rows' `notes` for the recovering recipe, if any."
+      "type": ["boolean", "null"],
+      "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'. null (NA) when expenditure_concept = 'total' AND category = 'All Categories': the detector keys on per-category rows, which that mode collapses, so suppression cannot be computed -- see `expenditure_concept_note`. See the affected rows' `notes` for the recovering recipe, if any."
     },
     "revenue_concept": {
       "type": "string",
@@ -33,7 +33,48 @@
     },
     "harmonization":  { "type": "object" },
     "recipe":         { "type": ["object", "null"] },
-    "suggestions":    { "type": "array" },
+    "suggestions": {
+      "type": "array",
+      "description": "Harmonization recipes that would fill incomplete coverage in the requested years for this government. Empty on a healthy query, on an un-scoped (category = NULL) query, on basis = 'raw', and on a recipe = query (which resolves its own coverage).",
+      "items": {
+        "type": "object",
+        "required": ["recipe_id", "label", "available_years", "hint", "ig_recipe_id",
+                     "trigger", "suppressed_amount", "suppressed_years", "suppressed_codes"],
+        "properties": {
+          "recipe_id": { "type": "string" },
+          "label": { "type": "string" },
+          "available_years": {
+            "type": "array",
+            "items": { "type": "integer" },
+            "description": "[year_min, year_max] of the recipe's component coverage."
+          },
+          "hint": { "type": "string" },
+          "ig_recipe_id": {
+            "type": ["string", "null"],
+            "description": "The intergovernmental (M/L) counterpart recipe covering the same function suffixes, or null. Never set for revenue recipes."
+          },
+          "trigger": {
+            "type": "string",
+            "enum": ["empty_year", "suppressed_component"],
+            "description": "Why this fired. 'empty_year': the result has no rows at all in a requested year. 'suppressed_component': the result HAS rows, but a component code carries dollars this government reports in the requested years that the verb's underlying long view structurally excludes -- aggregate-published, carrying no harmonized code, or absent from summary_categories. This is NOT the same thing as 'excluded from the result': a component present in the view under a different category (a scoping choice, e.g. a different `category` or a narrower `expenditure_concept`) contributes 0 and never fires. 'empty_year' wins when both apply, being the stronger claim; the suppressed_* fields are populated either way, using the same underlying-view measurement, and can be 0 even on an 'empty_year' fire."
+          },
+          "suppressed_amount": {
+            "type": "number",
+            "description": "Full US dollars this government reports, in the recipe's component codes, in the requested years, that the verb's underlying long view structurally excludes (aggregate-published, carrying no harmonized code, or absent from summary_categories) -- summed across those years. This is NOT the same quantity as 'what the result excludes': a component present in the view under a different category or a narrower `expenditure_concept` is scoped out on purpose, counts as 0 here, and is not suppression. 0 does not always mean full coverage -- see 'trigger' and 'empty_year'. May be negative where Census publishes a negative `amt` for the excluded rows."
+          },
+          "suppressed_years": {
+            "type": "array",
+            "items": { "type": "integer" },
+            "description": "The requested years contributing to suppressed_amount."
+          },
+          "suppressed_codes": {
+            "type": "array",
+            "items": { "type": "string" },
+            "description": "The excluded component item codes, sorted."
+          }
+        }
+      }
+    },
     "scope":          { "type": "object" },
     "codes_summed":   { "type": "object" },
     "aggregate_fallback": { "type": ["object", "null"] },

man/cog_balances.Rd

@@ -28,7 +28,12 @@ argument: for holdings, `category` is a strict coarsening of
 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.}
+one `dplyr::filter()` away. The reserved pseudo-category
+`"All Categories"` (see [cog_spending()]) is **not** supported here and
+errors with class `uscogdata_all_categories_unsupported`: it sums a
+concept's subtype scope, and holdings are a stock with no concept
+vocabulary to sum across. Omit `category` to get every category broken
+out instead.}
 
 \item{per_capita}{Divide holdings by population. Note this is a **stock per
 resident** (reserves per person), which is *not* comparable to

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"`).}
@@ -16,13 +16,24 @@ rows), `"spending"`, or `"revenue"`.}
 \value{
 Tibble with columns `category`, `category_type`, `subtype`,
   `n_codes`, `item_codes` (comma-separated, alphabetical). Sorted by
-  `category_type`, `category`, `subtype`.
+  `category_type`, `category`, `subtype`. Includes one row per flow for the
+  reserved pseudo-category `"All Categories"`, which carries `NA` for
+  `subtype`, `n_codes` and `item_codes` because it is a query mode rather
+  than a crosswalk entry — see [cog_spending()]'s `category` argument.
 }
 \description{
 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_geographic_rollup.Rd

@@ -20,7 +20,11 @@ cog_geographic_rollup(
 `canonical_govid` values. At least one layer required.}
 
 \item{category}{Single category name or character vector (passed through
-to [cog_spending()]).}
+to [cog_spending()]), or the reserved `"All Categories"` for one summed
+row per `(year, canonical_govid, subtype)` covering every category in the
+concept's scope. `"All Categories"` is the efficient way to build a
+geographic total: without it a caller must issue one rollup per category
+and sum the results themselves.}
 
 \item{years}{Integer vector of years.}
 
@@ -80,3 +84,23 @@ the result. The dropped govids are recorded in
 (gov type 4) and school districts (gov type 5) from per-capita rollups
 by design — see `vignette('population-denominators')`.
 }
+\section{Reading `coverage`}{
+
+`provenance$coverage` reports `n_units_reporting` against
+`n_units_expected` per year. **`n_units_reporting` is category-conditional:
+it counts governments with rows for the category you asked for, not
+governments collected that year.** A government that was surveyed and
+genuinely spends nothing in that category is indistinguishable here from one
+that was never surveyed.
+
+The ratio is therefore **not a response rate** and must not be used as one.
+In FY2022 — a complete census year — Georgia reports 393 of 567 cities for
+`category = "Police"`; the 174-city gap is overwhelmingly cities that
+contract policing to the county sheriff, not non-response.
+
+The comparison that *is* valid is the same category across a census year
+(ending in 2 or 7) and a sample year, where the real-zero component is
+roughly constant and the difference reflects the survey cycle. `is_census_year`
+marks which is which.
+}
+

man/cog_peer_compare.Rd

@@ -107,3 +107,23 @@ call. Those summary rows are quantiles **within each category**, not
 quantiles of each peer's total — see the `@return` section before summing
 them.
 }
+\section{Reading `coverage`}{
+
+`provenance$coverage` reports `n_units_reporting` against
+`n_units_expected` per year. **`n_units_reporting` is category-conditional:
+it counts cohort members with rows for the category you asked for, not
+cohort members collected that year.** A government that was surveyed and
+genuinely spends nothing in that category is indistinguishable here from one
+that was never surveyed.
+
+The ratio is therefore **not a response rate** and must not be used as one.
+In FY2022 — a complete census year — Georgia reports 393 of 567 cities for
+`category = "Police"`; the 174-city gap is overwhelmingly cities that
+contract policing to the county sheriff, not non-response.
+
+The comparison that *is* valid is the same category across a census year
+(ending in 2 or 7) and a sample year, where the real-zero component is
+roughly constant and the difference reflects the survey cycle. `is_census_year`
+marks which is which.
+}
+

man/cog_revenue.Rd

@@ -13,7 +13,9 @@ cog_revenue(
   basis = c("harmonized", "raw"),
   recipe = NULL,
   revenue_concept = c("general", "total"),
-  complete = FALSE
+  complete = FALSE,
+  limit = NULL,
+  offset = NULL
 )
 }
 \arguments{
@@ -22,7 +24,16 @@ cog_revenue(
 \item{years}{Integer vector of years.}
 
 \item{category}{Character vector of category names (from
-`summary_categories.category`), or `NULL` for all categories.}
+`summary_categories.category`), or `NULL` for all categories broken out
+one row each. The reserved value `"All Categories"` instead returns a
+single summed row per `(year, canonical_govid, subtype)`, covering every
+category inside the requested concept's subtype scope. It cannot be
+combined with other category names, and it is not the same thing as
+`revenue_concept = "total"`: the concept chooses which subtypes are in
+scope, `"All Categories"` chooses whether rows inside that scope are
+broken out or summed. Because the result keeps one row per
+`revenue_subtype`, filtering the returned frame to
+`revenue_subtype == "own_source"` gives an own-source revenue total.}
 
 \item{per_capita}{If `TRUE`, adds `amt_per_capita_nominal` (and
 `amt_per_capita_real` when `adjust_to_year` is set) using the per-year
@@ -106,6 +117,15 @@ possibly-misleading `"harmonized"`/`"raw"` value.}
   `recipe` or with `expenditure_concept = "total"` (class
   `uscogdata_complete_unsupported`) — neither draws its cells from
   `code_set`.}
+
+\item{limit}{Maximum number of result rows to return, pushed into the SQL
+query itself (`LIMIT`/`OFFSET`) rather than applied after the full
+result is materialized. `NULL` (the default) returns every matching row,
+exactly as before this parameter existed. Mutually exclusive with
+`recipe` and with `complete = TRUE` -- see `offset` and `total_rows`.}
+
+\item{offset}{Rows to skip before `limit` starts counting (0-based).
+Ignored if `limit` is `NULL`; defaults to `0L` when `limit` is set.}
 }
 \value{
 Tibble with columns `year`, `canonical_govid`, `gov_name`,

man/cog_spending.Rd

@@ -13,7 +13,9 @@ cog_spending(
   basis = c("harmonized", "raw"),
   recipe = NULL,
   expenditure_concept = c("primary", "direct", "total"),
-  complete = FALSE
+  complete = FALSE,
+  limit = NULL,
+  offset = NULL
 )
 }
 \arguments{
@@ -22,7 +24,16 @@ cog_spending(
 \item{years}{Integer vector of years.}
 
 \item{category}{Character vector of category names (from
-`summary_categories.category`), or `NULL` for all categories.}
+`summary_categories.category`), or `NULL` for all categories broken out
+one row each. The reserved value `"All Categories"` instead returns a
+single summed row per `(year, canonical_govid, subtype)`, covering every
+category inside the requested concept's subtype scope. It cannot be
+combined with other category names, and it is not the same thing as
+`expenditure_concept = "total"`: the concept chooses which subtypes are in
+scope, `"All Categories"` chooses whether rows inside that scope are
+broken out or summed. Because the result keeps one row per
+`spend_subtype`, filtering the returned frame to
+`spend_subtype == "operations"` gives an operating-expenditure total.}
 
 \item{per_capita}{If `TRUE`, adds `amt_per_capita_nominal` (and
 `amt_per_capita_real` when `adjust_to_year` is set) using the per-year
@@ -97,7 +108,12 @@ possibly-misleading `"harmonized"`/`"raw"` value.}
   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.}
+  IG. When `category = "All Categories"` is combined with
+  `expenditure_concept = "total"`, this detection cannot run (it keys on
+  per-category rows, which all-categories mode collapses to one literal
+  value), so `expenditure_concept_direct_suppressed` is `NA` rather than a
+  possibly-false `FALSE`; query an explicit `category` to get a real
+  answer.}
 
 \item{complete}{If `TRUE`, fill the requested grid so that a cell the
   corpus does not carry still appears, labelled with **why** it is
@@ -121,6 +137,15 @@ possibly-misleading `"harmonized"`/`"raw"` value.}
   `recipe` or with `expenditure_concept = "total"` (class
   `uscogdata_complete_unsupported`) — neither draws its cells from
   `code_set`.}
+
+\item{limit}{Maximum number of result rows to return, pushed into the SQL
+query itself (`LIMIT`/`OFFSET`) rather than applied after the full
+result is materialized. `NULL` (the default) returns every matching row,
+exactly as before this parameter existed. Mutually exclusive with
+`recipe` and with `complete = TRUE` -- see `offset` and `total_rows`.}
+
+\item{offset}{Rows to skip before `limit` starts counting (0-based).
+Ignored if `limit` is `NULL`; defaults to `0L` when `limit` is set.}
 }
 \value{
 Tibble with columns `year`, `canonical_govid`, `gov_name`,
@@ -130,7 +155,11 @@ Tibble with columns `year`, `canonical_govid`, `gov_name`,
   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.
+  per-year `absence_means` rule that was applied. When `limit` is set,
+  also carries a `total_rows` attribute: the full unpaginated row count,
+  computed by the same query (`COUNT(*) OVER()`) rather than a second
+  round trip -- so a caller walking pages never has to ask "how many are
+  there" separately.
 }
 \description{
 One row per `(year, canonical_govid, spend_subtype, category)`. Amounts are

tests/testthat/test-all-categories-rollup.R

@@ -0,0 +1,58 @@
+test_that('cog_geographic_rollup() accepts "All Categories" and agrees with per-category sums', {
+  skip_if_no_corpus()
+  govs <- cog_gov_search(name = NULL, state = "WI", type = 2L)
+  expect_gt(nrow(govs), 1L)
+  ids <- list(city = utils::head(govs$canonical_govid, 25L))
+
+  by_cat <- cog_geographic_rollup(ids, category = NULL, years = 2019L)
+  total  <- cog_geographic_rollup(ids, category = "All Categories", years = 2019L)
+
+  expect_setequal(unique(total$category), "All Categories")
+  # one row per (govid, subtype) that appears in the per-category result
+  key_by_cat <- unique(paste(by_cat$canonical_govid, by_cat$spend_subtype))
+  key_total  <- paste(total$canonical_govid, total$spend_subtype)
+  expect_setequal(key_total, key_by_cat)
+
+  lhs <- tapply(by_cat$amt_nominal, paste(by_cat$canonical_govid, by_cat$spend_subtype), sum)
+  rhs <- tapply(total$amt_nominal,  key_total, sum)
+  expect_equal(as.numeric(rhs[names(lhs)]), as.numeric(lhs), tolerance = 1e-8)
+})
+
+test_that('"All Categories" survives per_capita and inflation adjustment through the rollup', {
+  skip_if_no_corpus()
+  govs <- cog_gov_search(name = NULL, state = "WI", type = 2L)
+  ids  <- list(city = utils::head(govs$canonical_govid, 10L))
+  r <- cog_geographic_rollup(ids, category = "All Categories", years = 2019L,
+                             per_capita = TRUE, adjust_to_year = 2020L)
+  expect_true(all(c("amt_per_capita_nominal", "amt_real", "amt_per_capita_real") %in% names(r)))
+  expect_setequal(unique(r$category), "All Categories")
+  expect_true(all(is.finite(r$amt_real)))
+})
+
+test_that('cog_geographic_rollup() still refuses expenditure_concept = "total" with "All Categories"', {
+  skip_if_no_corpus()
+  govs <- cog_gov_search(name = NULL, state = "WI", type = 2L)
+  ids  <- list(city = utils::head(govs$canonical_govid, 5L))
+  expect_error(
+    cog_geographic_rollup(ids, category = "All Categories", years = 2019L,
+                          expenditure_concept = "total")
+  )
+})
+
+test_that("n_units_reporting is category-conditional, not a response rate", {
+  skip_if_no_corpus()
+  govs <- cog_gov_search(name = NULL, state = "WI", type = 2L)
+  ids  <- list(city = govs$canonical_govid)
+
+  police <- cog_geographic_rollup(ids, category = "Police", years = 2012L)
+  allcat <- cog_geographic_rollup(ids, category = "All Categories", years = 2012L)
+
+  cov_police <- cog_explain(police, format = "list")$coverage
+  cov_all    <- cog_explain(allcat, format = "list")$coverage
+
+  # Same year, same requested govids, same collection -- yet a single category
+  # reports fewer units than the all-categories query. That gap is real zeros,
+  # not non-response, which is exactly why the ratio is not a response rate.
+  expect_lte(cov_police$n_units_reporting, cov_all$n_units_reporting)
+  expect_identical(cov_police$n_units_expected, cov_all$n_units_expected)
+})

tests/testthat/test-all-categories.R

@@ -0,0 +1,267 @@
+# Baseline at branch point: 843 PASS / 0 FAIL / 0 SKIP / 0 WARN (2026-08-05, origin/main 2fc9e75)
+
+test_that(".build_verb_sql emits a literal category and no category filter in all-categories mode", {
+  sql <- uscogdata:::.build_verb_sql(
+    view         = "spending_annotated",
+    subtype_col  = "spend_subtype",
+    govid        = "552025209777",
+    years        = 2019L,
+    category     = NULL,
+    subtype_scope = c("operations", "capital"),
+    all_categories = TRUE
+  )
+
+  expect_match(sql, "'All Categories' AS category", fixed = TRUE)
+  # no category filter of any kind
+  expect_false(grepl("AND category IN", sql, fixed = TRUE))
+  # category is not a grouping key
+  expect_false(grepl("GROUP BY year, canonical_govid, gov_name, xwalk_gov_name, spend_subtype, category",
+                     sql, fixed = TRUE))
+  # the subtype allowlist still applies -- this is what makes the sum a concept
+  expect_match(sql, "AND spend_subtype IN ('operations','capital')", fixed = TRUE)
+})
+
+test_that(".build_verb_sql is unchanged when all_categories is FALSE", {
+  args <- list(
+    view = "spending_annotated", subtype_col = "spend_subtype",
+    govid = "552025209777", years = 2019L, category = NULL,
+    subtype_scope = c("operations", "capital")
+  )
+  old <- do.call(uscogdata:::.build_verb_sql, args)
+  new <- do.call(uscogdata:::.build_verb_sql, c(args, list(all_categories = FALSE)))
+  expect_identical(old, new)
+  expect_match(new, "GROUP BY year, canonical_govid, gov_name, xwalk_gov_name, spend_subtype, category",
+               fixed = TRUE)
+})
+
+test_that(".ALL_CATEGORIES is the exact reserved string", {
+  expect_identical(uscogdata:::.ALL_CATEGORIES, "All Categories")
+})
+
+test_that('cog_spending(category = "All Categories") sums to the per-category total', {
+  gov <- "552025209777"
+  by_cat <- cog_spending(gov, 2019L)
+  total  <- cog_spending(gov, 2019L, category = "All Categories")
+
+  expect_true(nrow(total) > 0L)
+  expect_setequal(unique(total$category), "All Categories")
+  # one row per subtype present in the by-category result
+  expect_setequal(unique(total$spend_subtype), unique(by_cat$spend_subtype))
+  expect_equal(nrow(total), length(unique(by_cat$spend_subtype)))
+
+  # the dollars agree, per subtype
+  lhs <- tapply(by_cat$amt_nominal, by_cat$spend_subtype, sum)
+  rhs <- tapply(total$amt_nominal,  total$spend_subtype,  sum)
+  expect_equal(as.numeric(rhs[names(lhs)]), as.numeric(lhs), tolerance = 1e-8)
+})
+
+test_that('"All Categories" respects expenditure_concept', {
+  gov <- "552025209777"
+  prim <- cog_spending(gov, 2019L, category = "All Categories",
+                       expenditure_concept = "primary")
+  dir  <- cog_spending(gov, 2019L, category = "All Categories",
+                       expenditure_concept = "direct")
+  # direct = primary plus interest and insurance benefits, so it is never smaller
+  expect_gte(sum(dir$amt_nominal), sum(prim$amt_nominal))
+})
+
+test_that('"All Categories" works on revenue and respects revenue_concept', {
+  gov <- "552025209777"
+  gen <- cog_revenue(gov, 2019L, category = "All Categories",
+                     revenue_concept = "general")
+  tot <- cog_revenue(gov, 2019L, category = "All Categories",
+                     revenue_concept = "total")
+  expect_setequal(unique(gen$category), "All Categories")
+  expect_gte(sum(tot$amt_nominal), sum(gen$amt_nominal))
+})
+
+test_that('"All Categories" cannot be combined with another category', {
+  expect_error(
+    cog_spending("552025209777", 2019L, category = c("All Categories", "Police")),
+    class = "uscogdata_all_categories_not_combinable"
+  )
+})
+
+test_that('"All Categories" is recorded in provenance', {
+  r <- cog_spending("552025209777", 2019L, category = "All Categories")
+  expect_identical(cog_explain(r, format = "list")$category, "All Categories")
+})
+
+test_that('"All Categories" combines with subtype to give operating totals', {
+  gov <- "552025209777"
+  ops_by_cat <- cog_spending(gov, 2019L)
+  ops_by_cat <- ops_by_cat[ops_by_cat$spend_subtype == "operations", ]
+  ops_total  <- cog_spending(gov, 2019L, category = "All Categories")
+  ops_total  <- ops_total[ops_total$spend_subtype == "operations", ]
+  expect_equal(sum(ops_total$amt_nominal), sum(ops_by_cat$amt_nominal),
+               tolerance = 1e-8)
+})
+
+test_that('cog_categories() advertises "All Categories" for both flows', {
+  all <- cog_categories()
+  rows <- all[all$category == "All Categories", ]
+  expect_setequal(rows$category_type, c("expenditure", "revenue"))
+  expect_true(all(is.na(rows$subtype)))
+  expect_true(all(is.na(rows$n_codes)))
+})
+
+test_that('cog_categories(type=) still scopes, including the pseudo-category', {
+  sp <- cog_categories(type = "spending")
+  expect_setequal(unique(sp$category_type), "expenditure")
+  expect_true("All Categories" %in% sp$category)
+
+  rev <- cog_categories(type = "revenue")
+  expect_setequal(unique(rev$category_type), "revenue")
+  expect_true("All Categories" %in% rev$category)
+
+  # balances have no concept vocabulary, so no pseudo-category
+  bal <- cog_categories(type = "balance")
+  expect_false("All Categories" %in% bal$category)
+})
+
+test_that('cog_categories(pattern=) matches the pseudo-category', {
+  hit <- cog_categories(pattern = "^All Categories$")
+  expect_equal(nrow(hit), 2L)
+})
+
+# --- final whole-branch review fixes ---------------------------------------
+
+test_that('complete = TRUE is refused when combined with "All Categories"', {
+  # .completion_grid_sql() would emit `AND c.category IN ('All Categories')`,
+  # match zero crosswalk rows, and the early return in .complete_result()
+  # would stamp completion$applied = TRUE, rows_filled = 0 -- reading as "the
+  # grid was checked and nothing was missing" when nothing was actually
+  # checked. Filling a summed row has no defined semantics, so the verb must
+  # refuse the combination outright (finding 2).
+  expect_error(
+    cog_spending("552025209777", 2019L, category = "All Categories",
+                complete = TRUE),
+    class = "uscogdata_complete_unsupported"
+  )
+  expect_error(
+    cog_revenue("552025209777", 2019L, category = "All Categories",
+               complete = TRUE),
+    class = "uscogdata_complete_unsupported"
+  )
+})
+
+test_that('cog_balances() rejects "All Categories" instead of silently returning zero rows', {
+  # cog_balances() reuses .validate_verb_inputs() but did not pass
+  # allow_all_categories = TRUE, so "All Categories" used to become
+  # `AND category IN ('All Categories')` against balance_annotated -- 0
+  # matching crosswalk rows, 0 rows back, no error (finding 3). Holdings are
+  # a stock with no concept vocabulary to sum across, so the honest answer is
+  # to refuse, the same way cog_spending()/cog_revenue() refuse other
+  # nonsensical combinations.
+  expect_error(
+    cog_balances("552025209777", 2019L, category = "All Categories"),
+    class = "uscogdata_all_categories_unsupported"
+  )
+  # An ordinary category still works -- this is not a blanket regression.
+  r <- suppressMessages(
+    cog_balances("552025209777", 2019L, category = "Fund Balances")
+  )
+  expect_gt(nrow(r), 0L)
+})
+
+test_that('expenditure_concept_direct_suppressed is NA, not FALSE, when categories are collapsed', {
+  # .detect_direct_suppressed() keys on
+  # paste(year, canonical_govid, category, sep = "\r"). In all-categories
+  # mode every row carries the literal "All Categories" value, so an IG-only
+  # row's key collides with any ordinary Direct row for the same
+  # (year, govid) -- has_direct reads TRUE whenever the government has ANY
+  # direct spending at all, candidate is always empty, and the detector can
+  # never fire. Before the fix this silently reported FALSE, an affirmative
+  # claim the code did not actually compute (finding 1). NA is the honest
+  # answer: cog_explain(x, format = "list") is required here, since without
+  # format = "list" it returns the result tibble, not the provenance list.
+  gov <- "552025209777"
+  t <- cog_spending(gov, 2019L, category = "All Categories",
+                    expenditure_concept = "total")
+  prov <- cog_explain(t, format = "list")
+  expect_true(is.na(prov$expenditure_concept_direct_suppressed))
+  expect_false(isTRUE(prov$expenditure_concept_direct_suppressed))
+  expect_match(prov$expenditure_concept_note, "unavailable", fixed = TRUE)
+
+  # A per-category "total" query on the same government/year is unaffected --
+  # the detector can still key correctly and reports a strict logical.
+  t_by_cat <- cog_spending(gov, 2019L, expenditure_concept = "total")
+  prov_by_cat <- cog_explain(t_by_cat, format = "list")
+  expect_false(is.na(prov_by_cat$expenditure_concept_direct_suppressed))
+})
+
+test_that('"All Categories" still signposts coverage gaps (finding 6, final whole-branch review)', {
+  # .build_suggestions()'s candidate sub-select used to be keyed on
+  # `category`, e.g. `WHERE category IN ('All Categories')`. Since
+  # .ALL_CATEGORIES is never itself a row in summary_categories.category,
+  # that sub-select always came back empty in all-categories mode, so
+  # `candidates` was empty and .build_suggestions() short-circuited to
+  # list() -- coverage signposting was structurally impossible for the one
+  # mode whose whole selling point is "you cannot sum the wrong scope"
+  # (uscogdata#9's entire point, silently defeated).
+  #
+  # AL state government, FY2011, category = "Corrections": this category has
+  # no legacy leaf rows in FY2011 (aggregate-flagged E04/E05 family), so the
+  # per-category query returns 0 rows and 3 recipe-hint suggestions fire
+  # (empty_year path). All-categories mode does not have an empty year --
+  # the government has other primary spending in FY2011 -- but the same
+  # suppressed Corrections dollars are still excluded from the summed total,
+  # so the fix (scoping the candidate sub-select by subtype_col/subtype_scope
+  # instead of by category, symmetric with .build_verb_sql()) must still
+  # surface them via the suppressed_component path.
+  gov <- "010000226085"
+
+  by_cat <- suppressMessages(cog_spending(gov, 2011L, category = "Corrections"))
+  sugg_by_cat <- cog_explain(by_cat, format = "list")$suggestions
+  expect_gt(length(sugg_by_cat), 0L)
+
+  all_cat <- suppressMessages(cog_spending(gov, 2011L, category = "All Categories"))
+  sugg_all_cat <- cog_explain(all_cat, format = "list")$suggestions
+  expect_gt(length(sugg_all_cat), 0L)
+
+  # The same Corrections recipe that fired per-category must also fire in
+  # all-categories mode -- not just some unrelated recipe.
+  ids_by_cat <- vapply(sugg_by_cat, function(s) s$recipe_id %||% "", character(1))
+  ids_all_cat <- vapply(sugg_all_cat, function(s) s$recipe_id %||% "", character(1))
+  expect_true("corrections_combined" %in% ids_by_cat)
+  expect_true("corrections_combined" %in% ids_all_cat)
+
+  # In all-categories mode the government DOES have other primary spending
+  # in FY2011 (the year itself is not a gap), so the suggestion can only have
+  # fired via the suppressed_component path, not empty_year.
+  corr_all <- sugg_all_cat[[which(ids_all_cat == "corrections_combined")]]
+  expect_identical(corr_all$trigger, "suppressed_component")
+  expect_gt(corr_all$suppressed_amount, 0)
+})
+
+test_that('"All Categories" candidate scoping is symmetric with .build_verb_sql() -- subtype, not category', {
+  # Direct assertion on the mechanism itself (finding 6): in all-categories
+  # mode .build_suggestions() must scope its candidate recipe sub-select by
+  # subtype_col/subtype_scope, not by the literal "All Categories" value.
+  # Passing all_categories = FALSE with the identical category value proves
+  # the branch -- not merely the subtype_col/subtype_scope arguments' mere
+  # presence -- is what changes the query.
+  con <- uscogdata:::.ensure_session()
+
+  none <- uscogdata:::.build_suggestions(
+    con, govid = "010000226085", years = 2011L,
+    category = "All Categories", result = NULL, basis = "harmonized",
+    flow_prefixes = c("E", "F", "G"),
+    long_view = "spending_long_harmonized",
+    all_categories = FALSE,
+    subtype_col = "spend_subtype",
+    subtype_scope = c("operations", "capital", "assistance")
+  )
+  expect_length(none, 0L)
+
+  scoped <- uscogdata:::.build_suggestions(
+    con, govid = "010000226085", years = 2011L,
+    category = "All Categories", result = NULL, basis = "harmonized",
+    flow_prefixes = c("E", "F", "G"),
+    long_view = "spending_long_harmonized",
+    all_categories = TRUE,
+    subtype_col = "spend_subtype",
+    subtype_scope = c("operations", "capital", "assistance")
+  )
+  expect_gt(length(scoped), 0L)
+})

tests/testthat/test-categories.R

@@ -29,7 +29,9 @@ test_that("cog_categories(type = 'spending') returns only expenditure rows", {
   # 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%
+  # Exclude pseudo-category which has NA for subtype
+  r_crosswalk <- r[r$category != "All Categories", ]
+  expect_true(all(r_crosswalk$subtype %in%
                   c("operations", "capital", "intergovernmental", "assistance",
                     "interest", "insurance_benefits")))
 })
@@ -54,7 +56,9 @@ test_that("cog_categories(type = 'revenue') returns only revenue rows", {
   # 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%
+  # Exclude pseudo-category which has NA for subtype
+  r_crosswalk <- r[r$category != "All Categories", ]
+  expect_true(all(r_crosswalk$subtype %in%
                   c("own_source", "federal", "state", "local_aid",
                     "insurance_trust", "utility", "liquor_store")))
 })
@@ -69,6 +73,8 @@ test_that("cog_categories(pattern = ...) filters case-insensitively", {
 test_that("cog_categories has one row per (category, subtype)", {
   skip_if_no_corpus()
   r <- cog_categories()
+  # Exclude pseudo-category which is not a crosswalk entry
+  r <- r[r$category != "All Categories", ]
   key <- paste(r$category, r$subtype, sep = "|")
   expect_equal(length(key), length(unique(key)))
 })
@@ -76,6 +82,8 @@ test_that("cog_categories has one row per (category, subtype)", {
 test_that("cog_categories item_codes is non-empty comma-separated string", {
   skip_if_no_corpus()
   r <- cog_categories()
+  # Exclude pseudo-category which has NA for n_codes and item_codes
+  r <- r[r$category != "All Categories", ]
   expect_true(all(nzchar(r$item_codes)))
   expect_true(all(r$n_codes >= 1L))
   # n_codes should equal count of commas + 1
@@ -93,3 +101,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-manifest.R

@@ -111,7 +111,7 @@ test_that("cog_manifest returns the active session's parsed manifest", {
   })
 })
 
-test_that(".validate_schema accepts schema_version 4, 5 and 6, rejects others", {
+test_that(".validate_schema accepts schema_version 4 through 7, rejects others", {
   expect_silent(uscogdata:::.validate_schema(list(schema_version = 4L)))
   expect_silent(uscogdata:::.validate_schema(list(schema_version = 5L)))
   # v6 = FIPS geography harmonization (2026-07-22): _code -> _asof rename +
@@ -119,12 +119,22 @@ test_that(".validate_schema accepts schema_version 4, 5 and 6, rejects others",
   # renamed columns and its geography comes from the xwalk, so v6 is accepted
   # without behavioural change -- see .validate_schema()'s note.
   expect_silent(uscogdata:::.validate_schema(list(schema_version = 6L)))
+  # v7 = `data_year` APPENDED as column 29 (cog_pipeline #80, 2026-08-03), the
+  # most recent fiscal year contributing to a collapsed key. Appended, never
+  # inserted: canonical_govid stays at position 26, so nothing this package
+  # reads shifts. Verified against the real v7 corpus before widening the
+  # allow-list -- cog_spending()/cog_balances() return correctly for FY2024 AND
+  # for FY2012, so the new column is inert here.
+  expect_silent(uscogdata:::.validate_schema(list(schema_version = 7L)))
   expect_error(
     uscogdata:::.validate_schema(list(schema_version = 3L)),
     "schema_version"
   )
+  # The upper bound still has to be ENFORCED, not just moved. Without this the
+  # test would no longer prove that an unknown future schema is refused, and a
+  # v8 corpus with a genuinely breaking change would sail through.
   expect_error(
-    uscogdata:::.validate_schema(list(schema_version = 7L)),
+    uscogdata:::.validate_schema(list(schema_version = 8L)),
     "schema_version"
   )
 })

tests/testthat/test-recipes.R

@@ -214,3 +214,255 @@ test_that("no signposting under basis = 'raw'", {
   prov <- attr(r, "provenance")
   expect_length(prov$suggestions, 0L)
 })
+
+# --- uscogdata#9: partial-coverage signposting ------------------------------
+
+test_that("no recipe component is ever renamed by harmonization", {
+  # The suppression trigger anti-joins the verb's long view on item_code.
+  # That is only sound because harmonization never rewrites a recipe
+  # component's code -- every component whose harmonized_code differs has
+  # harmonized_code IS NULL (and is aggregate-flagged). If this ever fails,
+  # .suppressed_components() would report reachable dollars as suppressed.
+  skip_if_no_corpus()
+  con <- uscogdata:::.ensure_session()
+  n <- DBI::dbGetQuery(con,
+    "SELECT COUNT(*) AS renamed FROM long
+     WHERE item_code IN (SELECT DISTINCT component_code FROM harmonization_recipes)
+       AND harmonized_code IS NOT NULL
+       AND harmonized_code <> item_code")$renamed
+  expect_equal(as.integer(n), 0L)
+})
+
+test_that(".select_long_view maps annotated view bases to their long views", {
+  expect_equal(
+    uscogdata:::.select_long_view("spending_annotated", "harmonized"),
+    "spending_long_harmonized")
+  expect_equal(
+    uscogdata:::.select_long_view("revenue_annotated", "harmonized"),
+    "revenue_long_harmonized")
+  expect_equal(
+    uscogdata:::.select_long_view("spending_annotated", "raw"),
+    "spending_long")
+})
+
+test_that(".suppressed_components measures the E67/E68 dollars Public Welfare drops", {
+  skip_if_no_corpus()
+  con <- uscogdata:::.ensure_session()
+  s <- uscogdata:::.suppressed_components(
+    con,
+    candidates = c("welfare_cash_e67_wide", "welfare_cash_e68_wide"),
+    govid = "061037123085", years = 2011L,
+    long_view = "spending_long_harmonized",
+    flow_prefixes = c("E", "F", "G"))
+
+  expect_s3_class(s, "tbl_df")
+  expect_equal(nrow(s), 2L)
+  s <- s[order(s$recipe_id), ]
+  expect_equal(s$recipe_id, c("welfare_cash_e67_wide", "welfare_cash_e68_wide"))
+  expect_equal(s$suppressed_amount, c(1803872000, 271589000))
+  expect_equal(s$suppressed_codes, c("E67", "E68"))
+})
+
+test_that(".suppressed_components finds nothing in a modern year", {
+  skip_if_no_corpus()
+  con <- uscogdata:::.ensure_session()
+  s <- uscogdata:::.suppressed_components(
+    con,
+    candidates = c("welfare_cash_e67_wide", "welfare_cash_e68_wide"),
+    govid = "061037123085", years = 2019L,
+    long_view = "spending_long_harmonized",
+    flow_prefixes = c("E", "F", "G"))
+  expect_equal(nrow(s), 0L)
+})
+
+test_that(".suppressed_components rejects a long_view outside the allowlist", {
+  skip_if_no_corpus()
+  con <- uscogdata:::.ensure_session()
+  expect_error(
+    uscogdata:::.suppressed_components(
+      con, candidates = "welfare_cash_e67_wide", govid = "061037123085",
+      years = 2011L, long_view = "long; DROP TABLE x",
+      flow_prefixes = c("E", "F", "G")),
+    class = "uscogdata_internal_error")
+})
+
+test_that(".suppressed_components never measures a component from the other flow family (I1)", {
+  # uscogdata#9 review, finding I1: without the flow_prefixes filter, a
+  # candidate recipe entirely outside the calling verb's own flow family is
+  # ALWAYS absent from that verb's view (by construction), so it was always
+  # reported as "suppressed" -- fabricating a dollar claim. E67/E68 are
+  # Public Welfare EXPENDITURE codes; scoping the measurement to revenue's
+  # own flow_prefixes must find nothing for them.
+  skip_if_no_corpus()
+  con <- uscogdata:::.ensure_session()
+  s <- uscogdata:::.suppressed_components(
+    con,
+    candidates = c("welfare_cash_e67_wide", "welfare_cash_e68_wide"),
+    govid = "061037123085", years = 2011L,
+    long_view = "revenue_long_harmonized",
+    flow_prefixes = c("T", "A", "U", "B", "C", "D"))
+  expect_equal(nrow(s), 0L)
+})
+
+test_that("uscogdata#9: Public Welfare signposts its suppressed E67/E68 dollars", {
+  # The bug: E74/E79 return rows for FY2011, so there is no row-absence gap,
+  # so nothing fired -- while E67 ($1,803,872,000) and E68 ($271,589,000) were
+  # dropped for being aggregate-published. LA County reports $3,185,943,000
+  # and omits $2,075,461,000, a 39% understatement, silently.
+  skip_if_no_corpus()
+  r <- suppressMessages(
+    cog_spending("061037123085", years = 2011L, category = "Public Welfare"))
+  sugg <- attr(r, "provenance")$suggestions
+
+  expect_length(sugg, 2L)
+  ids <- vapply(sugg, function(s) s$recipe_id, character(1))
+  expect_setequal(ids, c("welfare_cash_e67_wide", "welfare_cash_e68_wide"))
+
+  e67 <- sugg[[which(ids == "welfare_cash_e67_wide")]]
+  expect_equal(e67$trigger, "suppressed_component")
+  expect_equal(e67$suppressed_amount, 1803872000)
+  expect_equal(e67$suppressed_years, 2011L)
+  expect_equal(e67$suppressed_codes, "E67")
+  expect_equal(e67$hint, "re-run with recipe = 'welfare_cash_e67_wide'")
+
+  e68 <- sugg[[which(ids == "welfare_cash_e68_wide")]]
+  expect_equal(e68$trigger, "suppressed_component")
+  expect_equal(e68$suppressed_amount, 271589000)
+  expect_equal(e68$suppressed_codes, "E68")
+})
+
+test_that("uscogdata#9: an empty_year fire keeps its trigger and gains the dollars", {
+  # Corrections is the case that already worked: zero rows in FY2011, so the
+  # row-absence path fires. It must keep firing, keep trigger = "empty_year",
+  # keep its IG counterpart -- and now also report what was suppressed.
+  skip_if_no_corpus()
+  r <- suppressMessages(
+    cog_spending("061037123085", years = 2011L, category = "Corrections"))
+  sugg <- attr(r, "provenance")$suggestions
+
+  expect_length(sugg, 3L)
+  ids <- vapply(sugg, function(s) s$recipe_id, character(1))
+  expect_setequal(ids, c("corrections_combined", "corrections_capital_combined",
+                         "corrections_other_capital_combined"))
+  expect_true(all(vapply(sugg, function(s) s$trigger, character(1)) == "empty_year"))
+
+  cc <- sugg[[which(ids == "corrections_combined")]]
+  expect_equal(cc$suppressed_amount, 1371460000)
+  expect_equal(cc$suppressed_codes, "E05")
+  expect_equal(cc$ig_recipe_id, "corrections_ig_local_combined")
+})
+
+test_that("uscogdata#9: the revenue verb inherits the same trigger", {
+  # Alaska state FY2011 Miscellaneous Revenue reports $943,842,000 from
+  # U11/U20/U30 while dropping $1,899,995,000 of aggregate-published `U4-`
+  # rents and royalties -- the omission is LARGER than the reported figure.
+  skip_if_no_corpus()
+  r <- suppressMessages(
+    cog_revenue("020000227749", years = 2011L,
+                category = "Miscellaneous Revenue"))
+  sugg <- attr(r, "provenance")$suggestions
+
+  expect_length(sugg, 1L)
+  expect_equal(sugg[[1]]$recipe_id, "rents_royalties_u4_wide")
+  expect_equal(sugg[[1]]$trigger, "suppressed_component")
+  expect_equal(sugg[[1]]$suppressed_amount, 1899995000)
+  expect_equal(sugg[[1]]$suppressed_codes, "U4-")
+  # A revenue recipe must never be handed an M/L expenditure counterpart.
+  expect_null(sugg[[1]]$ig_recipe_id)
+})
+
+test_that("I1: cog_revenue never fabricates suppressed dollars for an expenditure-only recipe", {
+  # uscogdata#9 review, finding I1: Corrections is an expenditure-only
+  # category (E04/E05). cog_revenue() naturally returns zero rows for it, so
+  # corrections_combined still fires as an empty_year suggestion (its own
+  # generic join finds real E04/E05 data for this government) -- but before
+  # the flow_prefixes fix, .suppressed_components() measured E04/E05 against
+  # cog_revenue()'s OWN view (which can never contain an E-coded row by
+  # construction) and reported the full $3,631,945,000 as "suppressed",
+  # when cog_spending() for the same gov/years/category actually returns
+  # $3,691,029,000 -- nothing was suppressed at all.
+  skip_if_no_corpus()
+  r <- suppressMessages(
+    cog_revenue("061037123085", years = 2019:2020, category = "Corrections"))
+  sugg <- attr(r, "provenance")$suggestions
+  ids <- vapply(sugg, function(s) s$recipe_id, character(1))
+  expect_true("corrections_combined" %in% ids)
+
+  hit <- sugg[[which(ids == "corrections_combined")]]
+  expect_equal(hit$suppressed_amount, 0)
+  expect_equal(hit$suppressed_years, integer(0))
+  expect_equal(hit$suppressed_codes, character(0))
+
+  # And cog_spending() for the identical gov/years/category is unaffected --
+  # it actually finds the E04/E05 dollars the buggy measurement claimed were
+  # excluded.
+  sp <- suppressMessages(
+    cog_spending("061037123085", years = 2019:2020, category = "Corrections"))
+  expect_equal(sum(sp$amt_nominal), 3691029000)
+})
+
+test_that("uscogdata#9: no partial-coverage fire in a modern year", {
+  skip_if_no_corpus()
+  r <- cog_spending("061037123085", years = 2019L, category = "Public Welfare")
+  expect_length(attr(r, "provenance")$suggestions, 0L)
+})
+
+test_that("uscogdata#9: leaf-and-classified wide-era families never fire", {
+  # higher_ed_e18_wide and general_gov_e89_wide are the control group: their
+  # components (E16/E18, E85/E89) are ordinary classified leaves even in the
+  # wide era, so widening the trigger must leave them silent. This is the
+  # measurement that refutes "it would fire on every category in every legacy
+  # year" -- corpus-wide on the fixture, these two produce zero suppressed rows.
+  skip_if_no_corpus()
+  con <- uscogdata:::.ensure_session()
+  n <- DBI::dbGetQuery(con,
+    "SELECT COUNT(*) AS n
+     FROM long l
+     JOIN harmonization_recipes r
+       ON l.item_code = r.component_code
+      AND l.year BETWEEN r.year_min AND r.year_max
+     WHERE r.recipe_id IN ('higher_ed_e18_wide', 'general_gov_e89_wide')
+       AND l.amt <> 0
+       AND NOT EXISTS (
+         SELECT 1 FROM spending_long_harmonized v
+         WHERE v.canonical_govid = l.canonical_govid
+           AND v.year = l.year AND v.item_code = l.item_code)")$n
+  expect_equal(as.integer(n), 0L)
+})
+
+test_that("uscogdata#9: the cli message reports the suppressed dollars", {
+  skip_if_no_corpus()
+  expect_message(
+    cog_spending("061037123085", years = 2011L, category = "Public Welfare"),
+    "1,803,872,000", fixed = TRUE)
+  expect_message(
+    cog_spending("061037123085", years = 2011L, category = "Public Welfare"),
+    "FY2011", fixed = TRUE)
+  expect_message(
+    cog_spending("061037123085", years = 2011L, category = "Public Welfare"),
+    "E67", fixed = TRUE)
+})
+
+test_that("uscogdata#9: cog_explain() reports the suppressed dollars", {
+  # cog_explain()'s whole "print" output -- including the Suggestions
+  # section built from cli::cli_ul() -- is emitted on the message stream
+  # (verified empirically 2026-08-04: capture.output(..., type = "output")
+  # returns character(0) for this call; testthat::capture_messages() is what
+  # actually carries it), so that is the stream this test captures.
+  skip_if_no_corpus()
+  r <- suppressMessages(
+    cog_spending("061037123085", years = 2011L, category = "Public Welfare"))
+  out <- paste(testthat::capture_messages(cog_explain(r)), collapse = "")
+  expect_match(out, "271,589,000", fixed = TRUE)
+})
+
+test_that("the provenance schema documents the suggestion trigger fields", {
+  sch <- jsonlite::fromJSON(
+    system.file("schemas", "provenance-v1.json", package = "uscogdata"),
+    simplifyVector = FALSE)
+  props <- sch$properties$suggestions$items$properties
+  expect_true(all(c("trigger", "suppressed_amount", "suppressed_years",
+                    "suppressed_codes") %in% names(props)))
+  expect_setequal(unlist(props$trigger$enum),
+                  c("empty_year", "suppressed_component"))
+})

tests/testthat/test-revenue-pagination.R

@@ -0,0 +1,29 @@
+# Mirror of test-spending-pagination.R for cog_revenue(), which shares the
+# same .verb_spendrev()/.build_verb_sql() pushdown -- see that file for the
+# incident this fixes.
+
+test_that("cog_revenue limit/offset page correctly and report total_rows", {
+  skip_if_no_corpus()
+  full <- cog_revenue("121011212191", years = 2019:2020, category = NULL)
+  page <- cog_revenue("121011212191", years = 2019:2020, category = NULL,
+                      limit = 5L, offset = 3L)
+  expect_equal(nrow(page), 5L)
+  expect_equal(page[c("year", "canonical_govid", "revenue_subtype", "category")],
+              full[4:8, c("year", "canonical_govid", "revenue_subtype", "category")],
+              ignore_attr = TRUE)
+  expect_equal(attr(page, "total_rows"), nrow(full))
+})
+
+test_that("cog_revenue limit unset by default leaves total_rows absent", {
+  skip_if_no_corpus()
+  r <- cog_revenue("121011212191", 2020L, "Property Tax")
+  expect_null(attr(r, "total_rows"))
+})
+
+test_that("cog_revenue complete + limit conflict aborts the same way as cog_spending", {
+  skip_if_no_corpus()
+  expect_error(
+    cog_revenue("121011212191", 2020L, "Property Tax", complete = TRUE, limit = 5L),
+    class = "uscogdata_complete_pagination_conflict"
+  )
+})

tests/testthat/test-spending-pagination.R

@@ -0,0 +1,95 @@
+# cog-api's paginate() used to slice an already-fully-materialized result:
+# every page of a deep sweep re-ran the whole query and re-listified every
+# row, just to keep 1000 and discard the rest. For a 193,105-row fleet-wide
+# query walked 194 pages deep, that repeated the full cost 194 times and
+# wedged the production server for hours (2026-08-06 incident). limit/offset
+# here push the slice into the SQL itself, so a page costs O(limit), not
+# O(full result).
+
+test_that("limit without offset returns the first page, matching the unpaginated head", {
+  skip_if_no_corpus()
+  full <- cog_spending("121011212191", years = 2019:2020, category = NULL)
+  page <- cog_spending("121011212191", years = 2019:2020, category = NULL,
+                       limit = 10L)
+  expect_equal(nrow(page), 10L)
+  expect_equal(page[c("year", "canonical_govid", "spend_subtype", "category")],
+              full[1:10, c("year", "canonical_govid", "spend_subtype", "category")],
+              ignore_attr = TRUE)
+})
+
+test_that("offset skips ahead without gaps or overlap", {
+  skip_if_no_corpus()
+  full <- cog_spending("121011212191", years = 2019:2020, category = NULL)
+  page2 <- cog_spending("121011212191", years = 2019:2020, category = NULL,
+                        limit = 10L, offset = 10L)
+  expect_equal(nrow(page2), 10L)
+  expect_equal(page2[c("year", "canonical_govid", "spend_subtype", "category")],
+              full[11:20, c("year", "canonical_govid", "spend_subtype", "category")],
+              ignore_attr = TRUE)
+})
+
+test_that("walking every page reconstructs the unpaginated result exactly", {
+  skip_if_no_corpus()
+  full <- cog_spending("121011212191", years = 2019:2020, category = NULL)
+  n <- nrow(full)
+  limit <- 7L
+  pages <- list()
+  offset <- 0L
+  repeat {
+    p <- cog_spending("121011212191", years = 2019:2020, category = NULL,
+                      limit = limit, offset = offset)
+    if (nrow(p) == 0L) break
+    pages[[length(pages) + 1L]] <- p
+    offset <- offset + limit
+    if (offset > n + limit) stop("test runaway: paging did not terminate")
+  }
+  walked <- dplyr::bind_rows(pages)
+  expect_equal(nrow(walked), n)
+  key_cols <- c("year", "canonical_govid", "spend_subtype", "category", "amt_nominal")
+  expect_equal(walked[key_cols], full[key_cols], ignore_attr = TRUE)
+})
+
+test_that("total_rows attribute reports the full unpaginated count", {
+  skip_if_no_corpus()
+  full <- cog_spending("121011212191", years = 2019:2020, category = NULL)
+  page <- cog_spending("121011212191", years = 2019:2020, category = NULL,
+                       limit = 5L, offset = 0L)
+  expect_equal(attr(page, "total_rows"), nrow(full))
+})
+
+test_that("offset past the end returns zero rows, not an error", {
+  skip_if_no_corpus()
+  full <- cog_spending("121011212191", years = 2019:2020, category = NULL)
+  page <- cog_spending("121011212191", years = 2019:2020, category = NULL,
+                       limit = 10L, offset = nrow(full) + 100L)
+  expect_equal(nrow(page), 0L)
+  expect_equal(attr(page, "total_rows"), nrow(full))
+})
+
+test_that("limit is unset by default -- unpaginated calls are unaffected", {
+  skip_if_no_corpus()
+  r <- cog_spending("121011212191", 2020L, "Corrections")
+  expect_null(attr(r, "total_rows"))
+})
+
+test_that("per_capita and adjust_to_year still apply correctly within a page", {
+  skip_if_no_corpus()
+  full <- cog_spending("121011212191", years = 2020L, category = NULL,
+                       per_capita = TRUE, adjust_to_year = 2022L)
+  page <- cog_spending("121011212191", years = 2020L, category = NULL,
+                       per_capita = TRUE, adjust_to_year = 2022L,
+                       limit = 3L, offset = 2L)
+  expect_equal(page[c("amt_nominal", "amt_real", "amt_per_capita_nominal",
+                      "amt_per_capita_real")],
+              full[3:5, c("amt_nominal", "amt_real", "amt_per_capita_nominal",
+                          "amt_per_capita_real")],
+              ignore_attr = TRUE)
+})
+
+test_that("complete = TRUE with limit aborts -- pagination over a partial grid is undefined", {
+  skip_if_no_corpus()
+  expect_error(
+    cog_spending("121011212191", 2020L, "Corrections", complete = TRUE, limit = 5L),
+    class = "uscogdata_complete_pagination_conflict"
+  )
+})