Every TV station files its political ad contracts as PDFs. Adding them up meant reading them one by one. I built the system that reads them, works out who is really paying, and links every dollar back to its source.
The situation
Political TV ad spending is public record. It sits in the FCC’s public files as thousands of PDF contracts, each station with its own layout, each buyer under its own name. A PAC, a committee and a campaign can all spend for the same candidate without one filing saying so.
The problem
The client needed one answer every week: how much did each side spend in each market? Getting it meant opening filings one at a time and doing the math by hand, while new ones kept arriving.
The approach
Template parsers break the moment a station changes its form, and stations change them without notice. So the system reads each contract the way a person would: language models do the reading, code does the checking.
Polls the FCC for 44 stations across the New York and Philadelphia markets, downloads every new filing and skips the ones it has already seen.
Decides whether each filing is political, then extracts every line item: advertiser, amount, dates, spots. Anything uncertain goes to a person, not into the totals.
Works out who is really paying. Advertiser names are matched to political entities and traced through PACs to the candidate they support.
Weekly spend by coalition, trends and breakdowns. Every number opens the original filing it came from.
Decisions and trade-offs
The PDFs change faster than templates can be fixed, so models do the extraction. Claude classifies and resolves, Gemini extracts, all behind one gateway, so swapping a model is a configuration change.
Chose LLM extraction over layout templatesBefore asking a model who an advertiser is, the system checks what it already knows, and every human correction becomes permanent. Lookups dropped from about 3 seconds to under 100 ms, and repeat advertisers cost nothing.
Chose a learning cache over calling the model every timeLow-confidence results go to a review queue instead of being trusted or thrown away. Reviewers can repair records that were already counted, not just fill in gaps.
Chose human review over silent guessesThe twist
Months in, an audit turned up something worse than a missing number. Almost half the spend records came from extractions that had already been replaced: reprocessing a document added new numbers without retiring the old ones. I rebuilt publishing so a document’s numbers switch over in one step, and wrote a repair that fixed the history without destroying anything.
The outcome
Live in production against the real FCC API, and built so the next race or market is configuration, not a rebuild.
For the technical reader
Supabase’s API offers no client-side transactions, so every multi-step write lives in Postgres functions that lock the row and re-check their preconditions under the lock. When two reviewers race, the loser writes nothing. Entity deletion uses FOR UPDATE NOWAIT so it yields to a concurrent alias insert instead of racing it.
1,492 Python tests at 92% coverage and 1,025 frontend tests, enforced as a pre-push gate. The integration tier went from 55–66 minutes to about 22 seconds without deleting a single test, by replacing per-test TRUNCATE CASCADE with a children-first delete.
Per-user auth with argon2id hashing, hashed session tokens, __Host- cookies and CSRF protection. Along the way I found and closed open table access (row-level security was off) and a secret that was being baked into every Docker image.
The internal React console talks only to the public API, never the database, with types generated from the live OpenAPI schema. If the frontend and backend drift apart, the build fails.
The console triggers a pipeline run and polls a runs table with a bounded interval. The backend has nothing to stream, so a push channel would only have been polling with more ways to fail.
Next project · Voice AI and evals · 2026
Got a pile of documents nobody can add up?