Bin-Level Lot Genealogy

Bin-level genealogy is built from four primitives. Get any of them wrong and the graph becomes inconsistent — the rebuild cost during a recall is days of paper-trail reconciliation when the FDA expects an answer in hours.

Every change to the physical world that affects material identity or location must write a genealogy event. Skipping events is the silent killer — the graph drifts from reality + the gap surfaces during the recall when it matters most.

Bin-level genealogy is a directed acyclic graph (DAG) where nodes are containers + batches + work orders + shipments, and edges are sub-charge / split / merge / consume / ship events. Postgres recursive common table expressions (CTEs) traverse the graph backward (input lots that fed a batch) + forward (batches that consumed material from a container) with millisecond response times when the indexes are right.

Forward query (recall scenario): 'Container 940123456789012345 has been recalled — give me every batch + every shipment + every customer that received material derived from it.' The recursive CTE walks sub-charge events from the container forward to WOs, then from WOs to finished-product lots, then from finished-product lots to distribution shipments, then from shipments to customer accounts. Returned as a tree with hop count per node; total query time under 500 ms on a 10-million-row event table with correct (container_id, event_type, utc) compound indexes.

Backward query (investigation scenario): 'Finished-product lot FP-2025-0341 failed dissolution at QC — give me every input container that contributed material, with masses + dates + suppliers.' The recursive CTE walks sub-charge events backward from the WO to consumed containers, then from consumed containers to their receipt CTEs, then to supplier + PO + ASN. Returned as a per-component tree with quantities + percentage contribution per source container.

1. Indexes — (container_id, utc, event_type) compound + (wo_id, utc) compound + (parent_lot_id, utc) compound; partial indexes on state IN ('intact', 'opened') for live inventory queries.
2. Materialised view — daily-refreshed forward + backward graph snapshots per container for hot-path recall queries; live query for current-state + investigation depth.
3. Recursion limits — CTE depth-capped at 20 hops to prevent runaway queries on circular re-pack chains; circular detection raises an audit-trail anomaly flag.
4. Snapshot semantics — every query records the as-of timestamp; queries replay history by filtering events to event.utc <= as_of, so a 2024-09-15 recall investigation sees the graph as it was on that date even if events written later edited container states.
5. Performance budget — recall queries on a 50-million-row event table must complete in <2 s for forward + <2 s for backward; investigation queries with sub-charge mass aggregation under <5 s.
6. Recursive CTE template — WITH RECURSIVE walk AS (SELECT … FROM events WHERE container_id = $1 UNION ALL SELECT … FROM events e JOIN walk w ON e.parent_event_id = w.event_id) SELECT * FROM walk; — Postgres optimiser handles cycle detection if we set the right ordering + LIMIT clauses.

1. Lot-only genealogy — 12 IBCs of one lot all roll up to lot_id; recall pulls all 12 instead of the one affected; cost of unnecessary recall scope is the §211.196 finding-magnet.
2. Container ID is a manual hand-written sticker — not scannable, not unique, not GS1-compliant; first time the container moves between sites the chain breaks.
3. Open events not captured — container opened on the floor but no 'open' event written; subsequent sub-charges have no container state context; clean-validation of the container before next use cannot be reconstructed.
4. Sub-charge events aggregated to lot level — 14 individual draws from one drum captured as a single 'drum 90% consumed' summary; per-batch traceability lost; investigations can't isolate which draw produced the deviation.
5. Split / merge events ad-hoc + paper-only — operator splits an IBC into 5 totes on the warehouse floor; child container IDs created in Excel; events never written to the system; graph diverges from reality permanently.
6. Return-to-inventory without destination container ID — residual mass returned but the destination is logged as 'back to source lot'; if the source lot is now in 8 containers, the system doesn't know which one received the return; next pick from any of the 8 has unknown provenance.
7. Re-pack lineage not preserved — damaged container is repacked into new container; new container_id created but lineage to old container_id not captured; if the old container's lot has an issue, the recall query misses the re-packed child.
8. Quarantine state not RLS-enforced — quarantined container_id appears in pick lists because the application-layer filter has a bug; operator picks + dispenses quarantined material; §211.42 + 211.100 + 211.184 + 211.192 finding cluster with potential market recall.

• Container-ID coverage — fraction of inbound containers with GS1-SSCC or facility-unique scannable ID at receipt (target 100%); manual sticker coverage = data-integrity gap.
• Event capture rate — fraction of physical events (receipt + put-away + move + open + sub-charge + close-down + consume + return + split + merge + scrap + re-pack) captured to the genealogy event table within 5 min of physical execution (target ≥99%).
• Recall query response time — median time to return forward + backward genealogy result for a single container_id (target ≤2 s on production-size event table); slower = §211.196 + FSMA-204 + 820.198 risk.
• Recall scope reduction ratio — average ratio of (bin-level recall scope) ÷ (lot-level recall scope) across past 12 months of mock + real recalls (target ≤0.3; lower is better — proves the bin-level data prevents over-recall).
• Mock-recall pass rate — fraction of quarterly mock recalls completed within FDA / EMA expected response time (24 h for FDA, 48 h for EMA) with full forward + backward chain (target 100%).
• Split / merge QA-approval coverage — fraction of split + merge events with documented QRM + QA approval reference (target 100% for regulated material; 0% merges for penicillin / cytotoxic / hormone / live-biologic).
• Return-to-inventory destination_container_id capture — fraction of returns with explicit destination container ID (target 100%); 'back to source lot' returns are graph breakage.
• Quarantine RLS enforcement test — quarterly automated test confirms quarantined containers cannot appear in pick lists or be sub-charged (target 100% block); service-role-key bypass test must also fail (RLS works at DB layer).

