Proposed database structure for MVP candidate applications

This document is intended for the purpose of rapidly building the candidate application tool for 31 October to launch exercise 153. This documents the minimum viable Firestore database schema that I think would be needed for launch.

The schema would be formed of three database collections:

• /exercises/{exerciseID}
• /candidates/{userID}
• /applications/{applicationID}

/exercises/{exerciseID}

Candidates must be able to read these documents:

• this can be achieved with the get permission in Firestore security rules
• the list permission is not required here, as we don't need to display a list of all exercises in the collection
• the app will already know the ID of the exercise which the user is applying for, so there's not need to query (list) the collection

/candidates/{userID}

Candidates must be able to read & write to the document which has the same ID as their authenticated user ID:

• get can be used for read permissions
• create and update can be used for write permissions
• list and delete permissions should not be required
• candidates must not be able to read documents belonging to other candidates

Data to store in this document

The candidate document should contain data related to the 'Account profile' section of the application form:

• Personal details
• Character information
• Diversity information
• PAJE

The intention is that this data belongs to the candidate rather than to their application. When completing future applications, the candidate shouldn't need to re-populate these fields.

Security rules

The security rule for this collection could look something like this:

match /candidates/{userId} {
  // Users can read & write to their own candidate document
  allow get, create, update: if request.auth.uid == userId;
}

/applications/{applicationID}

Candidates must be able to create new documents, and read & write documents which they own in this collection.

Schema

Documents in this collection should have two 'reference' fields:

• candidate – reference to a document in collection /candidates. This field associates the application with the candidate which is submitting it. This is used to identify ownership of an application, and to enforce security rules so that candidates cannot edit applications belonging to others.
• exercise – reference to a document in collection /exercises. This field associates the application with the exercise for which it is being submitted.

A state field should also exist:

• The minimum required states to handle application submissions are: 'draft' and 'submitted'
• Applications should have a state of 'draft' until they've been submitted by candidates – at which point the state field should be changed to 'submitted'
• Once an application has state 'submitted', it should no longer be writable by the candidate.

An example record would include:

{
  candidate, // reference to the record belonging to the user in /candidates collection
  exercise, // reference to the document in /exercises collection
  state: 'draft' // either "draft" or "submitted"
}

Data to store in this document

The application document should contain data specifically related to the application for that exercise. This includes the 'Qualifications and experience' and 'Assessments' sections of the application form:

• Relevant qualifications
• Post-qualification work experience
• Judicial experience
• Independent assessor details
• Self-assessment competencies

These fields are specific to the application for the exercise, and should not be re-populated in the application form for other future exercises.

Security rules

Upon document creation:

• the field candidate should be populated with a reference to the current user's candidate document
• the field exercise should be populated with a reference to the current exercise document

Upon document read/write:

• security rules should enforce that the candidate field references the document which belongs to the currently authenticated user

The security rule for this section could look something like this:

match /applications/{applicationID} {
  function currentCandidate() {
    return path("/databases/" + database + "/documents/candidates/" + request.auth.uid);
  }

  // Allow existing records which belong to the current user
  allow get, list: if resource.data.candidate == currentCandidate();

  // Allow new records if they will belong to the current user
  allow create: if request.resource.data.candidate == currentCandidate();

  // Allow updates on records which belong, and will continue to belong, to the current user
  // But only if the application isn't in a 'submitted' state
  allow update: if resource.data.candidate == currentCandidate() &&
                   request.resource.data.candidate == currentCandidate() &&
                   resource.data.state != "submitted";
}

Questions

What should be done about old data in the production database?

There are numerous existing collections in the Firestore database:

• applications
• applicants (old name for 'candidates')
• references (alternate name for 'independent assessments')
• qualifying tests (multiple collections, all seem to be inter-related)

With the build of the new Vue applications ('Create a legal exercise' staff tool, and 'Apply' candidate tool), these pre-existing collections are no longer all relevant.

Application-related collections are related to exercise 128 Deputy District Judge which ran on the old Vue application form.

It's not obvious what data is stored relating to old qualifying tests. These collections were managed by the previous technical architect in the team, but weren't documented.

For all of these collections, it's unclear whether it's safe to delete the data or whether it should remain. However we will have clashes in the names of collections so would prefer not to mix old and new data, otherwise we risk making the database harder to maintain in the future.

What about security rules for the 'Create a legal exercise' staff tool?

The Firestore security rules proposed in this document support the access needs for candidates using the 'Apply' tool.

They do not cover the access needs of JAC staff using the 'Create a legal exercise' admin tool.

These are two distinctly different types of users with different data access needs. For example, JAC staff need to be able to write to the /exercises collection, whereas candidate users should have read-only access. Work will need to be done to reconcile the needs of both user types when implementing Firestore security rules.