Phase R2: basis= harmonized/raw, recipes, signposting (schema 4/5 dual-accept) #5

2026-07-19T10:59:21-04:00

jared commented

2026-07-19 10:59:21 -04:00

Implements Task 11 of cog_pipeline docs/superpowers/plans/2026-07-14-phase-r-year-extension.md. Checkpoint R2 approved by Jared 2026-07-19.

Schema 4/5 dual-accept; v5 harmonization views gated on manifest schema_version >= 5 (DuckDB registers views eagerly — discovered + verified).
basis = c("harmonized","raw") on cog_spending/cog_revenue; on a v4 corpus the DEFAULT gracefully resolves to raw (provenance-noted); explicit harmonized aborts cleanly. NA-harmonized rows excluded with counts in provenance.
cog_recipes() + recipe= via one generic aggregate-aware join (wide era exposes split families only as aggregate rows — data-verified); recipe results report basis="recipe" unambiguously.
Recipe-component signposting: prov$suggestions + cli message when a queried code's gap is covered by a recipe (coarse whole-result check per Checkpoint ruling; per-code refinement is R3 Task 19c).
cog_explain(): Suggestions section + series-break stories.
v5 fixture corpus regenerated: years 2011/2012/2019/2020 (the 2011->2012 aggregate->leaf seam is the load-bearing offline test; Broward pinned); real-SQL predicate tests (each view predicate independently falsified).

Suite: 342 -> 466 PASS / 0 FAIL / 0 WARN / 0 SKIP, fully offline. Task review: approved after one fix round (independently verified).

MERGE ORDER: this PR -> cog-api R2 PR -> cog_pipeline R2 PR.

Implements Task 11 of cog_pipeline docs/superpowers/plans/2026-07-14-phase-r-year-extension.md. Checkpoint R2 approved by Jared 2026-07-19. - Schema 4/5 dual-accept; v5 harmonization views gated on manifest schema_version >= 5 (DuckDB registers views eagerly — discovered + verified). - basis = c("harmonized","raw") on cog_spending/cog_revenue; on a v4 corpus the DEFAULT gracefully resolves to raw (provenance-noted); explicit harmonized aborts cleanly. NA-harmonized rows excluded with counts in provenance. - cog_recipes() + recipe= via one generic aggregate-aware join (wide era exposes split families only as aggregate rows — data-verified); recipe results report basis="recipe" unambiguously. - Recipe-component signposting: prov$suggestions + cli message when a queried code's gap is covered by a recipe (coarse whole-result check per Checkpoint ruling; per-code refinement is R3 Task 19c). - cog_explain(): Suggestions section + series-break stories. - v5 fixture corpus regenerated: years 2011/2012/2019/2020 (the 2011->2012 aggregate->leaf seam is the load-bearing offline test; Broward pinned); real-SQL predicate tests (each view predicate independently falsified). Suite: 342 -> 466 PASS / 0 FAIL / 0 WARN / 0 SKIP, fully offline. Task review: approved after one fix round (independently verified). MERGE ORDER: this PR -> cog-api R2 PR -> cog_pipeline R2 PR.

jared added 3 commits 2026-07-19 10:59:21 -04:00

feat: basis= harmonized/raw with v4/v5 dual-accept 7818cd2b1a

Adds schema_version 5 support alongside the existing v4 corpus:
.validate_schema() now accepts a supported set (4, 5) instead of a single
expected version, and cog_spending()/cog_revenue() gain basis =
c("harmonized", "raw"). Harmonized basis routes to new
spending_annotated_harmonized / revenue_annotated_harmonized views built on
spending_long_harmonized / revenue_long_harmonized (REPLACE(harmonized_code
AS item_code), excluding aggregate and NA-harmonized rows); raw basis is
byte-identical to the pre-Phase-R2 behavior. On a v4 corpus, an unspecified
basis silently resolves to "raw" with a provenance note; an explicit
basis = "harmonized" aborts with an actionable message.

