shad0w/pelagia-portal
1
0
Fork 0
Code Issues 10 Pull requests 2 Projects Releases 2 Packages Wiki Activity Actions
Page: Data Model
Pages
Architecture Changelog Crewing Architecture Crewing Data Model Crewing Design Document Crewing Implementation Spec Crewing Module Crewing Roles and Permissions Crewing Use Cases Crewing User Stories Crewing Workflows Data Model Deployment and Operations Design System E2E Test Framework E2E Test Plan Environment Variables Feature Catalogue File Storage Getting Started Glossary Home Inventory and Catalogue Inventory on Approval Issue to Deploy Pipeline Notifications Open Questions PO Lifecycle Pages and Navigation Planned Features Playwright Test Design Purchase Orders Reports Mockup Reports Roles and Permissions Tech Debt Test Plan Test Testing Vendors and GST Lookup Workflows
5 Data Model
Hardik edited this page 2026-06-21 12:23:09 +05:30
Unescape Escape
Table of Contents
This file contains ambiguous Unicode characters

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.

Data Model

Source of truth: App/prisma/schema.prisma. This page mirrors the current schema (PostgreSQL via Prisma 5). Monetary values are Decimal; quantities use Decimal(10,3); IDs are cuid().

Enums

enum Role { TECHNICAL  MANNING  ACCOUNTS  MANAGER  SUPERUSER  AUDITOR  ADMIN }

enum POStatus {
  DRAFT  SUBMITTED  MGR_REVIEW  VENDOR_ID_PENDING  EDITS_REQUESTED  REJECTED
  MGR_APPROVED  SENT_FOR_PAYMENT  PARTIALLY_PAID  PAID_DELIVERED
  PARTIALLY_CLOSED  CLOSED
}

enum ActionType {
  CREATED  SUBMITTED  APPROVED  APPROVED_WITH_NOTE  REJECTED  EDITS_REQUESTED
  VENDOR_ID_REQUESTED  VENDOR_ID_PROVIDED  PAYMENT_SENT  PARTIAL_PAYMENT_CONFIRMED
  RECEIPT_CONFIRMED  PARTIAL_RECEIPT_CONFIRMED  CLOSED  REASSIGNED
  PRODUCT_PRICE_UPDATED  MANAGER_LINE_EDIT
}

enum RequestStatus { PENDING  APPROVED  DENIED }

POStatus drives the PO Lifecycle; ActionType rows form the per-PO audit trail.

Entity relationships

User ──< PurchaseOrder (submitter)          PurchaseOrder >── Vessel  (cost centre, REQUIRED)
User ──< POAction (actor)                    PurchaseOrder >── Account (accounting code, REQUIRED)
User ──< Notification                        PurchaseOrder >── Company (optional, billing)
User ──< ItemConsumption (recordedBy)        PurchaseOrder >── Vendor  (optional)
User ──< SuperUserRequest                    PurchaseOrder >── Site    (optional, delivery → inventory)

PurchaseOrder ──< POLineItem                 POLineItem >── Product (optional)
PurchaseOrder ──< PODocument                 POLineItem >── Account (optional, per-line)
PurchaseOrder ──< POAction                   Vendor ──< VendorContact
PurchaseOrder ──1 Receipt                    Vendor ──< ProductVendorPrice >── Product
PurchaseOrder ──< Notification               Product/Site ──< ItemInventory  (unique product+site)
                                             Product/Site ──< ItemConsumption (unique product+site+date)
Account ──< Account (self: AccountHierarchy, 3 levels)

Core models

User

id, employeeId (unique), email (unique), name, passwordHash?, role, isActive, signatureKey?, createdAt, updatedAt.

  • passwordHash is nullable → SSO-only users (Microsoft Entra) have no password; they may set one from their profile.
  • signatureKey → storage key of an uploaded approval signature (only approvers upload one; appears on exported docs).
  • Relations: submitted POs, actions, notifications, recorded consumption, super-user requests (as requester and as resolver).

PurchaseOrder

The central entity. Key fields:

Field Notes
poNumber (unique) COMPANY/VESSEL/ID/FY — see Purchase Orders
status POStatus, default DRAFT
totalAmount Decimal(12,2), GST-inclusive sum of line items
paidAmount? accumulates across partial payments
currency default INR
poDate? editable PO date; export "Date" = poDate ?? approvedAt ?? createdAt
dateRequired?, projectCode?
managerNote?, paymentRef?, paymentDate? paymentDate compulsory at payment, no future dates
piQuotationNo/Date?, requisitionNo/Date?, placeOfDelivery? quotation/requisition metadata
tcDelivery / tcDispatch / tcInspection / tcTransitInsurance / tcPaymentTerms / tcOthers Terms & Conditions text
submittedAt / approvedAt / paidAt / closedAt / createdAt / updatedAt lifecycle timestamps
cancelledAt? / cancellationReason? set when a manager/superuser cancels the PO
supersededById? self-relation → the existing PO that replaces this cancelled one (reciprocal supersedes)

Required FKs: submitterId → User, vesselId → Vessel (cost centre), accountId → Account (accounting code). Optional FKs: companyId, vendorId, siteId. Cascade-deletes its lineItems and documents.

