Why does a debit increase the cash reconciliation total?

Prepare the diagnostic input

The bank fixture stores every amount as a positive magnitude and carries direction in kind. Summing amount alone treats withdrawals as incoming cash. Translate the documented fixture direction into signed amounts before summing; do not infer direction from description text.

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 bank_rows(id TEXT PRIMARY KEY,kind TEXT,amount_cents INTEGER);
INSERT INTO bank_rows VALUES ('B1','credit',10000),('B2','debit',3000);

Reproduce the misleading result and correct it

SELECT SUM(amount_cents) AS net_change_cents FROM bank_rows;

SELECT SUM(CASE WHEN kind='credit' THEN amount_cents WHEN kind='debit' THEN -amount_cents END) AS net_change_cents FROM bank_rows;

A 10000-cent credit and 3000-cent debit change the account by 7000 cents, not 13000. This is account movement, not invoice revenue or a closing balance. An opening balance would be needed to derive closing cash.

Check the boundary before applying the fix

Add a 7000-cent debit. The net movement becomes zero, showing that zero can represent offsetting inflows and outflows rather than no activity.

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.

INSERT INTO bank_rows VALUES ('B3','debit',7000);

The two kind values are fixture rules, not universal bank-code meanings. Unknown kinds produce NULL in the CASE and must be separately flagged before relying on totals.

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.