Table of Contents

Crewing Data Model

1. Enums
2. Rank — the org hierarchy
3. Entity-relationship diagram
4. Model notes
5. Relationship to existing models

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

Crewing Data Model

Authoritative build spec: Crewing Implementation Spec carries the full reconciled instructions (codebase changes, roles/nav matrices, screen spec). This page is the detailed schema, aligned with that spec.

Proposed source of truth: App/prisma/schema.prisma (new models). Mirrors the conventions on Data Model: monetary values are Decimal, IDs are cuid(), every lifecycle change writes an audit row.

1. Enums

// who/what kind of vacancy
enum RequisitionReason   { LEAVE  END_OF_CONTRACT  TERMINATION  MEDICAL  OTHER }
enum RequisitionStatus   { OPEN  SHORTLISTING  PROPOSING  INTERVIEWING  SELECTED  FILLED  CANCELLED }

// candidate progress against a requisition (the gated pipeline)
enum CandidateType       { EX_HAND  NEW }
enum ApplicationStage {
  SHORTLISTED  COMPETENCY_AND_REFERENCES  DOC_VERIFICATION  SALARY_AGREEMENT
  PROPOSED  INTERVIEW  SELECTED  REJECTED  ONBOARDED
}
// the 7 board stages are SHORTLISTED → COMPETENCY_AND_REFERENCES → DOC_VERIFICATION
// → SALARY_AGREEMENT → PROPOSED → INTERVIEW → SELECTED; ONBOARDED is the terminal
// system state, REJECTED the branch. Joining formalities fold into the onboard action.

// the person's overall status
enum CrewStatus          { PROSPECT  CANDIDATE  EMPLOYEE  EX_HAND  BLACKLISTED }

// an onboarded tour of duty
enum AssignmentStatus    { ACTIVE  ON_LEAVE  SIGNED_OFF }

// site HR
enum AttendanceStatus    { PRESENT  ABSENT  HALF_DAY  ON_LEAVE  SIGN_OFF }
enum LeaveType           { ANNUAL  MEDICAL  EMERGENCY  UNPAID  OTHER }
enum LeaveStatus         { APPLIED  APPROVED  REJECTED  CANCELLED }   // decided by the Manager
enum ReliefRequestStatus { OPEN  CONVERTED  DISMISSED }              // site-staff gap flag → office converts

// PPE kit
enum PpeItem {
  BOILER_SUIT  SAFETY_SHOES  HELMET  VEST  GLOVES  MASK  GOGGLES
  TIFFIN  TORCH  WALKIE_TALKIE
}

// documents
enum SeafarerDocType {
  STCW  AADHAAR  PAN  PASSPORT  CDC  COC  PHOTOGRAPH
  DRIVING_LICENSE  MEDICAL_FITNESS  CONTRACT_LETTER
}
enum VerificationStatus  { PENDING  VERIFIED  REJECTED  EXPIRED }

// appraisal + payroll
enum AppraisalStatus     { DRAFT  SUBMITTED  MPO_VERIFIED  MANAGER_APPROVED  REJECTED }
enum WageReportStatus    { DRAFT  GENERATED  MANAGER_APPROVED  SENT_TO_ACCOUNTS }

// audit
enum CrewActionType {
  REQUISITION_RAISED  REQUISITION_CANCELLED  REQUISITION_FILLED
  CANDIDATE_SHORTLISTED  GATE_PASSED  GATE_FAILED  PROPOSED  INTERVIEWED
  SELECTED  REJECTED  ONBOARDED  SIGNED_OFF
  RECORD_UPDATED  RECORD_VERIFIED  RECORD_REJECTED
  PPE_ISSUED  PPE_RETURNED  LEAVE_APPLIED  LEAVE_DECIDED
  APPRAISAL_SUBMITTED  APPRAISAL_VERIFIED  APPRAISAL_APPROVED
  WAGE_REPORT_GENERATED  WAGE_REPORT_APPROVED  WAGE_REPORT_SENT
}

2. Rank — the org hierarchy

