org.finished_goods_inventory_ledger

Append-only event log for finished-goods movement. Same shape as the materials ledger, anchored on sku_id instead of material_id. Summary views are derived.

ER diagram

Columns

Identical shape to materials_inventory_ledger, anchored on sku_id instead of material_id. event_type uses the finished_goods_inventory_event_type ENUM (same values as materials, plus PRODUCE and REVERSE_PRODUCE which only apply to FG).

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. FIL-001). Auto-generated when not supplied. UNIQUE per (org_id, code). Distinct from ref.
sku_id	UUID FK	NOT NULL. → org.skus(id) ON DELETE RESTRICT.
location_id	UUID FK	→ org.locations(id) ON DELETE RESTRICT.
event_type	finished_goods_inventory_event_type	NOT NULL. ENUM. See Event types below.
quantity	NUMERIC(18,6)	NOT NULL. CHECK > 0. Direction encoded in event_type.
unit_cost	NUMERIC(18,6)	Cost basis. Set at write time. NULL when not applicable.
ref	TEXT	External reference (PO/WO/SO number, TFR-FG-###). 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(). Real-world event time.
source_type	org.ledger_source_kind	ENUM. po_receipt (FG-target POs), 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: same as the materials ledger — ((source_type IS NULL AND source_id IS NULL) OR both set).

Event types

finished_goods_inventory_event_type ENUM:

Value	Meaning
ORDER	A PO targeting FG was placed — committed inflow not yet received.
RECEIVE	FG physically received (from a PO or completing a transfer).
PRODUCE	FG produced by a WO completion. Distinct from RECEIVE so reporting can separate purchased FG from in-house production.
DEMAND	An SO was placed — committed outflow.
ALLOCATE	Soft reservation against a specific SO before fulfillment.
CONSUME	Actual outflow when the SO ships.
TRANSFER	FG leaves location_id for to_location_id. Paired with a later RECEIVE sharing the same ref.
ADJUST	Manual correction (positive quantity). Reductions use DISPOSE.
DISPOSE	Manual removal — write-off, damage, loss.
REVERSE_ORDER	Cancels an ORDER (e.g. FG-target PO transitioned to Cancelled).
REVERSE_RECEIVE	Cancels a RECEIVE (e.g. PO receipt deleted, or transfer reversed).
REVERSE_PRODUCE	Cancels a PRODUCE (e.g. WO receipt deleted).
REVERSE_DEMAND	Cancels a DEMAND (e.g. SO transitioned to Rejected).
REVERSE_ALLOCATE	Releases a previously written ALLOCATE (was RELEASE in earlier drafts; renamed for consistency).
REVERSE_CONSUME	Cancels a CONSUME (e.g. SO deleted after shipment).
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.

Event sources

• RECEIVE from a PO receipt when the PO's target_type = 'finished_goods'.
• RECEIVE from a WO receipt — the canonical source of new FG inventory.
• DEMAND / ALLOCATE / CONSUME from SO progression.
• TRANSFER paired with a later RECEIVE (sharing the same TFR-FG-xxx ref) — the manual transfer flow.
• ADJUST / DISPOSE from the manual flow on the Finished Goods Inventory page.

ref conventions

Event type	ref value
RECEIVE (from PO)	PO number
RECEIVE (from WO)	WO number
DEMAND / ALLOCATE / CONSUME (from SO)	SO number
TRANSFER	TFR-FG-xxx (auto-generated)
RECEIVE (completing transfer)	Same TFR-FG-xxx as the paired TRANSFER
ADJUST / DISPOSE	NULL