Consider dropping `status` table for a dedicated type (or enum)

[ctron]

Currently we have a status table, which holds the following entries we ingest by default:

id	slug	name	description
85b912db-fc1b-4e75-8b27-68b68c0ed828	affected	Affected	Vulnerabililty affects
619aba21-abba-4220-9e3e-110cf87e5393	not_affected	Not Affected	Vulnerabililty does not affect
c0273e43-2b0c-4dae-a3b3-c4f9733fbfa7	fixed	Fixed	Vulnerabililty is fixed
23613500-86a4-4cdb-bc92-8c74e18764da	under_investigation	Under Investigation	Vulnerabililty is under investigation
2bb0325b-0948-44ea-bab7-46af9fc834eb	fixed	Fixed	Vulnerabililty is fixed
858a3f17-d864-4be8-932e-4a634de47b8b	recommended	Recommended	Vulnerabililty is fixed & recommended

The purl_status table then has a field of status_id, referencing an entry there. Which always requires a JOIN.

I would propose to just use a custom type, e.g. enum and only store the slug. Having all other variations (if required) in code.

[helio-frota]

+1

In addition to JOIN, we are going to the database to retrieve the values ​​from the table 👍

• Dashboard is hitting status table 10x

https://gist.github.com/helio-frota/f821493c83a2c62c248f321d5e2c738b

• We got performance improvements not going to database to get the status every time perf: Improves a bit the recommend endpoint #2183

[helio-frota]

I found these endpoints (+ dashboard and search page) using status:

[ I probably unintentionally skipped one or two ( or more 🤷‍♂️ ) during the process, but we can already get a sense of the positive impact ]

• https://gist.github.com/helio-frota/490e98201e27d0f36b813d4419fc10aa#post-apiv2vulnerabilityanalyze
• https://gist.github.com/helio-frota/490e98201e27d0f36b813d4419fc10aa#post-apiv3vulnerabilityanalyze
• https://gist.github.com/helio-frota/490e98201e27d0f36b813d4419fc10aa#get-apiv2sbomurnuuid019b0ed7-7157-7742-901e-13e7fddcc7b5advisory
• https://gist.github.com/helio-frota/490e98201e27d0f36b813d4419fc10aa#get-apiv2vulnerabilitycve-2025-9909
• https://gist.github.com/helio-frota/490e98201e27d0f36b813d4419fc10aa#post-apiv2purlrecommend
• https://gist.github.com/helio-frota/490e98201e27d0f36b813d4419fc10aa#post-apiv2advisorylabelsstring_value-upload-advisory
• https://gist.github.com/helio-frota/490e98201e27d0f36b813d4419fc10aa#post-apiv2datasetlabelsstring_value-upload-dataset
• https://gist.github.com/helio-frota/490e98201e27d0f36b813d4419fc10aa#dashboard
• https://gist.github.com/helio-frota/490e98201e27d0f36b813d4419fc10aa#search