generate reports of all observed MIxS environmental triad slot values, within a given context

[turbomam]

We should be able to report all env_broad_scale, env_local_scale and env_medium values in two dimensions of contextuality:

MongoDB or SubmissionPortal

• prod
• dev
• sandbox
• napa
• berkeley-schema-fy24

etc

The reports should be available on demand, from either a custom web page or a new endpoint in one of our APIs

The reports should have the following columns:

• environment identifiers (MongoDB or SubmissionPortal, environment, etc)
• submission id if relevant
• environmetnal package value
• Biosample id or unique identifier for submission row
• MIxS slot identifier (env_broad_scale, env_local_scale or env_medium)
• raw MIxS slot value
• term id portion of raw MIxS slot value, if accessible
• term label portion of raw MIxS slot value, if accessible
• is term id present in one of our trusted ontologies?
• is term label present in one of our trusted ontologies?
• do term id and term label match?
• is the term from a path in the ontology that makes sense for the combination of the environmental package and the MIxS slot identifier?

possible stakeholders: @turbomam, @mslarae13 , @pkalita-lbl

[pkalita-lbl]

I think we need to narrow the scope of this request a bit. Namely:

1. Checking data in MongoDB
2. Checking data in the submission portal
3. Available from either a custom web page or a new endpoint in one of our APIs

Choose two of those three. There is no website or API service that has direct connections to both MongoDB and the submission portal database.

[mslarae13]

IMO, ideally, we'll have a way to generate a report of NMDC submissions that have been submitted, so are "submitted pending review" ... This report will allow me to review how someone has filled out a submitted submission's triad and evaluate it for approval.

This is 3rd priority after implementing the submission status framework which James is going to work on. So... for me, this issue is considered complete when

• We can query the NMDC submissions data (so submission portal postgres) for all submissions with status "submitted: pending review"
  • I am hoping we can leverage @eecavanna 's work on https://github.com/microbiomedata/nmdc-server/issues/1285
• Each submission fitting that criteria, data will be exported in the format seen here with the information needed for the env triad evaluation.
  • It doesn't need to be an excel file. It can be a .tsv or .csv
  • 2 options, I prefer the 1st, but if easier, the alternative is acceptable
• I have the ability to review & will respond via the new "submission status" capability for updates if needed.

Many of Mark's requests here can be stretch goals. If possible, let's do it! But I'm not sure how hard it'll be!

• term id portion of raw MIxS slot value, if accessible
  • I think this comes form what the user inputs into each of the 3 fields, it's just a matter of splitting it up (ex: name [ENV:111] where what is in brackets is what's here
• term label portion of raw MIxS slot value, if accessible
  • I think this comes form what the user inputs into each of the 3 fields, it's just a matter of splitting it up (ex: name [ENV:111] where the name is what's here
• is term id present in one of our trusted ontologies?
  • Requires querying the ontololy
• is term label present in one of our trusted ontologies?
  • TRUE / FALSE check based on term ID (the part in the [ ])
• do term id and term label match?
  • Requires querying the ontology
• is the term from a path in the ontology that makes sense for the combination of the environmental package and the MIxS slot identifier?
  • not sure... but seems hard?