Change Log

Change Log and CSV exports — Technical Spec

All in app/im2.py. Templates: app/templates/changes.html, and the Export button on app/templates/grid.html (line 131) for /export.

Routes

All depend on current_user only — any signed-in role, including level3, can read the whole audit trail and export the item master. There is no permission check on either export.

audit_log

scripts/schema.sql: id bigserial, sku text, field_name text, old_value text, new_value text, changed_by text, changed_at timestamptz default now(), source text. Index audit_sku (sku, changed_at DESC) — note there is no index on changed_at alone, which is what this screen orders and filters by.

sku is overloaded: item rows hold a real SKU, user-admin rows hold USER:<name>, permission rows hold PERM:<kind>:<key>.

Writers (grep insert into audit_log)

Filtering (_change_where, CHANGE_RANGES)

CHANGE_RANGES = {today, 7, 30, all}; anything else falls back to today. today → changed_at >= date_trunc('day', now()); 7/30 → now() - (%s || ' days')::interval; all → no date clause. sku → ILIKE %…%; who, field, source → equality. The helper takes an alias prefix p because the screen query joins (a.) and the count query does not.

Screen query: select a.*, i.item_name from audit_log a left join items i using (sku) … order by a.changed_at desc, a.id desc limit 200 offset (page-1)*200. page is clamped to 1..page_count. Three extra select distinct queries fill the who / field / source dropdowns from the whole table, unfiltered.

/changes.csv runs the same clause with no limit and writes SKU, Item, Field, Old value, New value, Changed by, Changed at, Source, with changed_at formatted %Y-%m-%d %H:%M:%S.

/export (item master)

Calls query_items(conn, search, gen_loc, item_group, manufacturer, flag, include_archived=bool(int(archived)), limit=100000, offset=0, sort, direction) with the raw query params, then csv.DictWriter over ["sku"] + QB_FIELDS + WH_FIELDS, extrasaction="ignore":

• QB_FIELDS: item_name, description_po, description_so, qty_on_hand, purchase_cost, primary_vendor, qb_active
• 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

include_archived is either/or, not additive (query_items comment): off → archived_at is null, on → archived_at is not null. flag accepts needs_bin | no_mfr | changed_today | no_uom. The column set is deliberately the same one /api/upload matches on (sku key + WH_FIELDS), so export → edit → upload round-trips.

Known limits

1. No permission gate on /changes, /changes.csv or /export. Costs, vendors and the entire change history are readable by every signed-in user.
2. archived= is parsed with int(...) inside bool(...) — a non-numeric value in the query string raises ValueError → HTTP 500 rather than a clean 400.
3. /changes.csv and /export build the whole file in memory (io.StringIO); /export is capped at 100,000 rows, /changes.csv is uncapped.
4. left join items using (sku) leaves item_name NULL for USER: / PERM: rows, and the template still renders the SKU cell as a link to /?search=USER:x.
5. audit_log has no changed_at-only index; range=all on a large table is a full scan plus sort.
6. The explanatory note on the screen lists app / bulk / upload / abc-report / cycle count / perms_admin but omits qb_sync, user_admin, new item screen, recount and counts, which do appear in the Source dropdown.