All case studies
~/cases/[classified]/mutirao
confidential · NDAPlatforms & CRMsPresidential campaignOct 2026Completed

P-03

Mutirão

Volunteer mobilization under a legal deadline and a hostile audience: every rule lives in the database.

client████Nationwide majoritarian campaign (runoff)

A web platform to organize volunteers in an election's final stretch: a daily mission, an event calendar with personal share links, a log of votes won, and a coordination panel. Built in days and run in production until the legal cutoff, with Cloudflare Workers in front and Postgres deciding everything, on the assumption that opponents would sign up, read everything and try to inflate the numbers.

~1,850
pgTAP assertions
49
tables with RLS
9,880
disposable domains blocked
11 days
active period
  • cloudflare
  • supabase
  • postgres
  • rls
  • react
  • ssr
  • rate-limiting
  • lgpd
  • tests
architecture.en.svgenlarge ↓

01Overview

Mutirão is the platform a nationwide campaign used to organize volunteers during the last two weeks before a runoff election. Volunteers sign up with their city, get one mission per day, find street events near them, share each event with a personal link and log the conversations that paid off. The coordination team runs everything from an admin panel: publishing missions, approving events, following numbers by territory and reviewing anything that looks off.

Two things made it unusual. First, the deadline: mobilization ends at a fixed time set by law, and after that nothing can be published, changed or sent. Second, the audience: anyone can sign up, including people who want to discredit the campaign. The system was designed on the assumption that opponents would read everything, call the API directly and try to inflate the numbers.

It went from first commit to production in a few days and kept shipping daily in production, built by a small dev team with a product owner deciding rules and copy.

02The problem

  • A short, hard deadline. Roughly two weeks of operation, new rules arriving every day, and a legal cutoff at 10 p.m. the night before election day.
  • A hostile audience by design. Open sign-up, no invites. Any number on screen can become a screenshot against the campaign; any counter can be scripted.
  • Electoral law and Brazil's LGPD. Rules on where an event may happen (never at a polling place), what may be collected about third parties (nothing) and when communication must stop.
  • Unpredictable spikes. An event link shared in messaging groups can get thousands of hits in minutes.

03The solution

  • Passwordless sign-up with an emailed code, city and neighborhood, and separate consents.
  • Mission of the day: one national mission per date, switching at 7 p.m. and emailed in batches; territorial missions and missions targeted at specific groups.
  • Event calendar: events with a meeting point checked against forbidden zones, an "I'm going" button, attendance, personal share links with QR codes, and hit counts credited to whoever shared.
  • Votes-won log: a per-volunteer counter with daily limits, automatic review above the limit, and undo within 5 minutes.
  • Coordination panel (/painel): missions, events, groups, in-portal notices, parameters tunable without a deploy, abuse signals, bulk suspension, CSV exports and an audit log of every admin action.
  • Portal for legislators' teams: their own link, aggregate numbers for their network, and exports.
  • Live scoreboard for big screens: participants and valid votes, frozen automatically at the cutoff.
  • Street tool: map and search of high-traffic spots, fed by a partner API through an allowlisted proxy.
  • The Supabase publishable key executes no function at all: only the Worker talks to the database, and a pgTAP test proves it.
  • The legal 10 p.m. cutoff is an immutable function used in 65 places in the schema, with an injectable clock to test shutdown.
  • A user-creation hook rejects 9,880 disposable domains and variants of the same Gmail inbox.
  • Vote logging limits route entries to review instead of rejecting them, giving no feedback to whoever is trying to inflate counts.
  • No IP is ever stored: a daily-salted SHA-256 wherever the database needs to limit by origin.
  • 348 commits in 11 days, 99 migrations, and CI running pgTAP and end-to-end tests on every push.

04Architecture

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

