DPL-896 For Targeted Nanoseq Pipeline - Merge Data from the following data sets: Input amount file, Hamilton driver file, PCR XP concentration file

[SujitDey2022]

User story

As a Short Read Team User, I want to be able to download a merged file following the Library Prep process containing sample details received from the Sample Input Amount File from the faculty group (CASM), PCR XP Concentration File (Limber LIMS), and PCR XP Plate Data from Sequencescape LIMS SO THAT we can use the merged data in the sample 'rearray' template required for the ISC process.

This is a completely manual process that requires Short Read Targeted Nanonseq Team to collavborate with the SSRs to be able to merge data from various source files and Sequencescape LIMS so as to allow for the merged data to be fed into the 'rearray' template that includes the pooling plan. Each single merged file corresponds to a single 96-well sample plate and the manual efforts can vary from 2-to-40 sample plates requiring 1-2 FTEs every week.

Current Manual Process:

1. Receive the Sample Input Amount File from the faculty (CASM) SSR (Siobhan)

• SSR sends a pre-populated file template received from the Targeted Nanonseq Team to the faculty for providing the requested details. Example: SQPP-30689-M_TNano_AL Lib_qPCRconc_310823.csv

2. Download the concentration .csv file for custom pooling from Limber

• Example: Limber SQPP-22955-O : https://limber.psd.sanger.ac.uk/limber_plates/186d9fd4-0b6a-11ee-9148-fa163eac3af7

3. Ctrl+A the Plate data from Sequencescape

• Example: Sequencescape SQPP-22955-O : https://sequencescape.psd.sanger.ac.uk/labware/27502701

4. Merge all the data mentioned in points 1-to-3 above

• Attached is a sample of the merged file created manually - Copy of SQPP-20828-A_Merged file_150623.xlsx

In addition to this, below is an overview of the approx. monthly demand forecast,

LIBRARIES	Duplex/Targeted Nanoseq Samples	Approx. No. of 96 Well Plates
Aug-23	241	2
Sep-23	193	2
Oct-23	145	1
Nov-23	905	9
Dec-23	804	8
Jan-24	804	8
Feb-24	2201	22
Mar-24	1397	14
Apr-24	1397	14
May-24	1397	14
Jun-24	0	0
Jul-24	0	0

Who are the primary contacts for this story Sara W. Maria M.

Who is the nominated tester for UAT Sara W. Maria M.

Acceptance criteria To be considered successful:

• [x] Need to identify if the data from the source files can be fetched to generate the merged file
• [x] Identify if the manual input data received as part of the Sample Input Amount file provided by SSR can be automated or entered/saved in LIMS
• [x] Create new user stories for the implementation work required to provide a solution for this process.

References Sample of an existing merged file for the incoming sample requests,

Copy of SQPP-20828-A_Merged file_150623.xlsx

Additional context Add any other context or screenshots about the feature request here.

[andrewsparkes]

Pipeline flow: LTN AL Lib - in Limber download the concentration file for the customer mentioned in step 1, with empty columns (there is some discrepency between columns, e.g. they have added an extra column for 'Hyb Panel' and some columns are missing) LTN AL Lib Dil - in Limber we upload the now completed file, with input amount desired and available completed, in LabwareCreators::PcrCyclesBinnedPlate, where it is using in the binning calculations. The file is uploaded to Sequencescape qc files on the plate, via api v1 as 'duplex_seq_customer_file.csv' (see app/models/labware_creators/pcr_cycles_binned_plate.rb - this filename should come from the labware creator config in the plate purpose, and should have a different name for targeted nanoseq). We do not appear to otherwise persist the data after using it for the binning. 'Hyb panel' is not parsed or persisted. LTN Lib PCR - nothing relevant LTN Lib PCR XP - here they do another QC and can download the concentration file mentioned in step 2 for custom pooling. It is this plate they are looking up further information in Sequencescape for step 3

