All case studies
~/cases/pimenta-no-senado
Platforms & CRMsStatewide racesDec 2025 → Sep 2026Completed

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.

221
migrations
38
edge functions
67
tables
~53k
TS/TSX lines
  • supabase
  • postgres
  • rls
  • edge
  • react
  • crm
  • anti-bot
  • rate-limiting
  • lgpd
  • pwa
architecture.en.svgenlarge ↓

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

real architecture · hover over the nodes
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

Backend and data
  • 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_role enum) with has_role(), is_admin() and is_ativista() as SECURITY DEFINER, never on profiles.
  • Single profile. crm_profiles has a UNIQUE normalized phone; crm_events has UNIQUE (profile_id, form_slug), one sign-up per person per page; crm_field_history stores 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 by crm_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 carry source='auto' and a source_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_links and page_events feed funnel RPCs (get_funnel_overview, get_funnel_detail) and geographic ones (get_crm_geo_overview, normalizing city names with unaccent).
  • 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_cron purges email logs daily.
  • Score import through the import-score-csv function in three phases (crm_start/apply/finish_score_import) with a batch record.
Frontend
  • React 18, Vite 5 (SWC), TypeScript, Tailwind CSS 3 with semantic HSL tokens, shadcn/ui on Radix.
  • 40 routes loaded with lazy() and Suspense; TanStack Query v5 for server state.
  • Forms with react-hook-form + zod; phones always captured as (DD) 9XXXX-XXXX through src/lib/phone.ts; URL parameters validated in urlParamValidation.ts.
  • supabaseFetchAll and 1,000-row pagination to get around PostgREST's db-max-rows on large lists.
  • Recharts and react-simple-maps for 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.
Infrastructure and deploy
  • 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_net and 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.

attackdefense
API shortcut

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

Accounts and login

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

Privilege escalation

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

Forged origin and requests

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

Data leakage and tracking

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

Tampering and audit trail

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 login function, 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, answering 429 with Retry-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 supporters only 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 DEFINER functions.
  • EXECUTE revoked from anon (and public) on admin RPCs and on RLS helpers such as is_admin, has_role and the referral-code generators. Internal CRM functions are not callable through the API.
  • Realtime locked down: RLS on realtime.messages limited to admins, because otherwise any signed-in user could subscribe to channels for admin tables even while table RLS blocked a direct SELECT.
  • Admin Edge Functions validate the JWT and call is_admin() before acting.

Forms and abuse

  • Anonymous INSERT on supporters was revoked: the only public sign-up path is the submit-form function, 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_log keeps 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 DELETE on supporters touching 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
how we measured

git rev-list --count HEAD

221
migrations
how we measured

ls supabase/migrations | wc -l

38
edge functions
how we measured

ls supabase/functions | wc -l

67
tables
how we measured

Tables section of src/integrations/supabase/types.ts

~177
SQL functions
how we measured

distinct names in CREATE [OR REPLACE] FUNCTION across the migrations

40
lazy-loaded routes
how we measured

grep -c 'lazy(' src/App.tsx

~53k
TS/TSX lines
how we measured

wc -l on tracked .ts and .tsx files (includes Edge Functions and generated types)

~14k
SQL lines
how we measured

wc -l on supabase/migrations/*.sql

Dec 2025 – Sep 2026
timeframe
how we measured

git log (first code commit 2025-12-15, last 2026-09-28)

09Timeline

  1. Dec 2025

    Kickoff: sign-up landing page and a gamified recruitment funnel

  2. Jan 2026

    Busiest month (635 commits): activist area, CRM and email marketing

  3. Apr 2026

    Audit log, recovery from a mass deletion, DELETE guard, and Realtime and RPC lockdown

  4. Jul 2026

    Mobilization CRM: single profile, automatic Lead Score, UTM funnel and map (28 PRs in 5 days)

  5. Jul 2026

    Turnstile on sign-up and login, and anonymous INSERT revoked

  6. Aug 2026

    Integration with the official site and a redirect that keeps referrals and UTMs

  7. Sep 2026

    Final event landing pages and end of development

10Stack

Frontend
  • 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
Backend
  • Supabase Edge Functions (Deno)
  • PostgREST
  • PL/pgSQL SECURITY DEFINER
  • Supabase Auth
  • Resend
  • N8N
Data
  • PostgreSQL
  • Row Level Security
  • pg_cron
  • pg_net
  • Supabase Vault
  • Supabase Storage
  • unaccent
Infra
  • Deploy contínuo via GitHub
  • PWA (vite-plugin-pwa)
  • GA4 Data API
Security
  • 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

node@pimenta-no-senado: ~/cases/pimenta-no-senado
node@pimenta-no-senado ─────────────
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
$ ls supabase/migrations | wc -l && ls supabase/functions | wc -l
221
38
$ psql -c "select crm_normalize_phone('+55 (11) 90000-0000')"
crm_normalize_phone
---------------------
11900000000
(1 row)
$ curl -s -X POST $FN/submit-form -d '{"data":{"nome_completo":"Teste","whatsapp":"11900000000"}}'
{"success":false,"error":"CAPTCHA","message":"Falha na verificação de segurança. Recarregue a página e tente novamente."}
$ grep -ri 'enable row level security' supabase/migrations | wc -l
72
$ psql -c "delete from supporters where created_at < now() - interval '1 day'"
ERROR: Mass delete em public.supporters bloqueado: tentativa de apagar 6 linhas em uma unica statement (limite: 5).
$ npm run build
vite v5.4.19 building for production...
✓ built

© 2026 NODE. All rights reserved.