12  CTD QA/QC, start to release

For the CTD team. The casts you quality-control can live in two places on purpose: a working store you can edit — a multi-user PostgreSQL database on the CalCOFI server where every cast file is loaded verbatim and never changed, and your flags and proposed fixes sit in a ledger beside it — and the frozen release everyone else reads, which the ledger feeds. This page is the path from one to the other: what the store holds, how a flag travels, where to start, and what reaches the release. Accounts, keys, the tunnel and the clients are in Server access.

Available, not yet adopted. The store is built, loaded and working — but it is not yet the team’s QA/QC practice, so today CalCOFI’s CTD flags reach the release the way every other dataset’s do: inside the provider’s own files. That is why the system figure in Architecture does not show it, and why this chapter has its own (Figure 12.1). Nothing below is a plan; it all runs today.

12.1 Where the store sits in the system

Figure 12.1: The whole system with the CTD working store in it: the team proposes and reviews flags in ctd.flag, the server writes the accepted rows to flag_accepted.parquet every night, and the next CTD ingest applies them as measurement_qual. The pipeline never reads the live database — only that file — and pg_duckdb lets the store read the release back. Compare the overview figure in Architecture, which is the same system without this box.

Figure 12.1 is the ingest → integrate → publish path of the overview figure with one box added on the left. What is built and running, since 2026-08-19: PostgreSQL 18 with PostGIS 3.6 and pg_duckdb on the CalCOFI server; 409 cast files and 10,812,360 scans loaded verbatim in all 82 source columns; the ctd.flag ledger with its audit trail and the v_scan_qc / v_scan_clean views over it; 20,521 best-stage casts, 1993–2026; personal accounts, a tunnel and clients in R and Python (Server access); and the nightly job that writes every accepted flag to gs://calcofi-db/qc/ctd/flag_accepted.parquet.

The pipeline never reads the live database. The bridge is that one parquet file, so an ingest runs on a laptop, on the server or in CI without a tunnel, a credential or a database being up — and the flags it applies are a dated, immutable snapshot rather than whatever the ledger happened to say mid-run.

What adopting it would change: a flag accepted in the ledger reaches the next release on its own, with nobody touching the release, re-exporting a cast file or asking the data team to re-ingest anything — and the reasoning behind each flag stays in the database, attributable and reversible. What happens instead today: flags arrive with the provider’s files, so a correction waits for the next archive delivery, and the judgement behind it lives wherever the person who made it kept it. Both paths end in the same column, measurement_qual, uninterpreted.

12.2 The store, in one figure

Figure 12.2: The CTD archive is loaded verbatim and never edited; a flag is a row beside it; the accepted flags are snapshotted nightly and applied by the next CTD ingest, so the release carries the team’s judgement without anyone touching the release.

Figure 12.2 is the store itself, close up, and Table 12.1 is what it holds. Database calcofi, three kinds of schema:

Table 12.1: The schemas of database calcofi and who writes to each; Server access has the longer form.

schema	holds	who writes
ctd	the archive verbatim — ctd.file (one row per cast file, with its archive and path) and ctd.scan (every scan, all 82 source columns, -99 sentinels included); ctd.scan_issue for the handful of cells that could not be typed; ctd.scan_column, the data dictionary; the ledger — ctd.flag (IODE codes from ctd.qual_code, one row per scan × variable, with a status) and ctd.flag_audit (every change to it); ctd.cast, one row per cast, materialized; and the views ctd.v_scan_qc (the originals with a <var>_qc and <var>_fix column per variable from the accepted flags) and ctd.v_scan_clean (the fixes applied, accepted-bad values nulled)	originals: the loader only; flags: anyone proposes, curators accept or reject
release	read-only views over the public release through pg_duckdb — release.cruise, release.ship, release.dataset — so a query can join the archive to the release without leaving psql	nobody; regenerated per release
work	shared scratch, readable and writable by the whole team	everyone
<you>	your own schema, first on your search_path; colleagues can read it	you

The rules of the road: originals never change. A problem is a row in ctd.flag — which scan, which variable, what code, an optional proposed value, why; a fix is an accepted flag; every derived product is computed from originals plus accepted flags, so it can always be regenerated.

12.3 A flag, end to end

