Workbench live on-hand and on-PO — technical specification

Live OH / On PO (app/live_qty.py)

Dave, 2026-09-06: the workbench must show on-hand and on-PO as at the moment it is run, not last night's sync.

What runs on every workbench load

1. On hand — select Id, QtyOnHand from Item where Type='Inventory', the whole catalogue (1,480 items), written into items.qty_on_hand in one execute_values statement. ~1.1s.
2. On PO — QuickBooks Change Data Capture (GET /v3/company/{realm}/cdc, entities=PurchaseOrder&changedSince=<cursor>), applied to qb_po_line delete-then-insert per PO. ~0.5s. Cursor lives in qb_cdc_cursor.

Total ~1.7s and flat in the number of ROs — scoping the item pull to the SKUs on ROs was tried first and abandoned: hundreds of RO SKUs are coming once service tickets load, and the whole catalogue costs the same.

Things that will bite whoever changes this

• QuickBooks has no on-PO figure on the item. The Item entity carries QtyOnHand and nothing else about supply (fields: Active, AssetAccountRef, Description, ExpenseAccountRef, Id, InvStartDate, Name, PrefVendorRef, PurchaseCost, QtyOnHand, Sku, TrackQtyOnHand, Type, UnitPrice). The "Quantity on PO" on the QB item screen is computed by QuickBooks from open PO lines; there is nothing to read. qb_po_line is our equivalent, which is why it has to be kept fresh.
• POStatus is not queryable. Filter it client-side after pulling by TxnDate.
• Page size matters more than anything else here. 1,480 items at maxresults 200 is eight round trips and 5.4 seconds; at 1,000 it is two trips and 1.1 seconds. PAGE = 1000.
• CDC's changedSince rejects +0000. It must be …T02:00:59-00:00.
• CDC only reaches back 30 days. With no cursor, or a cursor older than CDC_MAX_DAYS (25), the code truncates qb_po_line and does the full 16–21s pull instead, because a missed window cannot be recovered from CDC.
• refresh() never raises. A QuickBooks outage leaves the grid on last night's numbers with the stamp reading "from last night's sync — QuickBooks did not answer", rather than an error page. Keep it that way.
• Times are stamped in America/Chicago; the droplet runs UTC.

The nightly 02:45 sync_qb_open_pos.py still runs and is still the safety net.

Ver 2 - 2026-09-07 - refresh throttle

live_qty.refresh(ro_conn, im2_conn, force=False) re-uses the previous snapshot when it is younger than MIN_AGE_SECS (120) and returns reused=True with the original timestamp, so the stamp never claims a pull that did not happen. State lives in table qb_live_refresh (single row, created on first use). force=True always pulls.

The workbench being slow was not QuickBooks. company_holidays() was called once per row and each call with no connection to hand opened its own database connection to read Settings - 102 rows cost 4.3s of a 5s page. Names and resolved holiday dates are now cached in po.py for 60s (_HOLIDAY_CACHE). The page went from ~7.4s to ~0.4s; the live QuickBooks pull is ~1.9s and only on a cold snapshot.

Ver 2 — 2026-09-07 — refresh throttle

re-uses the previous snapshot when it is younger than (120) and returns with the original timestamp, so the stamp never claims a pull that did not happen. State lives in (single row, created on first use). always pulls.

The workbench being slow was not QuickBooks. was called once per row and each call opened its own database connection to read Settings — 102 rows cost 4.3s of the 5s page. Names and resolved holiday dates are now cached in for 60s (). Page went from ~7.4s to ~0.4s; the live pull itself is ~1.9s and only on a cold snapshot.