revenue_concept='general' silently returns own-source only for all 35,040 local governments: IG revenue is aggregate-only and dropped by revenue_long #38

New Issue

2026-08-05T12:36:40-04:00

jared commented

2026-08-05 12:36:40 -04:00

Found while building the client-facing Southern API guide (cog_explorer/docs/superpowers/specs/2026-08-05-south-api-guide-design.md). Measured against the full published corpus 2026-08-05. Verdict: defect.

Symptom

cog_revenue(..., revenue_concept = "general") returns own-source revenue only for every local government. The federal, state and local_aid subtypes come back empty — silently, with no suggestion and no provenance note — even though general is defined as their sum.

Nine large Southern cities, FY2022 and FY2024, via the deployed API:

city            yr   subtypes present   IG share
ATLANTA        2022  own_source             0.0%
CHARLOTTE      2022  own_source             0.0%
HOUSTON        2022  own_source             0.0%
MIAMI          2022  own_source             0.0%
NEW ORLEANS    2022  own_source             0.0%
BIRMINGHAM     2022  own_source             0.0%
MEMPHIS        2022  own_source             0.0%
RICHMOND       2022  own_source             0.0%
NASHVILLE      2022  own_source             0.0%

Identical at FY2024. A city receiving zero federal and state aid is not a credible figure.

Asking for the IG categories directly returns nothing:

B=https://cog-api.civilytics.org/api/v1
for C in "IG%20State" "IG%20Federal" "IG%20Local"; do
  curl -s "$B/governments/132121194678/revenue?years=2022&category=$C" | jq -c '{n:(.data|length)}'
done
# {"n":0}
# {"n":0}
# {"n":0}

basis=raw returns the same thing, so this is not the harmonization layer.

Root cause

The dollars are in the corpus. Atlanta FY2022:

item_code	is_aggregate	amt ($1,000s)
B89	TRUE	94,119
C89	TRUE	72,974
D89	TRUE	225,726

All three are in summary_categories — B89 under IG Federal, C89 under IG State, D89 under IG Local. But inst/sql/21-revenue_long.sql filters:

CREATE OR REPLACE VIEW revenue_long AS
SELECT * FROM long
WHERE item_code IN (SELECT item_code FROM summary_categories WHERE category_type = 'revenue')
  AND NOT is_aggregate;

so every one of them is dropped before revenue_annotated ever sees it.

Magnitude

Corpus-wide, FY2022, item-code prefixes B/C/D:

`is_aggregate`	rows	governments	amount
FALSE (survives)	578	50	$1,068.61B
TRUE (dropped)	67,127	35,040	$424.88B

The 50 survivors are the states — they report leaf-level IG codes. Every county, city and township reports intergovernmental revenue only at the aggregate *89 level, so all of it is dropped.

US cities alone, FY2022: B $59.38B + C $80.13B + D $17.33B = $156.84B excluded, across 9,270–17,196 cities per family. Not one non-aggregate B/C/D row exists for any city that year.

Why this is not covered by the existing aggregate rule

cog_pipeline/docs/reader-specification.md §3.1 and §4 discuss this pattern at length and state the NOT is_aggregate rule is "exactly right for Direct". But that discussion is about legacy-era spending (M/L families, the Total concept), and it says explicitly that those families' "leaves first appear in the modern era."

That premise does not hold here. This is modern-era revenue (FY2022, FY2024), and for local governments the B/C/D leaves do not appear at all — the aggregate is the only row that exists. So the rule that correctly prevents double-counting when leaves exist instead deletes the entire quantity when they do not.

The spec's own remedy for the spending case — assemble year-scoped via recipes — has no revenue counterpart.

Why it is a contract violation, not just a coverage caveat

revenue_concept = "general" is documented as own_source + federal + state + local_aid, mirroring Census's published concept. Returning own-source alone is not a narrower answer to the question asked; it is a different quantity presented under the same name. For Atlanta FY2022 the reported general revenue of $1,627,842,000 omits $392,819,000 that the corpus holds and the crosswalk classifies.

Nothing signals it: suggestions is [], and no notes field on any returned row mentions the exclusion.

Suggested

