Skip to content

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 FieldPPP EntitySchema TableKey ColumnsGap?
Full nameUser profileusersfirstName, lastNameNo
EmailUser profileusersemailNo
Phone numberUser profileusersphoneNumberNo
Date of birthUser profileusersdobNo
Regulatory bodyUser profileusersregulatoryBodyNo
Registration numberUser profileusersregistrationNumberNo
AHPRA number (AU)User profileusersahpraNumberNo
UK GMC numberUser profileusersukNumberNo
CountryUser profileuserscountryNo
Job titleUser profileusersjobTitleNo
SpecialtyUser profile → Specialityusers.specialityId → specialityspeciality.id, speciality.nameNo
Post-graduate yearUser profileuserspostGraduateYearNo
Scope of workUser profileusersjobTitle (partial)Partial — needs structured scope field
Employing hospital(s)Hospital assignmentsuser_hospitalshospitalId → hospitalsNo
Educational supervisorUser relationshipsuserseducationalSupervisorId → usersNo
Colleague appraiserUser relationshipsuserscolleagueAppraiserId → usersNo
Additional supervisorsSupervisor assignmentsuser_supervisorsuserId, supervisorIdNo
Account typeUser profileusersaccountAccess (cpd / ppp)No
Profile pictureUser profileusersprofilePictureNo
CV fileUser profileuserscvFileNo
Previous appraisal dateAppraisal historyNEW TABLE—Yes — requires appraisals table (Part 2)
Previous appraiser nameAppraisal historyNEW TABLE—Yes — requires appraisals table (Part 2)

Section 2: Year in Review ​

Appraisal FieldPPP EntitySchema TableKey ColumnsGap?
CPD plan statusCPD Planscpd_plansstatus (draft / archived / locked), yearNo
CPD plan EOY confirmedCPD Planscpd_plansconfirmEoy (boolean)No
High-level requirementsCPD Planscpd_planshighLevelRequirements (jsonb)No
EOY reflectionCPD End of Yearcpd_end_of_yearreflection (jsonb, mapped from attributes column)No
EOY reflection dateCPD End of Yearcpd_end_of_yeardateNo
EOY linked CPD planCPD End of Yearcpd_end_of_yearcpdPlanId → cpd_plansNo
Logbook case countActivitiesactivitiesCOUNT(*) WHERE type='logbookCase' filtered by date rangeNo
Logbook procedure breakdownActivities → Proceduresactivities.procedureId → proceduresGrouped by procedureNo
Logbook hospital breakdownActivities → Hospitalsactivities.hospitalId → hospitalsGrouped by hospitalNo
CPD plan activitiesCPD Plan Activitiescpd_plan_activitiescpdPlanId, cpdActivityId, targetPointsNo
CPD plan classesCPD Plan Classescpd_plan_classesLinked via cpdPlanIdNo

Section 3: Well-being & Health ​

Appraisal FieldPPP EntitySchema TableKey ColumnsGap?
WHO-5 score (latest)Wellness Questionnairewellness_questionnairescore (0–100), dateNo
WHO-5 individual itemsWellness Questionnairewellness_questionnairecheerfulGoodSpirits, calmRelaxed, activeVigorous, freshRested, dailyLifeInterest (1–5 each)No
WHO-5 trendWellness Questionnairewellness_questionnaireMultiple rows over appraisal periodNo
Readiness scoreWellness Score Dailywellness_score_dailyreadinessScore (0–1), readinessScoreDisplay (0–100)No
Sleep guidanceWellness Score Dailywellness_score_dailyrecommendedSleepHours, sleepGuidanceLevel, sleepGuidanceMessageNo
Sleep timingWellness Score Dailywellness_score_dailyrecommendedBedtime, wakeTarget (HH:MM)No
Workload guidanceWellness Score Dailywellness_score_dailyworkloadGuidanceLevel, workloadGuidanceMessageNo
Health metrics (daily)Health Metric Dailyhealth_metric_dailymetricType, value, unit, source, dateNo
Health metrics (raw)Health Metrichealth_metricsource, metricType, value, unit, startTime, endTimeNo
Connected data sourcesData Source Connectiondata_source_connectionsource, status, lastSyncAt, dataTypesNo
Well-being narrative———Partial — can be AI-generated from metrics, or free-text (Part 5)

Section 4: CPD Activities & Evidence ​

Appraisal FieldPPP EntitySchema TableKey ColumnsGap?
Activity listActivitiesactivitiesid, type, date, status, userIdNo
Activity type breakdownActivitiesactivitiestype enum: wba, feedback, reflection, auditEvent, logbookCaseNo
WBA type breakdownActivitiesactivitieswbaType enum: cex, cbd, pba, ssa, qmf, mcrNo
Activity attributes/detailsActivitiesactivitiesattributes (jsonb)No
Assessor ratingsActivitiesactivitiesconRatingLevel (float)No
Case acuityActivitiesactivitiescaseAcuityNo
Patient demographicsActivitiesactivitiesgender, dobPatient, patientCategoryNo
Operative timeActivitiesactivitiesoperativeTime (float)No
Hospital contextActivities → Hospitalsactivities.hospitalId → hospitalsHospital name, locationNo
Procedure performedActivities → Proceduresactivities.procedureId → proceduresProcedure name, SNOMED codeNo
Clinical settingActivities → Settingsactivities.settingId → settingsSetting nameNo
CPD plan linkageCPD Plan Activitiescpd_plan_activitiescpdPlanId, cpdActivityId, targetPoints, fieldGroup (jsonb)No
Activity evidence filesStorageAzure Blob StorageVia activity record referencesNo
Imported casesActivitiesactivitiesisImported, importedAt, importNotes (jsonb)No

