---
title: "Test Release — gate latest.txt on query suite"
calcofi:
target_name: test_release
workflow_type: test
dependency:
- release_database
output: _output/test_release.html
execute:
echo: true
message: true
warning: true
editor_options:
chunk_output_type: console
format:
html:
code-fold: true
editor:
markdown:
wrap: 72
---
## Overview {.unnumbered}
**Goal**: Promote a freshly-built release to `latest.txt` only when every
pre-baked query in [CalCOFI/db-query](https://github.com/CalCOFI/db-query) runs
cleanly against it.
Pipeline position: this notebook runs after `release_database.qmd` has
uploaded the parquet, `metadata.json`, `relationships.json`, `erd.mmd`, and
`catalog.json` to `gs://calcofi-db/ducklake/releases/{release_version}/`,
but **before** `latest.txt` is updated. Every `.md` query in the
[`_queries/`](https://github.com/CalCOFI/db-query/tree/main/_queries) folder
is rendered with its YAML defaults, executed against the just-uploaded
release on GCS, and reported below. If every query returns without error,
`latest.txt` is written; otherwise the target fails and consumer apps
keep pointing at the previously-promoted version.
## Setup
```{r}
#| label: setup
devtools::load_all(here::here("../calcofi4db"))
librarian::shelf(
CalCOFI / calcofi4db,
DBI,
duckdb,
dplyr,
DT,
fs,
glue,
here,
jsonlite,
purrr,
tibble,
yaml,
quiet = T)
# which release to test? read the just-uploaded version from the local
# release directory (most recent date-stamped folder). A STAGING run
# (CALCOFI_RELEASE_PREFIX=ducklake-staging/releases) keeps its sidecars under
# data/releases-staging/ — the same rule release_database.qmd applies — so the
# version MUST be picked there: until 2026-09-04 this always read data/releases/,
# and the 2026-08-28 staging run "passed" by testing the promoted v2026.08.25
# instead of the staging catalog (no test_results.json ever landed under
# data/releases-staging/). A false green is worse than a failure.
release_prefix <- Sys.getenv("CALCOFI_RELEASE_PREFIX", "ducklake/releases")
staging <- release_prefix != "ducklake/releases"
releases_dir <- here(if (staging) "data/releases-staging" else "data/releases")
if (staging) message(glue("STAGING run: testing the release under {releases_dir}"))
release_dirs <- list.dirs(releases_dir, recursive = FALSE)
release_dirs <- release_dirs[grepl("v[0-9]{4}[.][0-9]{2}[.]?[0-9]*$",
basename(release_dirs))]
stopifnot(length(release_dirs) > 0)
release_version <- basename(release_dirs[which.max(file.mtime(release_dirs))])
message(glue("Testing release: {release_version}"))
# where the pre-baked queries live
queries_dir <- here("../db-query/_queries")
if (!dir.exists(queries_dir)) {
stop(glue(
"Could not find {queries_dir}. Clone CalCOFI/db-query as a sibling of ",
"this repo, or set queries_dir manually."))
}
message(glue("Query repo: {queries_dir}"))
# fresh in-memory DuckDB with httpfs + spatial — every query reads parquet
# from GCS over httpfs, mirroring what the query app's DuckDB-WASM does
con_test <- get_duckdb_con(":memory:")
load_duckdb_extension(con_test, "httpfs")
load_duckdb_extension(con_test, "spatial")
```
## Render the query templates
The query app uses Handlebars at runtime. For tests we replicate the
subset of Handlebars features actually used in `_queries/*.md`:
- `{var}` — simple substitution
- `{{var}}` — raw substitution (no escaping; same effect here)
- `{sqlesc var}` — escape single quotes for SQL string literals
- `{#if var}...{{/if}}` — include block when `var` is truthy
```{r}
#| label: render_helpers
sqlesc <- function(x) gsub("'", "''", as.character(x %||% ""), fixed = TRUE)
# truthy test mirrors Handlebars: non-NULL, non-NA, non-empty string,
# non-zero number, non-FALSE logical
is_truthy <- function(val) {
if (is.null(val)) return(FALSE)
if (length(val) == 0) return(FALSE)
if (is.logical(val)) return(isTRUE(val))
if (is.character(val)) return(any(!is.na(val) & nzchar(val)))
if (is.numeric(val)) return(any(!is.na(val) & val != 0))
!is.na(val[[1]])
}
# resolve innermost {{#if X}}...{{/if}} repeatedly until none remain.
# [\\s\\S] (not .) so the body spans newlines; (?!\\{\\{#if) guard forces
# "innermost only" so we strip from the inside out.
render_if_blocks <- function(txt, params) {
pat <- "\\{\\{#if\\s+([A-Za-z_][A-Za-z0-9_]*)\\}\\}((?:(?!\\{\\{#if)[\\s\\S])*?)\\{\\{/if\\}\\}"
repeat {
m <- regexpr(pat, txt, perl = TRUE)
if (m == -1) break
full <- regmatches(txt, m)
parts <- regmatches(full, regexec(pat, full, perl = TRUE))[[1]]
var_name <- parts[2]
body <- parts[3]
replacement <- if (is_truthy(params[[var_name]])) body else ""
txt <- paste0(
substr(txt, 1, m - 1),
replacement,
substr(txt, m + attr(m, "match.length"), nchar(txt)))
}
txt
}
render_template <- function(template, params) {
txt <- template
# 1. {{#if X}}...{{/if}} blocks
txt <- render_if_blocks(txt, params)
# 2. {{sqlesc VAR}}
helper_pat <- "\\{\\{\\s*sqlesc\\s+([A-Za-z_][A-Za-z0-9_]*)\\s*\\}\\}"
repeat {
m <- regexpr(helper_pat, txt, perl = TRUE)
if (m == -1) break
full <- regmatches(txt, m)
name <- sub(helper_pat, "\\1", full, perl = TRUE)
txt <- sub(full, sqlesc(params[[name]]), txt, fixed = TRUE)
}
# 3. {{{VAR}}} (triple-brace, raw) and {{VAR}} (simple) — handle the
# triple-brace first so the {{VAR}} pass doesn't eat the inner pair
triple_pat <- "\\{\\{\\{\\s*([A-Za-z_][A-Za-z0-9_]*)\\s*\\}\\}\\}"
repeat {
m <- regexpr(triple_pat, txt, perl = TRUE)
if (m == -1) break
full <- regmatches(txt, m)
name <- sub(triple_pat, "\\1", full, perl = TRUE)
val <- as.character(params[[name]] %||% "")
txt <- sub(full, val, txt, fixed = TRUE)
}
single_pat <- "\\{\\{\\s*([A-Za-z_][A-Za-z0-9_]*)\\s*\\}\\}"
repeat {
m <- regexpr(single_pat, txt, perl = TRUE)
if (m == -1) break
full <- regmatches(txt, m)
name <- sub(single_pat, "\\1", full, perl = TRUE)
val <- as.character(params[[name]] %||% "")
txt <- sub(full, val, txt, fixed = TRUE)
}
# query-app convention: literal __VERSION__ placeholder -> release version,
# mirroring _includes/form-field.html ({% replace "__VERSION__",
# site.default_version %}). Used in free-form defaults like sql-shell/shell.md.
if (!is.null(params$version)) {
txt <- gsub("__VERSION__", as.character(params$version), txt, fixed = TRUE)
# query-app convention since the content-addressed releases: __TBL:obs__ (or
# __TBL:obs:dataset_key=calcofi_bottle__ for one partition) -> the
# read_parquet(...) resolved through that version's catalog, mirroring
# db-query lib/release.js. Never a hand-built releases/{v}/parquet path.
tbl_pat <- "__TBL:([A-Za-z0-9_]+)(?::([A-Za-z0-9_]+)=([^_]+(?:_[^_]+)*?))?__"
repeat {
m <- regexpr(tbl_pat, txt, perl = TRUE)
if (m == -1) break
full <- regmatches(txt, m)
tb <- sub(tbl_pat, "\\1", full, perl = TRUE)
col <- sub(tbl_pat, "\\2", full, perl = TRUE)
val <- sub(tbl_pat, "\\3", full, perl = TRUE)
txt <- sub(full, tbl_sql(as.character(params$version), tb,
if (nzchar(col)) col else NULL, if (nzchar(val)) val else NULL),
txt, fixed = TRUE)
}
}
txt
}
# read_parquet(...) for a table (or one partition of it) in a release, through
# the catalog; one catalog fetch per version. objects[].path is bucket-relative,
# so the staging prefix resolves the same way. A catalog VIEW (`obs` over
# obs_bio + obs_env since D-S1, calcofi4db 3.31.0) expands to its SQL over the
# objects of the tables it reads, parenthesised — exactly what db-query's
# readParquetFor() does for `__TBL:obs__`, so this suite exercises the view the
# browser will run; a partition token on a view becomes a WHERE on it.
.tbl_cats <- new.env(parent = emptyenv())
tbl_sql <- function(version, table, col = NULL, val = NULL) {
if (is.null(.tbl_cats[[version]])) {
pre <- Sys.getenv("CALCOFI_RELEASE_PREFIX", "ducklake/releases")
.tbl_cats[[version]] <- jsonlite::fromJSON(
glue("https://storage.googleapis.com/calcofi-db/{pre}/{version}/catalog.json"),
simplifyVector = FALSE)
}
cat_ <- .tbl_cats[[version]]
if (table %in% names(calcofi4r::cc_catalog_views(cat_))) {
v <- paste0("(", calcofi4r::cc_view_sql(cat_, table, function(t) tbl_sql(version, t)), ")")
if (!is.null(col)) v <- glue("(SELECT * FROM {v} WHERE \"{col}\" = '{val}')")
return(v)
}
src <- calcofi4r::cc_release_sources(cat_, table)
if (!is.null(col)) {
keep <- grepl(glue("/{col}={val}/"), src$urls, fixed = TRUE)
if (isTRUE(src$canonical) && any(keep)) src$urls <- src$urls[keep]
else if (!isTRUE(src$canonical)) # legacy layout: the partition's own path
src$urls <- sub("/\\*\\*/\\*\\.parquet$", glue("/{col}={val}/*.parquet"), src$urls)
}
calcofi4r::cc_read_parquet_sql(src)
}
# parse YAML front-matter + body from a query .md file
parse_query_md <- function(path) {
raw <- readLines(path, warn = FALSE)
delims <- which(trimws(raw) == "---")
if (length(delims) < 2) {
stop(glue("Missing YAML delimiters in {path}"))
}
yaml_text <- paste(raw[(delims[1] + 1):(delims[2] - 1)], collapse = "\n")
meta <- yaml::yaml.load(yaml_text)
body <- paste(raw[(delims[2] + 1):length(raw)], collapse = "\n")
list(meta = meta, body = body, path = path)
}
# build the params list for a query: each parameter's `default`, with
# `version` overridden to the release under test
defaults_for <- function(meta, release_version) {
params <- list(version = release_version)
if (!is.null(meta$parameters)) {
for (name in names(meta$parameters)) {
spec <- meta$parameters[[name]]
params[[name]] <- spec$default
}
}
params$version <- release_version
params
}
```
## Discover and execute every query
```{r}
#| label: execute
query_files <- list.files(queries_dir, pattern = "[.]md$",
recursive = TRUE, full.names = TRUE)
message(glue("Found {length(query_files)} query files"))
results <- purrr::map_dfr(query_files, function(qpath) {
rel <- sub(paste0(normalizePath(queries_dir), "/"), "",
normalizePath(qpath), fixed = TRUE)
q <- parse_query_md(qpath)
meta <- q$meta
# sql_builder queries are JS-only — flag them; the query app exercises
# them in the browser
if (!is.null(meta$sql_builder)) {
return(tibble::tibble(
query = rel,
label = meta$label %||% NA_character_,
status = "skip",
reason = paste0("sql_builder=", meta$sql_builder),
rows = NA_integer_,
ms = NA_real_,
error = NA_character_))
}
if (is.null(meta$sql)) {
return(tibble::tibble(
query = rel, label = meta$label %||% NA_character_,
status = "skip", reason = "no inline sql",
rows = NA_integer_, ms = NA_real_, error = NA_character_))
}
params <- defaults_for(meta, release_version)
sql <- render_template(meta$sql, params)
t0 <- Sys.time()
out <- tryCatch(
DBI::dbGetQuery(con_test, sql),
error = function(e) e)
elapsed_ms <- as.numeric(difftime(Sys.time(), t0, units = "secs")) * 1000
if (inherits(out, "error")) {
tibble::tibble(
query = rel, label = meta$label %||% NA_character_,
status = "fail", reason = NA_character_,
rows = NA_integer_, ms = round(elapsed_ms, 1),
error = conditionMessage(out))
} else {
tibble::tibble(
query = rel, label = meta$label %||% NA_character_,
status = "pass", reason = NA_character_,
rows = nrow(out), ms = round(elapsed_ms, 1),
error = NA_character_)
}
})
# status badges for the report
status_badge <- function(s) {
color <- c(pass = "#2e7d32", fail = "#c62828", skip = "#757575")[s]
glue("<span style='background:{color};color:#fff;padding:2px 8px;",
"border-radius:10px;font-size:11px;'>{toupper(s)}</span>")
}
results |>
mutate(status_html = status_badge(status)) |>
select(status_html, query, label, rows, ms, reason, error) |>
DT::datatable(
escape = FALSE,
rownames = FALSE,
options = list(pageLength = 20, order = list(list(0, "asc"))),
colnames = c("", "query", "label", "rows", "ms", "skip reason", "error"))
```
## Consumer contract queries
The `_queries/*.md` suite covers the query app, but the `sql_builder` (match) and
the reproducible-download / app SQL live in calcofi4r + the Shiny apps and are
otherwise only exercised in the browser — so a schema change (a renamed column, a
retired table) slips past the gate and breaks downloads/apps in production. These
**contract** queries pin the exact shapes those consumers depend on (bio↔env
match, station rollup, sample/cruise counts) plus core-integrity asserts, run
server-side against the frozen release, and feed the same promote gate.
```{r}
#| label: consumer_contract
# tables resolved through this release's catalog (content-addressed since
# v2026.09; objects[].path is bucket-relative, so the staging prefix works too),
# exactly as calcofi4r::cc_get_db() and every migrated consumer resolve them
rel_prefix <- Sys.getenv("CALCOFI_RELEASE_PREFIX", "ducklake/releases")
rel_cat <- jsonlite::fromJSON(
glue("https://storage.googleapis.com/calcofi-db/{rel_prefix}/{release_version}/catalog.json"),
simplifyVector = FALSE)
rp <- function(t) calcofi4r::cc_read_parquet_sql(calcofi4r::cc_release_sources(rel_cat, t))
# the catalog's `obs` VIEW over obs_bio + obs_env (D-S1, calcofi4db 3.31.0), as cc_get_db() and
# db-query serve `obs` from this release on: its SQL over the pair's objects, parenthesised so it
# stands wherever rp('obs') stood. Every contract row that reads obs runs twice — against the
# deprecated table's own objects (rp) and against the view (rpv) — until the objects are dropped.
rpv <- function(t) paste0("(", calcofi4r::cc_view_sql(rel_cat, t, rp), ")")
# a partitioned table through its single-file twin (obs publishes one): the table's own column order
rp1 <- function(t) calcofi4r::cc_read_parquet_sql(calcofi4r::cc_release_sources(rel_cat, t), prefer_single_file = TRUE)
has_obs_view <- "obs" %in% names(calcofi4r::cc_catalog_views(rel_cat))
stopifnot(has_obs_view) # the catalog must carry the view: consumers read obs through it
# the rows that read obs, parameterised on HOW obs is read (R = rp or rpv) and tagged
obs_rows <- function(R, tag) tibble::tribble(
~name, ~expect, ~sql,
glue("consumer: match env (bottle temperature) [{tag}]"), "nonzero", glue(
"SELECT count(*) n FROM {R('obs')}
WHERE realm='env' AND dataset_key='calcofi_bottle' AND measurement_type='temperature'"),
glue("consumer: match/download bio (ichthyo abundance x taxon x effort) [{tag}]"), "nonzero", glue(
"SELECT count(*) n FROM {R('obs')} o
JOIN {rp('taxon')} t ON t.taxon_key = o.taxon_key
LEFT JOIN {rp('sample_measurement')} shf ON shf.sample_key=o.sample_key AND shf.measurement_type='std_haul_factor'
WHERE o.realm='bio' AND o.dataset_key='swfsc_ichthyo' AND o.measurement_type='abundance'"),
glue("integrity: every non-null obs.taxon_key resolves to taxon [{tag}]"), "zero", glue(
"SELECT count(*) n FROM {R('obs')} o
WHERE o.taxon_key IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM {rp('taxon')} t WHERE t.taxon_key=o.taxon_key)"),
# db-viz-hex::get_sp() picks taxa by worms_id and walks the WoRMS hierarchy for
# children. Seabirds and marine mammals key `itis:` (WoRMS bird taxonomy lags),
# and nothing populated their worms_id COLUMN — so every one of them returned
# ZERO rows: 59,858 of 64,956 Farallon obs, 92.2% of the dataset, with no error
# anywhere. Resolving a taxon and making it REACHABLE are different things;
# assert the second.
glue("consumer: seabird/mammal taxa reachable by worms_id (db-viz-hex get_sp) [{tag}]"), "nonzero", glue(
"SELECT count(*) n FROM {R('obs')} o
JOIN {rp('taxon')} t ON t.taxon_key = o.taxon_key
WHERE o.dataset_key='farallon_bird-mammal' AND t.worms_id IS NOT NULL"),
# `o.taxon_key IS NOT NULL` is load-bearing, not defensive: 1,316 of this
# dataset's 66,272 obs carry NO taxon at all — species codes the source marks
# Include=0 (fish, and non-target sightings) — and a LEFT JOIN reports those as
# a NULL worms_id too. Without the filter this asserts "every observation has a
# taxon", which is a different (and false) claim than the one intended.
# birds key on ITIS by rule (class Aves, calcofi4db >= 3.29.0), and WoRMS has no record at all for
# some of them — Larus brachyrhynchus and the subspecies Hydrobates leucorhous chapmani, 123
# farallon rows at v2026.09.04 — so "reachable by worms_id" stopped being the invariant. What
# must never happen again is a farallon observation with NO authority id (59,858 rows in 2026-07).
glue("integrity: no farallon obs left unreachable by an authority id (worms_id or itis_id) [{tag}]"), "zero", glue(
"SELECT count(*) n FROM {R('obs')} o
LEFT JOIN {rp('taxon')} t ON t.taxon_key = o.taxon_key
WHERE o.dataset_key='farallon_bird-mammal'
AND o.taxon_key IS NOT NULL AND t.worms_id IS NULL AND t.itis_id IS NULL"),
glue("consumer: station coverage rollup (obs GROUP BY grid,dataset) [{tag}]"), "nonzero", glue(
"SELECT count(*) n FROM (
SELECT grid_key, dataset_key, count(*), count(DISTINCT sample_key), count(DISTINCT cruise_key)
FROM {R('obs')} WHERE grid_key IS NOT NULL GROUP BY 1,2)"),
glue("consumer: obs readable over HTTPS as the browser reads it (db-query match.js) [{tag}]"), "nonzero", glue(
"SELECT count(*) n FROM {R('obs')} WHERE realm='bio' LIMIT 1"),
glue("contract: obs.measurement_type all in registry [{tag}]"), "zero", glue(
"SELECT count(*) n FROM {R('obs')}
WHERE measurement_type NOT IN (SELECT measurement_type FROM {rp('measurement_type')})"),
glue("contract: obs.sample_key resolves in sample [{tag}]"), "zero", glue(
"SELECT count(*) n FROM {R('obs')} WHERE sample_key NOT IN (SELECT sample_key FROM {rp('sample')})"),
glue("contract: obs.hex_id present where lat/lng [{tag}]"), "zero", glue(
"SELECT count(*) n FROM {R('obs')} WHERE hex_id IS NULL AND latitude IS NOT NULL AND longitude IS NOT NULL"))
# the same contract, read from the pair DIRECTLY under its own names (value, no realm) — what a
# migrated consumer writes; obs_bio carries the effort inline, so the bio row needs no join
pair_rows <- tibble::tribble(
~name, ~expect, ~sql,
"consumer: match env (bottle temperature) [obs_env]", "nonzero", glue(
"SELECT count(*) n FROM {rp('obs_env')}
WHERE dataset_key='calcofi_bottle' AND measurement_type='temperature' AND value IS NOT NULL"),
"consumer: match/download bio (ichthyo abundance x taxon x effort) [obs_bio]", "nonzero", glue(
"SELECT count(*) n FROM {rp('obs_bio')} o JOIN {rp('taxon')} t ON t.taxon_key = o.taxon_key
WHERE o.dataset_key='swfsc_ichthyo' AND o.measurement_type='abundance' AND o.std_haul_factor IS NOT NULL"),
"integrity: every non-null obs_bio.taxon_key resolves to taxon [obs_bio]", "zero", glue(
"SELECT count(*) n FROM {rp('obs_bio')} o
WHERE o.taxon_key IS NOT NULL AND NOT EXISTS (SELECT 1 FROM {rp('taxon')} t WHERE t.taxon_key=o.taxon_key)"),
"consumer: seabird/mammal taxa reachable by worms_id [obs_bio]", "nonzero", glue(
"SELECT count(*) n FROM {rp('obs_bio')} o JOIN {rp('taxon')} t ON t.taxon_key = o.taxon_key
WHERE o.dataset_key='farallon_bird-mammal' AND t.worms_id IS NOT NULL"),
"integrity: no farallon obs_bio left unreachable by an authority id (worms_id or itis_id) [obs_bio]", "zero", glue(
"SELECT count(*) n FROM {rp('obs_bio')} o LEFT JOIN {rp('taxon')} t ON t.taxon_key = o.taxon_key
WHERE o.dataset_key='farallon_bird-mammal' AND o.taxon_key IS NOT NULL AND t.worms_id IS NULL AND t.itis_id IS NULL"),
"consumer: station coverage rollup (obs_bio + obs_env GROUP BY grid,dataset) [pair]", "nonzero", glue(
"SELECT count(*) n FROM (
SELECT grid_key, dataset_key, count(*), count(DISTINCT sample_key), count(DISTINCT cruise_key)
FROM (SELECT grid_key, dataset_key, sample_key, cruise_key FROM {rp('obs_bio')}
UNION ALL SELECT grid_key, dataset_key, sample_key, cruise_key FROM {rp('obs_env')})
WHERE grid_key IS NOT NULL GROUP BY 1,2)"),
"consumer: obs_bio single object readable over HTTPS (Explorer, db-query) [obs_bio]", "nonzero", glue(
"SELECT count(*) n FROM {rp('obs_bio')} LIMIT 1"),
"contract: pair measurement_type all in registry [pair]", "zero", glue(
"SELECT count(*) n FROM (SELECT measurement_type FROM {rp('obs_bio')} UNION ALL SELECT measurement_type FROM {rp('obs_env')})
WHERE measurement_type NOT IN (SELECT measurement_type FROM {rp('measurement_type')})"),
"contract: pair sample_key resolves in sample [pair]", "zero", glue(
"SELECT count(*) n FROM (SELECT sample_key FROM {rp('obs_bio')} UNION ALL SELECT sample_key FROM {rp('obs_env')})
WHERE sample_key NOT IN (SELECT sample_key FROM {rp('sample')})"),
"contract: pair hex_id present where lat/lng [pair]", "zero", glue(
"SELECT count(*) n FROM (SELECT hex_id, latitude, longitude FROM {rp('obs_bio')} UNION ALL SELECT hex_id, latitude, longitude FROM {rp('obs_env')})
WHERE hex_id IS NULL AND latitude IS NOT NULL AND longitude IS NOT NULL"),
# the view IS obs: same rows per realm (the gate release_database.qmd ran per dataset with a signature)
"integrity: the obs view reproduces obs row counts (bio = obs_bio, env = obs_env)", "zero", glue(
"SELECT abs((SELECT count(*) FROM {rp('obs')} WHERE realm='bio') - (SELECT count(*) FROM {rp('obs_bio')}))
+ abs((SELECT count(*) FROM {rp('obs')} WHERE realm='env') - (SELECT count(*) FROM {rp('obs_env')}))
+ abs((SELECT count(*) FROM {rp('obs')}) - (SELECT count(*) FROM {rpv('obs')})) AS n"),
# against the single-file twin, which carries the TABLE's column order; a read over the hive
# partition list appends the partition column (dataset_key) last, which is a reader artefact
"integrity: the obs view has obs's 18 columns in order", "zero", glue(
"SELECT count(*) n FROM (
SELECT row_number() OVER () AS i, column_name FROM (DESCRIBE SELECT * FROM {rpv('obs')})) v
FULL JOIN (
SELECT row_number() OVER () AS i, column_name FROM (DESCRIBE SELECT * FROM {rp1('obs')})) o USING (i)
WHERE v.column_name IS DISTINCT FROM o.column_name"))
# name | expect ('nonzero' rows for a consumer read, or 'zero' for an integrity
# assert) | SQL returning a single column `n`
contract <- tibble::tribble(
~name, ~expect, ~sql,
# The spatial tables had NO contract coverage at all until 2026-08-03, which is
# why renaming _spatial -> spatial and _spatial_attr -> spatial_attribute could
# not have been caught by the gate that exists to catch exactly that. These read
# them the way a consumer does — including the JOIN KEY, since `id` is per-layer
# and joining on it alone silently mixes layers.
"consumer: spatial layer with attributes (joined on spatial_key)", "nonzero", glue(
"SELECT count(*) n FROM {rp('spatial')} s
JOIN {rp('spatial_attribute')} a USING (spatial_key)
WHERE s.layer = 'Marine Protected Areas'"),
"integrity: every spatial_attribute row resolves to a spatial feature", "zero", glue(
"SELECT count(*) n FROM {rp('spatial_attribute')} a
WHERE NOT EXISTS (SELECT 1 FROM {rp('spatial')} s WHERE s.spatial_key = a.spatial_key)"),
"integrity: spatial_key is unique", "zero", glue(
"SELECT count(*) n FROM (SELECT spatial_key FROM {rp('spatial')}
GROUP BY 1 HAVING count(*) > 1)"),
# a sample-to-polygon join is the thing consumers actually do, and it ERRORS
# outright when CRS tags disagree — which they did until v2026.08.03
"consumer: sample joins spatial (CRS tags must agree)", "nonzero", glue(
"SELECT count(*) n FROM (SELECT geom FROM {rp('sample')}
WHERE geom IS NOT NULL LIMIT 20000) s
JOIN {rp('spatial')} sp ON ST_Intersects(sp.geom, s.geom)"),
# browser-shaped objects (Explorer plan D4, Phase 1): the app fetches these whole and aggregates in
# the browser; every consumer inherits the exact polygon membership and the D8 densities
"consumer: obs_bio carries the D8 densities for the default taxon (sardine larvae per 10 m2)", "nonzero", glue(
"SELECT count(*) n FROM {rp('obs_bio')}
WHERE taxon_key = 'worms:217452' AND life_stage = 'larva' AND density_per_10m2 IS NOT NULL"),
"integrity: obs_bio effort_class is the D8 vocabulary", "zero", glue(
"SELECT count(*) n FROM {rp('obs_bio')}
WHERE effort_class NOT IN ('count_with_effort', 'raw_count_no_effort', 'density_as_published', 'other_unit')"),
"integrity: obs_bio never derives a density without effort", "zero", glue(
"SELECT count(*) n FROM {rp('obs_bio')}
WHERE effort_class = 'raw_count_no_effort' AND (density_per_10m2 IS NOT NULL OR density_per_1000m3 IS NOT NULL)"),
"consumer: one env variable is one object (temperature, quality-filtered)", "nonzero", glue(
"SELECT count(*) n FROM {rp('obs_env')} WHERE measurement_type = 'temperature' AND qual_ok"),
"integrity: every sample_spatial root resolves to sample_root", "zero", glue(
"SELECT count(*) n FROM {rp('sample_spatial')} ss
WHERE NOT EXISTS (SELECT 1 FROM {rp('sample_root')} r WHERE r.root_id = ss.root_id)"),
"integrity: sample_spatial (root_id, spatial_key) is unique", "zero", glue(
"SELECT count(*) n FROM (SELECT root_id, spatial_key FROM {rp('sample_spatial')}
GROUP BY 1, 2 HAVING count(*) > 1)"),
"consumer: a region summary joins obs_bio to sample_spatial on root_id", "nonzero", glue(
"SELECT count(*) n FROM {rp('obs_bio')} o JOIN {rp('sample_spatial')} s USING (root_id)
WHERE s.layer = 'National Marine Sanctuaries'"),
"integrity: no non-finite coordinates in sample", "zero", glue(
"SELECT count(*) n FROM {rp('sample')}
WHERE isnan(latitude) OR isnan(longitude) OR isinf(latitude) OR isinf(longitude)"),
# (the rows that read obs live in obs_rows() above: once against the deprecated table's
# own objects, once against the catalog view; pair_rows() reads obs_bio / obs_env directly)
# an itis:-keyed taxon must KEEP its itis: key once worms_id is filled — the
# cross-reference is not a licence to re-key onto the lagging authority
"integrity: seabirds still key itis:, not worms:", "nonzero", glue(
"SELECT count(*) n FROM {rp('dataset_taxon')} dt
WHERE dt.dataset_key='farallon_bird-mammal' AND dt.taxon_key LIKE 'itis:%'"),
# taxonomic_status was the literal string "accepted" on all 2,090 taxa, stamped
# rather than fetched. A single distinct value means it has regressed to that.
"integrity: taxonomic_status is fetched, not stamped", "nonzero", glue(
"SELECT count(*) n FROM (
SELECT 1 FROM {rp('taxon')} WHERE status_checked IS NOT NULL LIMIT 1)"),
"consumer: sample event grain (bottle cast)", "nonzero", glue(
"SELECT count(*) n FROM {rp('sample')} WHERE dataset_key='calcofi_bottle' AND sample_type='cast'"),
"consumer: cruise enriched per-dataset counts", "nonzero", glue(
"SELECT count(*) n FROM {rp('cruise')} WHERE ichthyo IS NOT NULL"),
# the one baseline every anomaly subtracts (2026-08-31): read the way ctd-transects and the
# Explorer's Sections lens do — the cast's month-matched cell for the CTD headline series
"consumer: climatology month-matched cell (line 90 st 60, July, 0 m, temperature_ave)", "nonzero", glue(
"SELECT count(*) n FROM {rp('climatology')}
WHERE dataset_key = 'calcofi_ctd-cast' AND measurement_type = 'temperature_ave'
AND grid_key = 'st60-ln90' AND month = 7 AND depth_bin = 0 AND n_cruises >= 3"),
"integrity: climatology carries one window, stamped on every row", "zero", glue(
"SELECT count(*) - 1 AS n FROM (SELECT DISTINCT clim_yr_min, clim_yr_max FROM {rp('climatology')})"),
"integrity: climatology depth_bin is a 10 m floor bin within 0-500", "zero", glue(
"SELECT count(*) n FROM {rp('climatology')} WHERE depth_bin % 10 <> 0 OR depth_bin < 0 OR depth_bin > 500"),
# station_uuid (WS-B / Ed Weber's ask, calcofi4db >= 3.32.0): the SWFSC station
# occupation each event belongs to. Read the way a consumer would — obs -> its
# sample -> the ichthyo site sample.station_uuid names — and require a real
# majority of bottle casts to resolve (measured 78.0% of 35,644 at v2026.08.25;
# 25,000 is a conservative floor a schema regression would trip well before).
"consumer: obs -> sample.station_uuid resolves to an ichthyo site (bottle)", "nonzero", glue(
"SELECT CASE WHEN count(DISTINCT s.sample_key) >= 25000
THEN count(DISTINCT s.sample_key) ELSE 0 END AS n
FROM {rp('obs')} o
JOIN {rp('sample')} s ON s.sample_key = o.sample_key
JOIN {rp('sample')} i ON i.source_uuid = s.station_uuid
WHERE o.dataset_key = 'calcofi_bottle' AND s.station_uuid IS NOT NULL
AND i.dataset_key = 'swfsc_ichthyo' AND i.sample_type = 'site'"))
# obs three ways — the deprecated table's own objects, the catalog view, the pair directly — so the
# contract covers every path a consumer can take through the deprecation window (D-S1)
contract <- dplyr::bind_rows(contract, obs_rows(rp, "obs"), obs_rows(rpv, "obs view"), pair_rows)
contract_res <- purrr::pmap_dfr(contract, function(name, expect, sql) {
t0 <- Sys.time()
out <- tryCatch(DBI::dbGetQuery(con_test, sql), error = function(e) e)
ms <- as.numeric(difftime(Sys.time(), t0, units = "secs")) * 1000
if (inherits(out, "error"))
return(tibble::tibble(query = name, label = "consumer-contract", status = "fail",
reason = NA_character_, rows = NA_integer_, ms = round(ms, 1), error = conditionMessage(out)))
n <- as.numeric(out$n[1])
ok <- if (expect == "zero") n == 0 else n > 0
tibble::tibble(query = name, label = "consumer-contract",
status = if (ok) "pass" else "fail", reason = NA_character_,
rows = as.integer(n), ms = round(ms, 1),
error = if (ok) NA_character_ else glue("expected {expect}, got n={n}"))
})
# fold into the same results table the promote gate reads
results <- dplyr::bind_rows(results, contract_res)
contract_res |>
mutate(status_html = status_badge(status)) |>
select(status_html, query, rows, ms, error) |>
DT::datatable(escape = FALSE, rownames = FALSE,
options = list(pageLength = 20), colnames = c("", "contract query", "n", "ms", "error"))
```
## Dataset catalog record
`datasets.json` (calcofi4db >= 4.1.0; plan 2026-09-05 § D-1) is the one dataset list every product
reads — the landing page's dataset grid and pages, `cc_datasets()`, the sitemap, `data.json`, STAC.
Four rows join the promote gate: the file validates against `datasets.schema.json`, it declares the
**schema version this release was built with** (`1.1` from calcofi4db 4.5.0 — a record that says
`1.0` means the release ran an older builder than the checks assume), it carries every dataset
`metadata.json` carries, and `check_dataset_catalog()` reports no blocking finding (a dead endpoint
on a page is worse than absence; the network half follows `CALCOFI_SKIP_LINK_CHECK`).
Schema 1.1's three new findings are reported here with the rest and only one of them blocks:
`grain_without_description` is an **error** — the builder should never emit an ERDDAP grain nobody
has described, and a new grain means a new sentence in `erddap_grain_description()` — while
`registration_without_id` and `bbox_implausible` are warnings a human reads. `bbox_implausible` is
expected to fire for `swfsc_ichthyo` (its asserted extent reads 0–54° N × 180–77° W from bad upstream
coordinates while its sampled positions sit in the California Current; question Q16 to SWFSC), so its
count is printed rather than gated.
```{r}
#| label: dataset_catalog
ds_url <- glue("https://storage.googleapis.com/calcofi-db/{rel_prefix}/{release_version}/datasets.json")
ds_txt <- tryCatch(paste(readLines(ds_url, warn = FALSE, encoding = "UTF-8"), collapse = "\n"),
error = function(e) NULL)
cat_row <- function(name, ok, err) tibble::tibble(
query = name, label = "dataset-catalog", status = if (isTRUE(ok)) "pass" else "fail",
reason = NA_character_, rows = NA_integer_, ms = NA_real_,
error = if (isTRUE(ok)) NA_character_ else as.character(err))
catalog_res <- if (is.null(ds_txt)) {
cat_row("catalog: datasets.json present", FALSE, glue("{ds_url} not readable"))
} else {
ds_rec <- jsonlite::fromJSON(ds_txt, simplifyVector = FALSE)
meta_n <- length(jsonlite::fromJSON(
glue("https://storage.googleapis.com/calcofi-db/{rel_prefix}/{release_version}/metadata.json"),
simplifyVector = FALSE)$datasets)
schema_ok <- tryCatch(validate_dataset_catalog(ds_txt), error = function(e) conditionMessage(e))
ds_chk <- check_dataset_catalog(ds_rec, read_catalog_registries(here("metadata")),
network = !nzchar(Sys.getenv("CALCOFI_SKIP_LINK_CHECK")))
ds_bad <- ds_chk[ds_chk$level == "error" & !ds_chk$exempt, , drop = FALSE]
# the schema 1.1 findings, named so a reader sees them without opening the table
n_find <- function(f) sum(ds_chk$finding == f)
dplyr::bind_rows(
cat_row("catalog: datasets.json validates against datasets.schema.json", isTRUE(schema_ok), schema_ok),
cat_row(glue("catalog: schema_version is 1.1 [{ds_rec$schema_version %||% 'absent'}]"),
identical(ds_rec$schema_version, "1.1"),
glue("built by a calcofi4db older than 4.5.0 — the D-9 fields are absent")),
cat_row(glue("catalog: every ERDDAP grain is described ",
"(grain_without_description: {n_find('grain_without_description')}; ",
"registration_without_id: {n_find('registration_without_id')} warn; ",
"bbox_implausible: {n_find('bbox_implausible')} warn)"),
n_find("grain_without_description") == 0,
"add the grain to calcofi4db::erddap_grain_description()"),
cat_row(glue("catalog: n(datasets) == n(metadata.json datasets) [{length(ds_rec$datasets)} vs {meta_n}]"),
length(ds_rec$datasets) == meta_n, "count mismatch"),
cat_row(glue("catalog: no blocking finding (check_dataset_catalog: {nrow(ds_bad)} error, ",
"{sum(ds_chk$level == 'warn')} warn, {sum(ds_chk$exempt)} exempt)"),
nrow(ds_bad) == 0,
paste(sprintf("%s %s %s", ds_bad$dataset_key, ds_bad$finding, dplyr::coalesce(ds_bad$url, "")), collapse = "; ")))
}
results <- dplyr::bind_rows(results, catalog_res)
catalog_res |>
mutate(status_html = status_badge(status)) |>
select(status_html, query, error) |>
DT::datatable(escape = FALSE, rownames = FALSE, colnames = c("", "catalog check", "error"))
```
## Static STAC catalog
The STAC catalog `release_database.qmd` wrote to `gs://calcofi-db/stac/` (plan 2026-09-05 § D-5.3;
calcofi4db >= 4.3.0) is checked **as published**, not as built: the root, every child collection and
this release's item are fetched back from the bucket into a local mirror and run through
`check_stac()` — the structural half always, `stac-validator` (pip) over every document when it is
installed. That is what `stac-browser` at calcofi.io/stac/ will read.
```{r}
#| label: stac_catalog
stac_prefix <- if (grepl("staging", rel_prefix)) "stac-staging" else "stac"
stac_base <- glue("https://storage.googleapis.com/calcofi-db/{stac_prefix}")
dir_mirror <- file.path(tempdir(), "stac_mirror")
unlink(dir_mirror, recursive = TRUE)
get_json <- function(url) tryCatch(
paste(readLines(url, warn = FALSE, encoding = "UTF-8"), collapse = "\n"), error = function(e) NULL)
save_rel <- function(txt, rel) {
p <- file.path(dir_mirror, rel)
dir.create(dirname(p), recursive = TRUE, showWarnings = FALSE)
writeLines(txt, p)
p
}
root_txt <- get_json(glue("{stac_base}/catalog.json"))
stac_res <- if (is.null(root_txt)) {
cat_row("stac: catalog.json present", FALSE, glue("{stac_base}/catalog.json not readable"))
} else {
save_rel(root_txt, "catalog.json")
root <- jsonlite::fromJSON(root_txt, simplifyVector = FALSE)
kids <- Filter(function(l) identical(l$rel, "child"), root$links)
# the record was read above; if it was not readable this row still runs, on the root's own count
n_pub <- if (exists("ds_rec"))
sum(vapply(ds_rec$datasets, function(d) identical(d$visibility %||% "public", "public"), logical(1)))
else sum(!grepl("/layer_", vapply(kids, function(k) k$href, "")))
# mirror every child collection, and each dataset collection's item for THIS release
n_miss <- 0L
for (k in kids) {
rel <- sub(glue("^{stac_base}/"), "", k$href)
txt <- get_json(k$href)
if (is.null(txt)) { n_miss <- n_miss + 1L; next }
save_rel(txt, rel)
if (!grepl("/layer_", rel)) {
id <- basename(dirname(rel))
irel <- glue("collections/{id}/items/{release_version}.json")
itxt <- get_json(glue("{stac_base}/{irel}"))
if (is.null(itxt)) n_miss <- n_miss + 1L else save_rel(itxt, irel)
}
}
chk <- check_stac(dir_mirror, network = !nzchar(Sys.getenv("CALCOFI_SKIP_LINK_CHECK")))
bad <- chk[chk$level == "error", , drop = FALSE]
n_items <- length(list.files(dir_mirror, pattern = "^v.*[.]json$", recursive = TRUE))
dplyr::bind_rows(
cat_row(glue("stac: every child collection and this release's item fetched ({length(kids)} children)"),
n_miss == 0, glue("{n_miss} document(s) missing on the bucket")),
cat_row(glue("stac: one item per public dataset at {release_version} [{n_items} vs {n_pub}]"),
n_items == n_pub, "item count mismatch"),
cat_row(glue("stac: check_stac 0 errors ({sum(chk$finding == 'ok')} ok, {sum(chk$level == 'warn')} warn)"),
nrow(bad) == 0, paste(sprintf("%s %s", bad$document, bad$finding), collapse = "; ")))
}
results <- dplyr::bind_rows(results, stac_res)
stac_res |>
mutate(status_html = status_badge(status)) |>
select(status_html, query, error) |>
DT::datatable(escape = FALSE, rownames = FALSE, colnames = c("", "STAC check", "error"))
```
## Save results sidecar
```{r}
#| label: save_results
results_path <- file.path(releases_dir, release_version, "test_results.json")
jsonlite::write_json(
list(
release_version = release_version,
tested_at = format(Sys.time(), "%Y-%m-%dT%H:%M:%SZ", tz = "UTC"),
n_pass = sum(results$status == "pass"),
n_fail = sum(results$status == "fail"),
n_skip = sum(results$status == "skip"),
results = results),
results_path,
auto_unbox = TRUE, pretty = TRUE, null = "null")
# under the staging prefix too: this line hardcoded "ducklake/releases" while every other path in the
# file read CALCOFI_RELEASE_PREFIX, so a staging test wrote test_results.json to the REAL prefix (a
# phantom releases/v2026.09.04/ on 2026-09-04, deleted by hand). release_prefix is set in `setup`.
gcs_bucket <- "calcofi-db"
gcs_release <- glue("{release_prefix}/{release_version}")
put_gcs_file(results_path,
glue("gs://{gcs_bucket}/{gcs_release}/test_results.json"))
message(glue("test_results.json uploaded for {release_version}"))
```
## Promote (gated on all-pass)
`latest.txt` is written **only** if no query failed. Skipped queries
(JS-only `sql_builder`) do not block promotion — they're exercised in
the browser by the query app.
```{r}
#| label: promote
n_fail <- sum(results$status == "fail")
n_pass <- sum(results$status == "pass")
n_skip <- sum(results$status == "skip")
if (n_fail > 0) {
failures <- results |> filter(status == "fail")
for (i in seq_len(nrow(failures))) {
message(glue("FAIL [{failures$query[i]}]: {failures$error[i]}"))
}
stop(glue(
"{n_fail} query(ies) failed against {release_version}. ",
"latest.txt NOT updated."))
}
# All green — but a green suite is NOT sufficient to promote, and on 2026-08-14
# that distinction cost an hour of broken consumers. `release_database` died at
# `upload_frozen` with the parquet uploaded and the JSON sidecars not; this suite
# then passed 28/28 against that parquet — correctly, the data was fine — and
# moved `latest.txt` to a release with no catalog.json, the file `cc_get_db()`
# opens. The queries test the DATA; they cannot see whether the release is
# READABLE, because they never open the catalog.
#
# promote_release() answers the second question first: it refuses to move the
# pointer unless catalog.json / metadata.json / relationships.json are all
# present, and writes the object with Cache-Control: no-cache so the change
# reaches consumers immediately instead of up to an hour later.
promote_release(release_version, bucket = gcs_bucket,
prefix = Sys.getenv("CALCOFI_RELEASE_PREFIX", "ducklake/releases"))
message(glue(
"PROMOTED {release_version}: {n_pass} passed, {n_skip} skipped, ",
"0 failed. latest.txt is now {release_version}."))
# re-publish this version's RELEASE_NOTES.md now that catalog.json (with its
# total size) and test_results.json exist and latest.txt points here — the
# appendix carries the validation result and the "promoted" mark. Notes are not
# data, so a failure here is a warning, never an un-promotion.
tryCatch(
publish_release_notes(
release_version, here::here("RELEASES.md"), releases_dir,
bucket = gcs_bucket,
pkg_versions = c(
calcofi4db = as.character(packageVersion("calcofi4db")),
calcofi4r = as.character(packageVersion("calcofi4r")))),
error = function(e) warning(glue("release notes not re-published: {conditionMessage(e)}")))
# regenerate the browsable release index so the public listing cannot lag the
# bucket. `versions.json` and `latest.txt` are written by the release itself and
# were always current, but the HTML at
# storage.calcofi.io/calcofi-db/ducklake/releases/ is a STATIC page built by
# scripts/build_release_index.R -- which nothing called. It was last run by hand
# on 2026-07-29, so it advertised "16 releases, latest v2026.07.17" while the
# bucket held 20 and had long since promoted past it. Data consumers were
# unaffected (they read latest.txt); it was humans who were misinformed.
#
# Runs AFTER the promotion above, because the index reads latest.txt to mark the
# "latest" badge. Non-fatal for the same reason as the dispatch below: a stale
# index is a documentation bug, not a reason to fail a validated release.
idx_script <- here::here("scripts", "build_release_index.R")
if (file.exists(idx_script)) {
idx_rc <- system2(file.path(R.home("bin"), "Rscript"), idx_script,
stdout = TRUE, stderr = TRUE)
if ((attr(idx_rc, "status") %||% 0L) != 0) {
warning(glue("release index rebuild FAILED (latest.txt is still correct): ",
"{paste(utils::tail(idx_rc, 5), collapse = ' | ')}"))
} else {
message("release index rebuilt")
}
}
# nudge the query site to adopt the new default_version now (previously a
# 6-hourly cron poll): dispatch CalCOFI/db-query's bump-default-version workflow,
# which mirrors latest.txt -> _config.yml and triggers a Pages redeploy. Gated
# here on the all-pass promotion, so the site never points at a release whose
# queries haven't been validated. Non-fatal — a missing gh just means the bump
# waits for a manual `gh workflow run`.
# Declared as a table rather than one if-block per consumer: the first two were
# copy-pasted and had already drifted (only one warned when `gh` was missing), and
# each new static consumer would add another copy of the same eight lines.
gh_dispatch <- tribble(
~repo, ~workflow, ~what,
"CalCOFI/db-query", "bump-default-version.yml", "query-site default_version bump",
"CalCOFI/db-viz-station", "refresh.yml", "station-portal coverage refresh",
# rebuilds the per-(line, cruise) section shards from the new release
"CalCOFI/ctd-transects", "refresh.yml", "CTD transect sections refresh",
# re-fetches datasets.json and rebuilds the dataset grid + pages (plan 2026-09-05 D-2; the
# workflow is created by WS-P1 — until then the dispatch warns, it does not fail)
"CalCOFI/CalCOFI.github.io", "refresh.yml", "dataset catalog refresh (landing page + dataset pages)")
gh_bin <- Sys.which("gh")
if (!nzchar(gh_bin)) {
message(glue(
"gh not found; these need a manual `gh workflow run`: ",
"{paste(gh_dispatch$repo, collapse = ', ')}"))
} else {
pwalk(gh_dispatch, function(repo, workflow, what) {
disp <- system2(gh_bin,
c("workflow", "run", workflow, "--repo", repo),
stdout = TRUE, stderr = TRUE)
if (!identical(attr(disp, "status") %||% 0L, 0L))
warning(glue("{what} dispatch failed: {paste(disp, collapse = '; ')}"))
else
message(glue("dispatched {repo} {workflow} ({what})"))
})
}
# Server-side consumers (db-viz-hex, db-viz-cruise, the h3t tile API) are NOT
# deployed here. They are their own target — `deploy_consumers`, which depends on
# this one — so that "the release shipped but consumers were never updated" is a
# state visible in tar_visnetwork()/tar_outdated() rather than a silent branch
# inside this chunk, and so the deploy can be re-run without re-running the query
# suite. The dispatches above stay here because they are instantaneous and target
# GitHub-hosted consumers that redeploy themselves.
```
## Cleanup
```{r}
#| label: cleanup
close_duckdb(con_test)
```
::: {.callout-caution collapse="true"}
## Session Info
```{r session_info}
devtools::session_info()
```
:::
