rfmo/rfb rebuild: CCAMLR missing from hex entirely; _cng_fid absent from flat + 22 partitions and colliding where present

[cboettig]

Rewritten 2026-08-08 after verifying every claim against the live data. The original
issue reported only the _cng_fid partition hole and attributed it to a mixed-vintage build.
The row counts were exact, but the cause was wrong and two larger defects in the same asset
were missed — including an entire RFMO absent from the hex. Scope and acceptance criteria
are now recorded below.

public-high-seas/rfmo/rfb has three independent defects. All are verified against the
published data; none are hypothetical. The dataset needs a full rebuild from convert
onward, not a hex patch.

Defect 1 — CCAMLR is entirely missing from the hex (most severe)

The flat GeoParquet holds 105 features / 54 distinct RFB. The hex holds 104 / 53.

SELECT (SELECT COUNT(DISTINCT RFB) FROM read_parquet('s3://public-high-seas/rfmo/rfb.parquet')) AS rfb_flat,
       (SELECT COUNT(DISTINCT RFB) FROM read_parquet('s3://public-high-seas/rfmo/rfb/hex/h0=*/data_00.parquet', union_by_name=true)) AS rfb_hex,
       (SELECT COUNT(*)            FROM read_parquet('s3://public-high-seas/rfmo/rfb/hex/h0=*/data_00.parquet', union_by_name=true) WHERE RFB='CCAMLR') AS ccamlr_rows;
-- 54 | 53 | 0

The absent feature is OGC_FID = 12, ID = rfb_comp:ccamlr, RFB = CCAMLR — a single valid
polygon of 11,260 deg², the largest management area in the file. Consequence: any "which
RFMO governs this cell" query returns empty for the entire Southern Ocean, silently.

This is the same feature #325 reported dropped. PR #365 fixed the manifest
(backoffLimit: 0 → backoffLimitPerIndex: 3), but the hex currently on S3 was built
2026-06-07/08 and still has the hole — the manifest fix was never reflected in published data.
#325 should be treated as not fixed.

Defect 2 — _cng_fid is absent from the flat asset and unusable in the hex

2a. The flat GeoParquet has no _cng_fid at all (0%, not 19%)

s3://public-high-seas/rfmo/rfb.parquet was written 2026-03-31, predating _cng_fid
synthesis. Its columns are:

OGC_FID, ANCFEATURE, geom, ID, AREATYPE, FILL, STROKE, SHAPE_AREA, OBJECTID, RFB, DEFRULE, DISPORDER, SHAPE_LENG

The STAC rfb-parquet asset declares _cng_fid anyway. So the #369 violation covers the
whole collection, and a re-hex alone cannot fix it — the fix must start at re-convert.

2b. 22 of 121 hex partitions lack the column

WITH allf AS (SELECT DISTINCT file_name FROM parquet_schema('s3://public-high-seas/rfmo/rfb/hex/h0=*/data_00.parquet')),
     hasf AS (SELECT DISTINCT file_name FROM parquet_schema('s3://public-high-seas/rfmo/rfb/hex/h0=*/data_00.parquet') WHERE name = '_cng_fid')
SELECT (SELECT COUNT(*) FROM allf) AS total_files, (SELECT COUNT(*) FROM hasf) AS with_cng_fid;
-- 121 | 99

SELECT COUNT(*) AS rows, SUM(CASE WHEN _cng_fid IS NULL THEN 1 ELSE 0 END) AS rows_missing
FROM read_parquet('s3://public-high-seas/rfmo/rfb/hex/h0=*/data_00.parquet', union_by_name=true);
-- 1,568,880,000 | 303,196,000

303 M of 1.57 B rows (19.3%) have no _cng_fid. A plain
SELECT _cng_fid FROM read_parquet(<glob>) errors outright (DuckDB binder error) unless the
caller passes union_by_name=true; with it, those rows silently read NULL.

2c. Where it is present, it is not a valid feature key

Because the flat has none, the hex's values were synthesized during hexing and map back to
nothing. Across the whole hex:

SELECT COUNT(DISTINCT _cng_fid)            AS distinct_fid,    -- 97
       COUNT(DISTINCT OGC_FID)             AS distinct_ogc,    -- 104
       COUNT(DISTINCT (OGC_FID,_cng_fid))  AS distinct_pairs   -- 156
FROM read_parquet('s3://public-high-seas/rfmo/rfb/hex/h0=*/data_00.parquet', union_by_name=true);

104 features, 156 distinct pairs, 97 distinct non-null ids, one _cng_fid value shared by
two different features. COUNT(DISTINCT _cng_fid) returns 97 against 105 real features —
collisions on top of the NULL hole. Until the rebuild, consumers must use OGC_FID, not
_cng_fid, and must pass union_by_name=true.

This is also why the #509 sweep could not check this asset for duplicate rows. Using OGC_FID
instead, there are 1,243 duplicate (cell, OGC_FID) rows — small, and expected to clear on
rebuild via the post-2026-07-12 polyfill dedup.

Defect 3 — the STAC misdescribes both rfb assets

