Double-Entry Inventory Ledgers for High-Value Cold-Chain Medicines

In standard e-commerce software, tracking product stock is typically implemented with a simple integer column: UPDATE products SET stock = stock - 1 WHERE id = 42.

In high-consequence healthcare supply chains—tracking recombinant Factor VIII/IX clotting concentrates, monoclonal antibodies, and biological oncology vials costing tens of thousands of rupees per vial—this mutable approach is catastrophic.

A mutable counter cannot answer crucial audit questions: Which specific batch was dispensed? Who authorized the emergency transfer? At what timestamp did a cold-chain excursion render three vials unusable?

The solution is borrowing a proven 500-year-old financial concept: Double-Entry Bookkeeping applied to physical healthcare inventory.

1. The Core Invariant: Matter Cannot Be Created or Destroyed

In a double-entry inventory ledger, stock is never incremented or decremented in place. Every physical event is modeled as an immutable Transaction Journal composed of balanced debit and credit entries between distinct inventory accounts:

2. The Relational Schema in PostgreSQL

An enterprise double-entry inventory schema requires four core tables that maintain strict referential integrity and batch-level tracking:

-- 1. Master definition of specific medicine batches (potency, expiry, cold-chain bounds)
CREATE TABLE inventory_batches (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  medicine_code VARCHAR(64) NOT NULL,
  batch_number VARCHAR(128) NOT NULL UNIQUE,
  potency_iu INTEGER NOT NULL,            -- e.g. 500 IU or 1000 IU per vial
  expiry_date DATE NOT NULL,
  min_temp_celsius NUMERIC(4,1) DEFAULT 2.0,
  max_temp_celsius NUMERIC(4,1) DEFAULT 8.0,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 2. Inventory Accounts (Warehouses, Pharmacies, Patients, Loss accounts)
CREATE TABLE inventory_accounts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  account_type VARCHAR(32) NOT NULL,      -- 'WAREHOUSE', 'PHARMACY', 'PATIENT', 'LOSS'
  facility_id UUID REFERENCES facilities(id),
  name VARCHAR(255) NOT NULL
);

-- 3. Journal Header (The transaction envelope)
CREATE TABLE inventory_journals (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  transaction_type VARCHAR(64) NOT NULL,  -- 'PROCUREMENT', 'TRANSFER', 'DISPENSATION', 'EXCURSION'
  reference_number VARCHAR(128) NOT NULL, -- E.g. Gate pass # or Prescription #
  authorized_by UUID NOT NULL,
  logged_at TIMESTAMPTZ DEFAULT NOW()
);

-- 4. Immutable Postings (Debits and Credits)
CREATE TABLE inventory_postings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  journal_id UUID NOT NULL REFERENCES inventory_journals(id),
  batch_id UUID NOT NULL REFERENCES inventory_batches(id),
  account_id UUID NOT NULL REFERENCES inventory_accounts(id),
  quantity_vials INTEGER NOT NULL,        -- Positive for Debit (Inflow), Negative for Credit (Outflow)
  CONSTRAINT non_zero_posting CHECK (quantity_vials != 0)
);

CREATE INDEX idx_postings_account_batch ON inventory_postings(account_id, batch_id);

3. Calculating Current On-Hand Stock

Because stock is calculated by summing postings, on-hand inventory is always mathematically provable and impossible to corrupt through partial writes:

-- Calculate real-time available stock for a hospital pharmacy
SELECT
  b.batch_number,
  b.expiry_date,
  b.potency_iu,
  SUM(p.quantity_vials) AS on_hand_vials
FROM inventory_postings p
JOIN inventory_batches b ON p.batch_id = b.id
WHERE p.account_id = 'a1b2c3d4-...' -- District Hospital Account
GROUP BY b.batch_number, b.expiry_date, b.potency_iu
HAVING SUM(p.quantity_vials) > 0
ORDER BY b.expiry_date ASC;

4. FEFO Allocation with PostgreSQL Row Locks

When a clinical nurse initiates an infusion, the system must automatically allocate vials following First-Expired-First-Out (FEFO) to minimize wastage.

To prevent two nurses from dispensing the same cold-storage vial simultaneously under high concurrent load, we combine FEFO sorting with FOR UPDATE SKIP LOCKED:

-- Lock and allocate the nearest-expiry batch without blocking other concurrent dispensers
WITH available_lots AS (
  SELECT
    b.id AS batch_id,
    SUM(p.quantity_vials) AS current_stock
  FROM inventory_postings p
  JOIN inventory_batches b ON p.batch_id = b.id
  WHERE p.account_id = $pharmacy_account_id
  GROUP BY b.id, b.expiry_date
  HAVING SUM(p.quantity_vials) >= $requested_vials
  ORDER BY b.expiry_date ASC
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
INSERT INTO inventory_postings (journal_id, batch_id, account_id, quantity_vials)
VALUES
  ($journal_id, (SELECT batch_id FROM available_lots), $pharmacy_account_id, -$requested_vials),
  ($journal_id, (SELECT batch_id FROM available_lots), $patient_account_id, +$requested_vials);

5. Zero-Discrepancy Regulatory Audits

Implementing an immutable double-entry ledger gives health authorities and hospital leadership complete forensic transparency.

Whether undergoing an annual Comptroller and Auditor General (CAG) audit, verifying cold-chain temperature excursion insurance claims, or tracing a recalled manufacturing lot back to every recipient patient in seconds, double-entry architecture provides the foundational rigor demanded by modern healthcare systems.