Resolving Orphaned Receiving Events in FSMA 204 Recall Queries

An orphaned receiving event is the most common way a lot lineage fractures. A facility records a Receiving CTE that names an immediate previous source under 21 CFR § 1.1345, but no Shipping event anywhere in the graph produced that traceability lot code. The one-back edge points at a node that does not exist, so reconstruction reports a dangling reference and the chain has a hole. During a recall query this is not a cosmetic warning: the receiving facility knows it received product, but the query cannot trace that product back to its origin, so the root-cause search stops one hop short of where it needs to go.

This guide diagnoses why receiving events go orphaned, reproduces the defect with realistic KDE payloads, implements a reconciliation join that recovers the recoverable cases and cleanly isolates the rest, and gives you a nightly continuity query that catches new orphans before a recall depends on them. It extends the broken-link handling introduced in the parent One-Up, One-Back Chain Reconstruction guide, and it feeds the Fallback Routing Logic that decides what to do when a source genuinely cannot be found.

Root Cause Analysis

Orphaned receiving events almost always trace to one of three causes, and distinguishing them is the whole job because each has a different fix. The first is a quarantined upstream shipping event. The shipper’s own ingestion pipeline may have rejected the shipping record — a naive timestamp, a bad unit of measure, a lot code that failed schema validation — and routed it to quarantine instead of committing it. From the receiver’s side everything looks normal: it recorded a valid previous source. But the counterpart shipping event never reached the shared graph, so the edge dangles. The shipping record is not lost; it is sitting in the shipper’s quarantine store waiting for remediation, which is why checking quarantine is the first reconciliation step.

The second cause is timing. Receiving and shipping events arrive through independent feeds on independent schedules. A truck can be unloaded and its receiving event committed before the shipper’s nightly batch uploads the matching shipping event, producing a transient orphan that resolves itself hours later. Treating a fresh orphan as a permanent break generates false alarms; treating a week-old orphan as merely late hides a real gap. The reconciliation must therefore weigh the age of the orphan against the expected feed latency. The third cause is a mismatched traceability lot code: both events exist and both are committed, but their lot codes do not compare equal because of leading zeros, inconsistent casing, stray whitespace, or one side using a GTIN-plus-lot format where the other uses an internal code. The physical handoff happened and both records are valid; only the join key disagrees, which is the most recoverable case of all and the one a naive equality join silently misses. The diagram below shows the orphaned edge and the three outcomes reconciliation can produce.

Figure — orphaned receiving edge and reconciliation outcomes:

Minimal Reproducible Example

The payloads below reproduce all three causes at once. RCV-1 references a source whose shipping event is committed but under a differently formatted lot code (rom_8842 versus ROM-8842). RCV-2 references a source whose shipping event is still in quarantine. A naive equality join over committed shipping events flags both as orphans, even though one is genuinely recoverable.

from decimal import Decimal

committed_shipping = [
    # note the lot code formatting: lowercase, underscore
    {"tlc": "rom_8842", "cte": "Shipping", "loc": "0614141000012",
     "ts": "2026-03-02T13:05:00+00:00", "qty": Decimal("30")},
]
quarantined_shipping = [
    # rejected upstream for a naive timestamp; never committed
    {"tlc": "SPN-4415", "cte": "Shipping", "loc": "0614141000037",
     "ts": "2026-03-02 08:00:00", "qty": Decimal("18")},
]
committed_receiving = [
    {"tlc": "ROM-8842", "cte": "Receiving", "loc": "0614141000029",
     "ts": "2026-03-03T09:40:00+00:00", "qty": Decimal("30")},
    {"tlc": "SPN-4415", "cte": "Receiving", "loc": "0614141000029",
     "ts": "2026-03-03T10:10:00+00:00", "qty": Decimal("18")},
]

def naive_orphans(receiving, shipping):
    shipped = {s["tlc"] for s in shipping}
    return [r["tlc"] for r in receiving if r["tlc"] not in shipped]

print(naive_orphans(committed_receiving, committed_shipping))
# -> ['ROM-8842', 'SPN-4415']  (ROM-8842 is a false orphan; only SPN-4415 is real)

The naive join reports two orphans, but ROM-8842 is a false positive — its shipper exists, just under rom_8842. Recalling on this output would either overreport gaps or, worse, cause an operator to dismiss the real SPN-4415 orphan as noise among false alarms.

Fix Implementation

The reconciliation join fixes the false positive by normalizing lot codes before comparison, and it distinguishes the quarantined case from a true orphan by consulting the quarantine store. Only what survives all three checks is handed to fallback routing.

from __future__ import annotations

import logging
import re
from datetime import datetime

logger = logging.getLogger("fsma204.orphan_reconciliation")


def normalize_tlc(code: str) -> str:
    """Canonicalize a lot code for join comparison only.

    Strips case, whitespace, and separator style so rom_8842 and
    ROM-8842 compare equal. The stored code is never mutated.
    """
    return re.sub(r"[\s_-]+", "", code).upper()


