Why can a positive final stock balance hide an impossible issue?

Prepare the diagnostic input

The end balance can recover after a later receipt, while an earlier issue exceeded stock available at that point. Compute a running quantity by the fixture’s posting sequence and filter negative states. The diagnostic identifies the first evidence to inspect, not which movement to delete.

Use a fresh SQLite test database for the complete setup below. Every record is fictional; quantities are units and input monetary columns ending in cents use integer currency cents. Calculated averages may include fractions of a cent. Input previews include every table in this small fixture.

CREATE TABLE movements(id INTEGER PRIMARY KEY,sku TEXT,delta_qty INTEGER);
INSERT INTO movements VALUES (1,'A',5),(2,'A',-7),(3,'A',4);

Reproduce the misleading result and correct it

SELECT sku,SUM(delta_qty) AS balance_qty FROM movements GROUP BY sku HAVING SUM(delta_qty)<0 ORDER BY sku;

WITH running AS (SELECT id,sku,SUM(delta_qty) OVER(PARTITION BY sku ORDER BY id ROWS UNBOUNDED PRECEDING) AS balance_qty FROM movements)
SELECT id,sku,balance_qty FROM running WHERE balance_qty<0 ORDER BY sku,id;

Receipt five, issue seven and receipt four end at two units. The wrong final-only check reports no problem. The corrected path shows movement 2 at −2. A missing earlier receipt or wrong posting order could explain it; evidence must decide.

Check the boundary before applying the fix

Change the issue to exactly five. The running balance reaches zero but never becomes negative, so the exception list is empty.

For the boundary test, recreate the complete setup in another fresh synthetic database, apply only the change below, then rerun the corrected query. Do not apply a test mutation to a live system.

UPDATE movements SET delta_qty=-5 WHERE id=2;

Starting quantity is zero and ID order is the declared posting sequence. Real stock requires opening balance, effective ordering and permitted negative-stock policy.

The displayed bad, corrected and boundary results were executed in SQLite and checked against separately authored expected rows. This demonstrates the stated fixture only; it does not test other database engines, production performance or your live schema.