Reconciling Tank-Mix Batches Against Inventory

Problem statement

A tank mix is a single physical event that draws down several products at once. An operator loads a sprayer with a herbicide, an adjuvant, and a micronutrient, sprays 60 hectares, and the inventory system sees one batch. But the books do not carry a “tank mix” — they carry per-product stock, each product tied to a received lot. Unless that batch is decomposed into per-product quantities, the draw-down is either not recorded at all or is recorded as a lump that no lot can absorb, and the inventory ledger slowly drifts out of balance.

The second problem is that the theoretical draw and the actual draw rarely match to the gram. The recipe says a product should be applied at a fixed rate per hectare, so rate × area gives a theoretical quantity — but the tank was mixed by a human, the boom was calibrated last month, and a partial refill got rounded. A small gap is normal; a large one is a signal that the recipe was mis-loaded, a rate was fat-fingered, or a product is being over-applied past its label limit. This guide builds the module that decomposes a batch into per-product draw-downs, compares each product’s theoretical quantity against its measured usage with exact Decimal arithmetic, and flags any component whose variance exceeds a relative tolerance. It complements Tracking Pesticide Lot Numbers for Traceability, which takes each reconciled per-product quantity and links it to the specific lot it was drawn from.

Parameter reference table

Each value below changes whether a genuine mixing error is caught or a harmless rounding gap is treated as one. Recommended values assume canonical units handed over by the ingestion service.

Runnable implementation

The module below is fully typed, targets Python 3.10+, and uses only the standard library. It decomposes a batch into per-product theoretical draws, reconciles each against measured usage, and flags variance beyond tolerance.

from __future__ import annotations

import logging
import uuid
from dataclasses import dataclass
from decimal import Decimal, ROUND_HALF_UP

logger = logging.getLogger("inventory.tank_mix")


@dataclass(frozen=True)
class MixComponent:
    """One product in a tank-mix recipe, dosed per unit area."""
    product_name: str
    epa_reg_no: str | None
    rate_per_ha: Decimal      # canonical kg or L of product per hectare
    unit: str                 # "kg" or "L"


@dataclass(frozen=True)
class TankMixBatch:
    batch_id: str
    area_ha: Decimal
    components: list[MixComponent]


@dataclass(frozen=True)
class ComponentReconciliation:
    product_name: str
    unit: str
    theoretical: Decimal
    measured: Decimal
    variance: Decimal
    relative_variance: Decimal
    within_tolerance: bool


def theoretical_draw(component: MixComponent, area_ha: Decimal) -> Decimal:
    """Theoretical product quantity = rate x area, quantized to four decimals."""
    return (component.rate_per_ha * area_ha).quantize(
        Decimal("0.0001"), rounding=ROUND_HALF_UP
    )


def reconcile_batch(
    batch: TankMixBatch,
    measured: dict[str, Decimal],
    tolerance: Decimal = Decimal("0.05"),
) -> list[ComponentReconciliation]:
    """Decompose a batch into per-product draws and flag variance beyond tolerance."""
    correlation_id = str(uuid.uuid4())
    results: list[ComponentReconciliation] = []
    for component in batch.components:
        expected = theoretical_draw(component, batch.area_ha)
        actual = measured.get(component.product_name)
        if actual is None:
            # A recipe product with no measured usage cannot be reconciled:
            # flag it, never silently assume a zero draw-down.
            logger.error(
                '{"event":"missing_measurement","batch":"%s","product":"%s"}',
                batch.batch_id, component.product_name,
            )
            raise ValueError(f"{component.product_name}: no measured usage supplied")
        variance = (actual - expected).copy_abs()
        relative = (variance / expected) if expected else Decimal("0")
        ok = relative <= tolerance
        logger.info(
            '{"event":"component_reconciled","cid":"%s","batch":"%s","product":"%s",'
            '"theoretical":"%s","measured":"%s","variance":"%s","within_tolerance":%s}',
            correlation_id, batch.batch_id, component.product_name,
            expected, actual, variance, str(ok).lower(),
        )
        results.append(ComponentReconciliation(
            product_name=component.product_name,
            unit=component.unit,
            theoretical=expected,
            measured=actual,
            variance=variance,
            relative_variance=relative,
            within_tolerance=ok,
        ))
    flagged = [r.product_name for r in results if not r.within_tolerance]
    if flagged:
        logger.warning(
            '{"event":"batch_variance_flagged","batch":"%s","products":%s}',
            batch.batch_id, flagged,
        )
    return results

The reconciliation deliberately runs per component rather than on the batch total, because a total can net out: an over-draw of one product and an under-draw of another cancel, hiding two mixing errors behind a balanced sum. Reconciling each product against its own theoretical draw exposes both. Every quantity is a Decimal so rate × area and the variance are exact — a float path here would produce spurious sub-gram variances that either mask a real gap or trip a false flag.

Log patterns and observable signals

Each reconciliation emits structured JSON so any batch can be audited component by component.

Clean batch — every component within the 5% tolerance:

