Ideas to improve readability of auxtable code

[theosanderson]

If we were to keep the aux tables we should try to improve the readability of the code.

I spent a bit of time looking at https://github.com/loculus-project/loculus/blob/main/backend/src/main/kotlin/org/loculus/backend/service/submission/UploadDatabaseService.kt to try to think about what the options would be for that.

Perosnally I don't find the big SQL too horrendous to read but I acknowledge options will differ.

We should rename mapAndCopy to insertFromAuxTablesIntoMainTable or something.

---

We could create a VIEW in a migration, something like:

CREATE VIEW sequence_entries_staging AS
SELECT
    m.accession,
    m.version,
    m.organism,
    m.submission_id,
    m.submitter,
    m.group_id,
    m.uploaded_at AS submitted_at,
    m.upload_id,
    jsonb_build_object(
        'metadata', m.metadata,
        'files', m.files,
        'unalignedNucleotideSequences', 
        COALESCE(
            jsonb_object_agg(
                s.segment_name,
                s.compressed_sequence_data::jsonb
            ) FILTER (WHERE s.segment_name IS NOT NULL),
            '{}'::jsonb
        )
    ) AS original_data
FROM
    metadata_upload_aux_table m
LEFT JOIN
    sequence_upload_aux_table s
    ON m.upload_id = s.upload_id 
    AND m.submission_id = s.submission_id
GROUP BY
    m.upload_id,
    m.accession,
    m.version,
    m.organism,
    m.submission_id,
    m.submitter,
    m.group_id,
    m.uploaded_at,
    m.metadata,
    m.files;

then the SQL in the code would just be

INSERT INTO sequence_entries (
    accession,
    version,
    organism,
    submission_id,
    submitter,
    group_id,
    submitted_at,
    original_data
)
SELECT
    accession,
    version,
    organism,
    submission_id,
    submitter,
    group_id,
    submitted_at,
    original_data
FROM sequence_entry_staging
WHERE upload_id = ?
RETURNING accession, version, submission_id;

[anna-parker]

Wasn't quite sure where to put these thoughts, but if we change submission for multi-segmented to have the grouping specified in the metadata tsv this would also be easier as no fasta header parsing is required.

Proposal

Extend metadata tsv to have two metadata columns: sampleID (submissionID) and fastaIDs. If no fastaIDs column is specified sampleID is used as the fastaIDs column. fastaIDs is a comma-separated or space-separated list of all fasta IDs corresponding to one sample. Send the original data to the preprocessing pipeline with the fastaIDs to improve the error message.

Potential aux-table refactor

Sequences are added to the aux sequences table with their fastaID and the submissionBatchID as the primaryKey. Metadata is added to a metadata aux table with the sampleID and submissionBatchID as the primaryKey. An additional "junction" (map) table then needs to be created for each (sampleID, fastaID, submissionBatchID) from the metadata entries where fastaID is created from parsing the list of fastaIDs. This can then be used to join the tables. We should then error if a sampleID or fastaID does not exist or is not unique

In-memory alternative

Even better this can be done by only keeping the metadata and map in memory and streaming in the sequences. If we hit a sequence that is not in the map we error and if we don't find all specified fastaIDs we also error. We also error if sampleID or fastaID is not unique.

Pros

• No fasta header parsing issues e.g. Is _ a good separator for segments? #4734
• Can be extended easily to co-infection and multi-path

Cons

I guess the only case this does not cover is if someone wants to run an instance of Loculus where the user needs to specify the segment manually instead of having the pipeline assign it. This could be solved by having the administrator write a custom preprocessing pipeline that validates the fastaIDs of each submission. For example the preprocessing pipeline could enforce that the fastaIDs include a segment as we currently do.

[theosanderson]

And if we didn't want the junction table you can even do it on comma-separated values, something like:

SELECT 
    metadata.*, 
    fasta.*
FROM metadata
CROSS JOIN LATERAL unnest(string_to_array(metadata.fasta_ids, ',')) AS fasta_id
JOIN fasta ON fasta.id = fasta_id;

(I personally prefer all the SQL options to in memory options - I think SQL is designed for exactly these sorts of operations)

In terms of users specifying a segment, one option would be that we still do exactly this but the user happens to choose fasta headers which do conform to a pattern like the seperator, and then they write a pipeline that parses that info.

[anna-parker]

Steps:

1. Change compression dictionary: Change compression of original data #4962
   a. add label to compressed data that annotates which compression dictionary is used
   b. add a new compression dictionary
2. Refactor the aux-table (check if the junction/map table is more efficient else keep two tables) and change how fasta entries and metadata entries are joined/grouped
   a. change how the segments are uploaded: change in ingest, change example data and helper text
   b. need a db migration (?)
   c. move the validation and segment assignment to preprocessing -> can be first backward compatible to assign segment using underscore, or just one "clean break" immediate switch to prepro assigning segment