Audit views to ensure they align with database and REDcap and verify endpoint functions

[mwvaughn]

While spot-checking a few responses, I noticed some duplicated records. We need to systematically compare the results from views with the intended state based on the REDcap database. We also need to systematically verify the function of each endpoint.

[mwvaughn]

Biospecimens

Resolved duplicate rows in /biospecimens by adding a DISTINCT clause to the biospecimens_details and biospecimens_details_private view SQL. Confirmed by comparison of a couple containers to REDcap.

• There are 63 plasma aliquot biosamples in container K_RU_BOX_ALI_0005. They are from 8 collections.
• There are 47 paxgene biosamples in container K_RU_BOX_PAX_0001. They are all from different collections.

[mwvaughn]

Containers

As of 1/7/2022, there are currently 110 containers. They all have distinct tracking IDs. There is no duplication, so this endpoint is working as intended.

[mwvaughn]

Shipments

As of 1/7/2022, there are 6 shipments, reflecting the same number of records in REDcap.

Shipments/Containers

Spot checking shipment 285590095441, it holds containers K_NS_BOX_ALI0001, K_NS_BOX_ALI0002, K_NS_BOX_ALI0003, K_NS_BOX_ALI0004, K_NS_BOX_ALI0005, K_NS_BOX_ALI0006, K_NS_BOX_ALI0007, K_NS_BOX_PAX0001

This is consistent with the REDcap records, so this endpoint is working as intended.

[mwvaughn]

Subjects

A duplicate subject record (as determined by identical GUID) was identified in the API response. Further investigation revealed that there was actually a duplicate record, introduced by the recent enforcement of string case on tracking identifiers. The endpoint itself is working as intended.

[mwvaughn]

Commit 035be5a4894d9d9fadae07333179d40451cc6845 fixed an issue reported by @shwetagopaul92 where updating status of a biospecimen was failing due to a misnamed PgREST view.

[mwvaughn]

19 is now fixed, addressing an issue where it was not possible to move a biosample to a new container.