CalCOFI integrated database release v2026.08.25

Release date: 2026-08-25

sample is unique on its key — the 4,855-duplicate bug is fixed, and a gate now guards it

An earlier same-day cut of v2026.08.25 shipped sample with 1,472,100 rows for 1,467,245 distinct sample_keys — 3,345 bottles, 150 casts, 133 ichthyo sites and 13 underway samples appeared twice, identical except for a seafloor_depth_m differing in the 11th decimal. The seafloor stamp (new in that release) collapsed positions with unique() but joined them back with merge(), which compares coordinates as 15-significant-digit strings — two positions differing past that digit both matched every sample at either. Nothing checked sample for uniqueness; the release only warned on ship and cruise. 76,320 obs rows (35,047 bottle, 31,057 CTD, 6,032 zooscan, 1,482 ichthyo, …) joined twice through those samples, so any count or join keyed on sample over-counted them by exactly 2×.

• sample is unique on sample_key again (calcofi4db 3.23.3 maps positions back by exact index and errors on a duplicate), and check_core_pk_unique() fails the release on any core table that is not unique on its primary key.
• Consumers: anything built from v2026.08.25 (the cruise, hex and CTD apps' local databases, the station portal's derived JSON) over-counted those 4,855 samples until rebuilt on this release.

Release tables are content-addressed, and written deterministically

Between v2026.08.14 and v2026.08.25 only 52 MB of the 2.09 GB release was byte-identical, and tables whose row counts had not changed (obs_mets_full, taxon, cruise, measurement_type) still differed byte-for-byte: the release writes carried no total order and ran multi-threaded. Every released table is now written by one function with a unique ORDER BY, a single writer thread and pinned parquet options, so the same rows always give the same bytes.

• Schema: the provenance columns (_source_file, _source_row, _source_uuid, _ingested_at) that cruise, ship, lookup and a few reference tables still carried are no longer in the release — _ingested_at changed on every ingest and would have made every table look changed. lookup and spatial_attribute are exported from the assembled database like every other table instead of being copied from the ingest bucket.
• catalog.json keeps name/rows/partitioned/supplemental and adds, per table, content_hash, bytes and objects[] — one entry per parquet object with its path, bytes, sha256, content_hash and since (the first release that shipped that content; for partitioned tables, per partition). Consumers that only read table names are unaffected.
• Uploads: an object whose content is unchanged since the previous release is reused (GCS server-side copy) rather than uploaded; a release's upload is now its delta.
• Where the bytes live. Each object is stored once, under gs://calcofi-db/ducklake/tables/{table}/{content_hash}/{table}.parquet (partitioned tables: …/{table}/{col}={value}/{content_hash}/data_0.parquet), and every release whose catalog points at it shares it. The familiar releases/{version}/parquet/{table}.parquet path is a real copy only for the promoted version and the consolidated ones (below); on https://storage.calcofi.io/calcofi-db/… a legacy path that has no copy redirects (302) to the canonical object while it exists. Resolve tables through catalog.json objects[].path — calcofi4r::cc_release_sources() (1.11.0), calcofi4py.release_sources() (0.4.0), and the same rule in db-query, db-viz-station, ctd-transects, db-viz-hex, the apps, ERDDAP's parquet sync and the PostgreSQL release.* views — rather than building the path by hand.

Archive thinning: consolidated and retired versions

28 releases held 157 GB, most of it byte-identical tables re-uploaded under a new version directory. versions.json now says which versions keep their parquet: consolidated: true for v2026.04.08 (last per-dataset schema), v2026.05.14 (docs examples pin it), v2026.06.26, v2026.07.17, v2026.08.14 and v2026.08.25, plus always the promoted version and the one before it (metadata/release_policy.yml). Every other version keeps its catalog.json, metadata.json, relationships.json and RELEASE_NOTES.md — the record stays complete — and loses its parquet/; its entry carries retired: {retired_utc, to, reason} naming the nearest kept version, cc_get_db() and cc_get_db (py) refuse it with that name, and its release page says so. Pin a consolidated version for reproducibility; pin any other and plan to move.

A quality flag now reaches every consumer, not just the database