Decide the concept boundary for revenue aggregates explicitly, the way §3.1 did for spending IG. If a category's dollars for a given (government, year) exist only on aggregate rows, excluding them yields zero rather than preventing a double count — the filter should be conditional on whether leaves exist, not unconditional.
Failing that, disclose it. At minimum a suggestions entry or a notes value on the affected query, so a caller sees that a defined component of the requested concept was dropped. Silence is the part that makes this dangerous.
Consider whether revenue_concept = "general" should refuse to answer for governments where the federal/state/local_aid legs are entirely unavailable, rather than returning a number that reads as complete.

Impact on current work

This blocks a chart in the Southern API guide ("revenue mix — own-source vs IG Federal vs IG State share"), and it understates the guide's headline revenue-per-capita measure for every county and city. That is the small version of the problem; the general version is that any consumer computing local-government revenue from this corpus is currently missing roughly $425B a year with no indication.

Related: cog-api #37 (all-categories totals) — an all-categories revenue total inherits this exclusion silently too.

Found while building the **client-facing Southern API guide** (`cog_explorer/docs/superpowers/specs/2026-08-05-south-api-guide-design.md`). Measured against the full published corpus 2026-08-05. Verdict: **defect**. ## Symptom `cog_revenue(..., revenue_concept = "general")` returns **own-source revenue only** for every local government. The `federal`, `state` and `local_aid` subtypes come back empty — silently, with no suggestion and no provenance note — even though `general` is *defined* as their sum. Nine large Southern cities, FY2022 and FY2024, via the deployed API: ``` city yr subtypes present IG share ATLANTA 2022 own_source 0.0% CHARLOTTE 2022 own_source 0.0% HOUSTON 2022 own_source 0.0% MIAMI 2022 own_source 0.0% NEW ORLEANS 2022 own_source 0.0% BIRMINGHAM 2022 own_source 0.0% MEMPHIS 2022 own_source 0.0% RICHMOND 2022 own_source 0.0% NASHVILLE 2022 own_source 0.0% ``` Identical at FY2024. A city receiving zero federal and state aid is not a credible figure. Asking for the IG categories directly returns nothing: ```bash B=https://cog-api.civilytics.org/api/v1 for C in "IG%20State" "IG%20Federal" "IG%20Local"; do curl -s "$B/governments/132121194678/revenue?years=2022&category=$C" | jq -c '{n:(.data|length)}' done # {"n":0} # {"n":0} # {"n":0} ``` `basis=raw` returns the same thing, so this is not the harmonization layer. ## Root cause The dollars are in the corpus. Atlanta FY2022: | item_code | is_aggregate | amt ($1,000s) | |---|---|---| | B89 | **TRUE** | 94,119 | | C89 | **TRUE** | 72,974 | | D89 | **TRUE** | 225,726 | All three are in `summary_categories` — `B89` under **IG Federal**, `C89` under **IG State**, `D89` under **IG Local**. But `inst/sql/21-revenue_long.sql` filters: ```sql CREATE OR REPLACE VIEW revenue_long AS SELECT * FROM long WHERE item_code IN (SELECT item_code FROM summary_categories WHERE category_type = 'revenue') AND NOT is_aggregate; ``` so every one of them is dropped before `revenue_annotated` ever sees it. ## Magnitude Corpus-wide, FY2022, item-code prefixes B/C/D: | `is_aggregate` | rows | governments | amount | |---|---|---|---| | FALSE (survives) | 578 | **50** | $1,068.61B | | TRUE (dropped) | 67,127 | **35,040** | **$424.88B** | The 50 survivors are the states — they report leaf-level IG codes. **Every county, city and township reports intergovernmental revenue only at the aggregate `*89` level, so all of it is dropped.** US cities alone, FY2022: B $59.38B + C $80.13B + D $17.33B = **$156.84B** excluded, across 9,270–17,196 cities per family. Not one non-aggregate B/C/D row exists for any city that year. ## Why this is not covered by the existing aggregate rule `cog_pipeline/docs/reader-specification.md` §3.1 and §4 discuss this pattern at length and state the `NOT is_aggregate` rule is "exactly right for **Direct**". But that discussion is about **legacy-era spending** (`M`/`L` families, the Total concept), and it says explicitly that those families' "leaves first appear in the modern era." That premise does not hold here. This is **modern-era revenue** (FY2022, FY2024), and for local governments the B/C/D leaves do not appear at all — the aggregate is the only row that exists. So the rule that correctly prevents double-counting when leaves exist instead deletes the entire quantity when they do not. The spec's own remedy for the spending case — assemble year-scoped via recipes — has no revenue counterpart. ## Why it is a contract violation, not just a coverage caveat `revenue_concept = "general"` is documented as `own_source + federal + state + local_aid`, mirroring Census's published concept. Returning own-source alone is not a narrower answer to the question asked; it is a different quantity presented under the same name. For Atlanta FY2022 the reported general revenue of $1,627,842,000 omits $392,819,000 that the corpus holds and the crosswalk classifies. Nothing signals it: `suggestions` is `[]`, and no `notes` field on any returned row mentions the exclusion. ## Suggested 1. **Decide the concept boundary for revenue aggregates explicitly**, the way §3.1 did for spending IG. If a category's dollars for a given `(government, year)` exist *only* on aggregate rows, excluding them yields zero rather than preventing a double count — the filter should be conditional on whether leaves exist, not unconditional. 2. **Failing that, disclose it.** At minimum a `suggestions` entry or a `notes` value on the affected query, so a caller sees that a defined component of the requested concept was dropped. Silence is the part that makes this dangerous. 3. **Consider whether `revenue_concept = "general"` should refuse to answer** for governments where the federal/state/local_aid legs are entirely unavailable, rather than returning a number that reads as complete. ## Impact on current work This blocks a chart in the Southern API guide ("revenue mix — own-source vs IG Federal vs IG State share"), and it understates the guide's headline revenue-per-capita measure for every county and city. That is the small version of the problem; the general version is that any consumer computing local-government revenue from this corpus is currently missing roughly $425B a year with no indication. Related: `cog-api` #37 (all-categories totals) — an all-categories revenue total inherits this exclusion silently too.

