This all looks great. Maybe we could add created / updated fields to track changes on schemas?

Nevermind, saw your comment in the other thread

Postgresql's support of uuid7 is only in extensions at the moment, so I'd say probably not unless we know that we can provide enough functionality via the app side only and it passed through on a generic postgres uuid field.

The idea is to use a generic postgres uuid field and the uuid7 python module for id generation. Possibly worth a separate discussion but main benefit I see is having a natural ordering of created ids (because uuid7 starts with a time stamp) and the main drawback is having all the id prefixes being the same (in some cases it's nice to only need to look at the first few hex digits when comparing uuid4 values)

This reminds me I didn't add any created or modified date fields to the model. Those would be good to have.

This is storing the fields as a json blob, decoding to a list of field blobs. Does this help the issue of "data dictionaries are stored as json text, not with an enforced data schema, potentially leading to errors"? Understanding that if we're migrating existing data dictionaries over, then we won't have any sort of schema on the existing fields.

The current data dictionaries are stored as json in a text field and these are stored as jsonb in a column. IIUC jsonb is more efficient for querying and storing large values but does not record white space between elements (not a big deal) or key order in objects (possibly a problem)

The current data dictionaries don't have a schema for the "info" object for historical reasons, but every other field has a schema enforced by the installed IDataDictionaryForm plugins. I'm planning on keeping this behavior: schemas are enforced in python by the IDataDictionaryForm plugins and validators applied. This gives us maximum flexibility to represent any sort of data dictionary, including for things like a glossary of terms (fields don't have ids because they aren't referencing columns in the data) or json schema (stored in plugin_data as a json schema blob instead of separated into the fields)

I'm not clear how individual fields here could be shared between tables if they're part of a JSON blob, or what it means to link to two data dictionaries for an individual resource.

A resource may have exactly one "owned" resource data dictionary, and may have one or more "shared" org or site-wide data dictionary linked from its data_dictionaries field. Plugins implementing IDataDictionaryForm would include template overrides that determine how the dictionaries are combined and displayed to end-users.

e.g. a table might have 20 columns but 15 of those columns are standardized for many of an organization's datasets. This dataset could have some custom field descriptions in its "owned" resource data dictionary that is merged with a linked shared org-wide data dictionary (for fields with the same ids) when it is displayed to users. It could also include a link to a shared site-wide glossary of terms that would appear below the merged data dictionary display.

Would i18n for this be stored in plugin_data?

Yes i18n versions of title and notes could be stored in plugin_data and i18n versions of field names and descriptions could be stored as separate fields in fields (like it can be implemented in the current data dictionaries).

Here I'm following the way the rest of ckan works with translated metdata although I did consider going straight to title_translated and notes_translated fields in the model.

schemas are enforced in python by the IDataDictionaryForm plugins and validators applied

I understand the need for flexibility and that this might not be possible, but I think it would be nice to have some minimal consistency across fields schemas, so they can be used across different plugins. Say for instance that I need to describe the data fields in an ML Croissant RecordSet, not only for DataStore tables, but for GeoJSON, shapefiles, etc. It would be nice to know that I just need to check for a type key in the fields mapping (as opposed to field_type, ftype, datatype etc). Same for id to know the field name, etc.
I guess the alternative is to have IDataDictionaryForm provide a way of advertising its own fields schema and integrators need to harmonize it themselves

Having some standard for the stored type values in addition to the id value behavior makes sense to me.

1. We can make this part of the IDataDictionaryForm documentation
2. When we're implementing json schema or data package table schema or csvw schemas (schemas defined completely in plugin_data) as data dictionaries we can map their types to one of the standard field type values and store them in the fields jsonb along with the ids for db queries.

+1 on using JSON Schema as it widely supported, has a lot of available tooling, and can also be used for validation.

You may also want to take into account DCAT3, in particular - dcat:describedBy.

DOI-DO/dcat-us#138

IMHO - a machine-readable, standards based data dictionary is the best kind of metadata that can potentially describe not just columns' data type and description - with an "extended" dictionary, we can potentially describe what's INSIDE the dataset if summary statistics and frequency tables are included in the data dictionary, perhaps, as additional JSON properties in the JSON schema.

With summary statistics, one can even have a global "record-level" search, using a two-pass approach - the first pass will just search the data dictionary's summary statistics (as we can store enumerations, ranges, etc.) and present that to the user as "top-level" search results. Once the user clicks on a "top-level" search result, a "true" row-level datastore_search_sql search is executed on the selected dataset.

This effectively gives CKAN a very efficient, global record-level search and offers the possibility of just using PostgreSQL for search (using JSONB search for the first pass, and plain-old SQL for the second pass - which is very efficient as the summary statistics allow us to make intelligent choices for what to index)

See https://github.com/dathere/qsv/blob/master/scripts/NYC_311_SR_2010-2020-sample-1M.csv.schema.json which was created with the qsv schema command from a million-row, 41-column sample of NYC's 311 data in ~1 second (when an index is available. 8 seconds without an index).

sounds like it's worth adding a short timeout on that query so the page doesn't time out.