uscogdata/NEWS.md

# uscogdata 0.4.0

## DuckDB's resource budget is configurable

`USCOGDATA_DUCKDB_THREADS` and `USCOGDATA_DUCKDB_MEMORY_LIMIT` (with matching
`options(uscogdata.duckdb_threads = )` / `options(uscogdata.duckdb_memory_limit = )`
spellings) cap the DuckDB connection the package opens. Both follow the same
env-var > option > default precedence as `USCOGDATA_URL`.

Unset, **no pragma is issued at all** and DuckDB's own defaults apply exactly as
before -- every visible core. That is right for one interactive session on a
dedicated machine and wrong for a server: where several readers share a host, each
otherwise claims the whole machine and they contend. Capping measured ~5% on a
single-government all-years query (502 ms at 2 threads vs 475 ms uncapped on 16
cores), which is cheap enough that a server should always cap.

This replaces a workaround in which a consumer reached into the package namespace
at boot -- `getFromNamespace(".ensure_session", "uscogdata")()` followed by a manual
`SET threads` -- depending both on a private name and on the session already being
open.

## `cog_gov_search()` and `cog_balances()` gain `limit`/`offset`

Pagination arrived on `cog_spending()`/`cog_revenue()` in 0.3.0; the other two
verbs were left materializing everything and slicing in R. Both now take
`limit`/`offset` with the same semantics: `NULL` default, the page applied in
SQL behind a deterministic `ORDER BY`, and the unpaginated count returned as a
`total_rows` attribute computed by `COUNT(*) OVER()` in the same scan rather
than a second query.

`cog_gov_search()` had no `LIMIT` at all, which made it the one verb that
returns the entire 40,336-row crosswalk when called with no filter.

Two refusals rather than silent surprises:

* `cog_balances(recipe = , limit = )` aborts with class
  `uscogdata_recipe_pagination_conflict` -- a recipe's result comes from a
  separate query that pagination is not wired into.
* `cog_gov_search()` in basket mode (`length(name) > 1`) aborts with class
  `uscogdata_basket_pagination_conflict`. Basket mode returns one resolved row
  per requested name with a sidecar covering all of them; a page of that is not
  a page of anything the caller asked for.

## Cohorts can be named by predicate, not just by id

`cog_spending()`, `cog_revenue()` and `cog_balances()` gain optional `state`
and `type` arguments. Both default to `NULL`, so every existing call behaves
exactly as before.

Passing them expresses the cohort as a subquery against `canonical_fips_xwalk`
inside each statement, instead of round-tripping the ids through R and
rendering them back into a literal `IN` list:

```r
# before: resolve 20,106 ids in R, then embed them in every statement
ids <- cog_gov_search(NULL, state = "CA", type = "city")$canonical_govid
cog_spending(ids, years = 2022)

# now: the cohort never leaves the database
cog_spending(years = 2022, state = "CA", type = "city")
```

Measured against the production corpus, same FY2022 aggregate over the
20,106-government `type = "city"` cohort:

| cohort expressed as | time |
|---|---:|
| `IN (20,106 literals)` | 449 ms |
| join against a temp cohort table | 99 ms |
| predicate on `canonical_fips_xwalk` | **94 ms** |
| no cohort filter at all (the floor) | 88 ms |

**4.8x, within 7% of the floor.** The rendered `IN` list was 301,591
characters and was re-parsed in 5-8 separate statements per call, so the cost
was paid repeatedly; the predicate's size is constant in the cohort.

`state` and `type` use the same vocabulary and the same internal coercion as
`cog_gov_search()` -- `state` is a postal abbreviation (`"WI"`) even though the
crosswalk column holds a FIPS code (`"55"`).

Supplying `govid` **and** `state`/`type` intersects them: the governments in
`govid` that also match the predicate. Naming no cohort at all now aborts with
class `uscogdata_no_cohort` rather than R's "argument is missing" error.

When the cohort is named by predicate there is no id list to report, so
`provenance$scope$govids_found`/`govids_missing` are empty and
`provenance$scope$cohort` carries `state`, `type` and `n_governments` instead.
A `govid`-named cohort's provenance is unchanged.

## Fixes

* `cog_gov_search()` now orders by `population_acs DESC NULLS LAST,
  canonical_govid`. **`population_acs` alone is not a total order** -- ties, and
  the entire `NULLS LAST` block, came back in whatever order the scan produced.
  That was invisible while every call returned the full result set, but it makes
  a paged sweep unsound: two requests can order tied rows differently, so a row
  is duplicated on one page and missing from the next. Unpaginated results are
  unchanged except for the relative order of rows that were already tied.

* An unknown `state` abbreviation now aborts with "Unknown state abbreviation"
  (class `uscogdata_unknown_state`) instead of base R's "subscript out of
  bounds". `.state_abbrev_to_fips` is a named character vector, so `[[` on an
  absent name threw before the curated message could be reached -- making that
  message unreachable dead code in every verb that takes a `state`.

# uscogdata 0.3.0

First public release.

`uscogdata` provides curated R verbs over the Civilytics US Census of
Governments finance corpus: unit-level financial profiles, geographic rollups
and peer comparisons, with auditable provenance on every result.

## What it covers

Government types 0-3 (state, county, municipality, township), FY1967-FY2024 --
56 fiscal years, 46,148,034 rows, 190.6 MB. There is no source data for FY1968
or FY1969. Special districts (type 4) and school districts (type 5) are out of
scope pending validation.

## The verbs

`cog_spending()`, `cog_revenue()` and `cog_balances()` for flows and holdings;
`cog_gov_search()` to resolve place names (including basket mode for many at
once); `cog_find_peers()` and `cog_peer_compare()` for cohorts;
`cog_geographic_rollup()` for aggregates; `cog_categories()`, `cog_recipes()`,
`cog_manifest()` and `cog_explain()` for metadata and provenance; and
`cog_mirror()` for a local copy of the corpus.

## Reading the corpus now works out of the box

* The package reads the published corpus over HTTPS **with no configuration**.
  Previously the default was a placeholder sentinel and no document in the
  package supplied a working URL, so a new user had no path to a session.
* Remote reads work at all. The partitioned view used a glob, and DuckDB
  cannot expand a glob over generic HTTP -- there is no directory listing to
  expand against. Partition paths are now enumerated from the corpus manifest,
  which is host-agnostic: an HTTPS mirror, a Nextcloud share and a local
  `cog_mirror()` copy all take the same path.
* Nothing is written to disk in remote mode; DuckDB fetches only the row
  groups a query needs.

## Four things to know before your first query

* **Amounts are in full US dollars.** The raw Census files report thousands;
  the verbs multiply by 1000 on the way out. Do not multiply again.
* **Multi-government aggregates disclose their coverage.** The Census is a
  complete enumeration only in years ending in 2 and 7; every other year is a
  sample. Every such result carries `provenance$coverage` with per-year
  `n_units_reporting`.
* **Absence means two different things.** Before FY2012 an absent cell means
  Census published $0; from FY2012 it means not reported. `complete = TRUE`
  labels which.
* **Series breaks reach you unasked.** Catalogued breaks intersecting your
  query appear in provenance and in `cog_explain()`.

## Known limits

* Special districts (type 4) and school districts (type 5) are out of scope.
* Per-capita rollups exclude governments with no F-33 population, which is by
  design but does silently narrow a rollup.
* `n_units_reporting` is category-conditional and is not a response rate.
* Employee-retirement (`X`) codes stop at FY2016, when those systems moved to
  the Annual Survey of Public Pensions.

# 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).