Rank is reference data with a self-relation (parent/children), exactly like the 3-level Account accounting-code hierarchy. It drives requisition criteria, required documents per rank, and the org chart. Captured from Crewing.excalidraw:

flowchart TB
  PM["PM"] --> APM["Ass. PM"]
  APM --> ACC["Accountant"]
  APM --> DRV["Driver"]
  APM --> COOK["Cook"]
  COOK --> CH["Cook Helper"]
  APM --> SIC["Site in-charge"]
  SIC --> DIC["Dredger in-charge"]
  DIC --> SDO["Sr. Dredge Op."]
  SDO --> PLS["Pipeline Supervisor"]
  PLS --> PLA["Pipeline Ass."]
  SDO --> JDO["Jr. Dredge Op."]
  JDO --> ERO["Engine Room Op."]
  ERO --> DH["Deck Hand"]
  DH --> TR["Trainee"]
  DH --> MB["Mess Boy"]
  SDO --> ELE["Electrician"]
  SDO --> FAB["Sr. Fab"]
  FAB --> FW["Fab / Welder"]

  classDef sup fill:#eef,stroke:#88a;
  class ACC,DRV,COOK,CH sup;

Ranks carry a category (e.g. OPERATIONAL vs SUPPORT) and an isSeafarer flag (drives which document set applies). Driver additionally requires a driving licence (RankDocRequirement).

Login boundary — the rank tree is an org chart, not a login hierarchy. Only PM, Assistant PM and Site In-charge map to a portal login (the SITE_STAFF role). Every other rank in this tree (Dredger in-charge → … → Mess Boy, plus support staff) is a crew member / data subject with no login — site staff record their leave, attendance, PPE and documents on their behalf. A Rank therefore has a flag such as grantsLogin (true only for the three management ranks); a CrewMember becomes a User only when its rank grants a login.

3. Entity-relationship diagram