{"event": "component_reconciled", "cid": "3af0…", "batch": "MIX-7710", "product": "glyphosate", "theoretical": "54.0000", "measured": "54.9000", "variance": "0.9000", "within_tolerance": true}
{"event": "component_reconciled", "cid": "3af0…", "batch": "MIX-7710", "product": "AMS", "theoretical": "120.0000", "measured": "121.5000", "variance": "1.5000", "within_tolerance": true}

Flagged batch — one component’s measured usage runs past tolerance:

{"event": "component_reconciled", "cid": "9b12…", "batch": "MIX-7711", "product": "surfactant", "theoretical": "6.0000", "measured": "9.4000", "variance": "3.4000", "within_tolerance": false}
{"event": "batch_variance_flagged", "batch": "MIX-7711", "products": ["surfactant"]}

Missing measurement — a recipe product was never metered:

{"event": "missing_measurement", "batch": "MIX-7712", "product": "micronutrient"}

When triaging, treat batch_variance_flagged as the primary signal and read the paired component_reconciled line to see which direction the gap runs: a large positive measured-over-theoretical variance on a pesticide is the one to escalate, because it can push a product past its label rate. A steady low-level variance on the same product across many batches usually means a mis-set rate in the recipe rather than a mixing error — correct it at the source so the flag stops firing.

Safe override protocol

Occasionally a component legitimately exceeds tolerance — a field was re-treated after a washout, or a boom was deliberately run rich on a known-resistant weed patch under agronomic guidance. The override must record the higher measured draw honestly; it never edits the measurement down to fit, and it never widens the global tolerance.

Guard conditions, all mandatory:

1. Explicit reason, per component. The operator names the component and supplies a documented reason; the override applies only to that product in that batch, not the whole recipe.
2. Label-rate ceiling. The accepted measured quantity is checked against the product’s maximum label rate for the area; an override that would exceed it is rejected outright, because no reason justifies an over-label application.
3. Dry-run first. The override runs with persistence disabled and must produce a coherent reconciliation before it is allowed to write the draw-down.
4. Immutable audit trail. The batch id, component, accepted variance, reason, and approver are written to the append-only ledger and flagged for compliance review, consistent with EPA pesticide recordkeeping expectations.

def override_component_variance(
    result: ComponentReconciliation,
    max_label_qty: Decimal,
    reason: str,
    approver: str,
    dry_run: bool = True,
) -> ComponentReconciliation:
    """Accept a beyond-tolerance component, but never past the label ceiling."""
    if result.measured > max_label_qty:
        # No reason justifies an over-label application: reject the override.
        raise ValueError(
            f"{result.product_name}: measured {result.measured} exceeds label max {max_label_qty}"
        )
    logger.warning(
        '{"event":"variance_override","product":"%s","measured":"%s","reason":"%s",'
        '"approver":"%s","dry_run":%s}',
        result.product_name, result.measured, reason, approver, str(dry_run).lower(),
    )
    # Record the true measured value as accepted; the audit note carries the reason.
    return ComponentReconciliation(
        product_name=result.product_name,
        unit=result.unit,
        theoretical=result.theoretical,
        measured=result.measured,
        variance=result.variance,
        relative_variance=result.relative_variance,
        within_tolerance=True,  # accepted by review, not by silently loosening tolerance
    )

The label-rate ceiling is the guard that matters: an override can explain a variance, but it can never authorize an application beyond what the product’s registration allows.

Troubleshooting

• batch_variance_flagged fires on the same product every batch. Root cause: the recipe’s rate_per_ha is wrong, so the theoretical draw is consistently off. Remediation: correct the rate at the source in the recipe; do not raise tolerance, which would mask genuine mixing errors on other products.
• A component reconciles with a huge variance in the wrong direction. Root cause: theoretical and measured are in different units — one in kilograms of product, the other in litres of spray solution. Remediation: confirm both share the component’s canonical unit; reconcile product mass against product mass, never against solution volume.
• missing_measurement on a product that was applied. Root cause: the flow meter for that product did not report, or the measurement dictionary was keyed by a different product name. Remediation: align the measurement keys to the recipe’s product_name; never let the code default a missing product to zero, which would hide an unrecorded draw-down.
• Variances that should be zero come out as tiny non-zero numbers. Root cause: a float entered the rate or area path upstream. Remediation: ensure rate_per_ha and area_ha are Decimal end to end; the quantized product is exact only when its inputs are.

Frequently asked questions

Why reconcile each product separately? Because a batch total can net out — an over-draw and an under-draw cancel, hiding two errors behind a balanced sum. Per-component reconciliation exposes both and lets each product link to its own lot.

How is the theoretical draw computed? As rate_per_ha × area_ha in exact Decimal, quantized to four decimals, then compared against measured usage with the absolute variance tested against a relative tolerance.

What happens when a component exceeds tolerance? It is flagged for review and may be accepted through the override protocol with a documented reason and approver, but never past the product’s label-rate ceiling.

Related

• Tracking Pesticide Lot Numbers for Traceability — links each reconciled per-product draw-down to the specific received lot it came from.
• EPA/USDA Rule Mapping — the versioned rule set defining label-rate ceilings and the recordkeeping fields a reconciled draw-down must carry.

Up: Input Inventory Ingestion · Farm Data Ingestion & Field Boundary Synchronization