SK
Sandeep
Back to blog

Modelling cargo and freight: what a status column can't tell you

Freight software fails when a shipment becomes a row with a status column. Chargeable weight disputes, append-only tracking events with provenance, versioned transport documents, and recording the customs basis instead of computing duty.

10 min readby
LogisticsFreightDjangoData ModellingPostgreSQL

Most freight software goes wrong in the first hour of design, in the same way: someone creates a shipments table with a status column and starts adding fields.

Six months later that table has forty columns, status has nineteen possible values that mean different things depending on the service type, and nobody can answer "where was this consignment on the 14th and who told us that?" — which is the question that actually matters when a customer disputes an invoice.

Cargo is not a CRUD domain. It is a domain of disputed measurements, legally significant documents, and events arriving out of order from parties you do not control. Here is what I've learned modelling it for freight forwarding, courier, and logistics platforms.

Get the nouns right first

Freight has precise vocabulary and it is not decoration. Conflating these terms is the root of most modelling pain:

  • A package is one physical unit. It has dimensions, a weight, and a barcode.
  • A consignment (or shipment) is a set of packages moving from one shipper to one consignee under one contract. This is what the customer thinks they bought.
  • A leg is one movement of a consignment by one carrier between two points. A Kathmandu-to-Hamburg consignment might be trucked to Delhi, flown to Frankfurt, then trucked onward. Three legs.
  • A master document covers a consolidated load from the forwarder to the carrier. A house document covers one customer's consignment inside that load.

That last distinction is what freight forwarding is. A forwarder buys space in bulk and resells it, so one Master Air Waybill can contain thirty House Air Waybills. If your schema has a single awb_number field on a shipment row, you cannot represent the core business, and you will discover this after go-live.

class Consignment(models.Model):
    reference = models.CharField(max_length=32, unique=True)  # your own, stable
    shipper = models.ForeignKey("Party", related_name="sent", on_delete=models.PROTECT)
    consignee = models.ForeignKey("Party", related_name="received", on_delete=models.PROTECT)
    house_document = models.OneToOneField(
        "TransportDocument", null=True, blank=True, on_delete=models.PROTECT,
    )
    incoterm = models.CharField(max_length=3)  # EXW, FOB, CIF, DAP...

class Leg(models.Model):
    consignment = models.ForeignKey(Consignment, related_name="legs", on_delete=models.CASCADE)
    sequence = models.PositiveSmallIntegerField()
    carrier = models.ForeignKey("Carrier", on_delete=models.PROTECT)
    mode = models.CharField(max_length=8)  # air, sea, road, rail
    origin = models.ForeignKey("Location", related_name="+", on_delete=models.PROTECT)
    destination = models.ForeignKey("Location", related_name="+", on_delete=models.PROTECT)

    class Meta:
        constraints = [
            models.UniqueConstraint(
                fields=["consignment", "sequence"], name="unique_leg_sequence",
            )
        ]

Note incoterm on the consignment. Incoterms decide who pays for which leg and who carries the risk where. Leaving them out means bolting on a pile of boolean flags later to answer billing questions.

Chargeable weight is the number that causes arguments

If you build one thing carefully, make it this.

Carriers do not bill on weight. They bill on chargeable weight, which is the greater of actual weight and volumetric weight. A box of pillows weighs almost nothing and fills a pallet; the carrier is selling space, so they charge for the space.

The volumetric divisor varies by mode and by contract:

  • Air freight, IATA standard: 6000 cm³ per kg
  • Courier and express: commonly 5000 cm³ per kg
  • Sea LCL: 1 CBM treated as 1000 kg
from decimal import Decimal, ROUND_HALF_UP

def chargeable_weight_kg(
    packages,
    volumetric_divisor: Decimal,
    rounding_step: Decimal = Decimal("0.5"),
) -> Decimal:
    """Greater of actual and volumetric weight, rounded up to the carrier's step.

    `volumetric_divisor` is in cm3/kg and comes from the carrier contract --
    never hardcode it. Dimensions are in centimetres.
    """
    actual = sum((p.weight_kg for p in packages), Decimal("0"))
    volumetric = sum(
        (p.length_cm * p.width_cm * p.height_cm / volumetric_divisor
         for p in packages),
        Decimal("0"),
    )
    greater = max(actual, volumetric)
    steps = (greater / rounding_step).to_integral_value(rounding=ROUND_HALF_UP)
    return steps * rounding_step

Three things that repeatedly bite:

The divisor belongs to the contract, not to your code. Different carriers, different divisors; the same carrier may give you a better divisor on a renegotiated rate. Store it on the carrier rate record and pass it in.

