Return paperwork the receiving system accepts first time

Every unit coming back had to be announced to the warehouse system before it could be received against. The announcement is a workbook: three sheets, two rows of headers, a hundred and one columns, and one seeded example row carrying the standard defaults. The builder sets about ten of those columns. The rest are left exactly as the template has them.

Filling that in by hand is slow, and slow is the least of it. Three things in particular have to be right, and none of them looks important: a quantity written as a fixed five-decimal string rather than a plain number, a product code the customer's item master actually knows, and no gap in the middle of the rows where a filtered line used to be. Whether they were right is answered after the upload, on a results page, in front of whoever is standing there.

• Line detail arrived as a spreadsheet and had to be loaded into the tracking tables before anything could be built from it
• The only record of an upload was a boolean, and it said uploaded whether or not the import had been accepted
• A document whose stored file could not be retrieved left its row at not-uploaded forever, with no trace anywhere
• A refusal appeared on the portal's results page and nowhere else

The document is now a by-product of checking the return in rather than a task that follows it — the check-in path builds it directly. Detail lines are read from the tracking tables, grouped by product code and summed, filtered against the customer's item master, and written into the template. The finished workbook is stored in the database as a blob and queued for upload, so nothing depends on a file staying put on a particular machine.

Alongside it sits a manager screen over the line detail, because the data that feeds the document has to be right before the document is built. A second path builds the same document from a typed form for the returns that never came through the automatic route, using the same store, the same queue and the same uploader.

• Every field edit is its own request against an allow- listed column map — twenty-one fields, and anything not on the list is refused
• Edits apply immediately and roll back the individual control when the server rejects them, then quietly re- read from the database
• A new detail line gets its database row lazily, and the creation is memoised so a double-click cannot create two
• Expanding a return twice issues one fetch, not two
• Clearing the received date unwinds the lines under it — quantity received, audit flag, audit date, reconciliation — and writes each of those back rather than only to the screen
• The source workbook can be dropped on the page; sheets are matched on the headers they carry, with position used only as a fallback, and re-importing replaces those returns instead of duplicating them

Most of the work in this build is in the columns nobody looks at.

The template ships with one seeded example row, and that row carries values in a good many more columns than the builder writes. Overwrite it in place and the first generated line is fine; the second lands on a blank row, missing values the import expects. So the fields that vary per line — owner, pack key, line number — are written on every generated row rather than left to the template's first row, and the sibling builder for a related document goes further, copying the template's default row onto each line before overwriting only the fields it owns. That is the sort of bug that first appears on the day a return has more than one product code on it.

Filtering has a related trap. Lines whose product code is not in the item master are dropped before writing, and dropping rows from a table without re-indexing leaves the survivors carrying their original positions — which, in a fixed-layout sheet, means blank rows in the middle and lines written past the end. The index is reset deliberately for that reason. Quantities are stored as text in the source, so they are converted to numbers before they are summed; otherwise the totals are digits concatenated rather than added. They go back out as fixed five-decimal strings, because that is the format the import wants.

• Sheet names are resolved case- and plural- insensitively, and a missing sheet raises an error naming the sheets the file actually contained
• The automatic path filters against a static export of the item master; the typed-form path queries the item master live, for the owner account chosen on the form
• A failure to reach the item master is reported as 'could not validate', never as 'invalid' — the two have different fixes
• A reference that already has a document on file is refused rather than duplicated, because the uploader matches rows by reference alone

There is no write interface to the warehouse system, so the upload drives its own import screen in a browser. Everything about that arrangement is fragile, which is why the failure handling is where the design effort went.

Every document carries an explicit state — pending, uploading, uploaded, failed — with a one-line reason attached to the failure. The reason is assembled deliberately: a browser-automation timeout stringifies to an empty message, which used to store a blank reason on the row, so the exception class is always led with and the message line kept only when there is one.

The upload fails closed. An earlier version raised its error inside a block that caught and discarded it, so the caller always saw success. Now a detected error on the results page propagates, and a results page that cannot be read at all is treated as a failure rather than an assumption. A document whose stored file cannot be retrieved raises instead of returning quietly. The sweep that picks up pending documents runs inside a background loop that holds a database-level lock, so exactly one process in the fleet runs it no matter how many servers are up — and the status writes themselves are best-effort and never raise, because a problem recording the outcome must not fail an upload that actually worked.

• Documents live in the database, so a failed upload can be downloaded and submitted by hand without hunting for a file
• The related document flow retries once, after a delay chosen to outlast the one-time-passcode window — retrying inside it just re-sends the code that was already rejected
• Status columns are added idempotently at runtime, and rows created before status tracking existed fall back to the old boolean rather than showing blank
• A pass that completes kills any browser processes still running afterwards

The document is generated from the check-in, the line detail behind it is editable in one place by the people who know what changed, and every edit is written straight through rather than staged in a spreadsheet somebody has to remember to send.

The question that mattered most now has an answer. Not 'did we build the document' but 'did the receiving system take it' — every document carries its own state and, when it failed, the reason it failed. The narrow lesson we took away is that when a system's only interface is a file format, the format is the API, and the parts that look like decoration are load-bearing: the seeded example row, the duplicated header, the trailing decimals, the empty columns you are supposed to leave alone.