All case studies
~/cases/raio-x-eleitoral
Platforms & CRMsIntelligence and AISep 2026 → Oct 2026Completed

P-17

Raio X Eleitoral

From the national total to the ballot box: one territorial model for 2018, 2022 and 2026, a live count collected from the TSE and team access through links bound to a single candidate.

clientPolitical intelligence consultancy serving a nationwide majoritarian campaign and parliamentary offices

Developer responsible for the whole system: database and ETL, backend routes, frontend and the visual overhaul

A private dashboard on Brazil's public electoral data (TSE) for the 2026 general election. A self-managed Linux server downloads the per-section files, reconciles polling places, decodes ASN.1 voting-machine bulletins and collects the election-night count with ETags and coordinated back-off; a Postgres on Supabase holds 700k territories and 13 million result rows in a level-partitioned table, with RLS everywhere and no access for the public roles; a Next.js 16 site serves the candidate page, a canvas-map explorer, election-over-election comparison, the live count and the abstention tab, behind a login with HMAC cookies, scrypt and brute-force lockout. Six bilingual reports came out of the same base in the week after the first round.

700,822
territories
13.0M
result rows
~9.3 GB
database
497,897
2026 ballot boxes
  • ssr
  • react
  • postgres
  • supabase
  • etl
  • rls
  • lgpd
architecture.en.svgenlarge ↓

01Overview

Raio X Eleitoral ("Electoral X-Ray") is a private dashboard used by a political intelligence consultancy serving a national majoritarian campaign and a group of legislative offices. It answers the question every campaign asks on election night and in the weeks that follow: where did each candidate do well or badly, and what changed since the last election, going as deep as public data allows (region, state, city, electoral zone, neighbourhood, polling place and polling section).

Everything on screen comes from open sources: the per-section voting files on the TSE open-data portal, the JSON feeds published on election night, the bulletin of every voting machine (published in ASN.1) and the IBGE territorial meshes. The system does not infer voter profiles or attribute intent to anyone. The rule written into the project is "nothing invented, nothing assumed": when the TSE has not yet published a figure, the screen says so instead of estimating.

The product was built in nine days of commits, between late September and the week after the first round. A dedicated Linux server handles collection and loading; a Postgres database on Supabase holds the territorial model; a Next.js site, deployed on Vercel and on the same server, serves the team behind a login, gives field teams scoped access through follow links, and provides an admin panel with audit logs.

02The problem

Brazilian electoral data is public but far from campaign-ready:

  • Volume and format. The open-data portal delivers ZIPs of Latin-1, semicolon-separated CSVs, one per state, with hundreds of thousands of rows each. The full portal download, done on day two, came to 18 GB. None of it is queryable without a data model behind it.
  • Fragmented territory. Electoral zones and neighbourhoods have no official boundaries, the polling-place register disagrees between the votes file and the totals file, and election-night feeds only go down to the municipality. Comparing 2022 with 2026 on the same cut means reconciling all of it.
  • Time. The first round was on 4 October 2026. The system had to be ready to collect the live count that day, with 2022 and 2018 already loaded for comparison, and to turn into team reports the same weekend.
  • Controlled access. The dashboard serves a campaign. It needed per-person logins, a way to hand field teams only one candidate's live count without creating accounts, and a record of who viewed and exported what.

