Create New Inventory Item — Technical Spec

1. Routes

Handlers in app/im2.py, template newitem.html.

ACTION_DEFS registers item_create as "Create New Item — The /new screen and saving a new item." Permissions are data (role_action_perms, defaults in ACTION_DEFAULTS), editable on /perms.

2. GET context

next_sku(cur) = max(sku::bigint) where sku ~ '^[0-9]+$' + 1, floor 50001 — computed for display only, outside any lock. Context also carries setting_uoms(cfg), income_gl, expense_gl, default_count_group, list values for gen_loc / item_group / manufacturer / count_group, and pending (count of qb_status in ('pending','error','sent') on GET; the count returned by the create and retry endpoints counts ('pending','error') only).

Fixed bug retained in comments: the lead-time field used to be pre-filled from the PO Workbench syscon default_lead_time_days; it now renders blank and that syscon is left to po.py default_lead_time().

3. POST /api/items/new

Form fields: item_name, description_po, uom, primary_bin, secondary_bin, gen_loc, item_group, manufacturer, mfr_part_no, min_qty, max_qty, count_group, notes, primary_vendor, purchase_cost, lead_time_days.

Validation, in order:

1. require_action(user, "item_create").
2. item_name and description_po both non-blank → else 400.
3. primary_vendor (Ver 9, 2026-10-27): if non-blank it must match (case-insensitive) a qb_vendor_names() entry → snapped to master spelling, else 400. Screen: datalist + prefix autofill + blur check (checkVendor).

3b. uom must be in setting_uoms(settings()) → else 400 (UOMS is EA/FT today).

1. num() on purchase_cost, min_qty, max_qty, lead_time_days: blank → NULL, non-numeric → 400.
2. Min/Max rules (Dave, 2026-09-06): either value below 0 → 400 Min qty cannot be negative / Max qty cannot be negative; both present and min > max → 400 Min qty (n) cannot be more than Max qty (n). Either may be blank.
3. pg_advisory_xact_lock(SKU_LOCK), then a case-insensitive item_name uniqueness check → 400 if taken.
4. next_sku(cur) inside the lock — the authoritative SKU, which may differ from the one rendered.
5. INSERT INTO items with qty_on_hand = 0, description_so = description_po, qb_status='pending', qb_active=false, income_gl/expense_gl from settings, count_group defaulting to cfg['default_count_group'], created_by/updated_by = username, timestamps now(). Bin/UOM normalisation (uppercase/trim) is the DB trigger items_normalize_trg.
6. audit_log row: field_name='item', new_value=item_name, source='new item screen'.

The IM2 transaction commits before QuickBooks is contacted.

4. QuickBooks push

_push_new_item_to_qb(sku, item_name, description, cost, vendor, reorder_point) → qb.QBO().create_inventory_item(...): Type='Inventory', TrackQtyOnHand=True, QtyOnHand=0, InvStartDate=today, Sku, Name, Description+PurchaseDesc, PurchaseCost (if given), ReorderPoint = min_qty, income/expense/asset account refs from settings (qb_income_account_id, qb_expense_account_id, qb_asset_account_id), preferred vendor resolved by vendor_id(vendor).

