5 Keys and integrity

A database is only as trustworthy as its joins. This chapter states how the integrated database’s tables are keyed, what each key promises, and — because a promise is worth little unstated — what the release actually measures before it ships. Every table below is generated from the promoted release’s own sidecars, so the page and the database cannot disagree.

5.1 The rules

The suffix says what kind of value a column holds (Table 5.1), and this is checked in review, because a character-valued identifier named *_id is a promise the data cannot keep:

Table 5.1: What each column-name suffix promises about the value the column holds.

suffix	value	examples
`*_key`	a string natural key — meaningful, stable, human-readable	`cruise_key`, `site_key`, `grid_key`, `sample_key`, `taxon_key`, `dataset_key`, `ship_key`
`*_id`	an integer key — a source counter or a surrogate the release mints	`cast_id`, `bottle_id`, `obs_id`, `root_id`
`*_uuid`	the provider’s own identifier for that record, as they mint it (RFC 4122)	`cruise_uuid`, `source_uuid`, `station_uuid`
`*_seq`	an auto-incrementing sequence integer	—

(hex_id is the one integer that is neither a counter nor a surrogate: an H3 cell index at resolution 10, so coarser hexagons are h3_cell_to_parent(hex_id, res) rather than a column per resolution.)

Natural keys carry the integration. Four of them are worth stating exactly, because every dataset joins to the others through them:

cruise_key = YYYY-MM-NODC (1998-02-33JD): the month SWFSC designates for the cruise and the ship’s NODC code — never the month a cast or tow happened to fall in. A cruise routinely straddles a calendar boundary (5508BD ran 7 Aug – 25 Sep 1955) and the neighbouring month is usually a real cruise of the same ship, so keying by event month silently moved casts onto the wrong cruise with no foreign key ever failing. Events are keyed by containment in the reference cruise’s observed date span first, then by the source’s own designation, then by event month as a last resort, and which rule fired is recorded (cruise_key_method). The cruise reference itself is completed at release: one row per key an event names that SWFSC’s export has no station row for (cruise_key_method = 'derived'), which is why cruise holds more rows than SWFSC’s list.
sample_key = {dataset_key}:{sample_type}:{id} (calcofi_bottle:cast:7644): globally unique across datasets and across event levels, so a site, a tow, a net, a cast and a bottle share one table without collision. The hierarchy is an adjacency list — parent_sample_key points at the containing event, root_sample_key at the top of the chain — so counting distinct events at any level is one GROUP BY.
site_key is the CalCOFI station as "line station" ("090.0 062.0"); grid_key is the shared grid cell; dataset_key = {provider}_{dataset} is the provenance stamp on every observation.
taxon_key is a lowercase authority prefix plus that authority’s accepted id — worms:<AphiaID>, or itis:<TSN> for birds, whose WoRMS taxonomy lags (see The database).

The release mints one sequential integer, root_id on sample_root: a dense integer for each root sampling event (a sample row with no parent), numbered deterministically so a rebuild reproduces it, and carried on obs_bio and obs_env so a browser can join observations to their event without the string key.

5.1.1 Why not UUID primary keys?

NOAA’s own CalCOFI database keys cruises, stations and nets by UUID, and its curator has argued — from experience — that a compound natural key is a liability: when a ship is swapped or a date corrected, the key and the fields it was built from can disagree, and nothing stops a partial correction. The argument is right for a database that is edited. This one is not: a release is read-only, its keys are minted from the corrected fields at the freeze (create_cruise_key() refuses a blank or malformed NODC code outright), and the gate below fails the release if a key disagrees with its parts. What a natural key buys in return is legibility — 2019-07-39C2 says which cruise it is; a UUID says nothing until joined — and stability across the fifteen datasets whose sources mint no UUID at all.

The provider’s identifiers are released as typed columns beside the keys, never dropped: they are the join back to the provider’s own database.

cruise.cruise_uuid is NOAA/SWFSC’s CruiseId, populated for every SWFSC cruise — a public join key to NOAA’s CalCOFI database.
sample.source_uuid is the provider’s identifier for that event, exactly as shipped (SWFSC’s site_uuid on a site row, tow_uuid on a tow, net_uuid on a net), and NULL for the datasets whose sources mint none.
sample.station_uuid is the SWFSC station occupation any event belongs to, resolved at release, with station_uuid_method saying how (its own site, its parent’s, a match on cruise + station + occupation order, or the one ichthyoplankton occupation within 24 hours).

Wrangling-time provenance (_source_file, _source_row, …) is a different thing and is stripped at the freeze.

Figure 5.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.

5.2 The contract, per table

