P-11
CRM Eleitoral Alencar Santana
Public sign-ups, a referral network and a CRM on a database that doesn't trust its own public key.
clientFederal deputy Alencar Santana's pre-campaign
A supporter sign-up site, an activist portal and an internal dashboard with a CRM, gamification and link tracking, all on the same Postgres. Every supporter gets a referral link; activists see who they brought in and level up; staff merge contacts from several sources and measure every channel. Personal data stays closed to the anonymous key: sign-ups go through an RPC that sanitizes input, rate-limits attempts and returns only the referral code.
- supabase
- postgres
- rls
- edge
- react
- crm
- rate-limiting
- lgpd
01Overview
CRM Eleitoral Alencar Santana is the set of systems Alencar Santana's pre-campaign for federal deputy used to turn website visits into an organized supporter base. It is two applications on one backend: the public site, where anyone signs up in three steps, and an internal platform with two areas, one for campaign staff and one for the activists who bring in new supporters.
Every supporter gets a personal referral link. Supporters who become activists get a portal with their link, a QR code, the list of people they referred, a leaderboard and progress levels. Staff follow everything from a dashboard: live sign-ups, referrals, petitions, a CRM that merges contacts from several sources, per-channel tracking links and a gamification board.
The system ran from March to August 2026 and kept evolving in production. It was built on the assumption that the public form and the database's public key are in everyone's hands.
02The problem
- Open sign-up, sensitive data. The form collects name, email, WhatsApp, address, date of birth and party membership. All of it is personal data under Brazil's LGPD and must not leak through the same API that receives it.
- Growth through referrals. The campaign wanted to know who brought in whom, reward the most active people, and let a regular supporter become an activist without losing their referral history.
- Scattered lists. Contacts came from spreadsheets, in-person meetings, petitions, the site and, later, an external form from the official campaign. Staff needed one deduplicated view.
- Knowing what works. Every post, group and QR code needed its own link so channels could be compared by clicks and by sign-ups.
03The solution
Public site
- Three-step sign-up (personal data, address auto-filled from the postal code, ways to help) with mandatory LGPD consent and an optional manifesto signature.
- Referral link (
?ref=) that shows the referrer's first name, and an origin parameter (?origem=) that follows the visitor through to submission. - Thank-you page with the new supporter's own referral link, a welcome email and a hand-off to a messaging automation.
Activist portal
- Sign-up with a strong password, login and email password recovery.
- Tabs for sharing (personal link, QR code, one-tap share), a dashboard of referred supporters, a leaderboard and a profile.
Staff dashboard
- Live counters with realtime updates.
- Supporters (grid or list), referrals, and leaderboards for activists and for referring supporters.
- CRM: contacts in a status funnel (new → contacted → engaged → supporter → volunteer → discarded), colored tags, notes, favorites, filters by origin, city and zone, automatic matching against site sign-ups, and CSV export.
- Petitions and themed signature pages, each with KPIs, filters and export.
- In-person meetings page: a fixed-origin link and QR code for sign-ups on the spot.
- UTM tracker: per-channel links with an in-house URL shortener, click logging (device, browser, OS, referrer) and conversion into sign-ups.
- Gamification: 9 levels by referral count, top performers for the day, week and month, activity streaks, best per city and per state, overall ranking and export.
- Lead intake from an external form through an API-key-protected endpoint.
- The anonymous key can't read the supporter table: sign-ups go through a SECURITY DEFINER RPC that returns only the referral code.
- 16 dashboard functions open with has_role(auth.uid(), 'admin') and reject any other caller.
- Sanitization in the database: limits, regexes and allowlists on every field, with generic errors for anyone probing.
- Two-tier referrals (APO- and MIL-) that migrate automatically when a supporter becomes an activist.
- A CRM that dedupes on normalized email and the last 11 phone digits, with matching in a materialized view.
- Realtime under RLS: each activist can only listen on their own channel.
- 1,191 commits over 5 months of operation, across the site and the platform.
04Architecture
Read the diagram as a list (13 components, 17 connections)
Components
- Public: Public site<br>3-step sign-up<br>?ref= · ?origem=, External form<br>API key integration
- Signed-in areas: Activist portal<br>link, QR code, ranking, Admin dashboard<br>CRM, gamification, UTM
- Postgres: SECURITY DEFINER RPCs<br>submit_apoiador · dashboards<br>has_role('admin') gate, 20 tables with RLS<br>56 policies, materialized view<br>CRM × supporters
- Supabase: Auth<br>email + password<br>admin / activist roles, PostgREST + Realtime<br>RLS on realtime.messages too, Edge Functions · Deno<br>9 functions<br>signup, reset, approve, intake,<br>UTM redirect, welcome, webhook, PG
Connections
- Public site<br>3-step sign-up<br>?ref= · ?origem= → PostgREST + Realtime<br>RLS on realtime.messages too (anonymous RPC)
- Public site<br>3-step sign-up<br>?ref= · ?origem= → Edge Functions · Deno<br>9 functions<br>signup, reset, approve, intake,<br>UTM redirect, welcome, webhook (after sign-up)
- External form<br>API key integration → Edge Functions · Deno<br>9 functions<br>signup, reset, approve, intake,<br>UTM redirect, welcome, webhook (x-api-key)
- Activist portal<br>link, QR code, ranking → Cloudflare Turnstile
- Admin dashboard<br>CRM, gamification, UTM → Cloudflare Turnstile
- Activist portal<br>link, QR code, ranking → Auth<br>email + password<br>admin / activist roles (JWT)
- Admin dashboard<br>CRM, gamification, UTM → Auth<br>email + password<br>admin / activist roles (JWT)
- Activist portal<br>link, QR code, ranking → PostgREST + Realtime<br>RLS on realtime.messages too (JWT)
- Admin dashboard<br>CRM, gamification, UTM → PostgREST + Realtime<br>RLS on realtime.messages too (JWT)
- Activist portal<br>link, QR code, ranking → Edge Functions · Deno<br>9 functions<br>signup, reset, approve, intake,<br>UTM redirect, welcome, webhook
- Admin dashboard<br>CRM, gamification, UTM → Edge Functions · Deno<br>9 functions<br>signup, reset, approve, intake,<br>UTM redirect, welcome, webhook
- PostgREST + Realtime<br>RLS on realtime.messages too → SECURITY DEFINER RPCs<br>submit_apoiador · dashboards<br>has_role('admin') gate
- SECURITY DEFINER RPCs<br>submit_apoiador · dashboards<br>has_role('admin') gate → 20 tables with RLS<br>56 policies
- 20 tables with RLS<br>56 policies → materialized view<br>CRM × supporters
- Edge Functions · Deno<br>9 functions<br>signup, reset, approve, intake,<br>UTM redirect, welcome, webhook → 20 tables with RLS<br>56 policies (service role)
- Edge Functions · Deno<br>9 functions<br>signup, reset, approve, intake,<br>UTM redirect, welcome, webhook → Resend<br>transactional email
- Edge Functions · Deno<br>9 functions<br>signup, reset, approve, intake,<br>UTM redirect, welcome, webhook → n8n<br>automation
Two independent React front ends (the site and the internal platform) talk to the same Supabase project. There is no custom application server: sensitive business rules live in Postgres, in SECURITY DEFINER functions and RLS policies, and anything that needs a secret (service role, email provider key, integration API key, webhook secret) runs in Edge Functions.
05Under the hood
- Postgres with 20 tables in
public, all under RLS, plus RLS onrealtime.messages. Migrations are split across both repositories because both evolved the same database: 70 files in total. - 36 SQL and PL/pgSQL functions. Dashboard reads return pre-aggregated
jsonb(KPIs, daily series, rankings, period tops) instead of shipping rows to the browser. submit_apoiador, the site's entry point, went through 10 versions: per-field sanitization, an allowlist for ways to help, rate limiting, anti-enumeration, referral-code remapping and origin tracking.- Two-tier referrals. Supporters get an
APO-code and activists aMIL-code, both generated by triggers. When a supporter becomes an activist, a trigger moves their referrals from the old code to the new one, and new sign-ups using the old code are rewritten on submission. - Unified CRM. A trigger copies each new supporter into the contacts table, deduplicating on normalized email and the last 11 phone digits. A materialized view matches contacts to supporters and is refreshed with
REFRESH ... CONCURRENTLYthrough an admin-only function. - Name normalization with a Portuguese-aware title-case function that keeps prepositions lowercase ("Maria da Silva"), used in the backfill that split first and last names.
- Edge Functions (9, Deno): activist sign-up and password recovery, admin creation and approval of activists, external lead intake, short-link redirect with click logging, Turnstile verification, welcome email and an automation webhook.
- Integrations: Resend for transactional email, n8n through an authenticated webhook, ViaCEP for address lookup on the site, Cloudflare Turnstile on logins.
- React 18 + TypeScript + Vite, Tailwind CSS and shadcn/ui components on Radix.
- TanStack Query for data, React Hook Form + Zod for forms, Recharts for dashboards, Framer Motion for sign-up transitions and qrcode.react for QR codes.
- Supabase Realtime refreshes the dashboard, CRM and petitions without reloads.
- Lists and full exports fetch in 1,000-row pages to work around PostgREST's default cap; counts use
count: exact. - All dates are computed in the São Paulo time zone regardless of the browser's, so "today", "this week" and "this month" match the database.
- A per-form idempotency key kept in
sessionStorageso a double click or a refresh does not create a duplicate sign-up.
- Fully managed backend on Supabase: Postgres, Auth, Realtime, Storage and Edge Functions.
- Public Edge Functions are declared with
verify_jwt = falseinconfig.tomland authenticated by other means (API key, recent-sign-up check); admin functions keep the default JWT verification. - Function responses carry
X-Robots-Tag: noindex, nofollow.
06Security
8 attacks considered, 8 defenses in production.
Scripts that flood forms, inflate numbers or sweep the site for openings.
Flood the sign-up with a script
Database-side limit of 3 attempts per email and 10 per IP every 10 minutes, in a table the API can't reach.
Skipping the interface to talk straight to the database or API with the public key in hand.
Read the supporter table with the site's public key
SELECT revoked from anon; sign-ups only through a SECURITY DEFINER RPC that returns a single scalar, the referral code.
Someone with some access trying to get more: becoming admin, reading what they shouldn't, keeping revoked access.
Call dashboard functions without being an admin
16 functions check has_role(auth.uid(), 'admin') and raise 'forbidden'; EXECUTE revoked from anon.
Listen on other users' Realtime channels
RLS on realtime.messages: an activist can only join ativista-<own uid>; PII tables removed from the publication.
CSRF, fake webhooks, reused tokens, signed links and open redirects.
Abuse the welcome function as an email relay
The function takes only the referral code, checks the email and a sign-up less than 10 minutes old, and reads the recipient from the database.
Push leads through the integration endpoint without credentials
API key compared in constant time before any validation, plus 30 requests per IP every 10 minutes.
XSS, iframes, CSV formulas, disguised uploads and AI prompt manipulation.
Send malformed payloads or values the form never offers
Sanitization in the RPC: control characters stripped, length caps, per-field regexes and a 6-option allowlist.
Enumerating who is in the base, scraping personal data, tying questions to people, extracting secrets.
Find out which emails have an account
One login error message and generic success responses on sign-up and password recovery.
Authentication and roles
- Supabase Auth with email and password. Roles (
admin,ativista) live inuser_roles, which only an admin can change. A trigger grants the activist role when an activist record is created. has_role()isSECURITY DEFINERwith a pinnedsearch_path, which avoids RLS recursion and keeps the check in one place.- Activist passwords require 8+ characters with upper and lower case, a digit and a symbol, validated in the client (Zod) and again in the Edge Function.
- An activist whose status is not
ativois signed out on entry.
Authorization in the database
- All 20 tables have RLS. PII tables are readable by admins only; an activist only sees sign-ups whose
referred_bymatches their own code, resolved by a function that readsauth.uid(). - 16 dashboard read functions start with
has_role(auth.uid(), 'admin'), raiseforbiddenotherwise, and haveEXECUTErevoked fromanon. - Internal functions (code generation, triggers, IP extraction, sync) have
EXECUTErevoked fromPUBLIC,anonandauthenticated. - Control tables (login attempts, sign-up attempts, rate limits, idempotency) have their grants revoked plus an
AS RESTRICTIVE ... USING (false)policy: onlySECURITY DEFINERfunctions and the service role touch them. - The security event log is admin-readable and closed to direct writes by restrictive INSERT, UPDATE and DELETE policies.
Supporter table closed to the public key
anonhas noSELECTon the supporter table, and directINSERTwas replaced by aSECURITY DEFINERRPC that returns a single scalar, the referral code. NoRETURNING, no reading other columns through the public path.- The public supporter counter is a function that returns
count(*)and nothing else. - The "referred by" lookup returns only a first name and validates the code format.
Server-side validation and sanitization
submit_apoiadorstrips control characters, collapses whitespace, caps lengths (name 2–120, email 254, address 200), checks email by regex, phone by 10–13 digits, postal code by 8 digits, state by^[A-Z]{2}$, birth date as a real date, referral code by^[A-Za-z0-9_-]{1,32}$, and ways to help against a 6-value allowlist with at most 10 items.- Errors come back as generic codes (
DADOS_INVALIDOS,RATE_LIMITED) with no detail. - The external intake returns per-field errors, but only after the API key is authenticated.
Rate limiting and abuse
- Site sign-up: up to 3 attempts per email and 10 per IP every 10 minutes, counted in the database itself with opportunistic cleanup.
- Activist sign-up and password recovery: 3 per email and 3 per IP every 10 minutes, in a table shared by the Edge Functions.
- External intake: 30 requests per IP every 10 minutes.
- Honeypot fields on login, sign-up and recovery forms: if the hidden field is filled, the function answers success and discards the request.
- Cloudflare Turnstile on admin and activist logins, with the token checked by an Edge Function that holds the secret.
Anti-enumeration
- A single login error message ("wrong email or password").
- Activist sign-up with an existing email returns a generic success; password recovery answers the same whether or not the account exists.
- On the site, an email that is already registered does not raise an error: the RPC returns a silent success.
Integrations and email
- The external intake compares the API key in constant time and writes only through the service role; the table has no public write policy.
- The welcome-email and webhook functions accept neither recipient nor data from the client: they take only the referral code, check that the sign-up exists and is less than 10 minutes old, and read the data from the database. That rules out using them as an email or webhook relay.
- The welcome email HTML escapes every interpolated field.
Realtime
- PII tables were removed from the Realtime publication.
realtime.messageshas RLS: admins can listen on any channel, activists only on theativista-<their uid>topic.
LGPD
- Mandatory LGPD consent on the site's sign-up.
- Emails are stored as SHA-256 hashes in the security event log; short-link clicks store a hash of IP and user agent rather than the IP.
- Attempt records expire (1 hour for sign-up, 24 hours for login) through opportunistic cleanup.
07Technical challenges
Closing the table without breaking the public form
The first design had a public SELECT policy to feed the counter, which in practice exposed the entire table to the anonymous key. The fix came in layers: drop the policy, REVOKE ALL followed by GRANT INSERT on the form's columns only, a dedicated count function and, finally, replacing direct INSERT with a SECURITY DEFINER RPC that returns only the referral code. The site kept working throughout.
One person, two referral codes
Supporters and activists have different prefixes, and a supporter who had already referred people could later become an activist. To keep that history, a trigger on activist creation moves referrals from APO- to MIL-, the sign-up RPC writes the new code whenever someone uses the old one, and a migration backfilled existing data.
A CRM from sources that don't agree
Imported contacts had all-caps names, phones with and without country code, and emails with stray spaces. Normalization happens in the database: Portuguese title case, email compared by lower(btrim()), phone by its last 11 digits, a trigger that only inserts when there is no match, and a materialized view with a unique index for the matching, refreshable without blocking reads.
Numbers that add up
PostgREST caps responses at 1,000 rows and the browser may sit in another time zone. Dashboards moved to aggregated RPCs computed in São Paulo time, lists and exports to 1,000-row pages, and counts to count: exact.
08Results
- In production from March to August 2026, with 1,191 commits across both repositories.
- 70 migrations, 20 RLS-protected tables, 56 policies and 36 database functions; 9 Edge Functions.
- 15 staff screens and a 4-tab activist portal (sharing, referral dashboard, leaderboard, profile).
- 1,191
- commits
- 70
- migrations
- 20/20
- tables with RLS
- 56
- policies
- 36
- database functions
- 16
- admin-gated functions
- 9
- edge functions
- 15
- dashboard screens
how we measured
git log --oneline | wc -l across both repositories (644 platform + 547 site)
how we measured
files in supabase/migrations across both repositories (61 + 9); 9 of them empty
how we measured
distinct CREATE TABLE and ENABLE ROW LEVEL SECURITY in the public schema, across the migrations of both repositories
how we measured
distinct CREATE POLICY names in the migrations of both repositories
how we measured
distinct CREATE [OR REPLACE] FUNCTION public.* names in the migrations
how we measured
functions whose body checks has_role(auth.uid(), 'admin') and raises 'forbidden'
how we measured
directories in supabase/functions (7 platform + 2 site)
how we measured
child routes of /admin in src/App.tsx, not counting the redirect
09Timeline
- Mar 2026
Sign-up site and platform foundation: multi-step form, live dashboard and activist portal.
- Apr 2026
Referral system with per-person codes and a welcome email.
- Apr 2026
Security review: hardened RLS, a sign-up RPC with sanitization, rate limiting and anti-enumeration.
- Apr 2026
CRM with funnel, tags, notes and supporter matching; hardened login and password recovery.
- May 2026
Activist sign-up without manual approval, automatic CRM sync and an automation webhook.
- Jun 2026
UTM tracker with a URL shortener and a gamification board with levels and rankings.
- Aug 2026
Turnstile on logins and an authenticated endpoint for leads from an external form.
10Stack
- React 18
- TypeScript
- Vite
- Tailwind CSS
- shadcn/ui
- TanStack Query
- React Hook Form
- Zod
- Recharts
- Framer Motion
- Supabase Edge Functions (Deno)
- PostgREST
- PL/pgSQL SECURITY DEFINER
- Supabase Auth
- Resend
- n8n (webhook)
- PostgreSQL
- Row Level Security
- Materialized view
- Supabase Realtime
- Supabase Storage
- Supabase
- ViaCEP
- Cloudflare Turnstile
- RLS em 20/20 tabelas
- Policies RESTRICTIVE
- Rate limit em Postgres
- Honeypot
- Idempotência
- Anti-enumeração
- API key em tempo constante
11Tech sheet
- type
- sign-ups · referrals · CRM
- stack
- React 18 · TS · Vite
- db
- Postgres · 20 tables · RLS 20/20
- auth
- Supabase Auth · admin/activist roles
- backend
- 9 Edge Functions · 36 SQL functions
- modules
- site · activist portal · CRM · UTM · gamification
- status
- completed
The platform behind the campaign to end Brazil's 6x1 work schedule: nationwide sign-ups, a volunteer network and 7-layer anti-bot defense
Sign-ups, volunteers and a mobilization CRM on one database, with Postgres deciding who is who.
Sign-ups, activists and CRM on one base: every person becomes a single profile, and the database decides who scores.
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.