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_import.py · 13.6 KB · 316 lines
| 1 | """Bank statement import (plan/bank-import-reconciliation-spec.md, slice 1). |
| 2 | |
| 3 | The tests ARE the simulated bank: a statement that ties out, a truncated |
| 4 | one, a gapped sequence, an overlapping re-import, a bank that exports |
| 5 | withdrawals as positive numbers, and one that gives no balances at all. |
| 6 | |
| 7 | What is being pinned is the evidence posture: raw preserved verbatim, |
| 8 | lines append-only, checks that record whether they RAN as well as whether |
| 9 | they passed, and flagged imports that still land their lines (hiding a |
| 10 | truncated statement is the failure this control exists to catch). |
| 11 | """ |
| 12 | |
| 13 | import json |
| 14 | import pathlib |
| 15 | |
| 16 | import pytest |
| 17 | |
| 18 | import object_banking |
| 19 | import object_execution |
| 20 | import object_records |
| 21 | import python_object_runtime |
| 22 | from conftest import stage_collection |
| 23 | |
| 24 | REPO_ROOT = pathlib.Path(__file__).resolve().parents[1] |
| 25 | PACKAGES = REPO_ROOT / "packages" |
| 26 | BANKING_OBJECTS = PACKAGES / "app-banking" / "objects" |
| 27 | RUNTIME = python_object_runtime.PythonObjectRuntime() |
| 28 | |
| 29 | ACCOUNT = "bank-1" |
| 30 | PROFILE = "profile-1" |
| 31 | COLUMN_MAP = {"date": "Date", "amount": "Amount", "description": "Description", |
| 32 | "date_format": "%m/%d/%Y"} |
| 33 | |
| 34 | |
| 35 | def setup_env(tmp_path, monkeypatch, *, column_map=None, has_balances=True): |
| 36 | data_dir = tmp_path / "data" |
| 37 | for pkg, name in (("app-banking", "value_accounts"), |
| 38 | ("app-banking", "bank_import_profiles"), |
| 39 | ("app-banking", "bank_statement_imports"), |
| 40 | ("app-banking", "bank_lines"), |
| 41 | ("app-finance", "fin_accounts")): |
| 42 | stage_collection(data_dir, pkg, name) |
| 43 | monkeypatch.setenv("DBBASIC_DATA_DIR", str(data_dir)) |
| 44 | |
| 45 | object_records.create_collection_record( |
| 46 | "fin_accounts", {"id": "acct-cash", "name": "Cash", "account_type": "asset", |
| 47 | "owner_id": "dan"}, base_dir=data_dir) |
| 48 | object_records.create_collection_record( |
| 49 | "value_accounts", {"id": ACCOUNT, "name": "Business Checking", |
| 50 | "custodian": "Test Bank", "reference": "1004", "kind": "bank", |
| 51 | "fin_account_id": "acct-cash", "owner_id": "dan"}, |
| 52 | base_dir=data_dir) |
| 53 | object_records.create_collection_record( |
| 54 | "bank_import_profiles", |
| 55 | {"id": PROFILE, "name": "Test Bank CSV", "bank_account_id": ACCOUNT, |
| 56 | "source_format": "csv", |
| 57 | "column_map": json.dumps(column_map if column_map is not None else COLUMN_MAP), |
| 58 | "has_balances": "true" if has_balances else "false", |
| 59 | "owner_id": "dan"}, |
| 60 | base_dir=data_dir) |
| 61 | return data_dir |
| 62 | |
| 63 | |
| 64 | STATEMENT = """Date,Description,Amount |
| 65 | 07/02/2026,ACME CORP PAYMENT INV-1001,1500.00 |
| 66 | 07/05/2026,MONTHLY SERVICE FEE,-25.00 |
| 67 | 07/09/2026,OFFICE SUPPLY CO,-142.35 |
| 68 | """ |
| 69 | |
| 70 | |
| 71 | def run_import(payload): |
| 72 | result = object_execution.execute_object( |
| 73 | RUNTIME, |
| 74 | object_execution.ObjectExecutionRequest( |
| 75 | "action_import_bank_csv", method="POST", |
| 76 | payload={"_identity": {"user_id": "dan"}, **payload}), |
| 77 | roots=[BANKING_OBJECTS]) |
| 78 | return result.result |
| 79 | |
| 80 | |
| 81 | def import_statement(content=STATEMENT, *, opening=0, closing=133265, **extra): |
| 82 | payload = {"bank_account_id": ACCOUNT, "profile_id": PROFILE, "content": content, |
| 83 | "opening_balance_cents": opening, "closing_balance_cents": closing} |
| 84 | payload.update(extra) |
| 85 | return run_import(payload) |
| 86 | |
| 87 | |
| 88 | def lines(data_dir): |
| 89 | rows = object_records.read_collection_records("bank_lines", base_dir=data_dir) |
| 90 | rows.sort(key=lambda r: r["posted_on"]) |
| 91 | return rows |
| 92 | |
| 93 | |
| 94 | def imports(data_dir): |
| 95 | return object_records.read_collection_records("bank_statement_imports", base_dir=data_dir) |
| 96 | |
| 97 | |
| 98 | # --- 1. parsing and provenance ------------------------------------------------ |
| 99 | |
| 100 | def test_import_lands_lines_with_raw_preserved_and_import_stamped(tmp_path, monkeypatch): |
| 101 | data_dir = setup_env(tmp_path, monkeypatch) |
| 102 | result = import_statement() |
| 103 | assert result["status"] == 200 |
| 104 | assert result["imported"] == 3 and result["duplicates"] == 0 |
| 105 | assert result["import_status"] == "accepted" |
| 106 | |
| 107 | rows = lines(data_dir) |
| 108 | assert [r["posted_on"] for r in rows] == ["2026-07-02", "2026-07-05", "2026-07-09"] |
| 109 | assert [int(r["amount_cents"]) for r in rows] == [150000, -2500, -14235] |
| 110 | assert rows[0]["description"] == "ACME CORP PAYMENT INV-1001" |
| 111 | # raw keeps the original row verbatim -- the evidence, never rewritten |
| 112 | assert "1500.00" in rows[0]["raw"] and "ACME CORP" in rows[0]["raw"] |
| 113 | assert all(r["match_status"] == "unmatched" for r in rows) |
| 114 | assert all(r["import_id"] == result["import_id"] for r in rows) |
| 115 | |
| 116 | imp = imports(data_dir)[0] |
| 117 | assert imp["line_count"] == "3" |
| 118 | assert imp["period_start"] == "2026-07-02" and imp["period_end"] == "2026-07-09" |
| 119 | assert imp["file_hash"] |
| 120 | |
| 121 | |
| 122 | def test_two_column_debit_credit_and_flipped_sign_banks(tmp_path, monkeypatch): |
| 123 | data_dir = setup_env(tmp_path, monkeypatch, column_map={ |
| 124 | "date": "Posted", "debit": "Withdrawal", "credit": "Deposit", |
| 125 | "description": "Memo", "date_format": "%Y-%m-%d"}) |
| 126 | content = ("Posted,Memo,Withdrawal,Deposit\n" |
| 127 | "2026-07-02,CUSTOMER DEPOSIT,,\"1,500.00\"\n" |
| 128 | "2026-07-05,BANK FEE,25.00,\n") |
| 129 | result = import_statement(content, opening=0, closing=147500) |
| 130 | assert result["imported"] == 2 |
| 131 | assert [int(r["amount_cents"]) for r in lines(data_dir)] == [150000, -2500] |
| 132 | assert result["import_status"] == "accepted" |
| 133 | |
| 134 | |
| 135 | def test_parenthesised_negatives_and_european_decimals(tmp_path, monkeypatch): |
| 136 | assert object_banking.parse_cents("(45.00)") == -4500 |
| 137 | assert object_banking.parse_cents("$1,234.56") == 123456 |
| 138 | assert object_banking.parse_cents("1.234,56", decimal_sep=",") == 123456 |
| 139 | assert object_banking.parse_cents("") == 0 |
| 140 | |
| 141 | |
| 142 | # --- 2. the statement checks itself ------------------------------------------- |
| 143 | |
| 144 | def test_tie_out_passes_on_a_good_file(tmp_path, monkeypatch): |
| 145 | setup_env(tmp_path, monkeypatch) |
| 146 | result = import_statement(opening=0, closing=133265) |
| 147 | assert result["import_status"] == "accepted" |
| 148 | assert result["checks"]["tie_out"] == { |
| 149 | "ran": True, "passed": True, "expected_closing_cents": 133265, |
| 150 | "stated_closing_cents": 133265, "delta_cents": 0} |
| 151 | |
| 152 | |
| 153 | def test_truncated_file_is_flagged_with_the_delta_but_lines_still_land(tmp_path, monkeypatch): |
| 154 | data_dir = setup_env(tmp_path, monkeypatch) |
| 155 | truncated = "\n".join(STATEMENT.splitlines()[:3]) + "\n" # last line missing |
| 156 | result = import_statement(truncated, opening=0, closing=133265) |
| 157 | assert result["import_status"] == "flagged" |
| 158 | assert "tie_out" in result["failed_checks"] |
| 159 | assert result["checks"]["tie_out"]["delta_cents"] == -14235 # the missing row |
| 160 | # Evidence still lands -- hiding a truncated statement is the failure |
| 161 | # this control exists to catch. |
| 162 | assert result["imported"] == 2 |
| 163 | assert imports(data_dir)[0]["status"] == "flagged" |
| 164 | |
| 165 | |
| 166 | def test_continuity_chains_statements_and_flags_a_gap(tmp_path, monkeypatch): |
| 167 | data_dir = setup_env(tmp_path, monkeypatch) |
| 168 | first = import_statement(opening=0, closing=133265) |
| 169 | assert first["checks"]["continuity"]["ran"] is False # nothing to chain to yet |
| 170 | |
| 171 | august = ("Date,Description,Amount\n" |
| 172 | "08/03/2026,CUSTOMER PAYMENT,500.00\n") |
| 173 | good = import_statement(august, opening=133265, closing=183265) |
| 174 | assert good["import_status"] == "accepted" |
| 175 | assert good["checks"]["continuity"]["passed"] is True |
| 176 | |
| 177 | september = ("Date,Description,Amount\n" |
| 178 | "09/03/2026,CUSTOMER PAYMENT,100.00\n") |
| 179 | gapped = import_statement(september, opening=200000, closing=210000) |
| 180 | assert gapped["import_status"] == "flagged" |
| 181 | assert "continuity" in gapped["failed_checks"] |
| 182 | assert gapped["checks"]["continuity"]["gap_cents"] == 200000 - 183265 |
| 183 | |
| 184 | |
| 185 | def test_balance_less_csv_records_that_no_check_ran(tmp_path, monkeypatch): |
| 186 | setup_env(tmp_path, monkeypatch, has_balances=False) |
| 187 | result = run_import({"bank_account_id": ACCOUNT, "profile_id": PROFILE, |
| 188 | "content": STATEMENT}) |
| 189 | # Honest weaker assurance: accepted, but the flags say nothing was verified. |
| 190 | assert result["import_status"] == "accepted" |
| 191 | assert result["checks"]["tie_out"]["ran"] is False |
| 192 | assert result["checks"]["continuity"]["ran"] is False |
| 193 | assert result["failed_checks"] == [] |
| 194 | |
| 195 | |
| 196 | # --- 3. dedup ----------------------------------------------------------------- |
| 197 | |
| 198 | def test_reimporting_the_same_file_is_a_no_op(tmp_path, monkeypatch): |
| 199 | data_dir = setup_env(tmp_path, monkeypatch) |
| 200 | first = import_statement() |
| 201 | again = import_statement() |
| 202 | assert again["already_imported"] is True |
| 203 | assert again["import_id"] == first["import_id"] |
| 204 | assert again["imported"] == 0 |
| 205 | assert len(lines(data_dir)) == 3 |
| 206 | assert len(imports(data_dir)) == 1 |
| 207 | |
| 208 | |
| 209 | def test_overlapping_statement_skips_the_lines_already_on_file(tmp_path, monkeypatch): |
| 210 | data_dir = setup_env(tmp_path, monkeypatch) |
| 211 | import_statement() |
| 212 | overlapping = ("Date,Description,Amount\n" |
| 213 | "07/09/2026,OFFICE SUPPLY CO,-142.35\n" # already imported |
| 214 | "07/12/2026,NEW CHARGE,-50.00\n") # new |
| 215 | result = import_statement(overlapping, opening=133265, closing=128265) |
| 216 | assert result["imported"] == 1 and result["duplicates"] == 1 |
| 217 | assert len(lines(data_dir)) == 4 |
| 218 | |
| 219 | |
| 220 | def test_same_day_twins_both_land_but_a_replay_does_not(tmp_path, monkeypatch): |
| 221 | data_dir = setup_env(tmp_path, monkeypatch) |
| 222 | twins = ("Date,Description,Amount\n" |
| 223 | "07/02/2026,COFFEE,-4.50\n" |
| 224 | "07/02/2026,COFFEE,-4.50\n") |
| 225 | first = import_statement(twins, opening=0, closing=-900) |
| 226 | assert first["imported"] == 2 # two real transactions |
| 227 | replay = import_statement(twins + "07/03/2026,COFFEE,-4.50\n", opening=0, closing=-1350) |
| 228 | assert replay["imported"] == 1 # only the new day |
| 229 | assert replay["duplicates"] == 2 |
| 230 | assert len(lines(data_dir)) == 3 |
| 231 | |
| 232 | |
| 233 | def test_hook_rejects_a_duplicate_line_on_the_http_path(tmp_path, monkeypatch): |
| 234 | data_dir = setup_env(tmp_path, monkeypatch) |
| 235 | import_statement() |
| 236 | existing = lines(data_dir)[0] |
| 237 | outcome = object_execution.execute_object( |
| 238 | RUNTIME, |
| 239 | object_execution.ObjectExecutionRequest( |
| 240 | "hook_bank_lines", method="BEFORE_WRITE", |
| 241 | payload={"action": "create", "collection": "bank_lines", |
| 242 | "record": {"bank_account_id": ACCOUNT, |
| 243 | "posted_on": existing["posted_on"], |
| 244 | "amount_cents": existing["amount_cents"], |
| 245 | "description": existing["description"]}}), |
| 246 | roots=[BANKING_OBJECTS]).result |
| 247 | assert outcome["status"] == 409 |
| 248 | assert "already on file" in outcome["error"] |
| 249 | |
| 250 | fresh = object_execution.execute_object( |
| 251 | RUNTIME, |
| 252 | object_execution.ObjectExecutionRequest( |
| 253 | "hook_bank_lines", method="BEFORE_WRITE", |
| 254 | payload={"action": "create", "collection": "bank_lines", |
| 255 | "record": {"bank_account_id": ACCOUNT, "posted_on": "2026-07-20", |
| 256 | "amount_cents": "-999", "description": "SOMETHING NEW"}}), |
| 257 | roots=[BANKING_OBJECTS]).result |
| 258 | # A new line is allowed through, with its dedup hash stamped for next time. |
| 259 | assert fresh["record"]["line_hash"] |
| 260 | |
| 261 | |
| 262 | def test_external_ids_dedup_when_the_bank_supplies_them(tmp_path, monkeypatch): |
| 263 | data_dir = setup_env(tmp_path, monkeypatch, column_map={ |
| 264 | "date": "Date", "amount": "Amount", "description": "Description", |
| 265 | "external_id": "FITID", "date_format": "%m/%d/%Y"}) |
| 266 | first = ("Date,Description,Amount,FITID\n" |
| 267 | "07/02/2026,PAYMENT,100.00,TXN-A\n") |
| 268 | # Same transaction id, description re-worded by the bank on a later export. |
| 269 | second = ("Date,Description,Amount,FITID\n" |
| 270 | "07/02/2026,PAYMENT (POSTED),100.00,TXN-A\n") |
| 271 | assert import_statement(first, opening=0, closing=10000)["imported"] == 1 |
| 272 | result = import_statement(second, opening=0, closing=10000) |
| 273 | assert result["imported"] == 0 and result["duplicates"] == 1 |
| 274 | assert len(lines(data_dir)) == 1 |
| 275 | |
| 276 | |
| 277 | # --- 4. refusals -------------------------------------------------------------- |
| 278 | |
| 279 | def test_import_requires_identity_and_ownership(tmp_path, monkeypatch): |
| 280 | data_dir = setup_env(tmp_path, monkeypatch) |
| 281 | anon = object_execution.execute_object( |
| 282 | RUNTIME, |
| 283 | object_execution.ObjectExecutionRequest( |
| 284 | "action_import_bank_csv", method="POST", |
| 285 | payload={"bank_account_id": ACCOUNT, "content": STATEMENT}), |
| 286 | roots=[BANKING_OBJECTS]).result |
| 287 | assert anon["status"] == 403 |
| 288 | object_records.create_collection_record( |
| 289 | "value_accounts", {"id": "bank-2", "name": "Someone Else", |
| 290 | "owner_id": "pat"}, base_dir=data_dir) |
| 291 | other = run_import({"bank_account_id": "bank-2", "profile_id": PROFILE, |
| 292 | "content": STATEMENT}) |
| 293 | assert other["status"] == 403 |
| 294 | |
| 295 | |
| 296 | def test_unreadable_statements_are_refused_not_half_read(tmp_path, monkeypatch): |
| 297 | data_dir = setup_env(tmp_path, monkeypatch) |
| 298 | bad_date = ("Date,Description,Amount\n" |
| 299 | "not-a-date,PAYMENT,100.00\n") |
| 300 | result = import_statement(bad_date) |
| 301 | assert result["status"] == 422 |
| 302 | assert lines(data_dir) == [] and imports(data_dir) == [] |
| 303 | empty = import_statement("Date,Description,Amount\n") |
| 304 | assert empty["status"] == 422 |
| 305 | |
| 306 | |
| 307 | def test_missing_profile_and_bad_column_map_are_reported(tmp_path, monkeypatch): |
| 308 | data_dir = setup_env(tmp_path, monkeypatch) |
| 309 | missing = run_import({"bank_account_id": ACCOUNT, "profile_id": "nope", |
| 310 | "content": STATEMENT}) |
| 311 | assert missing["status"] == 404 |
| 312 | object_records.update_collection_record( |
| 313 | "bank_import_profiles", PROFILE, {"column_map": "{not json"}, |
| 314 | base_dir=data_dir) |
| 315 | broken = import_statement() |
| 316 | assert broken["status"] == 409 |