Y25-118 - As a Project Manager (Laura H) I would like to add two new columns to the seqops_tracking_per_plate table, library_type_ordered and project_name from the order_made event so that we can improve our financial planning.

[TWJW-SANGER]

User Story
As a Project Manager (Laura H) I would like to add two new columns to the seqops_tracking_per_plate table, library_type_ordered and project_name from the order_made event so that we can improve our financial planning.

Who are the primary contacts for this story
Tom W
Laura H
Michael F (finance)

Who is the nominated tester for UAT
Testing can be performed by the development team - see acceptance criteria.

Acceptance Criteria

• A new longtext column before 'libary_type' called 'library_type_ordered' is added to the table.
• 'library_type_ordered' is populated by a distinct concatenation of values from the library_type metadata key/value pair on the order_made event
• A new longtext column before sequencing_cost_code called 'project_name' is added to the table.
• project_name is populated by a distinct concatenation of values from the 'project' role/subject data associated with the order_made event.
• The output of the new query minus the 2 new columns is identical to the output of the old query.
• The Confluence page "Sample Tracking. Report" is updated with new columns and restrictions.

Additional context
The library_type value indicates the pipeline a sample is being processed on and is therefore key to estimating the cost for faculty. Currently library_type and sequencing_cost_code is only filled in when sequencing actually happens. This can be many months or even years after a sample has been submitted to SciOps.
The desired library type is present on the order_made event and 60% of samples in the database have an order_made event within 10 days of a manifest being uploaded.
The SequenceScape project name also allows users to identify the cost code associated with the sample sequencing earlier in the process.

[khelwood]

@TWJW-SANGER I've updated seq_ops_tracking_per_plate on test to add these two columns.

[TWJW-SANGER]

Looks good to me.
I have spot checked 5 plates, checked for differences between library_type_ordered and library_type and consistency between prod and uat.

Only the Confluence page to update with the new columns left.