Margin Calculation Engine — Developer Reference

Developer documentation — How Jules computes trading margins across three distinct systems. Intended for engineers working on the harold-api backend, Prisma migrations, or the P&L epic.

Table of Contents

1. Architecture overview
2. Shared concepts
3. Container Margin
4. Dashboard Margin
5. Bulk Margin
6. Performance characteristics
7. Adding a new cost component
8. Testing margin logic

Architecture overview

Jules uses three margin systems that differ in granularity, data source, and purpose:

Computation strategy

All three margin systems are computed on-the-fly from normalized invoicing data — there are no separate margin fact tables for the existing system. The upcoming Margin & P&L epic introduces ContainerMarginSnapshot as an immutable snapshot store (see New data models below), but the current system reads live from ContainerInvoicingLine.

Multi-tenancy note

All margin queries are scoped to the organization's PostgreSQL schema ({client}_lotr_extr). Prisma switches the schema at connection time based on the JWT claim. There is no cross-schema margin aggregation.

Shared concepts

The monetary value triple

Every monetary amount in the margin engine is represented as a triple: { quantity, currency, volume }.

// A margin of 150 USD per metric tonne:
{ quantity: 150.00, currency: "USD", volume: "T" }

This structure exists across invoicing lines, rate cards, and computed margin outputs. It ensures that the unit of measure (tonne, kg, short tonne) and the currency are always co-located with the amount, preventing silent unit-mismatch bugs.

Estimated vs final

The engine maintains two parallel margin values for every container:

Both fields live directly on the Container model:

// On Container model
estimatedMargin  Decimal  // Pre-invoicing estimate
finalMargin      Decimal  // Post-invoicing actual

The estimated margin uses estimatedAmount from each ContainerInvoicingLine; the final margin uses actualAmount.

Currency normalization

Before any margin arithmetic, all component amounts are converted to the sale operation's currency. The conversion uses the FX rate applicable at the time of each transaction (event-date FX for operational stages, document-date FX for posted invoices).

Normalized amount = txAmount × fxRate  →  sale currency

This conversion happens at the invoicing line level. Dashboard margin then aggregates already-normalized values.

Per-tonne normalization

After currency conversion, all amounts are expressed per metric tonne (/ netWeight). This makes margins comparable across containers of different sizes.

Container Margin

When it applies

Container margin applies to export operations where commodities travel in physical shipping containers. It is the most common margin type — produced for every container linked to an allocation.

Formula

Container margin (per tonne) =
    Sale price/T
  − Purchase price/T
  − Logistics cost/T

For totals: margin/T × netWeight.

Incoterm gate: when logistics is required

The logistics deduction is conditional on the incoterm pair. It is only included when the organization bears the transport cost:

Include logistics cost IF:
  sale_incoterm ≠ EXW
  AND purchase_incoterm = EXW

Rationale: If the buyer buys EXW (ex-works, the organization picks up from the supplier), but sells at CFR/CIF/FOB (the organization delivers to port or destination), the organization incurs the freight. That cost must appear in the margin.

If both sides are EXW, or if the purchase incoterm already includes transport (CFR, CIF), the logistics line is excluded from the deduction.

This logic is evaluated in the resolver when computing isLogisticRequired and controls whether the FREIGHT_COST and PRECARRIAGE lines are factored into the margin.

Data source: ContainerInvoicingLine

The container margin reads directly from ContainerInvoicingLine rows. Each row represents one cost or revenue element for one container:

The margin computation groups lines by containerId and sums:

• SELL lines → revenue
• BUY lines → purchase cost
• PROVIDER lines matching the active logistics elements → logistics cost

Weighted average aggregation

When rolling up container margins to the operation or allocation level, Jules uses a quantity-weighted average rather than a simple average:

Aggregate margin/T = Sum(margin_i × quantity_i) / Sum(quantity_i)

This ensures that heavier containers (e.g., 25 T) have proportionally more influence on the aggregate than lighter ones (e.g., 18 T). A simple average would overweight small containers.

Grouping dimensions

The resolver groups containers along 5 dimensions for the margin matrix:

1. buyOperationId — purchase operation
2. sellOperationId — sale operation
3. buyOperationQualityId — material grade on the buy side
4. sellOperationQualityId — material grade on the sell side
5. containerId — individual container

The UI can display margin at any level of this hierarchy (per container, per quality, per operation pair).

Computability conditions

A container margin can only be computed when:

• A sale price exists on the sell-side OperationQuality
• A purchase price exists on the buy-side OperationQuality
• If isLogisticRequired = true: a logistics cost line exists and has a non-null amount

Missing any of these conditions results in a null margin (not zero). The resolver surfaces this as isComputable: false with explicit blockingReasons.

New data models — Margin & P&L epic

The in-progress Margin & P&L epic introduces ContainerMarginSnapshot as a point-in-time, immutable record of a margin computation:

