P-13
Pimenta no Senado
Sign-ups, activists and CRM on one base: every person becomes a single profile, and the database decides who scores.
clientStatewide campaign
A statewide campaign platform with three fronts: sign-up landing pages, an activist dashboard with referral links and rankings, and an admin CRM with a single phone-keyed profile, automatic Lead Score, UTM funnel and geographic map. Built on Supabase with 38 Edge Functions and 221 migrations, with RLS on every table, Turnstile and rate limiting on login, public sign-up only through a server function, and guards against mass deletion.
- supabase
- postgres
- rls
- edge
- react
- crm
- anti-bot
- rate-limiting
- lgpd
- pwa
01Overview
Pimenta no Senado is the digital platform of a statewide election campaign. It brings together three things that usually live in separate tools: the public pages where people sign up as supporters, a dashboard for volunteers (the "activists") to follow who they brought in and see how they rank, and an internal CRM where the coordination team sees the whole base, segments it, sends email and measures where every sign-up came from.
In practice, a citizen arrives through a landing page or a referral link, fills in a short form and becomes a single profile keyed by phone number, even if they signed up on five different pages over several months. Each action they take adds points to a Lead Score, which the team uses to tell who is cold, warm or ready to be asked for more.
The system ran in production from December 2025 to September 2026, went through more than 1,800 commits and 221 database migrations, and handled voters' personal data under Brazil's data protection law (LGPD), in an environment where the audience includes opponents.
02The problem
- Scattered records. The same person showed up in
supporters, in per-campaign landing page tables, in event forms and in an imported legacy base. Nobody could answer "how many real people do we have" or "what has this person done with us". - Attribution. The team needed to know which page, which UTM and which activist brought each sign-up, and to tell supporter referrals apart from new-activist recruitment.
- Measurable engagement. Actions (sign-up, referral, attendance, outside interactions by email and WhatsApp) had to become a configurable score, without spreadsheets.
- Exposure. Public forms attract bots, the activist area puts an authenticated surface in front of thousands of people outside the staff, and the database holds voters' names, phones and emails.
03The solution
Public capture
- Multi-step landing form plus a lightweight form with a three-way post-signup step (share, become an activist, join the official channel).
- Themed landing pages registered in
landing_pages, each with its own sign-up points and cause. - City field as a combobox over the national statistics office's municipality list (bottom sheet on mobile).
- Installable PWA, accessibility widget, consent banner and privacy policy.
Activist area
- Personal referral link with QR code, message templates and downloadable materials.
- Overall, regional and city rankings, levels, achievements and a daily streak.
- Restricted visibility: through RLS, an activist can only read their own record and the supporters they referred.
Admin CRM
- Single profile per phone number, with a journey (one event per sign-up page), field-by-field history, score and band, filters by tag, origin, state and band, and CSV export.
- Lead Score with configurable actions, bands and causes; automatic points on sign-up and referral; CSV import limited to outside actions.
- Tracker: origins, a five-dimension UTM builder and a conversion/drop-off funnel per page and per UTM.
- Dashboard with KPIs, a country choropleth with drill-down into the priority state and an origin filter; one failing card does not take the page down.
- Activist approval queue, single and bulk invites, password-link resend.
- Email marketing with templates, scheduling and metrics synced from the provider.
- Referral ranking with a Pareto curve, events, petitions, a news carousel and PDF reports.
- Privacy Mode: a header toggle masks names, phones and figures on screen, so the dashboard can be shown in meetings or screen recordings.
- A single phone-keyed profile on top of several sign-up tables, with one ingestion function shared by real-time triggers and an idempotent 20,000-row batched backfill.
- Idempotent Lead Score: two partial unique indexes keep automatic-event dedup (per source) apart from CSV-import dedup (per day).
- Public sign-up only through an Edge Function: anonymous INSERT revoked, Turnstile verified server-side and a column whitelist.
- Login with Turnstile and a two-key rate limit (5 failures per IP, 8 per email in 2 min), with IPs stored only as hashes.
- After a mass-deletion incident, rows restored from the audit log and a statement-level trigger that rejects any DELETE over 5 rows.
- Forwarding to external systems moved from the browser to a pg_net trigger with its key in Vault.
04Architecture
Read the diagram as a list (23 components, 23 connections)
Components
- Clients: Supporter<br>(landing, LPs, PWA), Activist<br>(/ativista · ranking, link, QR), Coordination<br>(/admin · CRM)
- React 18 + Vite SPA: Turnstile<br>sign-up and login, tracking.ts<br>UTM + funnel, Privacy Mode<br>client-side masking
- 38 Edge Functions (Deno): submit-form<br>Turnstile + whitelist, login<br>Turnstile + rate limit, provision / activation<br>HMAC link uid+sig, admin functions<br>JWT + is_admin(), webhooks<br>secret / Svix
- Postgres: RLS on public.*<br>user_roles + has_role(), Mobilization CRM<br>crm_ingest · phone-keyed profile, Lead Score<br>idempotent events, Guardrails<br>audit log + mass DELETE guard, pg_net + Vault<br>server-side forwarding, pg_cron<br>log cleanup
- Supabase: EF, Auth<br>email and password, PostgREST<br>SECURITY DEFINER RPCs, PG
Connections
- Supporter<br>(landing, LPs, PWA) → Turnstile<br>sign-up and login
- Turnstile<br>sign-up and login → submit-form<br>Turnstile + whitelist
- Activist<br>(/ativista · ranking, link, QR) → login<br>Turnstile + rate limit
- Coordination<br>(/admin · CRM) → login<br>Turnstile + rate limit
- login<br>Turnstile + rate limit → Auth<br>email and password
- Activist<br>(/ativista · ranking, link, QR) → PostgREST<br>SECURITY DEFINER RPCs
- Coordination<br>(/admin · CRM) → PostgREST<br>SECURITY DEFINER RPCs
- Coordination<br>(/admin · CRM) → admin functions<br>JWT + is_admin()
- tracking.ts<br>UTM + funnel → PostgREST<br>SECURITY DEFINER RPCs
- submit-form<br>Turnstile + whitelist → PG
- PostgREST<br>SECURITY DEFINER RPCs → RLS on public.*<br>user_roles + has_role()
- RLS on public.*<br>user_roles + has_role() → Mobilization CRM<br>crm_ingest · phone-keyed profile
- Mobilization CRM<br>crm_ingest · phone-keyed profile → Lead Score<br>idempotent events
- provision / activation<br>HMAC link uid+sig → Auth<br>email and password
- provision / activation<br>HMAC link uid+sig → Resend<br>transactional email and campaigns
- admin functions<br>JWT + is_admin() → Resend<br>transactional email and campaigns
- admin functions<br>JWT + is_admin() → GA4<br>Data API
- webhooks<br>secret / Svix → PG
- N8N / WhatsApp<br>webhooks → webhooks<br>secret / Svix
- Resend<br>transactional email and campaigns → webhooks<br>secret / Svix
- pg_net + Vault<br>server-side forwarding → N8N / WhatsApp<br>webhooks
- submit-form<br>Turnstile + whitelist → Cloudflare<br>Turnstile siteverify
- login<br>Turnstile + rate limit → Cloudflare<br>Turnstile siteverify
The SPA talks to Supabase in two ways: authenticated reads through PostgREST (RLS plus SECURITY DEFINER RPCs that check the caller's role), and sensitive operations through Edge Functions that hold the service role server-side. CRM writes never come from the client: triggers on supporters and forms_eventos call crm_ingest() inside the database.
05Under the hood
- 67 tables in the public schema (per the generated types), 221 migrations from Dec 2025 to Sep 2026, about 177 SQL functions and 38 Deno Edge Functions (8k lines).
- Roles in their own table (
user_roles,app_roleenum) withhas_role(),is_admin()andis_ativista()asSECURITY DEFINER, never onprofiles. - Single profile.
crm_profileshas aUNIQUEnormalized phone;crm_eventshasUNIQUE (profile_id, form_slug), one sign-up per person per page;crm_field_historystores every field change. A profile's origin is always its oldest sign-up. - One ingestion path.
crm_ingest()is the only writer into the CRM, called by real-time triggers and bycrm_backfill_core()/crm_backfill_crm_geral_batch()in batches of 20,000, all idempotent, to migrate the legacy base. - Lead Score. The score is the sum of
crm_score_events. Automatic events carrysource='auto'and asource_ref, with a partial unique index on(action_slug, source_ref); CSV imports dedupe on(profile_id, action_slug, occurred_on). The same sign-up never scores twice, while an activist can still get several referrals on the same day. Sensitive causes are routed to a care flow and do not score. - Funnel and UTM.
landing_pages,utm_linksandpage_eventsfeed funnel RPCs (get_funnel_overview,get_funnel_detail) and geographic ones (get_crm_geo_overview, normalizing city names withunaccent). - Server-side integrations. Sign-ups are forwarded to external endpoints by a trigger using
pg_net(async, never blocks the INSERT), with the API key read from Supabase Vault rather than the code.pg_cronpurges email logs daily. - Score import through the
import-score-csvfunction in three phases (crm_start/apply/finish_score_import) with a batch record.
- React 18, Vite 5 (SWC), TypeScript, Tailwind CSS 3 with semantic HSL tokens, shadcn/ui on Radix.
- 40 routes loaded with
lazy()andSuspense; TanStack Query v5 for server state. - Forms with
react-hook-form+zod; phones always captured as(DD) 9XXXX-XXXXthroughsrc/lib/phone.ts; URL parameters validated inurlParamValidation.ts. supabaseFetchAlland 1,000-row pagination to get around PostgREST'sdb-max-rowson large lists.- Recharts and
react-simple-mapsfor charts and maps; jsPDF + html2canvas for reports. - PWA via
vite-plugin-pwa: HTML is fetched network-first on navigation so a new release applies without a hard refresh, and heavy media stays out of the precache.
- Continuous deploy of the front end from the main branch, with an SPA fallback in
public/_redirects. - Managed Supabase backend: Postgres, Auth, Storage, Edge Functions,
pg_cron,pg_netand Vault. - When the campaign's official site launched, the old domain's home started redirecting to it through a script at the top of
<head>, while keeping/admin,/ativista, the activation routes, the landing pages and any URL with?ref=or?utm_intact, so referral links and CRM attribution kept working. - There is no CI and no automated test suite in the repository.
06Security
7 attacks considered, 7 defenses in production.
Skipping the interface to talk straight to the database or API with the public key in hand.
Call admin RPCs or permission helpers with the public key
EXECUTE revoked from anon and public; admin functions check is_admin() internally
Brute force, rogue account creation, password reset and account takeover.
Brute force or credential stuffing on admin and activist login
Turnstile on login plus a limit of 5 failures per IP and 8 per email every 2 minutes, answering 429 with Retry-After
Someone with some access trying to get more: becoming admin, reading what they shouldn't, keeping revoked access.
A signed-in activist trying to read the whole supporter base
RLS: activists only read their own row and their referrals; CRM tables are admin-only
CSRF, fake webhooks, reused tokens, signed links and open redirects.
Forge email-provider events
Svix signature verified on the raw body before any processing
Forge an activation link to take over an account
Link signed with HMAC-SHA256 over the uid and verified server-side
Enumerating who is in the base, scraping personal data, tying questions to people, extracting secrets.
Subscribe to Realtime channels to receive admin table data
RLS on realtime.messages restricted to admins
Fiddling with counts, wiping the base, editing history or denying what was authorized.
Wipe the supporter base in a single operation
A statement-level trigger rejects DELETEs over 5 rows; an audit log keeps a snapshot of every deleted row
Authentication
- Admin and activist login goes through the
loginfunction, which requires a server-verified Cloudflare Turnstile token and applies a two-key rate limit: 5 failures per IP and 8 per email in a 2-minute window, answering429withRetry-After. IPs are stored only as SHA-256 hashes. - Password policy: at least 8 characters, one uppercase letter and one digit.
- Account activation through a link signed with HMAC-SHA256 (
uid+sig, base64url), verified server-side before a password can be set. - A discreet monitoring notice on the login screens.
Authorization and RLS
- RLS on every public table, with explicit
GRANTs in the same migration. - An activist reads
supportersonly for their own row and their referrals (referred_by_supporter_id = get_ativista_supporter_id(auth.uid())); only admins update or delete. - CRM tables: admin-only reads, writes only through
SECURITY DEFINERfunctions. EXECUTErevoked fromanon(andpublic) on admin RPCs and on RLS helpers such asis_admin,has_roleand the referral-code generators. Internal CRM functions are not callable through the API.- Realtime locked down: RLS on
realtime.messageslimited to admins, because otherwise any signed-in user could subscribe to channels for admin tables even while table RLS blocked a directSELECT. - Admin Edge Functions validate the JWT and call
is_admin()before acting.
Forms and abuse
- Anonymous
INSERTonsupporterswas revoked: the only public sign-up path is thesubmit-formfunction, which checks Turnstile and writes only whitelisted columns, blocking column injection through PostgREST. - Public lookup endpoints return the bare minimum, no name or role, to limit enumeration.
- Webhooks: Svix signature verified on the raw body before parsing (email provider); shared-secret headers on automation integrations.
Data protection
supporters_audit_logkeeps a JSON snapshot of every deleted supporter, with who deleted it, their role and origin, readable only by admins.- Mass-delete guard: a statement-level trigger rejects any
DELETEonsupporterstouching more than 5 rows unless an explicit session bypass is set. - Self-service account deletion disabled pending review after an incident.
- Integration secrets kept out of the code: Edge Function environment variables, and Supabase Vault for calls made from the database.
LGPD
- Public consent banner and privacy policy, hashed IPs in login tracking, Privacy Mode for showing the dashboard without exposing personal data, and activists only see full data for their own referrals.
07Technical challenges
Merging the base without losing history
Sign-ups lived in several tables with phones in different formats, and the legacy digit-cleaning function had a bug: its translate also stripped the digit 0, producing wrong keys. The fix was a new layer on top of the existing tables rather than a replacement: crm_digits() with regexp_replace, crm_normalize_phone() (drops the country code, requires at least 10 digits), a UNIQUE normalized phone and a single ingestion function shared by the triggers and the batched backfill. The first release revoked EXECUTE on crm_digits and broke public sign-ups in production (42501), because a functional index on supporters evaluates the function with the inserting role's privileges; the hotfix granted EXECUTE back on that pure function and the rule was documented.
Idempotent scoring from two sources
CSV-imported events and automatic events need different duplicate rules. Two partial unique indexes (per day for imports, per source for automatic events) let the database decide, so reprocessing sign-ups or re-importing a file never inflates a score.
A mass-deletion incident
In April 2026, supporters were deleted in bulk. The audit log had been in place for four days, so a migration reinserted the rows from each ID's latest snapshot (jsonb_populate_record, ON CONFLICT DO NOTHING) and, in the same migration, added the statement-level guard using a transition table. Self-deletion was switched off, and three days later another migration revoked anon EXECUTE on admin RPCs and denied non-admins any read on the approval queue.
Sign-ups lost in the browser
Event sign-ups were forwarded to an external endpoint by a front-end fetch, exposed to PWA caching, blockers and bad connections. The call moved into a pg_net trigger that is async, never fails the INSERT and reads its key from Vault; the PWA switched to network-first HTML so fixes reached users without a hard refresh.
08Results
- In production from December 2025 to September 2026, with 1,847 commits and 221 migrations.
- 38 Edge Functions, 67 tables, about 177 SQL functions, 40 lazily loaded routes.
- The mobilization CRM shipped as a series of 28 pull requests between July 2 and 6, 2026: single profile, automatic Lead Score, UTM funnel, geographic map, Turnstile and the anonymous-INSERT lockdown.
- 1,847
- commits
- 221
- migrations
- 38
- edge functions
- 67
- tables
- ~177
- SQL functions
- 40
- lazy-loaded routes
- ~53k
- TS/TSX lines
- ~14k
- SQL lines
- Dec 2025 – Sep 2026
- timeframe
how we measured
git rev-list --count HEAD
how we measured
ls supabase/migrations | wc -l
how we measured
ls supabase/functions | wc -l
how we measured
Tables section of src/integrations/supabase/types.ts
how we measured
distinct names in CREATE [OR REPLACE] FUNCTION across the migrations
how we measured
grep -c 'lazy(' src/App.tsx
how we measured
wc -l on tracked .ts and .tsx files (includes Edge Functions and generated types)
how we measured
wc -l on supabase/migrations/*.sql
how we measured
git log (first code commit 2025-12-15, last 2026-09-28)
09Timeline
- Dec 2025
Kickoff: sign-up landing page and a gamified recruitment funnel
- Jan 2026
Busiest month (635 commits): activist area, CRM and email marketing
- Apr 2026
Audit log, recovery from a mass deletion, DELETE guard, and Realtime and RPC lockdown
- Jul 2026
Mobilization CRM: single profile, automatic Lead Score, UTM funnel and map (28 PRs in 5 days)
- Jul 2026
Turnstile on sign-up and login, and anonymous INSERT revoked
- Aug 2026
Integration with the official site and a redirect that keeps referrals and UTMs
- Sep 2026
Final event landing pages and end of development
10Stack
- React 18
- Vite 5 (SWC)
- TypeScript 5
- Tailwind CSS 3
- shadcn/ui
- Radix UI
- TanStack Query 5
- React Router 6
- react-hook-form
- zod
- Recharts
- react-simple-maps
- jsPDF
- Supabase Edge Functions (Deno)
- PostgREST
- PL/pgSQL SECURITY DEFINER
- Supabase Auth
- Resend
- N8N
- PostgreSQL
- Row Level Security
- pg_cron
- pg_net
- Supabase Vault
- Supabase Storage
- unaccent
- Deploy contínuo via GitHub
- PWA (vite-plugin-pwa)
- GA4 Data API
- Cloudflare Turnstile
- Rate limit por IP e por e-mail
- Hash de IP (SHA-256)
- Links de ativação HMAC-SHA256
- Webhooks Svix
- Audit log + trava de DELETE em massa
- RLS em realtime.messages
11Tech sheet
- type
- mobilization CRM
- stack
- React 18 · Vite · TS
- db
- Postgres · 67 tables · RLS
- auth
- Supabase Auth · Turnstile · rate limit
- edge
- 38 Edge Functions (Deno)
- deploy
- continuous deploy + PWA
- modules
- sign-ups · activists · CRM · lead score
- status
- completed
Sign-ups, volunteers and a mobilization CRM on one database, with Postgres deciding who is who.
The platform behind the campaign to end Brazil's 6x1 work schedule: nationwide sign-ups, a volunteer network and 7-layer anti-bot defense
Public sign-ups, a referral network and a CRM on a database that doesn't trust its own public key.
Want a system like this for your operation?
Node designs, builds and protects tailor-made platforms for campaigns, elected officials and movements.

TECHNOLOGY AND POLITICAL INTELLIGENCE
© 2026 NODE. All rights reserved.