Partitions - Migrate from Table inheritiance to declarative partitioning

**This is a dangerous change and should be done in a branch until we are sure it's ok**

The old way is full of hacks, we should aim towards moving to new style:

**OLD: Table inheritance**

`ClinVarVersion`, `GeneAnnotationVersion`, `HumanProteinAtlasAnnotationVersion`, `VariantAnnotationVersion`, plus the direct `RelatedModelsPartitionModel` users: `CohortGenotypeCollection`, `VariantCollection`, `GeneCoverageCollection`, `VariantZygosityCountCollection`.

**New: Declarative partitioning**

`DBNSFPGeneAnnotationVersion`

------

## Agreed approach: in-place per-partition migration with a maintenance gate

In-place per-partition migration works because **child tables keep their names throughout** and the existing `sql_partition_transformer` (`library/django_utils/django_partition.py:79`) already routes queries directly to children regardless of which parent they're attached to. Per-partition PK rebuilds take ACCESS EXCLUSIVE only on the child being migrated, so other partitions stay queryable.

Earlier dump-and-restore-latest plan was solving a *separate* problem (freeing disk by purging old VAVs). That's now delegated to #1537 (generic pre-drop archival pipeline) and is independent of this migration.

## Deploy-decoupled: code ships before any server migrates

A code deploy must **not** force the schema migration. Each deployed instance (Shariant, SA Pathology, variantgrid.com, dev/test) migrates at a time of its own choosing. To achieve this:

### Runtime-detected partition-creation strategy

The model code stays inheriting from `RelatedModelsPartitionModel` / `SubVersionPartition`, but `create_partition()` chooses behaviour at runtime based on the current schema state:

```python
class SubVersionPartition(RelatedModelsPartitionModel):
def create_partition(self):
if self._declarative_parent_exists():
self._create_declarative_partition() # add_list_partition against new parent
else:
super().create_partition() # legacy CREATE TABLE ... INHERITS

def _declarative_parent_exists(self) -> bool:
# cached process-local check: pg_class entry for new parent with relkind='p',
# OR a row in a small PartitionMigrationState table
...
```

This means the same code binary works against:

- **Pre-migration**: old inheritance parent exists; new parent doesn't. Code uses old path. Server keeps running normally on the old schema.
- **Mid-migration** (per-partition work in progress): both parents exist. Code uses the new path for any new partitions created during the window. Existing children are being moved one at a time by an out-of-band management command.
- **Post-migration**: only the (renamed-back) declarative parent exists. Code uses the new path.

The `sql_partition_transformer` stays in place across all three states — its parent→child name rewrite is harmless when the parent is declarative and the child is reachable by name.

### Schema migration is a management command, not a Django migration

Putting the schema work into a `0NNN_*.py` Django migration would force every server to migrate at deploy time. The actual schema work runs as:

```
manage.py migrate_partitions
```

