Implementing FIFO Inventory Costing with C and SQLite

Introduction

FIFO means first in, first out, or “use the oldest available stock first.” The program keeps each batch separate, so it can calculate the cost of an issue when one sale uses stock bought at different prices.

FIFO inventory costing answers a question that a single quantity cannot: when stock acquired at different prices leaves inventory, which cost should leave with it?

The Inventory-cli-app repository answers with first in, first out. Each stock addition is a batch with its own unit cost and remaining quantity. A stock issue consumes the earliest eligible batch first, then continues into later batches until the requested quantity has been allocated.

That is the mental model. The implementation adds an important qualification: this project defines “first” by an addition row's auto-incrementing id, not by a transaction-date column. In this article, a batch means a group of units added at one time with its own unit cost. An allocation records how many units from that batch were used by a particular issue.

One issue can span several batches

Consider this reproducible example. The IDs are illustrative rather than copied from the repository's database:

An issue of seven units exhausts addition 101 and takes two units from addition 102. The item changes from nine units worth $98 to two units worth $24. FIFO does not average the two purchase prices: it preserves the cost attached to each consumed batch.

The example demonstrates the arithmetic. The repository source establishes how that arithmetic is persisted.

The schema stores stock and its allocation history

Four tables participate in the operation:

• items stores the current aggregate quantity and total value.
• stock_additions stores each incoming batch's added_qty, unit_cost, and unused_qty.
• stock_issues stores the quantity requested for one outgoing transaction.
• stock_issues_add_relation records how much of that issue came from each addition.

When a batch is created, unused_qty starts equal to added_qty. The two values have different jobs: added_qty preserves the original batch size, while unused_qty decreases as later issues consume it.

For the example above, the relation table would need two rows: five units connecting the issue to addition 101, and two units connecting it to addition 102. That detail makes a multi-batch issue traceable. It also lets the project's deletion function identify which additions to restore.

The schema declares foreign keys from additions and issues to items, and from each relation row to both its issue and addition. A foreign key is a database rule that connects a row to a valid row in another table. The initialization code explicitly runs PRAGMA foreign_keys = ON; SQLite requires foreign-key enforcement to be enabled for each connection.

What “oldest” means in this project

The issue function selects available batches with this query:

SELECT id, unused_qty, unit_cost
FROM stock_additions
WHERE item_id = ? AND unused_qty > 0
ORDER BY id ASC;

Three parts establish the invariant:

1. item_id = ? excludes additions belonging to other items.
2. unused_qty > 0 excludes exhausted batches.
3. ORDER BY id ASC presents eligible additions in increasing insertion ID.

The loop can therefore consume the first returned row before stepping to the next. Modifying or removing that ordering would make row order unspecified and break the application's chosen FIFO rule.

The limitation is explicit in the source: stock_additions has no transaction-date column. A lower id normally means an earlier insert, but it does not necessarily mean an earlier real-world purchase. Back-dated imports, corrections, or delete-and-reinsert workflows would need a documented business timestamp and a deterministic tie-breaker if accounting chronology mattered.

Following db_issue_stock()

The function prepares the issue insert, the ordered batch query, the relation insert, the batch update, and the item-total update. Its central loop is equivalent to this shortened pseudocode:

remaining = requested quantity
total cost = 0

while remaining > 0:
    read next addition ordered by id
    if there is no row:
        roll back

    allocated = min(addition.unused_qty, remaining)
    remaining -= allocated
    total cost += addition.unit_cost * allocated

    insert relation(issue, addition, allocated)
    subtract allocated from addition.unused_qty

subtract requested quantity and total cost from item totals
commit

The key line is the minimum. If the current batch has more units than the issue still needs, allocation stops partway through that batch. If it has fewer, the batch reaches zero and the next loop iteration reads another row.

After each relation insert and addition update, the C code calls sqlite3_reset() so the prepared statements can be rebound and reused. The batch query remains positioned on its result set and advances with another sqlite3_step().

Why the relation row matters

Updating only unused_qty would produce today's totals, but it would lose the explanation for them. The relation table records a many-to-many allocation:

• one issue may consume several additions;
• one sufficiently large addition may supply several issues.

For the seven-unit example, an audit query can recover both contributing costs instead of guessing from the current item total. The repository's issue-reading query uses the recorded allocations to calculate an issue's total cost.

This is documented behavior in the schema and database layer. Calling it a complete accounting audit trail would be a design claim the project does not support: there are no business timestamps, immutable journal entries, user identities, or correction workflow in this learning application.

The transaction is the consistency boundary

Issuing stock changes several rows. The new issue, every allocation relation, every batch's unused_qty, and the aggregate values in items must agree. Committing only some of those changes would create contradictory data.