POLineItem

name, description?, quantity Decimal(10,3), unit, unitPrice Decimal(12,2), totalPrice Decimal(12,2), gstRate Decimal(5,4) default 0.18, sortOrder, size?, deliveredQuantity? , productId?, accountId? + poId (cascade).

  • totalPrice = quantity × unitPrice × (1 + gstRate); the PO totalAmount is the sum of line totalPrice. See GST.
  • deliveredQuantity supports partial receipt.
  • accountId allows a per-line accounting code override.

POAction (audit trail)

actionType (ActionType), note?, metadata Json?, createdAt + poId, actorId. Every state transition and notable event writes one row. metadata is flexible (payment ref, vendor ID, edit diffs, etc.).

PODocument / Receipt

  • PODocument: fileName, fileSize, mimeType, storageKey, uploadedAt (cascade on PO delete). Attachments are grouped by type on the detail page.
  • Receipt: one per PO (@unique poId); storageKey, fileName, notes?, confirmedAt. Upserted on repeat confirmations.

Notification

subject, body, link?, isRead (default false), sentAt, status (default "sent")

  • optional poId, userId. Backs the in-app notification bell; every email event is also persisted here. See Notifications.

Reference / catalogue models

Vessel — the Cost Centre

id, name, code (unique), isActive. A PO's cost centre is a Vessel (PurchaseOrder.vesselId required). Surfaced as "Cost Centre" throughout the UI (/admin/vessels → "Cost Centre Management"). The earlier Vessel-or-Site cost-centre design and costCentreRef encoding were removed; Vessel no longer links to a Site.

Account — the Accounting Code (3-level hierarchy)

code (unique, 6-digit numeric), name, description?, isActive + self-relation parentId/children (AccountHierarchy). Three levels:

Top Category (100000) → Sub-Category (100100) → Leaf Item (100101)

Only leaf accounts (no children) are PO-selectable. Seed data: prisma/accounting-codes-data.ts. Line items may carry a per-line accountId.

Company — multi-company invoicing

name, code? (unique, e.g. PMS), gstNumber?, address?, telephone?, mobile?, email?, invoiceEmail?, invoiceAddress?, logoKey?, stampKey?, isActive. The sister company a PO is billed under (PurchaseOrder.companyId, optional); its details populate the exported PO header / invoice block (falling back to Pelagia defaults). logoKey/stampKey are storage keys for the branding images shown on exported POs (see File Storage). Managed at /admin/companies.

Vendor + VendorContact

Vendor: name, vendorId? (unique formal code), address?, pincode?, gstin?, latitude?, longitude?, isVerified (default false), isActive. VendorContact[]: name, role?, mobile?, email?, isPrimary (cascade).

  • Submitters can create vendors (unverified). A vendor becomes verified on a closing/paying PO, on import, or via Manager/Accounts/Admin.
  • latitude/longitude geocoded from pincode for vendor-distance sorting.
  • See Vendors and GST Lookup.

Product + ProductVendorPrice

Product: code (unique), name, description?, lastPrice?, lastVendorId?, isActive. ProductVendorPrice: one row per (productId, vendorId) with price. On payment confirmation, lastPrice/lastVendorId and per-vendor prices are updated. See Inventory and Catalogue.

Site / ItemInventory / ItemConsumption (inventory, feature-flagged)

  • Site: name, code (unique), address?, latitude?, longitude?, isActive. Ports/depots/offices that hold stock; used for vendor-distance and delivery.
  • ItemInventory: quantity of a product at a site — unique (productId, siteId). Incremented at PO approval (not on close) when the PO has a siteId.
  • ItemConsumption: daily draw-down — unique (productId, siteId, date), with recordedById.

The whole inventory surface is gated by NEXT_PUBLIC_INVENTORY_ENABLED (lib/feature-flags.ts). The vendor/product catalogue used for PO creation stays available regardless.

SuperUserRequest

userId (requester), reason?, status (RequestStatus), createdAt, resolvedAt?, resolvedById?. Backs the "request SuperUser access" flow from the profile page, resolved at /admin/superuser-requests.

Migrations

Migrations live in prisma/migrations/ and are committed/reviewed in PRs. The chronology is a useful changelog of schema evolution, e.g.:

  • add_item_db, add_product_catalogue_size_manager_edit
  • add_po_export_fields, structured_tc_fields, add_line_item_name
  • add_product_vendor_price, add_site_inventory_consumption
  • vendor_pincode, vendor_contacts
  • partial_receipt, partial_payment
  • user_profile_signature, notification_isread_link
  • optional_password_hash_for_sso
  • vessel_optional_cost_centre → account_hierarchy → vessel_no_site_po_vessel_required (cost-centre / accounting-code rework)
  • add_company, company_invoice_email, company_code
  • po_payment_date, add_po_date

Always apply migrations before new code serves traffic. pnpm build runs only prisma generate; deploying code whose client expects a not-yet-migrated column yields P2022 … column does not exist. The deploy workflow runs migrate deploy. See Deployment and Operations.

Pelagia Portal (PPMS)

Overview

Build & Run

System

Product

Planned

Quality

Ops

Engineering

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.