The September 28 bank statement closes at $40. The books have a $350 deposit and a $300 payment, both cleared. Starting from zero, that leaves $50. The last line on the statement is a $10 service charge. That one is missing from the books.

A test with just those amounts is easy to fool. Forget the date filter and it still passes. Run the fee import once and you’ll never find out what a retry does. Give the fixture a $25 payment that cleared the next morning. Then deliver the September fee a second time.

The number to assert

For this test, the gap is the statement closing balance minus the cleared book balance at the cutoff. For this statement that starts at 4000 - (35000 - 30000) = -1000 cents. A negative result means the cleared books are higher than the bank. The statement line is what tells us why.

Save this block as reconcile.sql and run sqlite3 -bail :memory: < reconcile.sql. Each entry belongs to one checking account. Amounts are signed integer cents, dates are ISO dates, and cleared_on is the day the entry cleared the bank.

CREATE TEMP TABLE book_entry ( bank_ref TEXT PRIMARY KEY NOT NULL, cleared_on TEXT NOT NULL, cents INTEGER NOT NULL ) STRICT; INSERT INTO book_entry VALUES ('deposit-17', '2026-09-27', 35000), ('payment-18', '2026-09-28', -30000), ('payment-19', '2026-09-29', -2500); CREATE TEMP VIEW at_cutoff AS SELECT 4000 - COALESCE(SUM(cents), 0) AS gap_cents, COUNT(*) AS cleared_rows FROM book_entry WHERE cleared_on <= '2026-09-28'; CREATE TEMP TABLE checks ( step TEXT NOT NULL, gap_cents INTEGER NOT NULL, cleared_rows INTEGER NOT NULL, expected_gap INTEGER NOT NULL, expected_rows INTEGER NOT NULL, CHECK (gap_cents = expected_gap AND cleared_rows = expected_rows) ) STRICT; INSERT INTO checks SELECT 'fee missing', gap_cents, cleared_rows, -1000, 2 FROM at_cutoff; INSERT INTO book_entry VALUES ('fee-20', '2026-09-28', -1000) ON CONFLICT (bank_ref) DO NOTHING; INSERT INTO checks SELECT 'fee recorded', gap_cents, cleared_rows, 0, 3 FROM at_cutoff; -- The importer delivers the same bank line again. INSERT INTO book_entry VALUES ('fee-20', '2026-09-28', -1000) ON CONFLICT (bank_ref) DO NOTHING; INSERT INTO checks SELECT 'fee replayed', gap_cents, cleared_rows, 0, 3 FROM at_cutoff; SELECT step, gap_cents, cleared_rows FROM checks ORDER BY rowid; 
fee missing|-1000|2 fee recorded|0|3 fee replayed|0|3

The last two rows are deliberately identical apart from the label. After the fee posts, $310 has gone out against $350 in. Replaying the same bank line should leave both the $40 balance and the count of three cleared entries alone.

Two ways to break it

Remove the WHERE line (keeping the statement’s final semicolon). The next morning’s $25 payment slips into September 28. The first gap flips from −$10 to +$15 and the first CHECK fails. On a fresh run, change only the replay’s reference from fee-20 to fee-21. The database accepts a fourth entry, the gap becomes +$10, and the last CHECK fails.

That second failure says something about the import contract. Two genuine fees can have the same date and amount. This example relies on a stable reference for a bank line. If references are unique only within an account, include the account in the unique key. If a bank later corrects the amount under an existing reference, that needs a correction path of its own.

ChatGPT drafted this article and its SQL. The example and both failure cases were tested with SQLite 3.43.2.