Two structural changes came out of this incident. Each was designed to close a specific detection gap identified during the investigation.
Change 1
Incoming lot validation introduced
Every new botanical lot is now tested at intake through single botanical distillation against the house baseline before it enters production. This moved detection upstream to the point where the ingredient could be rejected before use. Lots that match the baseline are approved. Lots that deviate are rejected.
Change 2
Fixed reference batch introduced for quality control
Each batch is now compared against a fixed, documented reference batch. Before this, batches were compared against a reference bottle selected from available stock, so the comparison point changed over time. This allowed gradual flavour drift to pass unnoticed across multiple batches.
Blast Radius Query
The query below traces lot PDI-JNP-220620 through the database from stock receipt to bottled output.
SELECT sr.supplier_lot_reference, b.batch_id, b.batch_date,
b.product_name, bo.sku, bo.bottles_filled
FROM stock_receipts sr
JOIN batch_ingredients bi ON sr.receipt_id = bi.receipt_id
JOIN batches b ON bi.batch_id = b.batch_id
JOIN batch_outputs bo ON b.batch_id = bo.batch_id
WHERE sr.supplier_lot_reference = 'PDI-JNP-220620'
ORDER BY b.batch_id, bo.sku;
Traces lot PDI-JNP-220620 from stock receipt through production to bottled output. Nine bottling runs. Four batches. Three products. 715 bottles quarantined.
The query runs in seconds because batch_ingredients was modelled as a junction table linking stock receipts to batches at the lot level. That makes the blast radius immediately visible. Without it, the same question would have to be answered manually by cross-referencing stock receipts, batch logs, production records, and bottling records under time pressure, with no guarantee of completeness.
Schema
PostgreSQL 16 schema supporting lot-level trace from supplier receipt to batch output. View full schema →
Assumptions That Failed
I assumed ingredient quality at receipt was good enough and that batch-level quality control would catch any problem later. That assumption delayed detection and allowed a contaminated lot to move across four batches.
I also assumed that comparing batches against available stock was sufficient. In practice, this created a shifting standard, allowing small deviations to pass across consecutive batches without triggering a failed check.