03The solution

  • Candidate page (president, governor, senator and a tracked set of deputies) with a territorial explorer: map, table and bars, at the scale the user picks, with a Back button that undoes each step.
  • Election-over-election comparison on the same territory: the candidate against themself in 2022 (first or second round) and against the runoff opponent, grouped into four actions (recover, defend, push, hold).
  • Compare candidates freely across 2026, 2022 and 2018, with a playful fighting-game presentation, browser-generated sounds and faces cropped from the official photos.
  • Find polling places: every candidate for an office in a territory, from state to section.
  • Live count: scoreboard, sections counted, the night's curve and a map updating on their own from the collector; for deputies, the ten most voted in each city plus the tracked ones, with their real ranking.
  • Straight from the machines: the voting-machine bulletins published before the official tally, decoded from ASN.1 with the TSE specification.
  • Abstention tab: abstention by group (age, gender, schooling, marital status, mandatory vs optional vote, disability) from the country down to the zone, and the total down to the ballot box, for 2014, 2018, 2022 and 2026.
  • Final report per candidate with PDF and CSV export, plus six bilingual (PT/EN) reports published as password-protected static pages for the team and partners.
  • Administration: users (create, change password, deactivate), per-team follow links with expiry and open counts, and logs of logins, activity, exports, imports and admin actions.
  • One territorial model, 8 levels deep, for every election: 700,822 territories and 13 million result rows in a level-partitioned table, keyed by territory code first and CLUSTERed after every load.
  • A count collector with ETag/304, coordinated back-off on 403/429/503, two phases per round (scoreboard in seconds, cities afterwards), every JSON stored gzipped, and a rehearsal mode that rolls back against the official simulator.
  • Voting-machine bulletins read straight from the TSE: ASN.1 bu.dat decoded with the official specification versioned in the repo.
  • Login-free team links bound to one candidate: signed cookie + route and parameter allowlist in the middleware + database re-check on every scoreboard refresh.
  • RLS enabled and revoke all from anon/authenticated on every table, function and sequence; the browser never talks to Supabase.
  • Loads that stop on an unknown column and a validation after every load (sections, places and neighbourhoods add up to the zone); taxpayer and voter IDs verified absent from the database.
  • eleicoes_de rewritten to use the index: 47 s → 0.03 s.

04Architecture

real architecture · hover over the nodes
Read the diagram as a list (26 components, 26 connections)

Components

  • Public sources: TSE · open data<br>per-section ZIPs (Latin-1, ';'), TSE · results release<br>JSON per country/state/city (ETag), TSE · voting-machine bulletin<br>bu.dat in ASN.1, IBGE · meshes<br>TopoJSON
  • Self-managed Linux server (collection and loads): baixar_* .py<br>resumable downloads, carregar_* .py<br>psycopg 3 + DuckDB<br>validar_territorio.py, coletar_apuracao.py<br>systemd timer · 60 s · ETag/304<br>back-off on 403/429/503, coletar_bu.py<br>asn1tools · spec bu.asn1, vigiar_abertos_2026.py<br>timer 5 min day / 30 min night, gerar_malhas.py<br>IBGE → TSE codes
  • Supabase Postgres · tse schema: territorio (8 levels)<br>br → regiao → uf → municipio<br>→ zona → local → secao · bairro, resultado<br>partitioned by level (6)<br>+ territorio_total, SQL/plpgsql functions<br>explorar · locais_disputa<br>abstencao_filhos · trajetoria, usuario · link_acesso<br>log_evento · etl_execucao, RLS on every table<br>revoke all anon/authenticated<br>no taxpayer/voter IDs
  • Next.js 16 · React 19 (SSR + ISR): proxy.ts<br>HMAC-SHA256 cookie · timingSafeEqual<br>team-link allowlist, 20 /api routes<br>parameter allowlist<br>gzip + in-memory cache, 18 pages<br>candidate page · explorer · live count<br>compare · abstention · logs, canvas map<br>d3-geo + topojson-client
  • Hosting: Vercel (gru1)<br>transaction pooler · DB_PREPARE=0, Self-managed server<br>systemd user · VPN only
  • Static reports: DuckDB pipeline → JSON/binary, PT/EN pages on Vercel<br>middleware with password in env

