Audience intelligence for a TV network

Context

A Brazilian broadcast network produces audience reports every day: minute-by-minute curves per program, daily grids with every broadcaster’s share, top-five rankings, monthly reports per market, targets, social listening, football matches. Thousands of PDF pages, in about a dozen report families, and growing. The programming team and the board want to ask questions in Portuguese (“what if we launched a cooking reality show at 10 pm in one of our regional markets?”) and get an answer grounded only in those reports, with the page it came from. Analysts keep it current by uploading new reports; nothing else is manual. The constraints are real: one Linux server the station owns, Docker Compose, a fixed budget, a demo at the end of each month, and the LLM bill paid by the client.

My role

I’m the only engineer on it, under contract since August 2026. I wrote the design spec and the three architecture decisions before the first line of code, and I’m building it in the order the spec sets.

The decisions: the ingestion queue is the documents table itself in Postgres (status, attempts, a lock timestamp, claimed with one SKIP LOCKED statement), because Temporal or Celery would add containers and a vocabulary nobody at the station knows, for a single kind of job; extraction is hybrid per page, because pure RAG can’t average hundreds of rows and thirteen hand-written parsers would break at the first layout change; and every model call (chat, extraction, classification and embeddings) goes through a LiteLLM proxy, so the app holds no provider key and swapping a model is a YAML change.

Built so far: the schema (one long-format metrics fact table behind a view, page embeddings under an HNSW index), accounts and cookie auth, documents deduplicated by hash with a bulk import CLI, page rendering and text extraction with timeouts, the header and digest agents with deterministic output validation and escalation to a stronger model, and the extraction eval suite. Next up: the pipeline end to end, the chat agent with its four tools, and the Vue app.

1. 01 Reports in
   • upload from the web app
   • bulk import from the CLI
   • deduplicated by SHA-256
2. 02 Queue the documents table itself, no broker
   • status, attempts, lock timestamp
   • claimed with one statement SELECT … FOR UPDATE SKIP LOCKED
3. 03 Worker one document at a time, one transaction per result
   • render pages: pdftoppm and pdftotext, with timeouts
   • header agent on page 1: family, market, program, period
   • digest agent on every page: summary plus metric rows
   • validation: drop and count, retry with reasons, escalate to a stronger model
4. 04 Store PostgreSQL + pgvector
   • metrics long-format fact table behind one read-only view
   • pages summary and embedding, HNSW index, filtered search
5. 05 Chat agent PydanticAI, four tools, streamed over SSE to the Vue 3 app
   • search_pages
   • query_metrics
   • read_page
   • list_programs
   the answer carries [n] citations that open the page image

LiteLLM proxy every model call (chat, extraction, classification, embeddings) goes through one proxy; the app holds no provider key and swapping a model is a YAML change

The hard part

The contract between the model and the database. The model reads a page (image and text) and proposes a summary plus metric rows. It’s never trusted. Deterministic validation runs as the agent’s output validator: aliases normalized, values bounded by metric type, required fields per granularity, every value present in the page text, duplicates dropped by natural key. Rows that fail are dropped and counted, never patched. If too many drop, the agent is retried with the rejected rows and the reasons; if it still fails, the page goes to a stronger model, and if that fails the document ends “partial” with the page marked. The eval suite measures precision and recall per page on a hand-checked sample of thirty real pages and projects the cost of the whole backlog; both gates are fixed in code, not tuned at the threshold.

The second: the same fact appears in five report families under different targets. A program’s share on one day is in the minute report, the daily grid, the top five, the month-to-date update and the monthly report. An aggregate that doesn’t filter family, target and granularity is wrong while looking right. That’s why the metrics table is long format (one row per number) and the SQL tool the agent gets is read-only over one view, on a database role that can’t see anything else.

Client tool (in development, screenshot pending)

Outcome

In progress. The first milestone, ingestion, is nearly closed; the chat and the web app are the second and third. Everything runs on one server with one compose file, which is the point.