Healthcare Supply Chain Inventory: FIFO, Expiry-Aware Architecture

The Supply Chain Problem in Complex Healthcare Operations

Surgical and specialty practices routinely lose budget to:

• Expired supplies discarded because nobody tracked which lot expired first
• Duplicate ordering of the same item across locations because each clinic ordered independently
• Stockouts of critical supplies that nobody noticed until the procedure was scheduled

The fix isn't a new SaaS subscription — it's an inventory system architected around how clinical workflows actually work.

The Data Model: Lots, Not Just SKUs

The standard mistake is modeling inventory at the SKU level only. For healthcare supplies, the lot (a specific batch with its own expiry date) is the relevant unit:

CREATE TABLE inventory_items (
    id       UUID PRIMARY KEY,
    sku      VARCHAR(50) NOT NULL UNIQUE,
    name     TEXT NOT NULL,
    category VARCHAR(100)
    -- Master item record, no quantity
);

CREATE TABLE inventory_lots (
    id          UUID PRIMARY KEY,
    item_id     UUID NOT NULL REFERENCES inventory_items(id),
    location_id UUID NOT NULL REFERENCES locations(id),
    lot_number  VARCHAR(100) NOT NULL,
    expiry_date DATE NOT NULL,
    quantity    INTEGER NOT NULL CHECK (quantity >= 0),
    received_at TIMESTAMPTZ DEFAULT NOW()
);

-- The picking index gives FIFO consumption for free
CREATE INDEX idx_lots_picking
    ON inventory_lots(item_id, location_id, expiry_date)
    WHERE quantity > 0;

The picking index sorted by expiry_date ASC means when staff scan an SKU at the point of use, the system automatically picks from the earliest-expiring lot with available quantity. FIFO consumption is enforced structurally, not by policy.

The Barcode Picking Workflow

Modern Bluetooth scanners present as keyboard input, so a standard web frontend works with any compliant scanner. The flow:

1. Staff scan an SKU at point of use
2. System identifies the item and queries the picking index for the earliest-expiring lot at that location
3. Decrements the lot's quantity
4. Logs the transaction with timestamp, user, and patient association if applicable
5. Triggers a low-stock alert if the location's total quantity drops below threshold

Consolidated Ordering

The biggest budget leak isn't expiry waste — it's duplicate ordering. Teams place independent orders without visibility into shared inventory.

The architecture surfaces a consolidated view:

• Total stock per SKU across the operation
• Reorder thresholds by storage site or service line (configurable)
• Suggested orders that aggregate demand to hit supplier minimum-order discounts
• Transfer suggestions when one storage site is overstocked and another is understocked

This is the same organizational data model pattern documented in the complex clinic operations software article.

Supplier API Integration

Major medical supply distributors (Henry Schein, McKesson, Cardinal Health) expose order APIs. Direct integration eliminates manual order placement and ensures order status (acknowledged, shipped, delivered) flows back through webhooks to update the inventory_lots table on receiving.

For distributors without APIs, the system falls back to email-to-supplier with a structured PDF order form, then ingests the supplier's confirmation email to close the loop.

Audit Logging for Compliance

For DEA-controlled substances and high-value supplies, audit log requirements are stricter than standard HIPAA. Every transaction records user, timestamp, lot, and patient association where applicable. The log is immutable — corrections create new entries with reversal codes, never UPDATE or DELETE on historical rows. Same pattern as HIPAA audit logging requirements.

Build vs Buy

Off-the-shelf medical inventory products work for single locations. They start to break when multiple specialties need different supply mixes, inter-location transfers matter, custom supplier integrations are needed, or the inventory data needs to feed into the practice management system directly.

The Custom Practice Management service includes inventory architecture for surgical and specialty teams with complex operating rules.