Store DOI, raw read and target gene identifiers on the rows that use them #944

Summary

DOI, raw read (SRA), Ensembl, RefSeq and UniProt identifiers each live in a shared table that records link to. They're really attributes of the record. Every column besides the identifier is either a constant (db_name), always null (db_version, reference_html), or derived from the identifier (url). This issue moves each identifier string onto the row that uses it, merges the three gene offset tables into one, and drops the five shared tables.

That makes them ordinary owned rows under RLS, visible exactly when their record is (terms in the glossary on #808).

Scope

1. DOI and raw read link tables. Replace each link's foreign key to the shared row with the identifier string. The primary key becomes (<parent>_id, identifier). The tables:

   • DOI: scoreset_doi_identifiers, experiment_doi_identifiers, experiment_set_doi_identifiers
   • raw read: scoreset_sra_identifiers, experiment_sra_identifiers, experiment_set_sra_identifiers

2. One table for target gene identifiers. Replace ensembl_offsets, refseq_offsets and uniprot_offsets with target_gene_external_identifiers:

   target_gene_external_identifiers (
     target_gene_id integer NOT NULL REFERENCES target_genes (id),
     db_name        text    NOT NULL CHECK (db_name IN ('Ensembl', 'RefSeq', 'UniProt')),
     identifier     text    NOT NULL,
     "offset"       integer NOT NULL,
     PRIMARY KEY (target_gene_id, db_name)
   )

   • The primary key matches the data: each target gene has at most one identifier per database.
   • The api already presents these as one list, external_identifiers on target genes, built from three relationships by transform_external_identifier_offsets_to_list. One relationship replaces them, and the transform goes away.

3. Drop the shared tables: doi_identifiers, sra_identifiers, ensembl_identifiers, refseq_identifiers and uniprot_identifiers.

4. Normalize DOIs to one case before storing them, so a record can't cite the same DOI twice.

5. Compute urls when serializing, not store them. Build raw read urls from the accession type, which fixes Submitted Raw Read Identifiers are only created with SRA URLs, even though validation accepts other database identifiers #445.

6. Write paths. Create and update store the strings directly. Remove find_or_create_doi_identifier, find_or_create_raw_read_identifier and find_or_create_external_gene_identifier (lib/identifiers.py, L411-L446) and their callers in routers/experiments.py and routers/score_sets.py.

7. Search routes. These return distinct identifiers from the link rows and from target_gene_external_identifiers:

   • POST /doi-identifiers/search
   • POST /raw-read-identifiers/search
   • POST /target-gene-identifiers/search, which already takes db_name

   Until RLS is enforcing, they filter to rows whose parent the caller can read. Once the link tables carry policies, the database does that filtering.

8. Statistics. The identifier counts in routers/statistics.py group these rows by identifier.

9. Api schema. Changes are limited to removing fields:

   • the DOI and raw read response models lose id;
   • the external gene identifier model loses id and reference_html.

   Target gene responses keep the same external_identifiers shape. A ui PR regenerates src/schema/openapi.d.ts.

Out of scope: converting DOI identifiers that resolve to publications into publication citations (#217). Publication identifiers keep their shared table (#945).

Acceptance criteria

• The five shared identifier tables and the three offset tables are gone. The DOI and raw read link tables hold the identifier string, and target_gene_external_identifiers holds the gene identifiers, under the keys above.
• The migration replays from an empty database (alembic/README.md), and its downgrade rebuilds the old tables from the new rows.
• Creating and updating a score set or experiment with DOIs, raw read identifiers and target gene identifiers stores the strings without reading any shared table.
• Target gene responses return the same external_identifiers list as before, minus the removed fields.
• The three search routes return only identifiers attached to records the caller can read.
• Raw read urls match their accession type: SRA, BioProject, GEO and ArrayExpress (Submitted Raw Read Identifiers are only created with SRA URLs, even though validation accepts other database identifiers #445).
• A record can't hold the same DOI twice in different cases.
• Router tests cover create, update and search for each identifier type, including a private record's identifier staying out of another user's search.
• The ui PR that regenerates the API types is linked.