Every released table declares a primary key, and every foreign key between released tables — every arrow in Figure 5.1 — is declared in the release’s relationships.json (calcofi4db::core_relationships() for the core family and the tables the release builds; metadata/relationships_cross.csv for the edges that cross datasets). Table 5.2 reads that file for v2026.09.06.

Table 5.2: Keys declared for every table of v2026.09.06. A NULL in a foreign-key column is permitted (an env observation has no taxon; an ungridded sample has no grid cell); a non-NULL value must resolve.

table	tier	rows	primary key	foreign keys	referenced by
climatology	core	768,880	dataset_key, grid_key, month, depth_bin, measurement_type	grid_key → grid; measurement_type → measurement_type; dataset_key → dataset	0
cruise	core	842	cruise_key	ship_key → ship	7
dataset	core	16	dataset_key		11
dataset_taxon	core	1,917	ds_taxon_key	taxon_key → taxon; dataset_key → dataset	0
grid	core	218	grid_key		8
lookup	core	26	lookup_id		0
measurement_type	core	200	measurement_type		8
obs_attribute	core	458,184	obs_attribute_id	sample_key → sample; taxon_key → taxon; measurement_type → measurement_type; dataset_key → dataset	0
obs_bio	core	1,258,665	obs_id	root_id → sample_root; sample_key → sample; dataset_key → dataset; grid_key → grid; cruise_key → cruise; measurement_type → measurement_type; taxon_key → taxon	0
obs_env	core	25,006,583	obs_id	root_id → sample_root; sample_key → sample; dataset_key → dataset; grid_key → grid; cruise_key → cruise; measurement_type → measurement_type	0
region	core	4	region_key		0
sample	core	1,469,155	sample_key	parent_sample_key → sample; root_sample_key → sample; grid_key → grid; cruise_key → cruise; dataset_key → dataset; station_uuid → sample	8
sample_measurement	core	589,603	sample_measurement_id	sample_key → sample; measurement_type → measurement_type; dataset_key → dataset	0
sample_spatial	core	929,664	root_sample_key, spatial_key	root_sample_key → sample; spatial_key → spatial; root_id → sample_root	0
ship	core	49	ship_key		1
spatial	core	13,206	spatial_key		2
spatial_attribute	core	148,461	spatial_key, fld	spatial_key → spatial	0
taxon	core	2,614	taxon_key	parent_taxon_key → taxon	5
taxon_group	core	441	taxon_group_key, taxon_key	taxon_key → taxon	0
obs	deprecated	26,265,248	obs_id	sample_key → sample; measurement_type → measurement_type; taxon_key → taxon; grid_key → grid; cruise_key → cruise; dataset_key → dataset	0
obs_ctd_full	supplemental	271,394,164	obs_id	sample_key → sample; dataset_key → dataset; measurement_type → measurement_type; cruise_key → cruise; grid_key → grid	0
obs_mets_full	supplemental	19,927,416	obs_id	dataset_key → dataset; measurement_type → measurement_type; cruise_key → cruise; grid_key → grid	0
sample_root	supplemental	421,454	root_id	root_sample_key → sample; dataset_key → dataset; cruise_key → cruise; grid_key → grid	3

5.3 Measured, this release

Declaring a key is cheap. Since 2026-09-08 the release measures every declaration on the assembled tables before it freezes — calcofi4db::check_release_relationships() — and writes the result beside catalog.json as integrity.json. A duplicate or NULL primary key, or any non-NULL foreign key with no match, stops the release. The schema browser reads the same file, so a ✓ on a key there means measured, not declared.

For v2026.09.06: 23 primary keys and 57 foreign keys measured, 0 skipped, every key ok.

Table 5.3: Primary keys, measured — rows, distinct key values, duplicates, and rows with a NULL anywhere in the key.

table	key	rows	distinct	status
climatology	dataset_key,grid_key,month,depth_bin,measurement_type	768,880	768,880	ok
cruise	cruise_key	842	842	ok
dataset	dataset_key	16	16	ok
dataset_taxon	ds_taxon_key	1,917	1,917	ok
grid	grid_key	218	218	ok
lookup	lookup_id	26	26	ok
measurement_type	measurement_type	200	200	ok
obs	obs_id	26,265,248	26,265,248	ok
obs_attribute	obs_attribute_id	458,184	458,184	ok
obs_bio	obs_id	1,258,665	1,258,665	ok
obs_ctd_full	obs_id	271,394,164	271,394,164	ok
obs_env	obs_id	25,006,583	25,006,583	ok
obs_mets_full	obs_id	19,927,416	19,927,416	ok
region	region_key	4	4	ok
sample	sample_key	1,469,155	1,469,155	ok
sample_measurement	sample_measurement_id	589,603	589,603	ok
sample_root	root_id	421,454	421,454	ok
sample_spatial	root_sample_key,spatial_key	929,664	929,664	ok
ship	ship_key	49	49	ok
spatial	spatial_key	13,206	13,206	ok
spatial_attribute	spatial_key,fld	148,461	148,461	ok
taxon	taxon_key	2,614	2,614	ok
taxon_group	taxon_group_key,taxon_key	441	441	ok

