Define the Dataset model in the harvester db schema

[neilmb]

Feature/what we're after

Define the database model for a single "Dataset" which is the unit of information for the new catalog.

Anticipated/hypothesized benefits

• Ready to sync harvest records into Datasets.
• Ready to display dataset information in a web interface.

Measurements/metrics

• Model is in datagov-harvester and deployed to development.

References/background

• @jbrown-xentity has the first cut at what fields we should include in https://docs.google.com/document/d/1k5vSyMvGHqsmXwkWipMogybLgTIjKOenZcSpXQKXU20/edit?tab=t.0#heading=h.er17fd9hq74c
• We're going to lean on JSON columns in the database using the DCAT-US representation

[rshewitt]

we don't need this if we use a materialized view. we would add the new columns to the harvest record table. the point of the materialized view is to

• avoid duplicate tables (HarvestRecord and Dataset are basically the same thing)
• have a table containing just the latest set of records for all harvest sources. HarvestRecord is the source of truth. This avoids duplicating writes to 2 separate tables.
• have these records written to disk (this is the "material" part of the view)

[neilmb]

HarvestRecord and Dataset are not the same thing. HarvestRecord is some data we record every time that we harvest. It's a breadcrumb about a single thing that our harvester did. Every time we harvest we make a new one.

Dataset is a single persistent object whose characteristics can change through the harvesting process. We have to decide how we define a dataset's "identity" (organization and identifier?) but it should be a single thing no matter how many times we harvest.

Whether we can make the Dataset table from HarvestRecord with a particularly fancy SQL query (... SORT BY date_created DESC LIMIT 1 ...) is a good question, but they aren't the same thing.

[rshewitt]

the latest instance of a record in HarvestRecord (e.g. create, update, update <- latest instance) would contain the exact same information as what would be expected in Dataset other than popularity (maybe we can aggregate the versions to maintain the value). Dataset is a duplicate of HarvestRecord it just doesn't store historic versions of the record because it updates in-place. This seems like a good opportunity for the database to do the work for us instead of us synchronizing another table.

my proposal is option 1 in #5491 . no need for further explanation on my end.

[jbrown-xentity]

@neilmb @rshewitt these are all semantics.
There is extra information, such as popularity and dataset_vector (and there may be more) that need to be generated, either via SQL joins or SQL functions.
A materialized view tries to scratch both problems; namely we're combining information from various tables into a single view (so we don't need a complete other table) while simplifying the data and making the query faster (it actually stores the data from the query, making it so that we aren't filtering from the much larger harvest_record table).
This materialized view needs a name, and calling it dataset or catalog makes sense to me, as that is what it represents. You can query from this view like it's a new table.
This materialized view won't refresh on every load, but we'll control when it's refreshed. I suggest at the end of every harvest job; that way it's like a batch load and everything is synced fairly regularly (it will refresh the whole view, not just that harvest job's data; so anything that broke or was missed before will be resolved).
So the catalog or dataset materialized view (which will function as it's own table) will be defined as a SQL query, something like the following, but it will be referenced by the new name. More information on postgreSQL docs.

SELECT id, ckan_name, raw_source::jsonb, ...
FROM harvest_record
WHERE [some logic to get only the most recent version of the records]