4 Database

4.1 Conventions, in one line each

Names follow Naming conventions: singular lower-case tables, snake_case columns with a unit suffix where one applies, a geom column tagged EPSG:4326, one row per measured value. Keys follow Keys and integrity: a *_key is a string natural key, a *_id an integer, a *_uuid the provider’s own identifier kept as a column; cruise_key, sample_key, site_key/grid_key and taxon_key carry the integration, and every declared key is measured before a release ships.

4.2 The core data model

20 source datasets — ichthyoplankton, zooplankton, bottle hydrography, CTD casts, DIC, underway meteorology, seabird and mammal transects, eDNA, and more — used to reach the database as ~40 per-dataset tables. They now project into a small core family that every consumer reads. A dataset’s projection lives in its own ingest notebook (the generic shapes live in calcofi4db), so adding a dataset never edits a shared switch(). Figure 4.1 draws that family and the reference tables it joins to; Table 4.1 says what one row of each stands for.

Figure 4.1: The core family and the references it joins to, with the keys that carry the integration. Every other released table hangs off one of these; the full diagram for any release is in the schema browser.

Table 4.1: The core family: what one row of each table stands for, and whether it is in the default set.

table	grain	tier
`sample`	one row per physical sampling event — site, tow, net, cast, bottle, underway record, transect, subsample, eDNA filter (`filter`), region pool — with the hierarchy as an adjacency list (`parent_sample_key`, plus `root_sample_key` for the top of the chain)	core
`obs_bio`	the biological occurrence headline: one scalar per row for a taxon (`taxon_key`, `life_stage`, `measurement_type`, `value`) with its event’s gear, effort and the two standardized densities inline	core
`obs_env`	the environmental headline: one measured scalar per row (`measurement_type`, `value`, `measurement_qual`, `qual_ok`), one parquet object per measurement type	core
`obs`	the two above under one name — `realm` = `bio` or `env`, `value` as `measurement_value` — as a view the catalog carries; its own objects are deprecated	view
`obs_attribute`	sub-occurrence detail — length- and stage-frequency bins (`bin_value`, `bin_label`, `count`) and categorical behaviour	core
`sample_measurement`	event-level effort and conditions — a net’s `volume_sampled` and `std_haul_factor`, a cast’s surface observations	core
`sample_track`	the path of a sampling event that has extent along the sea surface — an effort segment of a line-transect survey, the observer’s line of sight to a sighting — with its beginning, middle and end in x, y and t (`datetime_beg/_mid/_end`, `longitude_beg/_mid/_end`, `latitude_beg/_mid/_end`), `length_km` and a beginning-to-end `LINESTRING` `geom`; at most one row per `sample` (`sample_key` is its primary key), and present in a release only when a dataset in it has paths	core
`sample_root`	one row per root sampling event with the dense integer `root_id` the browser objects join on, its position and the position’s H3 cell (`hex7`), time, depth span, gear and seafloor depth	supplemental
`obs_ctd_full`	full-resolution CTD scans (~271 M rows); `obs_env` carries the thinned series	supplemental
`obs_mets_full`	full-resolution underway meteorology (~20 M rows)	supplemental

Table 4.1 is the whole model, one row per table; Table 4.2 below is every table the release actually ships. An observation’s provenance is denormalized onto it on purpose: obs carries dataset_key, cruise_key, grid_key, the position, the datetime and hex_id (H3 at resolution 10, so coarser aggregations use h3_cell_to_parent(hex_id, res) rather than a column per resolution), so a rollup groups without a join. geom lives on sample and on the reference tables, never on obs. obs_bio and obs_env also carry year and quarter (and, appended after hex7, cruise_month and season_year), and these are the cruise’s: the year and month of the median datetime of its root samples (cruise.date_median / month_median, written at release), not the calendar date of the cast and not the month its cruise_key names, which is used only where the cruise has no dated sample; the date is used only where the key does not parse either. quarter is the season, 1 = DJF, 2 = MAM, 3 = JJA, 4 = SON, and season_year counts December toward the following year’s winter. A cruise often begins in the last days of the month before its own, so a cast taken on 30 June of CalCOFI 2607 (2026-07-3322) is in summer with the rest of that cruise, which is also the season its climatology baseline is filed under. test_release.qmd asserts it for that cruise.

A sampling event has a hexagon too. sample.hex7, copied onto sample_root.hex7 for the root events, is the resolution-7 H3 cell that is the parent of the resolution-10 cell of the event’s own position: the same definition as hex7 on obs_bio and obs_env, and not the resolution-7 cell the position falls in, because H3 cells do not nest exactly and the two differ near every cell edge. It is NULL where the event has no finite position. This is what places a value held per event in sample_measurement (a cast’s mixed-layer depth) in a hexagon, since such a value has no observation row to borrow a cell from. calcofi4db::check_sample_hex7() fails the release unless the cell is present exactly where the position is finite, is equal on the two tables for every root, and equals the cell of every observation sitting at the same position. An observation carries its own position, so a scan of a CTD cast that drifted across a cell edge lies in the neighbouring hexagon: join an observation to its sample on sample_key, never on the cell.

4.2.1 A path as well as a point: `sample_track`

sample holds one point per event, and that does not change: a net tow is placed at its position, a cast at its station, a line-transect effort segment at its midpoint, a sighting at the animal’s position. Some events also have extent along the sea surface (a survey leg, the observer’s line of sight to a sighting), and that extent lives beside the point in sample_track, one row for each such sample (sample_key is both its primary key and a foreign key to sample.sample_key, so a sample has at most one path). A consumer that reads sample is unchanged, and one that wants the path joins:

SELECT s.sample_key, t.track_type, t.length_km, t.geom      -- or t.longitude_beg … t.latitude_end
FROM sample s JOIN sample_track t USING (sample_key)
WHERE s.dataset_key = 'sio_cetacean-sightings' AND t.track_type = 'effort_segment';

track_type says what the line is (effort_segment, sight_line, …). The beginning, middle and end each carry a position and a time: the beginning and end come from the provider, the middle is computed (halfway in time, at the great-circle midpoint of the two ends). length_km is the provider’s own length where it gave one, otherwise the great-circle distance between the ends. geom is the straight LINESTRING from beginning to end, tagged EPSG:4326 at release like every released geometry; draw a segment from geom, or from the six longitude_* / latitude_* columns where the reader has no geometry type. A sighting’s own sample point is placed, by coordinate geometry (COGO), from the ship’s position, the true bearing of the line of sight and the distance, and its sight_line runs from the ship to that point (calcofi4db::cogo_true_bearing(), cogo_destination()). The table is assembled by calcofi4db::assemble_core() and declared in core_output_tables() and core_relationships() like the rest of the core family, and it is absent from a release in which no dataset has a path. A net tow’s start and end positions are the next use.

4.2.2 What the release publishes: `obs_bio` + `obs_env`, and `obs` as a view

The assembly grain is obs; what the release publishes is the pair cut from it, because the questions consumers ask are shaped differently for the two realms:

obs_bio — one object, the bio realm, with the gear and effort of the row’s own sampling event inline (tow_type, std_haul_factor, prop_sorted, volume_sampled_m3) and the two standardized densities derived once (density_per_10m2, density_per_1000m3) plus effort_class. One taxon question = one file.
obs_env — one object per measurement_type. One variable = one small fetch, which is what a browser-side reader (DuckDB-WASM cannot list a directory; the catalog gives it an explicit file list) actually needs.

Each is a strict superset of obs under a name mapping — realm is implied by which table you read, and value is measurement_value — and obs itself is a view the catalog carries (catalog.json → views.obs), a UNION ALL over the pair under obs’s original column names. calcofi4r::cc_get_db(), calcofi4py and the query site create it from the catalog, so FROM obs keeps working. Two consequences worth knowing:

