RO Inbox

RO Inbox — Technical Spec

Router: app/ro_import.py, mounted at /ro before ro_router (see the im2.py mount patch — this ordering is load-bearing: ro_router's /{order_no:int} and /{screen:str} catch-alls would otherwise swallow /ro/inbox).

Data

• ro_inbox — one staged job (or manual xlsx upload). Never an RO. See create_inbox_tables.sql.
• ro_inbox_line — the lines exactly as they'd land on Accept.

Both tables must be applied via create_inbox_tables.sql before this router is deployed — they do not exist in the schema this package shipped against.

Routes

All POST routes require level2+ (ro_import.can_write / require_write — a fixed role check, admin/level1/level2, per Ver 3 addendum ruling C.3, not a role_action_perms lookup).

Import flow (stage_source_job)

No longer a stub [Ver 2, 2026-09-06]: stage_source_job() now dispatches into app/ro_fp.py (stage_fp_job(), stage_bt_job(), stage_upload()); lookup_item() / search_items() query the IM2 items table directly; promote_inbox_to_ro() is implemented in this file. The contract below still holds — every caller treats a raise as "nothing was written".

One entry point covers all three sources:

• FP: one call, stage_source_job("FP", source_id, user) → {"inbox_id": N}.
• BT: two calls.

1. stage_source_job("BT", source_id, user) (no bom_ref) → {"choices": [...]}, nothing staged yet.
2. stage_source_job("BT", source_id, user, bom_ref=<picked>) → {"inbox_id": N}.

• XLSX: one call with file_bytes/file_name set instead of source_id → {"inbox_id": N}. The form's source=XLSX is dispatched through the same branch as MANUAL. Ruled by Dave 2026-09-04: an uploaded sheet and a hand-typed order are both staged as source_system='MANUAL' (added to the CHECK on ro_inbox / ro_header / ro_import_log) with entry_source='Manual' and source_ref=file_name — not NULL, and not 'XLSX'. The upload is rejected client-side unless the filename ends .xlsx/.xlsm (a PDF BOM cannot be parsed), and server-side with the same check. [Ver 2, 2026-09-06]

A raise from stage_source_job at any point means nothing was written — the route returns {"ok": false, "error": ...} with HTTP 502, and the operator can just retry.

Accept: promote or refresh (promote_inbox_to_ro) [Ver 2, 2026-09-06]

Dave, 2026-09-05: a re-import must refresh the RO the source job already produced, not create a second one. promote_inbox_to_ro() first looks for ro_header.source_id = ro_inbox.source_id (newest first); if found it hands off to refresh_ro() and returns that existing order_no. refresh_ro():

• matches source lines to ro_detail by SKU (case-insensitive), and NONINV lines by description text (without this every no-SKU line re-added itself as "new" on each refresh);
• qty differences → qty_source updated in place plus qty_source_at_adj / adj_by / adj_at / adj_note ("Re-import of X: qty A → B"), one ro_bom_audit and one ro_audit row each;
• new source lines → inserted with origin='Source' and the same trail;
• lines no longer on the source → line_status='HOLD' with an explanatory adj_note and an open ro_issue (issue_type='no qty'). Never deleted — material may already be picked or on a PO;
• _open_issue_exists() (added 2026-09-05) stops a second identical Open issue stacking on the same line, which used to keep the line on HOLD forever;
• writes ro_audit (action='ro_refresh') with the touched-line count.

On the fresh path it inserts ro_header (job_name falling back to customer_name / FP <id>, bom_name falling back to source_ref / Inbox <id>, carrying fp_material_list_id, fp_job_id, fp_job_cuid, source_system, source_id, note) then one ro_detail per ro_inbox_line, mapping would_hold to line_status='HOLD' and raising a ro_issue ('SKU not in QB' when the reason mentions QB, else 'no qty'). ro_header_job_bom_uniq (job_name, bom_name) makes a duplicate Accept fail cleanly.

A held line is non-blocking; the rest of the order picks, posts and orders normally. Once the last Open issue on a line is closed, ro.sync_line_hold() clears the HOLD by itself (Dave, 2026-09-05).

Status machine

Awaiting Review / Held / Error → (Accept) → Accepted, or → (Reject) → Rejected. Accepted and Rejected are terminal — every action route 400s on a terminal row. One exception, added 2026-09-05: POST /ro/{order_no}/delete (admin only, in ro.py) sets any ro_inbox row pointing at that RO back to status='Awaiting Review' with order_no, reviewed_by and reviewed_at cleared and a note — a deleted RO's source job returns to this queue.

Transaction rules (unchanged)

See the docstrings on stage_source_job() and promote_inbox_to_ro() in app/ro_import.py: a raise from either must never leave a half-written row. POST /ro/inbox/{id}/accept still updates ro_inbox in its own follow-up transaction, and on a raise sets status='Error' + parse_error and returns 502.

Shared table sort (2026-09-07)

Column sorting is one implementation in templates/base.html (Dave, 2026-09-07: "all screens that have tables like this need sorts on the appropriate columns"). A table opts in with class="sorttable"; every thead th becomes sortable except those with class="nosort"/class="toggle", an empty heading, or a checkbox in the heading. Client-side only, over the rows already rendered. Details: tech/table_sorting.md.