Inventory Movements — Append-Only with Balance Trigger

-- The current balance per (warehouse, product/variant, lot if applicable)
CREATE TABLE inventory_balances (
  id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id       BIGINT NOT NULL,
  warehouse_id    BIGINT NOT NULL,
  product_id      BIGINT NOT NULL,
  variant_id      BIGINT,                          -- NULL = simple product
  lot_id          BIGINT,                          -- NULL = no lot tracking
  on_hand         INTEGER NOT NULL DEFAULT 0,      -- physical
  reserved        INTEGER NOT NULL DEFAULT 0,      -- soft-locked
  available       INTEGER GENERATED ALWAYS AS (on_hand - reserved) STORED,
  version         BIGINT NOT NULL DEFAULT 0,       -- optimistic concurrency
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT inventory_balances_nonneg_on_hand   CHECK (on_hand   >= 0),
  CONSTRAINT inventory_balances_nonneg_reserved  CHECK (reserved  >= 0),
  CONSTRAINT inventory_balances_reserved_le_on_hand CHECK (reserved <= on_hand)
);

CREATE UNIQUE INDEX inventory_balances_unique_key
  ON inventory_balances (
    tenant_id, warehouse_id, product_id,
    COALESCE(variant_id, 0), COALESCE(lot_id, 0)
  );

-- The append-only audit/event log
CREATE TABLE inventory_movements (
  id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id       BIGINT NOT NULL,
  balance_id      BIGINT NOT NULL REFERENCES inventory_balances (id),
  warehouse_id    BIGINT NOT NULL,
  product_id      BIGINT NOT NULL,
  variant_id      BIGINT,
  lot_id          BIGINT,
  movement_type   TEXT NOT NULL,                   -- 'receipt','sale','transfer_in','transfer_out','adjustment','count','return'
  quantity_delta  INTEGER NOT NULL,                -- signed
  on_hand_after   INTEGER NOT NULL,
  reserved_after  INTEGER NOT NULL,
  reason          JSONB,                           -- { type: 'order', order_id: 71001 }
  occurred_by     BIGINT NOT NULL,                 -- user id
  occurred_at     TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY RANGE (occurred_at);

-- Initial monthly partition
CREATE TABLE inventory_movements_202605
  PARTITION OF inventory_movements
  FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

CREATE INDEX inventory_movements_balance_idx
  ON inventory_movements (balance_id, occurred_at DESC);

BEGIN;

-- Lock the balance row to serialize concurrent reservations
SELECT id, on_hand, reserved, version
  FROM inventory_balances
 WHERE tenant_id = $tenant_id
   AND warehouse_id = $warehouse_id
   AND product_id = $product_id
   AND variant_id IS NOT DISTINCT FROM $variant_id
   AND lot_id IS NOT DISTINCT FROM $lot_id
 FOR UPDATE;

-- Application checks: available >= requested? If not, abort with 409.
-- If yes:

UPDATE inventory_balances
   SET reserved = reserved + $quantity,
       version = version + 1,
       updated_at = now()
 WHERE id = $balance_id;

INSERT INTO inventory_reservations (
  tenant_id, balance_id, quantity, reason, expires_at
) VALUES (
  $tenant_id, $balance_id, $quantity,
  jsonb_build_object('type', 'order_pending', 'order_id', $order_id),
  now() + INTERVAL '15 minutes'
);

-- Outbox event in the same tx
INSERT INTO outbox_messages (tenant_id, aggregate_type, aggregate_id, event_type, payload)
VALUES ($tenant_id, 'Inventory', $balance_id, 'InventoryReserved', $payload);

COMMIT;

BEGIN;

UPDATE inventory_balances
   SET on_hand  = on_hand  - $quantity,
       reserved = reserved - $quantity,
       version  = version + 1,
       updated_at = now()
 WHERE id = $balance_id;

DELETE FROM inventory_reservations WHERE id = $reservation_id;

INSERT INTO inventory_movements (
  tenant_id, balance_id, warehouse_id, product_id, variant_id, lot_id,
  movement_type, quantity_delta,
  on_hand_after, reserved_after,
  reason, occurred_by
) VALUES (
  $tenant_id, $balance_id, $warehouse_id, $product_id, $variant_id, $lot_id,
  'sale', -$quantity,
  $on_hand_after, $reserved_after,
  jsonb_build_object('type', 'order_confirmed', 'order_id', $order_id),
  $user_id
);

INSERT INTO outbox_messages (tenant_id, aggregate_type, aggregate_id, event_type, payload)
VALUES ($tenant_id, 'Inventory', $balance_id, 'InventoryMovementCreated', $payload);

COMMIT;

WITH expired AS (
  DELETE FROM inventory_reservations
   WHERE expires_at < now()
   RETURNING *
), released AS (
  UPDATE inventory_balances b
     SET reserved = b.reserved - e.quantity,
         version = b.version + 1,
         updated_at = now()
    FROM expired e
   WHERE b.id = e.balance_id
   RETURNING b.id, b.tenant_id, e.quantity, e.reason
)
INSERT INTO inventory_movements (
  tenant_id, balance_id, warehouse_id, product_id, movement_type,
  quantity_delta, on_hand_after, reserved_after, reason, occurred_by
)
SELECT r.tenant_id, r.id, b.warehouse_id, b.product_id, 'reservation_expired',
       0, b.on_hand, b.reserved,
       jsonb_build_object('type', 'reservation_expired', 'original_reason', r.reason),
       0  -- system actor
  FROM released r
  JOIN inventory_balances b ON b.id = r.id;

CREATE TABLE inventory_lots (
  id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id       BIGINT NOT NULL,
  product_id      BIGINT NOT NULL,
  variant_id      BIGINT,
  lot_code        TEXT NOT NULL,
  manufactured_at DATE,
  expires_at      DATE,
  supplier_id     BIGINT REFERENCES suppliers (id),
  metadata        JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT inventory_lots_unique_code
    UNIQUE (tenant_id, product_id, variant_id, lot_code)
);

CREATE INDEX inventory_lots_expiration_idx
  ON inventory_lots (tenant_id, expires_at)
  WHERE expires_at IS NOT NULL;