Store what you measured, not just what you charged. Keep actual weight, every package's dimensions, the divisor used, and the computed chargeable weight — all of it, on the record. When a customer disputes a bill three months later, "the system says 47kg" is not a defence. "You declared 30kg, we measured 44kg actual and 47kg volumetric at a 6000 divisor, here is the weighbridge timestamp" ends the conversation.

Reweighing is normal and must be modelled. The shipper declares a weight. The warehouse measures a different one. The carrier measures a third. All three are real data points that arrive at different times and all three affect billing. A single weight column cannot hold that, and overwriting the declared value destroys the evidence you need for the dispute.

Status columns lie; event logs do not

The status of a consignment is not a property you set. It is a conclusion you draw from events.

class TrackingEvent(models.Model):
    consignment = models.ForeignKey(
        Consignment, related_name="events", on_delete=models.CASCADE,
    )
    leg = models.ForeignKey(Leg, null=True, blank=True, on_delete=models.SET_NULL)

    code = models.CharField(max_length=32)      # normalised internal vocabulary
    raw_code = models.CharField(max_length=64)  # exactly what the carrier sent
    description = models.TextField(blank=True)

    # When it happened where it happened, plus the zone, because "14:00" is
    # meaningless without knowing whose 14:00.
    occurred_at = models.DateTimeField()
    occurred_tz = models.CharField(max_length=64)
    location = models.ForeignKey("Location", null=True, on_delete=models.SET_NULL)

    # Provenance. Non-negotiable in a domain where events are evidence.
    source = models.CharField(max_length=32)  # carrier_api, edi, scan, manual
    source_reference = models.CharField(max_length=128, blank=True)
    recorded_at = models.DateTimeField(auto_now_add=True)
    recorded_by = models.ForeignKey(
        "users.User", null=True, blank=True, on_delete=models.SET_NULL,
    )

    class Meta:
        constraints = [
            # Makes carrier webhook redelivery a no-op.
            models.UniqueConstraint(
                fields=["consignment", "source", "source_reference"],
                name="unique_event_per_source_reference",
            )
        ]
        indexes = [models.Index(fields=["consignment", "occurred_at"])]

Why this shape:

Out-of-order arrival is the normal case, not an edge case. A "departed origin" event routinely arrives after "arrived at hub" because two carrier systems batch their pushes differently. If you derive status by "latest event wins by received time", your tracking page will move backwards in front of customers. Order by occurred_at, not by insertion.

Keep the raw code alongside your normalised one. You need a clean internal vocabulary to build UI and SLAs on. You also need the carrier's exact string when their support desk asks what you received. Normalising destructively means you can never reconcile with the carrier again.

Provenance is the whole point. A scan by a named warehouse operator, an EDI message, and a phone call typed in by a clerk are not equally reliable. Recording source lets you weight them, audit them, and answer "who told us that?" — which, in a claim, is the only question.

Then derive the status:

def current_status(consignment) -> str:
    latest = (
        consignment.events
        .exclude(code__in=EXCEPTION_CODES)
        .order_by("-occurred_at", "-recorded_at")
        .first()
    )
    return STATUS_BY_EVENT_CODE.get(latest.code, "in_transit") if latest else "booked"

Cache it if you need to — a denormalised status column is fine as a cache. It is only harmful as the truth.

Documents are records, not attachments

In freight, the document is often the legally operative thing. An Air Waybill is a contract of carriage. An original Bill of Lading is a document of title — whoever holds it can claim the goods. A Certificate of Origin determines the duty rate.

So a documents table with a file field and a type string is not enough:

class TransportDocument(models.Model):
    class Kind(models.TextChoices):
        MASTER_AWB = "master_awb"
        HOUSE_AWB = "house_awb"
        BILL_OF_LADING = "bill_of_lading"
        COMMERCIAL_INVOICE = "commercial_invoice"
        PACKING_LIST = "packing_list"
        CERTIFICATE_OF_ORIGIN = "certificate_of_origin"
        CUSTOMS_DECLARATION = "customs_declaration"

    kind = models.CharField(max_length=32, choices=Kind.choices)
    number = models.CharField(max_length=64)
    issued_by = models.ForeignKey("Party", on_delete=models.PROTECT)
    issued_at = models.DateTimeField()

    version = models.PositiveSmallIntegerField(default=1)
    supersedes = models.ForeignKey(
        "self", null=True, blank=True, related_name="superseded_by",
        on_delete=models.PROTECT,
    )
    is_original = models.BooleanField(default=False)  # matters for B/L
    file = models.FileField(upload_to="documents/%Y/%m/")
    content_hash = models.CharField(max_length=64)  # sha256 of the bytes

Never overwrite a document. Issue a new version pointing at what it supersedes. Amendments happen constantly — a corrected weight, a changed consignee, a fixed HS code — and the amendment history is frequently the thing under examination. Overwriting is destroying evidence.

