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
reconcile.py · 13.7 KB · 321 lines
| 1 | """site_reconcile -- the bank reconciliation statement (plan/bank-import- |
| 2 | reconciliation-spec.md section 6): "bank closing balance (last accepted |
| 3 | import) vs. book cash balance (fin account), reconciled by: matched total, |
| 4 | outstanding timing items, unresolved tail. The classic rec statement -- a |
| 5 | generative view over stored state, no new engine." |
| 6 | |
| 7 | Identity posture, same as packages/system-dashboard/objects/system/ |
| 8 | scheduler.py: this object is public execute, and everything is gated on a |
| 9 | signed-in identity inside. Unlike the scheduler board (admin-only), any |
| 10 | registered user may see this page -- but ONLY their own accounts and lines. |
| 11 | The scoping happens twice, deliberately: once here (every read below is |
| 12 | filtered to owner_id == the caller before it is ever displayed), and again |
| 13 | in object_banking.reconciliation() itself, which requires the caller to |
| 14 | name a bank_account_id it has already verified belongs to them -- the same |
| 15 | trust-boundary posture packages/app-finance/objects/site/trial_balance.py |
| 16 | documents for object_finance.trial_balance(): these library functions read |
| 17 | collections directly via object_records, which is NOT subject to |
| 18 | permissions/rules.json's row_filter the way the HTTP /collections/* API |
| 19 | is, so the caller (this page) is the only thing standing between one |
| 20 | owner's ledger and another's. |
| 21 | |
| 22 | The account list and the one-account statement are the same page (?account= |
| 23 | <id> selects one of the caller's own accounts) rather than two objects, |
| 24 | matching trial_balance.py's "pick simpler, report which" posture -- there |
| 25 | is no natural second collection endpoint here either; the statement is a |
| 26 | fold across three collections, not a single record's detail. |
| 27 | """ |
| 28 | from __future__ import annotations |
| 29 | |
| 30 | import html |
| 31 | import json |
| 32 | import os |
| 33 | |
| 34 | import object_banking |
| 35 | import object_books_status |
| 36 | import object_records |
| 37 | |
| 38 | DATA_DIR_ENV = "DBBASIC_DATA_DIR" |
| 39 | |
| 40 | |
| 41 | def _base_dir() -> str: |
| 42 | return os.environ.get(DATA_DIR_ENV, "data") |
| 43 | |
| 44 | |
| 45 | def _esc(value) -> str: |
| 46 | return html.escape(str(value or "")) |
| 47 | |
| 48 | |
| 49 | def _money(cents_value, currency: str = "USD") -> str: |
| 50 | """Whole-currency-unit display, 2dp -- the stored value stays integer |
| 51 | cents everywhere else (object_banking.py, bank_lines.amount_cents). |
| 52 | None means "unknown" (no import yet, no book account set), which is a |
| 53 | materially different thing from a real zero balance and must not |
| 54 | print as one. |
| 55 | """ |
| 56 | if cents_value is None: |
| 57 | return "—" # em dash: genuinely unknown, not zero |
| 58 | cents = int(cents_value) |
| 59 | sign = "-" if cents < 0 else "" |
| 60 | amount = abs(cents) |
| 61 | return f"{sign}{currency} {amount // 100:,}.{amount % 100:02d}" |
| 62 | |
| 63 | |
| 64 | ASSURANCE_LABEL = { |
| 65 | "verified": ("Verified", "ok"), |
| 66 | "unverified": ("Unverified", "warn"), |
| 67 | "flagged": ("Flagged", "bad"), |
| 68 | } |
| 69 | |
| 70 | |
| 71 | def _assurance_badge(assurance) -> str: |
| 72 | if not assurance: |
| 73 | return '<span class="badge muted">No imports yet</span>' |
| 74 | label, tone = ASSURANCE_LABEL.get(assurance, (assurance, "muted")) |
| 75 | return f'<span class="badge {tone}">{_esc(label)}</span>' |
| 76 | |
| 77 | |
| 78 | def _currency_for(base, account): |
| 79 | """The denomination code an account is kept in. |
| 80 | |
| 81 | value_accounts names a denomination record rather than carrying a |
| 82 | currency string, because the unit decides how an amount is even |
| 83 | rendered -- eight places for bitcoin, none for yen (object_money.py). |
| 84 | Falls back to USD so a half-configured account still displays. |
| 85 | """ |
| 86 | denomination_id = str(account.get("denomination_id") or "").strip() |
| 87 | if not denomination_id: |
| 88 | return "USD" |
| 89 | try: |
| 90 | row = object_records.get_collection_record( |
| 91 | "denominations", denomination_id, base_dir=base) |
| 92 | except Exception: |
| 93 | return "USD" |
| 94 | return str(row.get("code") or "USD") |
| 95 | |
| 96 | |
| 97 | def _my_accounts(base, user_id): |
| 98 | return [a for a in object_records.read_collection_records("value_accounts", base_dir=base) |
| 99 | if a.get("owner_id") == user_id] |
| 100 | |
| 101 | |
| 102 | def _my_lines(base, user_id, bank_account_id): |
| 103 | return [l for l in object_records.read_collection_records("bank_lines", base_dir=base) |
| 104 | if l.get("bank_account_id") == bank_account_id and l.get("owner_id") == user_id] |
| 105 | |
| 106 | |
| 107 | def _suggestion_summary(row) -> str: |
| 108 | raw = row.get("suggestions") or "" |
| 109 | if not raw: |
| 110 | return "" |
| 111 | try: |
| 112 | proposals = json.loads(raw) |
| 113 | except (ValueError, TypeError): |
| 114 | return "" |
| 115 | if not proposals: |
| 116 | return "" |
| 117 | best = proposals[0] |
| 118 | return (f"tier {_esc(best.get('tier'))}: {_esc(', '.join(best.get('refs') or []))}" |
| 119 | f" — {_esc(best.get('why'))}") |
| 120 | |
| 121 | |
| 122 | def _line_row_html(row, currency): |
| 123 | status = row.get("match_status") or "unmatched" |
| 124 | tone = "warn" if status == "suggested" else "" |
| 125 | return ( |
| 126 | "<tr>" |
| 127 | f"<td>{_esc(row.get('posted_on'))}</td>" |
| 128 | f"<td>{_esc(row.get('description'))}</td>" |
| 129 | f"<td class=\"num\">{_money(row.get('amount_cents'), currency)}</td>" |
| 130 | f"<td class=\"{tone}\">{_esc(status)}</td>" |
| 131 | f"<td>{_suggestion_summary(row)}</td>" |
| 132 | "</tr>" |
| 133 | ) |
| 134 | |
| 135 | |
| 136 | def _timing_row_html(row, currency): |
| 137 | return ( |
| 138 | "<tr>" |
| 139 | f"<td>{_esc(row.get('posted_on'))}</td>" |
| 140 | f"<td>{_esc(row.get('description'))}</td>" |
| 141 | f"<td class=\"num\">{_money(row.get('amount_cents'), currency)}</td>" |
| 142 | "</tr>" |
| 143 | ) |
| 144 | |
| 145 | |
| 146 | def _account_list_html(accounts, selected_id): |
| 147 | if not accounts: |
| 148 | return '<p class="hint">No bank accounts yet.</p>' |
| 149 | items = [] |
| 150 | for account in accounts: |
| 151 | current = " class=\"current\"" if account.get("id") == selected_id else "" |
| 152 | items.append( |
| 153 | f'<li{current}><a href="/reconcile?account={_esc(account["id"])}">' |
| 154 | f'{_esc(account.get("name") or account["id"])}</a>' |
| 155 | f' <span class="hint">{_esc(account.get("custodian"))}</span></li>' |
| 156 | ) |
| 157 | return "<ul class=\"accountlist\">" + "".join(items) + "</ul>" |
| 158 | |
| 159 | |
| 160 | def _statement_html(account, result, open_lines, timing_lines, currency="USD"): |
| 161 | tiles = f""" |
| 162 | <div class="tiles"> |
| 163 | <div class="tile"><div class="n">{_money(result['bank_closing_cents'], currency)}</div><div class="l">Bank closing balance</div></div> |
| 164 | <div class="tile"><div class="n">{_money(result['book_balance_cents'], currency)}</div><div class="l">Book balance</div></div> |
| 165 | <div class="tile"><div class="n">{_money(result['difference_cents'], currency)}</div><div class="l">Difference</div></div> |
| 166 | <div class="tile"><div class="n">{_money(result['timing_cents'], currency)}</div><div class="l">Outstanding timing items</div></div> |
| 167 | </div>""" |
| 168 | |
| 169 | rec_rows = "".join(_line_row_html(row, currency) for row in open_lines) or \ |
| 170 | '<tr><td colspan="5" class="hint">Nothing open -- every line is matched or resolved.</td></tr>' |
| 171 | timing_rows = "".join(_timing_row_html(row, currency) for row in timing_lines) or \ |
| 172 | '<tr><td colspan="3" class="hint">No outstanding timing items.</td></tr>' |
| 173 | |
| 174 | status_badge = ('<span class="badge ok">✓ Reconciled</span>' if result["reconciled"] |
| 175 | else '<span class="badge warn">Not yet reconciled</span>') |
| 176 | |
| 177 | statement_date = result.get("bank_statement_date") or "—" |
| 178 | |
| 179 | return f""" |
| 180 | <div class="pagehead"><h1>{_esc(account.get('name') or account['id'])}</h1> |
| 181 | <p class="hint">{_esc(account.get('custodian'))} · statement as of {_esc(statement_date)} |
| 182 | · {_assurance_badge(result['assurance'])}</p></div> |
| 183 | {tiles} |
| 184 | <h2>The tie</h2> |
| 185 | <table class="rec"> |
| 186 | <tbody> |
| 187 | <tr><td>Bank statement closing balance</td><td class="num">{_money(result['bank_closing_cents'], currency)}</td></tr> |
| 188 | <tr><td>Book balance (your ledger)</td><td class="num">{_money(result['book_balance_cents'], currency)}</td></tr> |
| 189 | <tr class="totals"><td>Difference</td><td class="num">{_money(result['difference_cents'], currency)}</td></tr> |
| 190 | <tr><td>Outstanding timing items (on the statement, not yet booked)</td><td class="num">{_money(result['timing_cents'], currency)}</td></tr> |
| 191 | <tr class="totals"><td>{status_badge}</td><td class="num">{result['unmatched_count']} unmatched |
| 192 | · {result['suggested_count']} suggested</td></tr> |
| 193 | </tbody> |
| 194 | </table> |
| 195 | <p class="hint">Reconciled means the difference between the bank and the books is |
| 196 | fully EXPLAINED by outstanding timing items -- not merely that the numbers |
| 197 | happen to match. Any unmatched or suggested line below is unexplained slack.</p> |
| 198 | <h2>Open lines (unmatched & suggested)</h2> |
| 199 | <table class="rec"> |
| 200 | <thead><tr><th>Posted</th><th>Description</th><th class="num">Amount</th><th>Status</th><th>Suggestion</th></tr></thead> |
| 201 | <tbody>{rec_rows}</tbody> |
| 202 | </table> |
| 203 | <h2>Resolved timing items</h2> |
| 204 | <table class="rec"> |
| 205 | <thead><tr><th>Posted</th><th>Description</th><th class="num">Amount</th></tr></thead> |
| 206 | <tbody>{timing_rows}</tbody> |
| 207 | </table> |
| 208 | """ |
| 209 | |
| 210 | |
| 211 | _STYLE = """ |
| 212 | .wrap { max-width: 1100px; margin: 0 auto; padding: 1.25rem; } |
| 213 | table.rec { width: 100%; border-collapse: collapse; margin: 0.5rem 0 1.5rem; } |
| 214 | table.rec th, table.rec td { text-align: left; padding: 0.4rem 0.6rem; border-bottom: 1px solid var(--line, #38384a); vertical-align: top; } |
| 215 | table.rec td.num, table.rec th.num { text-align: right; } |
| 216 | table.rec tr.totals td { font-weight: 700; border-top: 2px solid var(--line, #38384a); } |
| 217 | .tiles { display: flex; gap: 0.75rem; flex-wrap: wrap; margin: 1rem 0; } |
| 218 | .tile { background: var(--panel, #1a1a22); border: 1px solid var(--line, #333); border-radius: 8px; padding: 0.6rem 1rem; min-width: 160px; } |
| 219 | .tile .n { font-size: 1.2rem; font-weight: 600; } |
| 220 | .tile .l { font-size: 0.7rem; text-transform: uppercase; letter-spacing: 0.05em; color: var(--muted, #999); } |
| 221 | h2 { font-size: 0.85rem; text-transform: uppercase; letter-spacing: 0.05em; color: var(--muted, #999); margin: 1.5rem 0 0.5rem; } |
| 222 | .hint { color: var(--muted, #999); font-size: 0.85rem; } |
| 223 | .badge { display: inline-block; padding: 0.1rem 0.5rem; border-radius: 999px; font-size: 0.75rem; } |
| 224 | .badge.ok { background: rgba(82,210,115,0.15); color: var(--positive, #52d273); } |
| 225 | .badge.warn { background: rgba(241,183,71,0.15); color: var(--warning, #f1b747); } |
| 226 | .badge.bad { background: rgba(255,107,107,0.15); color: var(--danger, #ff6b6b); } |
| 227 | .badge.muted { background: rgba(153,153,153,0.15); color: var(--muted, #999); } |
| 228 | .warn { color: var(--warning, #f1b747); } |
| 229 | ul.accountlist { list-style: none; padding: 0; margin: 0.5rem 0 1.5rem; } |
| 230 | ul.accountlist li { padding: 0.4rem 0; border-bottom: 1px solid var(--line, #333); } |
| 231 | ul.accountlist li.current a { font-weight: 700; } |
| 232 | """ |
| 233 | |
| 234 | |
| 235 | def _page(body, *, title="Reconcile"): |
| 236 | return { |
| 237 | "content_type": "text/html; charset=utf-8", |
| 238 | "body": f"""<!doctype html> |
| 239 | <html lang="en"> |
| 240 | <head> |
| 241 | <meta charset="utf-8"> |
| 242 | <meta name="viewport" content="width=device-width, initial-scale=1"> |
| 243 | <title>{_esc(title)}</title> |
| 244 | <link rel="stylesheet" href="/style"> |
| 245 | <style>{_STYLE}</style> |
| 246 | </head> |
| 247 | <body> |
| 248 | <script src="/nav"></script> |
| 249 | <div class="wrap"> |
| 250 | <h1>Reconcile</h1> |
| 251 | {body} |
| 252 | </div> |
| 253 | </body> |
| 254 | </html>""", |
| 255 | } |
| 256 | |
| 257 | |
| 258 | def _books_banner_html(base): |
| 259 | """Warn, here of all places, when the ledger side cannot post. |
| 260 | |
| 261 | This page's whole claim is that two independent records agree. If the |
| 262 | composers are skipping because their accounts are unmapped, the book |
| 263 | side is not a second opinion at all -- it is an empty column, and a |
| 264 | difference shown against it means nothing. Better to say so where |
| 265 | someone is actively reading the comparison than to leave the reason in |
| 266 | a return value nobody sees. |
| 267 | """ |
| 268 | try: |
| 269 | status = object_books_status.books_status(base_dir=base) |
| 270 | except Exception: |
| 271 | return "" |
| 272 | if status.get("ready"): |
| 273 | return "" |
| 274 | items = "".join( |
| 275 | f"<li><code>{_esc(p.get('id'))}</code> — {_esc(p.get('impact'))}</li>" |
| 276 | for p in status.get("problems", [])[:6]) |
| 277 | return ( |
| 278 | '<div class="warn" style="border:1px solid currentColor;border-radius:8px;' |
| 279 | 'padding:0.75rem 1rem;margin:0 0 1rem">' |
| 280 | f"<strong>The books cannot post yet.</strong> {_esc(status.get('summary'))}" |
| 281 | + (f"<ul style='margin:0.5rem 0 0'>{items}</ul>" if items else "") |
| 282 | + "<p style='margin:0.5rem 0 0;font-size:0.85rem'>Any difference shown below " |
| 283 | "is measured against a ledger that is not receiving entries, so it reflects " |
| 284 | "configuration rather than a real discrepancy.</p></div>") |
| 285 | |
| 286 | |
| 287 | def GET(request): |
| 288 | identity = request.get("_identity") or {} |
| 289 | user_id = identity.get("user_id") |
| 290 | if not user_id: |
| 291 | return _page('<p class="hint"><a href="/login?next=/reconcile">Sign in</a> to see ' |
| 292 | "your bank accounts and their reconciliation statements.</p>") |
| 293 | |
| 294 | base = _base_dir() |
| 295 | accounts = _my_accounts(base, user_id) |
| 296 | requested_id = str(request.get("account") or "").strip() |
| 297 | # Owner scoping happens right here: an id the caller does not own is |
| 298 | # simply not in `accounts`, so it is silently treated as no selection |
| 299 | # rather than looked up directly -- there is no code path from a |
| 300 | # guessed/borrowed id to another owner's statement. |
| 301 | selected = next((a for a in accounts if a.get("id") == requested_id), None) |
| 302 | |
| 303 | body = _books_banner_html(base) + \ |
| 304 | f'<h2>Your bank accounts</h2>{_account_list_html(accounts, requested_id)}' |
| 305 | if selected is not None: |
| 306 | as_of = str(request.get("as_of") or "").strip() |
| 307 | result = object_banking.reconciliation(selected["id"], base_dir=base, as_of=as_of) |
| 308 | lines = _my_lines(base, user_id, selected["id"]) |
| 309 | open_lines = sorted( |
| 310 | (l for l in lines if (l.get("match_status") or "unmatched") in ("unmatched", "suggested")), |
| 311 | key=lambda l: l.get("posted_on") or "") |
| 312 | timing_lines = sorted( |
| 313 | (l for l in lines if l.get("match_status") == "resolved" |
| 314 | and (l.get("resolved_as") or "") == "timing"), |
| 315 | key=lambda l: l.get("posted_on") or "") |
| 316 | body += _statement_html(selected, result, open_lines, timing_lines, |
| 317 | _currency_for(base, selected)) |
| 318 | elif requested_id: |
| 319 | body += '<p class="hint">That account was not found among yours.</p>' |
| 320 | |
| 321 | return _page(body) |