• Success → items.qb_status='in_qb', qb_active=true, qb_item_id, qb_error=null, qb_synced_at=now().
• Failure → qb_status='error', qb_error = str(exc)[:500]; never raises. Response {"ok": true, "sku", "next_sku", "pending", "qb_ok": false, "qb_message": <Intuit's wording>}.

POST /api/items/retry-qb re-pushes every row with qb_status in ('pending','error') and returns created/failed/pending; 400 when nothing is waiting. qb_status='sent' rows are not re-sent — the new-item QB sync sheet endpoint /api/items/new-sync was removed 2026-09-06 (Dave: "we're not using sync sheets"), and re-sending 'sent' rows was what could create a duplicate QB item.

Note GET /api/items/pending still lists qb_status in ('pending','error','sent'), so any historical 'sent' row remains visible on the screen even though Retry no longer touches it.

5. What syncs back

scripts/qb_refresh.py mirrors QB-owned fields (name, description, cost, active flag, qb_item_id) onto items and archives QB deactivations. Item name is read-only in IM2 by design — a rename happens in QB and arrives on the next refresh. IM2 never writes QB except through this screen's create call.

6. Known limits / gaps

• The reserved SKU shown on GET is advisory; the committed SKU is recomputed under the advisory lock. The form's success message is the only reliable record of the assigned SKU.
• No duplicate detection beyond exact lower(item_name); near-duplicate names and duplicate mfr_part_no are allowed (the historical 15 duplicate-SKU problem in the spreadsheet is not guarded here).
• lead_time_days, item_group, gen_loc, count_group, notes, bins, min/max and mfr_part_no are IM2-only — none of them reach QuickBooks (min_qty reaches it only as ReorderPoint).
• No uom is sent to QuickBooks at all (QB units of measure are not touched by this build).
• The IM2 insert commits before the QB call, so a QB failure always leaves a real IM2 row in qb_status='error' — deliberate, but it means IM2 can hold items QuickBooks has never seen.

Client-side validation (added 2026-09-06)

Dave, 2026-09-06: "minimize the checks at save that could fail." newitem.html now mirrors every server-side rule on blur: checkNumber('purchase_cost'), checkMinMax() (numeric, non-negative, min <= max) and checkBin() for both bin boxes (upper-cased in place, ^[A-Z0-9][A-Z0-9 ./-]*$). Messages render in a .fmsg div per field and the field gets .bad; validateAll() gates the submit handler with "Fix the fields marked in red first". The server checks in create_item are unchanged and still authoritative — this only stops the round trip. Remaining save-time failures are by nature server-only: duplicate item name and whatever QuickBooks rejects. The bin pattern is client-only; there is no server bin format rule, so it is deliberately permissive.

Scan item (added 2026-10-25, Ver 4)

app/scan_item.py (router included at the bottom of im2.py). GET /api/items/scan/enabled → {enabled} (true when env IM2_VISION_KEY is set; the button is hidden otherwise). POST /api/items/scan (multipart photos, 1–3, action item_create): images are EXIF-rotated, shrunk to 1600px JPEG in memory (HEIC ok), sent to Gemini (IM2_VISION_MODEL, default gemini-2.5-flash) with a JSON response schema and the live item-group / manufacturer lists plus 12 sample item names for style. No Google Lens (no API). Photos are not stored.

Matching (find_matches) over all items rows incl. archived: exact = normalised model number equals mfr_part_no, or (len ≥ 4) appears in name/description/part #; possible = token overlap ≥ 2 words and ≥ 40%. Exact first, active before archived. Returns extracted, up to 6 matches, and prefill (manufacturer snapped to the known spelling, UOM limited to the allowed list, item group only if on the managed list, notes flag label vs sight). With no match the form is prefilled automatically; with matches, "create anyway" applies the prefill. Nothing is written to the DB by this endpoint — /api/items/new is unchanged.

Item photos (Ver 5, 2026-10-25)

Table item_photo(id, sku, filename, content_type, data bytea, created_by, created_at), created lazily. After a successful /api/items/new the page POSTs the scanned files to POST /api/items/{sku}/photo (item_create; re-shrunk to 1600px JPEG, audit_log row). GET /api/items/{sku}/photos lists, GET /api/item-photo/{id} serves. Not yet shown on the Items detail screen. Display (Ver 6): GET /api/items-with-photos feeds the 📷 link in grid.html; scan.html (shared by /scan and the compact HH/count views) loads /api/items/{sku}/photos into #itemPhotos. Ver 8: grid +📷 and scan.html Add photo button reuse POST /api/items/{sku}/photo (item_create). Ver 8: scan button always shown; with no vision key it only stages the photo for attach-on-Save.

As Of Date (Ver 9, 2026-10-28)

POST /api/items/new takes required form field as_of_date (ISO date, not future, else 400). Stored in items.as_of_date (date, nullable; DDL ALTER TABLE items ADD COLUMN as_of_date date). Sent to QuickBooks as InvStartDate via qb.create_inventory_item(as_of_date=); _push_new_item_to_qb(..., as_of_date); /api/items/retry-qb re-sends the stored value (null → today). Slack agent endpoint accepts as_of_date, defaulting to today. Screen: <input type=date id=asof> prefilled with today, max=today, reset restores today. Cycle-count posting still uses today (unrelated; see SKILL gotcha).