17 Status

Dated entries, newest first. From 2026-09 each entry is organized by the five phases of the data management plan — Ingest, Publish, Integrate, Visualize, Synthesize — one per quarter of the 2026–27 statement of work (Q1 due 2026-11-30); the entries before it follow the three components of the 2025–26 contract. Each is written by reviewing the git histories across the CalCOFI repositories since the entry before (libs/status_git-logs.R).

17.1 2026-11-02

The first quarter of the 2026–27 statement of work (2026-09-01 → 2026-11-30), reviewed on 2026-11-02 from the git histories of the CalCOFI repositories. Fourteen frozen releases were cut (v2026.09.04 → v2026.10.30, now 20 datasets), the CTD casts are served on ERDDAP, the CTD profile database is documented, and the bottle migration is written up below. Table 17.1 is the deliverable-by-deliverable crosswalk against the SoW; the sections after it follow the five phases.

Table 17.1: Q1 deliverables of the 2026-09 → 2027-08 SoW, against the SoW.

Q1 deliverable (SoW task)	Status	Where
Cloud-hosted integrated database with internal and external read access, and access guidance (Task 11)	✅ complete	Access the data, Server access
CTD casts → ERDDAP, live, backend choice documented from the benchmark (carryover)	✅ complete	CalCOFI ERDDAP, benchmark report
Bottle → non-proprietary database: schema documentation and migration write-up (Task 1)	✅ complete	Bottle: from the Access master to the release, below
CTD profile database integrated with bottle samples: data dictionary and schema documentation (Task 2)	✅ complete	CTD QA/QC, Server access
Naming-convention work begun: inventory of legacy names and a first draft of the conventions (Tasks 12–13)	✅ complete	Naming conventions, Providing data
Versioned frozen releases; quarterly status report; participation in DMP guidance and review	✅ complete	Releases, this entry

Ingest. The release now holds 20 datasets. New in the quarter: the CTD derived hydrographic products (calcofi_ctd-derived, 2026-09-23), the bottle-derived spice and TEOS-10 density at every bottle since 1949 (calcofi_bottle-derived, v2026.10.20), eight 1995–98 CTD cruises (v2026.10.20), SWFSC’s September and October ichthyoplankton and CUFES exports (v2026.10.05, v2026.10.20), and the cetacean sightings, sonobuoy and eDNA data, which are ingested and held out of the release (v2026.10.08). Betty Huang staged five CCE-LTER datasets (HPLC pigments, POC/PON, TOC, size-fractionated chlorophyll) and the SWFSC CTD-NOAA series; they are ingested, not yet released. Provider questions and answers now live in per-provider Google Sheets that sync with git, with a standing consent question on every dataset. The July 2009 bottle and picoplankton casts, which sat on a cruise of their own (2009-07-31M4), now join their cruise 2009-07-33MH (v2026.10.30).

17.1.1 Bottle: from the Access master to the release

The bottle data used to live in a 2.0 GB Microsoft Access database, the hydrographic master (CalCOFI_4903-2304_Master_Final_through_2105, 1949-03 → 2023-04). It is now a release dataset (calcofi_bottle) in the open Parquet lake, ingested from the published extract by the same pipeline as every other dataset, and the Access master is kept as a frozen archive that was mined, not replaced. Task 1 therefore has three parts, all done:

The master is documented. It was extracted to Parquet and SQL (28b0558, 2026-07-25), which gave a 1,715-line schema.sql, the 13 declared relationships, 182 field descriptions and a disposition for each of its 65 tables: 30 working copies or staging tables skipped, 11 documentation and metadata tables harvested into the registries, 9 QC reference tables kept for the CTD QA/QC work, 7 covered by an existing release dataset, 6 documented and deliberately not ingested, and 2 reconciled against the release. All 155 saved queries were inventoried and triaged by purpose (52 validation, 35 derived products, 31 correction history, 16 materialization, 8 exports, 13 unclassified) and scored for porting hazards. The master’s 280-entry change log is kept as data.
The release is verified faithful. Reconciled against the release of the time (e8a9a77), the release is a strict subset of the master: of 36,217 casts and 909,076 bottles in Access, 35,644 and 895,371 are in the release and none are release-only. The Access-only rows are exactly seven cruises, 202107–202304, newer than the “through 2105” extract the release was built from. Across about 6.0 million shared bottle measurements in 13 measurement types, one value differs by more than 1e-4 (oxygen, 3e-4). The comparison also found 85 bottles deleted from the master after the export was made (cruises 202105, 202111, 202208, 202211), none of them in the release.
The migration path is settled. No Access data is imported into the integrated database. The master is mined for the quality-code vocabulary, the −99 sentinel, method and accuracy provenance and the climatology that its QC queries encode, which feed the CTD ingest and the CTD QA/QC store; new bottle cruises arrive through the ingest, not through Access. The remaining QA/QC verification against each new release is the Q2 deliverable.

The CTD profile database (Task 2) is the PostgreSQL working store described in CTD QA/QC: 409 cast files and 10,812,360 scans loaded verbatim in all 82 source columns (workflows 1c82462, 2026-08-19), 20,521 best-stage casts for 1993–2026, a flag ledger with an audit trail, and a data dictionary (ctd.scan_column) generated from the measurement-type registry. Its casts key to the release’s cruise and ship tables, the public release reads inside PostgreSQL as release.* views, and one DuckDB connection joins both through cc_get_db() and cc_pg_attach(). Accepted flags reach the next ingest through a nightly snapshot (cf741ce, c5a72fc). Open: adoption of the store by the CTD team.

Publish. CTD casts are on ERDDAP: calcofi_ctd-cast, calcofi_ctd-cast_full (full resolution), calcofi_ctd-cast_sample, calcofi_ctd_measurement and calcofi_ctd_thin, among 45 tabledap datasets. The wide table that exhausted ERDDAP’s heap is resolved by the benchmark’s recommendation, now the rule for every dataset: ERDDAP EDDTableFromDatabase over DuckDB views on the release Parquet, in long format, with NetCDF files generated outside ERDDAP for CF users (benchmark report). The full-resolution table is queryable but not bulk-downloadable (bulk access is the Parquet release), and after a single unconstrained request took the server down on 2026-09-08 ERDDAP refuses bulk dumps (server 0965ce1, written up in the server’s incident log). Publishers rebuild only what a release changed, so EDI, OBIS, NetCDF and ERDDAP follow each release. Open: the EDI and OBIS deposits wait on provider licence and agreement decisions.

Integrate. The naming conventions are generated from the registries the pipeline enforces (Naming conventions); the “Legacy names, mapped” section is the Q1 inventory, a crosswalk per dataset from every source column to its standard name, with ship renames and cruise-key corrections kept as registries. Providing data is the provider guide in draft, and Keys and integrity states and measures the four integrating keys at every release; its cruise check gained an eleventh test on 2026-10-30. The station grid was rebuilt to one cell per official station, with a crosswalk (v2026.10.05).

Visualize. The Explorer runs over the release in the browser; every dataset, species and measurement has a generated page, and the measurement pages are the first variable-based entry to the data. calcofi4r is at 1.29.0 and calcofi4py at 0.9.4, with per-cast and seasonal climatologies and anomalies (v2026.10.13). Open: the authoritative inventory with variable-based discovery (Task 14, beta in Q3).

Synthesize. This book is the running record and the living document: its facts are generated from the release. DMP working sessions with CalCOFI program staff were held on 2026-09-03, 2026-09-08, 2026-09-23, 2026-10-01 (with Mark Gold) and 2026-10-12. Next (Q2): bottle QA/QC verification against the release, CTD integration validation, the draft naming guide and mapping tables, the NetID/UUID advisory, the invertebrate ↔︎ ichthyoplankton crosswalk and the first recorded webinar.

17.2 2026-09-08

The state of the system at the start of the new contract, by phase. The promoted release is v2026.11.05 (20 datasets, 26 tables, 385,903,651 rows, DOI 10.5281/zenodo.23993413).