Components

  • Clients: Supporter<br>(phone, PWA), Coordination<br>(/painel), Legislators<br>(/parlamentar), Big screen<br>(/placar)
  • Cloudflare Edge: Turnstile<br>anti-bot, Worker · React Router 8 SSR<br>nonce CSP · CSRF · body limit, Rate Limiting<br>6 per-IP limiters, Edge cache<br>public pages, Cron Triggers<br>batched email, Monitor Worker<br>1 min cron + KV
  • Postgres: public schema<br>RLS + SECURITY DEFINER functions, private schema<br>settings, clock, sends, hashes, pg_cron<br>cleanup and expirations
  • Supabase: Auth<br>email OTP<br>before-user-created hook, PostgREST<br>anon without EXECUTE, PG

Connections

  • Supporter<br>(phone, PWA) → Worker · React Router 8 SSR<br>nonce CSP · CSRF · body limit
  • Coordination<br>(/painel) → Worker · React Router 8 SSR<br>nonce CSP · CSRF · body limit
  • Legislators<br>(/parlamentar) → Worker · React Router 8 SSR<br>nonce CSP · CSRF · body limit
  • Big screen<br>(/placar) → Worker · React Router 8 SSR<br>nonce CSP · CSRF · body limit
  • Supporter<br>(phone, PWA) → Turnstile<br>anti-bot
  • Worker · React Router 8 SSR<br>nonce CSP · CSRF · body limit → Rate Limiting<br>6 per-IP limiters
  • Worker · React Router 8 SSR<br>nonce CSP · CSRF · body limit → Edge cache<br>public pages
  • Worker · React Router 8 SSR<br>nonce CSP · CSRF · body limit → PostgREST<br>anon without EXECUTE (user token)
  • Worker · React Router 8 SSR<br>nonce CSP · CSRF · body limit → PostgREST<br>anon without EXECUTE (secret key only without a session)
  • Worker · React Router 8 SSR<br>nonce CSP · CSRF · body limit → Auth<br>email OTP<br>before-user-created hook (Sb-Forwarded-For)
  • Worker · React Router 8 SSR<br>nonce CSP · CSRF · body limit → Polling places<br>external API (allowlisted proxy)
  • PostgREST<br>anon without EXECUTE → public schema<br>RLS + SECURITY DEFINER functions
  • public schema<br>RLS + SECURITY DEFINER functions → private schema<br>settings, clock, sends, hashes
  • Auth<br>email OTP<br>before-user-created hook → private schema<br>settings, clock, sends, hashes
  • pg_cron<br>cleanup and expirations → private schema<br>settings, clock, sends, hashes
  • Cron Triggers<br>batched email → Worker · React Router 8 SSR<br>nonce CSP · CSRF · body limit
  • Worker · React Router 8 SSR<br>nonce CSP · CSRF · body limit → Resend<br>batch + idempotency
  • Monitor Worker<br>1 min cron + KV → Worker · React Router 8 SSR<br>nonce CSP · CSRF · body limit (/api/saude)
  • Monitor Worker<br>1 min cron + KV → Telegram<br>downtime alert

(Diagram labels are in Portuguese, as in the codebase.)

A single Cloudflare Worker serves the volunteer portal, the admin panel, the legislators' portal and the scoreboard with server-side rendering (React Router 8). It is the database's only client: the browser never talks to Supabase. All business rules live in Postgres as RPC functions; the UI just reflects what the database decides. A second, isolated Worker monitors uptime.

05Under the hood

