Why Even Advanced AI Breaks Stock Registers: 12 Years of Snapshot Log Evolution and RDBMS Physics

A developer with 12 years of experience building high-load systems explains why advanced AI assistants and junior developers often break stock registers by applying textbook SQL solutions. The article details four fundamental pain points of stock registers and presents the Snapshot Log architecture, which uses O(1) state snapshots instead of costly SUM queries or window functions.

If you have ever designed inventory accounting, an ERP system, or billing, you know: calculating 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. SUM and the Physics of the LIMIT Hack 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; 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. Instead of a simple movement log, we store a state snapshot Snapshot Log . Every document execution increments two numbers: remains quantity new = remains quantity old + Δqty Calculation complexity is O 1 . The current balance is always the last recorded row . 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; 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. 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. is overplus : is overplus = 1 that keeps the balance at zero. remains costsum is lastprice remains costsum = remains quantity lastprice 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: parentid are written off the warehouse, establishing cost price. id is received for the total cost of ingredients and 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.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; All calculations and recalculation cascades are cut off by the condition dt = $mindt , reducing scanned data volume by 90–95%. 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.