Improving syncing of deletions

[tmorrell]

We should think about better ways of syncing deletions from the current queue.

[rsdoiel]

This is more than deletions, it's any request that changes the state of a record (in either the drafts or records_metadata table). Also I need to augment the fields harvested to support the reports requested in issue 105.

[rsdoiel]

I'm re-implementing how we harvest RDM request data. This will result is switching from the old rdm_review_queue.ds (dataset collection) to a new rdm_requests.ds (dataset collection). There will be new bash scripts implementing the harvest of the content via JSON-L files generated from the Postgres SQL queries run against production CaltechAUTHORS.

The new collection will hold an enhanced data shape to include the additional fields required for issue #105 to be implemented. The old rdm_review_queue.ds, related bash scripts and SQL in cold_api.yaml will be superceded in the next release of COLD.

[rsdoiel]

Root cause of some discovered bugs in harvesting: three separate mechanisms, not one

1. The review queue has been silently frozen since 13 April.

remote_harvest_rdm_review_queue.bash harvests the 918 submitted requests into
a JSON-L file and loads it with dataset load. One record in that file —
v62zd-ahy22, which carries 2,379 creators — serialises to 1.10 MB, and
dataset load's scanner buffer defaults to 1 MB. A too-long line does not skip
that record: the loader stops there. It sits at line 471 of 918, so the
remaining ~447 rows have never been written since it entered the queue.

Because that script upserts rather than wiping, the collection kept looking
full at 111,223 rows. The row timestamps give it away — 110,783 rows were
refreshed today, and 440 were last written on 2026-04-13. All 440 are in the
untouchable tail, no exceptions. v62zd-ahy22 itself has never loaded at all.

The realised harm is smaller than that sounds: 9 of the 440 differ in creators,
5 in custom fields, none in status or title, and roughly 15 were edited in RDM
after the freeze. Status transitions still got through, because the separate
submissions harvest covers status != submitted and its longest line is 963 KB,
under the limit. So the queue stopped refreshing for half its rows five months
ago and would have kept accumulating, but most of those records happened not to
change in the meantime. The structural failure is what matters here, not the
current damage.

2. A third of records were showing a superseded version.

A community-submission request points at one specific record version, and
publishing a new version does not create a second request. The harvest joined on
the version the request named, so it returned whichever version was originally
submitted. On CaltechAUTHORS that is 37,178 of 110,284 records — 34% — pinned to
a version that is no longer current, mostly an artefact of the EPrints migration
creating a second version.

A concrete case, and the one that matters for #105: azap5-pte18 carries all
four custom series fields. It is version 2 of its record, and the request names
version 1, so COLD only ever saw the version without them. What COLD held was
caltech:groups, caltech:other_num_id, caltech:other_num_name,
imprint:imprint; the current version adds caltech:series and
caltech:series_number. The record would still have appeared in the technical
reports report, since it carries other_num_* — but with series and
series_number blank, i.e. flagged as missing series metadata when it has it.

This also defeats any timestamp-based sync: a new version is a new row, so the
harvested row's updated never moves. No watermark can see it.

3. State transitions between full harvests — the original framing here, and
real, but the smallest of the three.

What's changing

The two RDM harvest scripts are replaced by a full/incremental pair writing to a
renamed collection, rdm_requests.ds:

• Metadata now comes from each record's newest version that has not been
  deleted, rather than from the version the request named.
• The full harvest upserts and then sweeps rows it did not touch, instead of
  wiping first, so a failed load can never leave the queue half empty. It also
  refuses to sweep unless it can account for every row it loaded.
• resource_type, parent_id, version identity and record state are harvested,
  which is what unblocks the reports in Tech reports report #105.
• The load runs with an 8 MB buffer.

A full harvest now loads all 111,202 rows, including the record that had been
blocking the queue. The count reconciles exactly against RDM: 111,231 matching
requests, less 29 whose record is wholly deleted.

Two things that will be visible to you

• Report outputs will shift. Moving to current versions changes creators
  on 28,223 records and custom_fields on 4,469, so the collaborator,
  affiliation and country-collaboration reports will produce different numbers.
  That is a correction — those rows were describing 2023 versions — but it will
  look like a change.
• The queue's ordering changes, and cancelled/declined requests will no
  longer appear in it.

Worth raising separately

dataset load reports a truncated load as a small error count regardless of how
many rows it skipped — one failure here said "1 load errors" for a 79,732 row
shortfall. Any dataset load can lose the tail of a JSON-L file and be told it
was one bad record. The CaltechTHESIS harvest pair has the same 1 MB default and
passes no -m.

[rsdoiel]

Fixes and refactoring will be deployed in the next release, v0.0.52.