The content_hash is cheap and pays for itself the first time somebody asks whether the PDF they were emailed is the one you hold.

Customs: record the basis, do not compute the duty

The instinct is to build a duty calculator. Resist it.

Duty depends on the HS classification, the declared value, the valuation method, the origin, current tariff schedules, trade agreements, and the discretion of an officer on the day. Tariff schedules change. Your calculator will be quietly wrong and someone will rely on it.

What is genuinely useful is recording the basis and the outcome:

class CustomsDeclaration(models.Model):
    consignment = models.OneToOneField(Consignment, on_delete=models.PROTECT)
    declaration_number = models.CharField(max_length=64, blank=True)

    declared_value_minor = models.BigIntegerField()
    declared_currency = models.CharField(max_length=3)
    valuation_basis = models.CharField(max_length=16)  # matches the incoterm
    country_of_origin = models.CharField(max_length=2)

    assessed_duty_minor = models.BigIntegerField(null=True, blank=True)
    assessed_at = models.DateTimeField(null=True, blank=True)
    assessed_reference = models.CharField(max_length=64, blank=True)

class LineItem(models.Model):
    consignment = models.ForeignKey(
        Consignment, related_name="line_items", on_delete=models.CASCADE,
    )
    description = models.TextField()
    hs_code = models.CharField(max_length=12)  # 6 international + national
    quantity = models.DecimalField(max_digits=12, decimal_places=3)
    unit_value_minor = models.BigIntegerField()
    net_weight_kg = models.DecimalField(max_digits=10, decimal_places=3)

HS codes are worth a note: the first six digits are internationally harmonised, and countries extend them with their own digits. Store the full string as declared and do not assume a length. Getting the classification right is a specialist skill, and the software's job is to capture it accurately and consistently — not to guess it.

Money, currencies, and rates at a point in time

Cargo billing is multi-currency by nature. You may quote in USD, get invoiced by the airline in USD, pay ground handling in NPR, and bill the customer in NPR.

Two rules:

Integer minor units, never floats. Same as any financial system.

Store the exchange rate you used and when you used it. Not a lookup at read time — the rate that was applied at quote, and the rate at invoice, as data on the record. A quote honoured three weeks later at a moved rate is a margin conversation, and you cannot have it if the rate is recomputed every time somebody opens the page.

class Charge(models.Model):
    consignment = models.ForeignKey(
        Consignment, related_name="charges", on_delete=models.CASCADE,
    )
    code = models.CharField(max_length=32)  # freight, fuel_surcharge, handling...

    amount_minor = models.BigIntegerField()
    currency = models.CharField(max_length=3)

    # Frozen at the moment of application, never recomputed.
    fx_rate = models.DecimalField(max_digits=18, decimal_places=8)
    fx_rate_at = models.DateTimeField()
    base_amount_minor = models.BigIntegerField()  # in your reporting currency

    is_payable = models.BooleanField()   # cost to us
    is_billable = models.BooleanField()  # revenue from customer

Splitting payable from billable on the same charge record is what lets you see per-consignment margin without a reconciliation job. Freight forwarding is a spread business; if your schema cannot express cost and revenue side by side, it cannot tell you whether a lane is profitable.

Integrating carriers: assume everything is unreliable

Carrier integrations are the least glamorous and most operationally significant part of the system.

Make every ingest idempotent. The UniqueConstraint on (consignment, source, source_reference) above means a redelivered webhook is a no-op. Carriers redeliver. Constantly.

Persist the raw payload before parsing it. Write the body to a staging table, acknowledge, then parse asynchronously. When a carrier changes a field without telling you — and they will — you can reprocess history instead of having lost it.

Normalise into your own vocabulary, and keep the mapping as data. A per-carrier code mapping table, editable without a deploy, beats a match statement in code that needs a release every time a carrier invents a status.

Never let a carrier's outage become your outage. Every carrier call goes through a timeout and a circuit breaker. Tracking degrades to "last known position, as of X" rather than a 500. Customers accept stale data; they do not accept a broken page.

The through-line

Looking back at every one of these decisions, they are variations on one idea: separate what happened from what you currently believe.

Events, measurements, and documents are immutable records of things that occurred, each with a source and a timestamp. Status, current location, chargeable weight, and margin are conclusions you derive from them, and conclusions can be recomputed when you learn something new.

Freight is a domain where you are still arguing about a shipment ninety days after it was delivered, using data reported by five organisations that do not agree. A system built on mutable state cannot reconstruct what it knew and when. One built on an append-only record of attributed facts can answer any of those questions months later — which, when there is money on the table, is the entire value of the software.