FIFO Cost-of-Goods
Fexl Lite values inventory and cost-of-goods sold using FIFO — First-In-First-Out. The first units in are the first units assumed sold, at the cost they were brought in at. This page is the mechanics: where the layers live, how they drain, and how every COGS number on every report ends up reading the same source.
The append-only inventory ledger
Every quantity change to a product writes a row in inventory_transactions. The table is append-only — there’s no UPDATE path that rewrites a historical row. A row carries:
product_id,variant_id— what changed.type—ingress(came in) oregress(went out) oradjustment(manual delta).qty_delta— the signed change.unit_cost— for ingress, the landed cost from the PO or restock dialog; for egress, the FIFO unit cost resolved at sale time.qty_remaining— for ingress rows only, how many of the originalqty_deltaunits are still on the shelf.source—po_receive,manual_restock,sale_egress,bonus_egress,refund_ingress,defect_egress, etc.created_at— the FIFO ordering key.
The qty_remaining column is the FIFO machinery. On ingress, it opens equal to qty_delta. On every egress that draws from this row’s layer, it decrements until it hits zero. A row with qty_remaining = 0 is a fully-consumed layer; a row with qty_remaining > 0 is open and contributes to the inventory valuation.
How an egress drains layers
When the POS rings a cash sale of 5 units of Product X, the sale handler does the following inside one transaction:
- Loads the open ingress layers for Product X, ordered by
created_atascending (oldest first). - Walks them, decrementing
qty_remainingon each layer, picking up the layer’sunit_costper unit consumed. - If a layer doesn’t have enough quantity to cover the egress, the handler moves to the next layer — and the egress’s resulting cost is the weighted sum across the layers it consumed.
- Writes one egress row to
inventory_transactionswith the resolvedunit_cost(weighted average across layers),qty_delta = −5,source = 'sale_egress', and the invoice ID linked. - Posts the JE: DR
5010(qty × unit_cost), CR1200(qty × unit_cost), alongside the cash and revenue legs.
A worked example — three open layers on Product X, sale of 5 units:
Layer A (oldest): qty_remaining=3 unit_cost=$10Layer B: qty_remaining=4 unit_cost=$12Layer C (newest): qty_remaining=8 unit_cost=$14Selling 5 units consumes:
- 3 from Layer A at $10 = $30
- 2 from Layer B at $12 = $24
- Total cost: $54
- Per-unit weighted cost: $54 / 5 = $10.80
After the egress:
Layer A: qty_remaining=0 (fully consumed, closed)Layer B: qty_remaining=2 (partially consumed, still open)Layer C: qty_remaining=8 (untouched, still open)The egress row stamps unit_cost = $10.80 and qty_delta = −5. The COGS leg of the JE is $54 (DR 5010 / CR 1200). Next sale starts from Layer B.
Why egress cost is stamped at sale time
This is the property that makes period reports stable. Re-running January’s P&L in June returns the same numbers it did in February, because the egress cost on January’s sales was resolved on the day of each sale and never touched again.
Bonus units
Bonus / BOGO units are FIFO-costed the same way as sold units — they’re real inventory drawn from real layers. The egress row writes source='bonus_egress' and unit_cost is the FIFO weighted average for that draw. The JE side splits to 5020 COGS — Bonus Items instead of 5010, so the P&L can show promotional cost separately.
Refunds re-ingress at the original cost
When a customer returns a unit through the refund wizard, the wizard writes an ingress row for the returned unit at the original sale’s egress cost — not at the current product cost. That keeps the layer chronology honest: the unit goes back onto the shelf valued at what it cost when it left. The refund-ingress row has its own qty_remaining = qty_delta, and from that point on it drains FIFO like any other layer.
If the unit went out at $10.80 weighted (across two underlying layers) and comes back, the re-ingress is one new layer at $10.80 — not split back across the original layers it came from. That’s a deliberate simplification: the alternative (un-draining the original layers) would require mutating historical qty_remaining values, which violates the append-only contract.
Defects, write-offs, adjustments
- Defect egress — a unit pulled from stock for repair / write-off. FIFO drains the same way as a sale, but the JE credits an inventory write-off account (typically
7100 Other Expense) instead of5010. - Stocktake adjustment — a manual delta from a physical count discrepancy. Positive deltas open a new layer at the user-entered cost; negative deltas drain FIFO.
- PO void — reverses a received PO. The handler walks the original ingress’s
qty_remainingand the egresses that consumed it, refusing the void if the layer has been partially or fully sold. If the layer is intact, the ingress is reversed in place.
How the v71 backfill fixed historical egress
Before the v71 backfill (shipped in v1.6.100), egress rows on legacy data could carry an unreliable unit_cost — earlier versions of the codebase wrote the rolling-product cost_price of the day, which drifts via WAC over time. The backfill walked every (tenant, product, variant), reconstructed the FIFO layer history from the chronological ingress / egress sequence, and rewrote each egress row’s unit_cost to its FIFO-correct value. Idempotent; runs once per tenant; safe to re-run.
After v71, P&L runs over historical periods read FIFO-faithful costs, not the rolling cost-of-the-day. Tenants on v1.6.100 or later have correct historical COGS for any period; tenants who skipped that version need to apply it before deeper-history reports are trustworthy.
How to inspect a single product’s layers
Open the product’s inventory timeline (Inventory page → click product → Timeline tab). Every ingress and egress row appears in chronological order, with the unit cost and qty_remaining (on ingress rows) visible. The total of all open qty_remaining × unit_cost is the on-hand value contributing to the Inventory Valuation report and the 1200 Inventory line on the Balance Sheet.