model ContainerMarginSnapshot {
  id               String      @id @default(uuid())
  containerId      String
  stage            MarginStage // FORECAST | OPERATIONAL | ACCRUED | ACTUAL | FINAL
  triggerEvent     String      // e.g. "loading_confirmed", "posting", "manual_refresh"

  // Computed totals in base currency
  totalRevenue     Decimal     @db.Decimal(15, 2)
  totalCogs        Decimal     @db.Decimal(15, 2)
  totalExpenses    Decimal     @db.Decimal(15, 2)
  totalWorkingCap  Decimal     @db.Decimal(15, 2)
  totalAdjustments Decimal     @db.Decimal(15, 2)
  totalMargin      Decimal     @db.Decimal(15, 2)
  marginPerUnit    Decimal     @db.Decimal(15, 4)
  currency         String

  // FX at snapshot level (one rate per stage)
  fxRate           Decimal?    @db.Decimal(15, 8)
  fxSource         FxSource?   // ESTIMATED | EVENT | DOCUMENT

  quantity         Decimal     @db.Decimal(15, 4)
  quantityUnit     String

  isComputable     Boolean     @default(true)
  blockingReasons  String[]
  isCurrent        Boolean     @default(true)  // latest for this stage
  isFrozen         Boolean     @default(false) // true for FINAL snapshots

  lines            ContainerMarginSnapshotLine[]
  adjustments      MarginAdjustment[]

  @@index([containerId, stage, isCurrent])
  @@map("container_margin_snapshots")
}

Each snapshot has child ContainerMarginSnapshotLine rows, one per MarginComponent:

enum MarginComponent {
  REVENUE
  COGS
  FREIGHT
  INSURANCE
  BUNKERING
  FEES
  WORKING_CAPITAL
  ADJUSTMENT
}

The five stages progress as the container moves through its lifecycle:

FORECAST → OPERATIONAL → ACCRUED → ACTUAL → FINAL

• FORECAST: contract signed — expected quantity, contract price, estimated FX
• OPERATIONAL: loading confirmed — loaded quantity, provisional price, event-date FX
• ACCRUED: delivery confirmed — delivered quantity, resolved pricing, event-date FX
• ACTUAL: all invoices and payables posted — from actual document amounts; adjustments happen at this stage
• FINAL: frozen/immutable — explicit freeze action; no further changes

Dashboard Margin

When it applies

Dashboard margin is used in the advanced reporting layer — the margin dashboard and P&L views. It provides the full cost breakdown per allocation (buy/sell pair), not just the three-line container view.

Formula

Dashboard margin (per tonne) =
    Sale price/T
  − Purchase price/T
  − Logistics cost/T
  − Pre-carriage cost/T
  − Customs fees/T
  − BL (Bill of Lading) fees/T
  − Inspection cost/T
  − Buy agent commission/T
  − Sell agent commission/T
  − Goal admin fees/T
  − Payment term fees/T
  − Other costs/T

That is 11+ deducted components versus the 1 logistics deduction in the container margin. The two systems can diverge — the container margin is a fast operational indicator, the dashboard margin is the reconciled P&L view.

Data flow

The resolver:

1. Fetches all ContainerInvoicingLine rows for the allocation
2. Converts each amount to the sale currency using the applicable FX rate
3. Groups them by costElement into the 11+ categories
4. Divides by total net weight to produce per-tonne values
5. Returns both estimatedAmount and actualAmount views

Component mapping

Currency conversion in the dashboard

All components are converted to the sale operation's currency before aggregation. The FX rate used depends on the stage:

At the Actual stage, different lines within the same container can use different FX rates (each reflecting the rate at the time the document was posted). The snapshot model captures this with fxRate/fxSource/fxDate at the line level.

Bulk Margin

When it applies

Bulk margin applies to warehouse operations where commodities are stored in stockpiles before resale. Unlike container operations, there is no direct link between a specific purchase contract and a specific sale — the material from multiple purchases is blended in the stockpile.

Formula

Bulk margin =
    Net sale revenue
  − Material purchase cost (from stockpile weighted average)
  − Loading cost

Stockpile weighted average cost

The key distinction of bulk margin is how the purchase cost is determined. Instead of reading from a specific OperationQuality.price, the system uses the weighted average cost (mean_purchase_cost) of the stockpile:

mean_purchase_cost =
    Sum(purchase_price_i × quantity_i)
  / Sum(quantity_i)

  for all purchase receipts that fed this stockpile

Example:

mean_purchase_cost = (100 × 200 + 50 × 250) / (100 + 50)
                   = (20,000 + 12,500) / 150
                   = 32,500 / 150
                   = $216.67/T

This weighted average is updated each time new material is received into the stockpile. It is stored on the Stockpile model as meanPurchaseCost.

Bulk margin data flow

Loading cost

The loading cost covers third-party costs incurred at the stockpile/warehouse to prepare the material for dispatch (e.g., front-end loaders, handling). It is tracked as PROVIDER invoicing lines against the bulk sale operation, not against individual containers.

