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
import_ofx.py · 7.2 KB · 171 lines
| 1 | """action_import_ofx -- import an OFX/QFX download. |
| 2 | |
| 3 | POST {bank_account_id, content, opening_balance_cents?, profile_id?} |
| 4 | |
| 5 | OFX is the format worth supporting after CSV, because it carries the two |
| 6 | things a CSV usually cannot: a stable per-transaction id (FITID), which |
| 7 | makes deduplication exact instead of heuristic, and a stated closing |
| 8 | balance (LEDGERBAL), which lets the statement check its own arithmetic. |
| 9 | Parsing lives in object_ofx.py; this action is the same landing path |
| 10 | action_import_bank_csv uses, so everything downstream -- gates, dedup, |
| 11 | matching, resolution, reconciliation -- is unchanged. One canonical shape, |
| 12 | thin importers (plan/bank-import-reconciliation-spec.md section 3). |
| 13 | |
| 14 | **Chained openings.** OFX states a closing balance but has no standard |
| 15 | opening-balance tag at all, so a lone file cannot tie out. It can once |
| 16 | there is a prior statement: last month's closing IS this month's opening. |
| 17 | When the caller does not supply one, this action derives the opening from |
| 18 | the account's previous import and records in the import's flags that the |
| 19 | figure was DERIVED rather than stated. That distinction matters -- a |
| 20 | derived opening makes continuity true by construction, so the check that |
| 21 | actually earns its keep is tie-out: do this statement's transactions |
| 22 | explain the movement from the last statement's end to this one's? Claiming |
| 23 | both checks passed when one was tautological would be exactly the |
| 24 | comfortable lie the flags exist to prevent. |
| 25 | """ |
| 26 | |
| 27 | import json |
| 28 | import os |
| 29 | |
| 30 | import object_banking |
| 31 | import object_ids |
| 32 | import object_ofx |
| 33 | import object_records |
| 34 | |
| 35 | ACTOR = "action_import_ofx" |
| 36 | |
| 37 | |
| 38 | def _base_dir(): |
| 39 | return os.environ.get("DBBASIC_DATA_DIR", "data") |
| 40 | |
| 41 | |
| 42 | def POST(request): |
| 43 | identity = request.get("_identity") or {} |
| 44 | user_id = identity.get("user_id") or "" |
| 45 | if not user_id: |
| 46 | return {"status": 403, "error": "Sign in to import a statement."} |
| 47 | |
| 48 | bank_account_id = str(request.get("bank_account_id") or "").strip() |
| 49 | content = request.get("content") |
| 50 | if not bank_account_id or not isinstance(content, str) or not content.strip(): |
| 51 | return {"status": 400, "error": "bank_account_id and content are required."} |
| 52 | |
| 53 | base = _base_dir() |
| 54 | try: |
| 55 | account = object_records.get_collection_record( |
| 56 | "value_accounts", bank_account_id, base_dir=base) |
| 57 | except Exception: |
| 58 | return {"status": 404, "error": f"Bank account not found: {bank_account_id}"} |
| 59 | if account.get("owner_id") and account["owner_id"] != user_id \ |
| 60 | and "admin" not in (identity.get("roles") or []): |
| 61 | return {"status": 403, "error": "That bank account belongs to someone else."} |
| 62 | |
| 63 | digest = object_banking.file_hash(content) |
| 64 | prior = object_banking.find_import_by_file_hash(bank_account_id, digest, base_dir=base) |
| 65 | if prior is not None: |
| 66 | return {"status": 200, "already_imported": True, "import_id": prior.get("id"), |
| 67 | "imported": 0, "duplicates": 0, |
| 68 | "note": "This exact file was already imported; nothing changed."} |
| 69 | |
| 70 | try: |
| 71 | parsed = object_ofx.parse_ofx(content) |
| 72 | except object_ofx.OfxError as exc: |
| 73 | return {"status": 422, "error": f"Could not read the statement: {exc}"} |
| 74 | lines = object_banking.assign_line_hashes(parsed["lines"]) |
| 75 | if not lines: |
| 76 | return {"status": 422, "error": "The statement had no transactions."} |
| 77 | |
| 78 | period_start = parsed["period"].get("start") or "" |
| 79 | period_end = parsed["period"].get("end") or "" |
| 80 | closing = parsed["balances"].get("closing_balance_cents") |
| 81 | |
| 82 | opening = request.get("opening_balance_cents") |
| 83 | opening_derived = False |
| 84 | if not object_banking.is_present(opening): |
| 85 | previous = object_banking.previous_import( |
| 86 | bank_account_id, base_dir=base, before=period_start) |
| 87 | if previous is not None and str(previous.get("closing_balance_cents") or "").strip(): |
| 88 | opening = int(previous["closing_balance_cents"]) |
| 89 | opening_derived = True |
| 90 | |
| 91 | gates = object_banking.run_gates( |
| 92 | lines, bank_account_id=bank_account_id, |
| 93 | opening_balance_cents=opening, closing_balance_cents=closing, |
| 94 | period_start=period_start, base_dir=base) |
| 95 | checks = dict(gates["checks"]) |
| 96 | if opening_derived: |
| 97 | # Say plainly that continuity could not fail here: it compared the |
| 98 | # previous closing balance against itself. |
| 99 | checks["opening_balance"] = { |
| 100 | "ran": True, "derived": True, |
| 101 | "detail": ("OFX states no opening balance; used the previous statement's " |
| 102 | "closing balance, so continuity is true by construction and " |
| 103 | "tie-out is the check that carries weight here"), |
| 104 | } |
| 105 | if isinstance(checks.get("continuity"), dict): |
| 106 | checks["continuity"]["tautological"] = True |
| 107 | gates = {**gates, "checks": checks} |
| 108 | |
| 109 | import_id = object_ids.new_uuid4() |
| 110 | import_row = { |
| 111 | "id": import_id, |
| 112 | "bank_account_id": bank_account_id, |
| 113 | "profile_id": str(request.get("profile_id") or "").strip(), |
| 114 | "source_format": "ofx", |
| 115 | "file_hash": digest, |
| 116 | "period_start": period_start, |
| 117 | "period_end": period_end, |
| 118 | "opening_balance_cents": str(opening) if object_banking.is_present(opening) else "", |
| 119 | "closing_balance_cents": str(closing) if closing is not None else "", |
| 120 | "line_count": str(len(lines)), |
| 121 | "status": gates["status"], |
| 122 | "flags": json.dumps(checks, sort_keys=True), |
| 123 | "owner_id": user_id, |
| 124 | } |
| 125 | if account.get("entity_id"): |
| 126 | import_row["entity_id"] = account["entity_id"] |
| 127 | object_records.create_collection_record( |
| 128 | "bank_statement_imports", import_row, base_dir=base, actor=ACTOR) |
| 129 | |
| 130 | known_ids, known_hashes = object_banking.existing_line_keys(bank_account_id, base_dir=base) |
| 131 | imported = duplicates = 0 |
| 132 | for line in lines: |
| 133 | # FITID first: the bank's own id survives the bank re-wording a memo, |
| 134 | # which a content hash does not. |
| 135 | if (line.get("external_id") and line["external_id"] in known_ids) or \ |
| 136 | line["line_hash"] in known_hashes: |
| 137 | duplicates += 1 |
| 138 | continue |
| 139 | row = { |
| 140 | "id": object_ids.new_uuid4(), |
| 141 | "import_id": import_id, |
| 142 | "bank_account_id": bank_account_id, |
| 143 | "posted_on": line["posted_on"], |
| 144 | "amount_cents": str(line["amount_cents"]), |
| 145 | "description": line["description"], |
| 146 | "external_id": line["external_id"], |
| 147 | "line_hash": line["line_hash"], |
| 148 | "raw": line["raw"], |
| 149 | "match_status": "unmatched", |
| 150 | "owner_id": user_id, |
| 151 | } |
| 152 | if account.get("entity_id"): |
| 153 | row["entity_id"] = account["entity_id"] |
| 154 | object_records.create_collection_record("bank_lines", row, base_dir=base, actor=ACTOR) |
| 155 | if line.get("external_id"): |
| 156 | known_ids.add(line["external_id"]) |
| 157 | known_hashes.add(line["line_hash"]) |
| 158 | imported += 1 |
| 159 | |
| 160 | return { |
| 161 | "status": 200, |
| 162 | "import_id": import_id, |
| 163 | "imported": imported, |
| 164 | "duplicates": duplicates, |
| 165 | "import_status": gates["status"], |
| 166 | "failed_checks": gates["failed"], |
| 167 | "checks": checks, |
| 168 | "opening_derived": opening_derived, |
| 169 | "period": {"start": period_start, "end": period_end}, |
| 170 | "account": parsed.get("account", {}), |
| 171 | } |