The release proves the pair reproduces obs before it freezes — row counts, distinct obs_ids and a hash signature of every column, per realm × dataset. The one allowed difference is a depth fallback: a bio row with no depth of its own carries its tow’s span.
obs’s physical objects still ship for one release, marked deprecated in the catalog with replaced_by: ["obs_bio", "obs_env"]. Migrate to the pair — a consumer reading the view needs no change, a consumer building a path to obs.parquet does.

Do not rename value, root_id or hex7 on the pair; new columns are appended and the view maps names, so consumers never have to.

4.2.3 Shared reference tables

cruise (one row per cruise, with cruise_uuid, the ship, and the observed date_min/date_max span that keys every other dataset’s events), ship, grid (the CalCOFI station grid, materialized deterministically from calcofi4r::cc_grid: one cell per official station, beside the previous cells that lay beyond the official pattern, kept as they were) with grid_crosswalk (how the previous grid’s keys map onto it — a key keeps its form but not necessarily its polygon, so never match one across releases by name; see The grid key changed in October 2026), spatial + spatial_attribute (boundary layers), sample_spatial (the sample→layer memberships), region, lookup, dataset (one row per released dataset, with its measured coverage and its citation), measurement_type (the measurement vocabulary, below) and climatology.

climatology is the one baseline every anomaly subtracts. Until 2026-08-31 three products each computed their own and the same section read +1.4 °C in one and ~0 in another. It is a plain mean per dataset × site_key (the real station, since calcofi4db 4.10.0 — the inshore cells of the earlier grid held 2–4 stations 15–30 km apart, so a cell-grained baseline blended them, and the key on the baseline stayed site_key when the grid was rebuilt one cell per station; grid_key stays on the row as the station’s modal cell, labelled by the current grid) × the cruise’s month × 10 m depth bin × measurement type over 1993–2013, kept only where ≥ 5 distinct cruises contribute (3 until v2026.09.06), with the window stamped on every row. The month is the one the cast’s cruise_key designates (YYYY-MM-NODC), not the date the station was occupied, since calcofi4db 4.22.0: a cruise often begins in the last days of the month before, and keyed on the calendar date CalCOFI 2607’s line 93.3 stations inshore of station 50, worked on 30 June, looked up a June baseline no cruise samples and drew no anomaly. A cast’s anomaly is its value minus the row of its own station and cruise month; pooling months produces a seasonal-cycle map, not an anomaly.

The table has two arms and one extra baseline, none of which changes the grain above:

Per-bin rows are the depth series of obs_env, one row per 10 m depth_bin.
Per-cast rows are the derived CTD products of Section 4.2.8 (the mixed-layer depths, the chlorophyll maximum and integral, the hypoxic boundary and nitracline depths, the pycnocline depth and its N² maximum), pooled by the same rules (station, cruise month, window, quality, cruise floor). A cast-level value has no depth, so its depth_bin is NULL, meaning “a per-cast value”, never a depth. A NULL never matches =, so a per-bin join on depth_bin = 40 cannot pick one up by accident; a per-cast consumer matches depth_bin IS NULL. relationships.json declares the column under nullable_key_columns, and check_release_relationships() reads a declared nullable key column as a value rather than as a failure. The release names the types, so calcofi4db carries no list of datasets.
A seasonal baseline sits beside the monthly one as appended columns (nothing was renamed): quarter and season_mean, season_sd, season_n, season_n_cruises. The season is that of the cruise’s month — the meteorological seasons, numbered 1 = DJF (December–February), 2 = MAM, 3 = JJA, 4 = SON, December counting toward the following year’s winter (calcofi4db::cruise_quarter_sql(); through release v2026.10.30 quarter was Q1 January–March … Q4 October–December) — the same season the Explorer’s season filter keys on, so CalCOFI 2607’s 30 June casts are summer. The columns are set on every month of a season that passes the same cruise floor, so a baseline is still looked up by station, month, bin and type, and nothing that sums the table counts a cell twice. Where a station’s months are each below the floor but its season is not (mostly the northern lines 60–73.3, whose winter and spring cruises are split across January–April), the row has a seasonal mean and a NULL clim_mean: a NULL clim_mean means “no monthly baseline”, exactly as an absent row does, so a consumer tests clim_mean IS NOT NULL before it subtracts. calcofi4r::cc_climatology(season = TRUE) reads the seasonal arm and calcofi4r::cc_cast_anomaly() the per-cast one.
The cruise’s month is its median cast month. A cruise ID names sometimes the month a cruise started and sometimes the month it mostly covered, so the month a cast is filed under — in the climatology, in obs_env / obs_bio (year, quarter, and the appended cruise_month, season_year) and in every anomaly — is the calendar month of the median datetime of the cruise’s root samples, written on cruise as date_median, month_median, season and season_year by calcofi4db::add_cruise_median_date() at release. A cruise with no dated sample keeps its key’s month. A consumer reads cruise.month_median or obs_env.cruise_month, never the key’s substr(cruise_key, 6, 2).

These rules are enforced. test_release.qmd’s consumer contract withholds latest.txt unless a per-cast cell exists for a derived product, every monthly cell also carries a seasonal baseline, seasonal-only cells exist, a NULL depth_bin appears only on a per-cast type (never on a type obs_env carries by depth), depth_bin is otherwise a 10 m floor bin, one window is stamped on every row, cruise carries the four median-date columns, a December cruise is DJF of the following year, every obs_env row’s quarter / cruise_month / season_year match its cruise, and every obs_env row of CalCOFI 2607 is in season 3, summer (the gates).

4.2.4 Every table in the release

The full inventory — what a database client’s object browser would list — read from the promoted release’s catalog.json and metadata.json. A supplemental table is hosted and catalogued but left out of cc_get_db()’s default set; a deprecated one still ships its objects for one release while its replacement is named.

Table 4.2: Every table of v2026.11.05, with its tier, rows, columns and description.

