ARIA AT App Database Update Proposal V2
Last Edited: July 10, 2023
This document describes proposal on a future implementation of the ARIA-AT App’s database, as at the last edited date. This includes information on the entities, their columns, relationships and any other relevant information or considerations.
Generally, the structure of the database remains the same with what has been described in the current database implementation document. What's mainly being targeted here is to solve how to have a more manageable way to move a defined Test Plan as the WM describes it. Other benefits of this might be allowing us to have more confidence in the reproducibility of displaying historic data when needed, and to move data to better defined tables, which should better enable the possibility of table extensions in the future, if needed.
Note that some aspects of this proposal are already underway to ensure that #518 and #648 is not blocked once it has been approved. But these pieces of work are still in review and up for discussion.
This is a newly added table. Do note that even though the name here represents what the WM calls a test plan, it isn’t the same. This is more synonymous with an example as pulled from the w3c/aria-at/tests folder. This represents a source of truth for what is the most up to date references for a Test Plan’s name and information in the application. It should dramatically reduce the need to derive test plan information unnecessarily during queries.
We’ve gone along with this name because of the context within the database and our current naming scheme. Other possible names would have been Example, Directory, Pattern or similar, but there may be additional utility to this table and the chance of it being extended further is possible. Additionally, when discussing a test plan in a public space, we don't expect it to be explicitly focused on this particular table.
Current issues that would benefit from this change include, but are not limited to w3c/aria-at-app#580 and w3c/aria-at-app#581. This change is currently under review in w3c/aria-at-app#620.
Columns
| id (int) - PK | Auto incrementing identifier |
|---|---|
| title (text) | The most recent title found for the test plan. This comes from the tests’ references.csv file |
| directory (text) | The most recent identifier name found for the test plan. This comes from the tests’ directory’s names |
The following columns have been removed from this table:
- status (text)
- candidateStatusReachedAt (timestamp)
- recommendedStatusTargetDate (timestamp)
- recommendedStatusReachedAt (timestamp)
These have been moved to the TestPlanVersion table. It is more logical to have it there as those attributes would apply to an entire Test Plan Version.
The following columns have been added to this table:
- markedReadyDate (timestamp)
Columns
| id (int) - PK | Auto incrementing identifier |
|---|---|
| testPlanVersionId (int) - FK | Foreign key identifier for a Test Plan Version (TestPlanVersion.id) |
| atId (int) - FK | Foreign key identifier for an assistive technology (At.id) |
| browserId (int) - FK | Foreign key identifier for a browser (Browser.id) |
| vendorReviewStatus (text) | An enumerated field which should only take IN_PROGRESS and READY, which represents a state of “readiness” when a test plan report has a CANDIDATE status |
| metrics (json) | An object holding calculated data such as, but not limited to the number of tests, the number of conflicts, the number of tests failed, passed, and more for the test plan report. Used for display purposes when applicable, to guarantee a faster page load |
| createdAt (timestamp) | The time the test plan report was created (added to the Test Queue) |
| markedReadyDate (timestamp) | The time the test plan report was marked as ready to be promoted from the Test Queue |
The following columns have been removed from this table:
- title (text)
- directory (text)
These can be referenced from the newly defined TestPlan table.
The following columns have been added to this table:
- testPlanId (int)
- phase (text)
- hashedTests (text)
- draftPhaseReachedAt (timestamp)
- candidatePhaseReachedAt (timestamp)
- recommendedPhaseTargetDate (timestamp)
- recommendedPhaseReachedAt (timestamp)
- archivedAt (timestamp)
Columns
| id (int) - PK | Auto incrementing identifier |
|---|---|
| testPlanId (int) - FK | Foreign key identifier for the test plan (TestPlan) |
| phase (text) | An enumerated field which should only take RD, DRAFT, CANDIDATE and RECOMMENDED, which represents different phases a test plan version can be in |
| gitSha (text) | The git sha of the default branch when the information of the test(s) was pulled from w3c/aria-at |
| gitMessage (text) | The git message of the default branch when the information of the test(s) was pulled from w3c/aria-at |
| testPageUrl (text) | Generated url for the tests found in the w3c/aria-at/tests/*pattern*/reference folder. The application opens this link through a proxy when needing to view a test from the application (such as with the Open Test button found on the Test Run page) |
| hashedTests (text) | Used to uniquely identify tests for a Test Plan Version. A huge benefit of this is not having to capture every test plan unnecessarily with no change, when a new "version" of the tests are imported. This would address #579 and is currently under review at #621 |
| tests (json) | An object containing information on all tests related to the test plan version for the referenced pattern |
| metadata (json) | An object containing information with other miscellaneous data found in the references.csv |
| updatedAt (timestamp) | Time when the test plan version was imported. This can also be thought of as having the Research and Development phase being completed (RD) |
| draftPhaseReachedAt (timestamp) | Time when the test plan version was promoted to DRAFT (when it can first have runs being added to the Test Queue) |
| candidatePhaseReachedAt (timestamp) | The time the test plan version was promoted to CANDIDATE |
| recommendedPhaseTargetDate (timestamp) | The time the test plan version should be promoted to RECOMMENDED. This is calculated based on the candidatePhaseReachedAt and the 180 days described in the WM for the recommended time the Test Plan should remain in the CANDIDATE phase. This value can be manually updated |
| recommendedPhaseReachedAt (timestamp) | The time the test plan version was promoted to RECOMMENDED |
| archivedAt (timestamp) | The time the test plan version was archived (or sunsetted) |
- One-to-many with TestPlanVersion
- Many-to-one with TestPlanVersion
- Many-to-one with TestPlan
- One-to-many with TestPlanReport
-
Based on previous discussions (though not on the critical path), there could be some benefit in tracking the actions happening throughout the system; this could help for reverting changes, accountability checks or just some general reporting use case. The granularity of what events could or should be tracked is another discussion.
-
What’s been proposed is a table called
Audit. The columns would be:-
id(int) -
event(text) -
metadata(json) -
eventDate(timestamp)
-
Matt King last said in #632: Definitely agree that an events table could be very useful. We would probably need a separate table defining the types of events, e.g., eventTypes.
-