Data Model Reference — Jules
Entity relationships, key Prisma models, and the multi-tenant schema architecture for Jules (Harold).
Data Model Reference — Jules
Developer documentation — Key entities, relationships, and multi-tenant architecture for the Jules backend.
Table of Contents
- Multi-Tenant Architecture
- Core Trading Entities
- Operations
- Containers
- Allocations
- Logistics
- Commercial & Contracts
- Financial & Invoicing
- Hedging
- Companies, Sites & Contacts
- Configuration & Reference Data
- Users & Auth
- Key Design Patterns
Multi-Tenant Architecture
Jules uses per-organization PostgreSQL schemas for data isolation. Each client organization has its own schema:
{client}_lotr_extr
For example: garfield_lotr_extr, demirdokum_lotr_extr.
All business data (operations, containers, invoices, etc.) lives in the organization schema. Prisma applies the schema prefix at runtime based on the authenticated user's organization.
Consequences:
- No cross-org queries are possible by design
- Each org's schema is independent — migrations run per org
- The
publicschema holds only auth/org-level tables
Core Trading Entities
Operations
The Operation model is the central commercial entity.
Key fields
| Field | Type | Description |
|---|---|---|
id | UUID | Primary key |
haroldNumber | String | Human-readable unique reference (e.g., OP-2026-001) |
type | Enum | BUY or SELL |
status | Enum | IN_PROGRESS, CONFIRMED, CLOSED, CANCELLED |
marketType | Enum | EXPORT or LOCAL |
companyId | FK | Counterparty company |
siteId | FK | Counterparty site |
contractId | FK | Linked contract (optional) |
isWarehouse | Boolean | Whether this is a warehouse operation |
createdById | FK | Creating user |
createdAt | DateTime | — |
OperationQuality
Each operation has one or more quality lines (the material grades being traded):
| Field | Type | Description |
|---|---|---|
operationId | FK | Parent operation |
qualityId | FK | Material grade |
quantity | Decimal | Contracted volume |
price | Decimal | Price per unit |
incoterm | Enum | FOB, CFR, CIF, EXW, etc. |
priceType | Enum | SPOT, INDEX, TREATMENT |
paymentTerms | String | — |
portOfLoadingId | FK | — |
portOfDestinationId | FK | — |
hedgingStatus | Enum | REQUIRED, PARTIALLY_HEDGED, HEDGED |
Containers
Container
| Field | Type | Description |
|---|---|---|
id | UUID | Primary key |
haroldNumber | String | e.g., CTN-2026-001234 |
referenceNumber | String | Physical container ID (e.g., CLHU1234567) |
sealedNumber | String | Security seal number |
blNumber | String | Bill of Lading number |
operationId | FK | Parent operation |
followUpStatus | Enum | UNPLANNED, PLANNED, LOADED, DELIVERED, CLOSED |
bookingStatus | Enum | BOOKING_REQUESTED, PC_BOOKED, FREIGHT_BOOKED, ALL_BOOKED |
netWeight | Decimal | Weight of commodity |
grossWeight | Decimal | Total weight incl. tare |
tareWeight | Decimal | Empty container weight |
weightSlip | Decimal | Weight at destination |
dateOfLoading | DateTime | — |
dateOfDelivery | DateTime | — |
estimatedMargin | Decimal | Pre-invoicing margin estimate |
finalMargin | Decimal | Post-invoicing margin |
isPurchaseInvoicingClosed | Boolean | — |
isSaleInvoicingClosed | Boolean | — |
bookingId | FK | Freight booking |
shipmentId | FK | Shipment |
ContainerToOperationQuality
Junction table tracking quantities per quality within a container:
| Field | Description |
|---|---|
containerId | FK → Container |
operationQualityId | FK → OperationQuality |
plannedQuantity | Intended quantity at creation |
loadedQuantity | Actual weight at loading |
deliveredQuantity | Weight at destination |
ContainerInvoicingLine
One row per cost/revenue element per container:
| Field | Description |
|---|---|
containerId | FK → Container |
elementType | BUY, SELL, or PROVIDER |
costElement | Enum (FREIGHT_COST, PRECARRIAGE, AGENT_COMMISSION, etc.) |
status | PLANNED, LOADED, DELIVERED, CLOSED |
invoicingStatus | PENDING or INVOICED |
estimatedAmount | From rate card |
actualAmount | From invoice |
currency | — |
Allocations
Allocation
| Field | Type | Description |
|---|---|---|
id | UUID | — |
haroldNumber | String | e.g., ALLOC-2026-0012 |
status | Enum | DRAFT, CONFIRMED |
buyOperationQualityId | FK | Buy side quality line |
sellOperationQualityId | FK | Sell side quality line |
referenceNumber | String | External reference |
trackingStatus | Enum | LOADED → SHIPPED → ARRIVED → RECOVERED |
annex7Status | Enum | PENDING → SIGNED_AND_UPLOADED |
bookingFulfilmentStatus | Enum | PENDING → ALL_BOOKING_OK |
customsStatus | Enum | PENDING → SENT_TO_CARRIER |
loadReportStatus | Enum | PENDING → SENT_TO_DOCS_TEAM |
isVGMSubmitted | Boolean | — |
marginPerTon | Decimal | Estimated margin/T |
Containers are linked to allocations via a many-to-many (Allocation ↔ Container).
Logistics
Booking
| Field | Description |
|---|---|
type | FREIGHT or CARGO_BULK |
status | REQUESTED, IN_PROGRESS, CONFIRMED, CANCELLED |
shippingLineId | FK → ShippingLine |
logisticForwarderId | FK → LogisticForwarder |
portOfLoadingId | FK → Port |
portOfDestinationId | FK → Port |
numberOfBookedContainers | Integer |
freightCostId | FK → FreightRate |
vesselName / voyageNumber | For cargo bulk |
charterPartyDate | For cargo bulk |
quantityAllowanceType | MOLOO / CHOPT / MOLCHOP (bulk) |
BookingToPort (bulk bookings)
Multi-port loading/destination per voyage:
| Field | Description |
|---|---|
bookingId | FK → Booking |
portId | FK → Port |
direction | LOADING or DISCHARGE |
sequence | Port call order |
Shipment
| Field | Description |
|---|---|
haroldNumber | e.g., SHIP-2026-0441 |
blNumber | Bill of Lading number |
locationStatus | AT_ORIGIN → IN_TRANSIT → COMPLETED |
timingStatus | ON_TIME / DELAYED / EARLY_ARRIVAL |
documentationStatus | TO_START → SENT_TO_CLIENT |
releaseType | BL release method |
currentEta / originalEta | — |
actualSailingDate | — |
letterOfCreditId | FK → LetterOfCredit (optional) |
trackedContainerId | Reference container for tracking |
FreightRate / PreCarriageRate
Rate cards used for logistics cost estimation:
| Entity | Key fields |
|---|---|
FreightRate | shippingLineId, portOfLoadingId, portOfDestinationId, cost, logisticMaterialId, validityStart/End |
PreCarriageRate | preCarriageLineId, preCarriageAreaId, portOfLoadingId, mode (ROAD/RAIL/BARGE), cost |
Both have validationStatus: NOT_VALIDATED, VALIDATED, UNCHECKED.
Commercial & Contracts
Contract
| Field | Description |
|---|---|
type | TRADING, WM_SPOT, WM_RECURRING |
direction | BUY or SELL |
status | IN_PROGRESS → CONFIRMED → CLOSED |
companyId | Counterparty |
siteId | Counterparty site |
marketType | EXPORT or LOCAL |
startDate / endDate | Contract period |
ContractStream
Quality lines within a contract:
| Field | Description |
|---|---|
contractId | FK → Contract |
qualityId | Material grade |
quantity | Contracted volume |
price | Agreed price |
priceType | SPOT / INDEX / TREATMENT |
incoterm | — |
toleranceRate | Allowed quantity deviation |
mqc | Minimum quality commitment per container |
Goal
| Field | Description |
|---|---|
qualityId | Targeted material |
quantity | Volume target |
targetPrice | Price objective |
priceType | BUY or SELL |
startDate / dueDate | Time window |
fulfilledQuantity | Volume achieved through operations |
Financial & Invoicing
Invoice
| Field | Description |
|---|---|
haroldNumber | — |
type | BUY_INVOICE, SELL_INVOICE, PROVIDER_INVOICE, etc. |
status | DRAFT → CONFIRMED → PAID |
companyId | Counterparty |
totalAmount | — |
currency | — |
operationId | Linked operation |
shipmentId | Linked shipment (optional) |
Budget
| Field | Description |
|---|---|
name | Budget identifier |
templateId | FK → BudgetTemplate |
status | PENDING, APPROVED, REJECTED |
overdraftPeriod | Days of negative cash |
BudgetElement
One cost line per budget:
| Field | Description |
|---|---|
budgetId | FK → Budget |
elementId | FK → cost element |
costType | FLAT / PERCENTAGE / LOGISTICS_RATE |
price | Amount |
costPercentage | For percentage-based |
percentageOf | PURCHASE / SALE / TURNOVER |
Hedging
HedgingContract
| Field | Description |
|---|---|
haroldNumber | — |
type | PURCHASE / SALE / PURCHASE_AVG_PRICE / SALE_AVG_PRICE / PURCHASE_DEHEDGE / SALE_DEHEDGE / BORROWING / LENDING / etc. |
market | LME or COMEX |
commodity | Hedged commodity |
quantity | Total hedge volume |
tradeDate | When executed |
promptDate | Settlement date on exchange |
status | OPEN / PARTIALLY_CLOSED / CLOSED / PAST |
allocatedQuantity | Volume allocated to containers |
dehedgedQuantity | Volume closed out |
parentId | FK → HedgingContract (for de-hedges/carry) |
ContainerQualityHedging
Links a hedging contract to a specific container quality:
| Field | Description |
|---|---|
hedgingContractId | FK → HedgingContract |
containerId | FK → Container |
operationQualityId | FK → OperationQuality |
hedgedQuantity | Tonnes covered |
loadedWeight | Actual container weight |
recoveryPercentage | For recovery-formula hedges |
Companies, Sites & Contacts
Company
| Field | Description |
|---|---|
name | — |
type | SUPPLIER / CUSTOMER / BOTH / INTERNAL |
isBlocked | Blocked from new operations |
erpId / erpValue | External ERP identifiers |
Site
| Field | Description |
|---|---|
companyId | FK → Company |
name | — |
siteType | WAREHOUSE / SUPPLIER / CUSTOMER / PORT |
isWarehouse | Warehouse mode flag |
address | — |
portOfLoadingId | Default port (for supplier sites) |
portOfDestinationId | Default port (for customer sites) |
erpId / erpCompanyId | External identifiers |
SiteToCompany
Multiple companies can be linked to a site (e.g., a port used by many companies). Tracks the primary company relationship.
Contact
| Field | Description |
|---|---|
firstName / lastName | — |
email | — |
phone | — |
companyId | Primary company |
isPrimary | Primary contact flag |
Configuration & Reference Data
Quality (material grade)
| Field | Description |
|---|---|
name | e.g., "HMS 1&2" |
familyId | FK → QualityFamily |
groupId | FK → QualityGroup |
defaultVolume | Default quantity unit |
defaultPriceVolume | Default price unit |
isDeleted | Soft delete flag |
LogisticMaterial (container type)
| Field | Description |
|---|---|
name | e.g., "40' HC" |
capacity | Standard capacity in tonnes |
ShippingLine / LogisticForwarder / PreCarriageLine
Reference entities for logistics providers. All support contact linking.
Port
| Field | Description |
|---|---|
name | Port name |
country | — |
type | LOADING or DESTINATION or BOTH |
Users & Auth
User (in org schema)
| Field | Description |
|---|---|
email | — |
firstName / lastName | — |
role | User role within the organization |
departmentId | FK → Department |
UserToCompany
Links internal users to counterparty companies they manage:
| Field | Description |
|---|---|
userId | FK → User |
companyId | FK → Company |
ExternalUser (portal users)
| Field | Description |
|---|---|
email | — |
companyId | The counterparty company they represent |
type | SUPPLIER_PORTAL or CUSTOMER_PORTAL |
isActive | — |
Key Design Patterns
1. Harold numbers
Every key entity gets a system-generated human-readable identifier (haroldNumber) in addition to the UUID primary key. Format: {PREFIX}-{YEAR}-{SEQUENCE} (e.g., OP-2026-001234).
2. Soft deletes
Most reference data (qualities, sites, companies) uses isDeleted = true rather than hard deletes, to preserve referential integrity.
3. Estimated vs actual amounts
Most financial fields exist in pairs:
estimatedFreightCost/actualFreightCostestimatedMargin/finalMargin
Estimated values come from rate cards at creation. Actual values come from invoicing.
4. ERP sync fields
Entities that sync with external ERPs carry erpId, erpValue, erpCompanyId fields (nullable). Sync status is tracked per entity.
5. Watchers pattern
Multiple entities (allocations, shipments, operations) support a watchers relation — a list of users who receive notifications about changes.
6. Multi-currency
Financial amounts always carry a currency field. Margin calculations normalize all amounts to the sale currency before computing.
Last updated today
Built with Documentation.AI