Connections

  • TSE · open data<br>per-section ZIPs (Latin-1, ';') → baixar_* .py<br>resumable downloads
  • baixar_* .py<br>resumable downloads → carregar_* .py<br>psycopg 3 + DuckDB<br>validar_territorio.py
  • carregar_* .py<br>psycopg 3 + DuckDB<br>validar_territorio.py → resultado<br>partitioned by level (6)<br>+ territorio_total
  • TSE · open data<br>per-section ZIPs (Latin-1, ';') → vigiar_abertos_2026.py<br>timer 5 min day / 30 min night
  • vigiar_abertos_2026.py<br>timer 5 min day / 30 min night → carregar_* .py<br>psycopg 3 + DuckDB<br>validar_territorio.py
  • TSE · results release<br>JSON per country/state/city (ETag) → coletar_apuracao.py<br>systemd timer · 60 s · ETag/304<br>back-off on 403/429/503
  • coletar_apuracao.py<br>systemd timer · 60 s · ETag/304<br>back-off on 403/429/503 → resultado<br>partitioned by level (6)<br>+ territorio_total
  • TSE · voting-machine bulletin<br>bu.dat in ASN.1 → coletar_bu.py<br>asn1tools · spec bu.asn1
  • coletar_bu.py<br>asn1tools · spec bu.asn1 → resultado<br>partitioned by level (6)<br>+ territorio_total
  • IBGE · meshes<br>TopoJSON → gerar_malhas.py<br>IBGE → TSE codes
  • gerar_malhas.py<br>IBGE → TSE codes → canvas map<br>d3-geo + topojson-client
  • carregar_* .py<br>psycopg 3 + DuckDB<br>validar_territorio.py → territorio (8 levels)<br>br → regiao → uf → municipio<br>→ zona → local → secao · bairro
  • territorio (8 levels)<br>br → regiao → uf → municipio<br>→ zona → local → secao · bairro → resultado<br>partitioned by level (6)<br>+ territorio_total
  • resultado<br>partitioned by level (6)<br>+ territorio_total → SQL/plpgsql functions<br>explorar · locais_disputa<br>abstencao_filhos · trajetoria
  • SQL/plpgsql functions<br>explorar · locais_disputa<br>abstencao_filhos · trajetoria → 20 /api routes<br>parameter allowlist<br>gzip + in-memory cache
  • usuario · link_acesso<br>log_evento · etl_execucao → proxy.ts<br>HMAC-SHA256 cookie · timingSafeEqual<br>team-link allowlist
  • proxy.ts<br>HMAC-SHA256 cookie · timingSafeEqual<br>team-link allowlist → 20 /api routes<br>parameter allowlist<br>gzip + in-memory cache
  • 20 /api routes<br>parameter allowlist<br>gzip + in-memory cache → 18 pages<br>candidate page · explorer · live count<br>compare · abstention · logs
  • 18 pages<br>candidate page · explorer · live count<br>compare · abstention · logs → canvas map<br>d3-geo + topojson-client
  • SITE → Vercel (gru1)<br>transaction pooler · DB_PREPARE=0
  • SITE → Self-managed server<br>systemd user · VPN only
  • Team<br>login → proxy.ts<br>HMAC-SHA256 cookie · timingSafeEqual<br>team-link allowlist
  • Team link<br>no login → proxy.ts<br>HMAC-SHA256 cookie · timingSafeEqual<br>team-link allowlist
  • Admin → proxy.ts<br>HMAC-SHA256 cookie · timingSafeEqual<br>team-link allowlist
  • resultado<br>partitioned by level (6)<br>+ territorio_total → DuckDB pipeline → JSON/binary
  • DuckDB pipeline → JSON/binary → PT/EN pages on Vercel<br>middleware with password in env

The system has three physical layers: a self-managed Linux server that talks to the TSE (from a developer laptop the TSE answers 403), Postgres on Supabase as the publishing database, and the Next.js site, which reads the database only from the server side.

(Diagram labels are in Portuguese, matching the codebase: "fontes públicas" = public sources, "servidor próprio" = self-managed server, "coleta e cargas" = collection and loading, "relatórios" = reports.)

The decisions the diagram summarises:

  • Collection separated from publishing. The self-managed server downloads, validates and loads; Supabase only publishes. Large loads run in separate transactions and, after reloading sections, the process runs CLUSTER on the territory key and VACUUM ANALYZE, because physical order is what keeps the explorer fast.
  • One territorial model. Every election (2018, 2020, 2022, 2024, 2026) lands in the same tse.resultado table, partitioned by level, with the territory code as the first column of the key. Comparing years becomes a query over one structure.
  • No browser access to the database. No Supabase key ships to the client. Pages and API routes use the server connection; the browser only receives compact JSON.

05Under the hood

Backend and data