1. Container master schema — container_id (GS1 SSCC or facility-prefixed serial, unique), parent_lot_id, supplier_id, initial_mass, current_mass, current_state (intact / opened / consumed / split / merged / scrapped / returned / quarantined / re-packed / under-investigation), current_location_id, cofa_doc_ref, expiry_date, retest_date, restricted_access_flag, container_class (drum / IBC / pail / tote / sub-container).
2. Event schema — genealogy_event (event_id, container_id, event_type, utc, operator_id, supervisor_id, qa_id, wo_ref, dispense_result_id, mass_before, mass_after, mass_delta, from_location, to_location, parent_event_id, parent_container_id, child_container_ids[], reason_code, deviation_id, qrm_doc_ref, qa_approval_id, audit_trail_id) — append-only, RLS-protected, indexed for graph traversal.
3. Receipt workflow — receiver scans PO + ASN + container GS1 barcode at dock; system creates container_id row + writes receipt event with supplier + PO + ASN + CoA ref + mass; container state = intact; auto-routes to put-away location per FEFO + zone rules.
4. Move + put-away — every scan-move writes a move event with from + to + operator; no supervisor required for routine moves; movements within an open WO line stage are tracked as line-side movements with WO ref.
5. Open + sub-charge — when operator selects container for first weighment, kiosk writes open event (container state → opened); every dispense_result references the source container_id + writes a sub-charge event; container current_mass auto-decrements.
6. Close-down + consume — at end of WO or shift, operator triggers close-down (container re-sealed + verified); if current_mass ≤ residue envelope, auto-prompts consume event (state → consumed); otherwise container remains opened-closed-down (re-openable).
7. Return-to-inventory — operator initiates return; UI mandates destination_container_id selection (default: original source if still available); creates return event + paired inventory_transaction row; orphan returns RLS-blocked.
8. Split workflow — operator triggers split from kiosk; UI walks through parent → child container creation (new GS1 SSCC per child or facility-issued); supervisor + QA e-sig required for regulated material; creates split event with parent_container_id → child_container_ids[].
9. Merge workflow — operator triggers merge; UI mandates QRM doc reference + QA approval ID; RLS blocks merge for penicillin / cytotoxic / hormone / live-biologic regardless of approval; creates merge event with child_container_ids[] → parent_container_id.
10. Quarantine workflow — QA triggers quarantine on container_id; state → quarantined; RLS blocks any subsequent pick / sub-charge / move (except QA-authorised quarantine-cage move); paired deviation_id required.
11. Recall + investigation API — server function takes container_id (or wo_id or finished_lot_id) + direction (forward / backward) + optional as_of_timestamp; executes recursive CTE; returns graph with hop count + masses + dates; PDF report renders the graph + chain-of-custody for regulatory submission.
12. Mock-recall quarterly drill — automated job picks a random container_id from past 6 months of events; executes full forward + backward query; logs response time + completeness; surfaces on Quality dashboard; chronic miss triggers index + materialised-view tuning.
13. §211.192 + 820.198 + FSMA-204 review integration — every batch record render auto-includes the bin-level genealogy graph for every consumed container; reviewer can drill into any container's full event chain; closing the batch records the genealogy snapshot to immutable storage.

• Q: Is lot-level genealogy enough if we're not on the FSMA-204 Food Traceability List? A: Lot-level meets the literal §211.184 + 820.65 minimum. But §211.196 + 820.198 recall expectations + the foreseeable-risk doctrine increasingly drive inspectors to ask 'why didn't you use bin-level when it was achievable?' — especially after one expensive over-recall. The cost differential is small relative to one avoided unnecessary recall.
• Q: How small is 'too small' for a container_id? A: The smallest physical unit that is independently moved, opened, or sub-charged. For a 25 kg drum that's drum-level. For a 1000 L IBC that's IBC-level. For a 5 g vial of reference standard that's vial-level. The rule is: if it can move independently, it gets an ID.
• Q: Can we use facility-private serials instead of GS1 SSCC? A: For internal-only material that never crosses a site or supply-chain boundary, yes. The moment material moves to another site, a contract manufacturer, or a customer, GS1 SSCC is the only interoperable standard that scans reliably + carries through the chain. We recommend GS1 SSCC from day one to avoid the future migration.
• Q: How do we handle continuous-flow material (no discrete containers)? A: Time-bin the flow into 'virtual containers' — every N minutes or N kg of throughput is a virtual container with a synthetic container_id + start + end timestamps. Sub-charges reference the virtual container by timestamp. This pattern works for continuous granulation, continuous fluid-bed, continuous tabletting, and many fermentation / bioreactor processes.
• Q: What's the audit-trail retention for genealogy events? A: §211.180(c) = 1 year past expiry or 3 years past distribution, whichever later. FSMA-204 = 24 months from event date. EU GMP = retention per the marketing-authorisation regime, typically 5 years past last distribution. We default to the longest applicable + recommend 7 years for regulated facilities.
• Q: How do we genealogy-track returns from customers? A: A customer return is a receipt event with source = customer + reverse-trace to the original shipment + finished-lot. The returned material is quarantined by default + cannot re-enter manufacturing without QA disposition + risk assessment + likely re-test. The genealogy chain stays connected so subsequent investigations can trace 'which customer batch returned this material'.
• Q: Does bin-level genealogy work for active raw materials AND for packaging components? A: Yes — packaging components (vials, stoppers, caps, labels, cartons, inserts) follow identical genealogy patterns. The DSCSA + EU FMD + UDI regimes all require package-level traceability for finished products; the upstream component genealogy is the input to the finished-product serial number.