add a compliance_checks_resources table

[UlisesGascon]

Objective

In the spreadsheet we have multiple items for the columns (MITRE, Sources and How To). Originally we believed that only one item was included, so when we pull the data to build the website (see) the information collected only includes one item per row(see).

Ideally we can create a new table compliance_checks_resources and add this information linked to each compliance check, this change and population will be done using a population script (see)

Pending actions

Step1

• Create a new table compliance_checks_resources
• Populate the table with the data from the spreadsheet

Step2

• Remove the fields mitre_url, how_to_url and how_to_description from compliance_checks table
• Update the export-checks script (see) to query the data from both tables
• Create a PR in the website repository that update the populate-details script as now the data has a different shape for the templates (see)

Technical Notes

In the first version of the website you can find and script that was use to convert a dump html from Google spreadsheets to json, so you might save sometime to prepare the population data (ref)

[UlisesGascon]

Note: seems like some of the links are currently broken like https://github.com/cncf/tag-security/blob/main/supply-chain-security/supply-chain-security-paper/sscsp.md#build-and-related-continuous-integrationcontinuous-delivery-steps-should-all-be-automated-through-a-pipeline-defined-as-code that should be https://github.com/cncf/tag-security/blob/main/community/working-groups/supply-chain-security/supply-chain-security-paper/sscsp.md#build-and-related-continuous-integrationcontinuous-delivery-steps-should-all-be-automated-through-a-pipeline-defined-as-code. We need to check this also when doing the migration.

[telekosmos]

I could pick this one (oldest one with tag technical-debt) but, to be sure what I'm doing, are we calling resource to an entity with fields mitre_url, mitre_description, how_to_url, how_to_description, sources_url, sources_description ? what this resource actually mean? more in detail, what is (the meaning of) that mitre name? how these resources are linked to checks.json file in the website? I mean, if the resources are going to be in a table, that might be subject to change (add, delete, update), hence the check.json 🤔 ?

So many questions, but I'd like to know prior to start doing (silly) things 😄

[UlisesGascon]

Awesome! Thanks for the help offer, let me try to provide more context and try to help with the questions. BTW there are no silly questions ;)

are we calling resource to an entity with fields mitre_url, mitre_description, how_to_url, how_to_description, sources_url, sources_description ?

Yep.

what this resource actually mean?

Basically, a resource is any kind of URL + description that we can link to a specific check. This resource should be useful later on to provide more information to visionBoard users when trying to understand how that check works and how the mitigation works.

more in detail, what is (the meaning of) that mitre name?

Technically, MITRE is a non-profit organization, and what we are interested in on visionBoard are the CWE (Common Weakness Enumerations) references, like CWE-308: Use of Single-factor Authentication. These provide a lot of context on what CWE is linked to the specific check that we built. Not all checks have CWE references. We inherited this column name from the spreadsheet, but it makes sense to update it to something more aligned with cwe.

Note that one check can also be linked to multiple CWE(s).

how these resources are linked to checks.json file in the website?

This has changed a lot since I created this issue (now we have OpenPathfinder/openpathfinder.com as OpenPathfinder/website will be archived soon), but in general terms, we have an integration that clones this repo, and then runs the script db:export-checks, which generates a file called checks.json. This file is later used in the website to generate the check details pages using the script populate-checks.

Basically, that script uses the checks.json to create/update the check details (see).

At the end of the process, we have the details section populated with these resources (example):

if the resources are going to be in a table, that might be subject to change (add, delete, update), hence the check.json 🤔 ?

Yes, they are subject to change. But the most important thing is that the data shape will change from a plain object to an object that includes a list of resources, as now we might have multiple links for how_to, etc.

---

I will think that we can limit the scope of this ticket to include the changes in db:export-checks, and we can work on the website consumption in a different one. WDYT?

[telekosmos]

Thanks for the description @UlisesGascon , now I understand.
As you say above, if a resource is some entity with name, url and description (and type if we want), then I can figure out (right now) we need a table for the resources and another one to connect with the checks. I don't see a compliance_check_id as FK in the new compliance_check_resources table, unless we are very sure each resource will belong to a single check.