Territorial model. tse.territorio holds every place where votes are counted, in eight levels (br, regiao, uf, municipio, zona, bairro, local, secao), with per-level codes designed for prefix search (collate "C"): mun:zona, mun:BAIRRO, mun:zona:local, mun:zona:secao. Per the model documentation (3 Oct), there are 700,822 territories. tse.resultado is list-partitioned by level into six partitions (aggregates, municipality, zone, neighbourhood, polling place, section), with 13 million real rows on the same date; tse.territorio_total holds the denominators (eligible voters, turnout, valid, blank and null votes) per territory, office, year and round.

Read functions. The site does not assemble its own SQL for the explorer: it calls database functions. tse.explorar returns a territory's children with votes, rank, main rival and totals and, through the p_cmp_* parameters, the same territory in another result for the Comparison tab. tse.locais_disputa lists every candidate for an office in a territory; tse.abstencao_filhos does the same for abstention by group. Functions that take the level as a parameter run with plan_cache_mode = force_custom_plan, because the generic plan did not prune partitions.

Migrations. 26 SQL files applied in order (2,097 lines), with DDL, functions and a comment on every column. Migration 011 records a scope decision: the publishing database kept only president, governor and senator plus the tracked deputies; assets, campaign finance, polls and electorate profiles were dropped, with a backup taken first.

Python ETL. 26 active scripts under etl/ (psycopg 3 and DuckDB), running on the self-managed server:

  • baixar_*: resumable downloads of the portal ZIPs.
  • carregar_territorio.py and carregar_secao_ano.py: load a whole election year from the per-section files. The list of columns each load reads is fixed: if the TSE renames one, the load stops instead of silently adapting.
  • validar_territorio.py: after every load, checks that sections, polling places and neighbourhoods add up to the zone, that no territory has candidate votes above its valid votes and that none is missing totals.
  • coletar_apuracao.py: the election-night collector (see Technical challenges).
  • coletar_bu.py: voting-machine bulletins, decoded from ASN.1 with asn1tools and the official specification versioned at etl/spec/2026/bu.asn1, stored in tse.bu_secao.
  • vigiar_abertos_2026.py: a monitor of the 2026 open-data files that detects new publications and triggers the load on its own.

Scheduling. Six user-level systemd units versioned under servidor/systemd/: one timer starts the collector ten minutes before the TSE begins publishing, another wipes simulation data before the real count (the collector refuses to write while a simulation is flagged in the database), and a third runs the monitor every 5 minutes by day and every 30 at night.

Traceability. Every load records start, end, row count, errors and a detalhe payload in tse.etl_execucao, including the list of territories it created. The project rule is to never delete an "empty" territory by a generic criterion, only what the load itself recorded creating.

Frontend

