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_stock.py · 15.1 KB · 371 lines
| 1 | """Derived stock levels, folded from the immutable stock_moves append log. |
| 2 | |
| 3 | This module belongs to the STOCK slice of packages/app-catalog (locations + |
| 4 | stock_moves), but lives here at the repo root next to the other shared |
| 5 | object_*.py modules (object_records.py, object_record_changes.py, ...) |
| 6 | rather than inside packages/app-catalog/ itself. That placement is |
| 7 | deliberate, not a convenience shortcut: package installs only ever copy the |
| 8 | artifacts a manifest declares (objects/schemas/permissions/seed -- see |
| 9 | object_packages.install_package) into the runtime's object/data roots, so a |
| 10 | plain Python module sitting inside a package directory would never actually |
| 11 | land anywhere importable at runtime. Every object in this codebase that |
| 12 | needs shared logic imports a bare top-level module the same way |
| 13 | packages/app-orders/objects/system/order_totals.py imports object_records |
| 14 | and object_record_changes; this module follows that exact precedent so |
| 15 | packages/app-catalog/objects/site/stock.py can `import object_stock` and |
| 16 | have it resolve. |
| 17 | |
| 18 | Source model (reconciled against a private predecessor-system catalog |
| 19 | audit, not part of this repo): a StockMove is immutable -- product_id moves |
| 20 | quantity from from_location_id to to_location_id, once, forever. Current |
| 21 | stock is never a stored column anywhere; it is always the fold of every |
| 22 | move for a product (docs/append-only-storage-design.md's own "stock_moves |
| 23 | is the textbook append-mode collection" framing, and the same source |
| 24 | audit's own note: "StockMove (immutable, levels DERIVED not stored)"). |
| 25 | This module is that fold, nothing more. |
| 26 | |
| 27 | Quantity discipline: every quantity here is a Decimal, never a bare float. |
| 28 | quantity is a count/measure, not currency -- may be fractional (e.g. 2.5 |
| 29 | kg) -- but Decimal arithmetic is still required so a fractional quantity |
| 30 | can never introduce binary-float rounding error, the same "count/measure, |
| 31 | not money" exception app-orders' order_lines.quantity documents. |
| 32 | |
| 33 | Blank from_location_id/to_location_id ("external origin"/"external |
| 34 | destination" in the source model -- e.g. a purchase with no supplier |
| 35 | location on file, or a sale with no customer location on file) simply have |
| 36 | nothing to accumulate at: the fold below only touches a (product, |
| 37 | location) key when the id is non-empty. The paired real location's balance |
| 38 | still moves normally either way. |
| 39 | |
| 40 | Virtual locations (location_type customer/supplier/virtual) hold balances |
| 41 | like any other location -- a stock move into a virtual "customer" location |
| 42 | models stock that left the business, for instance -- but they are excluded |
| 43 | from total_quantity's on-hand figure, matching the source model's own |
| 44 | total_quantity semantics: only real, physical locations count as "on |
| 45 | hand." |
| 46 | |
| 47 | Negative levels: no floor or clamp is applied anywhere in this module. A |
| 48 | location that has shipped out more than it ever received folds to a |
| 49 | negative Decimal and is left visible rather than floored to zero. The |
| 50 | predecessor audit's own business-records notes admit stock/balance |
| 51 | integrity is enforced nowhere at write time ("WEAK: ... balance NOT |
| 52 | enforced at post" -- said there of the ledger, but the same honesty |
| 53 | applies here); inventing a clamp the append log itself does not assert |
| 54 | would hide a real data-quality signal (overselling) rather than surface |
| 55 | it. This is the documented choice where the source audit does not spell |
| 56 | out clamp-vs-negative behavior explicitly. |
| 57 | |
| 58 | Scale note: every function here is a pure O(len(stock_moves)) fold over |
| 59 | the full log on every call -- no materialized rollup, no cache. That is |
| 60 | the right, honest trade for a first slice at this collection's expected |
| 61 | scale (append-mode collections read fine up to the thresholds documented |
| 62 | in docs/storage-modes.md). A materialized/incremental rollup -- recomputed |
| 63 | only from moves newer than the last snapshot, or a per-(product,location) |
| 64 | sidecar -- is future work, the same trade docs/append-only-storage-design.md |
| 65 | already calls out for any derived index over an append log ("Secondary |
| 66 | sidecar indexes are future work, only when a named workload demands one"). |
| 67 | """ |
| 68 | from __future__ import annotations |
| 69 | |
| 70 | from decimal import Decimal, InvalidOperation, ROUND_HALF_UP |
| 71 | from pathlib import Path |
| 72 | from typing import Any, Iterable |
| 73 | |
| 74 | import object_records |
| 75 | import object_schemas |
| 76 | |
| 77 | STOCK_MOVES_COLLECTION = "stock_moves" |
| 78 | LOCATIONS_COLLECTION = "locations" |
| 79 | |
| 80 | # Location types that are counterparties, not physical inventory -- |
| 81 | # excluded from total_quantity's on-hand figure. Matches locations.json's |
| 82 | # location_type enum tail (customer, supplier, virtual). |
| 83 | VIRTUAL_LOCATION_TYPES = frozenset({"customer", "supplier", "virtual"}) |
| 84 | |
| 85 | |
| 86 | def _decimal(value: Any) -> Decimal: |
| 87 | """Parse a stored quantity as Decimal; blank/invalid -> 0.""" |
| 88 | text = str(value if value is not None else "").strip() |
| 89 | if not text: |
| 90 | return Decimal("0") |
| 91 | try: |
| 92 | return Decimal(text) |
| 93 | except InvalidOperation: |
| 94 | return Decimal("0") |
| 95 | |
| 96 | |
| 97 | def _stock_moves( |
| 98 | *, |
| 99 | base_dir: Path | str, |
| 100 | owner: str | None = None, |
| 101 | roots: Iterable[Path] | None = None, |
| 102 | ) -> list[dict[str, str]]: |
| 103 | moves = object_records.read_collection_records( |
| 104 | STOCK_MOVES_COLLECTION, base_dir=base_dir, roots=roots |
| 105 | ) |
| 106 | if owner is not None: |
| 107 | moves = [move for move in moves if move.get("owner_id") == owner] |
| 108 | return moves |
| 109 | |
| 110 | |
| 111 | def _locations( |
| 112 | *, |
| 113 | base_dir: Path | str, |
| 114 | owner: str | None = None, |
| 115 | roots: Iterable[Path] | None = None, |
| 116 | ) -> list[dict[str, str]]: |
| 117 | """Return locations records, or [] when the collection isn't known yet. |
| 118 | |
| 119 | Defensive only: in every real install locations and stock_moves ship |
| 120 | together in the same package, but a caller could point base_dir at a |
| 121 | data dir where locations was never installed (e.g. an isolated test of |
| 122 | stock_moves alone) -- rather than raising, virtual-location resolution |
| 123 | degrades to "nothing is virtual" instead of failing the whole fold. |
| 124 | """ |
| 125 | try: |
| 126 | locations = object_records.read_collection_records( |
| 127 | LOCATIONS_COLLECTION, base_dir=base_dir, roots=roots |
| 128 | ) |
| 129 | except (object_schemas.SchemaNotFoundError, LookupError, OSError): |
| 130 | return [] |
| 131 | if owner is not None: |
| 132 | locations = [loc for loc in locations if loc.get("owner_id") == owner] |
| 133 | return locations |
| 134 | |
| 135 | |
| 136 | def virtual_location_ids( |
| 137 | *, |
| 138 | base_dir: Path | str, |
| 139 | owner: str | None = None, |
| 140 | roots: Iterable[Path] | None = None, |
| 141 | ) -> set[str]: |
| 142 | """Return the set of location ids whose location_type is virtual |
| 143 | (customer, supplier, or virtual) -- the ids total_quantity excludes. |
| 144 | """ |
| 145 | return { |
| 146 | loc["id"] |
| 147 | for loc in _locations(base_dir=base_dir, owner=owner, roots=roots) |
| 148 | if loc.get("id") and loc.get("location_type") in VIRTUAL_LOCATION_TYPES |
| 149 | } |
| 150 | |
| 151 | |
| 152 | def _fold_levels(moves: Iterable[dict[str, str]]) -> dict[tuple[str, str], Decimal]: |
| 153 | """Fold immutable stock_moves rows into net (product_id, location_id) deltas. |
| 154 | |
| 155 | A move INTO a location adds quantity; a move OUT OF a location |
| 156 | subtracts it. A blank from/to id has no location to accumulate at and |
| 157 | is simply skipped for that side of the move. |
| 158 | """ |
| 159 | levels: dict[tuple[str, str], Decimal] = {} |
| 160 | for move in moves: |
| 161 | product_id = move.get("product_id") |
| 162 | if not product_id: |
| 163 | continue |
| 164 | quantity = _decimal(move.get("quantity")) |
| 165 | |
| 166 | to_location_id = move.get("to_location_id") |
| 167 | if to_location_id: |
| 168 | key = (product_id, to_location_id) |
| 169 | levels[key] = levels.get(key, Decimal("0")) + quantity |
| 170 | |
| 171 | from_location_id = move.get("from_location_id") |
| 172 | if from_location_id: |
| 173 | key = (product_id, from_location_id) |
| 174 | levels[key] = levels.get(key, Decimal("0")) - quantity |
| 175 | |
| 176 | return levels |
| 177 | |
| 178 | |
| 179 | def quantity_at_location( |
| 180 | product_id: str, |
| 181 | location_id: str, |
| 182 | *, |
| 183 | base_dir: Path | str, |
| 184 | owner: str | None = None, |
| 185 | roots: Iterable[Path] | None = None, |
| 186 | ) -> Decimal: |
| 187 | """Return the net on-hand quantity of one product at one location. |
| 188 | |
| 189 | Sum of quantity for every move INTO location_id minus every move OUT |
| 190 | OF it, for product_id only. A blank location_id (the "external |
| 191 | origin/destination" sentinel -- see module docstring) always returns 0: |
| 192 | it names no real location to hold a balance at. |
| 193 | """ |
| 194 | if not location_id: |
| 195 | return Decimal("0") |
| 196 | levels = _fold_levels(_stock_moves(base_dir=base_dir, owner=owner, roots=roots)) |
| 197 | return levels.get((product_id, location_id), Decimal("0")) |
| 198 | |
| 199 | |
| 200 | def total_quantity( |
| 201 | product_id: str, |
| 202 | *, |
| 203 | base_dir: Path | str, |
| 204 | owner: str | None = None, |
| 205 | roots: Iterable[Path] | None = None, |
| 206 | ) -> Decimal: |
| 207 | """Return one product's total real on-hand quantity. |
| 208 | |
| 209 | Sums net quantity across every location the product's moves touch, |
| 210 | EXCLUDING virtual location types (customer/supplier/virtual) -- matches |
| 211 | the source model's total_quantity semantics: only physical locations |
| 212 | count as "on hand." A location id referenced by a move but no longer |
| 213 | present in the locations collection (e.g. deleted) is treated as real |
| 214 | (included), the conservative default when its type can no longer be |
| 215 | resolved. |
| 216 | """ |
| 217 | levels = _fold_levels(_stock_moves(base_dir=base_dir, owner=owner, roots=roots)) |
| 218 | virtual_ids = virtual_location_ids(base_dir=base_dir, owner=owner, roots=roots) |
| 219 | |
| 220 | total = Decimal("0") |
| 221 | for (moved_product_id, location_id), quantity in levels.items(): |
| 222 | if moved_product_id != product_id: |
| 223 | continue |
| 224 | if location_id in virtual_ids: |
| 225 | continue |
| 226 | total += quantity |
| 227 | return total |
| 228 | |
| 229 | |
| 230 | def stock_levels( |
| 231 | *, |
| 232 | base_dir: Path | str, |
| 233 | owner: str | None = None, |
| 234 | roots: Iterable[Path] | None = None, |
| 235 | ) -> dict[str, list[dict[str, str]]]: |
| 236 | """Return a page-ready summary: per-(product, location) net quantity, |
| 237 | and per-product on-hand totals (virtual locations excluded). |
| 238 | |
| 239 | One fold over stock_moves serves both halves of the summary, so this |
| 240 | is cheaper than calling quantity_at_location/total_quantity in a loop |
| 241 | per product. Quantities are returned as strings (str(Decimal(...))) -- |
| 242 | JSON- and TSV-safe, exact, never a float. |
| 243 | """ |
| 244 | moves = _stock_moves(base_dir=base_dir, owner=owner, roots=roots) |
| 245 | levels = _fold_levels(moves) |
| 246 | virtual_ids = virtual_location_ids(base_dir=base_dir, owner=owner, roots=roots) |
| 247 | |
| 248 | totals: dict[str, Decimal] = {} |
| 249 | level_rows = [] |
| 250 | for (product_id, location_id), quantity in sorted(levels.items()): |
| 251 | level_rows.append({ |
| 252 | "product_id": product_id, |
| 253 | "location_id": location_id, |
| 254 | "quantity": str(quantity), |
| 255 | }) |
| 256 | if location_id not in virtual_ids: |
| 257 | totals[product_id] = totals.get(product_id, Decimal("0")) + quantity |
| 258 | |
| 259 | total_rows = [ |
| 260 | {"product_id": product_id, "quantity": str(quantity)} |
| 261 | for product_id, quantity in sorted(totals.items()) |
| 262 | ] |
| 263 | |
| 264 | return {"levels": level_rows, "totals": total_rows} |
| 265 | |
| 266 | |
| 267 | # === valuation: what the goods cost, for COGS ================================== |
| 268 | # |
| 269 | # The stock fold above answers "how many"; this answers "at what cost", |
| 270 | # which is the prerequisite COGS-on-sale was blocked on. The method is |
| 271 | # WEIGHTED AVERAGE, and it is a decision rather than a default: |
| 272 | # |
| 273 | # FIFO would be more precise, and it is what a lot-tracked warehouse |
| 274 | # should use -- but it requires cost LAYERS (which units, from which |
| 275 | # receipt, are still on the shelf), and stock_moves has no lot dimension |
| 276 | # at all. Bolting layers on to satisfy one journal entry would be a |
| 277 | # storage change driven by an accounting report. LIFO is not permitted |
| 278 | # under IFRS and is a bad fit besides. Weighted average needs nothing the |
| 279 | # append log does not already carry, is accepted under both GAAP and |
| 280 | # IFRS, and is what most small shops actually run. |
| 281 | # |
| 282 | # MOVING average, specifically, folded in move order -- not the simpler |
| 283 | # "total spent / total ever bought". The two genuinely differ: buy 10 at |
| 284 | # 100, sell 10, buy 1 at 300, and the lifetime average says 118 while the |
| 285 | # moving average says 300, which is what the next sale actually costs. |
| 286 | # The lifetime version quietly reprices goods you no longer own. |
| 287 | # |
| 288 | # A depletion at an unknown average (nothing on hand) consumes nothing and |
| 289 | # says so by leaving the value alone; the caller sees None and must skip |
| 290 | # rather than guess, which is the whole point of the exercise. |
| 291 | |
| 292 | ACQUISITION_REASONS = frozenset({"purchase", "return"}) |
| 293 | |
| 294 | |
| 295 | def _move_order_key(move: dict) -> tuple: |
| 296 | """Chronological order, falling back to insertion order. |
| 297 | |
| 298 | occurred_at is the business date and may be blank or coarse (a plain |
| 299 | date), so created_at breaks ties -- two receipts on the same day must |
| 300 | still fold in the order they were recorded. |
| 301 | """ |
| 302 | return (str(move.get("occurred_at") or move.get("created_at") or ""), |
| 303 | str(move.get("created_at") or "")) |
| 304 | |
| 305 | |
| 306 | def weighted_average_cost_cents( |
| 307 | moves: Iterable[dict[str, str]], |
| 308 | product_id: str, |
| 309 | ) -> int | None: |
| 310 | """The moving weighted-average unit cost of one product, in cents. |
| 311 | |
| 312 | Returns None when there is nothing on hand or no acquisition ever |
| 313 | carried a cost -- there is no honest average to report, and a caller |
| 314 | that stamps one anyway has invented a valuation method by accident. |
| 315 | |
| 316 | Pure: takes rows, returns a number. No I/O, no clock. |
| 317 | """ |
| 318 | on_hand = Decimal("0") |
| 319 | value = Decimal("0") |
| 320 | |
| 321 | for move in sorted((m for m in moves if m.get("product_id") == product_id), |
| 322 | key=_move_order_key): |
| 323 | quantity = _decimal(move.get("quantity")) |
| 324 | if quantity <= 0: |
| 325 | continue |
| 326 | reason = str(move.get("reason") or "").strip() |
| 327 | cost = _decimal(move.get("unit_cost_cents")) |
| 328 | into = str(move.get("to_location_id") or "").strip() |
| 329 | out_of = str(move.get("from_location_id") or "").strip() |
| 330 | |
| 331 | # An acquisition is stock arriving from outside with a price on |
| 332 | # it. A transfer (both ends set) moves goods between shelves and |
| 333 | # must not re-average anything. |
| 334 | acquiring = (reason in ACQUISITION_REASONS or (into and not out_of)) |
| 335 | if acquiring and into and not out_of: |
| 336 | if cost > 0: |
| 337 | value += quantity * cost |
| 338 | on_hand += quantity |
| 339 | else: |
| 340 | # Free/uncosted stock still occupies the shelf, and |
| 341 | # pretending it does not would inflate the average for |
| 342 | # everything else on it. |
| 343 | on_hand += quantity |
| 344 | continue |
| 345 | |
| 346 | if out_of and not into: |
| 347 | # A depletion consumes stock at the average it was carried at, |
| 348 | # which leaves the average itself unchanged -- the defining |
| 349 | # property of weighted average. |
| 350 | if on_hand <= 0: |
| 351 | continue |
| 352 | unit = value / on_hand |
| 353 | consumed = min(quantity, on_hand) |
| 354 | value -= consumed * unit |
| 355 | on_hand -= consumed |
| 356 | |
| 357 | if on_hand <= 0 or value <= 0: |
| 358 | return None |
| 359 | return int((value / on_hand).quantize(Decimal("1"), rounding=ROUND_HALF_UP)) |
| 360 | |
| 361 | |
| 362 | def average_cost_for_product( |
| 363 | product_id: str, |
| 364 | *, |
| 365 | base_dir: Path | str = object_records.DEFAULT_DATA_DIR, |
| 366 | owner: str | None = None, |
| 367 | roots: Iterable[Path] | None = None, |
| 368 | ) -> int | None: |
| 369 | """weighted_average_cost_cents against stored moves.""" |
| 370 | return weighted_average_cost_cents( |
| 371 | _stock_moves(base_dir=base_dir, owner=owner, roots=roots), product_id) |