10  Status

10.1 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.

10.1.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.

10.1.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.

10.1.3 3. Share — Publishing to ERDDAP & OBIS, plus Discovery Tools

  • ERDDAP. A CalCOFI-branded ERDDAP server was configured and deployed, serving multiple EDDTableFromParquetFiles datasets (bottle, phytoplankton, and others). A dedicated benchmark study compared Parquet, NetCDF (EDDTableFromNcCFFiles), and DuckDB serving backends to choose the best layout — prompted by the wide CTD table OOM-ing ERDDAP’s heap, which remains the one open serving issue (see crosswalk).

  • OBIS. The fish eggs & larvae → OBIS workflow (Darwin Core + EML, joining biological observations to CTD environment by cruise) was refined to use the stable UUID keys and ichthyo-only scope.

  • Discovery & documentation. Two new browser-based, serverless tools were built and linked throughout the docs: a Schema explorer (schema/ — diagram, tables, columns, datasets, measurements, with a cross-tab dataset filter and clickable ERD) and a Query playground (query/ — a Jekyll + DuckDB-WASM SQL sandbox). calcofi4r was rewired to read release metadata.json, emits llms.txt, and documents bio↔︎environment matching helpers; the docs site added Data Access and Matching pages.

  • Webinar series — pending. The final Share deliverable (a recorded webinar series) has not yet been produced; a progress deck (Dec 2025 – Jun 2026) was assembled in its place.

10.1.4 Deliverables Crosswalk

Against the SOW (** = must-complete by end of contract):

Ingest — all must-complete datasets done:

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:

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)

10.1.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.

10.2 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.

10.2.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.

10.2.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.

10.2.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.

10.2.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.

10.2.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.

10.2.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.

10.3 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.

10.3.1 API Enhancements

10.3.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).
  • 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).

10.3.2 Apps Development

10.3.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.

10.3.3 calcofi4db: R Package & Data Management

10.3.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).

10.3.4 calcofi4r: Spatial & Ecological Data Tools

10.3.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).

10.3.5 Documentation (docs)

10.3.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.

10.3.6 Server

10.3.6.1 Backend Infrastructure

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

10.3.7 Workflows

10.3.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.

10.3.8 Key Themes & Impact

10.3.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.

10.3.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.

10.3.8.3 Infrastructure & Sustainability

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

10.3.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.