Spike: Change `User` primary key from `username` to a surrogate key

Why are we doing this?

It's generally helpful to have a "surrogate" primary key (a meaningless integer, for example) rather than primary key composed of meaningful information about the entity (a "natural key"). Using a natural key makes it harder to change that information because any change also has to change tables with foreign keys to it, while maintaining integrity, which could involve creating and deleting several tables to manage the transition. Django's ORM primary_key fields are read-only. It's very hard to accurately foresee that particular bits of information will never need to change. For example, we don't allow users to change username, but when sanitizing a backup of the database of PII for local developer use we would like to change the username.

In the case of User.username this is also Personally Identifiable Information which means other tables that have a foreign key to it also include that PII, which is an IG issue.

It's the kind of technical debt that's difficult to resolve in the context of a ticket addressing a different issue, because working out how to do it well is a fairly substantial amount of effort, and it's a risky change.

Raised as a spike because we suspect this may be fairly painful and it's not known how much work it will ultimately be.

How will we know when it's done?

We have either changed the primary key or have a firmer plan and possible prototype for how we want to approach this. The outcome will either be further Issues, and/or PRs. For PRs around this to be complete:

• User will have an auto-incrementing id field or similar as primary_key. username will no longer be the primary_key and defined as unique.
• All foreign keys and references to username as the primary_key are updated to use the new surrogate key.
• Ideally, the operation will be reversible.

Other tables with natural keys will have been identified and we'll decide what to do with those also. For example, Peter has pointed out that organisation slug is used as a primary key in some models.

What are we doing?

Investigating and prototyping approaches to doing this. We can either try to use Django migrations (auto-generated and/or manual) or write raw SQL to perform the updates. In either case the Django models will need to change.

We should test any code extensively locally before deploying.

Slack thread initially about sanitization but with discussion of this issue.
Jon's PR for sanitzing data https://github.com/opensafely-core/opencodelists/pull/2329. Note that this PR's approach isn't blocked by this ticket. An approach using a Django management command suggested in the thread is blocked by this ticket.

Defining delivery tasks guidance

Start by inspecting the Django User model, its migrations, and all foreign keys or references that use username as the primary key. Compare a Django migration approach with the raw SQL option and prototype the safest reversible path locally. Done means either a firmer implementation plan and prototype, or PRs that introduce a surrogate key, update references, and preserve integrity.