Columns in merge file and origin of values: Original Plate Barcode - barcode of the LTN AL Lib plate - from Sequencescape Original Well ID e.g. A1 - well of LTN AL Lib plate - from Sequencescape [ORDER ON THIS COLUMN] Concentration (nM) e.g. 20 - concentration of well - from QC data in Sequencescape for LTN AL Lib plate (well.latest_molarity) Sanger Sample ID e.g. 1234STDY12345678 - from Sequencescape Supplier Sample Name e.g. PD12345b_tds0001 - from Sequencescape Input amount available (fmol) - comes from api/v2/well method input_amount_available on LTN AL Lib well - also displayed in customer file downloaded on the LTN AL Lib plate and uploaded on the LTN AL Lib Dil plate Input amount desired (fmol) - specified in customer file uploaded on the LTN AL Lib Dil plate New Plate Barcode - the LTN Lib PCR XP plate barcode (plate from which this file is created) New Well ID - the LTN Lib PCR XP plate well _Concentration (ng/ul) - concentration of well - from QC data in Sequencescape for LTN Lib PCR XP Hyb Panel (string) - hyb panel name for use at sequencing - specified in customer file uploaded on the LTN AL Lib Dil plate

Summary LIMS appears to have all the information needed to create an export csv merge file to download on the LTN Lib PCR XP plate. Need to make changes to parse and persist all the relevant customer uploaded information in LIMS on the requests, so easily accessible later at the XP stage.

The wells are rearranged when creating the LTN AL Lib Dil plate, so fetching the ancestor well information is not straight forward.

Stories Story 1 See https://github.com/sanger/sequencescape/issues/3941 Create functionality to allow storage of custom metadata for requests. Create request_custom_metadata and request_custom_metadatum_collection tables and models to allow storing custom information on requests.

See existing custom_metadata and custom_metadatum_collection versions linked to labware (asset_id).

Tasks: 1.) Create migration for tables 2.) Create models 3.) Create api v2 access so accessible from Limber

Size: medium

Story 2 See https://github.com/sanger/limber/issues/1448 Modify customer file handling for Targeted Nanoseq pipeline Modify the customer file download/upload columns, and persist the following data on the requests at upload on the LTN AL Lib Dil plate (values from file uploaded) so they can be used for merged file creation later:

• original plate barcode (LTN AL Lib plate barcode)
• original well id (e.g. A1)
• concentration nM
• input amount available
• input amount desired
• hyb panel

See labware creator PCRCyclesBinnedPlate after_transfer! method which currently stores values in wells (specific columns were added to receptacles table to facilitate this). NB. this creator is also used for duplex seq, and they seem to have different lists of columns, so may need to create variants of this class for duplex seq vs targeted nanoseq, or use distinct configurations.

Store key / value pairs in the request custom metadata tables for use generating the merged file later.

Tasks: 1.) modify or duplicate PCRCycleBinnedPlate labware creator so can have different file handling behaviour for duplex seq vs targeted nanoseq (duplex seq should be unchanged) 2.) modify customer file download for targeted nanoseq to have correct columns including hyb panel 3.) modify customer file upload to parse modified columns 4.) persist customer file upload values by well to the request metadata

Size: Large story

Story 3 See https://github.com/sanger/limber/issues/1449 Create merged file for Targeted Nanoseq pipeline Create the merge file on the LTN Lib PCR XP plate as a standard export file (see exports_controller and exports.yml). For each well in the plate, fetch the above values from the well library requests (request type key in pipeline config), and get the following from the current plate:

• new plate barcode (LTN Lib PCR XP plate barcode)
• new plate well id (e.g. A1)
• concentration ng/ul - well.latest_concentration

Sort on original plate barcode, original well id

Tasks: 1.) For each well on XP plate, pull information from request metadata linked to the XP well and combine with XP well information 2.) Sort so ordered by original plate barcode and well id 3.) write rows into csv file

Size: Medium story