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
customer_funds.py · 7.8 KB · 192 lines
| 1 | """Customer funds reconciliation: does what we owe match what we booked? |
| 2 | |
| 3 | The report that makes prepaid balances honest. Wallet balances are a |
| 4 | LIABILITY -- money customers handed over before the service was |
| 5 | rendered -- and this page answers the one question that liability has to |
| 6 | survive: does the sum of every wallet equal the credit balance of the |
| 7 | customer-funds account in the books? |
| 8 | |
| 9 | Two facts about the arithmetic, both of which a naive version gets wrong: |
| 10 | |
| 11 | **Outstanding holds are added back.** A wallet with a hold in flight |
| 12 | reports a balance BELOW what its owner is actually owed; the money is |
| 13 | still theirs until the run settles, and system_wallet_books deliberately |
| 14 | composes nothing for a hold (a hold is money that was never spent, not a |
| 15 | debit that got undone). Without adding holds back, this report would |
| 16 | show a discrepancy every time a template run was in progress -- a |
| 17 | reconciliation that cries wolf is one nobody reads. |
| 18 | |
| 19 | **A liability's natural balance is a CREDIT.** Booked liability is |
| 20 | credits minus debits, not the trial balance's raw column totals. |
| 21 | |
| 22 | Computed server-side, like app-finance's trial_balance page and for the |
| 23 | same reason: it is a fold across every wallet and every posted journal, |
| 24 | not one record's detail, so there is no natural collection endpoint for |
| 25 | the browser to call. |
| 26 | |
| 27 | Trust boundary: this is an OPERATOR report. Unlike trial_balance, which |
| 28 | scopes to the signed-in user's own books, reconciling customer funds is |
| 29 | inherently cross-owner -- the question is whether OUR books match ALL |
| 30 | customers' balances. It therefore requires an admin identity and says so |
| 31 | rather than quietly showing a signed-in customer a fold over everyone's |
| 32 | money. |
| 33 | """ |
| 34 | from __future__ import annotations |
| 35 | |
| 36 | import os |
| 37 | |
| 38 | _STYLE = """ |
| 39 | table.cf { width: 100%; border-collapse: collapse; margin: 1rem 0; max-width: 40rem; } |
| 40 | table.cf th, table.cf td { text-align: left; padding: 0.4rem 0.6rem; border-bottom: 1px solid var(--line, #38384a); } |
| 41 | table.cf td.num { text-align: right; font-variant-numeric: tabular-nums; } |
| 42 | table.cf tr.total td { font-weight: 700; border-top: 2px solid var(--line, #38384a); } |
| 43 | .verdict { padding: 0.6rem 0.9rem; border-radius: 6px; margin: 1rem 0; max-width: 40rem; } |
| 44 | .verdict.ok { border: 1px solid #2f7d4f; } |
| 45 | .verdict.off { border: 1px solid #b5493a; } |
| 46 | """ |
| 47 | |
| 48 | DATA_DIR_ENV = "DBBASIC_DATA_DIR" |
| 49 | |
| 50 | |
| 51 | def _data_dir() -> str: |
| 52 | import object_records |
| 53 | return os.environ.get(DATA_DIR_ENV, object_records.DEFAULT_DATA_DIR) |
| 54 | |
| 55 | |
| 56 | def _money(minor, currency: str = "USD") -> str: |
| 57 | value = int(minor or 0) |
| 58 | sign = "-" if value < 0 else "" |
| 59 | amount = abs(value) |
| 60 | return f"{sign}{currency} {amount // 100}.{amount % 100:02d}" |
| 61 | |
| 62 | |
| 63 | def _esc(value) -> str: |
| 64 | return ( |
| 65 | str(value or "") |
| 66 | .replace("&", "&") |
| 67 | .replace("<", "<") |
| 68 | .replace(">", ">") |
| 69 | .replace('"', """) |
| 70 | .replace("'", "'") |
| 71 | ) |
| 72 | |
| 73 | |
| 74 | def _report(base): |
| 75 | import object_billing |
| 76 | import object_finance |
| 77 | import object_records |
| 78 | |
| 79 | wallets = object_records.read_collection_records("wallets", base_dir=base) |
| 80 | entries = object_records.read_collection_records("wallet_entries", base_dir=base) |
| 81 | |
| 82 | balances = [] |
| 83 | for wallet in wallets: |
| 84 | try: |
| 85 | balances.append(int(wallet.get("balance_minor") or 0)) |
| 86 | except (TypeError, ValueError): |
| 87 | continue |
| 88 | |
| 89 | funds_account = "" |
| 90 | for row in object_records.read_collection_records("app_settings", base_dir=base): |
| 91 | if row.get("key") == "billing.journal.customer_funds_account": |
| 92 | funds_account = str(row.get("value") or "").strip() |
| 93 | break |
| 94 | |
| 95 | debit = credit = 0 |
| 96 | if funds_account: |
| 97 | for row in object_finance.trial_balance(base_dir=base): |
| 98 | if row["account_id"] == funds_account: |
| 99 | debit = int(row["debit_total_cents"] or 0) |
| 100 | credit = int(row["credit_total_cents"] or 0) |
| 101 | break |
| 102 | |
| 103 | result = object_billing.customer_funds_reconciliation( |
| 104 | balances, debit, credit, |
| 105 | outstanding_holds=object_billing.outstanding_holds_minor(entries), |
| 106 | ) |
| 107 | result["wallet_count"] = len(balances) |
| 108 | result["funds_account"] = funds_account |
| 109 | return result |
| 110 | |
| 111 | |
| 112 | def GET(request): |
| 113 | identity = request.get("_identity", {}) or {} |
| 114 | user_id = identity.get("user_id") |
| 115 | is_admin = bool(identity.get("is_admin")) or "admin" in (identity.get("roles") or []) |
| 116 | |
| 117 | if not user_id: |
| 118 | body = ('<p class="hint"><a href="/login?next=/customer-funds">Sign in</a>' |
| 119 | " to see this report.</p>") |
| 120 | elif not is_admin: |
| 121 | body = ('<p class="hint">Customer funds reconciliation is an operator ' |
| 122 | "report: it folds every customer's balance against the books, " |
| 123 | "so it is deliberately not scoped to one signed-in account. " |
| 124 | "An admin identity is required.</p>") |
| 125 | else: |
| 126 | try: |
| 127 | result = _report(_data_dir()) |
| 128 | except Exception as exc: |
| 129 | result = None |
| 130 | body = ('<p class="hint">Could not compute: ' |
| 131 | f"{_esc(str(exc)[:200])}</p>") |
| 132 | if result is not None: |
| 133 | if not result["funds_account"]: |
| 134 | verdict = ('<div class="verdict off"><strong>Not configured.</strong> ' |
| 135 | "Set <code>billing.journal.customer_funds_account</code> in " |
| 136 | "app_settings so wallet money reaches the books. Until then " |
| 137 | "nothing is booked and there is nothing to reconcile " |
| 138 | "against.</div>") |
| 139 | elif result["balanced"]: |
| 140 | verdict = ('<div class="verdict ok"><strong>Balanced.</strong> ' |
| 141 | "Every cent owed to a customer is on the books as a " |
| 142 | "liability.</div>") |
| 143 | else: |
| 144 | verdict = ('<div class="verdict off"><strong>Off by ' |
| 145 | f"{_esc(_money(result['difference_minor']))}.</strong> " |
| 146 | "What customers are owed does not match the booked " |
| 147 | "liability. Entries created before " |
| 148 | "system_wallet_books was installed compose nothing " |
| 149 | "retroactively -- that is the usual cause, and it is a " |
| 150 | "backfill, not a bug.</div>") |
| 151 | body = f""" |
| 152 | <div class="breadcrumb"><a href="/">Home</a> / Customer Funds</div> |
| 153 | <div class="pagehead"><h1>Customer Funds Reconciliation</h1></div> |
| 154 | <p class="hint">Prepaid balances are money owed, not money earned. This |
| 155 | compares what customers are owed against what the books say we owe.</p> |
| 156 | {verdict} |
| 157 | <table class="cf"> |
| 158 | <tbody> |
| 159 | <tr><td>Wallet balances ({result['wallet_count']} wallets)</td> |
| 160 | <td class="num">{_esc(_money(result['wallet_balances_minor']))}</td></tr> |
| 161 | <tr><td>Outstanding holds (ring-fenced, still the customer's)</td> |
| 162 | <td class="num">{_esc(_money(result['outstanding_holds_minor']))}</td></tr> |
| 163 | <tr class="total"><td>Owed to customers</td> |
| 164 | <td class="num">{_esc(_money(result['owed_to_customers_minor']))}</td></tr> |
| 165 | <tr><td>Booked liability (credits − debits)</td> |
| 166 | <td class="num">{_esc(_money(result['booked_liability_minor']))}</td></tr> |
| 167 | <tr class="total"><td>Difference</td> |
| 168 | <td class="num">{_esc(_money(result['difference_minor']))}</td></tr> |
| 169 | </tbody> |
| 170 | </table> |
| 171 | """ |
| 172 | |
| 173 | who = (f"signed in as <strong>{_esc(user_id)}</strong>" if user_id |
| 174 | else '<a href="/login?next=/customer-funds">sign in</a>') |
| 175 | html = f"""<!doctype html> |
| 176 | <html lang="en"> |
| 177 | <head> |
| 178 | <meta charset="utf-8"> |
| 179 | <meta name="viewport" content="width=device-width, initial-scale=1"> |
| 180 | <title>Customer Funds</title> |
| 181 | <link rel="stylesheet" href="/style"> |
| 182 | <style>{_STYLE}</style> |
| 183 | </head> |
| 184 | <body> |
| 185 | <div class="wrap"> |
| 186 | <header class="app"><h1><a href="/">DBBASIC</a></h1><div class="who">{who}</div></header> |
| 187 | {body} |
| 188 | </div> |
| 189 | <script src="/nav"></script> |
| 190 | </body> |
| 191 | </html>""" |
| 192 | return {"content_type": "text/html; charset=utf-8", "body": html} |