table	tier	rows	columns	name	description
climatology	core	1,921,596	17	Climatology	The one seasonal baseline every CalCOFI anomaly is a departure from (calcofi4db::build_climatology(), release_database.qmd browser_objects). A plain mean of the env realm of obs per dataset × station (site_key, with grid_key as the station’s modal cell) × calendar month × 10 m floor depth bin × measurement type across the window stamped on every row (clim_yr_min–clim_yr_max, 1993–2013), kept only where at least 5 distinct cruises contribute. Calendar month because CalCOFI’s quarterly cruises give many years per month at a station but few days; 10 m floor bins (labelled by the shallow edge) because obs carries the thinned CTD series and 5 m off-grid bins sample only the profile’s inflection points. Partitioned by measurement_type. ctd-transects, the Explorer’s Sections lens and calcofi4r::cc_climatology() subtract this table; a cell that is absent has no baseline and its anomaly is blank, never zero.
cruise	core	869	25	Cruise	unique by ship and year-month
dataset	core	20	24	Dataset
dataset_taxon	core	1,940	7	Dataset Taxon	Crosswalk from each dataset’s own taxon vocabulary to the global `taxon_key`. `obs` resolves its taxon by joining this on (`dataset_key`, `ds_taxa_code`), so a dataset can keep its local codes without leaking them into the shared model.
fish	core	10,796	4	Fish	Fish larger than larval size (SWFSC Fish table): generally juveniles but not exclusively, counted per net and CalCOFI species code in addition to the larvae of the same net, never in both (Ed Weber, 2026-10-14, swfsc_ichthyo_20). Not in obs: no life stage in the vocabulary says exactly “larger than larval size”.
grid	core	225	10	Grid	CalCOFI survey grid polygons with standard and historical station patterns
grid_crosswalk	core	673	5	Grid Crosswalk	How the cells of the previous CalCOFI grid (every release through v2026.10.01; `calcofi4r::cc_grid_v1`) map onto the cells of this release’s `grid`, rebuilt as one cell per official station beside the previous cells kept as they were (CalCOFI/workflows#130). One row per overlapping pair of a previous and a current cell, with the overlap area and its share of each cell (`prev_frac`, `grid_frac`), built at release by calcofi4db::build_grid_crosswalk() and gated by check_grid_crosswalk(). 196 of the 218 previous keys are also keys of the rebuilt grid, 84 of them over a polygon that changed, so a `grid_key` saved from an earlier release must be mapped through this table, never matched by name: `prev_frac` is the weight that carries a per-cell value to the current cells. Keyed on (`prev_grid_key`, `grid_key`).
ichthyo_net	core	77,888	6	Ichthyo Net	One row per SWFSC ichthyoplankton net with the two source columns the core projection drops: the net’s side of a bongo pair and the tow’s number at its station occupation, beside SWFSC’s own NetId and TowId and the tow type. Join sample (sample_type = ‘net’, dataset_key = ‘swfsc_ichthyo’) USING (sample_key). What tells the two nets of a bongo tow, and two tows of one type at one occupation, apart; the SIO Pelagic Invertebrate Collection’s Parent Net Event crosswalk matches on it.
lookup	core	26	9	Lookup	Unified vocabulary lookup table for egg stages, larva stages, and tow types
measurement_type	core	298	22	Measurement Type
obs_attribute	core	455,300	10	Observation Attribute	Sub-occurrence attribution: the breakdown within an `obs` headline row. Covers numeric frequency distributions (length bins, stage numbers) and categorical breakdowns (seabird behavior). Counts here sum to the headline where the source is internally consistent.
obs_bio	core	1,586,795	34	Biological Observations	The bio realm of the observations — one row per taxon occurrence headline (count, density or biomass) with the gear and effort of its own sampling event inline (`tow_type`, `std_haul_factor`, `prop_sorted`, `volume_sampled_m3`), the two standardized densities (`density_per_10m2`, `density_per_1000m3`) and `effort_class`, `qual_ok`, `root_id` (→ `sample_root`), `sample_key` (→ `sample`, the net / tow itself), `hex_id` (H3 res 10) and `hex7`. A strict superset of `obs`’s bio rows under a name mapping (`value` = `measurement_value`, realm implied); `obs` is a view over this table and `obs_env`. One object (~26 MB), read whole by the CalCOFI Explorer. Depth is the observation’s, falling back to its sample’s span (a net tow) and its root’s.
obs_env	core	34,448,082	34	Environmental Observations	The env realm of the observations — one row per measured scalar (bottle, CTD headline series `ctd_thin`, DIC, METS, picoplankton) with the same columns as `obs_bio` (effort and taxon NULL), `qual_ok` evaluated, `root_id` (→ `sample_root`), `sample_key` (→ `sample`, the bottle / cast), `hex_id` and `hex7`. Hive-partitioned by `measurement_type` so one variable is one object (~4 MB median); `obs` is a view over `obs_bio` and this table. A strict superset of `obs`’s env rows under a name mapping (`value` = `measurement_value`).
region	core	4	7	Region	The 4 pooled regions defined on the CalCOFI grid (Hayward & Venrick 1998), with the member station codes the source declares and a POLYGON derived from them: each declared station claims the water nearest to it, clipped to the convex hull of all 34 stations and dissolved by region, so the four tile their pooled domain with no overlap and no gaps. Stations are placed by the `+proj=calcofi` transform rather than a grid lookup, which is what lets the six intermediate inshore stations with no cell in the regularized grid resolve. latitude/longitude is an interior representative point, not a centroid (the regions are concave). Land is not erased. See questions Q01.
sample	core	1,467,380	22	Sample	One row per physical sampling event at its native grain (site, tow, net, subsample, cast, bottle, water, filter, underway, transect, sighting, deployment, scan, region pool). An adjacency list: `parent_sample_key` points at the containing event and `root_sample_key` at the top of the chain, so counting distinct events at any level is a single `GROUP BY`.
sample_measurement	core	714,892	6	Sample Measurement	Event-level (effort) measurements: quantities that describe the sampling event itself rather than anything observed in it — net volume filtered, standard haul factor, proportion sorted, bottle cast conditions. Kept apart from `obs` so effort is never mistaken for an observation.
sample_spatial	core	2,295,877	5	Sample Spatial Membership	Exact polygon membership per root sample — one row per (root sample, spatial feature) for every layer in `spatial` — computed once at release so every consumer inherits the same membership (Explorer plan D4). Keyed on (`root_sample_key`, `spatial_key`).
ship	core	49	8	Ship	unique ship with many cruises
spatial	core	3,395	5	Spatial Layers	Geometries for ancillary spatial layers (CA jurisdictions NOAA boundaries marine protected areas etc.) keyed by spatial_key. Sourced from metadata/spatial_layers.csv. Companion to spatial_attribute.
spatial_attribute	core	37,968	9	Spatial Layer Attributes	Non-geometry attributes for features in spatial in long entity-attribute-value form (spatial_key layer fld and one typed val_* column). Allows attribute schemas to differ across layers without column proliferation.
taxon	core	2,606	19	Taxon	Taxonomic hierarchy for species via WoRMS and ITIS authorities (built from spp.duckdb via recursive CTEs)
taxon_group	core	438	3	Taxon Group	Portable groupings of taxa (phytoplankton functional groups, seabirds, marine mammals). Many taxa per group; a taxon may belong to several.
obs	deprecated	36,034,877	18	Observation	The occurrence-headline long table: one scalar measurement per row, environmental (`realm = 'env'`) and biological (`realm = 'bio'`) together. Biological taxon is the global `taxon_key`; sub-occurrence detail (length/stage bins, behavior) lives in `obs_attribute`, and event-level effort in `sample_measurement`.
obs_ctd_full	supplemental	286,495,695	18	Observation (Full CTD)	Supplemental full-resolution CTD scans, identical in shape to `obs`. Excluded from the default table list and the ERD because of its size; opt in with `cc_get_db(supplemental = TRUE)`. The default `obs` carries CTD via the adaptively thinned `ctd_thin`.
obs_mets_full	supplemental	19,926,523	18	Obs Mets Full
sample_root	supplemental	419,439	15	Root Samples	One row per root sampling event (a `sample` with no parent) with the dense integer `root_id` the browser objects (`obs_bio`, `obs_env`, `sample_spatial`) join on, its position, datetime, depth span, gear and GEBCO seafloor depth. Supplemental: built by release_database.qmd, hidden from `cc_get_db()` unless `supplemental = TRUE`.

4.2.5 Taxonomy

Three shared tables replace the ~7 per-dataset taxon tables:

taxon — one row per taxon: taxon_key (worms:<AphiaID>, or itis:<TSN> when the taxon’s class is Aves, because WoRMS bird taxonomy lags), the cross-reference ids (worms_id, itis_id, gbif_id), parent_taxon_key, rank and rank_order, the flattened lineage (kingdom, phylum, class, order_taxon, family), scientific_name, common_name, and taxonomic_status with status_checked — read the two together; a status with no check date is not a fact.
dataset_taxon — the per-dataset vocabulary → taxon_key crosswalk. obs resolves its taxon_key by joining this on (dataset_key, ds_taxa_code), and ds_source_json records whatever ids the source itself supplied, so the source and the authority can be audited against each other.
taxon_group — groupings for rollups.

Two distinctions that cost real data before they were written down. The key authority and the cross-reference columns are different questions: a bird keys itis: and carries a worms_id, because a consumer joining on worms_id would otherwise match zero seabirds. And a key must be an accepted id while a cross-reference is whatever the authority links — a deprecated TSN is re-keyed onto its accepted successor and the event is recorded in taxon.notes.

When asserting lineage coverage, split by rank position: family is legitimately NULL above family rank, and kingdom is NULL for worms:1 Biota. A blanket non-NULL assertion is wrong, and someone will “fix” it by inventing data.

4.2.6 A face for every species page

Every taxon has a page at calcofi.io/species/ — the species catalog calcofi4db::build_taxa_catalog() writes as taxa.json, one row per taxon_key with its lineage, authority ids and observation stats. The page also shows a silhouette, a photo where one exists, a size on a familiar scale, and one sentence, so the reader has more than the taxonomy to go on (Figure 4.2). A PhyloPic silhouette, drawn in the page’s own ink, is resolved by WoRMS id and, failing that, by name at each rank up the lineage until one exists. A full-colour photo appears where one clears the licence policy below; where none does, the slot is simply absent and the layout is unchanged. Size is drawn on a scale a reader already knows — the taxon’s own length beside the nearest familiar reference (a US quarter, a person, the CalCOFI bongo net and ring, the ship) — and, for fishes, the egg-to-larva lengths FishBase carries from Matarese et al.’s (1989) laboratory guide of Northeast Pacific early life stages, with CalCOFI Atlas 33 (Moser 1996) held in reserve for the species NOAA’s Ichthyoplankton Information System does not cover. The sentence reuses Wikipedia’s lead under its own licence, followed by the release record’s own numbers. Every source is named on the page: PhyloPic for the silhouette; Wikimedia Commons, iNaturalist and GBIF for the photo; WoRMS, FishBase and SeaLifeBase for the size; NOAA’s Ichthyoplankton Information System for the fish plate. The licence policy is one sentence — Creative Commons and public-domain files are used, non-commercial licences are accepted but labelled, and every asset is credited and linked to its source. None of this is typed into the page: the sidecar (taxa_media.json) is refreshed by the landing repo’s own scripts whenever a release adds a taxon it has no entry for (the release’s deploy checks after every promotion), keyed by taxon_key beside the catalog, and a taxon without a match renders exactly as it does today (taxon-reference skill, Species faces).

Figure 4.2: The page head, composed: a PhyloPic silhouette in the page’s own ink beside a licensed photo faded into the band, a two-bar glance at size against a familiar reference, and a sentence stitched from Wikipedia, the release record and WoRMS, each part underlined in the colour of its source — the Pacific sardine’s live page at calcofi.io, light theme, 2026-09-11.

4.2.7 Measurements

measurement_type.csv names a series — one dataset’s own column of measured values (calcofi_bottle.temperature, calcofi_ctd-cast.temperature_ave). The bottle’s temperature and the CTD’s are the same physical quantity read two ways, and until 2026-09 nothing said so in one place: the Explorer hard-coded five such pairings in its own UNIFIED table with a note that the label “belongs in measurement_type.csv”. The measurements catalog is that one place.

One page per measurement key at calcofi.io/measurements/{key}/ — the registry’s variable where a series belongs to one, else the canonical measurement_type itself — lists every series that measures it, one row per measurement_type × dataset, each with the dataset’s own source and flag column. A series that is not canonical for its key (a raw CTD sensor, a thermosalinograph past the first) gets no page and is listed under its dataset as full_resolution_only[], the vocabulary_only[] of this catalog. This mirrors Taxonomy one-for-one: measurement_type.variable plays dataset_taxon’s crosswalk role, and metadata/variable.csv — read with calcofi4db::read_variable(), appended with register_variables(), never a bare write_csv() — plays taxon’s role of holding the one label, description and NERC P01 concept a key is shown under.

A variable is assigned only where (i) the NERC P01 concept is identical — or the two concepts differ only by a method qualifier for the same quantity — (ii) the units are the same or a declared conversion, (iii) the series come from the same kind of sample, and (iv) the two are not plausibly the same physical samples. calcofi4db::check_variable_registry() enforces (i) and (ii) at write time — a variable.csv row whose nerc_p01 disagrees with a member series’ own value is refused, so a crosswalk key can never silently misdescribe what it unifies. (iii) and (iv) are judgement calls a person records as evidence, not something a function can check from the column alone: the CTD files carry their own embedded bottle table (btl_*) that shares a P01 with the bottle dataset’s series but is plausibly the same physical bottles measured twice, so it fails (iv) and stays its own key, related to the bottle’s on each page with the reason (same_bottles); a replicate is not a mean (replicate_vs_mean); an underway intake is not a cast (underway_vs_cast). Table 4.3 is every key variable.csv carries on v2026.11.05, with its member series — sigma_theta’s two series keep their own NERC concepts (the bottle’s by computation, the CTD’s by CTD and computation) because criterion (i) reads “or a method qualifier,” not “identical always.”

The record, measurements.json (calcofi4db::build_measurements_catalog(), schema measurements.schema.json, written beside taxa.json in release_database.qmd), is taxa.json‘s twin for the environment half of the catalog: counts, one datasets[] entry per dataset carrying an obs_env row, one measurements[] entry per key with series[] carrying each series’ values by year, month, depth band and quality code, the observed quantiles within the registry’s declared bounds, and related[] naming the other keys that share a NERC P01 concept and are kept apart on purpose. test_release.qmd’s measurements_catalog gate re-derives the schema check and the arithmetic gate every consumer of this record leans on: the sum of series[].n_values over every key equals obs_env’s own row count, which is exactly what a series counted into two keys would break.

On v2026.11.05, the record (release) counts 104 measurements over 110 series in 7 datasets — 34,448,082 obs_env rows, 340,870,300 once the two full-resolution supplementals are counted.

Table 4.3: Every unified measurement key on the record above, with its member series — the dataset, the series’ own column, its values, and its own NERC P01 concept (identical for four keys; sigma_theta’s differ by method qualifier, D3 criterion i).

	series (column)	values	NERC P01
Dissolved oxygen (ml/L)
calcofi_bottle	oxygen_ml_l	725,612	DOXYZZXX
calcofi_ctd-cast	oxygen_ml_l_ave_sta_corr	665,354	DOXYZZXX
Dissolved oxygen (µmol/kg)
calcofi_bottle	oxygen_umol_kg	690,680	DOXMZZXX
calcofi_ctd-cast	oxygen_umol_kg_ave_sta_corr	627,819	DOXMZZXX
Salinity
calcofi_bottle	salinity	847,996	PSLTZZ01
calcofi_ctd-cast	salinity_ave_corr	687,017	PSLTZZ01
Potential density (σθ)
calcofi_bottle	sigma_theta	842,652	SIGTEQ01
calcofi_ctd-cast	sigma_theta_1	703,760	SIGTPR01
Temperature
calcofi_bottle	temperature	884,383	TEMPPR01
calcofi_ctd-cast	temperature_ave	704,038	TEMPPR01

Two facts the record states rather than leaves implicit. A series with no NERC concept says so — no_p01 on its flags, never a blank cell that reads as “not looked at” — the same exact-match discipline as every controlled-vocabulary id in Metadata registries: 54 of the 110 series on v2026.11.05 carry no P01. And a bound is declared or questioned, never inferred from the data (the ingest’s own rule — measurement bounds): a series’ observed{} range is computed within its registry bound, what falls outside is counted separately in out_of_bounds{n, min, max} rather than silently widening the range a reader sees, and 14 series declare no bound at all (no_bound) rather than letting their widest reading stand in for one; 2 series carry sentinel_suspected — a declared bound broken, or, absent one, an extreme past ±99 and 100× the series’ own 5th/95th percentile. That is how the catalog surfaced a sea-surface reading of thousands of degrees and a PAR sensor’s numeric fill value on v2026.11.05 — both certain bugs, fixed at the ingest, not questions.

4.2.8 Some measurements are one value per cast, not per depth

Nearly every series above is a value at a depth: a row of obs_env. A product of a whole cast — its mixed-layer depth, the depth and value of its chlorophyll maximum, its integrated chlorophyll, the depths at which its oxygen falls to 2.4, 1.4 and 0.5 mL/L and its nitrate reaches 1 µmol/L, its pycnocline depth and the N² maximum there — has no depth, so it is not a depth observation and is not projected into obs_env. The release keeps it in sample_measurement, on the cast’s sample (the registry’s grain is sample), and the measurements catalog lists it with grain: "sample" so it has a page like any other key. These are computed, not measured: calcofi_ctd-derived derives them from the CTD profiles by the CTD team’s own definitions, and the definition of each lives in the registry’s description and derivation columns, which Table 4.4 reads, rather than being restated here. A definition that changes gets a new key instead of reusing the old name, which states its threshold; the superseded key stays in the registry marked RETIRED, not canonical, and no row carries it. Each per-cast product also has a baseline in climatology, with depth_bin NULL, so its anomaly is read like any other (Shared reference tables).

On v2026.11.05 the record lists 13 measurement keys with grain: "sample".

Table 4.4: The per-cast products of calcofi_ctd-derived in the registry on v2026.11.05 (canonical keys only), with the registry’s own description and units. How each is computed is in the registry’s derivation column.

measurement_type	units	description
buoyancy_n2_max	1/s2	Maximum buoyancy frequency squared N2 (TEOS-10, 10 m running mean) between 10 and 200 m, at pycnocline_depth
chl_integrated	mg/m2	Chlorophyll-a integrated over the top 200 m (or the cast bottom): the sum of the 1 m bins
chl_integrated_depth	m	Depth reached by chl_integrated (200 m unless the cast is shallower)
chl_max	ug/L	Chlorophyll-a at its maximum: the 3 m running mean at chl_max_depth
chl_max_depth	m	Depth of the chlorophyll-a maximum (DCM): the highest value of a 3 m running mean
hypoxic_depth_05	m	Severe hypoxic boundary depth (core of the oxygen minimum zone): the shallowest depth where oxygen falls to 0.5 mL/L
hypoxic_depth_14	m	Hypoxic boundary depth: the shallowest depth where oxygen falls to 1.4 mL/L
hypoxic_depth_24	m	Mild hypoxic boundary depth: the shallowest depth where oxygen falls to 2.4 mL/L
mld_sigma_theta_002	m	Mixed-layer depth: where sigma-theta is 0.02 kg/m3 greater than at 10 m (CalCOFI legacy definition)
mld_sigma_theta_0125	m	Mixed-layer depth, sigma-theta increase of 0.125 kg/m3 from 10 m (Levitus 1982)
mld_temperature_02	m	Mixed-layer depth, temperature departure of 0.2 degC from 10 m (de Boyer Montegut et al. 2004)
nitracline_depth	m	Nitracline depth: where nitrate reaches 1 umol/L, interpolated between the bracketing samples
pycnocline_depth	m	Pycnocline depth: where the buoyancy frequency squared N2 (TEOS-10, 10 m running mean) is greatest between 10 and 200 m

To place one on a map or in a hexagon, join sample_measurement to sample on sample_key: the cast’s sample row carries its position, its time and its hex7, and the row’s own dataset_key is the derived dataset’s, not the cast’s.

4.2.9 Some measurements are derived per bottle

calcofi_bottle-derived computes values from the bottle database’s own temperature and salinity, one per bottle and back to the start of the record: spice (spiciness0, the same key and rule as the CTD series) and the TEOS-10 sigma-theta (sigma_theta_teos10). Its rows are depth observations in obs_env. Each sits on the calcofi_bottle bottle it was computed from, so the dataset adds no sample rows of its own. A bottle whose temperature or salinity its provider flags as suspect or missing gets no value, because a derived value carries no flag. The bottle database’s own sigma_theta, computed by the provider’s legacy software, stays as it is in calcofi_bottle, and the TEOS-10 value is published beside it under a key of its own. The two are not unified under one variable, because they come from the same bottles. A key that two datasets publish lists both in the registry’s _source_datasets. Filter on dataset_key to keep the bottle spice apart from the CTD spice.

4.2.10 A face for every measurement page

Every measurement page at calcofi.io/measurements/{key}/ carries the catalog above, and beneath it a face that answers three questions in one glance: what the thing is, how CalCOFI takes it, and why it matters (Figure 4.3). What follows NERC’s own identity chain — a P01 concept’s sameAs link to a chemical entity in ChEBI, or a biological one in WoRMS — down to a structure drawn from ChEBI’s own coordinates, in the page’s own ink; a mixture such as salinity shows its composition instead, the mass fractions of TEOS-10’s Reference-Composition Salinity Scale (Millero et al. 2008) rather than a single molecule, and a property with no chemical identity of its own shows its familiar scale alone. How comes from metadata/measurement_method.csv, one row per dataset and measurement key naming the platform, the instrument, the principle, and a link into the method’s own section of calcofi.org, styled like the front door’s pins. Why is one sentence stitched from the NERC definition, the release record’s own anomaly for every depth band where the quantity is measured against its climatology, and a single authored, cited line picked as the best one-sentence answer; the alternatives not picked — a second authored line, the GOOS essential-ocean-variable question, the Wikipedia lead — wait one click away in a collapsed panel, the same idiom the datasets catalog uses for what is “not yet in the database.” A key with no NERC concept of its own — an instrument voltage, an estimate, a sensor pair’s average — borrows the face of the quantity it stands in for and says so on the page, rather than showing a face for a concept it does not carry. None of this is typed into the page: the sidecar (measurements_media.json) is fetched by the landing repo’s own scripts whenever a release adds a key it lacks, keyed by measurement key beside the catalog, and a key without media renders exactly as it does today (metadata-registries skill, Measurement faces).

Figure 4.3: The page head, composed: the existing crumb, label, NERC ids and stats band, with a new face row beneath it — a molecule drawn from ChEBI’s own coordinates in the page’s own ink, a link to the method on calcofi.org, and a sentence stitched from the NERC definition, the release record and an authored, cited line, each part underlined in the colour of its source — the live nitrate page at calcofi.io, light theme, 2026-09-12.

4.2.11 Quality flags reach consumers only if consumers apply them

obs.measurement_qual is each dataset’s own vocabulary, uninterpreted — bottle 6 = ok-from-CTD, 8 = suspect, 9 = missing; CTD 1/2 = use primary/secondary sensor, 4 = set to zero, 8 = questionable, 9 = bad; DIC follows WOCE (2 good, 3 questionable, 4 bad, 9 missing). The same number means different things in different datasets (a CTD 4 is not a DIC 4), so a code is always read with its dataset. The registry is metadata/measurement_qual.csv in the workflows repo and it ships in metadata.json.

CTD code 4, below_detection, means below the detection limit, reported as 0 (Ben Gire, 2026-10-13). On the corrected chlorophyll-a and nitrate estimates the provider sets only a negative value (sensor noise at the detection floor) to 0 and codes it 4, instead of leaving it blank; the code may be used on any CTD sensor series. It is a real value, not a flag against the value: the quality predicate keeps it, so the zero counts in averages, integrated chlorophyll and anomalies. Where the raw sensor was unreliable the code is 9, with no corrected value.

In August 2026 a 2.18 ml/L oxygen spike at 1,144 m turned out to be a bottle flagged suspect in the source since 1955 — it reached a plotted app because no consumer filtered on the column. There is now one predicate per language — calcofi4r::cc_qual_ok_sql(), calcofi4py.qual_ok_sql(), the query site’s qualOkSQL() — NULL-safe, so an unflagged row is kept. Apply it. A value with a flag you do not read is not a value you can trust.

4.2.12 A data fix is diffed against the release before it is re-staged

A fix to an ingest changes values that consumers already hold, and the series it was meant to change is rarely the only one it touches: a corrected sensor pair moves every average built from it, a dropped sentinel changes a count. The rule is therefore that before a fixed ingest is re-staged it is diffed against the release it will replace — per measurement type, the rows changed, filled and removed and the largest change, for every series the fix can touch — and a dry run that leaves a series out of its breakdown is not a dry run.

How it is enforced: by a function, not by a gate. calcofi4db::diff_stage_vs_release() compares an ingest’s staged obs, obs_ctd_full and sample_measurement with a published release (a version, "latest", or a local copy) and returns one row per table × measurement_type with the rows added, removed, filled, blanked, changed and unchanged, the rows whose measurement_qual changed, the largest absolute change, and the duplicate keys on each side. Every type present on either side gets a row, and so does every type the caller names that is on neither, so a series cannot drop out unseen; diff_stage_vs_release_rows() returns the differing rows themselves. Nothing in the pipeline stops a re-stage that skipped the diff: what the release carries is the result, as the per-type change table the release notes (RELEASES.md) give for a fix, which is filled from this function’s output. The rule is stated as a practice because that is how it is kept.

4.2.13 Depth is a coordinate, and it is bounded as one

Every released depth is finite, non-negative and no deeper than 6,500 m, on obs, on sample and on the supplemental tables; a violation fails the release rather than shipping. Separately, sample.seafloor_depth_m carries the GEBCO 2025 sea-floor depth at the sample’s position (positive down, 0 on land, NULL outside the raster) so a consumer can judge a deep sample without fetching a raster. Where a sample’s deepest attributed depth exceeds the deepest GEBCO cell near it, that is reported and ratcheted, never deleted: nearly all such cases are minute-rounded 1949–1975 positions on slopes and canyons — the measurement is fine, the place is imprecise.

4.2.14 Two surveys of the same animals: the dedicated one is kept

Two datasets can observe the same thing from the same ship. On CalCOFI cruises the Farallon Institute’s strip-transect census (farallon_bird-mammal, seabirds and marine mammals) and the Scripps Whale Acoustics Lab’s line-transect cetacean survey (sio_cetacean-sightings) run with independent observer teams, so a whale or dolphin group can be logged by both, and summing the two counts it twice. The rule is that the dedicated survey is kept and the other dataset’s overlapping records are cut by whole cruise: ingest_farallon_bird-mammal.qmd drops Farallon’s marine-mammal observations on every cruise where the SIO survey has effort segments (the distinct cruise_key of its sample_track paths), and keeps its birds, fish and turtles, its transects, and its mammals on the cruises the SIO survey did not sail. It is enforced at the Farallon ingest, which depends on the sightings ingest, and it is gated on the replacement being public: the cut applies when sio_cetacean-sightings is in_release: true, and until then Farallon’s rows are unchanged, so a release never loses the mammals before the dedicated survey can replace them. A private preview candidate applies it with CALCOFI_FARALLON_CUT_COVERED=1. Each dataset’s description states the rule, and the notebook lists every Farallon cruise with marine mammals since the SIO survey began, covered or not.

4.3 Integrated database ingestion strategy

4.3.1 Overview

The integrated database is built by the CalCOFI/workflows pipeline: one ingest_{provider}_{dataset}.qmd notebook per dataset (orchestrated by targets, engine functions in calcofi4db) writes Parquet shards plus JSON sidecars; release_database.qmd assembles them in an in-memory DuckDB, validates, and freezes a versioned, immutable release on public Google Cloud Storage (next section). Consumers — the apps, calcofi4r::cc_get_db(), the schema and query sites — read the release as Parquet over HTTPS with no credentials.

Alongside the releases stands a PostgreSQL database (calcofi, PostgreSQL 18 + PostGIS 3.6 on the CalCOFI server) built as a working, multi-user store for the CTD team’s QA/QC: originals loaded verbatim and immutable, a flag/proposal ledger beside them, derived products on top. It is available but not yet adopted — the team’s flags still arrive with the provider’s files — and no part of the pipeline reads it live in either case; the bridge is a nightly parquet snapshot (CTD QA/QC). It is private and reached over SSH — see Server Access. DuckDB’s postgres extension joins the two worlds in one query (calcofi4r::cc_pg_attach()). (An earlier design ran the whole database in PostgreSQL with dev/prod schemas and a single create_db.qmd; that is superseded and only the legacy gis database remains, behind tile.calcofi.io.)

4.3.2 Release versioning

A release is an immutable, versioned copy of every table under gs://calcofi-db/ducklake/releases/{version}/, described by its catalog.json, content-addressed, with a DOI per version and one concept DOI for all of them. What ships, which versions are kept, how to pin one and what is checked before a release is cut are in Releases.

4.3.3 Metadata registries — the single sources of truth

Descriptions, vocabularies and provenance live as reviewable CSVs in CalCOFI/workflows — not in the database — so they travel with the code that produced them and change through pull requests.

Per dataset, under metadata/{provider}/{dataset}/:

tbls_redefine.csv — tbl_new, tbl_description
flds_redefine.csv — the crosswalk from every source column to its standard name and type (Naming conventions)
metadata_derived.csv — optional markdown/units overlay for derived tables and columns
questions.csv — the provider-question registry: what we have asked the data’s owners, what they answered, and what we have proposed an answer to and want confirmed. Each question has a durable global id and a short per-dataset label (Q15), a status (open / proposed / answered / wontfix) and a priority.
dataset_meta.yml — the descriptive half of the dataset’s metadata: abstract, methods, creators, citation, licence, DOI, contact, keywords (Metadata & the ingest loop)
citation_authority.json — a generated cache of what the source’s own authority (EDI, NCEI, ERDDAP, DataCite) says about the citation and licence. A proposal, never the record.

Shared, under metadata/:

Table 4.5: The shared registries under metadata/ in CalCOFI/workflows, and what each is the record of.

file	role
`field_dictionary.csv`	Prescriptive canonical field names, types, units and aliases; new datasets conform to it. `dwc_term` names the Darwin Core term a field publishes as.
`measurement_type.csv`	The canonical measurement vocabulary — description, `units`, `category`, `variable` (which types measure the same thing comparably across datasets), `derivation` for a derived type, `is_canonical`, the validity bounds, and the controlled-vocabulary ids `nerc_p01` / `units_nerc_p06` (below). Released as a table.
`variable.csv`	One row per crosswalk key `measurement_type.variable` points at (D3, Measurements) — its label, description, units, category and NERC P01, held once for the key rather than repeated on each member series. Read with `read_variable()`, appended with `register_variables()`, checked against `measurement_type.csv` with `check_variable_registry()`.
`measurement_qual.csv`	Each dataset’s quality-flag vocabulary, uninterpreted.
`category.csv`	The twelve data categories every dataset and measurement type is filed under.
`provider.csv`	The curating organizations. `provider` is the organization that holds and can license the data — not the portal that serves it, and not a collection or lab inside it.
`license.csv`	The licence ids a dataset may declare.
`life_stage.csv`	Every distinct `obs.life_stage` value with its Darwin Core label and NERC S11 id.
`gear.csv`	Every `sample.tow_type` code with its `samplingProtocol` sentence and NERC L22 device id.
`dataset_meta_fields.csv`	The tiers of authored dataset metadata (required, recommended, optional) and the guidance a provider sees for each; the field list every `dataset_meta.yml` follows.
`dataset_status.csv`	The pipeline-stage tracker, one row per dataset, and each dataset’s archive-of-record policy sentence.
`distribution.csv`	Every curated endpoint per dataset the release cannot measure itself — mirrors, archive records, the OBIS dataset, legacy ERDDAP ids with their successors — never deleted, only statused.
`distribution_observed.json`	Generated weekly: what each portal says now about every distribution; a change files a proposed provider question, never an edit.
`portal.csv`	The portals themselves — what each holds, how it can be queried, what it harvests from calcofi.io (Portals).
`holdings.csv`	Generated at release: the datasets CalCOFI holds but has not ingested, from their sidecars.
`taxon_group.csv`	The rules that assign taxa to the groups the apps offer (functional groups, “other”); a group label never becomes a taxon’s common name.
`questions_sheets.yml`	Which Google Sheet each provider’s questions and metadata tabs live in (Metadata & the ingest loop).
`relationships_cross.csv`	Cross-dataset foreign keys (intra-dataset ones live in each ingest’s `relationships.json`); every edge is measured at release (Keys and integrity).
`release_tables.csv`, `release_columns.csv`	Descriptions for tables built inside `release_database.qmd` itself.
`measurement_taxon.csv`, `taxon_override.csv`	How a taxon-bearing measurement name decomposes, and manual id resolution where a source taxon has no clean id.
`taxon_lineage.csv`, `taxon_xref.csv`	Generated caches of the WoRMS/ITIS classification chains and the WoRMS↔︎ITIS cross-reference. Safe to delete; they refetch.
`dataset.csv`	Deprecated. A dataset’s structural keys are the `calcofi:` YAML block in its ingest notebook; its descriptive metadata (abstract, citation, license, DOI, contact, …) is the `metadata/{provider}/{dataset}/dataset_meta.yml` sidecar, and the release reads the two merged (see Metadata & the ingest loop and Cite This Data). The CSV drifted from the notebooks and orphaned observations.

The per-dataset files above and the shared registries of Table 4.5 flow through two stages:

Per ingest: calcofi4db::build_metadata_json() writes data/parquet/{provider}_{dataset}/metadata.json (schema version 1.0) beside the parquet outputs, and reports its own documentation gaps.

Per release: calcofi4db::merge_metadata_json() merges every per-ingest sidecar plus the release-only registries into gs://calcofi-db/ducklake/releases/{version}/metadata.json (schema version 1.2):

{
  "schema_version": "1.2",
  "release_version": "v2026.09.06",
  "release_date":    "2026-09-06",
  "datasets":          { "calcofi_bottle": { ... } },
  "tables":            { "sample":  { "name_long": "Sample",
                                      "description_md": "One row per physical sampling event …",
                                      "provider": "swfsc", "dataset": "ichthyo" } },
  "columns":           { "sample.depth_min_m": { "name_long": "Depth Min",
                                                 "units": "m",
                                                 "data_type": "DOUBLE",
                                                 "description_md": "Shallowest depth of the event" } },
  "measurement_types": { "temperature": { "description": "...", "units": "degC",
                                          "is_canonical": true } },
  "contributions":     { "sample": { "swfsc_ichthyo": 0.62, "calcofi_bottle": 0.31 } },
  "erd_legend":        [ { "provider_dataset": "calcofi_bottle", "color": "#f5cad9" } ]
}

contributions is each dataset’s share of a shared table’s rows (the bar on a schema-browser card); erd_legend the colour each dataset takes in the diagram.

Markdown is supported anywhere in description_md; consumers should render it through a markdown parser when displaying.

4.3.4 Coverage is measured, never asserted

A dataset’s temporal and spatial extent is measured from the assembled data at release time — coverage_temporal_observed, coverage_spatial_observed and coverage_bbox in metadata.json, and coverage_temporal / coverage_spatial on the released dataset table (the measured values, under the plain names). Nothing hand-writes an extent.

This is not pedantry. A hand-written extent is authored once while the data grows underneath it: at v2026.08.06 seven of fifteen datasets were wrong — one claimed data through 2021-05 that ends 2015-04, three said “present” while stalling in 2019, 2022 and 2023. Measurement is also honest about what it finds, which is how two coordinate bugs surfaced that the prose had hidden (a dropped minus sign putting a dataset at 124.9° E, and a dataset reaching latitude 0.0). One dataset asserts its temporal half and says why in a comment: it is region-pooled and has no datetimes at all.

4.3.5 Citation and licence are a contract, and they are checked

Every dataset carries citation_main, license, doi, acknowledgement and contact in its dataset_meta.yml sidecar, and these are checked, not trusted — by the workflows index build and again by the release. A citation needs a year and a locator; license must be an active id in license.csv (custom requires a license_url; unknown or empty fails unless a provider question is open on that field); a doi is stored bare and must resolve. The check also asks the source’s own authority — EDI’s cite service, NCEI’s “Cite as”, an ERDDAP .das, DataCite — caches the answer, and reports drift without ever writing into the notebook: the author’s string is the record.

source_accessed is measured, not authored. And the release cites itself: catalog.json carries a citation, a concept_doi and, once minted, the version doi; the release notes open with “How to cite”; CITATION.cff and .zenodo.json are generated from the same source.

CalCOFI (2026). CalCOFI Integrated Database, release v2026.09.06 [Data set].
Scripps Institution of Oceanography, NOAA Fisheries, and California Department
of Fish and Wildlife. https://doi.org/10.5281/zenodo.22514953

(That is the release promoted as this page was written; the current one is in catalog.json and on Cite This Data.)

When you use these data, cite the release and the source datasets you used. The release dataset table carries each source’s own citation_main, license, doi and acknowledgement for exactly this purpose — one row per dataset, so a query that touched dataset_key already knows whom to credit.

4.3.6 Consuming descriptions

The fastest way to browse the schema is the CalCOFI Schema explorer — per-release ERD, sortable table/column lists with units, dataset provenance, and the measurement-type registry, all sourced from the release metadata.json sidecar on GCS. Deep-link to a specific release with #erd?v=v2026.08.25 (or #tables, #columns, #measurements).

From R, fetch the descriptions either column-by-column or as a full interactive catalog. Both calls hit the same release metadata.json sidecar:

library(calcofi4r)

# schema + descriptions + units for a single table
tbl <- cc_describe_table("sample")
attr(tbl, "description_md")    # table-level description

# interactive datatable of every table and column in the release
cc_db_catalog()
cc_db_catalog(tables = c("sample", "obs_bio"))
cc_db_catalog(version = "v2026.08.25")

When writing a new ingest, populate tbls_redefine.csv and flds_redefine.csv (especially fld_description and units) before running the ingest notebook — build_metadata_json() reads them directly. The Claude Code /generate-metadata and /validate-ingest skills enforce this.

4.3.7 Publishing to portals

Workflows in workflows/publish_to-*.qmd push selected tables to external portals — ERDDAP, EDI, OBIS — and write netCDF for the archives. They read the release catalog.json and metadata.json to assemble Ecological Metadata Language (EML), ERDDAP datasets.xml entries and netCDF attributes, so a description written once in a metadata registry reaches every portal without re-keying, and so no publish notebook builds a parquet path by hand.

The netCDF, ERDDAP, EDI and OBIS publishers are all generic — parameterised by dataset, driven by the release; the OBIS one (publish_to-obis.qmd, 2026-09-05) retired the per-dataset ichthyoplankton notebook. See Darwin Core / OBIS ENV-DATA mapping for what it reads, and Portals for the publishing steps. All four run after every promoted release and rebuild a dataset’s output only when its rows, metadata or the publisher’s code changed; publish_status.qmd then lists which portal copies are due a fresh upload (Portals).

ERDDAP (publish_to-erddap.qmd) discovers one config block per dataset_key from what the core actually holds, rather than a hand-maintained table list, and executes every view against the real release before writing its XML. Since the pre-release plan D-S1/D-S3 (2026-09) the {dataset_key} observation grain reads obs_bio (bio datasets) or obs_env (env datasets) through the release catalog (calcofi4r::cc_release_sources()) — never a hand-built releases/{v}/parquet path — carrying the sample’s own gear and effort (tow_type, std_haul_factor, prop_sorted, volume_sampled_m3) and the two canonical densities (density_per_10m2, density_per_1000m3, effort_class) inline, plus flag_values/flag_meanings on measurement_qual from metadata/measurement_qual.csv. A release cut before D-S1 (no obs_bio/obs_env in its catalog) falls back to the deprecated obs objects, with no effort/density columns.

4.4 Darwin Core / OBIS ENV-DATA mapping

The core model was designed against the OBIS ENV-DATA pattern described by De Pooter et al. (2017), so the shapes map one to one: sample’s adjacency list is an Event core, obs’s bio rows are an Occurrence extension, and the two measurement grains are extended Measurement-or-Fact tables. What was missing was never the shape — it was the vocabulary: an export had to guess which controlled term a CalCOFI measurement name meant, and the ichthyoplankton OBIS notebook did not guess, it wrote measurementTypeID = NA.

The ids now live in the registries the release already publishes, so a portal export is a lookup rather than a judgement.

Table 4.6: How each core table publishes as Darwin Core under the OBIS ENV-DATA pattern.

CalCOFI	Darwin Core / OBIS ENV-DATA	how it is constructed
`sample` (adjacency list)	Event core	`eventID` = `sample_key`; `parentEventID` = `parent_sample_key`; `eventDate` from `datetime` (a tow publishes as its start/end interval); `decimalLatitude` / `decimalLongitude`; `minimumDepthInMeters` / `maximumDepthInMeters` = `depth_min_m` / `depth_max_m`; `footprintWKT` from `geom`; `locationID` = `site_key`; `samplingProtocol` from `metadata/gear.csv` keyed on `tow_type`; `sampleSizeValue` = `volume_sampled_m3` with `sampleSizeUnit` = cubic metre
`obs_bio` (`obs` where `realm = 'bio'`)	Occurrence extension	`occurrenceID` from `obs_id`; `eventID` = `sample_key`; `scientificNameID` built from `taxon_key` (`urn:lsid:marinespecies.org:taxname:<worms_id>`, or the ITIS TSN for a bird); `scientificName` from `taxon`; `lifeStage` + its NERC S11 id from `metadata/life_stage.csv`; `organismQuantity` = `density_per_10m2` or `density_per_1000m3` with `organismQuantityType` naming that denominator
`sample_track`	the Event’s footprint (a survey segment or line of sight) — intended: no publisher writes it yet	`footprintWKT` = the `geom` `LINESTRING` as WKT in place of the point, with `footprintSRS` = `EPSG:4326`; `eventDate` as the interval `datetime_beg/datetime_end`; `decimalLatitude` / `decimalLongitude` stay the `sample` point
`sample_measurement`	eMoF on Events (net effort, cast conditions)	`measurementType` / `measurementValue` / `measurementUnit`, plus `measurementTypeID` = `measurement_type.nerc_p01` and `measurementUnitID` = `measurement_type.units_nerc_p06`
`obs_attribute`	eMoF on Occurrences (length and stage frequency)	`bin_value` / `bin_label` / `count` → `measurementValue`; ids as above
`obs_env` (`obs` where `realm = 'env'`)	eMoF on the bottle or depth Event	ids as above

Every row of Table 4.6 is a lookup in a registry the release already publishes, not a judgement made at export time. metadata/field_dictionary.csv carries a dwc_term column holding the full term URI for the 12 of 57 canonical fields that one Darwin Core term means exactly. A field Darwin Core splits — depth_m becomes minimumDepthInMeters and maximumDepthInMeters — or has no term for is deliberately left empty rather than mapped approximately.

4.4.1 An id is filled only on an exact match

Empty means “no concept in the vocabulary says exactly this”, never “nobody looked.” An id is written only when every facet the concept states — quantity, matrix, phase, method — is something the registry or the dataset’s documented protocol actually supplies. A generic concept is an exact match at coarser specificity: NERC P01 TEMPPR01, Temperature of the water body, is exactly what a QC’d bottle temperature is. A concept that adds a facet nobody recorded is not: IRRDUV01 pins PAR to a cosine-collector radiometer, which no CalCOFI metadata states, so PAR has no id.

Filling an id you are not sure of is worse than leaving it empty. An empty measurementTypeID is a portal record that says “this measurement is not mapped”; a wrong one is a record that confidently says the wrong thing, at an aggregator that will never ask.

Coverage as of v2026.09:

Table 4.7: Controlled-vocabulary coverage per registry — filled only on an exact match.

registry	column	filled
`measurement_type.csv`	`nerc_p01` (P01, `measurementTypeID`)	115 / 200
`measurement_type.csv`	`units_nerc_p06` (P06, `measurementUnitID`)	174 / 200
`life_stage.csv`	`nerc_s11` (S11, `lifeStage`)	10 / 23
`gear.csv`	`nerc_l22` (L22, `samplingProtocol` device)	4 / 11
`field_dictionary.csv`	`dwc_term`	12 / 57

The gaps in Table 4.7 are deliberate, and mostly not gaps. Of the 85 measurement types with no P01, 29 are taxon-bearing abundance, biomass or size types — P01 encodes the taxon in the concept, and CalCOFI carries it in taxon_key, so a per-type id there would be wrong rather than missing. Another 8 are event-level effort and sub-occurrence attributes BODC does not model as parameters. The genuine gaps are documented in RELEASES.md, and most of them are questions for a data provider rather than for the vocabulary: an unrecorded transmissometer wavelength, radiation channels that do not say whether they are upwelling or downwelling, flow-cytometry gating that was never written down.

4.4.2 Four caveats for anyone building an export

edna_presence from calcofi_2022-edna is detections only. Its source archive lists only positives, so a missing row is not a non-detection and occurrenceStatus = "absent" cannot be derived from it. The read counts (edna_reads_*) are a semi-quantitative signal of amplified DNA, not an abundance, and are not comparable between assays. Whether the archive omitted filters or assay runs with no detection is a question put to its provider and still open. (The Scripps cetacean eDNA screens, which did publish sequenced non-detections as 0, are held out of the release while their providers review them; see RELEASES.md.)
obs is positive-only for ichthyoplankton. A tow that was sorted and found nothing has no row, so occurrenceStatus = "absent" cannot be derived from zero values. Absences come from the sampling events (sample, root rows) minus the taxa observed on them — and a surveyed year with no positives is indistinguishable from an unsurveyed year if you count rows instead of events.
organismQuantityType is the whole meaning of the number. Areal (individuals per 10 m², from an oblique or vertical tow standardized by the haul factor) and volumetric (individuals per 1000 m³, from a surface Manta tow or any tow with a filtered volume) are not interconvertible without an integrated depth and a vertical-distribution assumption. Publish the denominator with the number, never a bare count.
Two life_stage values are not life stages. Euphausiid damaged marks specimens too damaged to stage (occurrenceRemarks), and ichthyoplankton invert is a provenance flag for the merged invertebrate counts. metadata/life_stage.csv says so per value; do not pass either through as lifeStage.

The generic publish_to-obis.qmd over the core reads exactly these registries; it replaced the per-dataset ichthyoplankton notebook on 2026-09-05 (see Portals).

4.5 Relationships between tables

Primary and foreign keys ship with every release as relationships.json beside catalog.json, and since 2026-09-08 every one of them is measured before the release is cut and the result ships as integrity.json. The keys, the joins worth memorising and the measured result are in Keys and integrity; the schema browser draws the diagram per release.

4.6 Spatial Tips

Use ST_Subdivide() when running spatial joins on large polygons.
All released geometry is tagged EPSG:4326, normalized immediately before the freeze. This matters because ST_Point(lon, lat) tags OGC:CRS84 while ST_Read() over GeoJSON tags EPSG:4326 — the same WGS 84 longitude/latitude — and DuckDB refuses ST_Intersects across differing tags. If you build geometry on one side of a join, tag both sides; do not coerce one to match the other, or the next release will break you.
NaN is not NULL, and it corrupts spatial queries beyond its own row. A NaN coordinate survives IS NOT NULL, and ST_Point(NaN, NaN) returns a real, non-NULL geometry that survives geom IS NOT NULL too. Worse, its presence makes ST_Intersects return different counts at different thread counts, dropping valid, unrelated pairs. The release normalizes NaN/Inf to NULL before minting geometry — but if you build your own, test isnan() and isinf() explicitly.