Carry scoreset_id on mapped_variants under a composite foreign key

[bencap]

Why

mapped_variants reaches scoresets only through variants, which has millions of rows. Delegating a policy through a large table is not viable: measured, it costs 688 ms to return 16 rows, because the plan builds a hash of every visible variant regardless of how selective the outer query is. The bitmap index scan works perfectly and is then wasted.

Carrying scoresets.id locally turns the policy into a semi-join against ~2k rows: 15 ms for a 400k-row scan, 1.2 ms for a narrow lookup.

This is the only table that needs it, assuming the cardinality measurement in #832 confirms target_genes and score_calibrations are small.

Scope

ALTER TABLE variants ADD CONSTRAINT variants_id_scoreset_uq UNIQUE (id, scoreset_id);

ALTER TABLE mapped_variants ADD COLUMN scoreset_id integer;
UPDATE mapped_variants mv SET scoreset_id = v.scoreset_id
  FROM variants v WHERE v.id = mv.variant_id;
ALTER TABLE mapped_variants ALTER COLUMN scoreset_id SET NOT NULL;

ALTER TABLE mapped_variants ADD CONSTRAINT mapped_variants_scoreset_fk
  FOREIGN KEY (variant_id, scoreset_id) REFERENCES variants (id, scoreset_id);

The composite FK is the point. A plain scoreset_id REFERENCES scoresets(id) would permit drift — a mapped variant pointing at a private score set's variant while claiming a public scoreset_id. With the composite constraint in place, an inconsistent UPDATE is rejected by the database. No trigger, no ongoing maintenance, no drift class.

Re-pointing a mapped variant to a variant in a different score set then requires updating both columns in one statement.

The composite FK subsumes the existing simple variant_id FK; decide whether to drop the latter or keep both.

Elevation

The backfill UPDATE runs inside system_role() (#827). Migrations default to mavedb_owner, which is a member of nothing, so an unelevated backfill is RLS-filtered and silently populates scoreset_id for public score sets only.

Answer these before writing the migration

Run against a FORCEd table as mavedb_owner with rows hidden from that role:

1. Does ALTER TABLE ... VALIDATE CONSTRAINT scan all rows, or only visible ones? If only visible ones, NOT VALID then VALIDATE CONSTRAINT marks the FK valid over an inconsistent table.
2. Does ALTER TABLE ... SET NOT NULL scan all rows?
3. Does CREATE UNIQUE INDEX see all rows?

If all three scan everything, the staged sequence below is safe and a mis-elevated backfill is caught loudly by SET NOT NULL. If any of them respects RLS, the sequence needs restructuring.

Migration cost

This is a large-table migration and should be planned as one:

• The backfill UPDATE rewrites every row in mapped_variants.
• SET NOT NULL requires a full scan. PG 12+ can use a validated CHECK to avoid the exclusive-lock scan.
• The unique index on variants(id, scoreset_id) is an additional index on a millions-row table — real storage, and it must be built before the FK.
• Adding the FK validates against every existing row; NOT VALID then VALIDATE CONSTRAINT avoids holding a strong lock throughout.

Set lock_timeout and retry rather than queueing behind long-running queries on variants.

Acceptance criteria

• The three scan questions above are answered and recorded in this issue before the migration is written.
• mapped_variants.scoreset_id is NOT NULL and consistent with variants for every row.
• The composite FK exists, and a test asserts an inconsistent update is rejected.
• A test asserts the backfill populates rows belonging to private score sets.
• Backfill and constraint validation are staged to avoid long exclusive locks, with lock_timeout set.
• The ORM model carries the column, and inserts populate it — new mapped variants cannot be created without it.
• A test asserts the mapped_variants policy's full-scan plan contains a hashed SubPlan.