FIFO costing — how Fexl Lite values your inventory

When the cost of your stock changes between deliveries, FIFO is what keeps your profit numbers honest. Fexl Lite tracks every restock as its own cost layer and drains the oldest layer first on every sale — so the cost-of-goods-sold (COGS) you see on a report is the actual price you paid for the units that left, not a rolling average that smooths the answer into something nobody can audit.

Updated 4 May 2026·For v2.2.0·6 min read 🐞 #44 · v1.6.100

What FIFO means in plain words

FIFO stands for First-In-First-Out: the oldest stock on your shelf is treated as the first stock to leave when a customer buys. Even if your assistant grabs a unit from the back of the rack, the books pretend the front-row unit went first. That’s the convention — and it matches how most shop owners actually think about turnover.

Worked example. You restock the same product twice:

• Monday: buy 10 widgets at $5 each — total $50.
• Wednesday: buy 10 more widgets at $7 each — total $70.

By Thursday afternoon you’ve sold 12 widgets at $9 each.

Under FIFO, COGS is computed layer-by-layer:

• The first 10 sold come from Monday’s layer at $5 → $50.
• The next 2 sold come from Wednesday’s layer at $7 → $14.
• Total COGS for the 12 units = $64.
• Revenue = 12 × $9 = $108.
• Gross profit = $108 − $64 = $44.

The 8 widgets still on the shelf belong to Wednesday’s layer and are valued at 8 × $7 = $56 on the balance sheet. If you’d used a single weighted-average cost ($6 per widget), COGS would have been $72 and the remaining stock $48 — same totals over the long run, but every line item along the way would be wrong.

How Fexl Lite tracks layers

There’s no separate “layers” table. Every restock writes one row into inventory_transactions with type='ingress' and qty_remaining set equal to the restock quantity. That row IS the layer. When a sale leaves the building, Fexl Lite walks the open ingress rows oldest-first (ORDER BY created_at, id ASC) and decrements qty_remaining until the sale is satisfied.

A layer is “open” when it still has qty_remaining > 0 and a non-null cost. Once qty_remaining hits zero, the layer is closed and the next sale skips past it to the layer behind.

inventory · history timeline showing FIFO layers (open vs closed)

ⓘ

One column, one source of truth

inventory_transactions.qty_remaining is the entire bookkeeping system for FIFO. There’s no shadow ledger, no parallel cache. Reports, the cancel path, the refund path, and the sale path all read and write the same column inside the same database transaction — so concurrent sales serialise correctly and nothing can drift.

What every operation does to the layers

Restock

A new ingress row is inserted at the supplier-cost you paid (per-unit). qty_remaining is set equal to the quantity received. The row is the new newest layer; nothing about pre-existing layers changes. This is true whether the restock comes from a purchase order, a manual stock adjustment, or a return marked resaleable.

Sale

For each line on the invoice, Fexl Lite asks lineCOGS to resolve the cost. The priority chain:

1. Serialised items — the per-unit cost stamped on serialized_items.cost when the unit was received.
2. Batched items — quantity × cost from the specific inventory_batches row drawn from.
3. Quantity-tracked items — drain oldest open FIFO layers in inventory_transactions until the line quantity is satisfied. Side-effect: qty_remaining is decremented on every layer touched, atomically inside the sale’s database transaction.
4. Legacy fallback — for products with no layer history (a pure-service product, or pre-v70 stock that couldn’t be reconstructed), fall back to the product’s stored cost.

Whichever priority resolves, the resulting per-unit cost is copied onto the sale’s egress audit row (inventory_transactions.cost for the egress) and onto items[i].unitCost in the invoice JSON. That cost is frozen. Reports read the audit row, not the live product cost — so a price increase next week won’t retroactively change last week’s gross profit.

Bonus units (BOGO, free gifts) are split off from the main sale and posted to bonus expense (5020) at the bonus-allocated cost; non-bonus units stay on regular COGS (5010). The FIFO walk treats both buckets the same — only the journal-entry side cares about the split.

Cancel an invoice