Next.js 16 with React 19 and TypeScript on the App Router: 18 pages, 29 route handlers (20 under /api), 28 components and 26 modules in lib/. Runtime dependencies are few: postgres (database client), d3-geo and topojson-client for the map, and server-only to keep server modules out of client bundles.

  • Canvas map. IBGE meshes (TopoJSON, lowest quality) are converted by scripts/gerar_malhas.py, which swaps IBGE codes for TSE codes and drops anything without a match. The site serves 27 per-state files plus regions and states (4.7 MB in total) and draws on canvas with d3-geo, reading colours from CSS tokens through getComputedStyle so the same map works in light and dark themes.
  • Compact responses. The /api/territorio route sends field names once and one array per row, gzips inside the handler (Next's server does not compress API routes) and keeps the result in an in-memory cache with a 6-hour TTL for closed years and 60 seconds for 2026.
  • ISR on the candidate page. Regenerated every 10 minutes; user-dependent pages (logs, links, account) are fully dynamic.
  • Live count with polling that cancels correctly when the territory changes, and a broadcast-style scoreboard.
  • Visual overhaul (7 Oct): a sidebar that opens on hover and pins on click, light and dark themes via tokens, four fonts loaded with next/font (Newsreader for headings, Source Sans 3 for text and tabular figures, Martian Mono for the ballot-box digits, Saira Condensed for the comparator stage), shared components (shadowless panels, a single page header) and motion driven by --dur-* and --ease tokens.
  • Export to PDF and CSV from the screen itself, with the event logged through sendBeacon.
Infrastructure and deploy
Piece How
Self-managed server Ubuntu, Python 3.14 in a venv, reachable only over a VPN (Tailscale). Runs loads, collectors and user-level systemd timers. Also serves a copy of the site as a systemd service on an internal port.
Database Supabase Postgres (Pro plan), schema tse. Session pooler from the server; transaction pooler from Vercel with DB_PREPARE=0 and DB_MAX=10.
Site Vercel, gru1 region (São Paulo), Next.js framework. vercel.json pins the region; poweredByHeader is off.
Deploy to the server admin/deploy.sh: rsync, npm install, build with credentials loaded from the environment, rewrite of the systemd unit and an active wait for HTTP 200. If the build fails, the service is not restarted.
Reports Seven static projects on Vercel, each with a password middleware.js and a vercel.json setting noindex and no-store; disconnected from GitHub after the first deploy.
Code Private GitHub repository, single branch, small commits in Portuguese.

Lessons learned and written down in the repo: long processes always under nohup with a log, because SSH sessions drop; never unpack a large file in the server's /tmp (it is RAM); at most 4 connections to the TSE for bulletins, and never while the count collector is running, because the TSE answers 429.

06Security

8 attacks considered, 8 defenses in production.

attackdefense
API shortcut

Skipping the interface to talk straight to the database or API with the public key in hand.

  • Read the tables straight through Supabase's public API with the anon key

    RLS enabled on every table in the schema and revoke all from anon and authenticated on tables, functions and sequences; with no policies, those roles read nothing. The site reads only server-side, with the pooler URL in an environment variable

Accounts and login

Brute force, rogue account creation, password reset and account takeover.

  • Keep using a session after the password changed or the account was deactivated

    The user's version is part of the cookie and increments on every password change or deactivation; pages check in the database that the user exists, is active and has the same version

  • Brute-force or e-mail enumeration on the login form

    6 failures in 15 min lock for 15 min, per e-mail and per address; an 800 ms delay on every failure; unknown e-mail and wrong password show the same message; passwords stored only as scrypt (N=16384, 16-byte salt)

Privilege escalation

Someone with some access trying to get more: becoming admin, reading what they shouldn't, keeping revoked access.

  • Use a team link to browse the whole dashboard or another candidate

    An 18-byte random code (144 bits) validated by regex before any database query; the link cookie is signed and bound to one candidate and office; the middleware unlocks only the follow page, photos and that candidate's 2026 queries, parameter by parameter; everything else gets a 403; the scoreboard re-checks the link in the database on every refresh

Forged origin and requests

CSRF, fake webhooks, reused tokens, signed links and open redirects.

  • Forge or tamper with the session cookie

    An id.version.expiry.signature cookie signed with HMAC-SHA256 under an environment secret and compared with timingSafeEqual; httpOnly, sameSite=lax, secure in production, 14 days

  • Send the person to another site after login (open redirect)

    The return parameter is only honoured if it starts with / and not //; otherwise the person lands on the home page

Injection and hostile content

XSS, iframes, CSV formulas, disguised uploads and AI prompt manipulation.

  • Inject SQL or unexpected parameters into the API routes

    Every query goes through postgres.js tagged templates (parameterised); years, rounds, offices and levels come from closed lists and codes and identifiers from regular expressions; anything else gets a 400 before touching the database

Data leakage and tracking

Enumerating who is in the base, scraping personal data, tying questions to people, extracting secrets.

  • Leak taxpayer or voter-registration numbers into the publishing database

    A project rule plus a validation script that checks no JSONB record carries those keys, no column in the schema has such a name and anon cannot see the schema

Perimeter. The whole site requires login. proxy.ts (the Next middleware) intercepts everything but static assets and only lets through a valid session cookie or a valid team-link cookie. API routes without a session get a JSON 401; pages redirect to /entrar with the destination in volta=, which only accepts paths starting with / and not // (no open redirect).

Sessions. The raiox_sessao cookie carries id.version.expiry.signature, signed with HMAC-SHA256 under an environment secret (SESSAO_SEGREDO) and compared with timingSafeEqual. Signature verification happens in the middleware without touching the database; who the user is, whether they are still active and whether they are an admin is checked against the database by the pages. The version is part of the cookie and increments when the password changes or the account is deactivated, which kills open sessions. The cookie is httpOnly, sameSite=lax, secure in production, valid for 14 days.

Passwords. Stored only as scrypt hashes (N=16384, r=8, p=1, 16-byte salt, 64-byte output), with the password normalised to NFKC before hashing, minimum 8 characters. The command-line script that creates the first admin uses the same algorithm.

Login brute force. Six failures in 15 minutes block for 15 minutes, counted per e-mail and per address; every failure waits 800 ms before responding; unknown e-mail and wrong password show the same message (the real reason goes to the log). Blocked attempts are logged at most once a minute per key so a bot cannot flood the log table.

Follow links. The admin creates one link per team (/l/<code>), with an 18-byte random base64url code, an expiry and a label. Opening the link sets a separate cookie (raiox_link), signed the same way and bound to one candidate and one office. The middleware holds an explicit allowlist of what that cookie unlocks: the /acompanhar page, photos and only the queries that candidate's live count makes, with parameters checked one by one (the sq must be that candidate's, the year must be 2026, the office must match). Any other route gets a 403. The scoreboard re-checks the link in the database on every refresh, so deactivating cuts access within seconds. Rejected codes are logged at most 10 times per hour per address.

