Create admin-only API endpoint that returns list of Submission Portal submissions

[eecavanna]

Create an API endpoint (accessible only by logged-in users that are admins) that responds with a TSV file containing a list of all Submission Portal submissions, including each one's:

• id
• Author Name and ORCID
• Study Name
• PI Name and Email address

Here's a SQL query that can be used to get that information:

SELECT t_sm.id                                                 AS "Submission ID",
       t_sm.author_orcid                                       AS "Author ORCID",
       t_ul.name                                               AS "Author Name",
       t_sm.metadata_submission -> 'studyForm' ->> 'studyName' AS "Study Name",
       t_sm.metadata_submission -> 'studyForm' ->> 'piName'    AS "PI Name",
       t_sm.metadata_submission -> 'studyForm' ->> 'piEmail'   AS "PI Email"
FROM   submission_metadata t_sm,
       user_logins         t_ul
WHERE  t_sm.author_id = t_ul.id
ORDER BY t_ul.name

Example output file (excerpt):

Submission ID   Author ORCID    Author Name Study Name  PI Name PI Email
f9882a44-0000-0000-0000-0fdcfe41243e    0000-0000-0000-8193 ""  My study    Alice...    alice@example.com
00001234-0000-0000-0000-0fdcfe123456    0000-0000-0000-1234 ""  Some study  Bobby...    bobby@example.com

This API endpoint could be used by team members that are collecting metrics for reports.

[eecavanna]

Hi @JamesTessmer, looks like I will have time to implement this after all. I'm working on something related, so it won't involve as much of a context switch as I had originally thought. I'll tag you as a reviewer in the PR.

[JamesTessmer]

@eecavanna Sounds good, thanks for the heads up! Tagging @mslarae13 in case you didn't see Eric's message

[eecavanna]

So far, I've implemented an endpoint that returns the same TSV file that I normally generate manually (using a Postgres client's "Export as TSV" button) and send to @mslarae13. This new endpoint will make that TSV file downloadable (to an admin) via the Swagger UI.

Still to do:

• Finish writing the corresponding test(s)
• Address any GitHub Actions failures (currently seems to be failing due to some linting errors)

I'm going to move this to the next sprint.

[eecavanna]

I implemented this in PR https://github.com/microbiomedata/nmdc-server/pull/1290, which is ready for review.

[eecavanna]

FYI @mslarae13, this endpoint now exists on the Data Portal in the development environment.

https://data-dev.microbiomedata.org/api/metadata_submission/report

It will be introduced into the production environment at the next NMDC system release (next week).

[mslarae13]

THanks @eecavanna ! This is in production now?

[eecavanna]

Yes, it's there!