org.materials_inventory_ledger

Append-only event log for materials movement. The summary view ("on-hand by material × location") is derived from this table; it isn't stored.

ER diagram

Columns

Column	Type	Notes
id	UUID PK
org_id	UUID FK	→ platform.organizations(id).
code	TEXT	NOT NULL. Row's own human-readable ID (e.g. MIL-001). Auto-generated when not supplied. UNIQUE per (org_id, code). Distinct from ref — code identifies this row; ref is the external reference shared across rows tied to the same originating event.
material_id	UUID FK	NOT NULL. → org.materials(id) ON DELETE RESTRICT.
location_id	UUID FK	→ org.locations(id) ON DELETE RESTRICT. The "from" location for transfers; the source/destination for other events. RESTRICT preserves audit integrity.
event_type	materials_inventory_event_type	NOT NULL. ENUM. See Event types below.
quantity	NUMERIC(18,6)	NOT NULL. CHECK > 0. Direction is encoded in event_type; quantity itself is always positive.
unit_cost	NUMERIC(18,6)	Cost basis. Set at write time (FIFO/weighted-avg-resolved for outflows, vendor cost for inflows). NULL when not applicable.
ref	TEXT	External reference (PO number, TFR-MI-###, etc.). Multiple rows can share the same ref.
to_location_id	UUID FK	→ org.locations(id) ON DELETE RESTRICT. Set only for TRANSFER events.
notes	TEXT
event_date	TIMESTAMPTZ	NOT NULL. Default NOW(). The real-world time the movement happened — distinct from created_at. Lets bulk imports stamp historical dates.
source_type	org.ledger_source_kind	ENUM. Originating record kind: po_receipt, wo_receipt, so_item, manual.
source_id	UUID	Originating row id. (source_type, source_id) is the idempotency key. No DB-level FK — application-enforced.
created_at	TIMESTAMPTZ	NOT NULL. Default NOW().

Constraints:

• CHECK ((source_type IS NULL AND source_id IS NULL) OR (source_type IS NOT NULL AND source_id IS NOT NULL)) — both NULL (manual entry) or both set.

Event types

materials_inventory_event_type ENUM:

Value	Meaning
ORDER	A PO was placed — future inflow committed but not yet received.
RECEIVE	Material physically received (against a PO, or completing a transfer).
DEMAND	A WO was placed — future outflow committed.
ALLOCATE	Soft reservation against a specific WO before consumption.
CONSUME	Actual consumption when the WO progresses.
TRANSFER	Material leaves location_id for to_location_id. Paired with a later RECEIVE sharing the same ref.
ADJUST	Manual correction (positive quantity — i.e., "I miscounted, there are more"). Reductions use DISPOSE.
DISPOSE	Manual removal — write-off, damage, loss.
REVERSE_ORDER	Cancels an ORDER (e.g. PO transitioned to Cancelled).
REVERSE_RECEIVE	Cancels a RECEIVE (e.g. PO receipt deleted, or transfer reversed).
REVERSE_DEMAND	Cancels a DEMAND (e.g. WO transitioned to Cancelled).
REVERSE_ALLOCATE	Releases a previously written ALLOCATE (was RELEASE in earlier drafts; renamed for consistency).
REVERSE_CONSUME	Cancels a CONSUME (e.g. WO receipt deleted).
REVERSE_TRANSFER	Cancels a TRANSFER (e.g. an in-transit transfer is voided).

REVERSE_* rows store positive quantities — the schema's quantity > 0 invariant holds. Direction is encoded entirely in event_type; the summary query interprets reverse rows as netting their original counterpart. Each reverse row carries source_type and source_id matching the original entry so audit pairs are easy to find.

ref conventions

Event type	ref value
RECEIVE (from PO)	PO number
ORDER (from PO)	PO number
TRANSFER	TFR-MI-xxx (auto-generated by createLedgerEntry)
RECEIVE (completing transfer)	Same TFR-MI-xxx as the paired TRANSFER
ADJUST / DISPOSE	NULL

Cross-references

• Receives written by PO receipts (when the PO targets materials) and the manual transfer flow.
• Allocations / consumes written by work orders progressing through their lifecycle.
• Adjusts / disposes written by the manual flow on the Materials Inventory page.