Ingest. 20 datasets run through one pipeline of reproducible notebooks. The CTD team’s PostgreSQL working store (2026-08-19) holds the cast archive verbatim with a flag ledger whose accepted flags would reach the next ingest through a nightly snapshot; it is built, running and documented, but not yet adopted — CTD flags still arrive with the provider’s files, so the overview figure shows the system without it (CTD QA/QC has the figure with it in). Open: adoption by the CTD team; the bottle database migration (Task 1) waits on the profile database and training.

Integrate. Every event keys to its cruise by date span (v2026.08.25); provider UUIDs are released as columns beside the natural keys (v2026.09.04); as of this entry every released table declares a primary key and every declared key is measured before a release ships (integrity.json: 26 primary and 61 foreign keys, all ok). The naming conventions are generated from the registries and the provider guide is drafted (Tasks 12–13, Q1 inventory).

Publish. One record and one EML document per dataset are generated at every release, and from them the dataset pages, DCAT, STAC, the Darwin Core archives, the EDI packages and ERDDAP’s metadata; every release has a DOI. Open: the OBIS and EDI uploads wait on provider decisions; CTD casts on ERDDAP (Q1).

Visualize. The Explorer (2026-08-29, six lenses since 2026-09-07) runs in the browser over the release and supersedes the Shiny viewers; the landing page is a dataset catalog with a page per dataset; the schema browser shows each key measured. Open: variable-based discovery (Task 14, Q3–Q4).

Synthesize. This book was restructured into five parts by audience with the plan’s phases as its spine, its facts generated from the release, its diagrams zoomable, and its PDF the living document the plan’s Task 16 asks for.

17.3 2026-07-01

This report covers the second half of the 2025-10-01 to 2026-06-30 contract period (work since the 2025-12-01 status) and serves as the end-of-contract wrap-up. It is organized around the three contract components — Ingest, Visualize, and Share — and closes with a deliverables crosswalk against the Statement of Work. Headline outcomes: all eight must-complete (**) datasets are ingested into a versioned, reproducible database; nine releases were published (v2026.03.25 → v2026.06.08, now 44 tables); a new on-the-fly hexagon tile service powers the integrated app; and CalCOFI data are now flowing to ERDDAP and OBIS, browsable through new schema and query explorers.

17.3.1 1. Ingest — A Versioned, Reproducible Integrated Database

The bulk of the period’s effort went into the ingestion pipeline (workflows) and its engine package (calcofi4db), which together turn heterogeneous source files into a single, versioned database.

