`Codelist` can somehow exist without `Handle`s

[Jongmassey]

Why are we doing this?

Codelist id 10907 currently has no associated Handles.

Various parts of the codebase assume all Codelists have at least one Handle and fail when this is not the case. The only way they should be created (by convention) is in codelists/actions.py. This makes two separate queries to insert the codelist then the related handle, but all public functions that call this are wrapped in transaction.atomic. So they should all only create codelists with at least one handle. There does not seem to be any code that deletes handles.

Attempting to enforce a constraint at database level seems over-complicated for the benefit. We can't do between-table constraints with a CHECK so it would have to be a database trigger. Circular keys as an alternative seems messy. But this means we cannot rule out developer action as the cause, via management shell or directly in the database. Either creating a codelist directly or else deleting the last/only handle.

Codelist id 10907 currently has no associated Handles. Therefore it is not visible on the site and can be safely deleted once we investigate.

Slack thread.

Related issue where this happened before #2731

What are we doing?

• Investigate if the code can lead to this situation.
• PR for a data migration to fix the bad codelist.
  • Lucy had the idea of a data migration, rather than typing something in on the command line. That makes it auditable, reviewable, repeatable, testable, avoids the possibility of manual error, and makes it fix up development environment databases as well. And provides a model if we need to do it again.
  • The migration should be idempotent and have a noop reversal.
  • Should live in a separate data_migrations or maintenance app to clearly separate them and avoid cluttering the feature apps.
• Issue for more on the above maintenance commit idea.

---

Defining delivery tasks guidance

Original bug report

What did you do and what did you expect to happen?

Iterating Codelist.objects.all() for an analytical data export, expected all codelists, got self.model.DoesNotExist(codelists.models.Handle.DoesNotExist: Handle matching query does not exist.

Impact of the bug

Data in the database is not consistent with implied (if not necessarily explicit) model constraints. This may affect other processes or new developments.

[mikerkelly]

Investigation

I agree with the previous ticket #2731 investigation that there is nowhere obvious in the application code where we delete Handle instances. The only models with deletion in codelists/actions.py or builder/actions.py are Codelist (when discarding the last draft version), CodelistVersion (when discarding drafts), and Search (when deleting a search in the builder).

Possibly there is some edge case we are missing, or it is being done manually in the production developer console or directly via SQL in the database.

PR

Let's prevent deletion of Handle instances via ORM and make a migration to remove the existing Codelist with no Handle (generically).

#2919

Issue for more on the above maintenance commit idea.

I no longer think there is any immediate further work to do in this area. We can fill out the app and documentation as we come to use it.