A per-order margin ledger: reconciling what you quoted against what the carrier billed

Shipping cost in most ecommerce backends is an estimate that nobody checks.

At checkout you show a number from a rate table. The parcel ships. The carrier sends an invoice six weeks later with a different number on it. Nobody joins the two, because joining them means matching a carrier's line-item PDF against your order ids, and so the difference just disappears into cost of goods sold and the margin dashboard keeps lying.

The fix is unglamorous and it pays for itself: a ledger that records the quote, the actual, and the variance per shipment, and a reconciliation step that fills in the actual when the invoice arrives. Here is the shape that worked in a China-origin small-parcel operation.

Three records, not one

create table shipment_cost (
  shipment_id      bigint primary key references shipment(id),
  quoted_at        timestamptz not null,
  quoted_amount    numeric(12,2) not null,
  quoted_currency    char(3) not null,
  quote_basis      jsonb not null,      -- divisor, measured dims, weight, lane, rate card version
  billed_amount    numeric(12,2),
  billed_currency    char(3),
  billed_at        timestamptz,
  invoice_ref      text,
  variance         numeric(12,2) generated always as (
                     coalesce(billed_amount,0) - quoted_amount
                   ) stored,
  variance_reason  text,                -- dims_remeasured | surcharge | fuel | residential | reroute | unknown
  reconciled_at    timestamptz
);

The important column is quote_basis, and the important thing about it is that it is written once, at quote time, and never updated. Six weeks later, when the carrier's billed weight disagrees with yours, that JSON is the only evidence you have about what your number was actually computed from.

Store the rate card version in it too. Rate cards change, and a quote from March has to be reproducible against March's table, not today's. If you cannot reproduce the original number you cannot tell a pricing error apart from a rate change, which means you have no case to make when you want money back.

The variance is the whole product

Once the join exists, the queries you wanted become trivial:

-- where the money is actually leaking
select s.lane_code,
       count(*)                        as shipments,
       sum(c.variance)                 as total_leak,
       avg(c.variance)                 as avg_per_shipment,
       sum(c.variance) / nullif(sum(c.quoted_amount),0) as leak_ratio
  from shipment_cost c
  join shipment s on s.id = c.shipment_id
 where c.reconciled_at is not null
   and c.billed_at >= now() - interval '90 days'
 group by s.lane_code
 order by total_leak desc;

Run that once and you will learn something uncomfortable, usually one of three things.

Dimensional re-measurement. The carrier measured the parcel bigger than you did. This is the classic case, and it clusters: specific SKUs, specific packers, or a box type that bulges after taping. The fix is operational, not financial, and it is cheap once you can name the SKU.

Surcharges you never quoted. Residential, remote area, oversized, fuel adjustment. Each is legitimate and each can be priced into the checkout quote if you know it applies to that lane. The variance report is what tells you it applies.

Lane substitution. The service you quoted was unavailable, so something else was used. This one is a contract conversation rather than a code fix, and it is much easier to have with a count and a total attached.

Grouping by variance_reason is where the discipline matters. Free-text notes cannot be aggregated, so constrain the reasons to a small set and classify during reconciliation. The first month is manual. After that you write rules: a variance under a few percent with a matching billed weight is fuel, a variance with a larger billed dimension is re-measurement, and so on.

Matching invoice lines to shipments is the hard part

Carrier invoices do not carry your order id. They carry a tracking number, sometimes a reference, sometimes only a date and a weight. So reconciliation is an entity-resolution problem wearing a boring hat.

Match in tiers, and stop at the first that succeeds:

const matchers = [
  (line) => byTracking(line.trackingNumber),          // strongest; covers most volume
  (line) => byCarrierRef(line.reference),             // present when you set it on the label
  (line) => byFingerprint({                            // last resort, and it must be unique
    weight: line.billedWeight,
    shipDate: line.shipDate,
    destPostal: line.destPostal,
    skuCount: line.skuCount,
  }),
];

The fingerprint tier has to be rejected when it is ambiguous. If two shipments match, leave the line unreconciled and report the count. A confidently wrong join is worse than a visible gap, because the gap tells you to fix the reference field and the wrong join quietly corrupts a margin number somebody will make a decision from.

Persist the tier that matched, per line. When the same tracking number shows up on a later invoice, that is your duplicate-detection signal, and carriers do double-bill occasionally, usually around re-manifested parcels.

Make the unreconciled set visible

A ledger nobody audits decays. The report that keeps it honest is the short one:

select
  count(*) filter (where billed_amount is null)                      as awaiting_invoice,
  count(*) filter (where billed_amount is null
                     and quoted_at < now() - interval '75 days')     as overdue_unmatched,
  count(*) filter (where variance_reason = 'unknown'
                     and abs(variance) > 2)                          as unexplained
from shipment_cost;

overdue_unmatched grows when a carrier changes its invoice format and your parser silently stops matching. That number going up is the earliest warning you will get, and it costs nothing to watch.

We keep this against the small-parcel and one-piece fulfillment lanes FulfillNexa by SBT (fulfillnexa.com) runs out of three sites in China, Dongguan at 8,000 m² for ecommerce order volume, Suzhou at 13,000 m² for East China supplier consolidation and Shenzhen at 3,000 m² for oversized and sea-air handoffs, because the variance pattern is different in each: single-carton e-commerce parcels get re-measured, consolidated inbound gets re-manifested, and oversized movements attract surcharges that no per-order rate table predicts.

The useful outcome is not a prettier dashboard. It is that when a lane is quietly costing more than you are charging for, you find out in the second month instead of at the end of the year.