In 2024, the election coverage I worked on depended on a very expensive third-party system, which went down right on the night of the count and nearly turned the coverage into a total loss. In 2026 the decision was different: instead of buying a ready-made solution, another developer and I built the pipeline in-house in about three months, with six small services and a single daily schedule.

The first post in this series told the story: three encounters with Brazil’s electoral data, and the third time the build was mine to lead. This post is that build — what the TSE (Brazil’s electoral authority, which runs every election and publishes the data behind it) actually publishes, how that data lands, and how it became a structured model an API could serve.

What the TSE actually sends

In July 2026, the TSE held a technical hearing with newsrooms and institutions that plan to redistribute the official results. The numbers presented there give a sense of the size of the system everyone in this space has to integrate with.

When polls close at 5 p.m. Brasília time on election day, a memory card comes out of every electronic voting machine in the country and starts a journey: collection points, validation, the TSE’s data center, vote totalization, publication. What lands on the public distribution network covers every level of aggregation — the country, 27 states, 5,569 municipalities, 6,883 electoral zones, and each of roughly 464,000 individual polling sections. Four files per polling section come straight from the voting machines, untouched: about 1.86 million files from that alone. Add candidate photos, digital signatures, and configuration files, and the full set approaches 2.5 million files per election cycle — and starting this cycle, the TSE keeps two cycles online at once.

Consuming that volume has rules. There’s no way to list what exists: every system consuming the data must construct each file’s path from a naming convention. Each IP gets at most 100 requests per second; exceed it and you’re blocked for ten minutes. Insist on requesting files that don’t exist yet and the block comes early. The TSE runs full-dress rehearsals before election day — simulated totalizations going from 0 to 100% — precisely so that every newsroom’s system can be tested before the night that matters.

That’s the context this pipeline operates in. The part it consumes is the one that exists long before election night: candidacy data — who is running, for what office, where, and with whom.

The candidacy files, up close

The TSE publishes candidacy data through an open catalog. Each resource is a ZIP containing a CSV, and a metadata endpoint tells you when a resource was last modified. Once a day, a scheduled task triggers the collector to check that date: if it changed, there’s new data to fetch; if not, nothing else runs. The schedule decides when to look; the data decides whether anything happens.

The files cover the main registration data (one line per candidacy), a complementary file, asset declarations, social media handles, and the number of seats in dispute per office and locality. Compared to my first contact in 2020, when the documentation contradicted itself across a PDF and a video and neither matched the actual files, this is a real improvement — the catalog is queryable and the files are internally consistent.

They still have their quirks, though. A sample of what the code has to handle:

  • Everything is semicolon-delimited, double-quoted, and encoded in latin-1, an old character standard, rather than UTF-8, the current one. In 2026.
  • The TSE’s servers drop the connection (socket hang up) if you request files too quickly. It’s the informal version of the request limits the results network enforces formally. The collector waits 500 ms between calls and, when one fails, retries while waiting longer each time (exponential backoff).

And those are just the two worth telling. Behind them sits a long list of smaller ones: none of them hard, none of them interesting, all of them mandatory. That’s what this work is really like: not one difficult problem, but a pile of trivial ones, each discovered in practice, and every one solved before the first useful row lands in the database.

The pipeline, briefly

Six services, all containers in a managed cloud. Instead of an orchestrator, the central tool that usually directs every step of a data pipeline, they talk through message queues: one service finishes its work, leaves a notice on the queue, and the next one starts when the notice arrives.

TSE open data catalog
  → collector (Node.js, one-shot serverless job, triggered daily)
      → object storage (ZIP → CSV) + queue message
  → processor (Python, event-driven)
      → staging table → dimensional model (Postgres)
  → migrations service (owns the schema, runs as a one-shot job)
  → REST API (Python, reads the model)
  → gateway (thin Node.js proxy in front of the API)
  → article generator (consumes the API, drafts editorial content)

The collector fetches ZIPs and drops a message on the queue. The processor is triggered by the message, reads the CSVs, and loads Postgres. The schema, which is the definition of the database tables, lives in a separate repository that applies structural changes (migrations) as a task that runs once and exits. No service changes the schema as a side effect; one repository exists just to look after it.

There’s no orchestrator anywhere in the six repositories, and exactly one schedule: the daily trigger that starts the collector. Everything past it is event-driven, meaning each service reacts to a message, not to a clock. For a pipeline whose real trigger is “the TSE published new data”, that split turned out to be the one closest to reality: one cheap scheduled check at the entrance, and no service downstream running just because the clock said so.

The staging table

The main candidacies file lands in a staging table first, a preparation area that keeps the data as it arrived: 49 columns, almost all raw text, deliberately no primary key. It’s a faithful copy of the CSV, structured just enough to be queryable.

Processing runs in two stages. First, empty the staging table and bulk-load every state’s file into it. Then upsert (insert or update in a single statement) the final model’s tables from staging, all at once, in SQL. Raw first, transform second: if a transformation is wrong, the raw data is still sitting there to re-run against, and the investigation happens in SQL, not in a file parser’s error trace.

The part of this design that paid off most isn’t the staging table itself but the schema guard built on top of it: a check that stops the pipeline from writing to the database when the table structure isn’t what the code expects. The upsert logic builds every column list from the table metadata, with nothing fixed in the code, and validates the schema twice: once when the code is loaded, and once at runtime against the live database’s information_schema — before any cleanup or write runs.

