Case study · In production · public code

RFDS Extractor

Gmail PDF scraper with a deterministic parser + optional Claude fallback

PythonpdfplumberGmail APIPyInstaller
30-second version
Problem
Operators opened hundreds of carrier RFDS PDFs monthly and copy-pasted ~30 fields each into a spreadsheet.
Built
Gmail-to-Excel extractor with signature-based template detection, deterministic pdfplumber parsers, a gated Claude fallback, shipped as one Windows .exe.
Result
1,518 RFDS attachments parsed in ~21 minutes in a documented run; packaged tool with a 9-module test suite.
Role
Sole engineer

The Problem

RFDS (Radio Frequency Data Sheet) PDFs arrive in a Gmail inbox from several major US carriers, each carrying ~30 structured fields per site (work-order ID, site ID, GPS, finance region, structure type, …). The existing process was a person opening each attachment and copy-pasting fields into a spreadsheet, every month, across hundreds of documents. It's slow, error-prone, and the layouts differ by carrier.

The tool automates the whole loop: pull RFDS attachments from Gmail, detect which carrier template a PDF is, parse it deterministically, and write monthly per-carrier Excel workbooks an operator can hand straight to reporting. Crucially, it ships as one Windows .exe so a non-technical operator runs it with no Python install.

Architecture

Gmail I/O and PDF parsing have opposite bottlenecks. One is network-bound, the other CPU-bound, so they run on different pools. Deterministic parsing is the default path; Claude is a gated fallback only for one carrier's PDFs when the parser can't read them.

rendering diagram…

gmail_client.py searches and downloads attachments over an OAuth'd Gmail service; a ThreadPoolExecutor keeps that network I/O from serializing. Each PDF's bytes go to a ProcessPoolExecutor, where pdf_parser.detect_template() matches template signature strings (a footer line, an internal URL, a document title, a field-name token) and routes to one of five per-carrier parsers backed by pdfplumber table extraction. If, and only if, a Carrier A parse is marked failed and ANTHROPIC_API_KEY is set, it falls back to a Claude (claude-haiku-4-5-20251001) pass. Rows are appended via an IncrementalWriter that saves the workbook after every row (and mirrors to JSONL), grouped into monthly files with one worksheet per carrier.

Key Decisions

  • Deterministic first, Claude only on failure. pdfplumber + signature matching parses standard templates in milliseconds with zero API cost and full auditability. The Claude fallback is opt-in (ANTHROPIC_API_KEY) and fires only for unrecognized Carrier A layouts; the model is a safety net, not the engine.
  • Two pools for two bottlenecks. Gmail fetching is network-bound (threads); PDF parsing is CPU-bound and GIL-blocking (processes). Splitting them keeps the I/O threads unblocked while parses run in parallel.
  • Incremental, crash-safe output. IncrementalWriter.add_row() saves after every append, so a run that dies at row 501 still leaves 500 rows on disk, with no need to re-pull from Gmail.
  • Month × carrier worksheet grouping. Email receive dates are converted to Eastern Time and bucketed into monthly workbooks (2026-03.xlsx), each with per-carrier tabs, matching how the ops team actually files reports.
  • Single-file .exe with bundled OAuth client. PyInstaller --onefile ships the tool with no Python dependency; the (non-secret) OAuth client ID is bundled while each user's token caches locally under %USERPROFILE%\.rfds-extractor\.

Code Samples

Template detection works by signature strings that route each PDF to the right carrier parser, with unknown as the explicit fallthrough:

# Marker strings are placeholders here; the real ones are template-specific
# footer lines, URLs and titles.
def detect_template(pdf_bytes: bytes) -> str:
    """Return one of: 'carrier_a', 'carrier_b', 'carrier_c', 'revision', 'completion', 'unknown'."""
    with pdfplumber.open(io.BytesIO(pdf_bytes)) as pdf:
        text = "\n".join((p.extract_text() or "") for p in pdf.pages[:2])
    if TEMPLATE_B_FOOTER in text:            # a fixed footer line
        return "carrier_b"
    if TEMPLATE_C_URL in text:               # a URL printed on every page
        return "carrier_c"
    if "NOTICE OF COMPLETION" in text:       # a document title
        return "completion"
    compact = "".join(text.split())          # table text often loses its spaces
    if "RFDSDetails" in compact and "SiteID" in compact:
        return "carrier_a"
    return "unknown"

The fallback is gated by three conditions: wrong carrier, a successful parse, or a missing key all skip Claude entirely.

if (
    isinstance(row, CarrierARow)
    and row.parse_method == "failed"
    and claude_fallback.is_available()
):
    row = claude_fallback.parse_pdf_with_claude(pdf_bytes)

Metrics

These are verifiable facts about the system, not curated outcomes:

  • ~2,350 lines of Python across 16 modules; 908 lines of tests across 9 test modules with real PDF fixtures
  • 6 document types (three carrier templates, a revision sheet, a completion notice, and Unknown) via 5 per-carrier parsers
  • 9 dependencies (pdfplumber, openpyxl, google-api-python-client, google-auth, google-auth-oauthlib, anthropic, tzdata, pytest, pyinstaller)
  • claude-haiku-4-5-20251001 as the gated fallback model
  • Ships as a ~56 MB single-file .exe (PyInstaller --onefile)
  • Documented real run: 1,518 RFDS attachments parsed in ~1,251s, written to monthly workbooks (119–836 rows/month)

What I Learned

  • Reach for the LLM last, not first. Deterministic parsing handles the overwhelming majority of real PDFs faster, cheaper, and more auditably than a model. Claude earns its place precisely because it's the rare-case fallback, not the default.
  • Match the concurrency primitive to the bottleneck. Threads for Gmail, processes for parsing: conflating them (one pool for both) would have either starved I/O or wasted the GIL. The split was the difference between a 20-minute run and a stalled one.
  • Save-as-you-go is cheap insurance. Per-row workbook saves cost almost nothing and turned "the run crashed, start over" into "the run crashed, resume from where it stopped."

GitHub repo. A working, packaged Windows tool with a test suite.