Biennial Inventory Reconciliation

Every registrant that handles controlled substances must take an actual physical count of its stock at least once every two years and reconcile that count against its perpetual records. The requirement in 21 CFR § 1304.11(c) is deceptively simple to state and unforgiving in practice: the biennial inventory is a complete and accurate record of all controlled substances on hand as of the count date, and it only has evidentiary value if the system it is compared against can produce the balance it held at that same instant. This reference sits within Regulatory Reporting & DEA Submissions and treats reconciliation as a fold over an append-only event ledger: the on-hand balance at the count timestamp is computed from history, never read from a mutable running total that may have drifted.

Regulatory Context & Compliance Boundaries

The biennial count is one obligation inside a continuous recordkeeping regime, and a reconciliation pipeline must honor each of the surrounding clauses rather than treating the count as a standalone event.

The compliance boundary that engineers most often miss is that the system side of the reconciliation is not “current stock.” It is the stock the ledger says existed at the count timestamp. If a count is taken at 07:00 on a Monday and reconciled against a balance materialized at 14:00, every dispense in those seven hours becomes a phantom variance. Computing the balance as of the count instant is therefore not an optimization — it is the difference between a defensible reconciliation and a fabricated one. That computation reads the same append-only stream established by the event-sourcing foundations of the platform, and its integrity rests on the audit anchoring that makes the historical events tamper-evident.

The Biennial Count Requirement

The rule sets a floor, not a ceiling: at least every two years. Most operators count controlled substances far more often — perpetual cycle counts, shift-change counts on Schedule II — but the biennial inventory is the one with a specific statutory form. Three properties define it.

• A fixed effective time. The inventory must state whether it was taken at the opening or close of business, fixing a single instant that the count reflects. That instant is the timestamp the ledger fold targets.
• Exactness by schedule. Under 21 CFR § 1304.11(e)(1), Schedule II substances require an exact count of every dosage unit. Schedules III–V may be estimated — unless the container holds more than 1,000 dosage units, in which case an exact count is required for that container too.
• Completeness. Every controlled substance on hand must appear, including expired stock awaiting destruction and stock in quarantine. Omission is itself a recordkeeping failure.

These properties map cleanly onto data structures: the effective time is a timestamp parameter to the fold; the exactness rule is a per-schedule tolerance; and completeness is an assertion that the set of counted NDCs covers the set of NDCs the ledger shows with a non-zero balance.

Reconciling Physical Counts Against Event-Sourced Balances

In a CRUD system the “system quantity” is a mutable column, and reconciling against it means trusting whatever the last write happened to leave there — including writes that landed after the count was taken. An event-sourced ledger removes that ambiguity. The on-hand balance is a pure function of the events up to a chosen timestamp, so the reconciliation compares two independently derived numbers: the physical observation and the deterministic fold.

The deterministic workflow for a biennial reconciliation is:

1. Fix the count timestamp T. Record whether the inventory is taken at opening or close of business and pin the exact instant in UTC.
2. Capture the physical count. Record dosage units per NDC, tagging each line with the schedule so the classifier can apply the right tolerance.
3. Fold the ledger to T. Replay every receipt, dispense, waste, transfer, and loss event with an effective time at or before T, accumulating a signed balance per NDC.
4. Align the domains. Take the union of NDCs present in the count and in the folded balance; an NDC in one set but not the other is itself a finding (uncounted stock, or stock the ledger does not know about).
5. Compute per-NDC variance. For each NDC, variance = physical − folded.
6. Classify by schedule. Apply the exact tolerance to Schedule II and the estimate tolerance to Schedules III–V, marking each line reconciled or flagged.
7. Emit and retain the report. Bind the count, the folded balance, and the variance result into a hashed, WORM-retained record.

The pivotal step is the fold at T. Because it is a pure function of immutable history, two independent runs produce the same balance, and an inspector re-running it arrives at the same number the reconciliation used.

Variance Classification: Schedule II vs III–V

The regulation deliberately holds Schedule II to a stricter standard, and the classifier must encode that asymmetry rather than applying a single global tolerance. A Schedule II variance of even one dosage unit is a genuine discrepancy that demands explanation; a small Schedule V variance within the estimation allowance is expected noise.