Performance characteristics

Current system: on-the-fly computation

In the current implementation, all three margin systems compute their values at query time from live ContainerInvoicingLine data. There are no precomputed margin tables or materialized views.

Implications:

• Margins are always up-to-date with the latest invoicing data
• For large operations (50+ containers, 11+ cost elements each), the resolver must aggregate hundreds of rows per query
• Query-time performance is acceptable for typical operations sizes on the demo (~70 org schemas)

Current mitigations:

• Prisma queries use indexed containerId and operationId lookups
• The allocation-level grouping is done in TypeScript after fetching the relevant rows (not a single complex SQL join)
• DataLoaders batch container fetches within a single GraphQL request to avoid N+1 queries

Upcoming: snapshot-based caching

The Margin & P&L epic introduces ContainerMarginSnapshot as a precomputed cache that is written on trigger events:

This moves the heavy computation out of the GraphQL read path and into the write path (triggered as side effects of mutations). The dashboard reads the most recent isCurrent = true snapshot per stage, which is a single indexed lookup.

-- Fast snapshot read (indexed on containerId + stage + isCurrent)
SELECT * FROM container_margin_snapshots
WHERE container_id = $1
  AND stage = 'ACTUAL'
  AND is_current = true;

Adding a new cost component

This section walks through every layer you must touch to introduce a new cost element into the margin calculation — for example, a new STORAGE_FEE charge for warehouse dwell time.

Step 1 — Add the cost element enum value

In harold-api/prisma/schema.prisma, add the new value to the CostElement enum:

enum CostElement {
  // existing values ...
  UNEXPECTED_COST
  STORAGE_FEE   // new
}

Step 2 — Generate and run the migration

# In harold-api/
npx prisma migrate dev --name add-storage-fee-cost-element

This generates a migration file and applies it to your local database. The migration will include an ALTER TYPE statement for the PostgreSQL enum.

Important: PostgreSQL enum additions are append-only. You can add a value but cannot remove or rename one without a more complex migration.

Step 3 — Update the Dashboard Margin component mapping

In the Dashboard Margin resolver (look for the function that maps costElement to dashboard component categories), add the new element to the appropriate bucket:

// In the dashboard margin aggregation logic
const OTHER_COSTS_ELEMENTS = [
  CostElement.UNEXPECTED_COST,
  CostElement.PENALTY,
  CostElement.GLOBAL_DISCOUNT,
  CostElement.ELEMENT_DISCOUNT,
  CostElement.STORAGE_FEE,  // add here — or create a new bucket if warranted
];

If STORAGE_FEE warrants its own top-level line in the dashboard (rather than being folded into "Other costs"), add a new mapping entry and update the GraphQL schema accordingly.

Step 4 — Update the GraphQL schema (if adding a new dashboard line)

In the Dashboard module's SDL schema, add the new field to the margin output type:

type DashboardMargin {
  # ... existing fields
  storageFee: MonetaryValue
  storageFeePerTon: MonetaryValue
}

Step 5 — Update the Container Invoicing UI

In harold-web, the container invoicing matrix must allow users to create lines of the new costElement type. Find the costElement dropdown configuration and add the new value with its display label.

Step 6 — Update the MarginComponent mapping (Margin & P&L epic)

If you are working within the new snapshot architecture, decide which MarginComponent the new element maps to:

const componentForElement: Record<CostElement, MarginComponent> = {
  // ...
  [CostElement.STORAGE_FEE]: MarginComponent.FEES,  // or a new component value
};

If a new MarginComponent value is needed, add it to the enum and update snapshot computation logic accordingly.

Step 7 — Write a test

The pattern in the codebase uses snapshot tests for SQL queries and unit tests for resolver logic. Add a test that:

1. Creates a container with a STORAGE_FEE invoicing line
2. Asserts the margin calculation correctly deducts the storage fee
3. Asserts the dashboard margin surfaces it in the expected bucket

yarn jest --testPathPattern ContainerMargin

Step 8 — Verify

yarn lint
yarn type:check

Both must pass before opening a PR.

Testing margin logic

Snapshot tests for SQL

The codebase uses Jest snapshot tests for raw SQL queries. These live in test/__snapshots__/ directories alongside the SQL modules. When changing SQL, run:

yarn jest --updateSnapshot

Review the diff carefully — snapshot changes are the actual SQL that will run against production.

Unit tests for aggregation logic

Margin aggregation logic (weighted average, component bucketing, FX conversion) is unit-testable without a database. Inject mock ContainerInvoicingLine arrays and assert on the output shape.

Key edge cases to always test

Related documentation

• Data Model Reference — Container, ContainerInvoicingLine, Allocation entity details
• Technical Stack — GraphQL module structure, Prisma conventions, TypeScript rules
• Margin Calculations (user doc) — Business-level explanation of the three systems
• Workflow: Month-End Margin Close — How Finance closes margins at period end
• Containers (user doc) — Container invoicing matrix and cost element catalogue