Meta: partition migration + data/metadata separation — implementation plan

🤖 Written by Claude (drafted in conversation with @davmlaw)

Umbrella issue tying together the partition-migration work and the data/metadata separation that supports it. Each phase has its own ticket; this issue is just the overall plan and ordering.

## Goal

Deploy the foundation work to a new server and run a new `VariantAnnotationVersion` (#1532) as soon as possible. The bigger partition-mechanism migration runs at leisure afterwards, per-server — but the **new VAV itself lands directly in declarative partitions** so we don't pay to migrate ~100 GB later.

## Phase 1 — Pre-deploy code work

### Step 1. Fix SACGF/variantgrid_com#22 — analysis-node tolerance ✅
Source nodes (`SampleNode`, `TrioNode`, `CohortNode`, `PedigreeNode`, `AllVariantsNode`, etc.) return an empty queryset cleanly + show "Source data missing" status when their input is gone, instead of cryptic ORM errors. Pure defensive code, ships independently as a small release.

### Step 2. `DataArchiveMixin` + audit (#1536) ✅
- Add the mixin columns (`data_archived_date`, `data_archived_by`, `data_archive_reason`, `data_restorable_from`) to `VCF`, `Sample`, `Cohort`, `Trio`, `VariantAnnotationVersion`. Additive Django migration.
- **Audit every code path that joins through these models** so each respects `data_archived=True` as "no rows" rather than silent-empty-join. Annotation queryset builders, analysis node graph, anywhere that aggregates across `CohortGenotype` / `VariantAnnotation`. The audit is the load-bearing piece; the columns are the affordance.
- VCF re-import path (SACGF/variantgrid_com#79) — optional in this phase; can land later.

### Step 3. Pre-drop archival pipeline (#1537) ✅
- `PARTITION_ARCHIVE_DIR` setting (default `/data/database/partition_dumps`, per-DB subdirectory).
- `PartitionArchive` model + Celery task that runs `pg_dump --format=custom`, verifies via `pg_restore --list`, then drops the source children.
- Standard Django admin actions: "Archive partition data" / "Mark restored" / "Clear dump". Minimal — Django's built-in confirmation page is enough.
- Wired into `RelatedModelsPartitionModel._partition_table_op('drop')` so every partition drop is dump-first.

This **must ship before step 5** since step 5 is the first `DROP TABLE` operation we run.

### Step 4. #1534 runtime-detection code
- `create_partition()` checks at runtime whether the new declarative parent exists; uses old or new path accordingly.
- `PartitionMigrationState` model.
- `manage.py migrate_partitions ` management command.
- Additive Django migration only — no schema work forced at deploy. Same code binary works pre/mid/post-migration on any server.

Shipped as part of Phase 1 so the new VAV (step 7) lands in declarative partitions directly. Existing VAVs stay on inheritance until step 9 runs at leisure.

## Phase 2 — Deploy and run the new VAV

### Step 5. Deploy to new server
Code on the new server now: #22 + #1536 + #1537 + #1534 (runtime-detection only). Existing servers running the same release stay on inheritance partitioning unchanged. No schema migration anywhere.

### Step 6. Archive stale VAVs on the new server (do not delete)
For each stale VAV (likely v7/v18/v20, possibly v19 — confirm via references query):

1. Trigger via the #1537 admin action — `pg_dump` the three children, verify, write `PartitionArchive` row.
2. On verified-success: drop the partition children. Set `data_archived_date` / `data_archived_by` / `data_restorable_from = ` on the VAV row.
3. The VAV row stays. Analyses pinned to it render with "data archived YYYY-MM-DD" banners (handled by step 2's audit). Restorable later via the manual `pg_restore` runbook.

Frees disk before the new VAV's data lands; preserves the audit trail of what was annotated when.

### Step 7. Create empty declarative parents for the VAV tables on the new server
One-shot `CREATE TABLE … PARTITION BY LIST (version_id)` for `annotation_variantannotation`, `annotation_varianttranscriptannotation`, `annotation_variantgeneoverlap` (under temporary `_new` names so existing inheritance parents keep working). No existing children attached yet — old VAVs continue to use the inheritance parents until step 9 migrates them.

With the declarative parents present, the runtime check in `create_partition()` will route any new VAV's children to the new scheme.

### Step 8. Run the new `VariantAnnotationVersion` (#1532) — milestone
`create_partition()` sees the declarative VAV parents from step 7 and attaches the new VAV's three children there directly. **This is the milestone we wanted ASAP, and we avoid having to migrate 100 GB of fresh data later.**

## Phase 3 — Partition migration of older data (at leisure)

### Step 9. Run `manage.py migrate_partitions` per-model on each server
At each server's leisure. Order per server: HPA → ClinVar → GeneAnnotation → existing VAVs → CohortGenotypeCollection / VariantCollection / GeneCoverageCollection / VariantZygosityCountCollection.

Per-server independent — each deployment runs the migrations whenever convenient. The pre-drop archival from #1537 fires automatically at the cutover step.

For VAV specifically, the new VAV is already in declarative partitions, so this step only migrates the older inheritance VAVs into the same declarative parents (and renames `_new` → final name once done).

Maintenance gates:
- VAV: `VariantAnnotationVersion.active = False` on the VAV being migrated (existing code handles "no active annotation"). Three-way table parallelism.
- `RelatedModelsPartitionModel` direct users: `settings.UPLOAD_ENABLED = False` to pause new imports.
- HPA / ClinVar / GeneAnnotation: stop the relevant import workers briefly.

### Step 10. Cleanup release
Once all deployed servers have completed step 9 (visible from `PartitionMigrationState`), ship a release that deletes:
- `RelatedModelsPartitionModel` and `SubVersionPartition` base classes.
- `sql_partition_transformer` hook (`library/django_utils/django_partition.py:79`).
- The inheritance fallback in `create_partition`.

The custom partition machinery is gone.

## Summary table

| Step | Work | Ticket | Blocks new VAV? |
|------|------|--------|-----------------|
| 1 | Analysis-node tolerance | variantgrid_com#22 | Defensive |
| 2 | `DataArchiveMixin` + audit | #1536 | Enables step 6 |
| 3 | Pre-drop archival pipeline | #1537 | **Yes** (required for step 6) |
| 4 | Runtime-detection code | #1534 | **Yes** (required for step 7) |
| 5 | **Deploy to new server** | — | — |
| 6 | Archive stale VAVs | uses #1536 + #1537 | Frees disk |
| 7 | Create empty declarative VAV parents | #1534 | Required for step 8 to land in new scheme |
| 8 | **Run new VAV** | #1531 | — milestone — |
| 9 | `migrate_partitions` per server (older data) | #1534 | No |
| 10 | Cleanup release | #1534 | No |

Minimum to unblock the new-VAV-into-declarative path is **steps 1–8**. Steps 9–10 stretch over months without holding anything up.

## Issues

- SACGF/variantgrid_com#22 — Source-node tolerance for missing inputs.
- SACGF/variantgrid_com#79 — Delete chicago sample (delivered via #1536 + #1537).
- #1536 — `DataArchiveMixin` + audit + VCF re-import.
- #1537 — Pre-drop archival pipeline (`pg_dump` before any partition `DROP TABLE`).
- #1534 — Migrate inheritance partitioning to declarative.
- #1531 — Annotation Upgrade - Run the new `VariantAnnotationVersion`.