published_record should identify the catalog(s) to which a record was published

[marksparkza]

The published_record table preserves ids and DOIs of all records that have ever been published, to prevent such records from being deleted or having their DOIs changed or removed.

A secondary use of this table is to enable retracted record stubs (containing only record id and published: false) to be included in the output of the catalog record list API endpoint. This works correctly for catalogs where the retracted record stubs would be expected. However, such stubs also end up being included in record listings for catalogs to which they have never been published. For example, a non-MIMS record that is retracted would appear as such a stub for both the SAEON and the MIMS catalogs.

Although this is not a major issue (an extraneous stub can and should simply be ignored by a MIMS catalog client, for example), the solution will in fact provide an additional benefit in terms of data integrity. In the same way that the foreign key constraints on published_record prevent deletion of ever-published records, a foreign key constraint on a published_record_catalog child table can prevent deletion of any catalog instance which has ever contained any published records.