Inventory & asset intelligence

-- Movements are append-only. Balances are derived, never typed in.
CREATE TABLE stock_movement (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  item_id     text        NOT NULL,
  location_id text        NOT NULL,
  kind        text        NOT NULL CHECK (kind IN ('RECEIPT', 'ISSUE',
                'TRANSFER_IN', 'TRANSFER_OUT', 'RETURN', 'ADJUSTMENT')),
  qty_delta   integer     NOT NULL,
  reason_code text,
  source_ref  text        NOT NULL,     -- goods receipt, order, count sheet
  actor       text        NOT NULL,
  approved_by text,
  occurred_at timestamptz NOT NULL,     -- when it happened on the floor
  recorded_at timestamptz NOT NULL DEFAULT now(),  -- when it reached us
  -- Adjustments post only after approval; pending ones wait in a review queue.
  CHECK (kind <> 'ADJUSTMENT' OR (reason_code IS NOT NULL AND approved_by IS NOT NULL))
);

-- A correction is a new row. Edits, deletes, and truncation are refused.
CREATE FUNCTION reject_change() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'stock_movement is append-only';
END $$;
CREATE TRIGGER stock_movement_no_edit BEFORE UPDATE OR DELETE ON stock_movement
  FOR EACH ROW EXECUTE FUNCTION reject_change();
CREATE TRIGGER stock_movement_no_truncate BEFORE TRUNCATE ON stock_movement
  FOR EACH STATEMENT EXECUTE FUNCTION reject_change();

-- On hand now.
CREATE VIEW stock_on_hand AS
SELECT item_id, location_id, sum(qty_delta) AS on_hand
FROM stock_movement
GROUP BY item_id, location_id;

-- On hand as it stood at quarter end: replay to a point in time.
SELECT item_id, location_id, sum(qty_delta) AS on_hand
FROM stock_movement
WHERE occurred_at < timestamptz '2026-07-01 00:00:00+06'
GROUP BY item_id, location_id;