Create an update-in-place process for opensearch sync

[tdlowden]

[NOTE: For individual stories, use the user story template! Only use this template for larger things that will likely break down into a series of smaller stories over time.]

Feature/what we're after

Clearing opensearch and then re-filling isn't a good long term solution, so we need a way to sync harvester changes to opensearch in an "upsert and delete" type of way.

Anticipated/hypothesized benefits

• opensearch is never cleared (unless manual sync is necessary).
• opensearch has most recent harvested data (no daily delay)

Measurements/metrics

• When a harvest is completed, then the changes (insertions, updates, deletions) are synced to open search and is able to be confirmed via search or API.

References/background

• Related to recent needs to re-sync and having the counts go to 0 (and search being limited)

Sketch

This could be done on a dataset individual level or in a batch at the end of the harvest job, whichever is easier. Batch would probably be faster, but individual may be easier to code/implement with the different cases of addition, update, and delete.

[rshewitt]

we can also decouple sync as a separate process (similar to how it's done currently). we can utilize the ckan_id field (changing its name to opensearch_id) to determine whether a harvest record is synced with opensearch. assuming opensearch has been bulk loaded and existing harvest records have been synced each subsequent run would look for harvest records without an opensearch_id, confirm the associated dataset record is the correct state, then sync with open search.

[rshewitt]

putting this back into catalog ui project. fuhu created a resync which should help avoid indexing issues with opensearch. we'll bring this back into progress if issues arise.

[rshewitt]

@FuhuXia sync compare doesn't handle updates only creates and deletes which means db and opensearch aren't synced. for example, take the most recent DOI ONRR DCAT-US job with 14 updates which occurred around 3 am today. opensearch sync occurred at 5 am. the record in dataset doesn't match what's in opensearch (should be the first record from that opensearch return). the dataset title is Monthly production of U.S. natural resources revenue. the difference is keywords. it appears the only changes are capitalizations (maybe not the best example) but those are changes nonetheless and should be reflected in opensearch.

example 1

opensearch keywords

[
    "Environment",
    "Federal Lands",
    "Outer Continental Shelf",
    "economy",
    "geoscientificInformation",
    "location",
    "offshore waters"
]

dataset keywords

[
    "Federal Lands",
    "Geoscientificinformation",
    "economy",
    "environment",
    "location",
    "offshore waters",
    "outer continental shelf"
  ]

based on the compare command we use as our action only missing and extra are handled (i.e. create and delete)

example 2

latest montgomery county job occurred on 2025-12-27 01:02 PM (GMT-7) which would contain fuhus update from Fri Dec 26 11:12:26 2025 -0500. opensearch sync occurred the following day at 5 am which means the updates from the job should be present in opensearch which isn't the case. for example, Police Marijuana Smoking Violations in harvest_record, which is present as the harvest record for the corresponding dataset (see terminal output below) does match what's in opensearch. specifically, the modified date is wrong. catalog says 2025-12-25 but opensearch says 2025-12-14.

cgawsbrokerprodiwd7z77ecc9uqk6=> select harvest_record_id from dataset where slug = 'police-marijuana-smoking-violations';
          harvest_record_id           
--------------------------------------
 55698097-4fc5-4b80-90eb-dd90e0978833

[FuhuXia]

@rshewitt let me confirm your findings and create a ticket if needed.

[FuhuXia]

The ticket has been created.

• support document update on opensearch sync #5617