Background Job — QuickBooks Item Master Refresh

(background process — no screen)

Technical Spec Ver 1 Updated 2026-09-06 Matches the live screen

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`)

Table	What is written
`items`	Upsert on `sku` of the QB-owned fields: `qb_item_id, item_name, description_po, description_so, qty_on_hand, purchase_cost, qb_active, expense_gl, income_gl, primary_vendor`, plus `qb_last_updated = now()`. New SKUs are inserted with blank warehouse fields (they land on the "needs a bin" queue).
`items` (status)	`qb_status` flipped to `'in_qb'` for rows found in either pull (adoption of items created in IM2); `archived_at = now()` where `qb_active = false AND qb_status='in_qb' AND archived_at IS NULL`; `archived_at = NULL` (un-archive) only for SKUs present in tonight's active pull.
`sync_runs`	One row per applied run: `finished_at, items_read, items_created, items_archived, exceptions`, and a JSON `detail` with the first 50 new SKUs, first 50 orphans (in IM2, not in QB), duplicate QB SKUs and the count of QB items with no SKU.

Inactive QB items never create IM2 rows — they only update/archive rows IM2 already has.

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.

Background Job — QuickBooks Item Master Refresh