askrobots / dbbasic-object-server
Public
Runs live, versioned Python application objects on one VM, with a suite of apps: projects, notes, tasks, files and more.
danq9
signup: the person chooses the password and name on the confirm page
83c7aa6
2 weeks, 5 days ago
History
test_bank_reconciliation_view.py · 15.9 KB · 332 lines
| 1 | """The bank reconciliation statement (plan/bank-import-reconciliation-spec.md |
| 2 | section 6): object_banking.reconciliation()'s fold math, and site_reconcile, |
| 3 | the page that renders it. |
| 4 | |
| 5 | The control under test is the same one tests/test_bank_reconciliation.py |
| 6 | checks for the matcher and resolution verbs: nobody -- not an anonymous |
| 7 | visitor, not another signed-in owner -- can see a bank account's |
| 8 | reconciliation state except the owner who set it up. The math itself is |
| 9 | the classic tie: bank closing balance minus book balance should equal |
| 10 | exactly the outstanding timing items once the unmatched tail is empty. |
| 11 | """ |
| 12 | |
| 13 | import json |
| 14 | import pathlib |
| 15 | |
| 16 | import object_banking |
| 17 | import object_execution |
| 18 | import object_records |
| 19 | import python_object_runtime |
| 20 | from conftest import stage_collection |
| 21 | |
| 22 | REPO_ROOT = pathlib.Path(__file__).resolve().parents[1] |
| 23 | PACKAGES = REPO_ROOT / "packages" |
| 24 | BANKING_OBJECTS = PACKAGES / "app-banking" / "objects" |
| 25 | RUNTIME = python_object_runtime.PythonObjectRuntime() |
| 26 | |
| 27 | DAN = {"user_id": "dan"} |
| 28 | PAT = {"user_id": "pat"} |
| 29 | |
| 30 | |
| 31 | def setup_env(tmp_path, monkeypatch): |
| 32 | data_dir = tmp_path / "data" |
| 33 | wanted = [("app-banking", "value_accounts"), ("app-banking", "bank_statement_imports"), |
| 34 | ("app-banking", "bank_lines"), |
| 35 | ("app-finance", "fin_accounts"), ("app-finance", "fin_journals"), |
| 36 | ("app-finance", "fin_journal_lines")] |
| 37 | for pkg, name in wanted: |
| 38 | stage_collection(data_dir, pkg, name) |
| 39 | monkeypatch.setenv("DBBASIC_DATA_DIR", str(data_dir)) |
| 40 | return data_dir |
| 41 | |
| 42 | |
| 43 | def make_fin_account(data_dir, aid, owner="dan"): |
| 44 | return object_records.create_collection_record( |
| 45 | "fin_accounts", {"id": aid, "name": aid, "account_type": "asset", "owner_id": owner}, |
| 46 | base_dir=data_dir) |
| 47 | |
| 48 | |
| 49 | def make_bank_account(data_dir, bid, *, fin_account_id, owner="dan", name=None, currency="USD"): |
| 50 | return object_records.create_collection_record( |
| 51 | "value_accounts", |
| 52 | {"id": bid, "name": name or bid, "fin_account_id": fin_account_id, |
| 53 | "owner_id": owner, "kind": "bank"}, |
| 54 | base_dir=data_dir) |
| 55 | |
| 56 | |
| 57 | def make_import(data_dir, iid, *, bank_account_id, period_end, closing_cents, |
| 58 | opening_cents=0, status="accepted", flags=None, owner="dan"): |
| 59 | return object_records.create_collection_record( |
| 60 | "bank_statement_imports", |
| 61 | {"id": iid, "bank_account_id": bank_account_id, "period_start": "2026-07-01", |
| 62 | "period_end": period_end, "opening_balance_cents": str(opening_cents), |
| 63 | "closing_balance_cents": str(closing_cents), "status": status, |
| 64 | "flags": json.dumps(flags or {}), "owner_id": owner}, |
| 65 | base_dir=data_dir) |
| 66 | |
| 67 | |
| 68 | def make_line(data_dir, lid, *, bank_account_id, amount, on="2026-07-02", desc="", |
| 69 | status="unmatched", resolved_as="", owner="dan", suggestions=""): |
| 70 | return object_records.create_collection_record( |
| 71 | "bank_lines", |
| 72 | {"id": lid, "bank_account_id": bank_account_id, "posted_on": on, |
| 73 | "amount_cents": str(amount), "description": desc, "raw": f"raw:{desc}", |
| 74 | "line_hash": f"h-{lid}", "match_status": status, "resolved_as": resolved_as, |
| 75 | "suggestions": suggestions, "owner_id": owner}, |
| 76 | base_dir=data_dir) |
| 77 | |
| 78 | |
| 79 | def make_journal(data_dir, jid, *, date="2026-07-05", status="posted", owner="dan"): |
| 80 | return object_records.create_collection_record( |
| 81 | "fin_journals", {"id": jid, "date": date, "status": status, "owner_id": owner}, |
| 82 | base_dir=data_dir) |
| 83 | |
| 84 | |
| 85 | def make_journal_line(data_dir, lid, *, journal_id, account_id, debit=0, credit=0, owner="dan"): |
| 86 | return object_records.create_collection_record( |
| 87 | "fin_journal_lines", |
| 88 | {"id": lid, "journal_id": journal_id, "account_id": account_id, |
| 89 | "debit_cents": str(debit), "credit_cents": str(credit), "owner_id": owner}, |
| 90 | base_dir=data_dir) |
| 91 | |
| 92 | |
| 93 | def page(method, payload): |
| 94 | result = object_execution.execute_object( |
| 95 | RUNTIME, |
| 96 | object_execution.ObjectExecutionRequest( |
| 97 | "site_reconcile", method=method, payload=payload), |
| 98 | roots=[BANKING_OBJECTS]) |
| 99 | return result.result |
| 100 | |
| 101 | |
| 102 | # --- reconciliation() fold math ---------------------------------------------- |
| 103 | |
| 104 | def test_no_data_folds_to_all_nones_never_raises(tmp_path, monkeypatch): |
| 105 | setup_env(tmp_path, monkeypatch) |
| 106 | result = object_banking.reconciliation("no-such-account", base_dir=tmp_path / "data") |
| 107 | assert result["bank_closing_cents"] is None |
| 108 | assert result["book_balance_cents"] is None |
| 109 | assert result["difference_cents"] is None |
| 110 | assert result["reconciled"] is False |
| 111 | assert result["assurance"] is None |
| 112 | |
| 113 | |
| 114 | def test_bank_closing_book_balance_and_difference(tmp_path, monkeypatch): |
| 115 | data_dir = setup_env(tmp_path, monkeypatch) |
| 116 | make_fin_account(data_dir, "cash") |
| 117 | make_bank_account(data_dir, "bank-1", fin_account_id="cash") |
| 118 | make_import(data_dir, "imp1", bank_account_id="bank-1", |
| 119 | period_end="2026-07-31", closing_cents=100000, |
| 120 | flags={"tie_out": {"ran": True, "passed": True}}) |
| 121 | # Book side: one posted journal debiting cash 90000. |
| 122 | make_journal(data_dir, "j1") |
| 123 | make_journal_line(data_dir, "jl1", journal_id="j1", account_id="cash", debit=90000) |
| 124 | |
| 125 | result = object_banking.reconciliation("bank-1", base_dir=data_dir) |
| 126 | assert result["bank_closing_cents"] == 100000 |
| 127 | assert result["bank_statement_date"] == "2026-07-31" |
| 128 | assert result["book_balance_cents"] == 90000 |
| 129 | assert result["difference_cents"] == 10000 |
| 130 | |
| 131 | |
| 132 | def test_reconciled_true_when_difference_equals_timing_items(tmp_path, monkeypatch): |
| 133 | data_dir = setup_env(tmp_path, monkeypatch) |
| 134 | make_fin_account(data_dir, "cash") |
| 135 | make_bank_account(data_dir, "bank-1", fin_account_id="cash") |
| 136 | make_import(data_dir, "imp1", bank_account_id="bank-1", |
| 137 | period_end="2026-07-31", closing_cents=100000, |
| 138 | flags={"tie_out": {"ran": True, "passed": True}}) |
| 139 | make_journal(data_dir, "j1") |
| 140 | make_journal_line(data_dir, "jl1", journal_id="j1", account_id="cash", debit=90000) |
| 141 | # A deposit-in-transit: on the statement (part of the 100000 closing |
| 142 | # balance) but never booked -- classic outstanding timing item. |
| 143 | make_line(data_dir, "L1", bank_account_id="bank-1", amount=10000, desc="DEPOSIT IN TRANSIT", |
| 144 | status="resolved", resolved_as="timing") |
| 145 | |
| 146 | result = object_banking.reconciliation("bank-1", base_dir=data_dir) |
| 147 | assert result["timing_cents"] == 10000 |
| 148 | assert result["timing_count"] == 1 |
| 149 | assert result["difference_cents"] == 10000 |
| 150 | assert result["reconciled"] is True |
| 151 | |
| 152 | |
| 153 | def test_unmatched_tail_breaks_reconciliation(tmp_path, monkeypatch): |
| 154 | data_dir = setup_env(tmp_path, monkeypatch) |
| 155 | make_fin_account(data_dir, "cash") |
| 156 | make_bank_account(data_dir, "bank-1", fin_account_id="cash") |
| 157 | make_import(data_dir, "imp1", bank_account_id="bank-1", |
| 158 | period_end="2026-07-31", closing_cents=100000, |
| 159 | flags={"tie_out": {"ran": True, "passed": True}}) |
| 160 | make_journal(data_dir, "j1") |
| 161 | make_journal_line(data_dir, "jl1", journal_id="j1", account_id="cash", debit=90000) |
| 162 | # Same 10000 gap, but this time it is an unexplained unmatched line, |
| 163 | # not a timing item -- the numbers still differ by 10000, but the |
| 164 | # difference is not EXPLAINED, so reconciled must be False. |
| 165 | make_line(data_dir, "L1", bank_account_id="bank-1", amount=10000, desc="MYSTERY DEPOSIT", |
| 166 | status="unmatched") |
| 167 | |
| 168 | result = object_banking.reconciliation("bank-1", base_dir=data_dir) |
| 169 | assert result["unmatched_cents"] == 10000 |
| 170 | assert result["unmatched_count"] == 1 |
| 171 | assert result["difference_cents"] == 10000 |
| 172 | assert result["timing_cents"] == 0 |
| 173 | assert result["reconciled"] is False |
| 174 | |
| 175 | |
| 176 | def test_matched_and_resolved_non_timing_lines_count_as_reconciled(tmp_path, monkeypatch): |
| 177 | data_dir = setup_env(tmp_path, monkeypatch) |
| 178 | make_bank_account(data_dir, "bank-1", fin_account_id="") |
| 179 | make_line(data_dir, "L1", bank_account_id="bank-1", amount=5000, status="matched") |
| 180 | make_line(data_dir, "L2", bank_account_id="bank-1", amount=-2500, status="resolved", resolved_as="fee") |
| 181 | make_line(data_dir, "L3", bank_account_id="bank-1", amount=200, status="suggested") |
| 182 | make_line(data_dir, "L4", bank_account_id="bank-1", amount=100, status="unmatched") |
| 183 | |
| 184 | result = object_banking.reconciliation("bank-1", base_dir=data_dir) |
| 185 | assert result["matched_cents"] == 5000 + (-2500) |
| 186 | assert result["unmatched_cents"] == 200 + 100 |
| 187 | assert result["suggested_count"] == 1 |
| 188 | assert result["unmatched_count"] == 1 |
| 189 | # No fin_account_id on this bank account -- book_balance_cents is |
| 190 | # genuinely unknown, not zero. |
| 191 | assert result["book_balance_cents"] is None |
| 192 | assert result["difference_cents"] is None |
| 193 | |
| 194 | |
| 195 | def test_as_of_scopes_imports_and_journals(tmp_path, monkeypatch): |
| 196 | data_dir = setup_env(tmp_path, monkeypatch) |
| 197 | make_fin_account(data_dir, "cash") |
| 198 | make_bank_account(data_dir, "bank-1", fin_account_id="cash") |
| 199 | make_import(data_dir, "imp1", bank_account_id="bank-1", |
| 200 | period_end="2026-06-30", closing_cents=50000, |
| 201 | flags={"tie_out": {"ran": True, "passed": True}}) |
| 202 | make_import(data_dir, "imp2", bank_account_id="bank-1", |
| 203 | period_end="2026-07-31", closing_cents=100000, |
| 204 | flags={"tie_out": {"ran": True, "passed": True}}) |
| 205 | make_journal(data_dir, "j1", date="2026-06-15") |
| 206 | make_journal_line(data_dir, "jl1", journal_id="j1", account_id="cash", debit=50000) |
| 207 | make_journal(data_dir, "j2", date="2026-07-15") |
| 208 | make_journal_line(data_dir, "jl2", journal_id="j2", account_id="cash", debit=50000) |
| 209 | |
| 210 | as_of_june = object_banking.reconciliation("bank-1", base_dir=data_dir, as_of="2026-06-30") |
| 211 | assert as_of_june["bank_closing_cents"] == 50000 |
| 212 | assert as_of_june["book_balance_cents"] == 50000 |
| 213 | |
| 214 | unscoped = object_banking.reconciliation("bank-1", base_dir=data_dir) |
| 215 | assert unscoped["bank_closing_cents"] == 100000 |
| 216 | assert unscoped["book_balance_cents"] == 100000 |
| 217 | |
| 218 | |
| 219 | # --- assurance levels --------------------------------------------------------- |
| 220 | |
| 221 | def test_assurance_verified_when_tie_out_passed(tmp_path, monkeypatch): |
| 222 | data_dir = setup_env(tmp_path, monkeypatch) |
| 223 | make_bank_account(data_dir, "bank-1", fin_account_id="") |
| 224 | make_import(data_dir, "imp1", bank_account_id="bank-1", period_end="2026-07-31", |
| 225 | closing_cents=100000, flags={"tie_out": {"ran": True, "passed": True}}) |
| 226 | assert object_banking.reconciliation("bank-1", base_dir=data_dir)["assurance"] == "verified" |
| 227 | |
| 228 | |
| 229 | def test_assurance_unverified_when_tie_out_did_not_run(tmp_path, monkeypatch): |
| 230 | data_dir = setup_env(tmp_path, monkeypatch) |
| 231 | make_bank_account(data_dir, "bank-1", fin_account_id="") |
| 232 | make_import(data_dir, "imp1", bank_account_id="bank-1", period_end="2026-07-31", |
| 233 | closing_cents=100000, |
| 234 | flags={"tie_out": {"ran": False, "reason": "statement carried no balances"}}) |
| 235 | assert object_banking.reconciliation("bank-1", base_dir=data_dir)["assurance"] == "unverified" |
| 236 | |
| 237 | |
| 238 | def test_assurance_flagged_when_a_check_failed(tmp_path, monkeypatch): |
| 239 | data_dir = setup_env(tmp_path, monkeypatch) |
| 240 | make_bank_account(data_dir, "bank-1", fin_account_id="") |
| 241 | make_import(data_dir, "imp1", bank_account_id="bank-1", period_end="2026-07-31", |
| 242 | closing_cents=90000, status="flagged", |
| 243 | flags={"tie_out": {"ran": True, "passed": False, "delta_cents": -10000}}) |
| 244 | assert object_banking.reconciliation("bank-1", base_dir=data_dir)["assurance"] == "flagged" |
| 245 | |
| 246 | |
| 247 | def test_assurance_reflects_latest_import_even_when_bank_closing_falls_back(tmp_path, monkeypatch): |
| 248 | """The newest statement failed tie-out (flagged), so its own balance is |
| 249 | not trustworthy enough to report as bank_closing_cents -- that falls |
| 250 | back to the last accepted statement. But assurance must still surface |
| 251 | the flagged state of the NEWEST statement; silently showing 'verified' |
| 252 | off an old import would hide exactly the anti-fraud signal this report |
| 253 | exists to raise.""" |
| 254 | data_dir = setup_env(tmp_path, monkeypatch) |
| 255 | make_bank_account(data_dir, "bank-1", fin_account_id="") |
| 256 | make_import(data_dir, "imp1", bank_account_id="bank-1", period_end="2026-06-30", |
| 257 | closing_cents=50000, status="accepted", |
| 258 | flags={"tie_out": {"ran": True, "passed": True}}) |
| 259 | make_import(data_dir, "imp2", bank_account_id="bank-1", period_end="2026-07-31", |
| 260 | closing_cents=999999, status="flagged", |
| 261 | flags={"tie_out": {"ran": True, "passed": False, "delta_cents": -500}}) |
| 262 | |
| 263 | result = object_banking.reconciliation("bank-1", base_dir=data_dir) |
| 264 | assert result["bank_closing_cents"] == 50000 # last trustworthy number |
| 265 | assert result["bank_statement_date"] == "2026-06-30" |
| 266 | assert result["assurance"] == "flagged" # but the newest import failed |
| 267 | |
| 268 | |
| 269 | # --- the page: identity gate + owner scoping ---------------------------------- |
| 270 | |
| 271 | def test_anonymous_visitor_sees_a_sign_in_prompt_and_no_data(tmp_path, monkeypatch): |
| 272 | data_dir = setup_env(tmp_path, monkeypatch) |
| 273 | make_fin_account(data_dir, "cash") |
| 274 | make_bank_account(data_dir, "bank-1", fin_account_id="cash", name="Dan's Checking") |
| 275 | make_line(data_dir, "L1", bank_account_id="bank-1", amount=5000, desc="SECRET DEPOSIT") |
| 276 | |
| 277 | body = page("GET", {})["body"] |
| 278 | assert "Sign in" in body |
| 279 | assert "Dan's Checking" not in body |
| 280 | assert "SECRET DEPOSIT" not in body |
| 281 | |
| 282 | |
| 283 | def test_another_owners_account_and_lines_never_appear(tmp_path, monkeypatch): |
| 284 | data_dir = setup_env(tmp_path, monkeypatch) |
| 285 | make_fin_account(data_dir, "cash-dan", owner="dan") |
| 286 | make_fin_account(data_dir, "cash-pat", owner="pat") |
| 287 | make_bank_account(data_dir, "bank-dan", fin_account_id="cash-dan", owner="dan", name="Dan Checking") |
| 288 | make_bank_account(data_dir, "bank-pat", fin_account_id="cash-pat", owner="pat", name="Pat Checking") |
| 289 | make_line(data_dir, "Ldan", bank_account_id="bank-dan", amount=1000, desc="DAN LINE", owner="dan") |
| 290 | make_line(data_dir, "Lpat", bank_account_id="bank-pat", amount=2000, desc="PAT LINE", owner="pat") |
| 291 | |
| 292 | dan_body = page("GET", {"_identity": DAN})["body"] |
| 293 | assert "Dan Checking" in dan_body |
| 294 | assert "Pat Checking" not in dan_body |
| 295 | |
| 296 | # Pat tries to view Dan's account by guessing its id in the query |
| 297 | # string -- the page must ignore it rather than looking it up. |
| 298 | pat_body_snooping = page("GET", {"_identity": PAT, "account": "bank-dan"})["body"] |
| 299 | assert "Dan Checking" not in pat_body_snooping |
| 300 | assert "DAN LINE" not in pat_body_snooping |
| 301 | assert "not found among yours" in pat_body_snooping |
| 302 | |
| 303 | # And when Pat legitimately opens their own account, only their own |
| 304 | # lines show up. |
| 305 | pat_body_own = page("GET", {"_identity": PAT, "account": "bank-pat"})["body"] |
| 306 | assert "Pat Checking" in pat_body_own |
| 307 | assert "PAT LINE" in pat_body_own |
| 308 | assert "DAN LINE" not in pat_body_own |
| 309 | |
| 310 | |
| 311 | def test_page_renders_the_statement_for_a_selected_account(tmp_path, monkeypatch): |
| 312 | data_dir = setup_env(tmp_path, monkeypatch) |
| 313 | make_fin_account(data_dir, "cash") |
| 314 | make_bank_account(data_dir, "bank-1", fin_account_id="cash", name="Business Checking") |
| 315 | make_import(data_dir, "imp1", bank_account_id="bank-1", period_end="2026-07-31", |
| 316 | closing_cents=100000, flags={"tie_out": {"ran": True, "passed": True}}) |
| 317 | make_journal(data_dir, "j1") |
| 318 | make_journal_line(data_dir, "jl1", journal_id="j1", account_id="cash", debit=90000) |
| 319 | make_line(data_dir, "L1", bank_account_id="bank-1", amount=10000, desc="DEPOSIT IN TRANSIT", |
| 320 | status="resolved", resolved_as="timing") |
| 321 | make_line(data_dir, "L2", bank_account_id="bank-1", amount=500, desc="UNKNOWN CREDIT", |
| 322 | status="suggested", suggestions=json.dumps( |
| 323 | [{"tier": 2, "refs": ["payments/p1"], "why": "exact amount within 3 day(s)"}])) |
| 324 | |
| 325 | body = page("GET", {"_identity": DAN, "account": "bank-1"})["body"] |
| 326 | assert "Business Checking" in body |
| 327 | assert "USD 1,000.00" in body # bank closing balance |
| 328 | assert "USD 900.00" in body # book balance |
| 329 | assert "Reconciled" in body |
| 330 | assert "DEPOSIT IN TRANSIT" in body |
| 331 | assert "UNKNOWN CREDIT" in body |
| 332 | assert "exact amount within 3 day(s)" in body |