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_finance.py · 31.8 KB · 723 lines
| 1 | """Pure computed helpers over packages/app-finance's collections. |
| 2 | |
| 3 | Mirrors the predecessor system's own design for its finance app (reconciled |
| 4 | against a private predecessor-system audit, not part of this repo): journal |
| 5 | totals and the trial balance were COMPUTED PROPERTIES, folded from journal |
| 6 | lines on read, never stored or enforced. This module keeps that exact |
| 7 | posture: |
| 8 | |
| 9 | - journal_totals() folds fin_journal_lines for one journal into |
| 10 | (total_debits_cents, total_credits_cents, is_balanced). Nothing writes |
| 11 | these numbers back onto the journal record -- there is no totals- |
| 12 | stamping HANDLES handler in this package, unlike app-invoices' |
| 13 | invoice_totals or app-orders' order_totals. That absence is |
| 14 | deliberate: the source's own posting flow never enforced or cached a |
| 15 | balance either (posting is a bare draft->posted status flip -- see |
| 16 | packages/app-finance/schemas/fin_journals.json's status field help and |
| 17 | dbbasic-package.json's Deferred list). A future slice could add a |
| 18 | HANDLES handler that stamps a balance-check warning, but that would be |
| 19 | new behavior the source never had, so it stays out of this migration. |
| 20 | |
| 21 | - trial_balance() is the one report this v1 slice ships (the predecessor |
| 22 | system's own reports "filter posted lines", per the same reconciled |
| 23 | source audit). Profit & loss, balance sheet, and cash flow are the same |
| 24 | fold shape |
| 25 | over the same posted-lines data and are DEFERRED -- not built here |
| 26 | (see dbbasic-package.json's description). |
| 27 | |
| 28 | Both functions are read-only folds over object_records.read_collection_ |
| 29 | records(); base_dir is the caller's responsibility, same convention as |
| 30 | packages/app-invoices/objects/system/invoice_totals.py's own _data_dir() |
| 31 | helper (this module is a plain library, not a DBBASIC object, so it has |
| 32 | no request payload to read an identity or base_dir override from -- see |
| 33 | this module's own callers for how each resolves base_dir). |
| 34 | |
| 35 | Integer-cents arithmetic only, per 00-doctrine-and-contract.md: money is |
| 36 | always a whole number of cents, parsed defensively (blank/None -> 0) |
| 37 | since a hand-edited TSV row or a partially-filled form draft can leave a |
| 38 | *_cents field blank. |
| 39 | |
| 40 | compose_posted_journal() joined this module later, at the doctrine-#4 |
| 41 | extraction threshold (docs/logic-decisions.md): once system_books |
| 42 | (payments/refunds/bounces/issues), fin_recurring_runner (adjusting |
| 43 | entries), and action_reverse_journal (mirrors) had each hand-rolled the |
| 44 | same create-journal -> create-lines -> verify -> post sequence, the third |
| 45 | composer (system_stock_books, inventory losses) triggered the extraction. |
| 46 | Callers own POLICY (which accounts, what amount, when to compose); this |
| 47 | function owns MECHANICS (idempotency by provenance, draft -> lines -> |
| 48 | re-read -> verify balance -> post). Composed entries are storage-level |
| 49 | writes that bypass the HTTP-only balance hook, so the re-verify here is |
| 50 | the gate for every generated journal; the hook remains the gate for |
| 51 | human entries. |
| 52 | |
| 53 | profit_and_loss() and balance_sheet() close the gap this module's own |
| 54 | docstring flagged as deferred above and that plan/accounting-coverage- |
| 55 | and-usability.md's M1 calls the highest-value missing piece: the two |
| 56 | financial statements everything else in this codebase has been building |
| 57 | toward. Both are the same fold shape as trial_balance() -- posted-only, |
| 58 | owner-scoped the same non-negotiable way -- just re-bucketed by sign |
| 59 | convention per account type instead of by raw debit/credit columns. |
| 60 | Neither stores or caches anything; call them again and the numbers are |
| 61 | whatever the ledger says right now. |
| 62 | """ |
| 63 | from __future__ import annotations |
| 64 | |
| 65 | from decimal import ROUND_FLOOR, Decimal |
| 66 | from pathlib import Path |
| 67 | from typing import Any, Iterable |
| 68 | |
| 69 | import object_ids |
| 70 | import object_records |
| 71 | from object_versions import DEFAULT_DATA_DIR |
| 72 | |
| 73 | # Chart-of-accounts display order for trial_balance rows: the conventional |
| 74 | # accounting statement order (assets, liabilities, equity, then income, |
| 75 | # expense), matching fin_accounts.json's account_type enum order. Any |
| 76 | # account_type not in this map (should not happen -- the schema enum is |
| 77 | # closed) sorts last rather than raising. |
| 78 | _ACCOUNT_TYPE_ORDER = { |
| 79 | "asset": 0, |
| 80 | "liability": 1, |
| 81 | "equity": 2, |
| 82 | "income": 3, |
| 83 | "expense": 4, |
| 84 | } |
| 85 | |
| 86 | _STATUS_POSTED = "posted" |
| 87 | |
| 88 | |
| 89 | def to_cents(value: Any) -> int: |
| 90 | """Parse a stored numeric string as an integer number of cents. |
| 91 | |
| 92 | THE defensive ledger parser for this codebase. Public because three |
| 93 | other modules had byte-identical private copies of it (app-invoices' |
| 94 | invoice_totals._to_int, app-orders' order_totals._to_int, and an |
| 95 | inlined bare int() in object_banking.reconciliation) -- one shared |
| 96 | floor-parser is one place to be right about rounding. |
| 97 | |
| 98 | Deliberately NOT the same function as object_money.to_minor (which |
| 99 | refuses inexact input) or object_money.quantize_minor (which rounds |
| 100 | half-up on request) or object_banking.parse_cents (which also strips |
| 101 | currency symbols and locale separators from bank exports). Those are |
| 102 | three different policies for three different jobs, and merging them |
| 103 | would silently change what a number means somewhere. |
| 104 | |
| 105 | Decimal (never a bare float) so a stray fractional value in a |
| 106 | hand-edited row can't introduce binary-float rounding error before |
| 107 | the floor -- same discipline invoice_totals.py's own _to_int uses. |
| 108 | Blank/None -> 0. |
| 109 | """ |
| 110 | text = str(value or "").strip() |
| 111 | if not text: |
| 112 | return 0 |
| 113 | return int(Decimal(text).to_integral_value(rounding=ROUND_FLOOR)) |
| 114 | |
| 115 | |
| 116 | # Historical private name, kept so this module's own callers stay unchanged. |
| 117 | _to_cents = to_cents |
| 118 | |
| 119 | |
| 120 | def journal_totals( |
| 121 | journal_id: str, |
| 122 | *, |
| 123 | base_dir: Path | str = DEFAULT_DATA_DIR, |
| 124 | roots: Iterable[Path] | None = None, |
| 125 | ) -> dict[str, Any]: |
| 126 | """Fold fin_journal_lines for one journal into computed totals. |
| 127 | |
| 128 | Returns {"total_debits_cents", "total_credits_cents", "is_balanced"}. |
| 129 | is_balanced is total_debits_cents == total_credits_cents -- true for |
| 130 | a journal with zero lines (0 == 0), same as any other computed- |
| 131 | property equality check. Nothing here checks whether journal_id |
| 132 | actually refers to an existing fin_journals record: a dangling or |
| 133 | unknown id simply folds zero matching lines and returns a balanced |
| 134 | zero total, the same graceful-empty behavior a fresh draft journal |
| 135 | (no lines yet) gets. |
| 136 | """ |
| 137 | lines = object_records.read_collection_records("fin_journal_lines", base_dir=base_dir, roots=roots) |
| 138 | |
| 139 | total_debits_cents = 0 |
| 140 | total_credits_cents = 0 |
| 141 | for line in lines: |
| 142 | if line.get("journal_id") != journal_id: |
| 143 | continue |
| 144 | total_debits_cents += _to_cents(line.get("debit_cents")) |
| 145 | total_credits_cents += _to_cents(line.get("credit_cents")) |
| 146 | |
| 147 | return { |
| 148 | "total_debits_cents": total_debits_cents, |
| 149 | "total_credits_cents": total_credits_cents, |
| 150 | "is_balanced": total_debits_cents == total_credits_cents, |
| 151 | } |
| 152 | |
| 153 | |
| 154 | def trial_balance( |
| 155 | *, |
| 156 | base_dir: Path | str = DEFAULT_DATA_DIR, |
| 157 | roots: Iterable[Path] | None = None, |
| 158 | owner: str | None = None, |
| 159 | ) -> list[dict[str, Any]]: |
| 160 | """Per-account debit/credit totals over POSTED journals only. |
| 161 | |
| 162 | Matches the source's own report semantics (reconciled against a |
| 163 | private predecessor-system audit, not part of this repo: reports |
| 164 | filter posted lines) -- a draft journal's lines never contribute, |
| 165 | with no separate "is this journal balanced" check |
| 166 | at all (that would be enforcement; the source never added it here |
| 167 | either, see this module's docstring). |
| 168 | |
| 169 | owner, when given, restricts the fold to journals owned by that |
| 170 | user (fin_journals.owner_id == owner) before any line is summed. |
| 171 | This is NOT a convenience filter -- callers that read a specific |
| 172 | user's financial data (e.g. packages/app-finance/objects/site/ |
| 173 | trial_balance.py) MUST pass owner=<that user's id>, because this |
| 174 | function reads collections directly via object_records and is not |
| 175 | itself subject to the row_filter owner_id=$user_id permission rule |
| 176 | that packages/app-finance/permissions/rules.json enforces on the |
| 177 | HTTP /collections/* API. Passing owner=None folds every owner's |
| 178 | posted journals together and is only appropriate for an operator/ |
| 179 | admin-level caller (nothing in this package calls it that way yet). |
| 180 | |
| 181 | Returns a list of {"account_id", "account_name", "account_code", |
| 182 | "account_type", "debit_total_cents", "credit_total_cents"} rows, one |
| 183 | per account that has at least one posted line -- accounts with zero |
| 184 | posted activity are omitted rather than listed at zero, since this |
| 185 | is a fold over lines, not an enumeration of the chart of accounts. |
| 186 | Sorted in conventional statement order (asset, liability, equity, |
| 187 | income, expense), then by account code/name within each type. |
| 188 | """ |
| 189 | journals = object_records.read_collection_records("fin_journals", base_dir=base_dir, roots=roots) |
| 190 | posted_journal_ids = { |
| 191 | journal.get("id") |
| 192 | for journal in journals |
| 193 | if journal.get("status") == _STATUS_POSTED |
| 194 | and (owner is None or journal.get("owner_id") == owner) |
| 195 | } |
| 196 | if not posted_journal_ids: |
| 197 | return [] |
| 198 | |
| 199 | lines = object_records.read_collection_records("fin_journal_lines", base_dir=base_dir, roots=roots) |
| 200 | totals_by_account: dict[str, list[int]] = {} |
| 201 | for line in lines: |
| 202 | if line.get("journal_id") not in posted_journal_ids: |
| 203 | continue |
| 204 | account_id = line.get("account_id") |
| 205 | if not account_id: |
| 206 | continue |
| 207 | bucket = totals_by_account.setdefault(account_id, [0, 0]) |
| 208 | bucket[0] += _to_cents(line.get("debit_cents")) |
| 209 | bucket[1] += _to_cents(line.get("credit_cents")) |
| 210 | |
| 211 | if not totals_by_account: |
| 212 | return [] |
| 213 | |
| 214 | accounts_by_id = { |
| 215 | account.get("id"): account |
| 216 | for account in object_records.read_collection_records("fin_accounts", base_dir=base_dir, roots=roots) |
| 217 | } |
| 218 | |
| 219 | rows = [] |
| 220 | for account_id, (debit_total_cents, credit_total_cents) in totals_by_account.items(): |
| 221 | account = accounts_by_id.get(account_id, {}) |
| 222 | rows.append({ |
| 223 | "account_id": account_id, |
| 224 | "account_name": account.get("name", ""), |
| 225 | "account_code": account.get("code", ""), |
| 226 | "account_type": account.get("account_type", ""), |
| 227 | "debit_total_cents": debit_total_cents, |
| 228 | "credit_total_cents": credit_total_cents, |
| 229 | }) |
| 230 | |
| 231 | rows.sort(key=lambda row: ( |
| 232 | _ACCOUNT_TYPE_ORDER.get(row["account_type"], 99), |
| 233 | row["account_code"], |
| 234 | row["account_name"], |
| 235 | )) |
| 236 | return rows |
| 237 | |
| 238 | |
| 239 | _UNREADABLE = object() # sentinel: could not scan fin_journals for a marker |
| 240 | |
| 241 | |
| 242 | def _scan_provenance(base_dir, generated_from): |
| 243 | try: |
| 244 | for row in object_records.read_collection_records("fin_journals", base_dir=base_dir): |
| 245 | if row.get("generated_from") == generated_from: |
| 246 | return row |
| 247 | except Exception: |
| 248 | return _UNREADABLE |
| 249 | return None |
| 250 | |
| 251 | |
| 252 | def find_journal_by_provenance(base_dir, generated_from): |
| 253 | """The fin_journals row stamped with this generated_from, else None. |
| 254 | |
| 255 | Unreadable books also return None -- callers that need the |
| 256 | do-not-risk-a-duplicate posture get it inside compose_posted_journal, |
| 257 | which distinguishes 'absent' from 'could not tell'. |
| 258 | """ |
| 259 | row = _scan_provenance(base_dir, generated_from) |
| 260 | return None if row is _UNREADABLE else row |
| 261 | |
| 262 | |
| 263 | def compose_posted_journal(base_dir, *, generated_from, date, description, |
| 264 | lines, owner_id, entity_id="", kind="standard", |
| 265 | actor="object_finance", post=True): |
| 266 | """Compose one journal from line specs and post it when balanced. |
| 267 | |
| 268 | lines: iterable of {"account_id", "debit_cents", "credit_cents", |
| 269 | optional "memo", optional "entity_id" (defaults to the journal's)}. |
| 270 | Returns {"ok": True, ...} shapes: |
| 271 | already composed -> {"ok", "skipped", "journal_id", "posted"} |
| 272 | nothing to book -> {"ok", "skipped"} |
| 273 | composed -> {"ok", "journal_id", "posted", maybe "note"} |
| 274 | and {"ok": False, "error"} only for unusable line amounts. post=False |
| 275 | composes but always leaves the draft (recurring templates without |
| 276 | auto_post). |
| 277 | """ |
| 278 | normalized, debits, credits = [], 0, 0 |
| 279 | for spec in lines or []: |
| 280 | if not isinstance(spec, dict): |
| 281 | continue |
| 282 | try: |
| 283 | dr = int(spec.get("debit_cents") or 0) |
| 284 | cr = int(spec.get("credit_cents") or 0) |
| 285 | except (TypeError, ValueError): |
| 286 | return {"ok": False, "error": "line amounts must be integer cents"} |
| 287 | if dr < 0 or cr < 0: |
| 288 | return {"ok": False, "error": "line amounts must not be negative"} |
| 289 | debits += dr |
| 290 | credits += cr |
| 291 | normalized.append({ |
| 292 | "account_id": str(spec.get("account_id") or ""), |
| 293 | "debit_cents": str(dr), |
| 294 | "credit_cents": str(cr), |
| 295 | "memo": str(spec.get("memo") or ""), |
| 296 | "entity_id": str(spec.get("entity_id") or entity_id or ""), |
| 297 | }) |
| 298 | |
| 299 | if generated_from: |
| 300 | existing = _scan_provenance(base_dir, generated_from) |
| 301 | if existing is _UNREADABLE: |
| 302 | return {"ok": True, "skipped": "books unreadable; not risking a duplicate"} |
| 303 | if existing is not None: |
| 304 | return {"ok": True, "skipped": f"already composed: {generated_from}", |
| 305 | "journal_id": existing.get("id"), |
| 306 | "posted": existing.get("status") == "posted"} |
| 307 | |
| 308 | if not normalized or (debits == 0 and credits == 0): |
| 309 | return {"ok": True, "skipped": "zero amount"} |
| 310 | |
| 311 | journal_id = object_ids.new_uuid4() |
| 312 | journal = { |
| 313 | "id": journal_id, |
| 314 | "date": date or "", |
| 315 | "description": description or "", |
| 316 | "status": "draft", |
| 317 | "generated_from": generated_from or "", |
| 318 | "kind": kind, |
| 319 | "owner_id": owner_id or "", |
| 320 | } |
| 321 | if entity_id: |
| 322 | journal["entity_id"] = entity_id |
| 323 | object_records.create_collection_record( |
| 324 | "fin_journals", journal, base_dir=base_dir, actor=actor, |
| 325 | allow_computed_submission=False, |
| 326 | ) |
| 327 | for spec in normalized: |
| 328 | line = { |
| 329 | "id": object_ids.new_uuid4(), |
| 330 | "journal_id": journal_id, |
| 331 | "account_id": spec["account_id"], |
| 332 | "debit_cents": spec["debit_cents"], |
| 333 | "credit_cents": spec["credit_cents"], |
| 334 | "owner_id": owner_id or "", |
| 335 | } |
| 336 | if spec["memo"]: |
| 337 | line["memo"] = spec["memo"] |
| 338 | if spec["entity_id"]: |
| 339 | line["entity_id"] = spec["entity_id"] |
| 340 | object_records.create_collection_record( |
| 341 | "fin_journal_lines", line, base_dir=base_dir, actor=actor |
| 342 | ) |
| 343 | |
| 344 | # Balanced by construction; verify by RE-READING what actually landed |
| 345 | # before posting (a failed line write must never post a lopsided entry). |
| 346 | landed = journal_totals(journal_id, base_dir=base_dir) |
| 347 | if (post and landed["is_balanced"] and landed["total_debits_cents"] > 0): |
| 348 | object_records.update_collection_record( |
| 349 | "fin_journals", journal_id, {"status": "posted"}, |
| 350 | base_dir=base_dir, actor=actor, |
| 351 | ) |
| 352 | return {"ok": True, "journal_id": journal_id, "posted": True} |
| 353 | note = ("left draft: did not balance" if not landed["is_balanced"] |
| 354 | else "left draft: post not requested") |
| 355 | return {"ok": True, "journal_id": journal_id, "posted": False, "note": note} |
| 356 | |
| 357 | |
| 358 | def _date_in_range(date_value: Any, start: str, end: str) -> bool: |
| 359 | """True when a journal's date string falls within the closed range |
| 360 | [start, end], where either bound blank means "unbounded on that side" |
| 361 | -- the same convention the page-level query params (?start=&end=& |
| 362 | as_of=) use: an unset param means "don't filter this side", not some |
| 363 | sentinel date. |
| 364 | |
| 365 | Dates are stored as plain ISO 8601 strings (fin_journals.date, schema |
| 366 | type "date"), and ISO 8601 dates sort correctly as plain strings, so |
| 367 | this is a string comparison, never a date-parsing exercise -- the |
| 368 | same "don't reach for machinery you don't need" posture the rest of |
| 369 | this module takes with money (Decimal only where floor-rounding |
| 370 | actually matters, plain int/str everywhere else). A journal with a |
| 371 | blank date (should not happen -- the schema marks it required, but a |
| 372 | hand-edited TSV row or a partially-written draft can still produce |
| 373 | one) sorts before every real date string and is therefore excluded by |
| 374 | any non-blank start, which is the conservative choice for a report: |
| 375 | an ambiguous-dated entry should not silently count as "in period" |
| 376 | just because blank comes first alphabetically. |
| 377 | """ |
| 378 | date_text = str(date_value or "") |
| 379 | if start and date_text < start: |
| 380 | return False |
| 381 | if end and date_text > end: |
| 382 | return False |
| 383 | return True |
| 384 | |
| 385 | |
| 386 | def _posted_journal_ids( |
| 387 | journals: list[dict[str, Any]], *, owner: str | None, entity_id: str, start: str, end: str, |
| 388 | ) -> set[str]: |
| 389 | """Shared posted+owner+entity+date filter for profit_and_loss/balance_sheet. |
| 390 | |
| 391 | Factored out of trial_balance's inline version (which has no date or |
| 392 | entity filter -- it folds every posted journal an owner has ever |
| 393 | made) because both statements below need the identical four-way |
| 394 | filter and a subtle bug in one copy silently disagreeing with the |
| 395 | other would be far worse here than in trial_balance's simpler case: |
| 396 | the balance sheet's whole reason to exist is to say, loudly, when the |
| 397 | ledger disagrees with itself. |
| 398 | """ |
| 399 | return { |
| 400 | journal.get("id") |
| 401 | for journal in journals |
| 402 | if journal.get("status") == _STATUS_POSTED |
| 403 | and (owner is None or journal.get("owner_id") == owner) |
| 404 | and (not entity_id or journal.get("entity_id") == entity_id) |
| 405 | and _date_in_range(journal.get("date"), start, end) |
| 406 | } |
| 407 | |
| 408 | |
| 409 | def _fold_account_totals( |
| 410 | posted_journal_ids: set[str], *, base_dir, roots, |
| 411 | ) -> dict[str, list[int]]: |
| 412 | """Fold fin_journal_lines into {account_id: [debit_cents, credit_cents]} |
| 413 | for exactly the given set of already-filtered posted journal ids. |
| 414 | Lines with no account_id are skipped (same defensive posture as |
| 415 | trial_balance -- a hand-edited or partially-written row should not |
| 416 | crash a report). Returns an empty dict, not an error, when the id set |
| 417 | or the resulting totals are empty -- an empty ledger is a normal |
| 418 | input to a report, not a failure. |
| 419 | """ |
| 420 | totals_by_account: dict[str, list[int]] = {} |
| 421 | if not posted_journal_ids: |
| 422 | return totals_by_account |
| 423 | lines = object_records.read_collection_records("fin_journal_lines", base_dir=base_dir, roots=roots) |
| 424 | for line in lines: |
| 425 | if line.get("journal_id") not in posted_journal_ids: |
| 426 | continue |
| 427 | account_id = line.get("account_id") |
| 428 | if not account_id: |
| 429 | continue |
| 430 | bucket = totals_by_account.setdefault(account_id, [0, 0]) |
| 431 | bucket[0] += _to_cents(line.get("debit_cents")) |
| 432 | bucket[1] += _to_cents(line.get("credit_cents")) |
| 433 | return totals_by_account |
| 434 | |
| 435 | |
| 436 | def profit_and_loss( |
| 437 | *, |
| 438 | base_dir: Path | str = DEFAULT_DATA_DIR, |
| 439 | owner: str | None = None, |
| 440 | roots: Iterable[Path] | None = None, |
| 441 | start: str = "", |
| 442 | end: str = "", |
| 443 | entity_id: str = "", |
| 444 | ) -> dict[str, Any]: |
| 445 | """Income statement: income and expense accounts, folded over POSTED |
| 446 | journals dated within [start, end] (either bound blank = unbounded). |
| 447 | |
| 448 | Sign convention -- the one thing a P&L must get exactly right, since |
| 449 | getting it backwards silently reports a profit as a loss and vice |
| 450 | versa: income accounts are CREDIT-normal (a sale credits revenue), so |
| 451 | each income row's amount_cents is (credit_total - debit_total) for |
| 452 | that account. Expense accounts are DEBIT-normal (a purchase debits |
| 453 | the expense), so each expense row's amount_cents is (debit_total - |
| 454 | credit_total). Both are reported as the account's activity in its |
| 455 | OWN natural direction -- a normal, healthy account of either type |
| 456 | therefore shows a positive amount_cents; a negative amount_cents is |
| 457 | not a bug, it is the report correctly saying that account ran |
| 458 | contra to its own type (e.g. a revenue account with more debits than |
| 459 | credits, from refunds booked straight to it rather than to a contra- |
| 460 | revenue account -- this package has no contra-account convention, so |
| 461 | that is exactly what a heavy refund period looks like here). |
| 462 | |
| 463 | owner, when given, restricts the fold to journals owned by that user |
| 464 | -- the same non-negotiable contract trial_balance() documents: this |
| 465 | function reads collections directly via object_records and is NOT |
| 466 | subject to permissions/rules.json's row_filter the way the HTTP |
| 467 | /collections/* API is, so any caller serving one user's data MUST |
| 468 | pass owner=<that user's id>. entity_id, when given, additionally |
| 469 | restricts to journals tagged with that entity (65 multi-entity); |
| 470 | blank means "don't filter by entity" -- the same posture as a plain |
| 471 | text field with no relation enforcement elsewhere in this schema. |
| 472 | |
| 473 | Returns {"period": {"start", "end"}, "income": [rows], "expenses": |
| 474 | [rows], "total_income_cents", "total_expenses_cents", |
| 475 | "net_income_cents"} where each row is {"account_id", "account_name", |
| 476 | "account_code", "amount_cents"}. net_income_cents is |
| 477 | total_income_cents - total_expenses_cents (a negative value is a net |
| 478 | loss for the period, reported as-is, never clamped to zero). Accounts |
| 479 | with zero net posted activity in the period are omitted, matching |
| 480 | trial_balance's "fold over lines, not an enumeration of the chart of |
| 481 | accounts" posture. Rows are sorted by account code then name. An |
| 482 | empty or nonexistent ledger returns all-zero totals and empty row |
| 483 | lists rather than raising. |
| 484 | """ |
| 485 | journals = object_records.read_collection_records("fin_journals", base_dir=base_dir, roots=roots) |
| 486 | posted_journal_ids = _posted_journal_ids( |
| 487 | journals, owner=owner, entity_id=entity_id, start=start, end=end) |
| 488 | |
| 489 | period = {"start": start, "end": end} |
| 490 | empty = { |
| 491 | "period": period, |
| 492 | "income": [], |
| 493 | "expenses": [], |
| 494 | "total_income_cents": 0, |
| 495 | "total_expenses_cents": 0, |
| 496 | "net_income_cents": 0, |
| 497 | } |
| 498 | if not posted_journal_ids: |
| 499 | return empty |
| 500 | |
| 501 | totals_by_account = _fold_account_totals(posted_journal_ids, base_dir=base_dir, roots=roots) |
| 502 | if not totals_by_account: |
| 503 | return empty |
| 504 | |
| 505 | accounts_by_id = { |
| 506 | account.get("id"): account |
| 507 | for account in object_records.read_collection_records("fin_accounts", base_dir=base_dir, roots=roots) |
| 508 | } |
| 509 | |
| 510 | income_rows: list[dict[str, Any]] = [] |
| 511 | expense_rows: list[dict[str, Any]] = [] |
| 512 | total_income_cents = 0 |
| 513 | total_expenses_cents = 0 |
| 514 | for account_id, (debit_total_cents, credit_total_cents) in totals_by_account.items(): |
| 515 | account = accounts_by_id.get(account_id, {}) |
| 516 | account_type = account.get("account_type", "") |
| 517 | row = { |
| 518 | "account_id": account_id, |
| 519 | "account_name": account.get("name", ""), |
| 520 | "account_code": account.get("code", ""), |
| 521 | } |
| 522 | if account_type == "income": |
| 523 | amount_cents = credit_total_cents - debit_total_cents |
| 524 | income_rows.append({**row, "amount_cents": amount_cents}) |
| 525 | total_income_cents += amount_cents |
| 526 | elif account_type == "expense": |
| 527 | amount_cents = debit_total_cents - credit_total_cents |
| 528 | expense_rows.append({**row, "amount_cents": amount_cents}) |
| 529 | total_expenses_cents += amount_cents |
| 530 | # asset/liability/equity accounts don't belong on a P&L at all -- |
| 531 | # skipped here, not an error; they surface on balance_sheet(). |
| 532 | |
| 533 | def _sort_key(entry: dict[str, Any]) -> tuple[str, str]: |
| 534 | return (entry["account_code"], entry["account_name"]) |
| 535 | |
| 536 | income_rows.sort(key=_sort_key) |
| 537 | expense_rows.sort(key=_sort_key) |
| 538 | |
| 539 | return { |
| 540 | "period": period, |
| 541 | "income": income_rows, |
| 542 | "expenses": expense_rows, |
| 543 | "total_income_cents": total_income_cents, |
| 544 | "total_expenses_cents": total_expenses_cents, |
| 545 | "net_income_cents": total_income_cents - total_expenses_cents, |
| 546 | } |
| 547 | |
| 548 | |
| 549 | def balance_sheet( |
| 550 | *, |
| 551 | base_dir: Path | str = DEFAULT_DATA_DIR, |
| 552 | owner: str | None = None, |
| 553 | roots: Iterable[Path] | None = None, |
| 554 | as_of: str = "", |
| 555 | entity_id: str = "", |
| 556 | ) -> dict[str, Any]: |
| 557 | """Balance sheet: assets, liabilities, and equity as of a date (blank |
| 558 | as_of = every posted journal, no upper bound). |
| 559 | |
| 560 | Sign convention: asset accounts are DEBIT-normal, so each asset row's |
| 561 | amount_cents is (debit_total - credit_total). Liability and equity |
| 562 | accounts are CREDIT-normal, so each of their rows is (credit_total - |
| 563 | debit_total). Same "own natural direction" reporting posture as |
| 564 | profit_and_loss() -- a negative amount_cents means that account ran |
| 565 | contra to its own type, which is reported plainly rather than hidden. |
| 566 | |
| 567 | CRITICAL, and the reason this docstring is long: the fundamental |
| 568 | accounting equation (assets == liabilities + equity) only holds once |
| 569 | the CURRENT period's net income is folded into equity. A stored |
| 570 | equity account only accumulates what has actually been posted TO it |
| 571 | (owner contributions, prior retained earnings, draws) -- nothing in |
| 572 | this package's posting flow ever sweeps a period's income and |
| 573 | expense accounts into an equity account, because that sweep is a |
| 574 | year-end CLOSE, and fin_journals.kind's "closing" enum value is |
| 575 | reserved for exactly that close and is deliberately not built yet |
| 576 | (see this module's and dbbasic-package.json's Deferred notes). Until |
| 577 | a real close exists, this function computes net income itself (via |
| 578 | profit_and_loss() over every posted journal up to as_of, no lower |
| 579 | bound -- life-to-date earnings, not just one period) and adds it as |
| 580 | ONE SYNTHETIC LINE in the equity section, labelled "Current period |
| 581 | earnings" with an empty account_id/account_code. This line is |
| 582 | DERIVED, not a stored fin_accounts row -- it does not appear in the |
| 583 | chart of accounts, has no journal lines of its own, and would |
| 584 | disappear (correctly) the moment a real close journal swept income |
| 585 | and expense into retained earnings and this function were pointed at |
| 586 | books that had already been closed. Treat it as a plug that keeps |
| 587 | the equation honest pre-close, not as a feature to build further |
| 588 | reports on top of. |
| 589 | |
| 590 | owner and entity_id have the same contract as profit_and_loss(): pass |
| 591 | owner=<user id> to scope to that user's books (this function is NOT |
| 592 | subject to permissions/rules.json's row_filter, same non-negotiable |
| 593 | posture as trial_balance() and profit_and_loss()); entity_id, when |
| 594 | given, additionally restricts to one set of books. |
| 595 | |
| 596 | Returns {"as_of", "assets": [rows], "liabilities": [rows], "equity": |
| 597 | [rows] (stored equity accounts, then the synthetic earnings line |
| 598 | last), "total_assets_cents", "total_liabilities_cents", |
| 599 | "total_equity_cents" (includes the synthetic line), |
| 600 | "balances": bool, "difference_cents"}. "balances" is exactly |
| 601 | total_assets_cents == total_liabilities_cents + total_equity_cents. |
| 602 | difference_cents is total_assets_cents - (total_liabilities_cents + |
| 603 | total_equity_cents), signed and exact -- NEVER rounded, abs()'d, or |
| 604 | silently swallowed. THIS IS THE SINGLE MOST VALUABLE THING THIS |
| 605 | FUNCTION DOES: a balance sheet that does not balance means the ledger |
| 606 | itself is broken (a line written with only one side, a partial write |
| 607 | that lost its balancing leg, a bypass of compose_posted_journal's |
| 608 | re-verify-before-post gate) and callers MUST surface "balances" and |
| 609 | "difference_cents" prominently rather than rendering the statement as |
| 610 | if everything is fine -- silence here is the failure mode this |
| 611 | function exists to prevent. |
| 612 | """ |
| 613 | journals = object_records.read_collection_records("fin_journals", base_dir=base_dir, roots=roots) |
| 614 | posted_journal_ids = _posted_journal_ids( |
| 615 | journals, owner=owner, entity_id=entity_id, start="", end=as_of) |
| 616 | |
| 617 | totals_by_account = _fold_account_totals(posted_journal_ids, base_dir=base_dir, roots=roots) |
| 618 | |
| 619 | accounts_by_id = { |
| 620 | account.get("id"): account |
| 621 | for account in object_records.read_collection_records("fin_accounts", base_dir=base_dir, roots=roots) |
| 622 | } |
| 623 | |
| 624 | assets: list[dict[str, Any]] = [] |
| 625 | liabilities: list[dict[str, Any]] = [] |
| 626 | equity: list[dict[str, Any]] = [] |
| 627 | total_assets_cents = 0 |
| 628 | total_liabilities_cents = 0 |
| 629 | total_equity_cents = 0 |
| 630 | for account_id, (debit_total_cents, credit_total_cents) in totals_by_account.items(): |
| 631 | account = accounts_by_id.get(account_id, {}) |
| 632 | account_type = account.get("account_type", "") |
| 633 | row = { |
| 634 | "account_id": account_id, |
| 635 | "account_name": account.get("name", ""), |
| 636 | "account_code": account.get("code", ""), |
| 637 | } |
| 638 | if account_type == "asset": |
| 639 | amount_cents = debit_total_cents - credit_total_cents |
| 640 | assets.append({**row, "amount_cents": amount_cents}) |
| 641 | total_assets_cents += amount_cents |
| 642 | elif account_type == "liability": |
| 643 | amount_cents = credit_total_cents - debit_total_cents |
| 644 | liabilities.append({**row, "amount_cents": amount_cents}) |
| 645 | total_liabilities_cents += amount_cents |
| 646 | elif account_type == "equity": |
| 647 | amount_cents = credit_total_cents - debit_total_cents |
| 648 | equity.append({**row, "amount_cents": amount_cents}) |
| 649 | total_equity_cents += amount_cents |
| 650 | # income/expense accounts don't get their own balance-sheet row -- |
| 651 | # their life-to-date net folds into the synthetic earnings line |
| 652 | # below instead, per this function's docstring. |
| 653 | |
| 654 | def _sort_key(entry: dict[str, Any]) -> tuple[str, str]: |
| 655 | return (entry["account_code"], entry["account_name"]) |
| 656 | |
| 657 | assets.sort(key=_sort_key) |
| 658 | liabilities.sort(key=_sort_key) |
| 659 | equity.sort(key=_sort_key) |
| 660 | |
| 661 | pl = profit_and_loss(base_dir=base_dir, owner=owner, roots=roots, start="", end=as_of, entity_id=entity_id) |
| 662 | net_income_cents = pl["net_income_cents"] |
| 663 | equity.append({ |
| 664 | "account_id": "", |
| 665 | "account_name": "Current period earnings", |
| 666 | "account_code": "", |
| 667 | "amount_cents": net_income_cents, |
| 668 | }) |
| 669 | total_equity_cents += net_income_cents |
| 670 | |
| 671 | difference_cents = total_assets_cents - (total_liabilities_cents + total_equity_cents) |
| 672 | |
| 673 | return { |
| 674 | "as_of": as_of, |
| 675 | "assets": assets, |
| 676 | "liabilities": liabilities, |
| 677 | "equity": equity, |
| 678 | "total_assets_cents": total_assets_cents, |
| 679 | "total_liabilities_cents": total_liabilities_cents, |
| 680 | "total_equity_cents": total_equity_cents, |
| 681 | "balances": difference_cents == 0, |
| 682 | "difference_cents": difference_cents, |
| 683 | } |
| 684 | |
| 685 | CLOSED_PERIODS_COLLECTION = "fin_closed_periods" |
| 686 | |
| 687 | |
| 688 | def closed_period_for(date_text, *, base_dir: Path | str = DEFAULT_DATA_DIR, |
| 689 | owner_id: str = "", entity_id: str = "") -> dict | None: |
| 690 | """The closed period covering a date, if any -- the period-lock check. |
| 691 | |
| 692 | A closed period is the line under settled books: from start_date |
| 693 | through end_date INCLUSIVE, nothing may be written, edited, or posted |
| 694 | (the predecessor system modeled exactly this control -- reconciled |
| 695 | against a private source audit, not part of this repo -- and it was |
| 696 | the one it had that this system lacked). Scoping mirrors how |
| 697 | books are scoped everywhere else: a period closes ONE owner's books, |
| 698 | and an entity-specific period closes only that entity's journals while |
| 699 | an entity-blank period closes the owner's unscoped journals. |
| 700 | |
| 701 | Plain string comparison on ISO dates, same as every date filter in |
| 702 | this module. A journal with no date cannot be inside a closed period |
| 703 | -- validation owns whether dates are required, not this check. |
| 704 | """ |
| 705 | date = str(date_text or "").strip()[:10] |
| 706 | if not date: |
| 707 | return None |
| 708 | try: |
| 709 | rows = object_records.read_collection_records( |
| 710 | CLOSED_PERIODS_COLLECTION, base_dir=base_dir) |
| 711 | except Exception: |
| 712 | return None |
| 713 | for row in rows: |
| 714 | if owner_id and row.get("owner_id") and row["owner_id"] != owner_id: |
| 715 | continue |
| 716 | row_entity = str(row.get("entity_id") or "") |
| 717 | if row_entity != str(entity_id or ""): |
| 718 | continue |
| 719 | start = str(row.get("start_date") or "")[:10] |
| 720 | end = str(row.get("end_date") or "")[:10] |
| 721 | if start and end and start <= date <= end: |
| 722 | return row |
| 723 | return None |