The relation table’s unique key was (subject, object, mapped predicate, source), and every insert said ON CONFLICT DO NOTHING. That looks like ordinary idempotence until you ask what “mapped” hides. When CIViC calls one variant both a diagnostic and a prognostic marker, both raw predicates map to biolink:biomarker_for, so the second row simply never existed. The same happened to ClinVar’s oncogenicity calls behind germline calls, to OncoKB’s lower levels behind level 1, and to the 370 PrimeKG drug–protein pairs whose drug both targets and is metabolised by the same protein. The audit replayed each raw source through its own connector, keyed the way the insert keys. The ClinVar replay landed on exactly the 1,622 rows stored, and that match is what made me trust the rest.

The fix is one more column in the key, but the table is 7.4 GB and every migration runs inside a single transaction at every boot. A plain index build there would block writes for minutes and could outlast Cloud Run’s patience. So the index was built CONCURRENTLY first, by a script, in 2.1 minutes. The migration then only had to drop the old constraint. It does that under a five-second lock timeout inside a savepoint, so a long export read postpones it rather than failing the boot. It also refuses outright to build the index at boot on a large table. The inserts lost their explicit conflict targets, so they work under either key while the swap happens.

The part I’d have missed without reading the merge scripts: both entity-merge scripts deduplicate a loser’s edges against the winner’s on the old four columns. Under the new key, that dedupe would delete an “enzyme” edge whenever the winner had a “target” edge to the same protein, which is exactly the loss CLAUDE.md’s hard rule exists to prevent. Both now dedupe on the full key. The test that pins it fails when the old dedupe is put back.

Then the roles. PrimeKG’s display_relation says target, enzyme, transporter or carrier, and the legacy connector threw it away. The re-ingest maps endpoints exactly as the original backfill did and asks no external service anything. Its dry run had one property I wanted before touching production: the set of (drug, protein) pairs it would write equals the set stored, 49,664 to 49,664. Only the roles are new. The rows that still fold together, 1,250 of them, do so because PrimeKG entities were merged long ago, and that is #438’s other item, disclosed rather than fixed tonight.

One evidence gap remains, and the wider key cannot close it. When CIViC records the same claim for the same variant and drug in several studies or diseases, the graph keeps the first row’s evidence only. That’s 2,984 pairs today. Rolling that evidence up into the kept row is the next piece of #627.