Backend and data
  • 99 versioned SQL migrations and 59 tables across two schemas: public (exposed only through functions) and privado (parameters, clock, email sends, hashes, signal cache), never exposed.
  • 434 PL/pgSQL and SQL functions. Write paths are SECURITY DEFINER with search_path = '' and authorize on auth.uid(), never on the connection role.
  • RLS enabled on 49 tables with 16 explicit policies; normal access goes through functions, not direct selects.
  • Rules as constraints. The 10 p.m. cutoff is an immutable function (privado.fim_mobilizacao()) referenced 65 times across migrations, in check constraints, triggers and write functions. An event without a meeting point, or dated after the last allowed day, is rejected by the table itself.
  • Injectable clock. privado.agora() reads a clock table when present (tests only) and falls back to now(), so "what happens at 10:01 p.m." is tested deterministically.
  • Parameters without deploys. Daily vote limits, review batch size and send windows live in privado.parametros, editable from the panel.
  • Meeting-point geofencing. zonas_proibidas holds polling places, schools, places of worship, public buildings and more, each with a radius up to 300 m; the event's point is checked with a lat/lon bounding box, so a zone in a neighboring city still counts.
  • Scheduling: 3 pg_cron jobs (expired unconfirmed sign-ups, query cleanup, expired proposals) and 3 Worker Cron Triggers for the mission email.
  • Query scale. The API caps responses at 1,000 rows; panel lists and CSVs paginate in the database (p_limite/p_deslocamento), and abuse signals are cached for 60 s and invalidated immediately on suspension.
  • Data loading by script. Cities, neighborhoods, events and meeting points are loaded with TypeScript scripts on Node 22 from data packages published as repository releases. No loading runs on the Worker.
Frontend
  • React 19 + React Router 8 with SSR on the Worker, Tailwind CSS 4, in-house components (CVA + tailwind-merge), Lucide icons.
  • 118 route files: 57 for the panel, 18 for the volunteer portal, 14 public, 14 for the legislators' portal and 17 internal API endpoints (5 of them CSV exports).
  • Mobile first: one primary action per screen, checked at 390 px before anything is called done, installable PWA.
  • Leaflet maps (events, meeting points, street tool) and server-generated QR codes.
  • No third parties on public pages: self-hosted fonts and a CSP that only allows the site's own origin and the database's.
  • Video served by the Worker with Range → 206, because iPhone Safari won't play video without partial requests and the static asset server always returned the whole file.
Infrastructure and deploy
  • Cloudflare Workers (app and monitor), Static Assets, the Rate Limiting binding, Cache API, Cron Triggers, KV and Turnstile.
  • Managed Supabase: Postgres, Auth, PostgREST, Storage (mission images and audio) and pg_cron.
  • Resend for the mission email via POST /emails/batch, 100 per request, with an idempotency key.
  • GitHub Actions CI, three jobs on every push: types, formatting and build; migrations from scratch plus every pgTAP test on a local Supabase inside the runner; and end-to-end tests running the real app against that database, with Mailpit catching login codes.
  • One-command local environment (sh scripts/montar_local.sh): starts Supabase, applies migrations, loads data, checks generated copy and runs the tests.
  • Deploy with npm run deploy, always from a clean tree. Secrets go in through wrangler secret put.
  • Uptime monitor on a separate Worker: hits /api/saude every minute through the public address, alerts only after two consecutive failures, reports recovery with downtime, and when the public address fails it asks the app Worker over a service binding to tell whether the problem is DNS/edge or the app itself.
  • Backup and restore with a written procedure and a rehearsal before the peak.

06Security

8 attacks considered, 8 defenses in production.

attackdefense
Bots and fake sign-ups

Scripts that flood forms, inflate numbers or sweep the site for openings.

  • Create hundreds of accounts with disposable emails or variants of one Gmail inbox

    A before_user_created hook rejects 9,880 disposable domains, '+' and dot/googlemail variants; one inbox per confirmed account; Turnstile on every code request

API shortcut

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

  • Call database functions directly with the publishable key, bypassing cache and limits

    EXECUTE revoked from anon across the public schema; new functions are born closed; only the Worker talks to the database, checked by a pgTAP test

Forged origin and requests

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

  • Forge a POST from another site riding the victim's session

    Any write with a foreign Sec-Fetch-Site or Origin gets a 403; httpOnly, SameSite=Lax, Secure cookie