Ralf Goericke reported a 2.18 ml/L oxygen spike at 1,144 m on station 080.0 160.0 in the station data finder. The value is real and was already flagged: bottle 198640 of cast 7644 (cruise 5508BD, R/V Black Douglas, 3 Sep 1955) carries O_qual = 8 (suspect) in the CalCOFI Bottle Database. Two gaps let it through. The registry mapped o_qual onto oxygen_ml_l and oxygen_saturation but not oxygen_umol_kg — the form the app plots — so the flag was dropped at ingest; and no consumer filtered on measurement_qual at all.

• oxygen_umol_kg now carries o_qual; the CTD unit-conversion siblings (oxygen_umol_kg_1/2, oxygen_saturation_1/2, potential_temperature_1/2) carry their sensor's ox1q/ox2q/ temp1q/temp2q. The bottle ingest writes 8, not 8.0, like the CTD ingest.
• The pre-QC r_* bottle types deliberately stay unflagged (Q09 to the provider): code 6 "OK but taken from CTD" describes a substitution made during QC and would mislabel ~36k rows.
• Consumers: one NULL-safe predicate per language — calcofi4r::cc_qual_ok_sql() (1.9.0), calcofi4py.qual_ok_sql() (0.3.7), db-query qualOkSQL() — applied in db-viz-station, db-viz-hex, ctd-transects, ctd-viz, db-query and the calcofi4r matchers/transects. On this release it excludes 35,587 bottle, 6,138 CTD and 51 DIC rows. Flagged values remain in the database with their codes; nothing is deleted.

cruise_key is the cruise's designated month, resolved by date span

YYYY-MM in cruise_key was each cast's or tow's own calendar month. A CalCOFI cruise routinely straddles a month boundary (5508BD ran 7 Aug – 25 Sep 1955; 184 of the 664 bottle cruises span two months) and the neighbouring month is usually a real cruise of the same ship, so the shorn-off casts landed on the wrong cruise with no FK ever failing: v2026.08.14 released 664 source bottle cruises as 799 keys, with 5,941 of 35,644 casts on a key their own source disagrees with. Seven other ingests keyed tows the same way.

• The ichthyo ingest stamps every reference cruise's observed date_min/date_max (new columns on cruise); every other ingest resolves span containment first (same ship, ± 3 d — no two cruises of one ship overlap), then the source's own designation (bottle Cruise = YYYYMM), then the event month. Bottle: 799 → 657 keys; 5508BD is 34 casts and 5509BD 22, as in the source.
• The reference wins when sources disagree on a designation (ichthyo calls the 9 Feb – 29 Mar 1984 Jordan cruise 8403, the bottle database 8402); the bottle notebook reports those cases.
• Second-order bug fixed on the way: the bottle CSV reader typed the all-digit Cruise column as DOUBLE ('195508.0'), which silently defeated the designation step on the first attempt.

Depth is a coordinate, and it is now bounded

The previous release contained a CTD "cast" with scans at 14,671 m over a 101 m seafloor — a fluorometer test dip (0010_001d) from the db-csvs/orig/ folder of the 2000-10 New Horizon archive, which the tier classifier matched by substring. Its 17,964 dbar pressure value had been deleted by the declared bound; the depth derived from it had not, because bounds apply to values, not coordinates.

