# Supplier catalog imports A local application for reviewing supplier CSV updates before changing a parts catalog. It stages the source, validates each row, shows the before/after values, and commits an approved batch in one SQLite transaction. ## Run Python 3.12 or newer is required. There are no third-party packages to install. ```bash python app.py --seed --port 8011 ``` Open http://127.0.0.1:8011. The default database is `data/catalog.db`. Use `--db /path/to/catalog.db` to choose another location. The server binds to 127.0.0.1 and accepts only localhost Host and Origin values. | Demo account | Password | Permissions | | --- | --- | --- | | importer | import-demo | Stage CSVs, refresh stale reviews, read catalog and reports | | reviewer | review-demo | Importer permissions plus approve | | viewer | view-demo | Read catalog, batches, source and reports | These fixed credentials demonstrate application roles. They are not a production identity system. Sessions are in memory, expire after one hour, and disappear on restart. Keep the service on localhost. ## Try the supplied files 1. Sign in as `reviewer`. The seed catalog has three ACME parts. 2. Load `fixtures/price-update.csv` or use the sample button. Leave supplier ACME and price format en-US, then stage the file. 3. Review one added part, one changed price, one unchanged item and one missing item. Missing items remain in the catalog. 4. Approve the batch. The catalog advances from version 0 to version 1. The price of BRG-10 becomes USD 13.25, and SHIM-2 is added at USD 2.10. 5. Stage the same file again. Its supplier, source digest and mapping version identify the existing batch. Repeating approval returns its prior result without a second application. 6. Stage `fixtures/rejected.csv`. It is blocked: two normalized duplicate part numbers, an unsupported unit, and an invalid price are reported. Correct the source and stage a new file. A blocked batch cannot partly update the catalog. 7. For `fixtures/euro-prices.csv`, use supplier EURO and **de-DE**. The choice is part of the batch identity; price punctuation is never guessed. See `OPERATOR.md` for stale reviews, reports and recovery. `report.html` opens directly in a browser without running the server. ## Verify and reproduce measurements ```bash python tests.py python evidence.py ``` The test suite uses temporary SQLite databases and localhost HTTP servers. It exercises CSV quoting, normalization, bad rows, integer money values, same-file retries, stale reviews, two competing database transactions, a fault after the first row write, HTTP permissions, CSRF, Origin/Host checks and complete report downloads. `verification.json` records the executed result. The evidence script writes review, applied and rejected examples plus `results/size-sweep.json`. It stages and applies synthetic files containing 1,000, 10,000 and 100,000 rows. Each size runs in a fresh subprocess. The reported memory value is peak Python allocations measured by `tracemalloc`, not process RSS. Stage time includes parsing, diff generation and the staging transaction. Review latency measures loading a 100-row page; the implementation still deserializes the whole stored diff. Commit time includes upserts, catalog version, batch state, audit and commit. These timings describe the recorded shared execution host, not a service capacity guarantee. ## Data model and transaction rule `schema.sql` is schema version 1 (`PRAGMA user_version`). Source and normalized rows remain attached to the batch. All mutations use `BEGIN IMMEDIATE`, SQLite WAL mode, foreign keys and FULL synchronous mode. This is a **SQLite local reference implementation**, replacing the earlier PostgreSQL plan. SQLite serializes database writers; it does not provide the per-supplier row locks that a PostgreSQL deployment would use. An approval names the batch, catalog version and digest of the reviewed diff. The transaction checks all three before changing any row. A successful transaction updates items, increments the supplier version, records application state and appends the audit event together. A lost HTTP response is retried by batch ID. Direct SQL edits bypass these rules and are unsupported. Identity is supplier ID plus uppercase part number. Prices use integer cents after Decimal validation. Supported currencies are USD, EUR and GBP. Supported units are EA/each, BOX, KG and M. There is no exchange-rate conversion. A supplier may have different explicit currencies or units on different rows; the diff displays both values when they change. CSV headers may use the aliases in `ALIASES` in `app.py`; each required column must map exactly once. Extra columns are ignored. Source must be UTF-8 text, at most 16 MiB and 100,000 rows. Descriptions are limited to 300 characters. Amounts must be nonnegative with at most two fractional digits, up to 10,000,000 units. The source parser accepts quoted commas and quoted newlines. ## Files - `app.py`: parser, domain transactions, HTTP routes and demo sessions. - `schema.sql`: suppliers, items, batches and audit schema. - `static/`: dark review interface; no remote scripts, fonts or assets. - `fixtures/`: accepted, rejected and European-format CSVs. - `tests.py`: database and HTTP integration tests. - `evidence.py`, `results/`, `verification.json`: reproducible examples and measurements. - `report.html`, `OPERATOR.md`: review evidence and operating instructions. ## Reset and backup Stop the server first. To start with a fresh catalog without removing the old one, rename `data` to a backup name and start again with `--seed`; or pass a new `--db` path. SQLite creates its WAL and shared-memory files beside the database. For a filesystem backup, stop the service cleanly and copy the **whole data directory**, then point `--db` at the copied database. Never copy only an actively written database file. The provided examples and test outputs contain synthetic supplier data. No runtime database or browser session is included in the package. ## Limits This application is intended for a single local operator or a local workflow review. It has no TLS, external authentication, supplier API, unattended import, multi-tenant separation or production deployment configuration. Source storage and diffs are held in memory during processing; pagination limits the response size, not the parse memory. Import history shows the latest 200 batches, and the catalog panel previews the first 100 items; the catalog API supports paging. Reports contain the entire diff. No deletion is inferred from an absent row, and deleting or retiring parts is outside this workflow. The transaction tests inject process-level failures, not machine power loss or filesystem corruption. Original CSV downloads preserve source text and may contain spreadsheet formulas supplied by the uploader; the application displays values as text and does not execute formulas. Review a source file before opening it in a spreadsheet program. Primary API references: [Python csv](https://docs.python.org/3/library/csv.html) and [Python sqlite3](https://docs.python.org/3/library/sqlite3.html). ## Completed operations rehearsal The operations run checks 2,000 rows across eight suppliers with four concurrent writers, abrupt exit before and after approval commit, session loss on restart, an online WAL snapshot, restored business-row equality, corrupted snapshots and occupied destinations. ```sh python operations_rehearsal.py ``` Read `operations-report.html` for expected and observed outcomes, or `completion.json` for source hashes and machine-readable checks. `OPERATIONS.md` contains local startup, online snapshot, restore, periodic backup and explicit recovery assumptions. `operations.py` and `backup_schedule.py` are runnable tools, not pseudocode. The rehearsal uses temporary data and stops all of its services before exiting.