Provenance gains basis, basis_note, and a harmonization block
(applied/na_rows_excluded/na_amount_excluded). The five new schema-v5-only
SQL views (harmonized long/annotated views, harmonization_map,
harmonization_recipes, series_breaks_pq) are registered conditionally on
manifest$schema_version >= 5, since DuckDB's read_parquet() errors eagerly
at CREATE VIEW time when the backing file doesn't exist on a v4 corpus.

Fixture corpus regenerated to schema_version 5 / years 2011, 2012, 2019,
2020 (2011->2012 spans the wide-aggregate -> modern-leaf format boundary
needed for the harmonization/recipe work), with the harmonization_map /
harmonization_recipes / series_breaks parquet tables bundled alongside the
existing metadata registries.

feat: cog_recipes + recipe= + signposting suggestions 4de915b557

Adds cog_recipes() to list the curated harmonization_recipes catalog (24
recipes / schema_version >= 5), and a recipe= argument on cog_spending()/
cog_revenue() that runs a recipe's generic multi-code join instead of the
category view: SUM(amt * weight) across whichever component codes are
present for a (year, canonical_govid), scoped by gov_type_scope. The join
deliberately does not filter is_aggregate -- the wide era (<= 2011) exposes
these split families (corrections 04+05, IG *89/*47, U4- rents, etc.) ONLY
as aggregate rows, with leaf codes first appearing in 2012, so excluding
aggregates would zero out the wide-era half of every recipe. This is safe
by corpus construction: wide-era rows are aggregate-only, modern rows are
leaf-only, and every component is year-scoped, so there is no
double-counting. recipe= is mutually exclusive with category=; the result's
subtype column reads "recipe" and category reads the recipe's label.

Adds recipe-component-driven signposting: when a basis="harmonized" +
category query comes back with zero rows in a requested year, and a
harmonization recipe covering that category would actually produce rows
for this government in that year (via the same join .run_recipe() uses),
the recipe is surfaced in provenance$suggestions plus one
cli::cli_inform() message. This is deliberately keyed off recipe
components rather than harmonization_map's suggested_recipe_id column
(which is empty on every live row -- the wide era's split families are
NA-by-construction via aggregate exclusion, not an NA ruling to hang a
suggestion off of).

Also populates the previously-always-empty provenance$series_break_refs
(schema v5 only: series_breaks_pq rows whose fin_code is among the
observed codes and whose break_year falls in the requested span), and
extends cog_explain() with Basis/Harmonization/Recipe/Suggestions/Series
breaks sections.

fix: exercise real harmonized-view SQL in tests; unambiguous recipe provenance

R-CMD-check / check (pull_request) Successful in 3m5s

Details

R-CMD-check / check (push) Successful in 3m1s

Details

77f48047b1

test-views.R's harmonized-view test previously ran a hand-rolled REPLACE
query with no WHERE clause, so a regression in any of
inst/sql/22-spending_long_harmonized.sql / 23-revenue_long_harmonized.sql's
three predicates (NOT is_aggregate, harmonized_code IS NOT NULL, the
E/F/G/K or T/A/U/B/C/D prefix filter) would go uncaught. Replaced it with a
test that reads the real SQL files off disk, substitutes {url} exactly as
.register_views() does, and executes them (plus their 10-long.sql
dependency) against a synthetic hive-partitioned parquet tree written via
DuckDB's own COPY ... TO (FORMAT PARQUET) (no arrow dependency, matching
this package's existing convention). Ten rows are crafted so each predicate
is independently falsifiable by a specific row; manually broke each
predicate in turn to confirm the test fails exactly as expected, then
restored the SQL files (see the task report for the RED-phase transcript).

Also fixes a provenance ambiguity: a recipe= query bypasses
spending_annotated(_harmonized)/revenue_annotated(_harmonized) entirely
(.run_recipe() joins `long` directly), so basis= has no effect on it, but
provenance was still reporting basis = "harmonized"/"raw" (whatever the
argument resolved to) with harmonization$applied = FALSE alongside it --
misleading, since it looks like harmonization was evaluated and found
nothing to exclude rather than "not applicable here." Recipe results now
report basis = "recipe" with an inert harmonization block carrying an
explicit note, regardless of what basis= was passed.

jared merged commit b0df1ec668 into main

2026-07-19 11:05:05 -04:00

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: Civilytics/uscogdata#5