A flagged Schedule II variance is exactly the kind of unexplained shortage that may become a reportable event. When investigation cannot account for it, the finding feeds the DEA Form 106 theft and loss reporting workflow, which freezes the discrepancy against a snapshot before filing. The classifier’s job is to route confidently: reconcile the expected noise, and escalate the genuine discrepancies with the schedule context an investigator needs.

Production Python Implementation

The implementation folds an append-only event stream to a point-in-time balance per NDC, then diffs that balance against a physical count with a schedule-aware tolerance. Every event carries an effective timestamp and a signed quantity; the fold ignores anything after T. Logging is structured and PHI-free, and the whole reconciliation is bound to a SHA-256 audit hash.

from __future__ import annotations

import hashlib
import json
import logging
from collections import defaultdict
from dataclasses import dataclass
from datetime import datetime, timezone
from enum import Enum

logger = logging.getLogger("pharmacy.reporting.biennial")


class EventType(str, Enum):
    RECEIVE = "RECEIVE"       # +
    DISPENSE = "DISPENSE"     # -
    WASTE = "WASTE"           # -
    TRANSFER_IN = "TRANSFER_IN"    # +
    TRANSFER_OUT = "TRANSFER_OUT"  # -
    LOSS = "LOSS"             # -


_SIGN = {
    EventType.RECEIVE: +1, EventType.TRANSFER_IN: +1,
    EventType.DISPENSE: -1, EventType.WASTE: -1,
    EventType.TRANSFER_OUT: -1, EventType.LOSS: -1,
}

# Estimate tolerance for Schedules III–V (fraction of folded balance).
_ESTIMATE_TOLERANCE = 0.02


@dataclass(frozen=True)
class LedgerEvent:
    ndc_11: str
    schedule: str            # "II".."V"
    event_type: EventType
    quantity: int            # always positive; sign derived from event_type
    effective_ts: str        # ISO-8601 UTC
    sequence: int            # monotonic


@dataclass(frozen=True)
class VarianceLine:
    ndc_11: str
    schedule: str
    folded: int
    physical: int
    variance: int
    status: str              # "RECONCILED" | "FLAGGED"


def fold_balance_at(events: list[LedgerEvent], count_ts: str) -> dict[str, int]:
    """Fold events with effective_ts <= count_ts into a per-NDC balance.

    Pure function of immutable history: re-running yields the same balance,
    satisfying the point-in-time reconstruction 21 CFR § 1304.11 demands.
    """
    cutoff = datetime.fromisoformat(count_ts)
    balances: dict[str, int] = defaultdict(int)
    # Deterministic order: by (effective_ts, sequence) so ties resolve stably.
    for ev in sorted(events, key=lambda e: (e.effective_ts, e.sequence)):
        if datetime.fromisoformat(ev.effective_ts) <= cutoff:
            balances[ev.ndc_11] += _SIGN[ev.event_type] * ev.quantity
    return dict(balances)


def _within_tolerance(schedule: str, folded: int, variance: int) -> bool:
    """Schedule II is exact; III–V permit a small estimate band."""
    if schedule == "II":
        return variance == 0
    allowance = max(1, round(abs(folded) * _ESTIMATE_TOLERANCE))
    return abs(variance) <= allowance


def reconcile(
    events: list[LedgerEvent],
    physical_counts: dict[str, int],
    schedules: dict[str, str],
    count_ts: str,
) -> list[VarianceLine]:
    """Compare folded ledger balances to a physical count, schedule-aware."""
    folded = fold_balance_at(events, count_ts)
    all_ndc = set(folded) | set(physical_counts)
    lines: list[VarianceLine] = []
    for ndc in sorted(all_ndc):
        f = folded.get(ndc, 0)
        p = physical_counts.get(ndc, 0)
        sched = schedules.get(ndc, "V")
        variance = p - f
        ok = _within_tolerance(sched, f, variance)
        line = VarianceLine(
            ndc_11=ndc, schedule=sched, folded=f, physical=p,
            variance=variance, status="RECONCILED" if ok else "FLAGGED",
        )
        lines.append(line)
        if not ok:
            # PHI-free: identifiers, quantities, and schedule only.
            logger.warning(
                "biennial_variance_flagged ndc=%s schedule=%s folded=%d "
                "physical=%d variance=%d", ndc, sched, f, p, variance,
            )
    return lines


