Healthcare Supply Chain Inventory: FIFO, Multi-Location, Expiry-Aware Architecture

The Supply Chain Problem at Multi-Location Practices

Multi-location 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

Cross-Location Consolidated Ordering

The biggest budget leak isn't expiry waste — it's duplicate ordering. Each clinic places independent orders without visibility into other locations' inventory.

The architecture surfaces a consolidated view:

• Total stock per SKU across all locations
• Per-location reorder thresholds (configurable)
• Suggested orders that aggregate across locations to hit supplier minimum-order discounts
• Inter-location transfer suggestions (Location A overstocked → Location B understocked = transfer, not reorder)

This is the same multi-location data model pattern documented in the multi-location clinic management 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 multi-location surgical and specialty groups.