Item Master Display — Technical Spec

1. Routes

Template: app/templates/grid.html. Handler: grid() in app/im2.py.

2. Inputs (GET /)

Query string, all optional: search, gen_loc, item_group, manufacturer, flag, archived (0/1), page (1-based), sort, dir (asc/desc). Page size is fixed at 200; offset is (page-1)*200. Unknown sort values fall back to sku; dir anything other than desc is asc. Pager UI: _page_url(request) rebuilds the current query string with a new page, and the template gets page_count (ceil(total/limit)), first_row, last_row for the First/Prev/Next/Last controls and the "Showing 401–600 of 4,182" readout. The filter form and sort links omit page, so any filter or sort change restarts at page 1. [Ver 2, 2026-09-04]

3. Query logic (query_items)

• archived_at is null unless archived=1.
• search → sku ILIKE %s OR item_name ILIKE %s OR description_po ILIKE %s, term wrapped in %.
• gen_loc / item_group / manufacturer → equality, ANDed with everything else.
• flag is exclusive, one of: needs_bin (primary_bin is null or ''), no_mfr, no_uom (uom is null), changed_today (updated_at >= date_trunc('day', now())).
• Count and page are run off the same WHERE clause, so the row total always matches the filters.

Ordering (order_clause) is whitelisted against SORT_COLUMNS — no user string reaches SQL. Numeric columns (qty_on_hand, purchase_cost, min_qty, max_qty, lead_time_days) sort numerically with nulls last; everything else sorts as nullif(col::text,'') with nulls last, then sku as tiebreak. Blank-last is deliberate in both directions.

4. Data read

Table items, all columns (select *). Supporting reads per render:

• list_values(conn, 'GenLoc', 'gen_loc'), 'Group'/'item_group', 'Mfg'/'manufacturer' — the managed list from app_settings (via LIST_SETTINGS) unioned with distinct in-use values not on the list, sorted case-insensitively. Retiring a value in Settings removes it from the dropdown but never rewrites items.
• flag_counts(conn) — four count(*) filter (...) aggregates over active items, driving the chips.
• last_sync(conn) — newest row of sync_runs (finished_at, status).
• setting_uoms(settings()) — UOM dropdown values.

5. Field ownership

QB_FIELDS = item_name, description_po, description_so, qty_on_hand, purchase_cost, primary_vendor, qb_active — rendered read-only/grey, rejected server-side by clean_value with "controlled by QuickBooks" regardless of role. QB is the book of record; these arrive on sync.

WH_FIELDS = primary_bin, secondary_bin, gen_loc, item_group, manufacturer, mfr_part_no, uom, min_qty, max_qty, lead_time_days, count_group, notes, abc_code — editable subject to role_field_perms. no_cost_expected / no_cost_reason are not in WH_FIELDS and are not rendered as columns on this grid; they exist only as grantable rows in EDITABLE_FIELD_LABELS on the Permissions screen and are set elsewhere (ABC/no-cost reporting). [Ver 4, 2026-09-06]

6. Write path (apply_change)

1. clean_value(field, value, user) — reject QB fields; reject fields not in editable_fields(user["role"]) (403); trim strings, empty → NULL; NUMERIC_FIELDS (min_qty, max_qty, lead_time_days) cast to float, negatives rejected; uom upper-cased and validated against the Settings list; abc_code upper-cased and restricted to A/B/C.
2. Read current value; if unchanged as text, return without writing (no empty audit rows).
3. Cross-field rule: min_qty must not exceed max_qty, checked whichever side is being edited.
4. update items set {field} = …, updated_by, updated_at = now().
5. Insert into audit_log (sku, field_name, old_value, new_value, changed_by, source).

POST /api/item/{sku} re-reads the stored value and returns it, so the cell shows what the database actually holds (normalised case, trimmed) rather than what was typed. POST /api/bulk splits skus on commas and calls apply_change per SKU, accumulating changed and a per-SKU errors list — one failure does not abort the batch.

7. Audit trail

GET /api/audit/{sku} returns audit_log rows for that SKU, newest first. source distinguishes origin: app (grid edit), bulk, upload, recount, counts, cycle count {batch_code}, perms_admin. No delete or update path exists for audit_log anywhere in the app.

