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_bank_csv.py · 7.3 KB · 169 lines
| 1 | """action_import_bank_csv -- read a bank's CSV export through a saved profile. |
| 2 | |
| 3 | POST {bank_account_id, profile_id, content, opening_balance_cents?, |
| 4 | closing_balance_cents?, period_start?, period_end?} |
| 5 | |
| 6 | The evidence-handling posture (plan/bank-import-reconciliation-spec.md): |
| 7 | |
| 8 | 1. **File-level idempotency** -- the same file re-imported is a no-op, |
| 9 | reported as already_imported with the original import's id. Operators |
| 10 | re-upload by accident constantly; that must be boring, not destructive. |
| 11 | 2. **Deterministic parsing** -- the profile's column_map decides how to |
| 12 | read the file. AI helps AUTHOR a profile once; it never re-reads |
| 13 | statements, because a fraud control cannot be nondeterministic. |
| 14 | 3. **The statement checks itself** -- tie-out (opening + lines == closing) |
| 15 | and continuity (this opening == the previous statement's closing) run |
| 16 | here, and their verdict SETS the import's status. Flagged imports still |
| 17 | store their lines: evidence is evidence, and hiding a truncated |
| 18 | statement is exactly the failure this control exists to catch. |
| 19 | 4. **Line-level dedup** -- lines already on file for this account (by the |
| 20 | bank's transaction id, else a content hash) are skipped and counted, |
| 21 | so overlapping date ranges between statements are safe. This action |
| 22 | writes at the storage level (never through the HTTP hook), so it |
| 23 | performs the same check hook_bank_lines enforces for direct writers. |
| 24 | |
| 25 | Nothing here posts to the books. Imported lines are the OTHER party's |
| 26 | record; journals only ever come from resolving a line deliberately. |
| 27 | """ |
| 28 | |
| 29 | import json |
| 30 | import os |
| 31 | |
| 32 | import object_banking |
| 33 | import object_ids |
| 34 | import object_records |
| 35 | |
| 36 | ACTOR = "action_import_bank_csv" |
| 37 | |
| 38 | |
| 39 | def _base_dir(): |
| 40 | return os.environ.get("DBBASIC_DATA_DIR", "data") |
| 41 | |
| 42 | |
| 43 | def POST(request): |
| 44 | identity = request.get("_identity") or {} |
| 45 | user_id = identity.get("user_id") or "" |
| 46 | if not user_id: |
| 47 | return {"status": 403, "error": "Sign in to import a statement."} |
| 48 | |
| 49 | bank_account_id = str(request.get("bank_account_id") or "").strip() |
| 50 | profile_id = str(request.get("profile_id") or "").strip() |
| 51 | content = request.get("content") |
| 52 | if not bank_account_id or not isinstance(content, str) or not content.strip(): |
| 53 | return {"status": 400, "error": "bank_account_id and content are required."} |
| 54 | |
| 55 | base = _base_dir() |
| 56 | try: |
| 57 | account = object_records.get_collection_record( |
| 58 | "value_accounts", bank_account_id, base_dir=base) |
| 59 | except Exception: |
| 60 | return {"status": 404, "error": f"Bank account not found: {bank_account_id}"} |
| 61 | if account.get("owner_id") and account["owner_id"] != user_id and "admin" not in (identity.get("roles") or []): |
| 62 | return {"status": 403, "error": "That bank account belongs to someone else."} |
| 63 | |
| 64 | column_map = {} |
| 65 | profile = None |
| 66 | if profile_id: |
| 67 | try: |
| 68 | profile = object_records.get_collection_record( |
| 69 | "bank_import_profiles", profile_id, base_dir=base) |
| 70 | except Exception: |
| 71 | return {"status": 404, "error": f"Import profile not found: {profile_id}"} |
| 72 | try: |
| 73 | column_map = json.loads(profile.get("column_map") or "{}") |
| 74 | except (ValueError, TypeError): |
| 75 | return {"status": 409, |
| 76 | "error": f"Profile {profile_id} has an unparseable column_map."} |
| 77 | if not column_map: |
| 78 | column_map = request.get("column_map") or {} |
| 79 | if not isinstance(column_map, dict) or not column_map: |
| 80 | return {"status": 400, |
| 81 | "error": "No column_map: pass profile_id (preferred) or an inline column_map."} |
| 82 | |
| 83 | digest = object_banking.file_hash(content) |
| 84 | prior = object_banking.find_import_by_file_hash(bank_account_id, digest, base_dir=base) |
| 85 | if prior is not None: |
| 86 | return {"status": 200, "already_imported": True, "import_id": prior.get("id"), |
| 87 | "imported": 0, "duplicates": 0, |
| 88 | "note": "This exact file was already imported; nothing changed."} |
| 89 | |
| 90 | try: |
| 91 | parsed = object_banking.parse_statement_csv(content, column_map) |
| 92 | except object_banking.ImportError_ as exc: |
| 93 | return {"status": 422, "error": f"Could not read the statement: {exc}"} |
| 94 | if not parsed: |
| 95 | return {"status": 422, "error": "The statement had no data rows."} |
| 96 | lines = object_banking.assign_line_hashes(parsed) |
| 97 | |
| 98 | dates = sorted(l["posted_on"] for l in lines if l.get("posted_on")) |
| 99 | period_start = str(request.get("period_start") or "").strip() or (dates[0] if dates else "") |
| 100 | period_end = str(request.get("period_end") or "").strip() or (dates[-1] if dates else "") |
| 101 | |
| 102 | gates = object_banking.run_gates( |
| 103 | lines, bank_account_id=bank_account_id, |
| 104 | opening_balance_cents=request.get("opening_balance_cents"), |
| 105 | closing_balance_cents=request.get("closing_balance_cents"), |
| 106 | period_start=period_start, base_dir=base) |
| 107 | |
| 108 | import_id = object_ids.new_uuid4() |
| 109 | import_row = { |
| 110 | "id": import_id, |
| 111 | "bank_account_id": bank_account_id, |
| 112 | "profile_id": profile_id, |
| 113 | "source_format": (profile or {}).get("source_format", "csv") or "csv", |
| 114 | "file_hash": digest, |
| 115 | "period_start": period_start, |
| 116 | "period_end": period_end, |
| 117 | # is_present, not `or ""`: a zero balance is a real balance. |
| 118 | "opening_balance_cents": (str(request.get("opening_balance_cents")).strip() |
| 119 | if object_banking.is_present(request.get("opening_balance_cents")) else ""), |
| 120 | "closing_balance_cents": (str(request.get("closing_balance_cents")).strip() |
| 121 | if object_banking.is_present(request.get("closing_balance_cents")) else ""), |
| 122 | "line_count": str(len(lines)), |
| 123 | "status": gates["status"], |
| 124 | "flags": object_banking.flags_json(gates), |
| 125 | "owner_id": user_id, |
| 126 | } |
| 127 | if account.get("entity_id"): |
| 128 | import_row["entity_id"] = account["entity_id"] |
| 129 | object_records.create_collection_record( |
| 130 | "bank_statement_imports", import_row, base_dir=base, actor=ACTOR) |
| 131 | |
| 132 | known_ids, known_hashes = object_banking.existing_line_keys(bank_account_id, base_dir=base) |
| 133 | imported = duplicates = 0 |
| 134 | for line in lines: |
| 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": gates["checks"], |
| 168 | "period": {"start": period_start, "end": period_end}, |
| 169 | } |