Update TSV/SQL data

[emiliorighi]

As we mentioned yesterday, the refseq assemblies always point to a genbank assembly, we can further reduce the db size (both tsv and sql), by merging the aliases lists of the paired assemblies:

genbank assembly accession (pk), refseq accession (index it but allow null values), merged list of aliases (use set to keep unique values from both lists)

[Max25R]

Thanks, quick question

1. "Merged list of aliases" — shape of the column?

(a) A single set of names with the convention info collapsed — e.g. {chr1, 1, NC_000001.11, CM000663.2} — where the tool only checks whether a name belongs to the set, or

(b) A structured field that preserves conventions — e.g. JSON like {"refseq": "NC_000001.11", "ucsc": "chr1", ...} — so --from refseq --to ucsc still works the same way?

(a) means reworking the v0.2 convert flow around equivalence-class lookup. (b) keeps the existing CLI working and fits the FASTA-driven flow for v2. leaning towards b

[emiliorighi]

I think b is the best option too, but maybe we can use string-like format (refseq:NC_000,ucsc:chr1, etc..) to further reduce the tsv size (and the unpack to json on the sql db built on runtime)

[Max25R]

1. TSV row granularity.
   When you said "merge the aliases lists of the paired assemblies" — did you mean:

• One row per assembly, with every sequence's aliases merged into a single field, or
• One row per sequence, with that sequence's aliases merged?

2. RefSeq-only assemblies.
   Some assemblies exist only on RefSeq (GCF_*) with no paired GenBank. GenBank-as-canonical is settled for the user-facing flow, but those assemblies have no GenBank accession to use as the storage-level primary key.
   Default plan: still one row, keyed by refseq_acc instead, with genbank_acc null. OK?

3. assembly_name on paired rows.
   Paired GenBank+RefSeq assemblies sometimes have slightly different names. Leaning toward storing both as separate columns rather than picking one. Sound right?

4. Between-sequence separator in the TSV cell.
   refseq:NC_x|ucsc:chr1 works within one sequence. Assemblies have many sequences, so we need a separator between them too. Three options:

• refseq:NC_x|ucsc:chr1;refseq:NC_y|ucsc:chr2 (; between, | within)
• Same with , between, | within
• ["refseq:NC_x|ucsc:chr1","refseq:NC_y|ucsc:chr2"] (JSON list)

Also, quick update: v1 is shipped — #3 (packaging) and #6 (first-run download) are closed. The workflow now also publishes failures.tsv

[emiliorighi]

1)TSV row granularity:
You can explore both possibilities try to find a good balance between tsv size and db creation: I'm leaning toward one row per assembly pair with columns for each convention containing the sorted list
Something like:
genbank_ass_acc refseq_ass_acc genbank_ass_name refseq_ass_name genbank_seq_acc refseq_seq_acc ucsc seq_acc

Where each _seq_acc column contains the ordered list (with null values) of the aliases: a cell will look like: 1,2,3,4,5,6,7,8...

Feel free to go into another direction if you think of a better and scalable solution.

2. ideally we want to have the same object where either the genbank or the refseq accession exist (at least one of them)

3. same as above (2)

4. here as well try to look for the most scalable and performant option: this queries are usually cheap as we can have up to 250 aliases max per assembly pair, so we should prioritize efficiency and scalability over readibility (fast and low memory)

[Max25R]

Like the column-per-convention approach. Three clarifications before I start:

1. TSV vs SQLite shape: same column layout in both, with comma-separated lists in TEXT cells?
2. Length: also a position-aligned comma-separated column?
3. Sort order for the lists: sorted by what? Default: length descending (matches today's top-50-longest behavior). The key matters since all position-aligned columns need to use the same ordering.