• sample.seafloor_depth_m (new column): bilinear GEBCO 2025 depth at every sample position (positive down, 0 on land, NULL outside the raster — 99.5 % of samples have one).
• Release gates: a depth beyond 6,500 m (the pressure ceiling) or NaN/negative fails the release (0 violations); samples deeper than the deepest GEBCO cell within one cell of their position + 10 m are reported and ratcheted (DEPTH_SEAFLOOR_OVER_MAX = 694, only ever down) — all but the test cast are 1949–1975 casts and tows on slopes and canyons with minute-rounded positions, so the measurement is fine and the place is imprecise. Never deleted.
• The CTD ingest excludes superseded orig*/uncorrected/ exports (every cast in them is also in the top-level file, except that test dip) but keeps separate_runs/ (20-1104SH's casts 031–036 exist nowhere else).

Two calcofi.org archives arrived with casts nobody can place

The 19-9604JD and 19-9608NH FinalQC archives, fetched from calcofi.org for the first time, carry 9,225 "RATHBURN CORE STN" scans with -99 positions and the station name in the line/station columns. They cannot enter sample/obs; the ingest now lists and drops them under a 0.2 % ratchet instead of failing (Q27 asks whether coordinates exist). R's default 60 s download timeout, which truncated every ~30 MB calcofi.org fetch at 15–25 MB, is now one hour.

Packages: calcofi4db 3.20.1, calcofi4r 1.9.0, calcofi4py 0.3.7. Consumers rebuilt: db-viz-station, ctd-transects (both had a broken DuckDB installer step, | sh → | bash), db-viz-hex, h3t API, db-viz-cruise, ERDDAP, db-query, ctd-qaqc, and ctd-viz — whose prep_db.R had needed the retired ctd_cast/ctd_thin/ctd_summary parquet since the core consolidation and had served a 2026-05-15 database for three releases; it now builds from sample/obs. Open: ERDDAP flag_values/flag_meanings on measurement_qual; netCDF *_qc companions.

Contents (generated)

18 tables, 320,260,205 rows, 2.10 GB.

Datasets (16): calcofi_bottle, calcofi_ctd-cast, calcofi_dic, calcofi_mets, calcofi_phyllosoma, calcofi_phytoplankton, cce-lter_euphausiids, cce-lter_picoplankton-bacteria, cce-lter_zoodb, cce-lter_zooscan, cdfw_dungeness-crab, farallon_bird-mammal, sio_mesopelagic-fish, sio_pic-zooplankton, swfsc_cufes, swfsc_ichthyo

Validation: 28 pass / 0 fail / 4 skip (consumer-contract suite, 2026-08-25T14:06:36Z).

How to cite

CalCOFI (2026). CalCOFI Integrated Database, release v2026.08.25 [Data set]. Scripps Institution of Oceanography, NOAA Fisheries, and California Department of Fish and Wildlife. https://calcofi.io/db-schema/?v=v2026.08.25

Cite the source datasets you use alongside the release:

• calcofi_bottle — CalCOFI. (2023). CalCOFI Bottle Database 194903-202105. CalCOFI.org. · license pending
• calcofi_ctd-cast — CalCOFI. (2023). CalCOFI CTD Cast Files. CalCOFI.org. · license pending
• calcofi_dic — Keeling, C.D.; Lueker, T.J.; Emanuele, G.; Dickson, A.G.; Martz, T.R.; Wolfe, W.H.; Mau, A. (2025). Discrete profile dissolved inorganic carbon, total alkalinity, water temperature and salinity measurements for CalCOFI (NCEI Accession 0301029). NOAA NCEI. https://doi.org/10.25921/3w9f-jd72 · CC BY 4.0
• calcofi_mets — CalCOFI. Underway (METS) TSG/Meteorology Data. CalCOFI.org. · license pending
• calcofi_phyllosoma — citation pending · license pending
• calcofi_phytoplankton — citation pending · license pending
• cce-lter_euphausiids — citation pending · license pending
• cce-lter_picoplankton-bacteria — Landry, M. (2004-2023). Picoplankton and Bacteria Abundance (CalCOFI Cruise). CCE LTER. · license pending
• cce-lter_zoodb — citation pending · license pending
• cce-lter_zooscan — citation pending · license pending
• cdfw_dungeness-crab — Rogers-Bennett, L.; Jones, E.; Klemmedson, A. (2026). CDFW Dungeness Crab Megalopae from archived CalCOFI plankton samples (1949-2014). California Department of Fish and Wildlife, published through CalCOFI / Scripps Institution of Oceanography. · CC BY 4.0
• farallon_bird-mammal — citation pending · license pending
• sio_mesopelagic-fish — Koslow, J. Anthony (2016). CalCOFI Trawl Data. In California Cooperative Oceanic Fisheries Investigations (CalCOFI): Acoustic and Trawl Data. UC San Diego Library Digital Collections. https://doi.org/10.6075/J0BZ64DH · CC BY 4.0
• sio_pic-zooplankton — citation pending · license pending
• swfsc_cufes — citation pending · license pending
• swfsc_ichthyo — NOAA Fisheries SWFSC. CalCOFI Ichthyoplankton Database. · license pending

Access

con <- calcofi4r::cc_get_db(version = "v2026.08.25")

con = calcofi4py.cc_get_db("v2026.08.25")

Parquet: https://storage.googleapis.com/calcofi-db/ducklake/releases/v2026.08.25/parquet/{table}.parquet; full history: RELEASES.md.