Database. Every table in the tse schema has RLS enabled and revoke all from the anon and authenticated roles, as do the functions and sequences; with no policies at all, those roles read nothing through the Supabase API. The site reads exclusively from the server, with the pooler URL in an environment variable, and uses the postgres client's tagged templates, which parameterise every query. In the API routes, parameters go through allowlists before reaching SQL: years and offices from closed lists, level from a closed list, codes and identifiers by regular expression; anything else gets a 400.

Personal data (LGPD). Taxpayer and voter-registration numbers never enter Supabase: validar_candidaturas.py checks that no JSONB record contains those keys and that no column in the schema carries such a name, and confirms that anon cannot see the schema. The event log stores e-mail, IP and user agent for dashboard auditing, never passwords. Tests delete their own events at the end.

Secrets. Credentials live only in environment variables (.env files outside git on the server; Vercel variables, with passwords marked sensitive). The repository has a written rule that no password goes into a .md, a commit message or the task tracker.

Abuse of internal APIs. The endpoint that receives browser activity events requires a session, accepts only paths starting with / and caps at 120 events per minute per person. The static reports sit behind an edge middleware that compares the password with the environment variable, sets an httpOnly, Secure cookie and returns 503 if the variable is missing (fail closed).

Operational hardening. The count collector refuses to write to the official environment while a simulation is flagged in the database. Loads stop on an unknown column or a new TSE code. Writing a log never fails the request (errors go to the console only). Deletes on large tables always use the full key.

07Technical challenges

Reconciling the TSE's territory

The per-section votes file and the per-section totals file sometimes disagree about a section's polling place (merged sections, a place change between rounds, duplicated rows). The rule adopted: the place where the votes were recorded wins, and totals are matched row by row by casar_locais: first the places equal in both files; if exactly one remains on each side, they match; otherwise the place whose vote sum equals the candidate votes. Zones and neighbourhoods have no official outline, so on the map they are circles at the centre of their polling places, inside the city outline; polling-place coordinates only count if they fall inside the municipality's IBGE bounding box. The validation after every load is what makes it safe to go down to the section.

Collecting the live count without hammering the TSE or being cut off

The collector reads the TSE status files and downloads only what changed, using ETags (the TSE answers 304 when a file is unchanged). If the TSE refuses (403, 429 or 503), all connections pause together and the pace drops, instead of retrying file by file. Each round has two phases: country and states first (few files, the scoreboard updates in seconds), cities afterwards with a per-round cap. Every new JSON is stored gzipped for auditing or reprocessing, and a --ensaio (rehearsal) mode runs everything against the TSE's official simulator and rolls the writes back. Even so, on election night the collector declared "complete" with cities missing; checking the city sum against the state per office became a mandatory, documented step, along with the re-collection procedure.