Section 5: MSF / 360 Reviews ​

Appraisal FieldPPP EntitySchema TableKey ColumnsGap?
360 review list360 Reviewsppp_360id, name, date, status, userIdNo
360 review status360 Reviewsppp_360status (e.g., supervisor_review, completed)No
360 supervisor assignment360 Reviewsppp_360supervisorId → usersNo
360 supervisor sign-off360 Reviewsppp_360supervisorSignOff (boolean), supervisorReviewDate, supervisorSummaryNo
360 reviewers360 Reviewersppp_360_reviewersname, email, group, tokenConsumedAtNo
360 reviewer reminders360 Reviewersppp_360_reviewerslastReminderSentAtNo
360 individual responses360 Reviews (responses)ppp_360_reviewsquestion, answer, isComment, orderNo
MCR assessor responsesMCR Assessor Responsesmcr_assessor_responsesactivityId, assessorId, educational, attributes (jsonb)No
MCR pending statusActivities + MCRactivities (type=wba, wbaType=mcr, status=pending)Joined with mcr_assessor_responsesNo
MSF/QMF activitiesActivitiesactivities (type=wba, wbaType=qmf)assessorId, status, attributesNo
Appraisal reminders sentNotificationsnotificationstype=appraisal_review_reminder, metadata (jsonb with appraisalType, appraisalId)No
Related formsRelated Formsrelated_formsLinked activity formsNo

Section 6: Workplace-Based Assessments (WBAs) ​

Appraisal FieldPPP EntitySchema TableKey ColumnsGap?
CEX assessmentsActivitiesactivitiestype='wba', wbaType='cex'No
CBD assessmentsActivitiesactivitiestype='wba', wbaType='cbd'No
PBA assessmentsActivitiesactivitiestype='wba', wbaType='pba'No
SSA assessmentsActivitiesactivitiestype='wba', wbaType='ssa'No
Rating progressionActivitiesactivitiesconRatingLevel over time, grouped by wbaTypeNo
Assessor detailsActivities → Usersactivities.assessorId → usersAssessor name, roleNo
Feedback consultant flagActivitiesactivitiesfeedbackConsultant (boolean)No

Section 7: Mandatory Training ​

Appraisal FieldPPP EntitySchema TableKey ColumnsGap?
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 FieldPPP EntitySchema TableKey ColumnsGap?
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 flagYes — Part 5
CPD plan overlapCPD Planscpd_planshighLevelRequirements (jsonb)Partial — structured goals need new schema

Section 9: Submission & Workflow ​

Appraisal FieldPPP EntitySchema TableKey ColumnsGap?
Appraisal status—NEW TABLE appraisalsStatus state machineYes — Part 2
Pre-submission checklist—NEW TABLE appraisalsjsonb checklist fieldYes — Part 2
Appraiser assignment—NEW TABLE appraisalsappraiserId → usersYes — Part 2
Appraiser summary—NEW TABLE appraisalsappraiserSummary textYes — Part 2
Outcome—NEW TABLE appraisalsenum: satisfactory/unsatisfactory/deferredYes — Part 2
Doctor sign-off—NEW TABLE appraisalsdoctorSignedOffAt timestampYes — Part 2
Appraiser sign-off—NEW TABLE appraisalsappraiserSignedOffAt timestampYes — Part 2
PDF export—NEW SERVICE—Yes — Part 6

Gap Analysis Summary ​

Fully Available (No Gap) ​

CountCategory
18Personal details & profile
11Year in review / CPD
10Well-being & health metrics
14CPD activities & evidence
11MSF / 360 reviews
7WBAs
71Total fields with existing data

Partial Gaps ​

FieldIssueResolution
Scope of workjobTitle exists but not structured scopeAdd scopeOfWork text field to users or new appraisal profile
Well-being narrativeMetrics exist but no summary textAI-generated narrative (Part 5) or free-text field
CPD → PDP overlapHigh-level requirements exist but not structured goalsMap highLevelRequirements jsonb to PDP goals

Full Gaps (New Schema Required) ​

SectionMissing EntityProposed TableTarget Part
Personal DetailsPrevious appraisal historyappraisalsPart 2 (#2462)
Mandatory TrainingTraining records & compliancemandatory_training (or LMS integration)Future
PDPStructured goals & progressappraisal_pdp_goalsPart 2 (#2462)
SubmissionWorkflow state machineappraisals (status field)Part 2 (#2462)
SubmissionPDF exportAppraisalPdfServicePart 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).

Internal documentation — org members only