def reconcile_receiving(
    receiving: list[dict[str, object]],
    committed_shipping: list[dict[str, object]],
    quarantined_shipping: list[dict[str, object]],
) -> dict[str, list[dict[str, object]]]:
    shipped = {normalize_tlc(str(s["tlc"])): s for s in committed_shipping}
    quarantined = {normalize_tlc(str(s["tlc"])) for s in quarantined_shipping}

    resolved: list[dict[str, object]] = []
    held: list[dict[str, object]] = []
    orphaned: list[dict[str, object]] = []

    for rec in receiving:
        key = normalize_tlc(str(rec["tlc"]))
        if key in shipped:
            # false orphan: shipper exists under a different code format
            resolved.append({"receiving": rec, "shipping": shipped[key]})
            logger.info("RECONCILED | recv_tlc=%s | matched=%s",
                        rec["tlc"], shipped[key]["tlc"])
        elif key in quarantined:
            # recoverable: chase the upstream quarantine remediation
            held.append(rec)
            logger.warning("SHIPPER_QUARANTINED | recv_tlc=%s", rec["tlc"])
        else:
            # genuine gap: no shipper anywhere
            orphaned.append(rec)
            logger.error("ORPHAN_RECEIVING | recv_tlc=%s | loc=%s",
                         rec["tlc"], rec["loc"])

    return {"resolved": resolved, "held": held, "orphaned": orphaned}

Normalizing on a copy of the key — never on the stored record — recovers ROM-8842 as a true match without corrupting the audited lot code. The quarantine check reclassifies SPN-4415 as held rather than a permanent orphan, because its shipping record exists and only needs upstream remediation to commit. Anything that reaches the orphaned bucket is a real gap with no candidate shipper, and that is the only bucket handed to the Fallback Routing Logic, which decides whether to widen the recall to every lot handled at the receiving location in the window or hold the query pending investigation. A timing-related orphan is handled by re-running reconciliation on the next feed cycle: a held or orphaned record that resolves once the late shipping event lands simply moves to resolved on the following pass.

Verification Steps

Prove reconciliation worked, then keep it proven with a nightly continuity query. The assertions below confirm the three buckets are sorted correctly for the reproduction above.

result = reconcile_receiving(
    committed_receiving, committed_shipping, quarantined_shipping
)
assert [r["receiving"]["tlc"] for r in result["resolved"]] == ["ROM-8842"]
assert [r["tlc"] for r in result["held"]] == ["SPN-4415"]
assert result["orphaned"] == []
print("reconciliation verified:", {k: len(v) for k, v in result.items()})

For ongoing assurance, a nightly continuity query over the committed ledger catches orphans introduced since the last run, before any recall depends on the graph. This left-anti-join returns every receiving event with no matching shipping event once lot codes are normalized, filtered to those older than the expected feed latency so transient timing orphans are excluded.

-- Nightly trace-continuity check: receiving events with no shipper.
SELECT r.traceability_lot_code,
       r.location_id,
       r.event_timestamp
FROM   receiving_events r
LEFT   JOIN shipping_events s
       ON  upper(regexp_replace(s.traceability_lot_code, '[\s_-]+', '', 'g'))
         = upper(regexp_replace(r.traceability_lot_code, '[\s_-]+', '', 'g'))
WHERE  s.traceability_lot_code IS NULL
  AND  r.event_timestamp < now() - interval '24 hours'
ORDER  BY r.event_timestamp;

A non-empty result set is an actionable list of confirmed orphans, each older than the 24-hour feed window, ready to route to fallback. Wiring this query into the mock-drill schedule turns orphan detection into a standing control rather than something discovered mid-recall. Every row it returns should correspond to exactly one entry in the reconciliation orphaned bucket; a divergence between the two means the normalization rules have drifted out of sync and must be realigned.

Related Edge Cases

• Orphaned shipping events. The mirror defect: a Shipping event names a subsequent recipient that never recorded a matching Receiving event. The same reconciliation applies with the join sides swapped, and a genuine orphan here means product left a facility with no confirmed downstream custody.
• Partial-quantity mismatch. The lot codes match but the received quantity exceeds the shipped quantity, implying a merged or mislabeled lot. Reconcile the edge but flag the quantity delta for the data-quality layer rather than treating it as an orphan.
• Split shipments. One shipping event fans out to several receiving events at different locations. This is valid, not an orphan — confirm each receiving event matches the single shipper before widening any scope.

Frequently Asked Questions

Related

• One-Up, One-Back Chain Reconstruction for FSMA 204 — the parent guide whose gap detection surfaces these orphaned edges.
• Reconstructing trace chains with NetworkX — how a disconnected component appears in a DiGraph, the graph-level view of an orphan.
• Fallback Routing Logic — decides how a recall responds to a confirmed orphan with no recoverable shipper.
• Schema Validation Rules — the ingestion gate whose quarantine store holds the missing upstream shipping events.
• FSMA 204 architecture reference — the § 1.1340 and § 1.1345 KDEs whose disagreement produces an orphan.

Up: One-Up, One-Back Chain Reconstruction — this guide resolves the most common broken link the parent reconstruction detects.