jared added the south-guide severity/high verdict/defect labels 2026-08-05 12:36:40 -04:00

jared commented

2026-08-05 12:52:50 -04:00

Withdrawing this. It is not a defect — the behaviour is a documented, deliberate ruling with a working remedy, and I filed it without reading the ruling first.

What I got wrong

cog_pipeline/docs/phase_r_harmonization_review.md §0.2 names B89, C89, D89 explicitly among the codes that are is_aggregate = TRUE, and rules:

the generic recipe join must NOT filter is_aggregate … (only there; the basis views keep it)

So revenue_long's NOT is_aggregate is the ruling working as intended, not an oversight. §2 then ships the remedy — five ig_{federal,state,local}_?89_wide / ige_{state,local}_?89_wide recipes — and §0.3 specifies that .build_suggestions() should surface them.

All three parts verified working

The recipe returns the money:

curl -s "$B/governments/132121194678/revenue?years=2022&recipe=ig_federal_b89_wide" \
  | jq -c '.data[] | [.category, .amt_nominal, .codes_included]'
# ["Federal IG revenue other/combined (B89 wide)", 94119000, "B89"]

And the signposting fires, naming the right recipe and the exact amount:

category = "IG Federal"  rows=0  suggestions=1  -> ig_federal_b89_wide  $94,119,000
category = "IG State"    rows=0  suggestions=1  -> ig_state_c89_wide    $72,974,000
category = "IG Local"    rows=0  suggestions=1  -> ig_local_d89_wide    $225,726,000

Those are precisely the three figures I reported as "silently dropped". They are not silent; I did not look where the signpost is emitted.

The actual mistake

I queried revenue_concept="general" without a category. .build_suggestions() returns early when category is NULL (R/suggestions.R:68) — a deliberate pre-existing design decision — so an unscoped query yields no suggestions. I read that documented silence as an undisclosed exclusion and generalised it to "$424.88B dropped for 35,040 governments with no indication".

The dollar figures in the original report are arithmetically correct. The characterisation built on them is not: the money is reachable, classified, and signposted whenever the caller scopes the query the way the signposting was designed for.

