Inventory Exception Agent

Inventory exports accumulate duplicate SKUs, stale stock, and unparseable values faster than anyone reviews them by hand. This tool surfaces the ones worth acting on.

Ingests a messy inventory/SKU CSV export and produces a single, ranked exception report — worst issues first.

Results

Run against data/inventory_raw_large.csv (1,605 rows).

Top of the ranked report — full output in report.csv (1,605 rows).

	Time
Manual baseline (200-row sample, timed)	9 min 48 sec
Manual baseline (extrapolated to 1,605 rows)	~78.6 min
Agent runtime (1,605 rows)	0.87s
Speedup	~5,400x

Manual review of a 200-row sample took 9:48. Against an independently computed ground truth, the tool matched exactly in all four categories; the human tally differed by 2 in one category (a tallying slip on rows failing multiple checks). This validates the tool's correctness at a scale where careful manual review is still feasible, and establishes that equivalent review of the full 1,605-row file would take approximately 79 minutes — the point at which manual review stops being practical.

Linear extrapolation likely understates manual effort, since duplicate detection degrades faster than linearly with row count and sustained manual review is subject to fatigue.

Category	Count
Conflicting duplicate	81
Validation failure	246 (210 missing field, 36 unparseable value) — counts distinct rows, not raw flags
Quantity anomaly	95 (56 negative, 39 placeholder 99999)
Dead stock	140
Exact duplicate (auto-collapsed)	48 groups, 48 rows removed

Validation-failure counting definition: the tool emits one exception record per row that fails validation, not one per failed field — a row missing product_name and with an unparseable unit_cost still counts once. This was confirmed against the 200-row manual accuracy check: an independently computed ground truth found 27 rows with a missing required field and 9 rows with a non-numeric quantity/cost (36 raw flags), but only 31 distinct rows once the 5 rows that fail both checks are deduped — and the tool reported 31, an exact match. (The human manual tally counted 33, off by 2 from ground truth — a tallying slip, not a tool discrepancy.) Distinct-rows is the standard used everywhere in this repo, including the 1,605-row table above; it's also the more useful unit for a warehouse manager, who wants one line per bad row rather than fragments of the same row scattered across the tier.

The 210 / 36 subtype split is priority-assigned, not non-overlapping. Every row gets exactly one subtype: missing_field if the row is missing any required field (regardless of whether it also has an unparseable value), otherwise unparseable_value if every problem on the row is an unparseable value. So 36 unparseable value specifically means unparseable-and-not-also-missing-a-required-field. At 1,605 rows, 55 rows have an unparseable value, but 19 of those also have a missing field and are counted in the 210 instead — 55 − 19 = 36. 210 + 36 = 246 is a true total (every row with any problem is counted exactly once), it just isn't two disjoint raw-flag counts.

Full output: report.csv (GitHub renders this as a sortable table) / report.html (GitHub shows this as raw source, not rendered — see the screenshot above for how it actually looks), both committed in this repo so you can see real results without cloning or running anything.

Standout finding

SKU-5194 (Trekking Poles Pair) has two conflicting records that disagree on quantity and warehouse:

• Row 879, warehouse wh-a: quantity 653
• Row 1047, warehouse WH-C: quantity 34

Both at the same unit cost ($184.81), that's a ~$114,397 discrepancy in reported inventory value for one SKU — the single highest-dollar conflict in the dataset.

Notes on the numbers above

• Dead stock is approximated via last_sale_date recency; a production version would use full transaction history.
• The manual baseline was extrapolated from a timed sample of 200 rows (data/manual_sample_200.csv), not a full manual review of all 1,605 rows. See the extrapolation caveat above.

Usage

Requires Python 3.10+ (uses X | None union type hints); developed and tested on 3.14.

python -m venv .venv

# Windows
.venv\Scripts\pip install -r requirements.txt
.venv\Scripts\python -m src.main data/inventory_raw_large.csv

# macOS / Linux
.venv/bin/pip install -r requirements.txt
.venv/bin/python -m src.main data/inventory_raw_large.csv

Writes report.csv and report.html to the project root. Run the test suite with:

# Windows
.venv\Scripts\python -m pytest tests/ -q

# macOS / Linux
.venv/bin/python -m pytest tests/ -q

Design decisions

• Matching key: normalized (lowercase, trimmed) sku only. Normalization is used purely to group rows for dedup/conflict detection — the report always shows original raw values.
• Conflicting duplicates are never auto-resolved. Each is reported as its own exception, ranked above exact duplicates, showing both raw records, which fields differ, and the numeric delta for quantity/cost where both sides parse.
• Dead stock cutoff: 365 days, chosen as the standard "no sale in a year" retail/warehouse heuristic.
• 99999 is its own quantity-anomaly subtype (likely placeholder/default), kept separate from negative quantities (physically impossible) and ranked below them.
• Missing/unparseable quantity or cost is a validation failure, not a quantity anomaly. An anomaly is a value that parsed fine but is semantically wrong; a blank or non-numeric cell is a different failure mode entirely — you can't judge a value you can't read.
• Missing warehouse is a low-severity validation note, not a blocking failure — a meaningful share of rows lack it, which would otherwise dominate the validation-failure tier and bury the failures that actually block action (unparseable quantity/cost, missing product identity).
• A single row can appear in more than one category (e.g. a 99999-quantity row with a stale last_sale_date shows up as both a quantity anomaly and dead stock) — categories are detected independently rather than forcing one label per row.

Testing

74 tests, one file per module (tests/test_*.py), covering ingest parsing, each detector module, and report ranking/rendering. Run with .venv\Scripts\python -m pytest tests/ -q (Windows) or .venv/bin/python -m pytest tests/ -q (macOS/Linux) — see Usage above.

At 1,605 rows: all 74 tests still pass, the run completes in under a second, and exception counts scale roughly proportionally to the ~15.6x increase in row count over the original 103-row sample — no new structural edge case required a code change. The one pattern that scales up (not new, just bigger): a single SKU can now have more than two disagreeing versions — sku-5955 has 4 distinct conflicting records, producing 6 pairwise conflict exceptions for that one SKU (itertools.combinations over k clusters is O(k²) per SKU group). That's fine at this scale (max k observed is 4) but would be worth capping or restructuring if a future dataset had many more disagreeing versions of the same SKU.

Project structure

data/
  inventory_raw.csv          original 103-row sample
  inventory_raw_large.csv    1,605-row sample used for the results above
  manual_sample_200.csv      200-row sample used for the manual-baseline timing/accuracy check
docs/
  report_screenshot.png      top of the ranked report, embedded in Results above
  sku5194_conflict.png       SKU-5194 conflicting-duplicate row, embedded in Standout finding
src/
  ingest.py                 load CSV, strip blank rows, build normalized match keys, parse helpers
  models.py                 ExceptionRecord, Category/Subtype enums, category ranking weights
  validators.py             category 4: missing fields, unparseable quantity/cost
  quantity_anomalies.py     category 3: negative quantities, 99999 placeholder
  dead_stock.py             category 2: stale last_sale_date
  duplicates.py             category 1 + conflicting duplicates
  report.py                 ranking, CSV/HTML rendering
  main.py                   CLI entry point
tests/                      pytest suite, one file per module (74 tests)