Automated payroll processing for Antigua Guatemala
An automated pipeline that turned scanned municipal payroll PDFs into a clean, machine-readable dataset — AWS S3, Textract, and LLMs in the loop on the flagged rows — replacing a fragile manual extraction workflow.
- ~75% reduction in payroll processing time
- ~90% reduction in per-cycle cost
- Manual PDF workflow decommissioned
Context
The Municipality of Antigua Guatemala publishes its payroll the way most public bodies do: as scanned PDFs, posted to a transparency portal, machine-unreadable. The data exists; the access doesn't. To turn it into something anyone could analyze — citizens, journalists, the municipality's own finance team — every cycle a person sat in front of those PDFs, extracted the rows by hand, sanity-checked them in a spreadsheet, and hoped nothing got mistyped.
That manual loop was the bottleneck. Delays were predictable; errors weren't. And both were blocking real governance and financial analysis downstream.
Role
Data Engineer and Product Lead. I owned the architecture, the pipeline implementation, and the handoff into a clean dataset the analytics layer could query. Goal: replace the manual PDF loop with a pipeline that ran on a schedule and was right almost every time, with a human in the loop only on the rows the system itself flagged.
What we built
A five-stage pipeline:
- Secure ingestion to AWS S3. Scanned PDFs land in a staging bucket the moment they're published, with lineage metadata captured at write time so every downstream row traces back to its source document.
- PDF conversion and text extraction with AWS Textract. Each page is converted to high-fidelity images and Textract pulls structured rows out. For the vast majority of pages this single step is the entire extraction.
- Robust quality checks. Typed schema checks, referential checks against the employee master, totals reconciliation against the per-page summary. Anything that fails gets routed to a flagged-rows queue with the original screenshot attached.
- LLM extraction on flagged rows. The flagged screenshots get a second pass through an advanced LLM tuned for tabular OCR. Catches what Textract missed without sending the entire corpus through an expensive model.
- Staging environment for analysis. Final reconciliation against the prior period, then the clean dataset is loaded into the analytics staging layer where the dashboard work picks up.
Stack: AWS S3 + AWS Textract + LLMs in the loop + Python orchestration.
Product decisions worth writing down
Two-tier extraction beats one-tier at any cost. Sending every page through an LLM is too expensive and too slow. Sending every page through OCR alone leaves errors in the dataset. Letting Textract handle the easy 90% and routing the flagged 10% to LLMs gave us accuracy with sane economics.
Quality checks are the product. The pipeline isn't really "PDFs in, rows out." The pipeline is "PDFs in, a clean dataset whose lineage you can defend to a journalist out." The validation layer is what makes that defensible.
Flagged rows ship the original screenshot. When the validation layer flags a row, the manual reviewer needs to see what the system saw — not a description of the failure. Attaching the screenshot to the flag is the difference between two-minute review and two-hour review.
Outcome
The manual PDF workflow was decommissioned. End-to-end payroll processing time dropped by ~75% and the per-cycle cost dropped by ~90%. The output dataset is now the input to the public-facing dashboard and to the analytics work in the companion case study. Next on the roadmap: a text-to-SQL agent over the same dataset for advanced financial analysis without writing SQL.