Table 5.4: Foreign keys, measured — rows, rows whose key is NULL (permitted), and orphans (a non-NULL value with no match; any orphan fails the release).

edge	rows	NULL	status
climatology.dataset_key → dataset.dataset_key	768,880	0	ok
climatology.grid_key → grid.grid_key	768,880	0	ok
climatology.measurement_type → measurement_type.measurement_type	768,880	0	ok
cruise.ship_key → ship.ship_key	842	0	ok
dataset_taxon.dataset_key → dataset.dataset_key	1,917	0	ok
dataset_taxon.taxon_key → taxon.taxon_key	1,917	0	ok
obs.cruise_key → cruise.cruise_key	26,265,248	179,587	ok
obs.dataset_key → dataset.dataset_key	26,265,248	0	ok
obs.grid_key → grid.grid_key	26,265,248	316,895	ok
obs.measurement_type → measurement_type.measurement_type	26,265,248	0	ok
obs.sample_key → sample.sample_key	26,265,248	0	ok
obs.taxon_key → taxon.taxon_key	26,265,248	25,007,346	ok
obs_attribute.dataset_key → dataset.dataset_key	458,184	0	ok
obs_attribute.measurement_type → measurement_type.measurement_type	458,184	0	ok
obs_attribute.sample_key → sample.sample_key	458,184	0	ok
obs_attribute.taxon_key → taxon.taxon_key	458,184	781	ok
obs_bio.cruise_key → cruise.cruise_key	1,258,665	143,957	ok
obs_bio.dataset_key → dataset.dataset_key	1,258,665	0	ok
obs_bio.grid_key → grid.grid_key	1,258,665	209,033	ok
obs_bio.measurement_type → measurement_type.measurement_type	1,258,665	0	ok
obs_bio.root_id → sample_root.root_id	1,258,665	0	ok
obs_bio.sample_key → sample.sample_key	1,258,665	0	ok
obs_bio.taxon_key → taxon.taxon_key	1,258,665	763	ok
obs_ctd_full.cruise_key → cruise.cruise_key	271,394,164	0	ok
obs_ctd_full.dataset_key → dataset.dataset_key	271,394,164	0	ok
obs_ctd_full.grid_key → grid.grid_key	271,394,164	0	ok
obs_ctd_full.measurement_type → measurement_type.measurement_type	271,394,164	0	ok
obs_ctd_full.sample_key → sample.sample_key	271,394,164	0	ok
obs_env.cruise_key → cruise.cruise_key	25,006,583	35,630	ok
obs_env.dataset_key → dataset.dataset_key	25,006,583	0	ok
obs_env.grid_key → grid.grid_key	25,006,583	107,862	ok
obs_env.measurement_type → measurement_type.measurement_type	25,006,583	0	ok
obs_env.root_id → sample_root.root_id	25,006,583	0	ok
obs_env.sample_key → sample.sample_key	25,006,583	0	ok
obs_mets_full.cruise_key → cruise.cruise_key	19,927,416	0	ok
obs_mets_full.dataset_key → dataset.dataset_key	19,927,416	0	ok
obs_mets_full.grid_key → grid.grid_key	19,927,416	16,128	ok
obs_mets_full.measurement_type → measurement_type.measurement_type	19,927,416	0	ok
sample.cruise_key → cruise.cruise_key	1,469,155	23,739	ok
sample.dataset_key → dataset.dataset_key	1,469,155	0	ok
sample.grid_key → grid.grid_key	1,469,155	27,922	ok
sample.parent_sample_key → sample.sample_key	1,469,155	421,454	ok
sample.root_sample_key → sample.sample_key	1,469,155	0	ok
sample.station_uuid → sample.source_uuid	1,469,155	436,533	ok
sample_measurement.dataset_key → dataset.dataset_key	589,603	0	ok
sample_measurement.measurement_type → measurement_type.measurement_type	589,603	0	ok
sample_measurement.sample_key → sample.sample_key	589,603	0	ok
sample_root.cruise_key → cruise.cruise_key	421,454	23,149	ok
sample_root.dataset_key → dataset.dataset_key	421,454	0	ok
sample_root.grid_key → grid.grid_key	421,454	13,231	ok
sample_root.root_sample_key → sample.sample_key	421,454	0	ok
sample_spatial.root_id → sample_root.root_id	929,664	0	ok
sample_spatial.root_sample_key → sample.sample_key	929,664	0	ok
sample_spatial.spatial_key → spatial.spatial_key	929,664	0	ok
spatial_attribute.spatial_key → spatial.spatial_key	148,461	0	ok
taxon.parent_taxon_key → taxon.taxon_key	2,614	19	ok
taxon_group.taxon_key → taxon.taxon_key	441	0	ok