• rfb-parquet declares 8 columns including _cng_fid, which does not exist, and omits 6
  that do (AREATYPE, DEFRULE, DISPORDER, FILL, OBJECTID, STROKE).
• rfb-hex declares 6 columns (_cng_fid, h8, h7, h6, h0, RFB) but the files carry 16 —
  including OGC_FID, the only usable fallback key, which a consumer therefore cannot discover
  from the catalog.
• rfb-pmtiles declares 12 columns without _cng_fid (consistent with the flat, and will need
  updating once the flat carries it).

Cause

Not a mixed-vintage merge — that hypothesis is disproved. The 22 bad partitions are
interleaved in write time with the 99 good ones (mtimes span 2026-06-07 22:14 → 2026-06-08
14:08, the same window). There were two write waves (18 files rewritten 06-08 13:56–14:11) but
only 3 of those 18 are among the 22.

The likeliest culprit is
rfmo-rfb-add-parents.yaml,
a second full-rewrite pass that does SELECT * over the partition glob without
union_by_name and rewrites every file. Folding the parents into the hex job removes that
pass and the suspect entirely.

Scope of the fix

• Source: https://s3-west.nrp-nautilus.io/public-high-seas/raw/rfmo-rfb.zip (unchanged).
• Extent: global, full upstream coverage — no clip. All 105 features / 54 RFBs.
• Bucket / dataset: public-high-seas, --dataset rfmo/rfb (paths and asset keys unchanged).
• H3: native resolution 8, parent resolutions "7,6,0" — matching what rfb-hex
  already declares, so downstream joins at h8/h6 are unaffected.
• Reducer: n/a (vector).

Steps, in order

1. Re-convert rfb.parquet — regenerates _cng_fid on the flat, which the hex then
   inherits as a real key. Use --row-group-size 2000 (geometry-heavy; the current build used
   100000, the stoi risk band).
2. Re-pmtiles so tiles carry _cng_fid.
3. Re-hex all 105 features with --parent-resolutions "7,6,0", writing to a staging
   prefix. Do not re-run add-parents as a separate pass.
4. Purge the live hex/ prefix and verify it is empty before syncing staging in (stale
   partitions from the old build would otherwise survive; note the old build's files are named
   data_00.parquet while a fresh hex writes data_0.parquet, and the STAC glob is
   hex/h0=*/*.parquet — a mixed prefix would double-count every row).
5. Rewrite the STAC table:columns for all three rfb assets to match the actual schemas, and
   re-run scripts/verify-stac.py --bucket public-high-seas --dataset rfmo.

Acceptance criteria

All must hold against the published data before this closes:

• COUNT(DISTINCT OGC_FID) on the hex == 105; COUNT(DISTINCT RFB) == 54.
• SELECT COUNT(*) … WHERE RFB='CCAMLR' > 0.
• _cng_fid present in every partition under the hex glob (parquet_schema check — expect
  ≥121, since CCAMLR's cells may add partitions), zero NULL rows, and
  COUNT(DISTINCT _cng_fid) == 105.
• Flat rfb.parquet carries _cng_fid, one per feature.
• No duplicate (h8, _cng_fid) rows.
• verify-stac.py --bucket public-high-seas --dataset rfmo exits 0 with no values-extra
  finding on rfb-hex.

Cost estimate: ~15 GB / 1.57 B hex rows, 105 pods × 32Gi, ~4 h wall for the hex step based on
the prior run.

Detection gaps this exposes — the gate reported the worst bug and we dismissed it

scripts/verify-stac.py --bucket public-high-seas --dataset rfmo exits 0 today (0 hard, 13
advisory) against all of the above. Two of those advisories are the bugs:

[values-extra] asset 'rfb-hex' column 'RFB': declared values ['CCAMLR'] never appear
  in the data (stale or superset — acceptable if intentional).
[hex-row-uniqueness-check-failed] asset 'rfb-hex': could not check (list index out of range).

The first is Defect 1, worded so as to invite ignoring it. The second is the #509 failure that
surfaced this issue.

Defects 2b and 3 are already tracked by #534 (declared-vs-actual schema, per partition file,
including the heterogeneous case) — implement the detector there, not here. #534 already names
this dataset as its regression fixture and expects it to report 22/121.

Defect 1 needed a detector that did not exist — now tracked in #535. A missing feature is
invisible to a schema check: every partition here has the right columns, the data is simply not
there. The gate is a flat-vs-hex feature-count comparison, plus a catalog-wide sweep for other
collections whose hex holds fewer features than their flat (riding #509's pre-gate audit pass).

Related: #535 (detector + sweep), #534 (schema check), #509 (pre-gate audit),
#325 (closed 2026-07-11 with its CCAMLR item unfixed — see item 2 of its body), #369, #494.

[cboettig]

Detection half is now tracked separately in #534 (check the STAC-declared schema against the actual parquet schema, per file rather than against the unioned glob). Implement it there, not here, so we do not end up with two overlapping schema checks.

This issue stays scoped to the remediation: rfmo/rfb/hex needs a re-hex so all 121 partitions carry _cng_fid. #534 will make the hole visible; it will not fix it.