pinion-pdf/VERIFICATION.md

How the reference conversion was validated

pinion was developed against a real file: a 131,072-page, 360 MB PDF of a law-enforcement network-audit spreadsheet (1,048,576 rows × 14 columns, printed from Excel via PDF-XChange on Windows). Every design decision below was forced by something that actually went wrong on that file.

Independent checks the output passed

1. Row-number chain. Excel prints its own row numbers in the left margin. The reconstruction reproduces 1..1,048,576 with no gaps and no duplicates. Any method that drops or invents rows fails this immediately — a competing hand count of the same file reported 1,081,343 rows, which is exactly one phantom row per page (Trap 3) and also exceeds Excel's hard row limit, so it can be rejected on arithmetic alone.

2. Cross-engine spot check. 40 randomly sampled rows were re-extracted with pdftotext (Poppler — a completely independent PDF engine) and matched on ID, agency, and printed row number: 40/40.

3. Implementation-independent reproduction. Four separately written implementations (a hardcoded-geometry minimal script, a heavily commented teaching version, a parallel self-verifying version, and this package) produce byte-identical CSVs across all 1,048,575 data rows, verified with cmp.

4. Internal consistency. Every value in the ID column is a well-formed UUID (1,048,575 of 1,048,575); every row has exactly the same field count; zero text runs failed to map to a row or column.

Bugs the checks caught during development

These are the reasons the self-checks exist. Each was caught only by diffing against known-good output or by a check line going red:

• Single-character redactions are ~3.4pt wide — narrower than some grid marks. A size-based bar filter dropped 5 of them. Fix: filter by shape (taller than half a row, thicker than a hairline), not by area.
• Some blackout bars are filled four-sided paths, not rectangles. Visually identical, different PDF instructions. Inspecting individual line segments missed 4 bars, including one 121pt wide. Fix: measure whole filled regions, never segments.
• Two redaction bars cover the printed row number itself, making the gutter unreadable on those pages. Anchoring rows solely on printed numbers silently dropped both rows. Fix: anchor on geometry, use the printed numbers as a cross-check (printed numbers disagreed must be 0).
• A one-character join bug (" ".join vs "".join) corrupted the 191 cells whose text the PDF drew in multiple pieces. Caught by byte-diff.

Known artifacts in the source data (not conversion errors)

• 19 rows contain an unmapped ff ligature (the subset font has no Unicode mapping for it): Sex O�enses, Tra�ic. Exact-match filters on those strings will miss these rows.
• 141 rows contain double-encoded UTF-8 from the source system (100’s, –). Present in the PDF itself.
• The sheet ends at exactly 1,048,576 rows — Excel's ceiling — with the final row fully populated. Comparison of per-day record counts against the file's claimed date range shows several end-of-range days thinned or absent: the export was truncated at the ceiling, and roughly 17% of the original records are estimated to be missing. All totals from the file are floors.

Headerless mode (v1.1.0)

Validated against three monthly Johnson City TN PD audit PDFs printed via "Microsoft Print to PDF" without headings (48/74/69 pages; 2,032 / 2,437 / 2,607 rows).

Checks passed on all three:

1. Cross-engine spot check. 40 random rows per file re-verified against pdftotext: 120/120 after making the probes wrap-tolerant (a cell that wraps mid-value in the PDF never appears as one contiguous string in any text extractor's output — probe fragments, not lines).
2. Range sanity. Every two-timestamp Time Frame parses and runs start <= end (7,074 ranges, 0 disordered).
3. Anomaly counters at zero: no bad UUIDs, no unplaced runs, no strays in the Name/Org windows.

Bugs the checks caught during development:

• Wrapped header labels ('Total Networks ' / 'Searched' on stacked runs) made a whole column invisible to name-matching; its values landed in the Org Name window and were silently overwritten. Fixed by merging stacked header runs; guarded by the strays counter.
• A false-alarm verification failure: 43 spot-checks "failed" because the probe assumed cells appear contiguously in pdftotext output. Glyph level inspection proved the CSV was right and the check was naive. Kept here as a reminder: verify the verifier before trusting a red result.

Schema drift is real: of the three sibling exports, one has all 13 columns, two lack License Plate entirely, and Total Networks Searched moves between its own column and being fused into the ID run. Headerless mode takes each file's schema from its own header and reports what's absent.