All case studies
~/cases/crm-eleitoral-alencar-santana
Platforms & CRMsChamber and AssemblyMar 2026 → Aug 2026Completed

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.

20/20
tables with RLS
16
admin-gated functions
56
policies
9
edge functions
  • supabase
  • postgres
  • rls
  • edge
  • react
  • crm
  • rate-limiting
  • lgpd
architecture.en.svgenlarge ↓

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

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

Backend and data
  • Postgres with 20 tables in public, all under RLS, plus RLS on realtime.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 a MIL- 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 ... CONCURRENTLY through 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.
Frontend
  • 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 sessionStorage so a double click or a refresh does not create a duplicate sign-up.
Infrastructure and deploy
  • Fully managed backend on Supabase: Postgres, Auth, Realtime, Storage and Edge Functions.
  • Public Edge Functions are declared with verify_jwt = false in config.toml and 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.

attackdefense
Bots and fake sign-ups

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.

API shortcut

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.

Privilege escalation

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.

Forged origin and requests

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.

Injection and hostile content

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.

Data leakage and tracking

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 in user_roles, which only an admin can change. A trigger grants the activist role when an activist record is created.
  • has_role() is SECURITY DEFINER with a pinned search_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 ativo is 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_by matches their own code, resolved by a function that reads auth.uid().
  • 16 dashboard read functions start with has_role(auth.uid(), 'admin'), raise forbidden otherwise, and have EXECUTE revoked from anon.
  • Internal functions (code generation, triggers, IP extraction, sync) have EXECUTE revoked from PUBLIC, anon and authenticated.
  • Control tables (login attempts, sign-up attempts, rate limits, idempotency) have their grants revoked plus an AS RESTRICTIVE ... USING (false) policy: only SECURITY DEFINER functions 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

  • anon has no SELECT on the supporter table, and direct INSERT was replaced by a SECURITY DEFINER RPC that returns a single scalar, the referral code. No RETURNING, 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_apoiador strips 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.messages has RLS: admins can listen on any channel, activists only on the ativista-<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
how we measured

git log --oneline | wc -l across both repositories (644 platform + 547 site)

70
migrations
how we measured

files in supabase/migrations across both repositories (61 + 9); 9 of them empty

20/20
tables with RLS
how we measured

distinct CREATE TABLE and ENABLE ROW LEVEL SECURITY in the public schema, across the migrations of both repositories

56
policies
how we measured

distinct CREATE POLICY names in the migrations of both repositories

36
database functions
how we measured

distinct CREATE [OR REPLACE] FUNCTION public.* names in the migrations

16
admin-gated functions
how we measured

functions whose body checks has_role(auth.uid(), 'admin') and raises 'forbidden'

9
edge functions
how we measured

directories in supabase/functions (7 platform + 2 site)

15
dashboard screens
how we measured

child routes of /admin in src/App.tsx, not counting the redirect

09Timeline

  1. Mar 2026

    Sign-up site and platform foundation: multi-step form, live dashboard and activist portal.

  2. Apr 2026

    Referral system with per-person codes and a welcome email.

  3. Apr 2026

    Security review: hardened RLS, a sign-up RPC with sanitization, rate limiting and anti-enumeration.

  4. Apr 2026

    CRM with funnel, tags, notes and supporter matching; hardened login and password recovery.

  5. May 2026

    Activist sign-up without manual approval, automatic CRM sync and an automation webhook.

  6. Jun 2026

    UTM tracker with a URL shortener and a gamification board with levels and rankings.

  7. Aug 2026

    Turnstile on logins and an authenticated endpoint for leads from an external form.

10Stack

Frontend
  • React 18
  • TypeScript
  • Vite
  • Tailwind CSS
  • shadcn/ui
  • TanStack Query
  • React Hook Form
  • Zod
  • Recharts
  • Framer Motion
Backend
  • Supabase Edge Functions (Deno)
  • PostgREST
  • PL/pgSQL SECURITY DEFINER
  • Supabase Auth
  • Resend
  • n8n (webhook)
Data
  • PostgreSQL
  • Row Level Security
  • Materialized view
  • Supabase Realtime
  • Supabase Storage
Infra
  • Supabase
  • ViaCEP
Security
  • 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

node@crm-eleitoral-alencar-santana: ~/cases/crm-eleitoral-alencar-santana
node@crm-eleitoral-alencar-santana ─────────────
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
$ find . -path '*/supabase/migrations/*.sql' | wc -l
70 # plataforma 61 + site 9, mesmo banco
$ curl -s "$SUPABASE_URL/rest/v1/bora_alencar?select=email" -H "apikey: $ANON_KEY"
{"code":"42501","message":"permission denied for table bora_alencar"}
$ curl -s "$SUPABASE_URL/rest/v1/rpc/submit_apoiador" -H "apikey: $ANON_KEY" -H 'content-type: application/json' -d '{"_nome":"A","_email":"x"}'
{"code":"P0001","message":"DADOS_INVALIDOS"}
$ ls supabase/functions
approve-ativista campanha-oficial-intake create-ativista
reset-password-ativista signup-ativista utm-redirect verify-turnstile
$ curl -s -X POST "$SUPABASE_URL/functions/v1/campanha-oficial-intake" -d '{}'
{"error":"Unauthorized"} # 401, API key comparada em tempo constante

© 2026 NODE. All rights reserved.