PO Alerts / change watch — technical specification

PO change watch (app/po_watch.py, P2 slice C)

Dave, 2026-09-06: alert when a PO or PO line an RO relies on is deleted, and when an RO line linked to a PO is deleted here — "CDC is the only foolproof way I can think of."

Read-only against QuickBooks. No creates, edits or deletes anywhere in this file.

How it runs

im2-po-watch.timer (systemd, hourly, RandomizedDelaySec=120, Persistent=true) → im2-po-watch.service → /opt/im2/venv/bin/python /opt/im2/app/po_watch.py. --dry-run prints and writes nothing. Cursor: qb_cdc_cursor entity PurchaseOrderWatch — deliberately separate from PurchaseOrder, which live_qty advances on every workbench load and would otherwise consume this watcher's window.

Detections

• QuickBooks → IM2 (scan_quickbooks): status == "Deleted" → PO deleted; POStatus == "Closed" → PO closed; our qb_po_line_id absent from the PO's lines → PO line removed.
• IM2 → QuickBooks: raised inside ro.delete_ro, not by the scan. ro_po_link has a foreign key onto ro_detail with NO ACTION, so deleting an RO with links would have failed on the constraint; delete_ro now writes the alert, deletes the links, then deletes the RO. po_watch.scan_ro_side remains as a safety net for rows removed straight in the database.

Gotchas

• {"ItemBasedExpenseLineDetail": {}} is falsy. Testing truthiness silently emptied the line-id set and killed the PO line removed detection. _line_ids uses in.
• A CDC stub carries no Line key at all; absent Line is not evidence a line was removed, so the check requires "Line" in po. Present-but-empty does alert.
• Hand-keyed links hold qb_po_id = 'PENDING:<doc>', so they are grouped under DOC:<number> and matched on DocNumber instead.
• If the QuickBooks half throws, the cursor is not advanced — the next run re-covers the window. A gap wider than CDC_MAX_DAYS is logged as a warning because deletes in that gap are unrecoverable; it does not pretend the scan was clean.
• Alerts de-duplicate on (type, PO number, order_no, line_key) while unacknowledged, so the hourly run is safe to repeat and a re-occurrence after sign-off raises again.
• No Slack notification (removed 2026-09-07, Dave: the menu/workbench count is prominent enough). announce() and the notify_slack import are gone from po_watch.py; the screen is the only delivery channel. Re-add on request.

Screens: GET /po/alerts (+ ?show=all), POST /po/alerts/{id}/ack in po_rel.py.

Ver 2 - 2026-09-07 - "PO not in QuickBooks"

Dave, while testing: "I've entered several fake POs for SKUs that don't exist in QB. What happens to those?" Nothing did. A hand-keyed or manually released link is stored as qb_po_id = 'PENDING:<doc>' and counts as supply on the workbench, and the nightly sync only ever matches links up - it never questions one. A typo'd or never-created PO number therefore suppressed a real requirement silently and forever.

po_watch.scan_pending(conn, qbo) now runs in the same hourly scan: every PENDING link older than PENDING_GRACE_HOURS (12) is looked up in QuickBooks by DocNumber, in chunks of 40, and anything QuickBooks does not have raises alert_type "PO not in QuickBooks". A failed lookup returns no alerts at all rather than reporting everything missing, so QuickBooks being down never floods /po/alerts.

Inventory rows are unaffected by design - their On PO is computed from live QuickBooks purchase orders, so a PO number that does not exist in QuickBooks contributes nothing.

Shared table sort (2026-09-07)

Column sorting is one implementation in templates/base.html (Dave, 2026-09-07: "all screens that have tables like this need sorts on the appropriate columns"). A table opts in with class="sorttable"; every thead th becomes sortable except those with class="nosort"/class="toggle", an empty heading, or a checkbox in the heading. Client-side only, over the rows already rendered. Details: tech/table_sorting.md.

Screen (Ver 2, 2026-09-07)

Dave, 2026-09-07: "let's put the alerts on a screen similar to the RO Import issues and let them resolve them from there with links to the RO and PO."

• GET /po/alerts?status=Open|Resolved|Ignored|All — po_rel.po_alerts, template po_alerts.html. ?show=all (Ver 1 link) still maps to status=All. po_release is left-joined on doc_number to supply the buy-list link; a hand-keyed PO number has no release row, so that link is simply absent.
• POST /po/alerts/{id}/resolve — form action=resolve|ignore, note. Writes acknowledged_at/by, note and the new po_alert.resolution column ('Resolved' | 'Ignored'). Guarded by can_write (level3 view-only) and by acknowledged_at is null, so a second close returns 404. POST /alerts/{id}/ack remains as a thin alias.
• po_rel.open_alert_count() — unresolved count, swallows its own exceptions and returns 0, because it renders inside unrelated screens:
• im2.logistics_menu substitutes <!--ALERTBADGE--> in menu.html (served from disk, so the badge cannot be baked into the file),
• po.workbench passes alert_count into po_workbench.html.

Migration: alter table po_alert add column if not exists resolution text — applied 2026-09-07. Rows closed under Ver 1 read as Resolved (the template falls back).

Stock Replenishment tiles removed from app/menu.html and the handheld menu (/var/www/im2docs/handheld.html, nginx alias for /handheld — note the copy in app/static/ is not the served one).

Closed-alert window (Ver 3, 2026-09-07)

Dave, 2026-09-07: "90 days is good with an older option." po_rel.CLOSED_WINDOW_DAYS = 90. GET /po/alerts adds older=0|1; when older is falsy the where-clause keeps acknowledged_at is null or acknowledged_at > now() - 90 days, so open alerts are never filtered by age. Chip counts use the same window and a second query supplies hidden (closed rows outside it) for the Show N older link. No purge — rows stay in po_alert indefinitely; this is display only.