Making the explorer fast over tens of millions of rows

The primary key of tse.resultado starts with the territory code, partitions are by level, and tables are physically reordered (CLUSTER) after every large load. Read functions run with a forced custom plan because the generic plan did not prune partitions. On the site, responses are gzipped and cached in memory with a per-year TTL. One concrete case from the history: eleicoes_de, used by the polling-place search, went from 47 seconds to 0.03 seconds when rewritten to use the index.

Reading the voting-machine bulletin

The TSE publishes each machine's bu.dat in ASN.1 as soon as it is received, before the official tally. The solution was to version the official specification in the repository and decode with asn1tools in two steps (generic envelope, then the bulletin), storing votes per office as JSONB. The cap on simultaneous connections to the TSE (4) was learned the hard way, when 28 connections knocked out a round of the count collector.

08Results

Only what the repository and its documentation prove:

Measure Value Source
Commits 148 in 9 days (29 Sep to 7 Oct 2026) git log
SQL migrations 26 files, 2,097 lines ls sql, wc -l
Territories in the model 700,822 docs/MODELO.md (3 Oct)
Rows in tse.resultado 13.0 million real docs/MODELO.md (3 Oct)
2026 ballot boxes by section 497,897 CHANGELOG.md (Report 4)
Database size ~9.3 GB docs/ESTADO-E-PLANO.md (7 Oct)
First-round count collected to 100% on the night of 4 Oct CHANGELOG.md, docs/ESTADO-E-PLANO.md
eleicoes_de 47 s → 0.03 s commit a479de9
Reports published 6 reports + 1 panel, PT/EN relatorios/README.md
148
commits
how we measured

git log --oneline | wc -l

9 days
active period
how we measured

git log: first commit 2026-09-29, last 2026-10-07

26
SQL migrations
how we measured

ls sql/*.sql

2,097
SQL lines
how we measured

wc -l sql/*.sql

11
live database functions
how we measured

create function in sql/ minus the ones dropped in sql/011_corte.sql

6
result partitions
how we measured

sql/009_territorio.sql (partition by list (nivel))

0 (deny-all)
RLS policies
how we measured

grep 'create policy' sql/ = 0; RLS enabled + revoke all on every table (sql/001, 009, 012…)

700,822
territories
how we measured

docs/MODELO.md, tse.territorio table (Oct 3)

13.0M
result rows
how we measured

docs/MODELO.md, actual tse.resultado rows (Oct 3)

497,897
2026 ballot boxes
how we measured

CHANGELOG.md, base of Report 4

~9.3 GB
database
how we measured

docs/ESTADO-E-PLANO.md (Oct 7)

18
pages
how we measured

git ls-files admin/src/app/**/page.tsx

29 (20 under /api)
route handlers
how we measured

git ls-files admin/src/app/**/route.ts

28
React components
how we measured

git ls-files admin/src/components

26
Python ETL scripts
how we measured

