Resend dropped messages in the Covid Pipeline

[victor-chaparro]

Problem statement

Some messages did not get routed to STLTs in the CP due to a missing mapping for specimen type. The specimen type has been added since April 17th. SimpleReport might keep messages for longer then 60 days so they may be able to resend all the messages that were dropped.

What you need to know

This query return all dropped messages in the last 60 days that need to be resent:

select 
detail->>'filteredTrackingElement' as messageId, 
detail->>'receiverName' as receiverName,
al.created_at,
detail->>'message' as message,
m.sender_id
from action_log al 
join covid_result_metadata m on detail->>'filteredTrackingElement' = m.message_id
where type = 'filter' 
and detail->>'filterType' = 'QUALITY_FILTER'
and detail->>'filterName' <> 'allowNone'
and detail->>'receiverName' <> 'ca-dph.full-elr-saphirestage'
and detail->>'filterName' not like '%reversed%'
and detail->>'message' not like '%simple_report%'
and detail->>'receiverName' not like '%secondary%'
and detail->>'receiverName' not like '%test%'
and detail->>'receiverName' not like '%stage%'
and detail->>'receiverName' not like '%elr-neg%'
and detail->>'message'  like '%specimen_type%'
and al.created_at > now() - interval '60 day'
order by detail->>'receiverName' desc

Acceptance criteria

• [ ] Check that all dropped messages are resent from SR

[chris-kuryak]

SR is going to be resending these messages

[chris-kuryak]

Look at messages that failed the quality filter the first time. Can check those messages have all been successfully sent.

[GilmoreA6]

SR resent 428 messages (listed in): https://www.simplereport.gov/metabase/question/872-test-events-filtered-by-specimen-type.

These were resent on 06/12/24 in 20 batches between 10:50 ET and 10:55 ET

[chris-kuryak]

Thank you @GilmoreA6 !

[JessicaWNava]

Query for determining which message_ids were needing to be resent: select distinct message_id from action_log al join covid_result_metadata m on detail->>'filteredTrackingElement' = m.message_id where type = 'filter' and detail->>'filterType' = 'QUALITY_FILTER' and detail->>'filterName' <> 'allowNone' and detail->>'receiverName' <> 'ca-dph.full-elr-saphirestage' and detail->>'filterName' not like '%reversed%' and detail->>'message' not like '%simple_report%' and detail->>'receiverName' not like '%secondary%' and detail->>'receiverName' not like '%test%' and detail->>'receiverName' not like '%stage%' and detail->>'receiverName' not like '%elr-neg%' and detail->>'message' like '%specimen_type%' and al.created_at > '2023-12-24' and al.created_at < '2024-04-17' and sender_id = 'simple_report.default'

[JessicaWNava]

Query for messages that appear to be resent: select * from report_file inner join item_lineage on item_lineage.child_report_id = report_file.report_id where report_file.created_at > '2024-06-12 10:50' and report_file.created_at < '2024-06-14' and report_file.transport_result like 'Success%' and tracking_id in ( select distinct message_id from action_log al join covid_result_metadata m on detail->>'filteredTrackingElement' = m.message_id where type = 'filter' and detail->>'filterType' = 'QUALITY_FILTER' and detail->>'filterName' <> 'allowNone' and detail->>'receiverName' <> 'ca-dph.full-elr-saphirestage' and detail->>'filterName' not like '%reversed%' and detail->>'message' not like '%simple_report%' and detail->>'receiverName' not like '%secondary%' and detail->>'receiverName' not like '%test%' and detail->>'receiverName' not like '%stage%' and detail->>'receiverName' not like '%elr-neg%' and detail->>'message' like '%specimen_type%' and al.created_at > '2023-12-24' and al.created_at < '2024-04-17' and sender_id = 'simple_report.default' )

[JessicaWNava]

Also going to add a note per Chris' request that says the query returns three messages and I am going to create a follow up ticket to track down what happened to those. #14850

[chris-kuryak]

So it appears all but 3 messages were sent successfully. Since follow on ticket is created, we are good to close this one.