Inventory variances that only close when they're actually gone

An emailed variance report cannot date a disagreement because each workbook is a snapshot with no memory: a resolved line returns looking new, and the root cause lives in whichever copy someone saved. We load every report into a register by upsert, keep the original occurrence date, change-log twelve fields, and let only the system close a line.

There was no feed, and no trading-partner integration to build one on. The reconciliation between the two systems was produced by somebody else's process and delivered as a workbook attached to a recurring email — a summary sheet plus the raw on-hand extract from each side. Whoever opened it worked it in place.

A snapshot has no memory, and that is the whole difficulty. Two consecutive workbooks look identical whether a disagreement is new or months old. A line somebody investigated and resolved comes back on the next report looking exactly like a new one. And the decision — the root cause, the ticket reference, the person who made the call — lived in whichever copy of the file you happened to be holding. The 940 and 945 guide explains why receipts and adjustments end up in a spreadsheet emailed on Friday in the first place.

• The reconciliation arrived as an email attachment, not an interface
• Each workbook was a snapshot with no relationship to the last one
• Nothing recorded how long a given disagreement had been open
• Classifications and resolution notes did not survive to the next report

A loader that pulls the workbook out of the mailbox and a persistent variance register behind it, with a per-record change log and an editable screen for the columns that require human judgment.

The load is an upsert, not an import. Records match on part identifier, organization and the disposition that arrives in the same cell, against anything not already closed, and a match updates the quantities in place while leaving the original reported and occurrence dates alone. That single decision is what makes the age of a disagreement meaningful — re-running the same report ten times does not reset any clock, and does not create ten records. The previous variance is kept alongside the new one, so the screen can show which way a line moved since the last report rather than only where it stands.

• Rows with no quantity difference never become records at all
• Days outstanding is computed in the database from the original occurrence date, and only accrues while a record is still being worked
• Both sides' raw on-hand extracts are written to their own tables on every run — truncated and repopulated, so the figures on screen can always be traced back to the rows of the current report
• Two ticket references per record — one for each side of the trading relationship, split out of a single earlier column by a migration that runs itself
• Two ways in — a mailbox pull and a manual upload — landing in the same folder and running the same loader over the newest workbook
• Four charts and a filterable, sortable table over the whole register, paged at a hundred rows

The input is generated by a process nobody on this side owns, which means the parser has to survive it changing shape. It tries the expected layout first and falls back to one with two rows of preamble before giving up. Three workbook formats are accepted and the read engine is chosen per file extension. Attachments that are not spreadsheets are skipped rather than optimistically parsed.

Two traps in that file were worth the code they cost. Blank numeric cells arrive as NaN, and NaN is truthy in Python — the obvious idiom for defaulting a missing number silently keeps the blank instead of zeroing it, so every quantity is coerced explicitly and non-numeric values are mapped to zero. And the identifier column carries two values in one cell separated by a slash, while some identifiers legitimately contain a slash themselves; the split is on the spaced form of the delimiter, not the character, because the naive version quietly truncates real part numbers.

The mail side is deliberately narrow. The mailbox credential is scoped to read and send only, so the loader can never alter or delete the source message — the evidence for any reconciliation stays where it arrived. When a stored credential cannot be refreshed, the mail helper logs and returns nothing rather than raising, and every caller checks for a null service and gives up; the load runs on a thread spawned by a web request, and a failure there should surface as no new data, not as a traceback nobody is watching. A run that fails to load anything also never advances the last-updated stamp the screen displays, so a stale dashboard looks stale instead of looking current.

A record moves through four states, and a person may set three of them. The fourth cannot be set by hand: the API rejects the attempt with an explicit message, and the screen renders a closed record as static text with no control on it. Closed means exactly one thing — the disagreement is no longer on the partner's report — and it is not an opinion anyone is allowed to hold.

After each successful load, a sweep closes every record for that organization that was in the register but absent from the new report, writing a change-log row for each. The sweep is gated on the load returning success, so an unreadable workbook or an empty mailbox skips it rather than closing an organization's open list against a file that never arrived.

The rule runs the other way too. A line somebody marked solved that reappears on the next report with a real difference is reopened automatically, its solved date cleared, and the reopen written to the change log under the system's name. Optimism about a fix does not survive contact with the next report.

• Field edits are validated against an allowlist of twelve columns before the column name reaches SQL
• Quantities, computed variance, organization and part identifier are not on that list — nobody edits the numbers the report produced
• Every change records the old value, the new value, the user and the time, viewable per record
• System-driven transitions land in the same log as human edits, attributed to the system
• Bulk edits fan out as individual writes, so each affected record gets its own log entry
• Classification is a closed vocabulary — seven transaction error types and a fixed root-cause list — so the same problem is named the same way twice
• All seven routes require an authenticated session; the query and edit endpoints run on a bounded worker pool and return a timeout rather than holding a request thread

The register carries what the workbook could not: how long each disagreement has been open, which direction it moved since the last report, who classified it and when, and which side's ticket it is sitting behind. Items leave the list when the underlying difference clears, rather than accumulating until people stop reading it. It is a reconciliation report for when two systems disagree, kept as a register instead of a file.

The schema maintains itself on the way in — tables are created if absent, new columns are added idempotently behind existence checks, and the older single-ticket field was backfilled into the first of its two successors on first run — so the register could grow new fields without a migration window on a system people use daily.

The honest observation from this build is that the arithmetic was never the hard part. Subtraction is subtraction. The work was deciding which facts belong to the machine and which belong to the person, the same discipline as naming the system of record before building the interface, writing that boundary into the API rather than into a policy document, and then refusing to let either side overwrite the other.

Emailed workbook or variance register