Restore missing FK constraints on genes_genesymbol on prod DBs

🤖 Written by Claude

## Background

While diagnosing a `genes/0078_alter_genesymbolalias_unique_together_and_more` migration failure on dev/test DBs, we discovered that production is missing **all** foreign key constraints pointing at `genes_genesymbol`. Other tables are fully constrained — this gap is specific to `GeneSymbol`.

Audit query result on prod:

```
relname | outgoing_fks | incoming_fks
---------------------------+--------------+--------------
classification_classification | 10 | 6
genes_genesymbol | 0 | 0
genes_geneversion | 4 | 4
snpdb_allele | 2 | 11
snpdb_variant | 2 | 25
```

`pg_constraint` returns zero FK rows where `confrelid = 'genes_genesymbol'::regclass`. Django's model state still believes the FKs exist (every dependent app declares `ForeignKey(GeneSymbol)`), but Postgres has no constraints enforcing them.

This explains why `genes/0078` migrated cleanly on prod in 2024-08-17 (1.5 s) while failing on every dev DB and from-scratch test build today: with no FK constraints in the way, `DROP COLUMN old_symbol` succeeded.

## Likely cause

Most likely a historical operation altered `GeneSymbol.symbol` (its type changed from CITextField → TextField + case-insensitive collation, and there were earlier type changes too) in a way that required dropping incoming FK constraints, and the recreation step was skipped or silently failed. From that point onwards every subsequent migration ran fine on prod because nothing was there to block it.

## Why it probably hasn't bitten us

GeneSymbol rows are essentially immutable in normal operation — we don't delete gene symbols, and renames are rare. With no `ON DELETE` cascade behaviour to rely on, the missing constraints have been mostly invisible. Risks remaining:

- `gene_symbol_id` values can in principle drift to a value that doesn't exist in `genes_genesymbol` (writes from buggy code, ad-hoc data ops, partial imports). Without the FK we'd never know.
- Anyone reasoning about referential integrity from the schema would assume the FKs are enforced.
- A future migration that itself relies on FK existence (e.g., another PK swap on `GeneSymbol`) could behave differently on prod vs. dev again.

## Proposed work

1. **Audit orphans first.** For each of the ~21 dependent tables, count rows where `gene_symbol_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM genes_genesymbol WHERE symbol = gene_symbol_id)`. If any row counts are nonzero, decide on cleanup (likely insert the missing `GeneSymbol` row, since we don't delete them).

2. **Add a migration that idempotently recreates the FKs.** New `RunSQL` migration in `genes/` (after the current head) that, for each dependent table, runs an `ALTER TABLE … ADD CONSTRAINT … FOREIGN KEY (gene_symbol_id) REFERENCES genes_genesymbol(symbol) DEFERRABLE INITIALLY DEFERRED` guarded by a `DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = …) THEN … END IF; END $$` block. This is a no-op on dev/CI (constraints already exist) and additive on prod.

3. Run the audit query post-migration to confirm prod and dev now agree on FK count.

## Dependent tables

From `pg_constraint` on a healthy dev DB, the 21 tables that should have an FK to `genes_genesymbol(symbol)`:

```
analysis_allvariantsnode
analysis_karyomappinggene
annotation_dbnsfpgeneannotation
annotation_genesymbolcitation
annotation_genesymbolpubmedcount
annotation_humanproteinatlasannotation
classification_clinvarexport
classification_conditiontextmatch
genes_canonicaltranscript
genes_genecoverage
genes_genecoveragecanonicaltranscript
genes_genelistgenesymbol
genes_genesymbolalias
genes_genesymbolwiki
genes_geneversion
genes_gnomadgeneconstraint
genes_hgnc
genes_mane
genes_panelapppanellocalcachegenesymbol
genes_releasegenesymbol
pathtests_pathologytestgenemodificationrequest
seqauto_goldcoveragesummary
```

## Related

- `genes/0078_alter_genesymbolalias_unique_together_and_more` was rewritten to retarget FKs dynamically via `pg_constraint`, fixing the dev/CI/from-scratch breakage. That fix is a no-op on prod (no FKs to retarget) and does not address this issue.