Provide unique IDs for items retrieved with list queries

[Etsija]

In front-end, we have a need to create dynamic parametrized routes for the details of items retrieved from back-end, for example rule violations, vulnerabilities and issues. This will give us the opportunity to dedicate more informative and visual sub-pages to inspect the details of these items, instead of just showing the details as part of the main table, as we have done for vulnerabilities and rule violations so far.

For construction of these parametrized routes, we need from back-end an id for each item, which is unique in some related context. This context can vary; for example, ortRunId is not globally unique, but it is unique within a repository, so we are using it when constructing a list of ORT runs for a repository.

As many (if not all) database tables in ORT Server contain a unique Id of type bigint for each item in a table, it would be extremely convenient for us if the back-end would return those ids as part of the items returned in a list query.

[mnonnenmacher]

The main problem with providing unique ids is that the query endpoints do not always map directly to a single table. Instead, the SQL queries behind the endpoints could join multiple tables, making it not obvious which id could be used and risking that ids are not unique. Some ideas how this could be addressed:

• Composite keys: Combine the unique ids of the involved tables in a format like table1ID-table2Id-....
• Hash keys: Generate a hash from the id columns of the involved tables.

Some requirements to consider:

• Do the generated ids have to be globally unique?
• Do the generated ids have to be stable across multiple requests with different settings (sorting, filtering)?