Appraisal Data Mapping
Overview
This document maps each appraisal form field to the corresponding PPP database entity, schema table, and key columns. The "Gap?" column indicates whether the data is fully available, partially available, or missing entirely.
Schema source: packages-shared/ppp/src/schema-groups/
Complete Data Mapping Table
Section 1: Personal Details & Inputs
| Appraisal Field | PPP Entity | Schema Table | Key Columns | Gap? |
|---|---|---|---|---|
| Full name | User profile | users | firstName, lastName | No |
| User profile | users | email | No | |
| Phone number | User profile | users | phoneNumber | No |
| Date of birth | User profile | users | dob | No |
| Regulatory body | User profile | users | regulatoryBody | No |
| Registration number | User profile | users | registrationNumber | No |
| AHPRA number (AU) | User profile | users | ahpraNumber | No |
| UK GMC number | User profile | users | ukNumber | No |
| Country | User profile | users | country | No |
| Job title | User profile | users | jobTitle | No |
| Specialty | User profile → Speciality | users.specialityId → speciality | speciality.id, speciality.name | No |
| Post-graduate year | User profile | users | postGraduateYear | No |
| Scope of work | User profile | users | jobTitle (partial) | Partial — needs structured scope field |
| Employing hospital(s) | Hospital assignments | user_hospitals | hospitalId → hospitals | No |
| Educational supervisor | User relationships | users | educationalSupervisorId → users | No |
| Colleague appraiser | User relationships | users | colleagueAppraiserId → users | No |
| Additional supervisors | Supervisor assignments | user_supervisors | userId, supervisorId | No |
| Account type | User profile | users | accountAccess (cpd / ppp) | No |
| Profile picture | User profile | users | profilePicture | No |
| CV file | User profile | users | cvFile | No |
| Previous appraisal date | Appraisal history | NEW TABLE | — | Yes — requires appraisals table (Part 2) |
| Previous appraiser name | Appraisal history | NEW TABLE | — | Yes — requires appraisals table (Part 2) |
Section 2: Year in Review
| Appraisal Field | PPP Entity | Schema Table | Key Columns | Gap? |
|---|---|---|---|---|
| CPD plan status | CPD Plans | cpd_plans | status (draft / archived / locked), year | No |
| CPD plan EOY confirmed | CPD Plans | cpd_plans | confirmEoy (boolean) | No |
| High-level requirements | CPD Plans | cpd_plans | highLevelRequirements (jsonb) | No |
| EOY reflection | CPD End of Year | cpd_end_of_year | reflection (jsonb, mapped from attributes column) | No |
| EOY reflection date | CPD End of Year | cpd_end_of_year | date | No |
| EOY linked CPD plan | CPD End of Year | cpd_end_of_year | cpdPlanId → cpd_plans | No |
| Logbook case count | Activities | activities | COUNT(*) WHERE type='logbookCase' filtered by date range | No |
| Logbook procedure breakdown | Activities → Procedures | activities.procedureId → procedures | Grouped by procedure | No |
| Logbook hospital breakdown | Activities → Hospitals | activities.hospitalId → hospitals | Grouped by hospital | No |
| CPD plan activities | CPD Plan Activities | cpd_plan_activities | cpdPlanId, cpdActivityId, targetPoints | No |
| CPD plan classes | CPD Plan Classes | cpd_plan_classes | Linked via cpdPlanId | No |
Section 3: Well-being & Health
| Appraisal Field | PPP Entity | Schema Table | Key Columns | Gap? |
|---|---|---|---|---|
| WHO-5 score (latest) | Wellness Questionnaire | wellness_questionnaire | score (0–100), date | No |
| WHO-5 individual items | Wellness Questionnaire | wellness_questionnaire | cheerfulGoodSpirits, calmRelaxed, activeVigorous, freshRested, dailyLifeInterest (1–5 each) | No |
| WHO-5 trend | Wellness Questionnaire | wellness_questionnaire | Multiple rows over appraisal period | No |
| Readiness score | Wellness Score Daily | wellness_score_daily | readinessScore (0–1), readinessScoreDisplay (0–100) | No |
| Sleep guidance | Wellness Score Daily | wellness_score_daily | recommendedSleepHours, sleepGuidanceLevel, sleepGuidanceMessage | No |
| Sleep timing | Wellness Score Daily | wellness_score_daily | recommendedBedtime, wakeTarget (HH:MM) | No |
| Workload guidance | Wellness Score Daily | wellness_score_daily | workloadGuidanceLevel, workloadGuidanceMessage | No |
| Health metrics (daily) | Health Metric Daily | health_metric_daily | metricType, value, unit, source, date | No |
| Health metrics (raw) | Health Metric | health_metric | source, metricType, value, unit, startTime, endTime | No |
| Connected data sources | Data Source Connection | data_source_connection | source, status, lastSyncAt, dataTypes | No |
| Well-being narrative | — | — | — | Partial — can be AI-generated from metrics, or free-text (Part 5) |
Section 4: CPD Activities & Evidence
| Appraisal Field | PPP Entity | Schema Table | Key Columns | Gap? |
|---|---|---|---|---|
| Activity list | Activities | activities | id, type, date, status, userId | No |
| Activity type breakdown | Activities | activities | type enum: wba, feedback, reflection, auditEvent, logbookCase | No |
| WBA type breakdown | Activities | activities | wbaType enum: cex, cbd, pba, ssa, qmf, mcr | No |
| Activity attributes/details | Activities | activities | attributes (jsonb) | No |
| Assessor ratings | Activities | activities | conRatingLevel (float) | No |
| Case acuity | Activities | activities | caseAcuity | No |
| Patient demographics | Activities | activities | gender, dobPatient, patientCategory | No |
| Operative time | Activities | activities | operativeTime (float) | No |
| Hospital context | Activities → Hospitals | activities.hospitalId → hospitals | Hospital name, location | No |
| Procedure performed | Activities → Procedures | activities.procedureId → procedures | Procedure name, SNOMED code | No |
| Clinical setting | Activities → Settings | activities.settingId → settings | Setting name | No |
| CPD plan linkage | CPD Plan Activities | cpd_plan_activities | cpdPlanId, cpdActivityId, targetPoints, fieldGroup (jsonb) | No |
| Activity evidence files | Storage | Azure Blob Storage | Via activity record references | No |
| Imported cases | Activities | activities | isImported, importedAt, importNotes (jsonb) | No |
Section 5: MSF / 360 Reviews
| Appraisal Field | PPP Entity | Schema Table | Key Columns | Gap? |
|---|---|---|---|---|
| 360 review list | 360 Reviews | ppp_360 | id, name, date, status, userId | No |
| 360 review status | 360 Reviews | ppp_360 | status (e.g., supervisor_review, completed) | No |
| 360 supervisor assignment | 360 Reviews | ppp_360 | supervisorId → users | No |
| 360 supervisor sign-off | 360 Reviews | ppp_360 | supervisorSignOff (boolean), supervisorReviewDate, supervisorSummary | No |
| 360 reviewers | 360 Reviewers | ppp_360_reviewers | name, email, group, tokenConsumedAt | No |
| 360 reviewer reminders | 360 Reviewers | ppp_360_reviewers | lastReminderSentAt | No |
| 360 individual responses | 360 Reviews (responses) | ppp_360_reviews | question, answer, isComment, order | No |
| MCR assessor responses | MCR Assessor Responses | mcr_assessor_responses | activityId, assessorId, educational, attributes (jsonb) | No |
| MCR pending status | Activities + MCR | activities (type=wba, wbaType=mcr, status=pending) | Joined with mcr_assessor_responses | No |
| MSF/QMF activities | Activities | activities (type=wba, wbaType=qmf) | assessorId, status, attributes | No |
| Appraisal reminders sent | Notifications | notifications | type=appraisal_review_reminder, metadata (jsonb with appraisalType, appraisalId) | No |
| Related forms | Related Forms | related_forms | Linked activity forms | No |
Section 6: Workplace-Based Assessments (WBAs)
| Appraisal Field | PPP Entity | Schema Table | Key Columns | Gap? |
|---|---|---|---|---|
| CEX assessments | Activities | activities | type='wba', wbaType='cex' | No |
| CBD assessments | Activities | activities | type='wba', wbaType='cbd' | No |
| PBA assessments | Activities | activities | type='wba', wbaType='pba' | No |
| SSA assessments | Activities | activities | type='wba', wbaType='ssa' | No |
| Rating progression | Activities | activities | conRatingLevel over time, grouped by wbaType | No |
| Assessor details | Activities → Users | activities.assessorId → users | Assessor name, role | No |
| Feedback consultant flag | Activities | activities | feedbackConsultant (boolean) | No |
Section 7: Mandatory Training
| Appraisal Field | PPP Entity | Schema Table | Key Columns | Gap? |
|---|---|---|---|---|
| Training records | — | — | — | Yes — no training table exists |
| Compliance status | — | — | — | Yes — no compliance tracking |
| Training certificates | — | — | — | Yes — could use file attachments |
| Expiry dates | — | — | — | Yes — no expiry tracking |
Section 8: Personal Development Plan (PDP)
| Appraisal Field | PPP Entity | Schema Table | Key Columns | Gap? |
|---|---|---|---|---|
| PDP goals | — | NEW TABLE appraisal_pdp_goals | — | Yes — Part 2 |
| Goal progress | — | NEW TABLE appraisal_pdp_goals | — | Yes — Part 2 |
| Previous PDP review | — | NEW TABLE appraisals | — | Yes — Part 2 |
| AI-suggested goals | — | NEW (AI service) | aiGenerated flag | Yes — Part 5 |
| CPD plan overlap | CPD Plans | cpd_plans | highLevelRequirements (jsonb) | Partial — structured goals need new schema |
Section 9: Submission & Workflow
| Appraisal Field | PPP Entity | Schema Table | Key Columns | Gap? |
|---|---|---|---|---|
| Appraisal status | — | NEW TABLE appraisals | Status state machine | Yes — Part 2 |
| Pre-submission checklist | — | NEW TABLE appraisals | jsonb checklist field | Yes — Part 2 |
| Appraiser assignment | — | NEW TABLE appraisals | appraiserId → users | Yes — Part 2 |
| Appraiser summary | — | NEW TABLE appraisals | appraiserSummary text | Yes — Part 2 |
| Outcome | — | NEW TABLE appraisals | enum: satisfactory/unsatisfactory/deferred | Yes — Part 2 |
| Doctor sign-off | — | NEW TABLE appraisals | doctorSignedOffAt timestamp | Yes — Part 2 |
| Appraiser sign-off | — | NEW TABLE appraisals | appraiserSignedOffAt timestamp | Yes — Part 2 |
| PDF export | — | NEW SERVICE | — | Yes — Part 6 |
Gap Analysis Summary
Fully Available (No Gap)
| Count | Category |
|---|---|
| 18 | Personal details & profile |
| 11 | Year in review / CPD |
| 10 | Well-being & health metrics |
| 14 | CPD activities & evidence |
| 11 | MSF / 360 reviews |
| 7 | WBAs |
| 71 | Total fields with existing data |
Partial Gaps
| Field | Issue | Resolution |
|---|---|---|
| Scope of work | jobTitle exists but not structured scope | Add scopeOfWork text field to users or new appraisal profile |
| Well-being narrative | Metrics exist but no summary text | AI-generated narrative (Part 5) or free-text field |
| CPD → PDP overlap | High-level requirements exist but not structured goals | Map highLevelRequirements jsonb to PDP goals |
Full Gaps (New Schema Required)
| Section | Missing Entity | Proposed Table | Target Part |
|---|---|---|---|
| Personal Details | Previous appraisal history | appraisals | Part 2 (#2462) |
| Mandatory Training | Training records & compliance | mandatory_training (or LMS integration) | Future |
| PDP | Structured goals & progress | appraisal_pdp_goals | Part 2 (#2462) |
| Submission | Workflow state machine | appraisals (status field) | Part 2 (#2462) |
| Submission | PDF export | AppraisalPdfService | Part 6 |
Proposed New Schema (Part 2 Reference)
The following tables are proposed for Part 2 (#2462):
appraisals
id SERIAL PRIMARY KEY
userId INTEGER → users.id
appraiserId INTEGER → users.id (NULL)
year INTEGER
periodStart DATE
periodEnd DATE
status ENUM: draft, submitted, under_review, completed, deferred
outcome ENUM: satisfactory, unsatisfactory, deferred (NULL)
checklist JSONB
appraiserSummary TEXT (NULL)
doctorSignedOffAt TIMESTAMP (NULL)
appraiserSignedOffAt TIMESTAMP (NULL)
snapshotData JSONB -- frozen aggregated data at submission time
auditFields (createdAt, updatedAt, createdBy, updatedBy)appraisal_pdp_goals
id SERIAL PRIMARY KEY
appraisalId INTEGER → appraisals.id
title VARCHAR(500)
description TEXT
targetDate DATE (NULL)
status ENUM: not_started, in_progress, completed, carried_forward
aiGenerated BOOLEAN DEFAULT false
aiPromptUsed TEXT (NULL)
auditFields (createdAt, updatedAt, createdBy, updatedBy)These schemas will be formally defined in Drizzle ORM format in Part 2 (#2462).