erDiagram
  USER ||--o{ CREW_ACTION : actor
  USER ||--o{ REQUISITION : raised_by
  USER ||--o{ APPRAISAL : "added/verified/approved"

  RANK ||--o{ RANK : parent
  RANK ||--o{ REQUISITION : for_rank
  RANK ||--o{ CREW_ASSIGNMENT : in_rank
  RANK ||--o{ RANK_DOC_REQUIREMENT : requires

  VESSEL ||--o{ REQUISITION : cost_centre
  VESSEL ||--o{ CREW_ASSIGNMENT : on
  SITE   ||--o{ WAGE_REPORT : for_site

  REQUISITION ||--o{ APPLICATION : receives
  REQUISITION ||--o| CREW_ASSIGNMENT : filled_by
  REQUISITION }o--|| CREW_MEMBER : vacated_by

  CREW_MEMBER ||--o{ APPLICATION : applies
  CREW_MEMBER ||--o{ CREW_ASSIGNMENT : has
  CREW_MEMBER ||--o{ SEAFARER_DOCUMENT : holds
  CREW_MEMBER ||--o| BANK_DETAIL : has
  CREW_MEMBER ||--o| EPF_DETAIL : has
  CREW_MEMBER ||--o{ NEXT_OF_KIN : has
  CREW_MEMBER ||--o{ EMERGENCY_CONTACT : has
  CREW_MEMBER ||--o{ EXPERIENCE_RECORD : has

  APPLICATION ||--o{ APPLICATION_GATE : evaluated_by
  APPLICATION ||--o{ REFERENCE_CHECK : has

  CREW_ASSIGNMENT ||--o{ SALARY_STRUCTURE : paid_by
  CREW_ASSIGNMENT ||--o| CONTRACT_LETTER : documented_by
  CREW_ASSIGNMENT ||--o{ ATTENDANCE : records
  CREW_ASSIGNMENT ||--o{ LEAVE_REQUEST : has
  CREW_ASSIGNMENT ||--o{ PPE_ISSUE : issued
  CREW_ASSIGNMENT ||--o{ APPRAISAL : appraised
  CREW_ASSIGNMENT ||--o{ WAGE_LINE : billed

  WAGE_REPORT ||--o{ WAGE_LINE : contains

  CREW_MEMBER {
    string id PK
    string employeeId "unique, set at onboard"
    string name
    date   dob
    string phone
    string email
    enum   status "CrewStatus"
    enum   type "EX_HAND|NEW"
    string cvKey "storage key"
    string currentRankId FK
  }
  REQUISITION {
    string id PK
    string vesselId FK
    string rankId FK
    enum   reason "RequisitionReason"
    enum   status "RequisitionStatus"
    string vacatedByCrewId FK
    int    minExperienceMonths
    string vesselTypeCriteria
    string raisedById FK
    bool   autoRaised
    date   neededBy
  }
  APPLICATION {
    string id PK
    string requisitionId FK
    string candidateId FK
    enum   type "CandidateType"
    enum   stage "ApplicationStage"
    decimal proposedSalary
    string remarks
    bool   interviewWaived
  }
  APPLICATION_GATE {
    string id PK
    string applicationId FK
    string gate "competency_reference|document|experience|salary|interview"
    enum   result "PENDING|VERIFIED|REJECTED"
    string note
    string decidedById FK
  }
  CREW_ASSIGNMENT {
    string id PK
    string crewMemberId FK
    string vesselId FK
    string rankId FK
    string requisitionId FK
    date   signOnDate
    date   signOffDate
    enum   status "AssignmentStatus"
  }
  SALARY_STRUCTURE {
    string id PK
    string assignmentId FK
    enum   rateBasis "MONTHLY|DAILY (the other is derived)"
    decimal basic
    decimal victualingPerDay
    json   allowances
    string currency
    date   effectiveFrom "effective-dated: many per assignment"
    date   effectiveTo "null = current"
    string approvedById FK "Manager"
  }
  CONTRACT_LETTER {
    string id PK
    string assignmentId FK
    string fileKey
    bool   salaryRestricted "hide salary from site staff"
  }
  ATTENDANCE {
    string id PK
    string assignmentId FK
    date   date
    enum   status "AttendanceStatus"
    string recordedById FK "site staff; visible to site + Manager, not MPO"
  }
  LEAVE_REQUEST {
    string id PK
    string assignmentId FK
    enum   type "LeaveType"
    date   fromDate
    date   toDate
    enum   status "LeaveStatus"
    string appliedById FK "Site in-charge (applies for crew)"
    string decidedById FK "Manager (approves/rejects)"
  }
  RELIEF_REQUEST {
    string id PK
    string vesselId FK
    string rankId FK
    string reason
    enum   status "ReliefRequestStatus"
    string requestedById FK "site staff (flags a foreseen gap)"
    string requisitionId FK "set when the office converts it"
  }
  PPE_ISSUE {
    string id PK
    string assignmentId FK
    enum   item "PpeItem"
    string size "for boiler suit / shoes"
    int    quantity
    date   issuedDate
    date   returnedDate
    string issuedById FK
  }
  SEAFARER_DOCUMENT {
    string id PK
    string crewMemberId FK
    enum   docType "SeafarerDocType"
    string number "masked"
    string fileKey
    date   issueDate
    date   expiryDate
    enum   verificationStatus "VerificationStatus"
    string verifiedById FK "MPO"
  }
  BANK_DETAIL {
    string id PK
    string crewMemberId FK
    string accountName
    string accountNumberEnc "encrypted"
    string ifsc
    string bankName
    enum   verificationStatus "by Accounts"
    string verifiedById FK
  }
  EPF_DETAIL {
    string id PK
    string crewMemberId FK
    string uan
    string aadhaarLast4
    string pfNumber
    enum   verificationStatus "EPFO check by Accounts"
    string verifiedById FK
  }
  EXPERIENCE_RECORD {
    string id PK
    string crewMemberId FK
    string vesselType
    string rankId FK
    date   fromDate
    date   toDate
    int    durationMonths
    string source "internal|declared"
  }
  APPRAISAL {
    string id PK
    string assignmentId FK
    string period
    json   ratings
    string comments
    enum   status "AppraisalStatus"
    string addedById FK "PM"
    string verifiedById FK "MPO"
    string approvedById FK "Manager"
  }
  WAGE_REPORT {
    string id PK
    string siteId FK
    string period "YYYY-MM"
    enum   status "WageReportStatus"
    decimal totalAmount
    string generatedById FK
    string approvedById FK
  }
  WAGE_LINE {
    string id PK
    string wageReportId FK
    string assignmentId FK
    int    daysAttended
    decimal dailyRate
    decimal victualing
    decimal lineTotal
  }
  CREW_ACTION {
    string id PK
    string entityType "requisition|application|assignment|..."
    string entityId
    enum   actionType "CrewActionType"
    string actorId FK
    string note
    json   metadata
    datetime createdAt
  }

4. Model notes

CrewMember is the spine: a row exists from the first application (PROSPECT/CANDIDATE) and persists as EMPLOYEE then EX_HAND. This is what makes "ex-hand preferred" cheap — their experience and documents are already on file. employeeId is assigned at onboarding.
Application vs Requisition — one requisition, many applications; APPLICATION_GATE rows are the audit of each vetting gate (competency & references, document, experience, salary, interview — competency and reference are one gate). interviewWaived is set true only after a Manager-approved waiver for a returning ex-hand — never automatically.
CrewAssignment is a single tour of duty. Onboarding creates it and flips the requisition to FILLED. Sign-off sets signOffDate/SIGNED_OFF, appends an EXPERIENCE_RECORD, and triggers a backfill requisition.
Verification is a field — verificationStatus + verifiedById on documents, bank and EPF. Routing rule (from the notebook): site-entered data → MPO; bank + EPF → Accounts. Attendance is operational: no verify gate, and it is visible only to site staff and the Manager — the MPO has no attendance access (the Manager reviews it for the wage report).
Leave (LEAVE_REQUEST) is applied by the Site in-charge for a crew member (appliedById = site staff, assignmentId = the crew member) and decided by the Manager (decidedById) — the MPO has no leave role. An approved leave that clashes — overlaps others for the same rank below its required strength — auto-raises a Requisition (reason = LEAVE, autoRaised, system-raised), mirroring the sign-off backfill on CrewAssignment.
RELIEF_REQUEST is the site-staff channel for flagging a foreseen cover gap (site staff do not raise requisitions). The office reviews it and converts it into a Requisition (requisitionId set, status CONVERTED).
SalaryStructure holds a rateBasis (MONTHLY or DAILY, the other derived), basic, victualingPerDay, and a JSON allowances; approved by a Manager. It is effective-dated (effectiveFrom/effectiveTo) so the salary or contract can change mid-assignment — many structures per assignment. Salary is the restricted field on the contract letter for site staff.
WageReport / WageLine are the generated monthly artefact. A line prorates across the effective-dated structures that overlap the month (days×rate₁ + days×rate₂); victualing is a separate accrual line, not rolled into base pay. The report totalAmount is the sum. Exportable to XLSX/PDF like a PO.
PPE as inventory — when the inventory flag is on, a PPE_ISSUE can also decrement ItemInventory at the site (optional integration).

5. Relationship to existing models

Existing model	Crewing relationship
`Vessel` (cost centre)	`Requisition.vesselId`, `CrewAssignment.vesselId`
`Site`	`WageReport.siteId`; site grouping for attendance & leave planner; optional PPE inventory
`User`	actors on every `CrewAction`; raisers/verifiers/approvers
`Notification`	crew events reuse the in-app bell + email log
`ItemInventory` (flagged)	optional PPE draw-down

Pelagia Portal (PPMS)

Home

Overview

Build & Run

System

Product

Planned

Quality

Ops

Engineering

Tech Debt

Pelagia Portal (PPMS) — internal purchase-order management. Self-hosted on pms1, live at pms.pelagiamarine.com. This wiki tracks the shipped product; authoritative sources are the repo code, App/CLAUDE.md, Docs/, and CHANGELOG.md.