All must-complete datasets ingested. Building on bottle, fish eggs & larvae (ichthyo), and CTD casts, the team added DIC (dissolved inorganic carbon), phytoplankton (Venrick, EDI #28, region-pooled grain with WoRMS taxonomy), lobster phyllosoma (EDI #33), CUFES (#35), euphausiids/krill, PIC zooplankton, and bird & mammal census data. Cephalopod/invertebrate counts were folded into the ichthyo dataset rather than maintained separately. Release v2026.06.08 now spans 44 tables.
Frozen, reproducible releases (DuckLake + Parquet on GCS). release_database.qmd acts as the pipeline “caboose”: it validates, freezes, and uploads each release to a Google Cloud Storage bucket as Parquet, with an auto-generated entity-relationship diagram and a metadata.json sidecar carrying full table/column descriptions, units, and data types. Nine releases were cut between March and June 2026.
A hardened, well-keyed data model. Identifiers were standardized on stable keys — UUID-first source keys, cruise_key as YYYY-MM-NODC, and sta_key → site_key. The CTD data — by far the largest input — was tamed with a new adaptively-thinned ctd_thin table (5.5M rows / ~258 MB, preserving inflections via Douglas-Peucker) replacing the 15+ GB raw measurement table as the headline product, and several duplicate-key and phantom-cruise bugs were fixed.
calcofi4db formalized (v2.4 → v2.8). New capabilities include content-hash deduplication of Parquet uploads (ignoring provenance columns), YAML-authoritative dataset metadata, native GEOMETRY storage with ST_Hilbert spatial ordering, dependency VIEWs, and server-side GCS copy — which together cut a full pipeline run from 60+ minutes to ~4 minutes. A reusable set of Claude Code ingest skills (explore → generate-metadata → ingest → validate) and cross-dataset match helpers now make adding a new dataset largely turnkey.
Data-lake migration. Source contributions were migrated from a personal Google Drive to the organizational CalCOFI Data Folder shared drive, with rclone-based GDrive→GCS sync via a dedicated calcofi-admin service account (provisioned with Erin’s help), establishing the long-term archive.

17.3.2 2. Visualize — On-the-Fly Hexagons, Management Areas, and Partner Dashboards

On-the-fly hexagon tile service (the cached-endpoint deliverable). The integrated app previously pre-computed hexagon summaries for all zoom levels across the entire study area. A new H3T tile API now summarizes only the current zoom level and map extent on demand. It was prototyped as an R/Plumber service (api-h3t), then rewritten as a FastAPI service (api-h3t-py) with multi-database support, fronted by Varnish caching and edge gzip/zstd compression at h3t.calcofi.io. The integrated app reads hex data from it behind a USE_H3T flag.
Management areas of interest. The integrated app (db-viz-hex) gained spatial layers for maritime zones, marine protected areas, and wind energy areas, plus a dark/light theme, reproducible query/data download bundles, and a direct connection to the calcofi4r Parquet database (replacing the old API dependency).
Standalone and partner apps. New/redesigned Shiny apps include ctd-viz (linked map/table, plotly transects, GEBCO bathymetry), a datacheck cross-dataset observation explorer with deep-linkable URLs, and a cruises explorer. Three 2026 student-capstone visualization products were also supported and now ship from GitHub Pages: the UCSB Station Data Portal, the UCSB Larvae Dashboard, and the UCLA California Ocean & Coastal Monitoring Inventory map — extending the partner coalition envisioned in the SOW.

17.3.4 Deliverables Crosswalk

Against the SOW (** = must-complete by end of contract); Table 17.2, Table 17.3 and Table 17.4 are that crosswalk, deliverable by deliverable:

Ingest — all must-complete datasets done:

Table 17.2: Ingest deliverables of the 2025-10 – 2026-06 contract, against the SOW.

Dataset	Status
Bottle Database **	✅ ingested
Fish Eggs & Larvae **	✅ ingested (ichthyo)
CTD Cast Files **	✅ ingested (`ctd_thin` + `ctd_cast`)
Krill / euphausiids **	✅ ingested
Zooplankton biovolume **	✅ ingested (PIC)
Cephalopod / invertebrate **	✅ folded into ichthyo
Phytoplankton (Venrick) **	✅ ingested (#28)
DIC **	✅ ingested
CUFES, lobster phyllosoma, bird/mammal census	✅ ingested (beyond must-complete)

Visualize:

Table 17.3: Visualize deliverables of the 2025-10 – 2026-06 contract.

Deliverable	Status
Add management areas (sanctuaries, MPAs, wind areas)	✅ added to db-viz-hex
On-the-fly hexagon endpoint by extent/zoom	✅ H3T tile API live
Expand hex summaries across new datasets	🔄 in progress as datasets land

Share:

Deliverable	Status
CTD Cast Files → ERDDAP **	🔄 wide-table OOM under investigation (backend benchmark)
Bottle Database → ERDDAP **	✅ published
Fish Eggs & Larvae → OBIS **	✅ workflow complete
Other portals (EDI, NCEI, additional OBIS)	🔄 / ⏳ as prioritized
Recorded webinar series	⏳ pending (progress deck delivered)

17.3.5 Program Coordination

Beyond code, the period included sustained coordination with the CalCOFI program (Erin Satterthwaite and team): contributing to the Data Management Plan, gathering input on a dataset search tool (categorizing variables by EOVs), scoping CalOOS/SCCOOS portal integration, mentoring the UCSB/UCLA capstone teams, and contributing to an MBON ocean-indicators manuscript and several joint proposals (GOMO AI/Data pilots, IOOS OTT). EcoQuants LLC’s CalCOFI work transitioned to Ocean Metrics LLC during this period.

17.4 2025-12-01

Over the past several months, CalCOFI’s software and data systems have advanced significantly toward a unified, reliable, and user‑friendly platform for exploring and publishing CalCOFI data. Work has focused on four main areas: the integrated data app, the underlying database and workflows, pushing to OBIS, and organizing of CTD data.

17.4.1 A More Capable, User-Friendly Integrated App

The CalCOFI integrated application (db-viz-hex) has evolved into a much richer and more intuitive tool for scientists and partners:

Taxonomy-aware exploration
The app now understands species and their taxonomic relationships. Users can browse taxa hierarchies, see taxonomic ranks, and work with improved species metadata tied to authoritative sources (e.g., WoRMS, ITIS). This makes it easier to find and compare species and groups of species consistently.
Better visual experience and theming
A new dark/light theme toggle has been implemented and refined so that maps, time series, and other plots remain readable and visually consistent. Navigation has been reorganized, with a clearer About page, a guided “tour” of the app, and more intuitive icons and labels, making the app easier to learn and use.
Stronger spatial and temporal tools
Spatial maps now rely on efficient hexagon grids calculated in the database, improving performance and scalability. Default settings for time and depth matching have been tuned to yield better joins between environmental and biological data out of the box.

Overall, the app is moving from a prototype to a polished, guided interface that better supports exploratory analysis and communication.

17.4.2 A Stable, Well-Documented Database Foundation

The CalCOFI database package (calcofi4db) has been formalized and versioned, providing a solid foundation for all downstream tools:

Two stable releases (versions 1.0 and 1.1) have established a reliable baseline for the database, including bottle-level data.
The project now follows a clear strategy for separate development and production databases, reducing risk when making changes and improving reproducibility.
Data ingestion from NOAA and other sources has been hardened, with several rounds of fixes to handle edge cases and ensure that raw files are consistently and correctly translated into the database.

In addition, the package’s online documentation site has been refreshed so that developers and analysts have up-to-date guidance on how data flow into and through the database.

17.4.3 Unified R Tools and Documentation Around DuckDB

Across the toolchain, CalCOFI has standardized on DuckDB as the core data engine:

The R package (calcofi4r) now encapsulates key logic originally developed inside the integrated app, so the same high-quality data access and processing is available in scripts, reports, and analyses—not just in the web interface.
Both the app and R package can connect to local or remote DuckDB databases, improving performance and enabling offline or near‑offline workflows.
Documentation in the docs repository has been updated to describe the full data creation process (from raw data to ready‑to‑use databases) and to explain the new development/production database strategy. Status documents and helper scripts provide clearer visibility into project progress.

This brings CalCOFI closer to a coherent, documented platform where analysts can move seamlessly between app-based exploration and scripted analysis.

17.4.4 Standards-Compliant Publication of Biological Data

The workflows repository has seen major progress in turning CalCOFI’s biological datasets into publication-ready products:

New workflows now publish larval data to OBIS, the global biodiversity information system, with repeatable recipes that combine biological observations with CTD (oceanographic) data.
The underlying data model for events and occurrences has been strengthened to align with international standards (e.g., Darwin Core), including:
- Clear hierarchies of sampling events,
- Better handling of life‑stage and size information (e.g., egg and larval stages),
- Automated generation of metadata files required for data archives.
Additional integrity checks and foreign key relationships help ensure that data are correct and consistent before publication.

These advances substantially improve CalCOFI’s ability to share high-quality, well-structured biodiversity data with the broader scientific community.

17.4.5 Improved Public Access and Infrastructure

Finally, several changes improve how external users find and access CalCOFI tools:

The public website (CalCOFI.github.io) now highlights key applications, including the integrated app and a pollutants-focused app, making them easier to discover.
Server configuration has been updated so that Shiny apps are served from a new dedicated domain app.calcofi.io (and still shiny.calcofi.io), clarifying the entry point for interactive tools and simplifying operations.

17.4.6 Overall Impact

Together, these developments move CalCOFI toward a modern, integrated data platform:

Scientists and partners gain a more powerful, user‑friendly app and R toolkit for exploring CalCOFI data.
The underlying database and workflows are more robust, testable, and clearly documented.
CalCOFI’s biological data are better positioned for global visibility and reuse through standards‑compliant publication channels like OBIS.
Public‑facing web presence and infrastructure are cleaner and more aligned, making it easier for stakeholders to find and use CalCOFI resources.

17.5 2025-07-01

This report summarizes the key development activities, major accomplishments, and ongoing work for the first 6 monhts of 2025 across the CalCOFI GitHub repositories: api, apps, calcofi4db, calcofi4r, docs, server, workflows. The findings are based on issues and commits from January–July 2025.

17.5.1 API Enhancements

17.5.1.1 New Features & Data Integration

Expanded API Options
- Added ability to include bottle data and use relaxed criteria for net-to-cast matching (commit).
- Supported upcast/downcast data downloads (commit).
- Added Zooplankton biomass and improved ichthyodata output (commit 307cd6f).
Performance & Maintenance
- Implemented docker compose restart for Plumber API service (commit).
Ongoing Work
- Migration of database contouring functions to API/app level for improved caching and rendering efficiency.
- Development of a robust, user-friendly API for seamless DB integration (issue).

17.5.2 Apps Development

17.5.2.1 Visualization & User Interface

Continuous Improvements
- Multiple commits indicate ongoing enhancement, likely focused on UI, data visualization, and integration with the API (see recent commit log).
- Close coordination between API and Apps for improved workflows and data access.

17.5.3 calcofi4db: R Package & Data Management

17.5.3.1 R Package Initialization & Data Ingestion

New R Package: calcofi4db
- Initial commit and setup (commit), including functions for ingesting CSV datasets and metadata.
- Refined change detection logic for source CSV files, improving tracking of table/field changes (commit).
- Enhanced documentation and site via pkgdown.
- Improved function naming and structure for ingestion (commits, commit).

17.5.4 calcofi4r: Spatial & Ecological Data Tools

17.5.4.1 Data Layers, Analysis, and Bug Fixes

Spatial Management Layers
- Ongoing integration of BOEM Wind Planning Areas, Marine Protected Areas, and SCCWRP management regions (issue, issue).
Analysis Functions
- Improved packages for ecological and spatial analysis, including new dependencies (commit).
User Feedback
- Addressing user-reported bugs such as deprecated function calls (issue).

17.5.5 Documentation (docs)

17.5.5.1 Infrastructure & Environment

Documentation Site Updates
- Added documentation for new packages and ingestion workflows (commit).
- Improved environment handling for rendering with Quarto and Chromium (multiple commits Jan-Mar 2025).
- Updated diagrams and edge labels for database documentation.

17.5.6 Server

17.5.6.1 Backend Infrastructure

Backend Maintenance
- Numerous commits for improving server reliability, configuration, and deployment.
- Indicates active backend support for API and Apps.

17.5.7 Workflows

17.5.7.1 Data Pipeline, Integration, and Registration

Workflow Automation
- Multiple commits show ongoing development of data ingestion, harmonization, and visualization workflows (commit, commit).
ODIS Registration
- Registering datasets with ODIS (using JSON-LD) for broader interoperability (issue).
Integration with External Data
- Ongoing work to load and harmonize diverse ecological datasets (bottle data, larvae, zooplankton, etc.).
Spatial Data Management
- Continued development of AOI (areas of interest), spatial buffer creation, and integration of management regions.

17.5.8 Key Themes & Impact

17.5.8.1 Integration & Interoperability

Strong focus on connecting API, Apps, R packages, and backend infrastructure for seamless data access and visualization.
Enhanced interoperability through ODIS registration and harmonized workflows.

17.5.8.2 Data Accessibility & Usability

Improvements to API and Apps make ecological data more accessible to researchers and managers.
Expanded support for spatial management areas and ecological datasets.

17.5.8.3 Infrastructure & Sustainability

Investments in documentation, backend reliability, and workflow automation contribute to long-term sustainability and reproducibility.

17.5.9 For More Details

Some results may be incomplete due to API limits.
To view all commits/issues for 2025, visit each repository’s GitHub UI and filter by year.