8. Permission gates

Field-level rights come from role_field_perms via editable_fields(role), cached in _PERM_CACHE and cleared on any save from /api/perms. The template only renders inputs for fields in editable, and clean_value enforces the same list server-side — the UI hiding a field is convenience, not security. Level 3 can never hold field rights (blocked at /api/perms).

Action-level keys live in role_action_perms (ACTION_DEFS / ACTION_DEFAULTS in im2.py) and are editable on the Permissions screen: item_create, item_qb_sync, item_upload, count_record, count_post, abc_run, abc_write, ro_view, ro_write, pick_do, pick_post, and rush_priority_set (added 2026-09-05 for the Rush toggle on the PO Workbench; default admin/level1). [Ver 4, 2026-09-06]

8a. Decided business rules carried in this screen

Rules settled in design that the code enforces here, restated so the logic can be checked without reading the code. [Ver 3, 2026-09-04]

• QuickBooks owns identity and money. Item name, descriptions, cost, vendor, on-hand and active flag are read-only here in every role. Items are never created or priced on this screen.
• The warehouse owns place and handling. Bins, Gen-Loc, group, manufacturer, part number, notes, UOM, min/max, lead time, count group, ABC.
• Level 3 is view-only by design and cannot be granted field rights, even by an Admin.
• Level 2 edits where-it-lives and who-made-it fields only; the planning numbers (min, max, UOM, ABC, count group, lead time) are Level 1 and Admin.
• no_cost_expected + reason is Admin only. It marks an item that legitimately has no purchase cost — used or surplus equipment, seed stock — and takes it off the uncosted-item exception list. Reason is required with the flag. An item carrying the flag is never given a cost by backfill; if it is genuinely purchased later, the flag comes off first.
• Cost is the base (unloaded) cost — no tax, no freight — everywhere it is displayed or used in a variance value.
• Min must not exceed Max, checked from whichever side is edited.
• Lead time is in working days, not calendar days.
• A retired list value is retired going forward only. It stops being offered in the dropdown; items already carrying it keep it. Nothing is silently rewritten.
• No write without an audit row, and no write when nothing changed — an edit that re-saves the same text is a no-op rather than an empty history line.
• A bulk edit is many single edits, each permission-checked, each audited, each able to fail on its own without stopping the rest.
• Spreadsheet update is Admin only; Create New Item is Admin and Level 1; Users, Settings and Permissions are Admin only and hard-coded, not adjustable on the Permissions screen.
• qty_on_hand is never typed. It moves only by a posted count or by the QuickBooks sync.

9. Errors

All failures are HTTPException with a plain-English detail rendered next to the cell: 400 for validation and QB-owned fields, 403 for insufficient level, 404 for an unknown SKU. Bulk edits report per-SKU errors in the response body.

10. Limits and dependencies

• 200 rows per page; /export streams the full filtered set with no page limit.
• Depends on the QuickBooks item sync for QB-owned columns and qty_on_hand; staleness is visible via last_sync. The grid never writes to QuickBooks.
• qty_on_hand is only ever changed by posted cycle counts (see the Cycle Count spec) or by sync.
• Requires app_settings rows for the UOM / Group / Gen-Loc lists; absent lists fall back to whatever is in use on items.

Uppercase fields are converted, not just styled (2026-09-06)

Dave, 2026-09-06 ("do the boxes requiring upper case auto convert?"). They were styled with text-transform: uppercase only, so a typed a12 displayed as A12 and was stored as a12 — two spellings of one bin that no longer match or sort together. Now clean_value() upper-cases primary_bin, secondary_bin and gen_loc (as it already did uom and abc_code), create_item upper-cases both bins and gen-loc on insert, and the grid's inline editor upper-cases the value before it is sent (data-upper="1" on bin and ABC cells). Existing data was already clean: 0 of 917 primary bins and 0 of 1,517 gen-locs were mixed case, so no backfill was needed. item_group and count_group are deliberately left in mixed case — they are pick lists of proper names ("Inverter Parts").