Background Job — QuickBooks Vendor & Open-PO Sync

Background Job — QuickBooks Vendor Sync + QuickBooks Open-PO Sync

Two separate nightly jobs that feed the Purchasing Workbench: the vendor typeahead and the "Qty on PO" supply figure. Both run from the Viktor sandbox (QB tokens live there), push a JSON file to the droplet by scp, and run a loader on the droplet against RO_DSN. Both tables live in the ro database, not im2.

1. Vendor sync

• Code: skills/im2_app/scripts/sync_qb_vendors.py; droplet loader /opt/im2/scripts/load_qb_vendors.py.
• Trigger: Viktor cron /im2/qb_vendor_sync, nightly 02:30 CT (07:30 UTC) (recorded in skills/requirements_orders/SKILL.md, 2026-09-04). Manual: uv run python skills/im2_app/scripts/sync_qb_vendors.py.
• Input: QB SELECT Id, DisplayName, CompanyName, Active, AcctNum FROM Vendor, paged 1000.
• Output table qb_vendor (qb_id, name, company, acct_num, synced_at) — ~932 rows live.
• Consumer: po._known_vendor_names() unions qb_vendor with items.primary_vendor and ro_po_link.vendor_name, wrapped in try/except so a missing table cannot break the Workbench.
• Guard: if fewer than 100 vendors come back the script aborts with exit 1 and does not touch the droplet — a partial QB pull can never wipe the list.

2. Open-PO sync

• Code: skills/im2_app/scripts/sync_qb_open_pos.py; droplet loader /opt/im2/scripts/load_qb_open_pos.py.
• Trigger: Viktor cron /im2/qb_open_po_sync, nightly 02:45 CT (recorded 2026-09-05). Manual: uv run python skills/im2_app/scripts/sync_qb_open_pos.py.
• Auth: self-contained Intuit OAuth client — env file /work/secrets/qbo_intuit_prod.env, token cache /work/secrets/.qbo_access_token.json, refresh at https://oauth.platform.intuit.com/oauth2/v1/tokens/bearer, optional QBO_PROXY. It refreshes and rewrites the refresh token in the env file (file is replaced, not truncated, because another sandbox user may own it).
• Input: SELECT FROM PurchaseOrder WHERE TxnDate >= '2025-01-01' (paged 200) plus SELECT FROM Item WHERE Type = 'Inventory' to map ItemRef → SKU.
• Output table qb_po_line (qb_po_id, qb_po_line_id, doc_number, vendor_name, sku, description, qty_ordered, qty_received, unit_price, po_date, expected_date, po_status, synced_at) — ~531 lines / 269 SKUs at build time.
• Consumers: po._on_po_by_sku() and /workbench/{sku}/supply union qb_po_line with ro_po_link, deduped on (qb_po_id, qb_po_line_id).
• Guard: if the shaped line list is empty the script aborts and leaves the existing table in place.

External calls

QuickBooks Online (read only) and scp/ssh to root@206.81.10.183 using skills/im2_app/deploy/im2_deploy_key. The key is copied to a temp dir and chmod 0600 first — ssh rejects the repo copy (mode 0644).

Failure behaviour / idempotency

• Both loaders are upsert/refresh style keyed on the QB identifiers, so re-running is safe.
• subprocess.run(..., check=True) means an scp/ssh failure raises and the run ends with a non-zero exit; nothing partial is left on the droplet beyond the staged JSON in /tmp.
• Neither job retries. A failed night simply leaves last night's data in place — which is acceptable per Dave ("'all' can be as of last night").

Logs to look at

• Cron output for /im2/qb_vendor_sync and /im2/qb_open_po_sync (each prints a count line plus whatever the droplet loader echoed).
• Freshness check in SQL: select count(*), max(synced_at) from qb_vendor; and the same on qb_po_line (RO database).

Known limits

• QB's query language will not filter on POStatus; POs are pulled by TxnDate >= 2025-01-01 and filtered client-side, so a genuinely old open PO is invisible.
• ~25% of open PO lines are booked to generic non-inventory items ("Material") with the real part only in free text — those lines are skipped, counted as unmapped, and never show as supply.
• A QB PO can sit "Open" with every line fully received, so outstanding qty is computed as Line.Qty − Line.Received, not from PO status.
• Both jobs depend on the sandbox, the deploy key, and the droplet being reachable; there is no droplet-side fallback.
• Vendor rows are not de-activated: Active is queried but the cleaned payload keeps every named vendor.

Ver 2 · 2026-10-24

The vendor loader now also keeps IM2 vendor_gl_default in step with QB (adds new vendors, renames, flags inactive). See tech/vendor_gl.