When you cancel an invoice, the sale’s egress reverses and the units flow back into stock. The cost used for the restoring ingress row mirrors the original sale’s per-unit cost, resolved by the same priority chain the refund path uses (resolveReturnUnitCost) — serial cost first, then batch cost, then the invoice-item unitCost snapshot, then the original sale’s posted COGS, then the egress audit row. 🐞 #41 · v1.6.100 made this match exactly: cancel-ingress cost = sale-egress cost, even for bonus items where COGS split across 5010 and 5020.

Refund (with disposition)

The refund wizard writes one of three things per returning unit:

• Resaleable — a fresh ingress at the resolved unit cost. The unit becomes the newest layer; it does not slot back into its original layer (that layer was closed when the sale drained it). This is FIFO-correct: the unit re-enters circulation as if it were just received.
• Defect — an egress to the defect-inventory account (5010 for non-bonus, 5020 for bonus units, split correctly per 🐞 #40 · v1.6.100 ). No layer movement on the regular FIFO chain.
• Dispose — an immediate write-off egress. Cost moves to disposal expense.

Reading the cost

The unit cost on an invoice (items[i].unitCost) is the FIFO unit cost at the time of sale, frozen. Every report that needs cost-of-goods reads either that snapshot or the matching egress audit row in inventory_transactions — never the live products.cost_price. This is what makes the books reproducible: you can re-run the P&L for January in June and get the same answer, because the costs that ran through January’s COGS were captured on January’s invoices and never touched again.

A short summary of where each report gets its cost:

• P&L → COGS — sum of egress audit rows in the period.
• Balance Sheet → Inventory — qty_remaining × cost summed across open layers.
• Inventory Valuation report — same as balance sheet, but per-product.
• Inventory Moves report — every ingress and egress row, with cost and qty_remaining columns visible.
• General Ledger → 5010 / 5020 — the journal entries posted at sale time, equal to the sum of egress audit costs (modulo bonus split).

✦

Why FIFO and not weighted average?

Weighted-average cost (WAC) is simpler — every unit gets the same blended cost number — but it loses the per-layer audit trail. Under price drift, WAC tells you the average, not the truth: a $5 unit and a $7 unit both leave at $6, and you can’t reconstruct which was which afterwards. FIFO matches how shop owners actually think (“the old stock sold first”) and stays correct under arbitrary price movement. The tradeoff is one extra column on one table — qty_remaining — and a tiny amount of bookkeeping per sale. Worth it.

Historical data

If you upgraded to v1.6.100 from an older version, four migrations ran the first time the server booted:

• v70 — added the qty_remaining column to inventory_transactions and stamped every existing ingress row with its original quantity (or the remainder, if later egresses had already drawn from it). After this migration, the FIFO walk works on every product that has any tracked ingress history.
• v71 — replayed FIFO for every (tenant, product, variant) group and rewrote the cost on historical sale-egress rows. Pre-v70, some egresses had a NULL cost or carried the sale price by mistake; v71 walks the layers chronologically and stamps the right per-unit cost on each one.
• v72 — backfilled items[i].unitCost on historical invoices from the corrected egress audit rows v71 just produced. Reports that read unitCost from the invoice JSON now match the journal entries exactly.
• v73 / v75 — final fixups for the rows v71 couldn’t reach: cancel/return ingresses with no layer history (v73), and historical defect-egress rows that were written without a cost (v75 — 🐞 #40 · v1.6.100 ).

!

Your old reports may look slightly different — that's the fix

If you compare a P&L or Balance Sheet from before the upgrade against the same period after the upgrade, small differences are expected. The new numbers are the correct ones, reconstructed from the FIFO walk over your real ingress history. If the difference is large for one product, look at its inventory-moves history — the most common cause is a pre-v70 sale that posted COGS at the live WAC of the day, which v71 has now corrected to the actual layer cost.

ⓘ

Migrations are idempotent

All four backfills check WHERE cost IS NULL (or equivalent) before writing, so they’re safe to re-run. They will not double-count, and they will not touch a row that already has a correct value.

Related

• Tracking modes — quantity, serialised, batched
• Restock a product
• Cancel an invoice — what happens to the layers when a sale reverses
• The 5-step return wizard — disposition rules for refunds
• Inventory Valuation report — open layers × cost
• Release notes — v1.6.100 — bugs #41, #44, #46, #49 and the v70–v75 migrations