stock balances is a minefield**.

In SQL textbooks, everything looks clean: run SUM(quantity) over transaction history or use a ROW_NUMBER() window function — done. But as soon as the system grows to millions of rows, these "pretty" academic solutions turn the RDBMS into a sluggish bottleneck. And when real business scenarios appear (negative sales, backdated documents, on-the-fly manufacturing), cost price quietly explodes exponentially.

What's worse, modern AI assistants and incoming junior "optimizers" think strictly in terms of these textbooks. They see low-level RDBMS hacks in the code, panic, make the code "clean"... and a week later, the accounting department receives garbage data.

In this article, I'll break down 4 fundamental pain points of stock registers, explain the query optimizer physics of MySQL/MariaDB, and demonstrate the Snapshot Log architecture we arrived at after 12 years of developing high-load systems.

Pain Point #1. Querying Stock Balance: The Failure of SUM() and the Physics of the LIMIT Hack

Why SUM() and Window Functions Kill the Database

Calculating stock balance via SUM(quantity) across the entire transaction history on multi-million row datasets is a classic architectural mistake. $O(N)$ complexity on every single check turns any report into an hour-long wait.

The second attempt by "academics" is using window functions:

-- Pseudo-pretty textbook SQL
SELECT item_id, warehouse_id, remains_quantity
FROM (
    SELECT item_id, warehouse_id, remains_quantity,
           ROW_NUMBER() OVER (PARTITION BY warehouse_id, item_id ORDER BY dt DESC, id DESC) as rn
    FROM stock_register
    WHERE dt <= '2026-07-01'
) t WHERE rn = 1;

Enter fullscreen mode Exit fullscreen mode

The Physics of Disaster: In MySQL/MariaDB, this query on large volumes leads to a fatal filesort. The engine is forced to create a temporary table on disk, scan gigabytes of indexes, and sort them in a temp file. Performance drops by orders of magnitude.

Architectural Solution: Snapshot Log

Instead of a simple movement log, we store a state snapshot (Snapshot Log). Every document execution increments two numbers:

$$
\text{remains_quantity}{\text{new}} = \text{remains_quantity}{\text{old}} + \Delta\text{qty}
$$

Calculation complexity is $$O(1)$$. The current balance is always the last recorded row.

The LIMIT Hack and the AI Dead End

To fetch the last row without window functions, subquery grouping is used. But RDBMS query optimizers are "smart": if you write a standard subquery with ORDER BY, the optimizer simply discards the sorting, and GROUP BY returns random rows.

To force the engine to actually sort the data, forced subquery materialization is applied:

SELECT 
    r.warehouse_id, 
    r.item_id, 
    IFNULL(remains_quantity, 0) AS remains,
    IFNULL(remains_costsum, 0) AS sumremains
FROM (
    SELECT warehouse_id, item_id, remains_quantity, remains_costsum
    FROM stock_register
    WHERE dt <= :target_dt
      AND warehouse_id IN (:warehouses)
      AND item_id IN (:items)
    /* Subquery materialization + sorting triad */
    ORDER BY dt DESC, is_overplus_neg ASC, id DESC 
    LIMIT 9999999999
) AS r
GROUP BY warehouse_id, item_id;

Enter fullscreen mode Exit fullscreen mode

Production Drama: The same story repeats regularly. A new developer (or AI assistant) arrives, sees the wild LIMIT 9999999999, and thinks: "What a hack! Let me make this clean and remove the LIMIT". The query suddenly flies, everyone is happy, and a day later stock balances break because MariaDB grouped random rows.

Pain Point #2. Out-of-Stock Consumption: Crossing "Zero"

In an ideal world, negative inventory doesn't exist. In real business — a cashier makes a mistake, an invoice wasn't posted in time, physical goods were dispatched, but they aren't in the database yet.

If negative balances are allowed, a mathematical anomaly occurs. If item balance goes down to -5 units at zero cost, and then a batch arrives at $100, basic school math produces a wild distortion in cost price.

Correctly Handling the Transition from Negative to Positive:

  1. Disabling Negative Balance via Patches (is_overplus): When balance drops below zero, the system auto-generates a virtual receiving entry patch (is_overplus = 1) that keeps the balance at zero.
  2. Allowed Negative Balance: If negatives are allowed, then when posting a receiving document after a negative balance, remains_costsum is forcibly calculated from the last positive receiving price (lastprice), rather than the current negative balance:

$$\text{remains_costsum} = \text{remains_quantity} \times \text{lastprice}$$

Pain Point #3. Just-In-Time Manufacturing (Transit)

In food service or retail, nobody creates separate "Production" documents before selling every burger or cup of coffee. Items are sold "on the fly".

At that exact moment, a transit calculation occurs within a single document:

  • Ingredients (children, parentid) are written off the warehouse, establishing cost price.
  • The finished dish (parent, id) is received for the total cost of ingredients and immediately written off to zero within the same transaction.
  • In the register, remains_quantity for the dish itself doesn't change (stays 0), but financial accounting records the exact cost price expense without distorting the warehouse average price.

Pain Point #4. Backdated Documents and the Point of No Return ($mindt)

When a user enters a document dated last Tuesday, the entire chain of calculated stock balances (remains_quantity, remains_costsum) after that date becomes invalid.

Recalculating 5 years of history is suicide for the database.

To solve this, the "Point of No Return" ($mindt) algorithm is used. We find the date of the last guaranteed positive receiving transaction for the items:

SELECT warehouse_id, item_id, MAX(dt) AS mindt
FROM stock_register
WHERE dt <= :target_dt
  AND is_overplus = 0 
  AND quantity > 0
GROUP BY warehouse_id, item_id;

Enter fullscreen mode Exit fullscreen mode

All calculations and recalculation cascades are cut off by the condition dt >= $mindt, reducing scanned data volume by 90–95%.

Summary and Tool

Inventory accounting isn't about "pretty code." It's about understanding the query physics of a specific RDBMS, managing transactional integrity, and handling edge-case mathematics.

Explaining these rules every time to new developers or arguing with LLM models that constantly try to drop LIMIT 9999999999 or rewrite queries with ROW_NUMBER() gets exhausting fast.

That's why I packed this entire 12-year architecture, sorting triad, is_overplus logic, and MariaDB hacks into a specialized system prompt (AI Trainer).

If you use AI assistants to generate SQL and design backend architecture, you can simply plug this system prompt into context. After that, any AI starts generating verification queries, DDL, and recalculation functions strictly according to senior engineering standards, without rookie mistakes.