One thing genuinely improved by adjacent work

uscogdata PR #37 (feat/all-categories-37) extends the same signposting to the all-categories path, which previously could never fire because "All Categories" is not a crosswalk row. Verified against the full corpus:

category = NULL              total=1,627,842,000  suggestions=0   (unchanged, by design)
category = "All Categories"  total=1,627,842,000  suggestions=3
    -> ig_federal_b89_wide  $94,119,000
    -> ig_local_d89_wide   $225,726,000
    -> ig_state_c89_wide    $72,974,000

So a caller asking for a revenue total now gets the disclosure automatically.

Residual, if anything

The only thing left that is arguably worth tracking is the unscoped (category = NULL) case staying silent — which is a deliberate decision, not a defect, and is now the one remaining shape where a caller can total local-government revenue without meeting the signpost. Not reopening for it; noting it in case that decision is ever revisited.

Closing. My apologies for the noise — this is exactly the case phase_r_harmonization_review.md exists to prevent, and I should have read it before filing.

**Withdrawing this. It is not a defect — the behaviour is a documented, deliberate ruling with a working remedy, and I filed it without reading the ruling first.** ## What I got wrong `cog_pipeline/docs/phase_r_harmonization_review.md` §0.2 names `B89, C89, D89` explicitly among the codes that are `is_aggregate = TRUE`, and rules: > the generic recipe join must NOT filter `is_aggregate` … (only there; **the basis views keep it**) So `revenue_long`'s `NOT is_aggregate` is the ruling working as intended, not an oversight. §2 then ships the remedy — five `ig_{federal,state,local}_?89_wide` / `ige_{state,local}_?89_wide` recipes — and §0.3 specifies that `.build_suggestions()` should surface them. ## All three parts verified working The recipe returns the money: ```bash curl -s "$B/governments/132121194678/revenue?years=2022&recipe=ig_federal_b89_wide" \ | jq -c '.data[] | [.category, .amt_nominal, .codes_included]' # ["Federal IG revenue other/combined (B89 wide)", 94119000, "B89"] ``` And the signposting fires, naming the right recipe and the exact amount: ``` category = "IG Federal" rows=0 suggestions=1 -> ig_federal_b89_wide $94,119,000 category = "IG State" rows=0 suggestions=1 -> ig_state_c89_wide $72,974,000 category = "IG Local" rows=0 suggestions=1 -> ig_local_d89_wide $225,726,000 ``` Those are precisely the three figures I reported as "silently dropped". They are not silent; I did not look where the signpost is emitted. ## The actual mistake I queried `revenue_concept="general"` **without a `category`**. `.build_suggestions()` returns early when `category` is `NULL` (`R/suggestions.R:68`) — a deliberate pre-existing design decision — so an unscoped query yields no suggestions. I read that documented silence as an undisclosed exclusion and generalised it to "$424.88B dropped for 35,040 governments with no indication". The dollar figures in the original report are arithmetically correct. The characterisation built on them is not: the money is reachable, classified, and signposted whenever the caller scopes the query the way the signposting was designed for. ## One thing genuinely improved by adjacent work uscogdata PR #37 (`feat/all-categories-37`) extends the same signposting to the all-categories path, which previously could never fire because `"All Categories"` is not a crosswalk row. Verified against the full corpus: ``` category = NULL total=1,627,842,000 suggestions=0 (unchanged, by design) category = "All Categories" total=1,627,842,000 suggestions=3 -> ig_federal_b89_wide $94,119,000 -> ig_local_d89_wide $225,726,000 -> ig_state_c89_wide $72,974,000 ``` So a caller asking for a revenue total now gets the disclosure automatically. ## Residual, if anything The only thing left that is arguably worth tracking is the unscoped (`category = NULL`) case staying silent — which is a deliberate decision, not a defect, and is now the one remaining shape where a caller can total local-government revenue without meeting the signpost. Not reopening for it; noting it in case that decision is ever revisited. Closing. My apologies for the noise — this is exactly the case `phase_r_harmonization_review.md` exists to prevent, and I should have read it before filing.

jared closed this issue

2026-08-05 12:52:50 -04:00

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: Civilytics/uscogdata#38