Runs live, versioned Python application objects on one VM, with a suite of apps: projects, notes, tasks, files and more.

0 downloads

dbbasic-object-server/tests/test_bank_import.py
Download zip
Clone
Clone with git
git clone https://gitstable.com/git/askrobots/dbbasic-object-server.git
HTTPS
https://gitstable.com/git/askrobots/dbbasic-object-server.git
SSH
ssh://git@gitstable.com:2222/askrobots/dbbasic-object-server.git

Cloning needs no sign-in. Pushing over HTTPS asks for your username and an access token. SSH needs your key under SSH keys.

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
3The tests ARE the simulated bank: a statement that ties out, a truncated
4one, a gapped sequence, an overlapping re-import, a bank that exports
5withdrawals as positive numbers, and one that gives no balances at all.
6
7What is being pinned is the evidence posture: raw preserved verbatim,
8lines append-only, checks that record whether they RAN as well as whether
9they passed, and flagged imports that still land their lines (hiding a
10truncated statement is the failure this control exists to catch).
11"""
12
13import json
14import pathlib
15
16import pytest
17
18import object_banking
19import object_execution
20import object_records
21import python_object_runtime
22from conftest import stage_collection
23
24REPO_ROOT = pathlib.Path(__file__).resolve().parents[1]
25PACKAGES = REPO_ROOT / "packages"
26BANKING_OBJECTS = PACKAGES / "app-banking" / "objects"
27RUNTIME = python_object_runtime.PythonObjectRuntime()
28
29ACCOUNT = "bank-1"
30PROFILE = "profile-1"
31COLUMN_MAP = {"date": "Date", "amount": "Amount", "description": "Description",
32 "date_format": "%m/%d/%Y"}
33
34
35def 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
64STATEMENT = """Date,Description,Amount
6507/02/2026,ACME CORP PAYMENT INV-1001,1500.00
6607/05/2026,MONTHLY SERVICE FEE,-25.00
6707/09/2026,OFFICE SUPPLY CO,-142.35
68"""
69
70
71def 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
81def 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
88def 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
94def 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
100def 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
122def 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
135def 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
144def 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
153def 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
166def 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
185def 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
198def 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
209def 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
220def 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
233def 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
262def 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
279def 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
296def 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
307def 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