The main mutation path calls begin_txn(db) before writing. A transaction is a group of database changes treated as one operation. If selecting, inserting, or updating fails, it jumps to a transaction-error label that calls rollback_txn(db). Only after all batches and item totals are updated does it call commit_txn(db). COMMIT keeps the changes, while ROLLBACK abandons them.

This provides atomicity, meaning that the operation should either complete as a whole or leave the database as it was before the operation started. For this issue, the issue row, allocation rows, batch quantities, and item totals should therefore agree with one another.

The loop also detects disagreement between two representations of stock. The function first compares the supplied item's current_qty with the request. Later, if the ordered query runs out of positive unused_qty while remaining is still greater than zero, it reports insufficient FIFO stock and rolls back. That second check prevents a partial allocation from being committed when the aggregate says stock exists but the batches cannot supply it.

A source-observed transaction gap

There is one error path worth fixing before relying on the function more broadly. The current code begins its transaction and then performs the initial item.current_qty < issue_qty check. When that check fails, it jumps directly to statement cleanup rather than the rollback label.

Finalizing statements is not the same as ending an explicit transaction. SQLite documents that a transaction opened with BEGIN normally persists until COMMIT or ROLLBACK. Leaving it active can make a later BEGIN on the shared connection fail and can retain transaction state longer than intended.

A safer structure would validate obviously invalid input before BEGIN, while still rechecking database state inside the transaction, or route every post-BEGIN exit through one rollback-aware cleanup path. The database—not a previously loaded Item structure—should be the authoritative source for a concurrency-sensitive availability check.

That recommendation is a design judgement based on the inspected control flow. It is not a claim that the bug has produced a particular failure in a recorded run.

Deletion does not replay FIFO history

db_delete_stock_issue() uses the relation rows to add the deleted quantities back to the same stock_additions rows. It then removes the relations and issue and restores the item's aggregate quantity and value. Those changes are wrapped in a transaction.

What it does not do is recalculate every later issue. Imagine issue A consumed the rest of addition 101 and part of 102, then issue B consumed more of 102. Deleting issue A restores units to both additions, but issue B remains related to 102. If history were replayed without issue A, FIFO would have allocated issue B to the newly available units in 101 first.

The resulting quantities may balance while the surviving allocation history no longer represents a fresh FIFO calculation over the remaining transactions. Rebuilding that history would require a clear ordering for issues and additions, a correction policy, and a transactional replay. The repository does not implement that workflow, so this project should not be presented as production accounting software.

Foreign keys protect a different property: relation rows cannot refer to missing parent rows while enforcement is enabled. They do not prove that the chosen allocation is chronologically correct, and they cannot recalculate FIFO after a deletion. Referential integrity and business-rule integrity are related but separate responsibilities.

What this project taught me

The interesting part of FIFO was not the min() calculation by itself. It was seeing one business operation cross several representations of the same event: an issue header, per-batch allocations, remaining batch quantities, and aggregate item totals.

That duplication can make reads convenient and preserve useful history, but it creates invariants that transactions and tests must defend. A useful next test suite would cover an exact batch match, a partial batch, a multi-batch issue, insufficient aggregate stock, disagreement between aggregate and batch totals, a failure midway through allocation, and deletion followed by later issues.

Statement types: fact, demonstration, and judgement

• Repository fact: available additions are selected with unused_qty > 0 and ORDER BY id ASC; allocations are stored in stock_issues_add_relation; successful issues update batch and item totals inside an explicit transaction.
• Reproducible demonstration: issuing seven units from five units at $10 and four units at $12 leaves two units and allocates $74 of cost.
• Design judgement: transaction cleanup should cover every path after BEGIN, and deleting historical issues should either trigger a documented replay policy or be restricted in a system that promises chronological FIFO accounting.

Key takeaways

• unused_qty is the per-batch state that tells FIFO how many units remain available.
• This implementation defines oldest stock by stock_additions.id ASC, not by a transaction date.
• One stock issue can consume multiple batches, and the relation table preserves each allocation.
• The issue cost is the sum of each allocated quantity multiplied by that batch's unit cost.
• A transaction keeps the issue, allocation rows, batch balances, and item totals together—but every exit after BEGIN must end that transaction deliberately.
• Foreign keys protect references; they do not validate FIFO chronology.
• Deleting an issue restores its original batches but does not replay later issues, so the remaining history may not represent recalculated FIFO.

Sources

• Author repository: Inventory-cli-app
• Project database implementation
• Project database initialization and schema
• SQLite: Transactions
• SQLite: Foreign-key support
• SQLite: Introduction to the C/C++ interface