Inventory

Read Conventions first.

stock_movements

The stock ledger. Balance = SUM(quantity) for an item+warehouse; negatives are issues.

• id, business_id, item_id NOT NULL, warehouse_id NOT NULL
• quantity numeric(14,3) NOT NULL - signed
• unit_cost numeric(12,2)
• reference_type NOT NULL; reference_id uuid NOT NULL - polymorphic link to the source document (no FK)
• bin_location_id → bin_locations NULL; created_at
• Indexes: (item_id, warehouse_id), (business_id, item_id)

Batch & serial tracking

• stock_movement_batch_allocations - which batch/serial a movement drew from. id, stock_movement_id CASCADE; item_batch_id NULL, item_serial_id NULL - CHECK: exactly one of the two is set; quantity, created_at
• item_batches - id, business_id, item_id, warehouse_id, batch_number - UNIQUE (business_id, item_id, warehouse_id, batch_number); expiry_date NULL; unit_cost; quantity_received, quantity_remaining NOT NULL; reserved_quantity DEFAULT 0; received_date, bin_location_id; reference_type/reference_id polymorphic origin; created_at
• item_serials - id, business_id, item_id, warehouse_id, serial_number - UNIQUE (business_id, item_id, serial_number); unit_cost; status DEFAULT ‘in_stock’ CHECK IN (‘in_stock’,‘consumed’); received_date, bin_location_id, reference_type, reference_id, created_at
• batch_reservations - id, business_id; item_batch_id CASCADE NULL / item_serial_id CASCADE NULL - CHECK: exactly one; quantity; reference_type, reference_id, reference_line_id all NOT NULL; created_by, created_at; partial UNIQUE index on (item_serial_id) WHERE item_serial_id IS NOT NULL - a serial can be reserved once

Non-PO goods movements

• goods_receipts - plain (non-PO) goods receipt. id, business_id, document_no UNIQUE (business_id, document_no); branch_id NOT NULL, warehouse_id NOT NULL, receipt_date, reference, notes; status CHECK IN (‘draft’,‘posted’,‘cancelled’); posted/cancelled stamps, created_by, created_at
• goods_receipt_lines - id, goods_receipt_id CASCADE, item_id NOT NULL, gl_account_id NOT NULL; quantity, unit_cost, line_total; batch_number, expiry_date, serial_numbers, admission_date; uom_id, cost_center_id, created_at
• goods_issues - same shape as goods_receipts plus reason; issue_date instead of receipt_date; UNIQUE (business_id, document_no)
• goods_issue_lines - id, goods_issue_id CASCADE, item_id, gl_account_id, quantity, unit_cost, line_total, uom_id, cost_center_id, created_at

Transfers

• inventory_transfer_requests - internal or external (inter-branch) transfer request. id, business_id, document_no UNIQUE (business_id, document_no); from_branch_id NOT NULL, to_branch_id NOT NULL; from_warehouse_id NOT NULL, to_warehouse_id nullable; is_external boolean DEFAULT false; request_date, journal_remarks, remarks; status CHECK IN (‘open’,‘closed’,‘cancelled’); closed/cancelled stamps, created_by, created_at
• inventory_transfer_request_lines - id, inventory_transfer_request_id CASCADE, item_id, quantity, uom_id; delivered_quantity DEFAULT 0; row_status CHECK IN (‘open’,‘closed’); created_at
• inventory_transfers - carries a full logistics status chain. id, business_id, document_no UNIQUE (business_id, document_no); from_branch_id/to_branch_id NOT NULL, from_warehouse_id NOT NULL, to_warehouse_id nullable; is_external; source_request_id NULL; transfer_date, reference, notes, vehicle_number; status DEFAULT ‘draft’ - CHECK IN (‘draft’,‘picked’,‘loaded’,‘in_transit’,‘posted’,‘closed’,‘cancelled’); stage stamps: picked_at/picked_by, loaded_at/loaded_by, in_transit_at/in_transit_by, posted_at/posted_by, closed_at/closed_by, cancelled_at/cancelled_by; created_by, created_at
• inventory_transfer_lines - id, inventory_transfer_id CASCADE, item_id, quantity, uom_id; received_quantity NULL; batch_allocations jsonb NOT NULL DEFAULT ‘[]’; source_request_line_id → inventory_transfer_request_lines RESTRICT; created_at

Opening balances

• inventory_opening_balances - id, business_id, document_no UNIQUE (business_id, document_no); branch_id NOT NULL, warehouse_id NOT NULL, balance_date, reference, notes; status CHECK IN (‘draft’,‘posted’,‘cancelled’); posting/cancel stamps; created_by, created_at
• inventory_opening_balance_lines - id, inventory_opening_balance_id CASCADE, item_id, gl_account_id; quantity, unit_cost, line_total; batch_number, expiry_date, serial_numbers; cost_center_id, created_at

Counting (two-document split)

The Counting document freezes stock when confirmed; a separate Inventory Posting document is what actually adjusts stock.

• inventory_counts - id, business_id, document_no UNIQUE (business_id, document_no); branch_id NOT NULL, warehouse_id NOT NULL, count_date, reference, notes; status DEFAULT ‘draft’ - CHECK IN (‘draft’,‘confirmed’,‘posted’,‘cancelled’); posting/cancel stamps; created_by, created_at
• inventory_count_lines - id, inventory_count_id CASCADE, item_id, gl_account_id; counted_quantity NOT NULL; is_frozen boolean DEFAULT true (blocks other stock-moving documents touching this item+warehouse); frozen_quantity DEFAULT 0 - the system quantity captured at line-creation time; the variance baseline, not recomputed at post time; unit_cost, cost_center_id, created_at
• inventory_postings - copied from a confirmed count; posting it creates the stock/GL adjustment and releases the freeze. id, business_id, document_no UNIQUE (business_id, document_no); inventory_count_id NOT NULL; branch_id NOT NULL, warehouse_id NOT NULL, posting_date, reference, notes; status CHECK IN (‘draft’,‘posted’,‘cancelled’); posting/cancel stamps; created_by, created_at
• inventory_posting_lines - id, inventory_posting_id CASCADE, inventory_count_line_id NOT NULL; item_id, gl_account_id; counted_quantity, system_quantity, variance NOT NULL; unit_cost, cost_center_id, created_at