def audit_hash(lines: list[VarianceLine], count_ts: str) -> str:
    """Bind the whole reconciliation to a tamper-evident SHA-256 digest."""
    canonical = json.dumps(
        {"count_ts": count_ts,
         "lines": [line.__dict__ for line in lines]},
        sort_keys=True, separators=(",", ":"), default=str,
    )
    return hashlib.sha256(canonical.encode("utf-8")).hexdigest()


if __name__ == "__main__":
    logging.basicConfig(level=logging.INFO)
    events = [
        LedgerEvent("00093310501", "II", EventType.RECEIVE, 500,
                    "2026-06-01T09:00:00+00:00", 1),
        LedgerEvent("00093310501", "II", EventType.DISPENSE, 80,
                    "2026-07-01T11:00:00+00:00", 2),
        # This dispense is AFTER the count and must be excluded from the fold.
        LedgerEvent("00093310501", "II", EventType.DISPENSE, 20,
                    "2026-07-16T17:00:00+00:00", 3),
    ]
    lines = reconcile(
        events,
        physical_counts={"00093310501": 415},
        schedules={"00093310501": "II"},
        count_ts="2026-07-16T07:00:00+00:00",
    )
    print(lines[0].folded, lines[0].variance, lines[0].status)
    print(audit_hash(lines, "2026-07-16T07:00:00+00:00")[:12])

Folding to 2026-07-16T07:00:00Z yields 420 (500 received minus 80 dispensed; the 17:00 dispense is after the count and excluded), so a physical count of 415 produces a Schedule II variance of −5 that is correctly flagged. The recipe for reconstructing that as-of balance for an arbitrary inspector-supplied timestamp is developed in point-in-time inventory reconstruction.

Compliance Mapping & Audit Boundaries

The retained artifact of a biennial reconciliation is the triple of the physical count, the folded balance, and the variance report, bound by the audit_hash. Each is meaningful only together: the count is the observation, the fold is the system’s claim about the same instant, and the hash makes the comparison tamper-evident. An inspector recomputes the fold from the immutable events, recomputes the hash, and confirms both match the retained report — the same append-only discipline the platform’s merkle-tree audit anchoring provides at the event level.

Because the fold reads only immutable history, a reconciliation is reproducible indefinitely, which is what 21 CFR § 1304.04 retention presumes: the record is not just stored, it is re-derivable from stored inputs for the full two-year horizon and beyond.

Error Handling & Offline Resilience

Reconciliation must behave predictably when inputs are imperfect, because the alternative — a silently wrong balance — is a recordkeeping failure:

• Events arriving after the fold. A late event with an effective time at or before T that lands after the reconciliation ran changes the historical balance. The fold is re-run and the report re-issued as a new, forward-chained version; the original is never edited.
• Out-of-order sequences. Events are sorted by (effective_ts, sequence) before folding, so an event ingested late but timestamped early lands in its correct historical position.
• Missing schedule metadata. An NDC without a resolved schedule defaults to the strictest interpretation for flagging rather than being silently reconciled, so an unclassified item never escapes review.
• Offline count capture. When a counting terminal is disconnected, counts are queued locally with the count timestamp and folded against a locally-durable event replay on reconnect, so an outage delays the report but never corrupts the effective time. The deferred-replay discipline mirrors the platform’s offline-sync fallback strategy.

Every degraded path preserves the count timestamp and re-derives the balance rather than trusting a cached total, keeping the reconciliation reproducible.

Downstream Integration

A completed reconciliation feeds several regulated workflows:

1. Loss reporting. A flagged, unexplained Schedule II shortage escalates to the DEA Form 106 pipeline, which snapshots the discrepancy before filing.
2. Perpetual-inventory correction. Confirmed, explained variances post as new compensating events, keeping the ongoing ledger honest without editing history.
3. Audit retention. The count, fold, and variance report are indexed into WORM storage under the platform’s event-sourcing foundations, readily retrievable for inspection.

By computing the system side of the reconciliation as a deterministic fold of immutable events, the pipeline turns the biennial count from a stressful spreadsheet exercise into a reproducible, defensible record.

Frequently Asked Questions

Related

• Regulatory Reporting & DEA Submissions — parent area this reconciliation workflow operates within
• Point-in-Time Inventory Reconstruction — replaying the event stream to an as-of balance
• DEA Form 106 Theft & Loss Reporting — where an unexplained Schedule II variance escalates
• Core Architecture & DEA Compliance Frameworks — the event-sourcing foundations the fold reads from
• Merkle-Tree Audit Anchoring — tamper-evident anchoring of the historical events