Repair duplicate `current` mapped variants across 26 published score sets

[bencap]

44,218 variants carry two mapped_variants rows both flagged current. The pair comes from the same mapping run — identical mapped_date and mapping_api_version — and differs only in that one row has vrs_version IS NULL and no digest. A replacement was written without the superseded row's current flag being cleared.

26 score sets are affected, all published. Most are affected in their entirety (4,885 of 4,885 variants in one case), so this traces to specific mapping runs rather than scattered corruption. Maximum is two current rows per variant. Affected URNs include 00001203-a-1/a-2, 00000661-a-1/b-1/c-1/d-1, 00000298-*, 00000202-*, 00000658-*.

The score set CSV export outer-joins on current, so those exports emit roughly double the rows, silently. Anyone who downloaded them got duplicated data. The VA-Spec annotation streams duplicate too, but X-Total-Count counts mapped variants, so the stream is self-consistent and the duplication is invisible from its own accounting.

No partial unique index on (variant_id) WHERE current. Reverse translation's windowed approach to currentness makes this shape unrepresentable given its indexing strategy, so a constraint we would drop shortly is not worth the lift. This is a one-time repair.

Discriminator

All 44,218 pairs have exactly one row with vrs_version IS NULL. A digest-based predicate discriminates only 32,320 of them, so it would miss a quarter.

Detection

WITH duplicate_variants AS (
    SELECT variant_id, COUNT(*) AS current_count
    FROM mapped_variants
    WHERE current = true
    GROUP BY variant_id
    HAVING COUNT(*) > 1
)
SELECT mv.id, mv.variant_id, dv.current_count, mv.mapped_date, mv.modification_date,
       mv.mapping_api_version, mv.vrs_version, mv.target_gene_mapping_id, mv.clingen_allele_id
FROM mapped_variants mv
JOIN duplicate_variants dv ON dv.variant_id = mv.variant_id
WHERE mv.current = true
ORDER BY mv.variant_id, mv.modification_date DESC, mv.id DESC;

Repair

Validated locally: targets exactly 44,218 rows, none left over.

UPDATE mapped_variants stale
   SET current = FALSE, modification_date = now()
 WHERE stale.current
   AND stale.vrs_version IS NULL
   AND EXISTS (SELECT 1 FROM mapped_variants keep
                WHERE keep.variant_id = stale.variant_id AND keep.current
                  AND keep.vrs_version IS NOT NULL AND keep.id <> stale.id)
   AND (SELECT count(*) FROM mapped_variants sib
         WHERE sib.variant_id = stale.variant_id AND sib.current) = 2;

Run the detection query against production first and inspect every row outside the pattern above — more than two current rows, or no row with a null vrs_version. Locally there are none, but production may hold shapes the local subset does not, and those must not be swept up.

Acceptance criteria

• the detection query returns zero rows against production after the repair
• mavedb.scripts.export_sweep exits 0 on the CSV surface for the 26 affected score sets
• the rows outside the pattern, if production holds any, are enumerated and handled deliberately rather than by the predicate above

Open: whether the reverse translation migration carries these duplicates forward or dissolves them. If windowed currentness derives from a different column, a pre-migration repair is either redundant or the last easy moment to do it.