Injection and hostile content

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

  • Inject script or frame the site

    Per-response nonce CSP with strict-dynamic, frame-ancestors 'none', X-Frame-Options DENY, and names stripped of invisible characters in the database

  • Use a panel CSV export to run a formula on a coordinator's machine

    Values starting with =, +, -, @, tab or CR get an apostrophe prefix on export

Cost and availability abuse

Blowing up the bill, exhausting quotas, flooding logs and knocking functions down with load.

  • Exhaust the login rate limit for everyone, since all traffic leaves the same proxy

    The Worker forwards the real IP in Sb-Forwarded-For only on /otp, /verify and /token; 6 per-IP limiters at the edge

Tampering and audit trail

Fiddling with counts, wiping the base, editing history or denying what was authorized.

  • Inflate vote or hit counters with a script

    Daily and windowed limits silently route to review; entries under review don't count; hits count once per visit, bots excluded, per-IP limit; the hit counter accepts service_role only

  • Publish or change content after the legal deadline

    The cutoff is an immutable function used in constraints, triggers and write functions: the database refuses, whatever the client

The threat model assumed a signed-up adversary holding the Supabase publishable key (it ships in every front end) and with time to automate.

API surface

  • The publishable key executes nothing. An internal audit found 23 read functions callable directly at /rest/v1/rpc/*, bypassing the edge cache and per-IP limits. The api_fechada migration revoked EXECUTE from anon across the public schema, and default privileges make every new function born closed. A pgTAP test asserts anon can execute none of them.
  • Only the Worker talks to the database. Signed-in calls carry the user's token (authenticated). Anonymous calls go through a custom fetch that swaps the publishable key for the secret one only for requests to the project's own PostgREST; anything else is left untouched. Functions still authorize on auth.uid(), which is null here, so the secret key grants a visitor nothing extra.
  • Counters only via server-side paths. registrar_acesso, which feeds the panel and the "link with no conversions" signal, accepts service_role only; before, anyone could inflate any volunteer's hit count.

Authentication and accounts

  • Passwordless login with an 8-digit emailed code (Supabase Auth OTP), session in an httpOnly, SameSite=Lax, Secure cookie, 30 days max.
  • A before_user_created hook rejects 9,880 disposable email domains (a CC0 list generated by script), addresses with +, and variants of the same Gmail inbox (dots and googlemail.com). An inbox is only reserved by a confirmed account, so nobody can lock someone else out by registering their address first; the rule is enforced again at profile creation to close the race between two pending accounts.
  • Auth rate limits per person, not per Cloudflare. Since every call leaves from the Worker, Supabase would see a single IP. The Worker forwards the real IP in Sb-Forwarded-For only on /otp, /verify and /token, restoring per-user limits.
  • Turnstile on every code request (sign-in, sign-up, event proposal), with a mode switchable without a deploy.

Authorization

  • Admins in their own table (administradores), checked by privado.eh_admin() inside the admin_* functions; the panel never trusts a client-side flag.
  • Minimal exposure to volunteers. The portal shows only a person's own numbers; city totals and targets stay in the panel, since any account could switch cities and read any city's total. Internal analysis was stripped from portal responses.
  • Approximate coordinates never leave the database: if an event's location is approximate, only the place name is returned.

Integrity of the numbers

  • Vote logging has a daily limit and a 10-minute window; above it the entry is not rejected (no feedback for an attacker) and goes to review; large batches go to review whole. Entries under review count toward no total.
  • Abuse signals computed in the database (sign-up bursts, links with no conversions, votes over the limit, large batches) feed the review queue and bulk suspension. Suspended accounts drop out of every total.
  • Event-link hits count once per visit: a "seen" cookie, a visit ID with a 30-minute window in the database, bots ignored, and a per-IP, per-event limit.
  • Every admin action is written to registro_admin.

HTTP hardening

  • Per-response CSP with a nonce injected via HTMLRewriter (script-src 'nonce-…' 'strict-dynamic'), frame-ancestors 'none', object-src 'none', form-action 'self', external origins limited to the database and Turnstile.
  • HSTS with includeSubDomains, X-Frame-Options: DENY, nosniff, Referrer-Policy, Permissions-Policy (geolocation for self only) and Cross-Origin-Opener-Policy.
  • CSRF via Sec-Fetch-Site and Origin: any cross-site write gets a 403.
  • Body size limits before reading the request: 64 KB by default, 8 MB only on the three panel routes that take files; bodies without Content-Length are refused.
  • 6 per-IP limiters at the edge (code send, email, attempt, sign-up, access and street tool), including on /api/saude.
  • Allowlisted partner-API proxy, endpoints and parameters validated by regex; everything else returns 400 without leaving the Worker.
  • CSV formula-injection guard: values starting with =, +, -, @, tab or carriage return get an apostrophe prefix.
  • Validation in the database, not just maxLength in the browser: max length on every panel text, and invisible characters (control, zero-width, bidi) stripped from names so nobody can impersonate someone else in the panel.

LGPD and privacy

  • No third-party data: a logged vote has no name, phone, address or free text. ID documents never enter the platform.
  • IPs are never stored. Where the database needs to rate-limit by origin, it gets a SHA-256 of IP, date and a secret salt, truncated to 16 bytes, rotating daily.
  • A purge routine (privado.apagar_dados_pessoais) that runs only with the service key and a confirmation phrase, cascading through accounts and records after the campaign.
  • Communication stays in the portal, with one approved exception (the mission email), gated on consent and with unsubscribe.
  • Secrets only in environment variables: local .dev.vars kept out of git, wrangler secret put in production.

07Technical challenges

Closing the API without breaking a live site

The Supabase publishable key is public by design. The first design left read functions open to it, and the audit showed that created a side door: no edge cache, no per-IP limit, and a forgeable x-forwarded-for header acting as the rate-limit key. The fix couldn't take down public pages already in production. EXECUTE was revoked from anon, and the Worker swaps keys only on anonymous calls to its own PostgREST, keeping auth.uid() null. Tests gained two distinct roles (teste.como(null, …) for "Worker without a session" and teste.anonimo(…) for "publishable key") to prove both halves. The hourly limit moved to a salted IP hash an outside client cannot forge.

A deadline enforced by the database

"After 10 p.m. nothing changes" couldn't depend on someone remembering to flip a switch. The cutoff became an immutable function used in constraints, triggers and write functions (65 references), with an injectable database clock for tests. A dev-only route shifts the UI clock to check the closed state (404 in production). The scoreboard freezes at the same instant.

Bulk email that can't duplicate or lie

The mission email goes out in batches: the Worker's cron asks the database for a batch of people who haven't received it, sends through Resend 100 at a time with an idempotency key, and reports results back. The database owns the reservation, so overlapping cron minutes never pick the same person. In one deploy the provider key was missing, and the dev "log only" mode marked thousands of emails as sent without sending any. The fix restricted log-only mode to a Supabase on 127.0.0.1/localhost; anywhere else, without the key, the cron doesn't even reserve a batch. A second daily send came later, with the send key extended to include the type (morning/evening).

Rate limiting behind a proxy

With everything leaving from the Worker, Supabase Auth's rate limit would treat the whole country as one IP. The Worker's fetch forwards the real IP only on the three IP-limited Auth endpoints, using the secret key, which is the only way Supabase accepts that header. Cloudflare limiters fail open on purpose: a limiter outage must not take down login the night before the election.

08Results

  • In production for the whole final stretch of the campaign, with the automatic shutdown set for 10 p.m. on Oct 24, 2026.
  • 348 commits between Oct 12 and Oct 22, 2026, from a team of two developers and a product owner.
  • 99 migrations, 59 tables, 434 database functions and 116 numbered business rules, with the server as the source of truth.
  • 60 pgTAP files with roughly 1,850 assertions, 188 end-to-end tests across 38 files and 23 unit tests, run in CI on every push.
  • Two internal security audits and two QA rounds recorded in the history, each fix shipped as its own migration.
348
commits
how we measured

git log --oneline | wc -l

11 days
active period
how we measured

git log: first commit 2026-10-12, last 2026-10-22

99
SQL migrations
how we measured

ls supabase/migrations | wc -l

59
tables
how we measured

unique create table in the migrations (public and private schemas)

434
database functions
how we measured

create [or replace] function, unique names in the migrations

49
tables with RLS
how we measured

grep 'enable row level security' in the migrations

~1,850
pgTAP assertions
how we measured

ok/is/throws_ok/results_eq etc. calls in supabase/tests (1,846), 60 test files

188
end-to-end tests
how we measured

test( in tests/e2e/*.ts (38 files)

118
routes
how we measured

git ls-files app/routes | wc -l

6
per-IP rate limiters
how we measured

ratelimits in wrangler.jsonc

9,880
disposable domains blocked
how we measured

header of the dominios_temporarios migration

~57,800
TS/TSX lines
how we measured

non-empty lines in tracked .ts/.tsx files, without package-lock and generated types

~26,700
SQL lines
how we measured

non-empty lines in tracked .sql files, without the generated domain list

09Timeline

  1. 12 Oct 2026

    First commit; portal, panel, daily mission and vote logging all ship the same day (129 commits)

  2. 12 Oct 2026

    First security audit: hit counter server-only, one account per inbox, disposable email blocking

  3. 14 Oct 2026

    Second QA round in six phases, with database pagination and slimmer portal responses

  4. 15 Oct 2026

    Uptime monitor on a separate Worker and a backup/restore rehearsal

  5. 16 Oct 2026

    Meeting points checked against forbidden zones (geofencing in the database)

  6. 19 Oct 2026

    Second audit: the publishable key can no longer execute any function; only the Worker talks to the database

  7. Oct 2026

    Oct 21–22: batched, idempotent mission email and a live scoreboard for big screens

10Stack

Frontend
  • React 19
  • React Router 8 (SSR)
  • TypeScript
  • Tailwind CSS 4
  • Leaflet
  • Lucide
Backend
  • Cloudflare Workers
  • Supabase Auth (OTP)
  • PostgREST
  • PL/pgSQL SECURITY DEFINER
  • Resend
Data
  • PostgreSQL
  • Row Level Security
  • pg_cron
  • Supabase Storage
Infra
  • Cloudflare Cache API
  • Cron Triggers
  • Workers KV
  • GitHub Actions
  • Supabase CLI
  • k6
Security
  • Cloudflare Turnstile
  • Workers Rate Limiting
  • CSP com nonce
  • CSRF por Fetch Metadata
  • Hash de IP com sal
  • Bloqueio de e-mail temporário

11Tech sheet

node@mutirao: ~/cases/mutirao
node@mutirao ─────────────
type
mobilization platform
stack
React Router 8 · Workers
db
Postgres · 59 tables · RLS
auth
email OTP · Turnstile
deploy
Cloudflare edge
tests
pgTAP · e2e · CI
modules
portal · dashboard · scoreboard
status
completed · live until the legal cutoff
$ sh scripts/montar_local.sh
supabase start ... ok
applying 99 migrations ... ok
carga: 5.571 cidades, 51.907 bairros
supabase test db ... All tests successful.
$ npm run unidade
ℹ tests 23
ℹ pass 23
ℹ fail 0
$ curl -s -X POST "$SUPABASE_URL/rest/v1/rpc/listar_ufs" -H "apikey: $PUBLISHABLE_KEY"
HTTP 401 permission denied for function listar_ufs
# a chave pública não executa nada (migração api_fechada)
$ curl -s -X POST localhost:5173/entrar -H 'Origin: https://outro-site.example'
HTTP 403 Requisição de outro site recusada.
$ curl -s localhost:5173/api/saude
{"ok":true,"banco":true,"ms":14}

© 2026 NODE. All rights reserved.