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
object_banking.py · 27.4 KB · 650 lines
| 1 | """Bank-statement import primitives: parsing, hashing, and the self-checks. |
| 2 | |
| 3 | Pure helpers over packages/app-banking's collections, same posture as |
| 4 | object_stock.py (derived stock levels) and object_finance.py (journal |
| 5 | folds): a plain importable library, not a registered object. |
| 6 | |
| 7 | The design point (plan/bank-import-reconciliation-spec.md): **import is not |
| 8 | ETL, it is evidence handling.** A bank statement is the only record in the |
| 9 | system authored by an independent third party -- that independence IS the |
| 10 | anti-fraud control. So imported lines are append-only, keep their original |
| 11 | text verbatim in `raw`, and are never hand-edited; parsed fields are a VIEW |
| 12 | of raw, never a replacement. |
| 13 | |
| 14 | "Are the imports good?" has a real answer because a statement carries its |
| 15 | own checksum. Three gates live here: |
| 16 | |
| 17 | - **tie_out**: opening + sum(lines) == closing. A truncated or tampered |
| 18 | file fails arithmetic. |
| 19 | - **continuity**: this statement's opening equals the account's previous |
| 20 | closing. Gaps and edited files surface as a mismatch. |
| 21 | - **dedup**: external_id (OFX FITID) when the format has one, else |
| 22 | line_hash over (date, amount, description, ordinal-within-day) so genuine |
| 23 | same-day twins still both land. |
| 24 | |
| 25 | CSV exports often lack balances and always lack transaction ids, so a |
| 26 | profile records what the format actually supports and an import records |
| 27 | which checks actually RAN. Honest weaker assurance beats pretended strong |
| 28 | assurance -- a reconciliation that silently skipped its checks is worse |
| 29 | than none. |
| 30 | """ |
| 31 | |
| 32 | from __future__ import annotations |
| 33 | |
| 34 | import csv |
| 35 | import hashlib |
| 36 | import io |
| 37 | import json |
| 38 | from datetime import datetime |
| 39 | from decimal import Decimal, InvalidOperation |
| 40 | from pathlib import Path |
| 41 | from typing import Any, Iterable |
| 42 | |
| 43 | import object_finance |
| 44 | import object_records |
| 45 | from object_versions import DEFAULT_DATA_DIR |
| 46 | |
| 47 | IMPORTS_COLLECTION = "bank_statement_imports" |
| 48 | LINES_COLLECTION = "bank_lines" |
| 49 | |
| 50 | STATUS_ACCEPTED = "accepted" |
| 51 | STATUS_FLAGGED = "flagged" |
| 52 | |
| 53 | |
| 54 | class ImportError_(Exception): |
| 55 | """Unusable import input (bad profile, unparseable content).""" |
| 56 | |
| 57 | |
| 58 | def is_present(value: Any) -> bool: |
| 59 | """True when a numeric field was actually supplied. |
| 60 | |
| 61 | Deliberately not `bool(value)`: 0 is a real balance (a new account |
| 62 | opens at zero) and must not read as "not provided" -- that distinction |
| 63 | decides whether a self-check runs at all. |
| 64 | """ |
| 65 | return value is not None and str(value).strip() != "" |
| 66 | |
| 67 | |
| 68 | def file_hash(content: str) -> str: |
| 69 | """Stable hash of the statement file itself -- re-importing the same |
| 70 | file is a no-op, the same provenance posture generated_from gives |
| 71 | composed journals.""" |
| 72 | return hashlib.sha256(content.encode("utf-8", "replace")).hexdigest() |
| 73 | |
| 74 | |
| 75 | def line_hash(posted_on: str, amount_cents: int, description: str, ordinal: int = 0) -> str: |
| 76 | """Identity of a statement line when the bank gives us no id. |
| 77 | |
| 78 | The ordinal distinguishes genuine same-day twins (two identical $4.50 |
| 79 | coffees) from a re-imported duplicate: within one statement the second |
| 80 | twin gets ordinal 1, so both land; across statements the same twin |
| 81 | hashes the same way and is rejected. |
| 82 | """ |
| 83 | parts = f"{posted_on}|{int(amount_cents)}|{' '.join(str(description or '').split())}|{ordinal}" |
| 84 | return hashlib.sha256(parts.encode("utf-8", "replace")).hexdigest()[:32] |
| 85 | |
| 86 | |
| 87 | def parse_cents(value: Any, *, decimal_sep: str = ".") -> int: |
| 88 | """Parse a money string from a bank export into integer cents. |
| 89 | |
| 90 | Handles thousands separators, currency symbols, parenthesised negatives |
| 91 | ("(45.00)" = -4500, the accounting convention many exports use), and |
| 92 | European "1.234,56" when the profile says the decimal separator is a |
| 93 | comma. Decimal throughout -- never a float (00-doctrine-and-contract). |
| 94 | """ |
| 95 | text = str(value or "").strip() |
| 96 | if not text: |
| 97 | return 0 |
| 98 | negative = text.startswith("(") and text.endswith(")") |
| 99 | if negative: |
| 100 | text = text[1:-1].strip() |
| 101 | cleaned = [] |
| 102 | for ch in text: |
| 103 | if ch.isdigit() or ch == "-": |
| 104 | cleaned.append(ch) |
| 105 | elif ch == decimal_sep: |
| 106 | cleaned.append(".") |
| 107 | # every other character (currency symbol, thousands separator, |
| 108 | # stray space) is noise between the digits |
| 109 | text = "".join(cleaned) |
| 110 | if not text or text in ("-", "."): |
| 111 | return 0 |
| 112 | try: |
| 113 | amount = Decimal(text) |
| 114 | except InvalidOperation as exc: |
| 115 | raise ImportError_(f"Unparseable amount: {value!r}") from exc |
| 116 | if negative: |
| 117 | amount = -amount |
| 118 | return int((amount * 100).to_integral_value()) |
| 119 | |
| 120 | |
| 121 | def parse_date(value: Any, date_format: str = "") -> str: |
| 122 | """Normalise a statement date to ISO (YYYY-MM-DD). |
| 123 | |
| 124 | A profile may pin the bank's format explicitly (%m/%d/%Y); without one |
| 125 | we try the common exports rather than guessing per row. |
| 126 | """ |
| 127 | text = str(value or "").strip() |
| 128 | if not text: |
| 129 | return "" |
| 130 | formats = [date_format] if date_format else [] |
| 131 | formats += ["%Y-%m-%d", "%m/%d/%Y", "%d/%m/%Y", "%m/%d/%y", "%d.%m.%Y", |
| 132 | "%Y/%m/%d", "%d-%b-%Y", "%b %d, %Y"] |
| 133 | for fmt in formats: |
| 134 | if not fmt: |
| 135 | continue |
| 136 | try: |
| 137 | return datetime.strptime(text[:len(text)], fmt).date().isoformat() |
| 138 | except ValueError: |
| 139 | continue |
| 140 | raise ImportError_(f"Unparseable date: {value!r}") |
| 141 | |
| 142 | |
| 143 | def _column(row: dict, header: list[str], key: Any) -> Any: |
| 144 | """Resolve a column_map entry: a header name or a 0-based index.""" |
| 145 | if key is None or key == "": |
| 146 | return "" |
| 147 | if isinstance(key, int) or (isinstance(key, str) and key.isdigit()): |
| 148 | index = int(key) |
| 149 | return row.get(header[index], "") if 0 <= index < len(header) else "" |
| 150 | return row.get(key, "") |
| 151 | |
| 152 | |
| 153 | def parse_statement_csv(content: str, column_map: dict) -> list[dict]: |
| 154 | """Parse a CSV export into canonical line dicts using a saved profile. |
| 155 | |
| 156 | column_map keys: date, description, amount (one signed column) OR |
| 157 | debit/credit (a pair), external_id (optional), plus date_format, |
| 158 | decimal_sep, and sign ("normal" | "flip" for banks that export |
| 159 | withdrawals as positive numbers). |
| 160 | |
| 161 | Returns [{posted_on, amount_cents, description, external_id, raw}] in |
| 162 | file order. Blank rows are skipped; a row whose date or amount cannot |
| 163 | be parsed raises -- a half-read statement is worse than a refused one. |
| 164 | """ |
| 165 | if not isinstance(column_map, dict) or not column_map: |
| 166 | raise ImportError_("Profile has no column_map") |
| 167 | text = content.lstrip("") |
| 168 | reader = csv.DictReader(io.StringIO(text)) |
| 169 | header = list(reader.fieldnames or []) |
| 170 | if not header: |
| 171 | raise ImportError_("CSV has no header row") |
| 172 | |
| 173 | date_format = str(column_map.get("date_format") or "") |
| 174 | decimal_sep = str(column_map.get("decimal_sep") or ".") |
| 175 | flip = str(column_map.get("sign") or "normal").lower() == "flip" |
| 176 | has_pair = bool(column_map.get("debit") or column_map.get("credit")) |
| 177 | |
| 178 | lines = [] |
| 179 | for row in reader: |
| 180 | if not any(str(v or "").strip() for v in row.values()): |
| 181 | continue |
| 182 | raw = "\t".join(str(row.get(col, "") or "") for col in header) |
| 183 | if has_pair: |
| 184 | debit = parse_cents(_column(row, header, column_map.get("debit")), decimal_sep=decimal_sep) |
| 185 | credit = parse_cents(_column(row, header, column_map.get("credit")), decimal_sep=decimal_sep) |
| 186 | # Money out of the account is negative in the canonical shape. |
| 187 | amount_cents = credit - abs(debit) if credit else -abs(debit) |
| 188 | else: |
| 189 | amount_cents = parse_cents( |
| 190 | _column(row, header, column_map.get("amount")), decimal_sep=decimal_sep) |
| 191 | if flip: |
| 192 | amount_cents = -amount_cents |
| 193 | lines.append({ |
| 194 | "posted_on": parse_date(_column(row, header, column_map.get("date")), date_format), |
| 195 | "amount_cents": amount_cents, |
| 196 | "description": " ".join(str(_column(row, header, column_map.get("description")) or "").split()), |
| 197 | "external_id": str(_column(row, header, column_map.get("external_id")) or "").strip(), |
| 198 | "raw": raw, |
| 199 | }) |
| 200 | return lines |
| 201 | |
| 202 | |
| 203 | def assign_line_hashes(lines: Iterable[dict]) -> list[dict]: |
| 204 | """Stamp line_hash on parsed lines, ordinal-disambiguating same-day |
| 205 | twins so two identical transactions on one statement both survive.""" |
| 206 | seen: dict[tuple, int] = {} |
| 207 | out = [] |
| 208 | for line in lines: |
| 209 | key = (line.get("posted_on"), int(line.get("amount_cents") or 0), |
| 210 | line.get("description")) |
| 211 | ordinal = seen.get(key, 0) |
| 212 | seen[key] = ordinal + 1 |
| 213 | stamped = dict(line) |
| 214 | stamped["line_hash"] = line_hash( |
| 215 | line.get("posted_on", ""), int(line.get("amount_cents") or 0), |
| 216 | line.get("description", ""), ordinal) |
| 217 | out.append(stamped) |
| 218 | return out |
| 219 | |
| 220 | |
| 221 | def existing_line_keys(bank_account_id: str, *, base_dir: Path | str = DEFAULT_DATA_DIR) -> tuple[set, set]: |
| 222 | """(external_ids, line_hashes) already stored for one bank account.""" |
| 223 | external_ids, hashes = set(), set() |
| 224 | try: |
| 225 | rows = object_records.read_collection_records(LINES_COLLECTION, base_dir=base_dir) |
| 226 | except Exception: |
| 227 | return external_ids, hashes |
| 228 | for row in rows: |
| 229 | if row.get("bank_account_id") != bank_account_id: |
| 230 | continue |
| 231 | if row.get("external_id"): |
| 232 | external_ids.add(row["external_id"]) |
| 233 | if row.get("line_hash"): |
| 234 | hashes.add(row["line_hash"]) |
| 235 | return external_ids, hashes |
| 236 | |
| 237 | |
| 238 | def is_duplicate_line(record: dict, *, base_dir: Path | str = DEFAULT_DATA_DIR) -> bool: |
| 239 | """True when this line is already on file for its bank account.""" |
| 240 | external_ids, hashes = existing_line_keys( |
| 241 | str(record.get("bank_account_id") or ""), base_dir=base_dir) |
| 242 | if record.get("external_id") and record["external_id"] in external_ids: |
| 243 | return True |
| 244 | return bool(record.get("line_hash")) and record["line_hash"] in hashes |
| 245 | |
| 246 | |
| 247 | def find_import_by_file_hash(bank_account_id: str, digest: str, *, |
| 248 | base_dir: Path | str = DEFAULT_DATA_DIR) -> dict | None: |
| 249 | """The prior import of this exact file, if any (file-level idempotency).""" |
| 250 | try: |
| 251 | rows = object_records.read_collection_records(IMPORTS_COLLECTION, base_dir=base_dir) |
| 252 | except Exception: |
| 253 | return None |
| 254 | for row in rows: |
| 255 | if row.get("bank_account_id") == bank_account_id and row.get("file_hash") == digest: |
| 256 | return row |
| 257 | return None |
| 258 | |
| 259 | |
| 260 | def previous_import(bank_account_id: str, *, base_dir: Path | str = DEFAULT_DATA_DIR, |
| 261 | before: str = "") -> dict | None: |
| 262 | """The account's latest accepted-or-flagged import before `before` |
| 263 | (by period_end) -- the continuity check's comparison point.""" |
| 264 | try: |
| 265 | rows = object_records.read_collection_records(IMPORTS_COLLECTION, base_dir=base_dir) |
| 266 | except Exception: |
| 267 | return None |
| 268 | candidates = [r for r in rows |
| 269 | if r.get("bank_account_id") == bank_account_id |
| 270 | and (r.get("period_end") or "") |
| 271 | and (not before or (r.get("period_end") or "") <= before)] |
| 272 | if not candidates: |
| 273 | return None |
| 274 | return max(candidates, key=lambda r: (r.get("period_end") or "", r.get("created_at") or "")) |
| 275 | |
| 276 | |
| 277 | def run_gates(lines: list[dict], *, bank_account_id: str, |
| 278 | opening_balance_cents: Any, closing_balance_cents: Any, |
| 279 | period_start: str = "", base_dir: Path | str = DEFAULT_DATA_DIR) -> dict: |
| 280 | """Run tie-out and continuity, returning the import's flags block. |
| 281 | |
| 282 | Shape: {"checks": {...ran/passed...}, "status": accepted|flagged}. The |
| 283 | flags record which checks RAN, not just which passed -- a balance-less |
| 284 | CSV that "passed" nothing must not look as trustworthy as a tied-out |
| 285 | OFX file. |
| 286 | """ |
| 287 | checks: dict[str, Any] = {} |
| 288 | total = sum(int(l.get("amount_cents") or 0) for l in lines) |
| 289 | checks["line_total_cents"] = total |
| 290 | |
| 291 | # `or ""` would be wrong here: a zero opening balance is a real balance |
| 292 | # (a new account starts at zero), and treating it as "no balance given" |
| 293 | # would silently skip the tie-out check on exactly the statement whose |
| 294 | # arithmetic is easiest to verify. |
| 295 | has_balances = is_present(opening_balance_cents) and is_present(closing_balance_cents) |
| 296 | if has_balances: |
| 297 | opening = int(opening_balance_cents or 0) |
| 298 | closing = int(closing_balance_cents or 0) |
| 299 | expected = opening + total |
| 300 | checks["tie_out"] = { |
| 301 | "ran": True, |
| 302 | "passed": expected == closing, |
| 303 | "expected_closing_cents": expected, |
| 304 | "stated_closing_cents": closing, |
| 305 | "delta_cents": closing - expected, |
| 306 | } |
| 307 | prior = previous_import(bank_account_id, base_dir=base_dir, before=period_start) |
| 308 | if prior is not None and str(prior.get("closing_balance_cents") or "").strip(): |
| 309 | prior_closing = int(prior["closing_balance_cents"]) |
| 310 | checks["continuity"] = { |
| 311 | "ran": True, |
| 312 | "passed": prior_closing == opening, |
| 313 | "previous_closing_cents": prior_closing, |
| 314 | "this_opening_cents": opening, |
| 315 | "gap_cents": opening - prior_closing, |
| 316 | "previous_import_id": prior.get("id", ""), |
| 317 | } |
| 318 | else: |
| 319 | checks["continuity"] = {"ran": False, |
| 320 | "reason": "no prior statement for this account"} |
| 321 | else: |
| 322 | checks["tie_out"] = {"ran": False, |
| 323 | "reason": "statement carried no opening/closing balance"} |
| 324 | checks["continuity"] = {"ran": False, "reason": "no balances to chain"} |
| 325 | |
| 326 | failed = [name for name, c in checks.items() |
| 327 | if isinstance(c, dict) and c.get("ran") and not c.get("passed")] |
| 328 | return { |
| 329 | "checks": checks, |
| 330 | "failed": failed, |
| 331 | "status": STATUS_FLAGGED if failed else STATUS_ACCEPTED, |
| 332 | } |
| 333 | |
| 334 | |
| 335 | def flags_json(gates: dict) -> str: |
| 336 | return json.dumps(gates.get("checks", {}), sort_keys=True) |
| 337 | |
| 338 | |
| 339 | # --- matching --------------------------------------------------------------- |
| 340 | # |
| 341 | # Matching is DERIVED and non-authoritative: the matcher proposes, a human |
| 342 | # (or an explicitly configured auto-match tier) disposes. That split is the |
| 343 | # control -- reconciliation performed invisibly by whoever moves the money |
| 344 | # is the classic fraud hole, so the act of confirming is an ordinary |
| 345 | # attributed update and shows up in the change log with a name on it. |
| 346 | |
| 347 | # Fields of a bank line that are EVIDENCE: what the bank said, verbatim. |
| 348 | # An update may never touch them -- corrections come from re-importing a |
| 349 | # corrected statement, not from editing the record to agree with the books. |
| 350 | EVIDENCE_FIELDS = ("import_id", "bank_account_id", "posted_on", "amount_cents", |
| 351 | "description", "external_id", "line_hash", "raw") |
| 352 | |
| 353 | |
| 354 | def _day_gap(left: str, right: str) -> int | None: |
| 355 | try: |
| 356 | a = datetime.strptime(str(left)[:10], "%Y-%m-%d").date() |
| 357 | b = datetime.strptime(str(right)[:10], "%Y-%m-%d").date() |
| 358 | except (ValueError, TypeError): |
| 359 | return None |
| 360 | return abs((a - b).days) |
| 361 | |
| 362 | |
| 363 | def _reference_hit(description: str, reference: str) -> bool: |
| 364 | """Does the statement text carry this record's reference? |
| 365 | |
| 366 | Banks mangle memos (case, padding, truncation), so compare |
| 367 | case-insensitively on collapsed alphanumerics. A reference shorter than |
| 368 | four characters is too weak to assert identity on -- "12" appears in |
| 369 | half of all descriptions. |
| 370 | """ |
| 371 | ref = "".join(ch for ch in str(reference or "") if ch.isalnum()).upper() |
| 372 | if len(ref) < 4: |
| 373 | return False |
| 374 | haystack = "".join(ch for ch in str(description or "") if ch.isalnum()).upper() |
| 375 | return ref in haystack |
| 376 | |
| 377 | |
| 378 | def candidate_matches(line: dict, candidates: Iterable[dict], *, |
| 379 | window_days: int = 5, max_combination: int = 3) -> list[dict]: |
| 380 | """Propose book-side records for one bank line, best first. |
| 381 | |
| 382 | `candidates` are dicts of {ref, amount_cents, date, reference} where |
| 383 | amount_cents is already expressed in the LINE's sign convention (money |
| 384 | into the account positive) and `ref` is "collection/id". |
| 385 | |
| 386 | Tiers, strongest to weakest: |
| 387 | 1. the record's reference appears in the statement text AND the |
| 388 | amount agrees -- two independent signals, safe to auto-confirm |
| 389 | 2. exact amount within the date window -- one signal; plausible but a |
| 390 | same-priced invoice from another customer looks identical |
| 391 | 3. a same-window combination of 2..max_combination records summing to |
| 392 | the line -- the batched deposit case; always suggestion-only |
| 393 | """ |
| 394 | amount = int(line.get("amount_cents") or 0) |
| 395 | posted_on = str(line.get("posted_on") or "") |
| 396 | description = str(line.get("description") or "") |
| 397 | |
| 398 | near = [] |
| 399 | for cand in candidates: |
| 400 | gap = _day_gap(posted_on, cand.get("date")) |
| 401 | if gap is None or gap > window_days: |
| 402 | continue |
| 403 | near.append((gap, cand)) |
| 404 | |
| 405 | proposals = [] |
| 406 | for gap, cand in near: |
| 407 | same_amount = int(cand.get("amount_cents") or 0) == amount |
| 408 | if same_amount and _reference_hit(description, cand.get("reference")): |
| 409 | proposals.append({ |
| 410 | "tier": 1, "refs": [cand["ref"]], "day_gap": gap, |
| 411 | "why": f"reference {cand.get('reference')} appears in the statement text " |
| 412 | f"and the amount matches exactly", |
| 413 | }) |
| 414 | elif same_amount: |
| 415 | proposals.append({ |
| 416 | "tier": 2, "refs": [cand["ref"]], "day_gap": gap, |
| 417 | "why": f"exact amount within {gap} day(s)", |
| 418 | }) |
| 419 | |
| 420 | if not any(p["tier"] == 1 for p in proposals) and max_combination >= 2: |
| 421 | proposals.extend(_combination_matches(amount, near, max_combination)) |
| 422 | |
| 423 | proposals.sort(key=lambda p: (p["tier"], p.get("day_gap", 99), len(p["refs"]))) |
| 424 | return proposals |
| 425 | |
| 426 | |
| 427 | def _combination_matches(amount: int, near: list, max_combination: int) -> list[dict]: |
| 428 | """Batched-deposit case: several book records banked as one line. |
| 429 | |
| 430 | Bounded on purpose (few candidates, small k): an unbounded subset search |
| 431 | over a busy account would both hang and produce coincidences that are |
| 432 | worse than no suggestion at all. |
| 433 | """ |
| 434 | from itertools import combinations |
| 435 | |
| 436 | pool = [c for _, c in near][:12] |
| 437 | found = [] |
| 438 | for size in range(2, min(max_combination, len(pool)) + 1): |
| 439 | for combo in combinations(pool, size): |
| 440 | if sum(int(c.get("amount_cents") or 0) for c in combo) != amount: |
| 441 | continue |
| 442 | found.append({ |
| 443 | "tier": 3, |
| 444 | "refs": [c["ref"] for c in combo], |
| 445 | "day_gap": 0, |
| 446 | "why": f"{size} records sum to this line (batched deposit)", |
| 447 | }) |
| 448 | if len(found) >= 3: # a handful of options, not a catalogue |
| 449 | return found |
| 450 | return found |
| 451 | |
| 452 | |
| 453 | def evidence_changes(existing: dict, changes: dict) -> list[str]: |
| 454 | """Which evidence fields an update is trying to alter (should be none).""" |
| 455 | touched = [] |
| 456 | for field in EVIDENCE_FIELDS: |
| 457 | if field not in changes: |
| 458 | continue |
| 459 | if str(changes.get(field) or "") != str(existing.get(field) or ""): |
| 460 | touched.append(field) |
| 461 | return touched |
| 462 | |
| 463 | |
| 464 | # --- the reconciliation statement -------------------------------------------- |
| 465 | # |
| 466 | # plan/bank-import-reconciliation-spec.md section 6: "bank closing balance |
| 467 | # (last accepted import) vs. book cash balance (fin account), reconciled by: |
| 468 | # matched total, outstanding timing items, unresolved tail." Nothing here |
| 469 | # writes anything -- it is a fold over already-stored state, the same |
| 470 | # posture as object_finance.trial_balance(). |
| 471 | |
| 472 | ACCOUNTS_COLLECTION = "value_accounts" |
| 473 | JOURNALS_COLLECTION = "fin_journals" |
| 474 | JOURNAL_LINES_COLLECTION = "fin_journal_lines" |
| 475 | |
| 476 | _STATUS_POSTED = "posted" |
| 477 | |
| 478 | ASSURANCE_VERIFIED = "verified" |
| 479 | ASSURANCE_UNVERIFIED = "unverified" |
| 480 | ASSURANCE_FLAGGED = "flagged" |
| 481 | |
| 482 | |
| 483 | def _safe_read(collection: str, base_dir: Path | str) -> list[dict]: |
| 484 | """read_collection_records, folding a missing/uninstalled collection to |
| 485 | an empty list instead of raising -- this report must degrade to "not |
| 486 | enough data" rather than a 500 when a dependent package (app-finance) |
| 487 | or a not-yet-imported account has nothing on file yet.""" |
| 488 | try: |
| 489 | return object_records.read_collection_records(collection, base_dir=base_dir) |
| 490 | except Exception: |
| 491 | return [] |
| 492 | |
| 493 | |
| 494 | def _latest_by_period_end(rows: list[dict]) -> dict | None: |
| 495 | return max(rows, key=lambda r: (r.get("period_end") or "", r.get("created_at") or ""), |
| 496 | default=None) |
| 497 | |
| 498 | |
| 499 | def _assurance_from_flags(flags_raw: Any) -> str: |
| 500 | """verified/unverified/flagged from one import's flags JSON (the |
| 501 | `checks` block run_gates() produces). |
| 502 | |
| 503 | "flagged" is deliberately keyed on ANY failed check (tie_out or |
| 504 | continuity), not just tie_out, because a broken chain of statements is |
| 505 | exactly as much of an anti-fraud gap as a broken statement -- both mean |
| 506 | the evidence trail cannot be trusted yet. "unverified" is the honest |
| 507 | middle ground for a balance-less CSV: nothing failed because nothing |
| 508 | that could fail was even run. |
| 509 | """ |
| 510 | try: |
| 511 | checks = json.loads(flags_raw or "{}") |
| 512 | except (ValueError, TypeError): |
| 513 | checks = {} |
| 514 | if not isinstance(checks, dict): |
| 515 | checks = {} |
| 516 | failed = any(isinstance(c, dict) and c.get("ran") and not c.get("passed") |
| 517 | for c in checks.values()) |
| 518 | if failed: |
| 519 | return ASSURANCE_FLAGGED |
| 520 | tie_out = checks.get("tie_out") |
| 521 | if isinstance(tie_out, dict) and tie_out.get("ran") and tie_out.get("passed"): |
| 522 | return ASSURANCE_VERIFIED |
| 523 | return ASSURANCE_UNVERIFIED |
| 524 | |
| 525 | |
| 526 | def reconciliation(bank_account_id: str, *, base_dir: Path | str = DEFAULT_DATA_DIR, |
| 527 | as_of: str = "") -> dict: |
| 528 | """The classic bank reconciliation statement for one account. |
| 529 | |
| 530 | Two independent truths are folded and compared, never merged: the |
| 531 | bank's (bank_statement_imports/bank_lines, imported evidence) and the |
| 532 | books' (fin_journals/fin_journal_lines through the account's own |
| 533 | fin_account_id). bank_closing_cents deliberately comes from the latest |
| 534 | ACCEPTED import only -- an import whose own tie-out either passed or |
| 535 | was never claimed to run -- because a statement that failed its own |
| 536 | arithmetic check is not something to build a "you're reconciled" claim |
| 537 | on top of. `assurance`, by contrast, is read off the single most |
| 538 | recent import regardless of its status: a fresh statement that just |
| 539 | failed tie-out is exactly the thing this view exists to surface, even |
| 540 | while the number it displays quietly falls back to the last |
| 541 | trustworthy one. |
| 542 | |
| 543 | The tie: once every bank line is either matched to a book record or |
| 544 | resolved (composing its own journal via action_resolve_bank_line), |
| 545 | bank_closing_cents - book_balance_cents should equal exactly the sum |
| 546 | of lines resolved as "timing" (on the statement, deliberately never |
| 547 | booked -- see object_banking module docstring / the resolution-verbs |
| 548 | tests). Any remaining unmatched or merely-suggested line is slack in |
| 549 | that equation, which is what `reconciled` actually tests: it is not |
| 550 | "do the two numbers match", it is "do they match FOR AN EXPLAINED |
| 551 | REASON". |
| 552 | |
| 553 | as_of, when given (ISO date), scopes the statement to that date: |
| 554 | imports with a period_end after it and posted journals dated after it |
| 555 | are excluded, so a caller can ask "were we reconciled as of last |
| 556 | month-end" without waiting for a fresh import. |
| 557 | |
| 558 | Never raises -- a bank account that does not exist, one with no |
| 559 | fin_account_id set, or collections that are not installed all fold to |
| 560 | a dict of mostly-None fields. This is a report a caller may render for |
| 561 | a not-yet-configured account, not a gate. |
| 562 | """ |
| 563 | accounts = _safe_read(ACCOUNTS_COLLECTION, base_dir) |
| 564 | account = next((a for a in accounts if a.get("id") == bank_account_id), None) |
| 565 | fin_account_id = str((account or {}).get("fin_account_id") or "") |
| 566 | |
| 567 | imports = [r for r in _safe_read(IMPORTS_COLLECTION, base_dir) |
| 568 | if r.get("bank_account_id") == bank_account_id |
| 569 | and (r.get("period_end") or "") |
| 570 | and (not as_of or (r.get("period_end") or "") <= as_of)] |
| 571 | latest_import = _latest_by_period_end(imports) |
| 572 | latest_accepted = _latest_by_period_end( |
| 573 | [r for r in imports if r.get("status") == STATUS_ACCEPTED]) |
| 574 | |
| 575 | bank_closing_cents = None |
| 576 | bank_statement_date = None |
| 577 | if latest_accepted is not None and is_present(latest_accepted.get("closing_balance_cents")): |
| 578 | bank_closing_cents = int(latest_accepted["closing_balance_cents"]) |
| 579 | bank_statement_date = latest_accepted.get("period_end") or "" |
| 580 | |
| 581 | assurance = _assurance_from_flags(latest_import.get("flags")) if latest_import else None |
| 582 | |
| 583 | book_balance_cents = None |
| 584 | if fin_account_id: |
| 585 | posted_ids = { |
| 586 | j.get("id") for j in _safe_read(JOURNALS_COLLECTION, base_dir) |
| 587 | if j.get("status") == _STATUS_POSTED |
| 588 | and (not as_of or (j.get("date") or "") <= as_of) |
| 589 | } |
| 590 | debit_total = credit_total = 0 |
| 591 | for line in _safe_read(JOURNAL_LINES_COLLECTION, base_dir): |
| 592 | if line.get("journal_id") not in posted_ids or line.get("account_id") != fin_account_id: |
| 593 | continue |
| 594 | # object_finance.to_cents, not bare int(): every other ledger fold |
| 595 | # in this codebase parses defensively (Decimal, floor, blank -> 0) |
| 596 | # because a hand-edited TSV row or a half-filled draft can leave a |
| 597 | # *_cents field blank or fractional. A bare int() raises ValueError |
| 598 | # on exactly those rows -- and this is the reconciliation view, the |
| 599 | # one page whose job is to keep working when the data is wrong. |
| 600 | debit_total += object_finance.to_cents(line.get("debit_cents")) |
| 601 | credit_total += object_finance.to_cents(line.get("credit_cents")) |
| 602 | # An asset account: debits increase the balance, credits decrease |
| 603 | # it -- the same convention object_finance.py uses throughout. |
| 604 | book_balance_cents = debit_total - credit_total |
| 605 | |
| 606 | matched_cents = timing_cents = unmatched_cents = 0 |
| 607 | unmatched_count = timing_count = suggested_count = 0 |
| 608 | for row in _safe_read(LINES_COLLECTION, base_dir): |
| 609 | if row.get("bank_account_id") != bank_account_id: |
| 610 | continue |
| 611 | amount = int(row.get("amount_cents") or 0) |
| 612 | status = row.get("match_status") or "unmatched" |
| 613 | if status == "matched": |
| 614 | matched_cents += amount |
| 615 | elif status == "resolved": |
| 616 | if (row.get("resolved_as") or "") == "timing": |
| 617 | timing_cents += amount |
| 618 | timing_count += 1 |
| 619 | else: |
| 620 | matched_cents += amount |
| 621 | else: |
| 622 | # unmatched and suggested are both still an open tail -- a |
| 623 | # suggestion is the matcher's opinion, not a human decision |
| 624 | # (object_banking module docstring), so it has not yet earned |
| 625 | # its way into matched_cents. |
| 626 | unmatched_cents += amount |
| 627 | if status == "suggested": |
| 628 | suggested_count += 1 |
| 629 | else: |
| 630 | unmatched_count += 1 |
| 631 | |
| 632 | difference_cents = None |
| 633 | if bank_closing_cents is not None and book_balance_cents is not None: |
| 634 | difference_cents = bank_closing_cents - book_balance_cents |
| 635 | reconciled = difference_cents is not None and difference_cents == timing_cents |
| 636 | |
| 637 | return { |
| 638 | "bank_closing_cents": bank_closing_cents, |
| 639 | "bank_statement_date": bank_statement_date, |
| 640 | "book_balance_cents": book_balance_cents, |
| 641 | "matched_cents": matched_cents, |
| 642 | "unmatched_cents": unmatched_cents, |
| 643 | "timing_cents": timing_cents, |
| 644 | "unmatched_count": unmatched_count, |
| 645 | "timing_count": timing_count, |
| 646 | "suggested_count": suggested_count, |
| 647 | "difference_cents": difference_cents, |
| 648 | "reconciled": reconciled, |
| 649 | "assurance": assurance, |
| 650 | } |