That guard got tested by a real change mid-project. The primary key of the electoral-units table had to grow from two columns to three, and another table dropped its surrogate key in the same migration. The change that adapted the processor touched three files. And what matters is what would have happened without it: not silently corrupted data, but an explicit schema-out-of-sync error that stops the pipeline, a crash loop anyone can detect.

That’s what “the staging table insulates the pipeline” actually means in practice. It doesn’t mean the pipeline never feels a change. It means the damage stays contained and the failure shows up right away instead of staying hidden.

A model drawn from three elections

Here’s the part I’d have loved to make sound more scientific: the dimensional model, the way the data is organised into tables so it can be queried, wasn’t discovered by an algorithm. It was drawn by hand, in a diagram, before most of the code existed.

What made that possible was the accumulated reverse-engineering of two previous cycles. The weeks I spent in 2020 checking contradictory documentation against the actual files — understanding each entity from what the data showed, not from what the specification said — is where the map of this data formed: which columns describe a candidate, which describe a party, which describe the election itself, and which quietly serve to link one row to another.

The best example of that last category: the running mate. In the TSE’s flat file there is no “vice of” column. A president and a vice-president are just two rows, each with its own candidate ID. The link only exists if you know to look for it: two rows sharing the same ballot number, election, and locality — but with different office codes. In the codebase that knowledge is a small map:

RUNNING_MATE_MAP = {
    PRESIDENT: VICE_PRESIDENT,
    GOVERNOR:  VICE_GOVERNOR,
    MAYOR:     VICE_MAYOR,
}

That mapping lives in the layer that talks to the database, with a comment explaining why: which offices have running mates is a business rule of the data, not an API detail. No document from the TSE states it. It’s the kind of knowledge you only have after you’ve already got it wrong with this data.

The model that came out of the diagram:

  • Dimensions, the tables that describe each entity: election, party, office, candidate, and electoral unit. Every one of them is identified by a composite key that includes the election ID, because in this data almost nothing is stable across elections. Party numbers get reused, offices vary by cycle. The election isn’t an attribute; it’s part of every key.
  • Detail tables for assets, social media, and complementary data, linked to the candidate.
  • A single bridge table, seats in dispute, which links two dimensions: office and electoral unit.
  • And one intentional decoupling: a table for editorial annotations on candidates that has no foreign key to the candidate dimension, meaning the database doesn’t require the annotated candidate to exist at that moment. The migration documents the reasoning: the ingestion pipeline is asynchronous and the upstream data can be inconsistent, so editorial data must survive even while the candidate’s row is being rewritten. A weak relationship, chosen deliberately.

The first client

The API’s main client today isn’t a screen — it’s another service: the article generator, which consumes the model and turns election data into draft editorial content, filtered by office, state, or municipality. The two services grew in parallel, and the API evolved around that client’s needs. You can see that relationship in how they fit together now:

  • For offices with few candidates, the generator makes one extra call per candidate to fetch the municipality name and the running mate — fields the list endpoint doesn’t expose. For high-volume offices like city councillor, it skips those fields rather than make thousands of calls.
  • Its retry policy has one intentional exception: a “not found” response is never retried. The TSE enforces the same lesson at scale — on its results network, clients that keep requesting files that don’t exist get blocked before they even hit the rate limit.
  • National seat totals are summed on the client’s side; the API serves them per state.

Every one of those touchpoints is documented in the client’s own repository, next to the matching workaround. That’s the working agreement between the two services: the API covers the common case, the client covers its own special cases, and each gap is a commit you can look up instead of a meeting nobody wrote down.

Monitoring that agreement in production follows the same philosophy as the rest of the stack: the dashboards live in the cloud platform’s managed monitoring, outside the repositories, observing the services from the outside. The code itself only exposes health and status endpoints — including live statistics for the database connection pool, which proved necessary during load testing.

Election day: 135 articles that update themselves

On election day, the model described above was used for what it was built for: publishing the coverage of the 26 states plus the Federal District and keeping it up to date with no manual intervention.

There were 135 automated articles. For each state and for the Federal District, one article per office (governor, senator, federal deputy and state deputy, which in the Federal District is the district deputy) and one general article covering every office, updated as the results closed. Five articles per unit, 26 states plus the Federal District.

Each article went through a flow of versions. At 8 a.m. I configured the system to publish the first version of all 135, already carrying information on the candidates in the race, among other data. At that point not a single vote had been counted: the article existed to be updated later.

The count began at 5 p.m., and our system received its first result events at 6:30 p.m., when a candidate was mathematically elected, meaning the remaining votes could no longer change the outcome. From then on it worked like this: when a result is settled, the article for that state and office is updated, and so is the state’s general article. Nobody triggers that by hand; each update is a reaction to an event, the same principle as the rest of the pipeline.

In parallel, we had designed a special results screen, built together with the frontend team, and it handled the traffic well. That wasn’t luck. In load tests with JMeter, we prepared the infrastructure for an extreme scenario of 1K to 3K requests per second, the traffic peak the journalism team expected.

What comes next

The model described here is the foundation, but a foundation is justified by what it holds up. The next post brings the results: what this automated coverage delivered in practice. It also brings the criticisms I raised about the project after reflecting on it.