Django migrations in this work are limited to **additive, backwards-compatible** changes:
- New `PartitionArchive` model (#1537).
- `DataPurgeMixin` columns (#1536).
- A small `PartitionMigrationState` table recording per-model migration status (`migrated: bool`, `migrated_date`, `migrated_by`). Surfaces in admin and used by the runtime-detection check.

### Cleanup release (much later, deploy-coupled in the trivial sense)

Once every deployed instance is known to be migrated (visible from the `PartitionMigrationState` rows or operational confirmation), a follow-up release deletes the inheritance fallback in `create_partition`, removes `sql_partition_transformer`, and deletes `RelatedModelsPartitionModel` / `SubVersionPartition` themselves. That release does require all servers to be migrated first — but that's the trivial form of coupling, not the "deploy forces a multi-hour outage" form.

## Schema during migration: dual parent, child-by-child move

```
snpdb_X (old inheritance parent)
├── snpdb_X_collection_1
├── snpdb_X_collection_2
└── ...

snpdb_X_new (new declarative parent, created alongside)
```

Per child:

```sql
-- a) detach (instant)
ALTER TABLE snpdb_X_collection_42 NO INHERIT snpdb_X;

-- b) rebuild PK as composite (long-pole, ACCESS EXCLUSIVE on this child only)
ALTER TABLE snpdb_X_collection_42 DROP CONSTRAINT snpdb_X_collection_42_pkey;
ALTER TABLE snpdb_X_collection_42 ADD PRIMARY KEY (id, collection_id);

-- c) attach to new declarative parent (instant if existing CHECK matches)
ALTER TABLE snpdb_X_new ATTACH PARTITION snpdb_X_collection_42 FOR VALUES IN (42);
```

After all children moved:

```sql
-- old parent now empty, dump it for rollback safety (#1537), then drop
DROP TABLE snpdb_X;
ALTER TABLE snpdb_X_new RENAME TO snpdb_X;
```

The brief table-rename window is the only system-wide block; everything else only blocks the partition under migration.

## Maintenance gates per hierarchy

- **VAV**: set `VariantAnnotationVersion.active = False` on the VAV being migrated. Existing code already handles "no active annotation". Run the three child tables (`variantannotation`, `varianttranscriptannotation`, `variantgeneoverlap`) in parallel sessions to minimise wall-clock.
- **CohortGenotypeCollection / VariantCollection / GeneCoverageCollection / VariantZygosityCountCollection**: set `settings.UPLOAD_ENABLED = False` to pause new imports during migration. Existing VCF queries continue to work since reads go directly to children via the SQL transformer.
- **HPA / ClinVar / GeneAnnotation**: tiny enough that no gating needed beyond stopping the relevant import workers briefly.

## Always dump before drop (#1537)

Every `DROP TABLE` of a partition child must be preceded by a `pg_dump --format=custom` to archive disk, even when the data is also being migrated via DETACH/ATTACH. The dumps are rollback safety, not the primary migration path. See #1537 for the pipeline.

This applies to:
- The pre-migration cleanup (stale VAV deletion).
- The cutover step where the old inheritance parent is dropped (defence in depth).
- Any future ad-hoc partition deletion.

## Estimated maintenance windows

For VAV (the biggest):
- v21 (largest): three tables in parallel → ~30 min wall-clock dominated by the 41 GB transcript-annotation index rebuild.
- All 7 VAVs sequentially through the VAVs but parallel across tables: ~2–3 hours.
- Just active VAVs (v21 + v54 if T2T): ~1 hour.

For the `RelatedModelsPartitionModel` tables: per-partition rebuild is seconds for ≤2M-row VCFs. Thousands of partitions × seconds, parallelisable → ~1–2 hours wall-clock.

## Pre-migration cleanup

Some old VAVs (likely v7/v18/v20, possibly v19) are stale. Drop them before the migration to shrink the migration set:

1. Dump each via the #1537 pipeline (safety net).
2. `VariantAnnotationVersion.delete()` — existing `pre_delete` signal drops the children in seconds.

## Sequencing

1. **Ship the runtime-detection code + the additive Django migrations** (`PartitionArchive`, `PartitionMigrationState`, `DataPurgeMixin` columns, `manage.py migrate_partitions` command). Servers continue to run on inheritance partitions; nothing is forced.
2. **HumanProteinAtlasAnnotationVersion first** — tiny, validates the new-style code path and the management command end-to-end on a real server.
3. **ClinVar and GeneAnnotation** next — same pattern, larger but manageable.
4. **VariantAnnotationVersion** — in-place per-partition with `active=False` gating and three-way table parallelism.
5. **`RelatedModelsPartitionModel` direct users** (CohortGenotypeCollection, VariantCollection, GeneCoverageCollection, VariantZygosityCountCollection) — same pattern, gated by `UPLOAD_ENABLED=False`.
6. Once all migrated **on all servers**, ship the cleanup release that deletes `RelatedModelsPartitionModel`, `sql_partition_transformer`, `SubVersionPartition`, and the inheritance fallback in `create_partition`.

## Current data sizes (VAV)

Production, queried via `pg_total_relation_size`:
- 7 active VariantAnnotationVersions (v7, 18, 19, 20, 21, 22, 54), totalling ~620 GB across `annotation_variantannotation`, `annotation_varianttranscriptannotation`, `annotation_variantgeneoverlap` children.
- Largest single child: `annotation_varianttranscriptannotation_version_21` at 100 GB / 329M rows.
- Top three transcript-annotation children (v21/v19/v7) account for ~284 GB on their own.

## Pre-conditions

For the `ATTACH PARTITION` step to skip validation scans, each existing child needs a `CHECK (collection_id = N)` (or equivalent partition-key constraint) — which `RelatedModelsPartitionModel.create_partition_for_base_table` (`django_partition.py:39`) already adds. Confirmed on existing children before migration.

The `id` sequence must be shared between the new declarative parent and the children. `RelatedModelsPartitionModel` already sets `nextval('parent_id_seq')` on each child (`django_partition.py:46`); the new parent must use the same sequence so newly-imported rows get unique IDs across all partitions.

## Related

- #1536 — `DataPurgeMixin` + audit. Required so analyses tolerate the brief per-partition maintenance windows without breaking joins.
- #1537 — Pre-drop archival pipeline. Mandatory before any `DROP TABLE` step in this migration. Must work against both inheritance and declarative children.
- SACGF/variantgrid_com#22 — Source nodes need to handle missing input data gracefully.
- SACGF/variantgrid_com#79 — Same data-purge concept applied to VCFs (delivered via #1536).