Proposal for tracking timeline of vendor approvals

[howard-e]

Currently the structure of a viewer is included in TestPlanVersion.tests as tests[x].viewers where tests[x] is a test object which has a specific tests[x].at.key.

tests[x].viewers is an array of loosely structured "viewer" objects with the most recent structure being:

id (int)	User identifier
isBot (boolean)	Indicate whether the viewer is a bot user or not
roles (array)	Details on roles of viewer (ideally only `VENDOR` role makes any impact)
username (string)	The viewer's username
vendorId (int)	Identifier pointing to the viewer's affiliated company. Linked to `Vendor.id`
createdAt (timestamp)	Being pulled from the user's database `createdAt` timestamp (irrelevant to the results)
updatedAt (timestamp)	Being pulled from the user's database `updatedAt` timestamp (irrelevant to the results)

This being included as a part of the TestPlanVersion.tests makes processing and iterating over the viewers' information difficult. Additionally, to better represent the fact that reviewing results happens on the TestPlanReport, it would be better represented by directly linking it with that table.

Some considerations here around maintaining a proper timeline of approvals and reviews are the following:

• How can we determine what the first approved TestPlanReport for a TestPlanVersion was?
• How can we determine what the latest approved TestPlanReport for a TestPlanVersion was?
• If a test in an updated version has to be re-reviewed, which of the vendor(s) is this required by?
• How can it be made clear what the changes between TestPlanReport's versions are?

To better support these questions, the following structure proposal change is:

A new VendorApprovalStatus (TBD name) table with the following structure, where each row represents a view done by a User representing a Vendor:

userId (int) - PK FK	User identifier pointing to `User.id`
vendorId (int) - PK FK	Vendor identifier pointing to `Vendor.id`
testPlanReportId (int) - PK FK	Test Plan Report identifier pointing to `TestPlanReport.id`
testPlanVersionId (int) - FK	Test Plan Version identifier pointing to `TestPlanVersion.id`
reviewStatus (text)	One of 'IN_PROGRESS' or 'APPROVED' (if no row exists for a newly promoted `testPlanReportId`, we can consider that as being 'READY' for review to maintain the current copy
approvedAt (timestamp)	Only exists if `reviewStatus` is 'APPROVED' and last updated at the time `reviewStatus` was marked as 'APPROVED'

• This should make it possible for multiple reviews from a vendor be considered. This isn't the current situation but perhaps something that will be considered in the future and is already being implicitly done with the TestPlanVersion.tests approach.
• This will make it easier to derive a calculated timeline of TestPlanVersions' approvals based on when the linked TestPlanReports Viewer statuses were updated.
• By grouping the collection of like testPlanVersion ids, we should also be able to start deriving what the changes between versions were for their respective TestPlanReport's

These changes mean the following will happen:

• Removing TestPlanVersion.tests[x].viewers[]
• Removing TestPlanReport.vendorReviewStatus

Note: These changes are not exhaustive or final and still open to discussion