Background Job — RO Intake (BOM → RO Inbox)

(background process — no screen)

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

Background Job — RO Intake (source BOM → RO Inbox)

Stages material sources as RO Inbox rows. Nothing here ever creates a Requirements Order — Accept, on the RO Inbox screen, does that.

Two scripts, byte-identical to the Viktor-side originals in skills/requirements_orders/scripts/ (ro_inbox_watcher.py, ro_inbox_lines.py) and deployed to /opt/im2/app as:

app/ro_watch.py — detection: which FieldPulse jobs have material to order.
app/ro_parse.py — line staging: parse the BOM/material list into ro_inbox_line, validate SKUs, and diff against an existing RO.

app/ro_fp.py calls the same two modules from the app's manual import routes, so the screen and the watcher can never drift apart.

Trigger / schedule

There is no schedule. Verified 2026-09-05 (skills/requirements_orders/SKILL.md): no droplet crontab or systemd timer and no Viktor cron for either script; every ro_inbox row to date was staged by Viktor running the scripts by hand.
The intended trigger is an hourly Viktor cron that must first check the system constant allow_fp_watcher (Settings screen, default No). That setting exists but no code reads it yet — wiring it is a prerequisite for turning the cron on.
Manual runs: uv run --with httpx --with psycopg2-binary python .../ro_inbox_watcher.py [--apply] and ... ro_inbox_lines.py [--apply] [--inbox-id N] [--recheck]. Both default to a dry run that prints exactly what they would write.

Inputs

FieldPulse internal API https://apibe.fieldpulse.com/v2.5, JWT from POST /authorize with FP_EMAIL / FP_PASSWORD (both set in /opt/im2/env).
Jobs in status Pending Material (status_id = 1096195); the filter is unreliable so the status is re-asserted client-side.
Per job: /job/{id}?withCustomfieldsAndRelatedData=true, /job/{id}/files/with-categories, and — when the job has no BOM — /project/{project_id}/files/with-categories (commercial BOMs often hang on the project).
Service work has no BOM attachment: the watcher sweeps /material-list, keeps lists whose customer_id matches a job of interest, then confirms the actual job link via rel[connections][job] on the detail call. Customer match alone is not proof.
BOM candidate test: the label must contain "bom", matched on display_name (name is an upload slug); .xlsx/.xls sort first.
Line parsing reads the sheet with parse_bom_xlsx (same rules as scripts/import_bom_xlsx.py); SKUs are validated against im2.items.

Tables written (RO database, `RO_DSN`)

Table	Written by	Contents
`ro_inbox`	`ro_watch.stage()`	one row per (job, BOM name): `source_system='FP'`, `source_id` (FP cuid), `fp_job_id`, `fp_material_list_id`, `job_name`, `customer_name`, `address`, `bom_name`, `source_ref`, `detected_at`, `status='Awaiting Review'`, `entry_source='Watcher'`, `note`.
`ro_inbox_line`	`ro_parse.insert_lines()`	parsed lines with `would_hold` + plain-English `hold_reason` for an unknown SKU, missing/zero qty or a sheet warning; change annotations ("new line", "qty 2 → 5") when the job already has an RO.
`ro_inbox` (updates)	`ro_parse`	`parse_error` set/cleared, `ro_type` filled, and a row auto-marked `Accepted` with a note when a recheck finds no differences.

Reads only: ro_header.source_id (already promoted), im2.items (SKU validation), bt_document (BuilderTrend cache).

Other intake paths (same parser, different front door)

BuilderTrend — BT has no API and the droplet has no browser, so skills/requirements_orders/scripts/bt_bom_sync.py (Viktor-side, browser login) caches *_BOM_with_QB_IDs.xlsx bytes into bt_document; ro_fp.stage_bt_job() lists them for the operator and parses the cached bytes. Manual, no schedule found.
Operator upload — ro_fp.stage_upload(), .xlsx/.xlsm only; a PDF BOM is rejected with a message telling the operator to get the spreadsheet.
Dropbox /SA_Operations/Requirement DB/To Process is the design-era intake folder described in the Requirements Orders skill. No Dropbox code exists in the app or in scripts/ (only the unrelated Item Master seeder references Dropbox). Treat Dropbox intake as not built.

Failure behaviour / idempotency

Watcher skips a job whose source_id is already in ro_header, and skips a (source_id, bom_name) pair already sitting in ro_inbox in a non-terminal state (terminal = Accepted / Rejected) — so repeated runs do not duplicate rows.
--apply refuses to write if the ro_inbox tables do not exist.
A file that cannot be read produces a staged row with parse_error set — that is the correct outcome, not an exception.
--recheck stages a review row only where the source now differs from the RO, and aborts entirely if the item-master lookup returns fewer than 100 SKUs (refuses to diff against a broken catalog).
Neither script retries FP calls; an FP outage means nothing is staged that run.

Logs to look at

Script stdout (PM jobs scanned: …, NEW …, skip … already an RO).
/work/temp/ro_inbox_watcher_last.json — full candidate dump from the last watcher run.
ro_inbox.parse_error, ro_inbox.status, and the RO Imports log screen.

Known limits / gaps

Unscheduled (see above) and allow_fp_watcher is unread by any code.
Only Pending Material jobs are detected; nothing else triggers intake.
A job with neither a BOM attachment nor a material list stages with the note "No BOM attachment and no material list found" (e.g. FP #1310 Ernest Gabriel).
Non-spreadsheet BOMs are detected but cannot be parsed.
FP credentials are hard-coded as defaults in both scripts (env vars override) — a credential rotation must touch the code or the env file.
The material-list sweep is capped at 40 pages of 100 lists.