Supplier catalog imports
CJ Ackley | SWE | Project brief

STATUS
Import design brief; source formats to confirm. Implementation and tests are planned.

THE PROBLEM
Purchasing receives CSV files with changed column names, duplicate part numbers, and mixed price formats. A bad import can overwrite usable records. This tool stages each file, shows a row-level diff, and applies an approved batch in one transaction.

DESIGN QUESTION
Can an operator explain every changed row and safely retry an interrupted import?

SCOPE
- Use a Python parser and PostgreSQL staging tables; preserve the source file and mapping version.
- Build a review screen for added, changed, rejected, and missing items. Missing rows never imply deletion.
- Commit approved changes atomically; identify repeated batches by supplier and file digest.

CONSTRAINTS
Identity: Supplier ID plus supplier part number
Values: Explicit currency, decimal prices, and unit mappings
Approval: Catalog changes require a reviewer
Scope: CSV inputs; one catalog and a defined schema

ENGINEERING DECISIONS (PROPOSED)
Catalog version checks at approval
Stage the whole file and attach the review to a supplier catalog version. At approval, lock that supplier, check the version, apply the batch, and increment the version in one transaction. Every catalog writer must follow the same version rule.

ALTERNATIVE
Applying valid rows as they arrive is simpler, but leaves a partly updated catalog when a later row fails.

TRADEOFF
A concurrent edit invalidates the whole review, even if it affects only one item. That costs reviewer time but keeps the approval understandable.

PROPOSED TOOLS AND METHODS
Python, PostgreSQL, CSV fixtures

VERIFICATION SCENARIO (NOT YET RUN)
Two reviewers approve overlapping imports
Stage two files against the same catalog version. Approve them concurrently, then retry the accepted request after dropping its response.

PASS CONDITION
One version change commits. The stale review changes nothing. Retrying the accepted batch returns its existing result; the import history contains one application.

EVIDENCE TO RETAIN
A database integration test with explicit transaction barriers, before/after catalog queries, and an import audit record.

MEASUREMENT AND LIMITS
Use synthetic files of 1,000, 10,000, and 100,000 rows. Record parsing memory, review-query latency, and commit duration on a documented machine. These are proposed test sizes, not current capacity claims.

COMPLETION CRITERIA (PLANNED)
- A fresh database can import and review the supplied example files.
- Malformed rows, repeated requests, and overlapping approvals pass the integration suite.
- The README includes one rejected import and explains how to correct it.

VERIFICATION PLAN
Bad rows
Reject ambiguous keys and invalid values without changing the catalog.

Retries
Interrupt the commit path, then resubmit; no row may be applied twice.

Concurrent edits
Reject approval if the catalog changed since the reviewed snapshot.

PLANNED DELIVERABLES
- Import service, review UI, and schema migrations
- Malformed-file fixtures and transaction-level integration tests
- Local setup, sample supplier files, and an operator guide

SCOPE NOTE
First release covers one catalog and CSV files. Supplier API connections and unattended imports are outside this scope.

NEXT STEP
Agree on the catalog schema and collect representative supplier files.
