Background Job — QuickBooks Item Master Refresh

Background Job — QuickBooks Item Master Refresh

Mirrors QuickBooks-owned item fields into the IM2 items table. QuickBooks is the book of record; this job never writes QuickBooks.

Code: skills/im2_app/scripts/qb_refresh.py (runs in the Viktor sandbox, not on the droplet — it uses the Viktor QuickBooks connector sdk.tools.mcp_quickbooks.quickbooks_run_custom_query).

Trigger / schedule

• Viktor cron /logistics/im2-nightly-sync, 1:00 AM CT (0 6 * UTC). It runs qb_refresh.py --apply and roster_sync.py in the same run, and is silent unless it fails or finds exceptions (source: skills/im2_app/SKILL.md).
• Also run on demand: uv run --with psycopg2-binary python scripts/qb_refresh.py [--apply]. Without --apply it is a dry run and prints counts only.
• Not verified from inside this task: the cron registration itself (no cron-listing tool was used here). The schedule above is what the skill records; confirm with sdk.tools.scheduled_crons.list_crons before relying on it.

Inputs

• QB query 1 (active): select * from Item where Type = 'Inventory', paged 500 at a time.
• QB query 2 (inactive, added 2026-09-03): select * from Item where Active = false, filtered client-side to Type == 'Inventory'.
• select * is mandatory — a narrow column list silently returns zero rows on this connector.
• DB: sa-apps-db cluster, im2 database, URI from /work/credentials/do/sa-apps-db.json with /defaultdb swapped for /im2.

Tables written (all in im2)

Changed 2026-09-15 (Dave): inactive QB items now create IM2 rows too, not only update ones IM2 already has — reversing the 2026-09-03 decision. This backfills the RO Inbox/RO Issues "SKU not in the QB item master" reason with QB's own retirement convention ("<old item name> - Use <new SKU>") so it can point the reviewer at the replacement SKU; see qb_inactive_index()/use_replacement() in app/ro_parse.py. First backfill run (2026-09-15, applied) added 876 previously-unknown inactive items (926 total inactive Inventory-type items with a SKU pulled from QB, 50 of which IM2 already had). New rows land archived immediately, with blank warehouse fields, same as any adopted item. Caveat: items.sku is the primary key, so if QB's inactive catalogue has the exact same SKU string reused across two different deleted items, only one survives in IM2 — a small, accepted data gap, not a bug to chase. Confirmed by Dave, 2026-09-15: SKUs are not unique in QB at all — active or inactive, the same SKU string can legitimately exist on more than one QB item. IM2's schema was never designed for that (sku is the IM2 primary key, one row per SKU), so qb_refresh.py already drops exact-duplicate SKUs from the active pull's write set entirely (qb_dup_skus in the run summary) rather than picking one arbitrarily, and the 2026-09-15 inactive-catalogue change does the same for inactive-only dupes. This means: a genuinely duplicated SKU in QB is invisible in IM2 (neither copy is written/updated), and any "Use xxxxx" suggestion built from an inactive item's name could point at the wrong one if that replacement SKU itself isn't unique. Not something to fix by adding uniqueness — it's a real QB data condition IM2 can't fully represent with a one-row-per-SKU table; treat every SKU-matched suggestion as a lead to verify, not a fact.

