P-07
Alicerce
Sign-ups, volunteers and a mobilization CRM on one database, with Postgres deciding who is who.
client████Statewide pre-campaign for a majoritarian race
The digital platform of a statewide pre-campaign: public support pages, an activist portal with referral links and a leaderboard, and an admin panel that works as a CRM. The mobilization layer merges each person into a single profile keyed by phone number, records their per-page journey and every field change, computes an event-sourced Lead Score and measures the funnel per page and per UTM. It all happens in the database, through triggers and SECURITY DEFINER functions, with RLS on every table, public sign-up only through a CAPTCHA-checked Edge Function, rate-limited login and guards against mass deletion. About ten months in production.
- supabase
- postgres
- rls
- edge
- react
- pwa
- crm
- anti-bot
- rate-limiting
- lgpd
01Overview
Alicerce is the digital platform of a statewide pre-campaign for a major elected office. It brings together three things that usually live in separate tools: the public pages where people declare support, a portal where volunteers track their referrals and download materials, and an admin panel that works as the campaign's CRM.
Visitors fill in a short form and become supporters. Those who want to do more sign up as activists and get a login, a personal referral link, outreach materials and a place on the leaderboard. The coordination team uses the panel to see where each sign-up came from, merge the same person across many pages, score each contact's engagement and send segmented email.
The system ran in production for about ten months (December 2025 to September 2026), through several waves of themed pages, events and online town halls. In the final stretch, the home page started redirecting to the campaign's official website and Alicerce stayed on as the back office: sign-ups from the new site kept flowing into the same CRM through an authenticated API.
02The problem
- One person, ten places. Every themed page, event or town hall had its own form and its own table. With no shared key, nobody could tell how many real people the campaign had, or what each one's journey looked like.
- Not all support is equal. Someone who signed up once is not the same as someone who attended three events and brought in ten people. The team needed to prioritize contacts without hand-built spreadsheets.
- Attribution. Knowing whether a sign-up came from an activist's link, a printed QR code, an email or a social media UTM, and where each page was losing people along the way.
- Public form, valuable list. A campaign contact list attracts bots, fake sign-ups and attempts at bulk reads or deletes. It is also personal data under Brazil's data protection law (LGPD).
- Campaign pace. New pages every week, shifting rules, a small team, and much of the codebase generated with an AI-assisted app builder, with hundreds of accumulated migrations.
03The solution
- Public capture: a lightweight form with a municipality combobox (official IBGE list), a Turnstile CAPTCHA, a success screen offering three next steps (share, become an activist, join the messaging channel), and themed landing pages with their own forms.
- Activist area (installable PWA): referral link with QR code, ready-made message templates, a materials gallery, the activist's leaderboard position and the top 10.
- Gamification in the panel: level distribution, rankings by state and city, top activists of the day and a daily-streak leaderboard.
- Activist onboarding: direct sign-ups are approved on the spot, with an account provisioned and an activation email sent; requests coming from the supporter success page land in an approval queue.
- Single-profile CRM: each person becomes one profile keyed by normalized phone number, with a journey of sign-ups per page and a history of every field change (name, email, state, city, neighborhood, postal code).
- Lead Score: a score summed from a configurable catalog of actions, with "temperature" bands, automatic points for each page sign-up and for each referral received, and CSV import restricted to external actions (email, messaging, social). Causes flagged as sensitive go into a care flow and are never scored.
- Acquisition tracker: a five-dimension UTM builder, per-sign-up origin, and a funnel per page and per UTM (visit → start → submit), including attribution of external traffic.
- Admin panel: 20 screens, including KPIs with a choropleth map of Brazil and city-level drill-down for the focus state, supporter and activist management, a referral leaderboard with Pareto analysis, scheduled email campaigns with metrics, events, petitions, birthdays and PDF reports.
- Privacy Mode: a header toggle (admin and activist) that blurs names and numbers on screen, so the panel can be presented in a meeting or screen-shared without exposing data.
- Single profile per phone number: one in-database ingestion function fed by triggers and idempotent backfills, with a per-page journey and field-level history.
- Event-sourced Lead Score, deduplicated by a partial unique index, with sensitive causes kept out of scoring.
- Public sign-up only through an Edge Function with Turnstile and a column whitelist; anonymous INSERT revoked on the table.
- Login through an Edge Function capped at 5 failures per IP and 8 per email over 2 minutes, with IPs stored only as hashes.
- Snapshot auditing of every deletion and a trigger that blocks any DELETE of more than 5 supporters per statement.
- 221 migrations, 67 tables and 177 RLS policies; 38 Edge Functions; 49 lazily loaded routes.
04Architecture
Read the diagram as a list (15 components, 14 connections)
Components
- Clients: Public landing pages<br>light form + themed LPs, Activist area<br>(PWA), Admin panel<br>CRM + reports
- Edge Functions (Deno) · 38: login<br>rate limit by IP + email, submit-form<br>captcha + allowlist, activation<br>HMAC-signed link, inbound webhooks<br>secret · Svix
- Postgres · RLS: supporters<br>audit + DELETE lock, unified profile<br>crm_ingest, Lead Score, funnel<br>LPs · UTMs · page_events
- Supabase: Auth<br>email + password, PostgREST + RPCs<br>is_admin / has_role, PG
Connections
- Clientes → Static SPA<br>Vite + Service Worker<br>Cloudflare Turnstile
- Static SPA<br>Vite + Service Worker<br>Cloudflare Turnstile → login<br>rate limit by IP + email
- Static SPA<br>Vite + Service Worker<br>Cloudflare Turnstile → submit-form<br>captcha + allowlist
- Static SPA<br>Vite + Service Worker<br>Cloudflare Turnstile → PostgREST + RPCs<br>is_admin / has_role (user JWT)
- login<br>rate limit by IP + email → Auth<br>email + password
- activation<br>HMAC-signed link → Auth<br>email + password
- submit-form<br>captcha + allowlist → supporters<br>audit + DELETE lock (service role)
- PostgREST + RPCs<br>is_admin / has_role → funnel<br>LPs · UTMs · page_events
- PostgREST + RPCs<br>is_admin / has_role → unified profile<br>crm_ingest
- supporters<br>audit + DELETE lock → unified profile<br>crm_ingest (trigger)
- unified profile<br>crm_ingest → Lead Score (trigger)
- Resend · n8n · GA4<br>external forms → inbound webhooks<br>secret · Svix (authenticated webhook)
- inbound webhooks<br>secret · Svix → supporters<br>audit + DELETE lock
- PG → Resend · n8n · GA4<br>external forms (pg_net / email / GA4)
The frontend is a React SPA served as static files, with no server of its own. Anything that needs privilege goes through one of 38 Deno Edge Functions or through Postgres SECURITY DEFINER functions called as RPCs. The database is the source of truth: profile merging and scoring happen in triggers, not in the client, so every entry point (the site's own forms, the chatbot webhook, the external site's API, imports) produces the same result.
05Under the hood
- 221 versioned migrations, 67 tables in the public schema (per the generated types), 177
CREATE POLICYstatements and 33 distinct triggers. - Roles in their own table:
user_roleswith theapp_roleenum and aSECURITY DEFINERhas_role()function; roles are never stored on the user profile. - A single ingestion path (
crm_ingest), called by triggers onsupportersand on the event-forms table, and by idempotent backfill functions for historical data (including keyset batches for the largest table). It normalizes the phone, finds or creates the profile, logs every changed field to history and records the journey event underUNIQUE (profile_id, form_slug), so there is one record per person per page even if the same sign-up is reprocessed. - Event-sourced Lead Score: the score is the sum of
crm_score_events. Automatic events carrysource='auto'and asource_ref; a partial unique index on(action_slug, source_ref)prevents scoring the same sign-up or referral twice, while CSV-imported events dedupe by person, action and day (in São Paulo time). - Page funnel:
page_eventsaccepts anonymous client events and aBEFORE INSERTtrigger resolves the page and the UTM; reads are admin-only and exposed through aggregate RPCs (get_funnel_overview,get_funnel_detail,get_utm_funnel). - Server-side paginated RPCs for the large lists (profiles, supporters, consolidated CRM), with filtering and sorting in Postgres, plus a frontend helper that pages with
.range()when the full table is needed beyond PostgREST's row cap. - Integrations via Edge Functions: transactional and campaign email through Resend (with an events webhook and metrics sync), n8n automations, a messaging chatbot, Google Analytics 4 (historical and realtime), a postal-code proxy, and ingestion of external events and forms.
- Jobs:
pg_cronfor daily email-log cleanup andpg_netto forward event-page sign-ups to an external endpoint asynchronously, without blocking or failing theINSERTif the target is down.
- React 18 + TypeScript + Vite (SWC), Tailwind CSS with semantic HSL tokens and shadcn/ui components on top of Radix.
- 49 routes with
react-router-domand 40 lazily loaded pages (lazy+Suspense), keeping the admin panel's weight out of the public page. - TanStack Query for server state,
react-hook-form+zodfor forms, Recharts andreact-simple-mapsfor charts and maps, jsPDF + html2canvas for reports. - PWA via
vite-plugin-pwa: installable, auto-updating, with HTML served network-first so a new release takes effect without a hard refresh. - Accessibility and LGPD in the UI: an accessibility widget, a consent banner and a published privacy policy.
- Two layers of data masking: pure masking functions (name, email, phone) and Privacy Mode, applied through a
<body>attribute and CSS rules.
- Frontend published as static files through continuous deployment from the main branch, with a route fallback (
_redirects) so deep links work. - Fully managed backend on Supabase: Postgres, Auth, Storage and Edge Functions.
- Cloudflare Turnstile as the CAPTCHA on sign-up and login.
- Domain redirects handled inside the SPA, preserving UTM query strings, when public traffic moved to the official website.
06Security
8 attacks considered, 8 defenses in production.
Skipping the interface to talk straight to the database or API with the public key in hand.
Bot mass-signing up straight through the REST API, skipping the CAPTCHA
Anonymous INSERT revoked on the supporters table; the only public path is an Edge Function that verifies Turnstile server-side
Brute force, rogue account creation, password reset and account takeover.
Password brute force on the admin or activist login
Login through an Edge Function with Turnstile, capped at 5 failures per IP and 8 per email within 2 minutes; 429 with Retry-After; IP stored only as a hash
Someone with some access trying to get more: becoming admin, reading what they shouldn't, keeping revoked access.
A regular user reading the CRM or calling panel RPCs
RLS with admin-only reads, writes only through SECURITY DEFINER functions, RPCs guarded by is_admin() with no EXECUTE for anon; roles in a separate table
CSRF, fake webhooks, reused tokens, signed links and open redirects.
Forging events on the email provider's webhook
Svix signature verified before the body is parsed; other webhooks require a per-integration secret
Abusing the email click link as an open redirect for phishing
Redirects only to allowlisted hosts
Forging an account activation link for another user
uid signed with HMAC-SHA256 and the signature recomputed server-side before access is granted
Fiddling with counts, wiping the base, editing history or denying what was authorized.
Sending extra columns on sign-up to forge referrals or flags
The row is built from a column whitelist inside the Edge Function
Wiping the supporter list with a compromised account
A statement-level trigger blocks DELETEs above 5 rows; a BEFORE DELETE trigger stores a full snapshot and the actor in a table no client can write
Authentication
- Admin and activist logins never hit Auth directly: they go through the
loginEdge Function, which checks Turnstile and caps failures per IP (5) and per email (8) within a 2-minute window, returning429withRetry-After. The IP is stored only as a SHA-256 hash inlogin_attempts. - Password policy (at least 8 characters, one uppercase letter and one digit) with a live checklist in the UI.
- Account activation via a link whose
uidis signed with HMAC-SHA256; the activation function recomputes the signature before granting access.
Authorization and RLS
- RLS enabled on every table in the public schema, with explicit
GRANTs toauthenticatedandservice_rolein the same migration that creates the table. - CRM tables are readable by admins only and writable only through
SECURITY DEFINERfunctions; internal functions (normalization, origin mapping, triggers) haveEXECUTErevoked fromanonandauthenticated. - Panel RPCs are guarded by
is_admin(auth.uid())and haveEXECUTErevoked fromanon. - Activists see full data only for the supporters they referred themselves; the public leaderboard reads a view that exposes only first name, referral code and referral count.
Anonymous surface
- Direct anonymous
INSERTon the supporters table was revoked. The only public sign-up path is thesubmit-formEdge Function, which verifies Turnstile, validates the minimum server-side and builds the row from a column whitelist, so fields such as referral codes or flags cannot be set through the API. - Public lookup functions (email existence, referral code) return the bare minimum and never expose names or roles.
Integrations
- The Resend webhook is verified against its Svix signature before the body is parsed.
- Automation, chatbot and external-ingestion webhooks each require their own secret header.
- Email click tracking only redirects to allowlisted hosts, closing the open redirect.
Protecting the list
- A
BEFORE DELETEtrigger stores a full snapshot of every deleted supporter, with who deleted it, their role, the client address and the application name, in a table only admins can read and no client can write. - A statement-level trigger blocks any
DELETEof more than 5 supporters at once unless the session explicitly sets an administrative flag. - Self-service account deletion has been disabled since the incident described below, pending a review of that flow.
LGPD
- Consent banner, published privacy policy, hashed IPs, on-screen masking of sensitive data and a monitoring notice on the login screens.
07Technical challenges
Merging the same person from many sources
The phone number was the only key present in nearly every form, but it arrived in many formats. We found that the inherited "digits only" helper also stripped the character 0, which would have made different numbers collide. The fix was a new normalizer built on regexp_replace that drops the 55 country code, a single in-database ingestion path shared by real-time triggers and backfills, and the rule that the profile keeps its earliest origin page while each later sign-up becomes a journey event plus a field-history entry.
An index that broke the public form
The backfill needed to match supporters to profiles by phone, so a functional index on crm_digits(whatsapp) was added. In the same migration, as good practice, EXECUTE on that function had been revoked from anon. In production the public form started failing with permission denied for function: Postgres evaluates an index expression with the privileges of whoever runs the INSERT, not the function owner. The hotfix granted EXECUTE back for that one pure function, documented why in the migration, and turned it into a rule in the project guide.
A mass deletion and the recovery
In April 2026 the supporter list suffered a mass deletion. The response came in layers: first an audit table with full snapshots written by a trigger, then restoring the rows from those snapshots (jsonb_populate_record with ON CONFLICT DO NOTHING), a statement-level trigger that blocks deletes above 5 rows, and disabling self-service account deletion until further review.
Closing the sign-up back door
A front-end CAPTCHA protects nothing if the table accepts anonymous INSERTs through the REST API. Sign-up was moved to an Edge Function that verifies Turnstile and filters columns, and once the frontend was on that path, the anon INSERT grant was revoked. Turnstile was designed to fail open only while its secret was not yet configured, and closed from then on.
08Results
- About ten months in production, from December 2025 to September 2026.
- 1,847 commits, 221 migrations, 67 tables, 38 Edge Functions and 49 routes.
- The CRM layer shipped as a run of 28 pull requests over five days: single profile, manual and automatic Lead Score, UTM funnel, geographic map and the hardening of the public sign-up.
- When public traffic moved to the official website, Alicerce kept receiving the new site's sign-ups through an authenticated API, with no loss of history or scores.
- 1,847
- commits
- ~10 months
- time in production
- 221
- migrations
- 67
- tables
- 177
- RLS policies
- 38
- edge functions
- 49
- routes
- ~65k
- lines of code
how we measured
git rev-list --count HEAD
how we measured
git log: first product commit on 2025-12-15, last on 2026-09-28
how we measured
ls supabase/migrations | wc -l
how we measured
keys of public.Tables in src/integrations/supabase/types.ts (generated types)
how we measured
count of 'CREATE POLICY' in supabase/migrations/*.sql (statements, including re-creations)
how we measured
ls supabase/functions | wc -l
how we measured
count of <Route in src/App.tsx
how we measured
non-empty lines in tracked files: TSX 34,434, TS 10,350 (6,987 of them in Edge Functions), SQL 13,300; generated types excluded (cloc unavailable)
09Timeline
- Dec 2025
First release: public landing page and gamified sign-up funnel
- Jan 2026
Activist area, referral leaderboard and email marketing; 635 commits that month
- Feb 2026
Installable PWA and tightened RLS policies
- Mar 2026
Login moved behind a rate-limited Edge Function
- Apr 2026
Deletion auditing, list restoration and a guard against mass DELETE
- Jul 2026
Mobilization CRM in 28 PRs: single profile, Lead Score, UTM funnel, map and Turnstile on sign-up and login
- Aug 2026
Public traffic moves to the official site; the CRM starts receiving its sign-ups via API
- Sep 2026
Last commit: final redirects to the official site
10Stack
- React 18
- TypeScript 5
- Vite 5 (SWC)
- Tailwind CSS 3
- shadcn/ui (Radix)
- TanStack Query 5
- React Router 6
- react-hook-form + zod
- Recharts
- react-simple-maps
- vite-plugin-pwa
- Supabase Edge Functions (Deno)
- Supabase Auth
- PL/pgSQL SECURITY DEFINER
- PostgREST
- Resend
- n8n
- Google Analytics 4 Data API
- PostgreSQL (Supabase)
- Row Level Security
- pg_cron
- pg_net
- Supabase Storage
- Hospedagem estática com deploy contínuo via GitHub
- Supabase gerenciado
- Cloudflare Turnstile
- Rate limiting de login no banco
- HMAC-SHA256
- Verificação de assinatura Svix
- Auditoria por trigger
- Allowlist de redirecionamento
11Tech sheet
- type
- mobilization CRM + PWA
- stack
- React 18 · Vite · TypeScript
- db
- Postgres · 67 tables · RLS
- auth
- Supabase Auth · rate-limited login
- edge
- 38 Edge Functions (Deno)
- anti-bot
- Cloudflare Turnstile
- modules
- landing pages · activist · CRM · lead score · funnel
- status
- completed · 10 months in production
Sign-ups, activists and CRM on one base: every person becomes a single profile, and the database decides who scores.
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.