SPIKE: Non-graph and linear queries approach for Universal Metadata Search

[JFU-NAVA-PBC]

User Story

As a developer working on report stream platform, while reviewing the universal pipeline metadata search proposals (see document at: https://github.com/CDCgov/prime-reportstream/blob/master/prime-router/docs/proposals/0020-universal-metadata-search/0020-universal-metadata-search.md), it is desirable that additional solution be added to support report stream (RS) universal pipeline (UP) metadata search and test report back tracking with the following characteristics:

1. Conceptually straight forward (no need to leverage graph to describe the concept) for users and implementors
2. Metadata can be obtained with straight forward SQL (Relational DB) in linear order
3. Back tracking the origin and transformation / filtering of tests and / or reports can be done with simple SQL in linear time
4. Underlying tables and their relationships relatively simple, the number of join operations needed to achieve 2. and 3. are minimum

Description/Use Case

As described in user story section, the current proposals are conceptually complex and likely result in complex SQL queries (e.g. recursive SQL queries), and the proposed approaches are suggesting performing metadata queries on RS runtime DB which will impact RS UP runtime operations, i.e. incur latencies on senders / receivers / processors operations since they all running on the same DB tables.

Risks/Impacts/Considerations

This is a SPIKE for alternative solutions, no risk / impact

Dev Notes

The alternative solution once reviewed by the team, can be added to the existing proposal document at (https://github.com/CDCgov/prime-reportstream/blob/master/prime-router/docs/proposals/0020-universal-metadata-search/0020-universal-metadata-search.md)

Acceptance Criteria

A proposal is delivered, e.g. as a mark done file under RS repo proposal folder, for team to discuss. the document includes:

• Concept description and diagram, use cases for the proposed approach
• Pros and Cons
• SQL queries for use cases - preferably a comprehensive list of SQL queries cover questions that can be answered by querying on the metadata collected from the UP flow

[bishoyayoub]

@JFU-NAVA-PBC Please amend this ticket to use the Platform User Story template via Github.

[bishoyayoub]

@JFU-NAVA-PBC Please move to ready for grooming or let me know if we should icebox this story.