1. Propose. From any client — psql, pgAdmin, R, Python — insert a row naming the scan, the variable, the IODE code (4 = bad; 5 = changed, with proposed_value) and the reason. The Python package wraps it: cc_propose_flags() takes a data frame of scan ids and codes, and cc_withdraw_flags() takes one back.
2. Review. A curator sets status to accepted or rejected with a note; reviewed_by and reviewed_at fill themselves, and ctd.flag_audit keeps the history.
3. See it. ctd.v_scan_qc shows the code beside the value; ctd.v_scan_clean applies it. Nothing else in the store moves.
4. It reaches the release. This is the step that waits on adoption, and it needs nothing built. Every night the server writes the accepted rows to gs://calcofi-db/qc/ctd/flag_accepted.parquet. The CTD ingest notebook reads that file — never the live database, so the pipeline can run anywhere — and applies each accepted flag as the scan’s measurement_qual; the release notebook then compares the snapshot’s count with what the ingest applied and warns when flags accepted since the last ingest are still pending. The next release carries them; the qual_ok column and the packages’ quality predicate (Access the data) honour them everywhere.

-- propose: a temperature spike on one scan
INSERT INTO ctd.flag (scan_id, variable, qual_code, reason)
SELECT scan_id, 'temp1', 4, 'spike vs neighbours'
FROM ctd.v_scan_best WHERE study = '2304SH' AND cast_id = '2304_001d' AND depth = 57;

-- review (curators)
UPDATE ctd.flag SET status = 'accepted', review_note = 'agree' WHERE flag_id = 123;

-- see it
SELECT depth, temp1, temp1_qc FROM ctd.v_scan_qc
WHERE study = '2304SH' AND cast_id = '2304_001d' AND temp1_qc IS NOT NULL;

12.4 Where to start

Two starting points, one in each language, both against the same store:

• Python: the QA/QC walkthrough — calcofi.io/calcofi4py/articles/ctd-qaqc, a notebook that connects through your tunnel (cc_pg_connect(tunnel=True)), lists a cruise’s casts (cc_ctd_casts()), pulls their scans (cc_ctd_scans(), cc_bin_1m() to bin them), runs the screening checks (cc_qc_range(), cc_qc_spike(), cc_qc_sensor_pair()), plots a profile or a section (cc_profile_plot(), cc_profile_explorer(), cc_section_plot(), cc_station_map()) and proposes flags from what it finds. Copy it, change the cruise, keep going.
• R: the cruise-variable cleaning notebook — calcofi.io/workflows/clean_ctd_cruise-var in CalCOFI/workflows, the same screening for one cruise and one variable from R (calcofi4r::cc_pg_connect()), with the QA/QC protocol it follows written up beside it (ctd-cast_qa-qc-protocol).

The schema and the flag vocabulary are new (August 2026). Propose a change to them in work; it is folded into ctd by the data team.

12.5 What reaches the release, and what does not

• Two preliminary tiers ship as one archive. A cruise’s _CTDPrelim.zip can hold both the fully processed preliminary casts and a sensor-only tier; the sensor-only tier reaches the release with no salinity and no oxygen, because they were never computed. The ingest classifies each file’s tier and records it; the final QC’d archive supersedes both.
• The release carries two CTD products. obs_env holds the thinned headline series per cast — a 10 m grid plus the profile’s inflection points plus every bottle depth, one direction — which is what the Explorer’s sections and the anomaly climatology read. obs_ctd_full holds every scan (~271 M rows), supplemental: catalogued, downloadable, off by default.
• measurement_qual is your vocabulary, uninterpreted — 1/2 use the primary/secondary sensor, 8 questionable, 9 bad or missing — and qual_ok is the release’s verdict from it. Your accepted flags become that column at the next ingest; nothing is re-interpreted on the way.
• Bottle data is its own dataset (calcofi_bottle), keyed to the same cruises and stations; the plan to migrate the bottle database off Access into this same store is Task 1 of the data management plan, and the profile database you are building on is Task 2.

12.6 Reading the release from the store

The release schema and pg_duckdb let psql read the public release directly, so a check can join your casts to what shipped:

SELECT * FROM release.cruise WHERE year = 2023;
-- any release table: resolve its object through the catalog (Access the data), then
SELECT r['sample_key']::text, r['datetime']::timestamp
FROM read_parquet('https://storage.googleapis.com/calcofi-db/ducklake/tables/sample/{content_hash}/sample.parquet') r
WHERE r['dataset_key']::text = 'calcofi_ctd-cast' LIMIT 10;

And the other way round, from R or Python: cc_get_db() for the release plus cc_pg_attach() for the store, in one DuckDB connection (Server access).