Skip to content

PPP360 Database Schema Overview ​

Table of Contents ​

Introduction ​

The PPP360 database schema is designed to support 360 feedback reviews within the Professional Performance Portfolio (PPP) system. It enables the storage, retrieval, and management of comprehensive feedback data collected from multiple reviewers about medical professionals. The schema follows a hierarchical structure with three primary tables that store reviews, reviewer information, and their responses respectively.

Schema Structure ​

The PPP360 schema consists of three interconnected tables:

ppp360/
├── ppp360                  # Main 360 review records
├── ppp360reviewers         # Reviewers associated with each 360 review
└── ppp360reviews           # Individual review responses from reviewers

Entity Relationships ​

Schema Details ​

ppp360 Table ​

The ppp360 table represents the main 360 review record created by a user who wishes to collect feedback.

Fields ​

FieldTypeDescriptionConstraints
idintegerPrimary key identifierPK, Generated
datetimestampDate of the 360 review
namevarcharName of the 360 review (typically format: "360 Review - Month YYYY")length: 255
statusvarcharCurrent status of the review (draft, pending, completed)length: 255
userIdintegerID of the user who created the reviewFK to users.id
createdAttimestampTimestamp when the record was createdAudit field
updatedAttimestampTimestamp when the record was last updatedAudit field
createdByintegerID of the user who created the recordAudit field
updatedByintegerID of the user who last updated the recordAudit field

Relationships ​

  • One-to-Many with ppp360reviewers: A 360 review has multiple reviewers
  • Many-to-One with users: A 360 review is created by one user

Business Purpose ​

The ppp360 table serves as the main record for a 360 review assessment. It tracks the date, name, and current status of the review process. The status field transitions through various states as the review progresses from creation to completion.

ppp360reviewers Table ​

The ppp360reviewers table stores information about individuals who are invited to provide feedback on a medical professional as part of a 360 review.

Fields ​

FieldTypeDescriptionConstraints
idintegerPrimary key identifierPK, Generated
ppp360IdintegerID of the 360 review this reviewer is associated withFK to ppp360.id
namevarcharName of the reviewerlength: 255, Not Null
emailvarcharEmail address of the reviewerlength: 255, Not Null
groupvarcharGroup classification (Medical Doctor, Medical Non-Doctor, or Non-Medical)length: 255, Not Null
createdAttimestampTimestamp when the record was createdAudit field
updatedAttimestampTimestamp when the record was last updatedAudit field
createdByintegerID of the user who created the recordAudit field
updatedByintegerID of the user who last updated the recordAudit field

Relationships ​

  • Many-to-One with ppp360: A reviewer is associated with one 360 review
  • One-to-Many with ppp360reviews: A reviewer provides multiple review answers

Business Purpose ​

The ppp360reviewers table maintains a record of each person invited to contribute to a 360 review. The group field categorizes reviewers into Medical Doctor, Medical Non-Doctor, or Non-Medical classifications, which is important for analyzing feedback patterns and ensuring proper representation from different stakeholder groups.

ppp360reviews Table ​

The ppp360reviews table contains the actual feedback responses provided by reviewers, including ratings and comments.

Fields ​

FieldTypeDescriptionConstraints
idintegerPrimary key identifierPK, Generated
ppp360reviewerIdintegerID of the reviewer who provided this feedbackFK to ppp360reviewers.id
questionvarcharText of the question being answeredlength: 255
answervarcharReviewer's response (rating value or comment text)length: 255
isCommentbooleanIndicates if this is a comment field (true) or rating (false)Default: false
orderintegerNumerical order/position of the question
createdAttimestampTimestamp when the record was createdAudit field
updatedAttimestampTimestamp when the record was last updatedAudit field
createdByintegerID of the user who created the recordAudit field
updatedByintegerID of the user who last updated the recordAudit field

Relationships ​

  • Many-to-One with ppp360reviewers: Many review responses are provided by one reviewer

Business Purpose ​

The ppp360reviews table stores individual responses to assessment questions. Each reviewer typically answers multiple standardized questions (where isComment=false) using a numerical rating scale, plus provides narrative feedback (where isComment=true). The order field enables proper sequencing of questions when retrieving the data.

Data Flow ​

The data flow within the PPP360 schema follows this general pattern:

  1. Creation Phase: A new record is created in the ppp360 table with status "draft" and associated metadata (date, name)
  2. Reviewer Assignment: Multiple reviewer records are created in the ppp360reviewers table, linked to the 360 review
  3. Feedback Collection: As reviewers provide feedback, responses are stored in the ppp360reviews table
  4. Status Progression: The 360 review record's status field is updated as it moves through its lifecycle:
    • draft: Initial state when first created
    • pending: When reviewers have been assigned and feedback collection is in progress
    • completed: When all required feedback has been collected

Technical Implementation ​

The schema is implemented using Drizzle ORM with PostgreSQL in the TypeScript codebase, featuring:

  • PostgreSQL Tables: Defined with appropriate types, constraints, and references
  • Relations Mapping: Establishes bi-directional relationships between tables
  • Audit Fields: Standard audit trail fields (createdAt, updatedAt, createdBy, updatedBy) on all tables
  • Cascading Deletes: Configured to maintain referential integrity (e.g., deleting a 360 review cascades to its reviewers and their responses)
  • Foreign Key Constraints: Enforces proper relationships between tables

Business Logic ​

360 Review Lifecycle ​

  1. Creation: A 360 review begins with a professional creating a new record with an initial "draft" status.
  2. Reviewer Selection: The review creator designates a set of reviewers (typically 5) from various professional groups to provide balanced feedback.
  3. Feedback Collection: Each reviewer submits responses to a standardized set of questions covering:
    • Technical Skills
    • Communication Skills
    • Collaboration
    • Management and Leadership
    • Health Advocacy
    • Scholar and Teacher
    • Professionalism
  4. Completion: Once all reviewers have submitted their feedback, the review status is updated to "completed".
  5. Analysis: The aggregated feedback can be analyzed by question, by reviewer group, or across multiple reviews for longitudinal assessment.

Data Constraints and Rules ​

  1. Reviewer Requirements: Each 360 review must have at least one reviewer, but typically has five reviewers.
  2. Group Distribution: Reviewers should include representatives from both Medical and Non-Medical groups to ensure comprehensive feedback.
  3. Question Standardization: A standardized set of 16 assessment questions plus one comments section ensures consistency across reviews.
  4. Rating Scale: Numerical ratings follow a standardized 5-point scale:
    • 1: Unsatisfactory (Below expectations)
    • 2: Borderline
    • 3: Satisfactory (Demonstrates competence)
    • 4: Proficient
    • 5: Outstanding
    • UE: Unable to evaluate (represented as "0" in the database)
  5. Status Transitions: Reviews follow a defined progression:
    • draft → pending → completed
    • A review cannot be completed until all assigned reviewers have provided feedback

Internal documentation — org members only