Two things Table 5.3 and Table 5.4 do not show, on purpose:

A NULL is not an orphan. Where a column is NULL the row has nothing to point at — an environmental observation has no taxon, a region-pooled sample has no grid cell, a root sample has no parent. The counts are shown so a reader can see how nullable an edge is; they are not failures.
One edge is knowingly undeclared. obs_mets_full.sample_key would point at sample, but the full-resolution meteorology mints one key per underway record and only the headline series’ events reach sample: 73,607 of its 2,168,850 distinct keys resolve. Declaring the edge would fail every release on some nineteen million orphans; leaving it silent would hide a real gap. It is recorded in the release notes and as CalCOFI/workflows#78 until the supplemental’s events either join sample or carry a documented per-record key.

5.4 What the release checks before it ships

Keys are one family of gate. The full list — what each checks, and whether it fails the release or only ratchets — is on the Releases page; the ones that concern keys are:

Table 5.5: The gates that stop a release outright, of which the first three are key gates.

gate	what it measures	on failure
`check_release_relationships()`	every declared primary key unique and non-NULL; every non-NULL foreign key resolving; written to `integrity.json`	stops the release
`check_core_pk_unique()`	the core family’s primary keys, before the release-built tables exist (the gate added after v2026.08.25 shipped 4,855 duplicate `sample_key`s)	stops the release
`check_cruise_key_integrity()`	`cruise_key` format, its month against the NODC code, the `sample`/`obs` → `cruise` edge, `cruise_uuid` hygiene, every event’s date within its cruise’s span (± 31 days, named exceptions only)	stops the release; three ratchets (span overlaps, derived cruises, NULL keys per dataset) may only ever go down
`validate_for_release()`	NULLs in any `_id` / `_key` / `*_uuid` column; latitude, longitude and counts in range; no empty table	NULLs stop the release; ranges warn
`check_obs_pair_parity()`	`obs_bio` + `obs_env` reproduce `obs` — row counts, distinct ids, a hash of every column, per realm and dataset	stops the release
`test_release.qmd` consumer contract	every query shape the apps and packages depend on, run against the frozen release three ways (objects, view, pair), plus the presence of an all-ok `integrity.json`	`latest.txt` is not promoted

The first three rows of Table 5.5 are the key gates proper; the last three catch a key problem on its way to a consumer.

5.5 Walking the keys

The joins worth memorising, as SQL against any release (see Access the data for the connection):

-- an observation to its sampling event, and up the hierarchy
obs_bio.sample_key          -> sample.sample_key
sample.parent_sample_key    -> sample.sample_key      -- the containing event
sample.root_sample_key      -> sample.sample_key      -- the top of the chain

-- the dense integer twin the browser objects use instead of the string key
obs_bio.root_id             -> sample_root.root_id
sample_spatial.root_id      -> sample_root.root_id    -- which polygons a root sample falls in

-- provenance and place, denormalized onto the observation so a rollup needs no join
obs_env.dataset_key         -> dataset.dataset_key
obs_env.cruise_key          -> cruise.cruise_key
obs_env.grid_key            -> grid.grid_key

-- what was measured, and of what
obs_env.measurement_type    -> measurement_type.measurement_type
obs_bio.taxon_key           -> taxon.taxon_key
(dataset_key, ds_taxa_code) -> dataset_taxon          -- the per-dataset vocabulary

-- back to the provider
cruise.cruise_uuid                                    -- NOAA's CruiseId
sample.source_uuid                                    -- the provider's own event id
sample.station_uuid         -> sample.source_uuid     -- the SWFSC station occupation

A worked example: every net tow in a cruise with the number of larvae observations under it, walking sample’s adjacency list once.

WITH tows AS (
  SELECT sample_key, root_sample_key, datetime
  FROM sample
  WHERE dataset_key = 'swfsc_ichthyo' AND sample_type = 'tow' AND cruise_key = '2019-07-39C2'
)
SELECT t.sample_key, t.datetime, count(o.obs_id) AS n_larvae_obs
FROM tows t
LEFT JOIN sample n ON n.parent_sample_key = t.sample_key          -- the nets under each tow
LEFT JOIN obs_bio o ON o.sample_key = n.sample_key AND o.life_stage = 'larva'
GROUP BY 1, 2
ORDER BY 2;