A pipeline that finds the right data in the wrong documents

The problem

A large US law firm needed bank payment details extracted from approximately 1,300 vendor PDF forms — bank names, account and routing numbers, SWIFT codes, ACH and wire flags, addresses — and loaded into a production database that drives real vendor payments. The forms were inconsistent. Some were image-only scans. Some had handwritten fields. Some had checkboxes. Manual extraction was not viable at this volume.

The architecture

Everything revolves around a single CSV file — running_list.csv — one row per PDF, tracked by a Last Successful Step column. Thirteen numbered scripts run in sequence. Each script does one job and skips rows already past its step unless forced. The pipeline is resumable from any failure point and idempotent by design.

This was not a design decision made upfront. It emerged from the first real question: what happens when a Claude API call fails on row 847 of 1,300? The answer had to be — resume from row 847, not from the beginning.

Eight decisions that shaped the system

1. Built the core pipeline

Scripts 01-05 build the row list, classify image-only PDFs, gate on the payment form phrase, count pages, and parse vendor numbers from filenames. Script 06 extracts via Claude — rendering the header page ±1 and sending to the API with a structured prompt. Script 07 is regex fallback — only for rows where the Claude API call itself failed technically, not a quality fallback.

2. The phrase gate was too brittle — built step 08

A real vendor form exposed a false negative. The form contained payment data but the exact phrase step 03 searched for appeared differently. An exact string match was not the right gate for a document corpus this varied. Added script 08 as a full-document, uncapped second-opinion pass that trusts Claude's own judgment about whether payment data is present — instead of a literal phrase match.

3. Model selection from evidence, not assumption

Ran Haiku 4.5, Sonnet 5, and Opus 4.8 head-to-head against five ground-truth documents. Haiku produced two dangerous errors — an inverted ACH/Wire checkbox and an invalid routing number. These are the fields that determine who gets paid and how. Opus edged out Sonnet on accuracy.

The initial cost projection for switching to Opus was approximately $597 — which was enough to pause. The number turned out to be based on a hypothetical 10,000-file run, not the actual corpus of 1,236 rows. Recalculated: true cost delta was $21-25. Switched the default to Opus 4.8. The decision had nearly been made on a cost estimate off by 25x.

4. Production image failure — root-caused and fixed

Some pages rendered to 10-18MB PNGs at 150dpi — dense scanned or photo content — exceeding Claude's 10MB image cap. Confirmed across three real rows in two 100-row test batches. Fixed by falling back from PNG to JPEG above an 8MB threshold.

5. Built the export, archive, and reporting layers

Script 10 exports to JSON. Script 11 exports to a verified SQL INSERT statement matching the real production schema, with a round-trip check. Script 12 archives PDFs by content hash — so a same-named file reprocessed across batches does not silently overwrite the earlier version — plus full run snapshots. Script 13 generates a business-readable prose summary of every run.

6. Real accuracy audit — found and fixed two prompt bugs

Ran a 12-document audit using parallel agents against source PDFs.

bank_address was synthesising city, state, and zip onto the field because the prompt schema explicitly said to include them. Fixed by mirroring the wording of the vendor address field. Verified on three affected rows.

vendor_name was sometimes pulled from an internal confirmation contact field instead of the form's actual labeled Vendor Name field. Fixed with an explicit prompt rule. Verified on both affected rows.

7. The most severe issue — the empty window recovery gap

A nine-page vendor packet where the header phrase appeared on a blank template page but the real bank data was several pages later, outside script 06's header ±2 extraction window. The row was being marked confirmed no data permanently because step 08 only covered rows where the phrase was never found at all — not rows where the phrase was found but the window came back empty.

Fixed step 08's row-selection logic to cover this case. Re-running it recovered the affected row plus four more containing real data out of ten rows in that state. This fix has only been run against approximately 200 of 1,236 rows. It is still owed on the rest of the corpus.

8. Silent row-drop risk — mitigated, not fixed

Script 01 diffs against the current source directory listing and silently drops rows for any PDF no longer present — confirmed this applies to subfolders too. Not fixed at the source. Mitigated: script 12 now archives running_list.csv and the extracted data file on every run, and content-hash-archives every PDF independently, so data survives even if source files are removed or renamed between batches.

Three things explicitly open

1. Approximately 1,235 rows are still stamped with the old extraction model from before the model switch. Not yet backfilled to Opus.
2. The empty-window recovery pass has only been run on approximately 200 of 1,236 rows. The rest is still owed.
3. Approximately 30% of regex fallback rows — 1,209 total — have corrupted Bank Country values. The ACH/SWIFT selection logic depends on this field. Decision pending: full Claude reprocessing at approximately $70, or a narrower targeted fix.

What this is not ready for

A full accuracy audit has been run on 12 documents — one clean, ten with minor issues, one significant, all fixed. The recommendation is 20-30 documents before trusting this dataset for live payments.

What I would change

The phrase gate in step 03 should never have been an exact string match on a corpus this varied. The right first design was a semantic gate — Claude deciding whether a form contains payment information — with the exact-phrase check as a fast pre-filter only. Building step 08 as a recovery mechanism was the right fix but it added complexity that a better initial gate would have avoided.

The silent row-drop in step 01 should be fixed at the source — a tombstone column in the CSV, not an archiving mitigation downstream. The mitigation is safe but it obscures the underlying risk.

Stack

Python · Claude API (Opus 4.8 default) · MySQL · S3 · PDF rendering via pypdfium2

Status: Production — corpus processing in progress. Three known issues open. Accuracy audit recommended before live payment use.