Nullable fields in the database (catalog and API)

[obulat]

While working on #2205, I've compiled a list of media properties that are set nullable in the catalog database, while in reality we do not save NULL values in them. Moreover, we either do not want to ever have NULL in them, or actively enforce null checks in the code to prevent saving items that have NULL values.

Here's the list of these columns, from a comment in the linked PR:

• identifier
• ingestion_type
• provider (there is an index on it, in combination with `foreign_identifier)
• source
• foreign_identifier (there is an index on it, in combination with provider)
• foreign_landing_url
• license (no data in the linked issue, but there should be no row without a license)
• license_version

Setting the columns as non-nullable in the catalog will probably be an expensive operation. However, not setting the columns nullable causes some problems down the line. One such problems is the API documentation, where many of these properties are shown to return "string or null", while in reality they only return "string". We could manually set that, for example, source is a non-nullable property. But it would be much safer if the database column was actually non-nullable.

What is the best course of action around nullability?

• Leave databases as they are now, and simply document the non-nullable properties. The advantage here is that we don't have to do expensive operations with the databases, and the disadvantage is that we can never guarantee what we document: there might be a media added with a null in one of such fields.
• Make all columns non-nullable in both the catalog and the API databases. I'm not sure how feasible this update would be, especially for the catalog.
• Only set the columns as non-nullable in the API database. This should be easier as that database is recreated every time the data is refreshed. It also solves the problem for downstream consumers of the API (including our Frontend, which would be able to safely auto-generate TS types from the openAPI spec)

[AetherUnbound]

Thank you for identifying this @obulat! Unfortunately, I think that the third option you suggest could lead us to issues we've experienced previously where differences between the API and catalog databases are only surfaced several hours into a data refresh when data is copied or indices are applied for example. I think our best option would be to set these columns to not nullable everywhere it makes sense to. We can test how long these operations would occur on a copy of the catalog, similar to how we've done some other performance testing in the past. Then we can use that to judge how best to apply the migration, IMO.