Bug found and fixed same day (Dave flagged the risk, 2026-09-15): a SKU can be reused across one active QB item and a separate, unrelated deactivated QB item — 10 such SKUs found (50292, 50446, 51067, 51118, 51119, 51123, 51124, 51125, 51374, 51544, all real active items colliding with an old deleted item's SKU). Because the inactive upsert ran after the active upsert with the same ON CONFLICT (sku) DO UPDATE, it silently overwrote those 10 active items' item_name/qb_active/other QB fields with the deleted item's data and (for some) left them archived — this bug already existed in the original 2026-09-03 inactive-sync design, not introduced today, but today's full-catalogue backfill made hitting it far more likely. Fixed (first pass): qb_refresh.py excluded any inactive-pull SKU that's also in the current active pull before writing — the active item always wins. The run prints sku_reused_active_and_inactive=[...] so these are visible every run. Repaired: re-ran --apply; all 10 corrupted rows were restored (unarchived=10) with correct active data and archived_at cleared. Verified by direct DB read.

Revised same day (Dave, 2026-09-15): "make them xxx-1 and bring them over" — dropping the colliding inactive record entirely made it invisible/unsearchable, defeating the "Use xxxxx" lookup for that exact SKU. Changed to: the active item keeps the real SKU; the colliding inactive item is written under "<sku>-1" (bumped to -2, -3... only if that's also taken — checked against every SKU IM2 already has, not just this run's pull), with the new items.qb_sku_alias column holding the true SKU. Deterministic across runs: if IM2 already has a row whose qb_sku_alias matches the original SKU, that same suffixed SKU is reused (via ON CONFLICT (sku) DO UPDATE) rather than picking a new one, so the same original SKU doesn't drift to -2, -3... on a later sync. qb_inactive_index() (ro_parse.py) now keys on coalesce(qb_sku_alias, sku) so a BOM referencing the ORIGINAL SKU still finds the renamed inactive item and its "Use xxxxx" suggestion. Migration: ALTER TABLE items ADD COLUMN IF NOT EXISTS qb_sku_alias text (applied live). Re-ran --apply: all 10 collisions landed as 50292-1 etc., qb_sku_alias set, active items still correct — verified by direct DB read.

Bug found same day (Dave: "This one has an inactive record pointing to a good SKU"): ro_parse.py::qb_skus() (the "known SKU" set classify() uses to decide would_hold) had no qb_active filter, so once the 926-row inactive backfill landed, ANY sku with an inactive IM2 row -- not just active ones -- counted as "known", meaning a BOM line referencing a retired SKU stopped being flagged at all instead of getting the "use X" suggestion. Before the backfill this was harmless (IM2 held almost no inactive rows); after it, this was backwards. Fixed: qb_skus() now filters qb_active = true. Backfill: 3 already-open "SKU not in QB" issues (647, 693, 694 -- SKUs 51019/50998/51024) had been created in the gap before their SKU's inactive row existed in IM2, so their title/detail never got the "use X" hint; updated in place once the fix landed (title/detail text only, no line/RO changes) -- verified by direct read.

External calls

QuickBooks Online (via the Viktor QB connector) — read only, two paged queries. Full read takes ~4 minutes; run long invocations under nohup with a log file.

Failure behaviour / idempotency

• Fully idempotent: upsert by SKU, and every archive/un-archive statement is conditional, so re-running the same night changes nothing extra.
• Duplicate SKUs in the QB pull are dropped from the write set entirely (counted as exceptions) rather than being written twice.
• Rows with qb_status <> 'in_qb' (created in IM2, not yet loaded to QB) are excluded from the orphan list and never archived.
• No retry logic. A connector or network failure aborts the run with a traceback and writes nothing (all writes are in one with conn transaction); the next night's run catches up.
• Un-archive is scoped to SKUs in the active pull so a row whose QB item vanished cannot be un-archived forever on a stale qb_active = true.

Logs to look at

• Cron output for /logistics/im2-nightly-sync (stdout lines: QB inventory items=… usable=… new_to_im2=… …, applied. archived=… unarchived=…, DONE).
• sync_runs table — the authoritative per-run record and the source of the app's "QB last sync" indicator.
• audit_log rows with source='qb_sync' for field-level changes.

Known limits / gaps

• Field ownership is still QB-wins for purchase_cost, even though Dave's 2026-08-29 decision is that IM2 owns cost and only name + qty on hand are QB-owned. Per-field ownership flags are not implemented in qb_refresh.py.
• The agreed three-leg nightly sync (IM2→QB, QB→IM2, QB→FP) is only leg 2 today; the IM2→QB push and the QB→FP update pass are not part of this job.
• Runs off-box, so it needs the Viktor sandbox and its QB connector; the droplet's own QB credentials (/opt/im2/env) are not used here.