Release to PO — technical specification

Release to PO (Phase 2, slice A) — Technical Spec

Source: app/po_rel.py, ddl/po_workbench_p2.sql, release card in app/templates/po_buylist.html. Slice A (manual) and slice B (auto-create) both live in po_rel.py; release_auto holds the only QuickBooks write in the whole workbench.

Routes

The POST takes po_number_<vendor_slug>, optional expected_<vendor_slug>, optional release_ref. vendor_slug is the vendor name lower-cased with every run of non-alphanumerics replaced by _ — the template and the server derive it the same way.

Tables

po_release — one row per PO produced (list_id, vendor_name, qb_vendor_id, doc_number, qb_po_id, line_count, total_amt, created_by, created_at, mode, status, error_text). Unique on (list_id, vendor_name) where status is not 'Failed': one PO per vendor per list.

po_number_seq — the 60000 counter. _next_po_number() allocates inside the release transaction with select … for update, so two concurrent releases cannot collide. Never derive a number by scanning QuickBooks: a hand-keyed 60xxx PO would break it.

qb_cdc_cursor — created now, used by slice C.

po_buy_list gains release_mode ('Manual'|'Auto') and release_ref (free text). released_at / released_by already existed; buy_list_display_status() already turns a non-null released_at into Ordered, so no template change was needed for the status.

ro_po_link gains a unique index on (qb_po_number, order_no, line_key) where qb_po_number is not null.

Pre-flight

preflight_release(conn, buy_list, lines, user) returns (problems, vendor_groups). Every check in the Phase 2 spec §3.1 runs, all of them, and the problems come back together, de-duplicated, naming line number and SKU. A non-empty list means nothing is written.

Checks: list not cancelled; not already released; caller holds po_release; at least one line ticked; no line still Pending Count; vendor present and resolving to exactly one qb_vendor row (name or company, case-insensitive); qty > 0; unit cost not negative; non-inventory lines have a description; the RO header behind the line has a class_ref.

Class and job live on ro_header (class_ref, qb_customer_id, qb_customer_name), not on the buy line — a buy line carries no RO reference of its own.

What gets linked back to an RO — and what deliberately does not

Only non-inventory lines. A buy line shaped N:<order_no>:<line_key> is bought for exactly one RO line, so it writes one ro_po_link row for the quantity ordered.

Inventory lines write no link at all (Dave, 2026-09-06). A SKU requirement is satisfied when it is picked from stock. The workbench sums all open demand for a SKU against on-hand and all open POs for that SKU, so an inventory PO replenishes stock in total — it is not owed to any one RO. Anyone tempted to "improve" this by allocating an inventory PO across RO lines by date is reintroducing a tie that does not exist in the business.

Consequence: On PO for an inventory SKU comes from the QuickBooks PO itself, via the nightly open-PO sync, not from release. Between a manual release and that sync the workbench does not yet see the PO.

Because inventory lines carry no RO, they carry no job and no class either — the class pre-flight check applies to non-inventory lines only.

The PENDING id, and why On PO does not double count

ro_po_link.qb_po_id is NOT NULL in the schema, so a hand-keyed PO is stored as PENDING:<doc_number> with qb_po_line_id null; the nightly QuickBooks sync backfills the real ids when it matches the DocNumber.

That created a real double-count risk: the same PO would be counted once from ro_po_link and again from qb_po_line as soon as the sync ran, because the existing exclusion in po._on_po_by_sku() matched on (qb_po_id, qb_po_line_id) — which a PENDING row never has. So _on_po_by_sku() now also excludes any qb_po_line whose doc_number already appears as a ro_po_link.qb_po_number. Anyone changing either query must keep both exclusions or the workbench will under-buy.

Permissions

New action po_release, label "Release a buy list to PO", default ('admin', 'level1'), registered in im2.ACTION_DEFS / ACTION_DEFAULTS so it appears on the permissions matrix. Gated with require_action(), not po.can_write() — releasing is a heavier act than editing a line.

Known limits in this slice

• No un-release. A wrongly recorded PO number needs a database fix; if this happens in practice the answer is a proper reversal that also removes the ro_po_link rows.
• Recording a PO does not verify the number exists in QuickBooks. A typo shows up as a PO that never syncs; slice C's change watch is what will surface it.
• Dave's "enter the pieces that come back to the RO manually" (per-line Add PO reference on the RO screen, link_source='Manual entry') is not in this slice.
• No PO report yet (/po/report, spec §6.3).
• expected_date falls back to the buy line's delivery_date when the operator leaves the date blank.

Slice B — auto-create (Ver 2, 2026-09-07)

Gate: syscon allow_auto_po. Off = release_auto refuses with a plain-English problem and the card shows only the manual path (/release/panel returns auto_enabled).

Pre-flight = the shared preflight_release() plus preflight_auto(): the syscon, a non-empty po_ship_to, a po_stock_class, every inventory SKU carrying items.qb_item_id, every non-inventory line's job resolving to a QuickBooks customer, and a live QuickBooks ping (select Id from CompanyInfo). All failures are returned together; nothing is written.

Payload (_po_payload, modelled on live PO 2961): DocNumber ours, TxnDate today, VendorRef from qb_vendor.qb_id, APAccountRef 588, ShipAddr = po_ship_to split on newlines into Line1..n, PrivateNote = "IM2 buy list {id} — RO {nos}", one ItemBasedExpenseLineDetail per line: ItemRef = the SKU's QB item or 2773 "Material" for non-inventory, ClassRef = the RO's class_ref (stock lines: syscon po_stock_class), CustomerRef = the RO's job (Service RO with no job → SA:Admin 15326, as ro_post.py does), BillableStatus NotBillable always, TaxCodeRef NON, UnitPrice the unloaded cost.

QuickBooks silently drops DueDate on PO create (verified 2026-09-07: sent 2026-09-14, read back absent — which is why every hand-keyed live PO has none either). The per-line expected date therefore lives only on the RO side. We still send it; harmless.

Idempotency: a po_release row is inserted with status 'Pending' before the POST, so a crash between POST and write-back is reconcilable rather than duplicable. Each vendor group is its own transaction and is committed as it succeeds; a re-release skips vendors that already have a Created/Pending row with a qb_po_id, and preflight_release(allow_retry=…) lets a part-failed list be retried even though released_at is stamped. Failures set status 'Failed' with error_text and are reported per vendor.

Write-back: Id and DocNumber are read off the created record, never assumed. Non- inventory lines get an ro_po_link row (link_source='IM2 auto release', real qb_po_id and qb_po_line_id); inventory lines get none by design (Dave, 2026-09-06) — their On PO comes from the nightly qb_po_line sync. Buy lines → Ordered, list → release_mode='Auto'.

Slice C interaction: po_watch._mark_deleted() sets ro_po_link.po_status='Deleted' and po_release.status='Deleted in QB' when CDC reports the PO deleted, and po._on_po_by_sku / po._noninv_on_po both exclude 'Deleted', so the RO line comes back on the workbench. A deleted PO holding only inventory lines has no link rows at all, so the watcher also matches CDC deletes against po_release directly and alerts from there.

Tested live 2026-09-07: PO 60001 (stock line, class Administration) and 60002 (non-inventory line on RO 799017, class Service, customer SA:Admin, ro_po_link written with QB line id 1), both verified field-by-field against hand-keyed PO 2961, re-release produced no duplicate, both then deleted in QuickBooks and picked up by the watch. Test rows removed, counter reset to 60001, allow_auto_po returned to No.