git ls-files etl/*.py (excluding etl/legado)

6 (3 timers, 3 services)
systemd units
how we measured

servidor/systemd/

27 states + regions + states, 4.7 MB
map meshes
how we measured

ls admin/public/geo; du -sh

9,328
TypeScript/TSX lines
how we measured

wc -l over git ls-files '*.ts' '*.tsx'

6,259
Python lines
how we measured

wc -l over git ls-files '*.py'

6 + 1 dashboard
published reports
how we measured

relatorios/README.md

09Timeline

  1. 29 Sep 2026

    Project structure, resumable downloader for TSE open data, schema DDL and a load of 20,062 candidacies; first site with search and candidate page

  2. 30 Sep 2026

    Course corrected to territorial performance with no voter-profile inference; unified model from the country to the section, IBGE meshes with TSE codes and a scope cut of the database

  3. 01 Oct 2026

    Private preview on Vercel, runoff comparison, compare candidates, find polling places and abstention by age

  4. 02 Oct 2026

    Per-user login, live count and the collector rehearsed against the TSE simulator and scheduled with systemd timers

  5. 03 Oct 2026

    Per-team follow links, admin logs and the mobile pass

  6. 04 Oct 2026

    Election day; count collected, voting-machine bulletins decoded from ASN.1, 2018 loaded and the first report published

  7. Oct 2026

    Oct 5–6: 2026 by section in the database, final report on the candidate page, open-data monitor, Abstention tab, eleicoes_de from 47 s to 0.03 s and reports 2 to 5

  8. 07 Oct 2026

    Visual overhaul (sidebar, typography, components, pages) merged and published; report 6

10Stack

Frontend
  • Next.js 16 (App Router, SSR, ISR)
  • React 19
  • TypeScript 5
  • CSS com tokens (tema claro/escuro)
  • next/font (Newsreader, Source Sans 3, Martian Mono, Saira Condensed)
  • d3-geo + topojson-client em canvas
  • Web Audio API
Backend
  • Route handlers do Next.js (20 rotas /api)
  • postgres.js (tagged templates)
  • gzip no handler
  • cache em memória com TTL
  • server-only
Data
  • PostgreSQL (Supabase), schema tse
  • 26 migrations SQL
  • tabela particionada por nível (6 partições)
  • funções plpgsql com force_custom_plan
  • Python 3.14 (psycopg 3, DuckDB, asn1tools)
  • CSV/JSON do TSE
  • malhas TopoJSON do IBGE
  • Parquet e binários compactos para os painéis
Infra
  • Servidor Linux próprio (Ubuntu) via VPN
  • systemd timers e serviços de usuário
  • Vercel (gru1, transaction pooler)
  • GitHub privado
  • Chromium headless para capturas e PDFs
Security
  • Cookie HMAC-SHA256 com timingSafeEqual
  • scrypt (N=16384)
  • versão de sessão
  • bloqueio por e-mail e IP
  • allowlist de rotas para links de equipe
  • RLS + revoke em todo o schema
  • allowlist de parâmetros nas APIs
  • logs de auditoria
  • segredos só em ambiente
  • checagem automática de ausência de CPF/título

11Tech sheet

node@raio-x-eleitoral: ~/cases/raio-x-eleitoral
node@raio-x-eleitoral ─────────────
type
data platform + live election dashboard
stack
Next.js 16 · React 19 · TypeScript · Python 3.14
db
Postgres (Supabase) · 6 partitions · RLS deny-all
etl
psycopg 3 · DuckDB · asn1tools · systemd timers
auth
HMAC-SHA256 cookie · scrypt · team links
deploy
Vercel gru1 + self-managed Linux server
modules
candidate page · explorer · live count · compare · abstention · logs
status
completed
$ ls sql/ | head -4 && ls sql/ | wc -l
001_tse_core.sql
002_funcoes_admin.sql
003_raiox.sql
004_funcoes_ficha.sql
26
$ .venv/bin/python coletar_apuracao.py --ambiente simulado --ensaio
17:02:11 rodada 1: 58 baixados, 0 sem mudança, 0 recusas ou falhas · estados/Brasil 58 novos
17:02:40 rodada 2: 412 baixados, 31 sem mudança, 0 recusas ou falhas
17:03:05 ensaio: gravação desfeita (rollback)
$ curl -s localhost:3100/api/territorio?sq=1&ano=2026
{"erro":"entre para continuar"} # 401: sem cookie de sessão
$ curl -s -b "$SESSAO" 'localhost:3100/api/territorio?sq=abc&ano=2099&turno=1&cargo=1&nivel=uf&pai_nivel=br&pai=BR'
{"erro":"parâmetros inválidos"} # 400: ano fora da lista, sq fora do padrão
$ ls servidor/systemd/
tse-apuracao.service tse-apuracao.timer
tse-limpar-simulacao.service tse-limpar-simulacao.timer
tse-vigia-abertos.service tse-vigia-abertos.timer
$ .venv/bin/python validar_territorio.py 2026
candidatos com soma secao ≠ zona: (0, 0)
candidatos com soma local ≠ zona: (0, 0)
secao: votos válidos acima dos válidos do território: 0 · sem total: 0

© 2026 NODE. All rights reserved.