All case studies
~/cases/[classified]/alicerce
confidential · NDAPlatforms & CRMsStatewide racesDec 2025 → Sep 2026Completed

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.

221
migrations
177
RLS policies
38
edge functions
67
tables
  • supabase
  • postgres
  • rls
  • edge
  • react
  • pwa
  • crm
  • anti-bot
  • rate-limiting
  • lgpd
architecture.en.svgenlarge ↓

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

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

Backend and data
  • 221 versioned migrations, 67 tables in the public schema (per the generated types), 177 CREATE POLICY statements and 33 distinct triggers.
  • Roles in their own table: user_roles with the app_role enum and a SECURITY DEFINER has_role() function; roles are never stored on the user profile.
  • A single ingestion path (crm_ingest), called by triggers on supporters and 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 under UNIQUE (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 carry source='auto' and a source_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_events accepts anonymous client events and a BEFORE INSERT trigger 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_cron for daily email-log cleanup and pg_net to forward event-page sign-ups to an external endpoint asynchronously, without blocking or failing the INSERT if the target is down.
Frontend
  • React 18 + TypeScript + Vite (SWC), Tailwind CSS with semantic HSL tokens and shadcn/ui components on top of Radix.
  • 49 routes with react-router-dom and 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 + zod for forms, Recharts and react-simple-maps for 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.
Infrastructure and deploy
  • 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.

attackdefense
API shortcut

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

Accounts and login

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

Privilege escalation

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

Forged origin and requests

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

Tampering and audit trail

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 login Edge Function, which checks Turnstile and caps failures per IP (5) and per email (8) within a 2-minute window, returning 429 with Retry-After. The IP is stored only as a SHA-256 hash in login_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 uid is 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 to authenticated and service_role in the same migration that creates the table.
  • CRM tables are readable by admins only and writable only through SECURITY DEFINER functions; internal functions (normalization, origin mapping, triggers) have EXECUTE revoked from anon and authenticated.
  • Panel RPCs are guarded by is_admin(auth.uid()) and have EXECUTE revoked from anon.
  • 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 INSERT on the supporters table was revoked. The only public sign-up path is the submit-form Edge 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 DELETE trigger 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 DELETE of 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
how we measured

git rev-list --count HEAD

~10 months
time in production
how we measured

git log: first product commit on 2025-12-15, last on 2026-09-28

221
migrations
how we measured

ls supabase/migrations | wc -l

67
tables
how we measured

keys of public.Tables in src/integrations/supabase/types.ts (generated types)

177
RLS policies
how we measured

count of 'CREATE POLICY' in supabase/migrations/*.sql (statements, including re-creations)

38
edge functions
how we measured

ls supabase/functions | wc -l

49
routes
how we measured

count of <Route in src/App.tsx

~65k
lines of code
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

  1. Dec 2025

    First release: public landing page and gamified sign-up funnel

  2. Jan 2026

    Activist area, referral leaderboard and email marketing; 635 commits that month

  3. Feb 2026

    Installable PWA and tightened RLS policies

  4. Mar 2026

    Login moved behind a rate-limited Edge Function

  5. Apr 2026

    Deletion auditing, list restoration and a guard against mass DELETE

  6. Jul 2026

    Mobilization CRM in 28 PRs: single profile, Lead Score, UTM funnel, map and Turnstile on sign-up and login

  7. Aug 2026

    Public traffic moves to the official site; the CRM starts receiving its sign-ups via API

  8. Sep 2026

    Last commit: final redirects to the official site

10Stack

Frontend
  • 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
Backend
  • Supabase Edge Functions (Deno)
  • Supabase Auth
  • PL/pgSQL SECURITY DEFINER
  • PostgREST
  • Resend
  • n8n
  • Google Analytics 4 Data API
Data
  • PostgreSQL (Supabase)
  • Row Level Security
  • pg_cron
  • pg_net
  • Supabase Storage
Infra
  • Hospedagem estática com deploy contínuo via GitHub
  • Supabase gerenciado
Security
  • Cloudflare Turnstile
  • Rate limiting de login no banco
  • HMAC-SHA256
  • Verificação de assinatura Svix
  • Auditoria por trigger
  • Allowlist de redirecionamento

11Tech sheet

node@alicerce: ~/cases/alicerce
node@alicerce ─────────────
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
$ ls supabase/migrations | wc -l; ls supabase/functions | wc -l
221
38
$ psql -c "select crm_normalize_phone('+55 (51) 99999-0000');"
crm_normalize_phone
---------------------
51999990000
(1 row)
$ curl -s -X POST "$SUPABASE_URL/rest/v1/supporters" -H "apikey: $ANON_KEY" -d '{"nome_completo":"bot"}'
{"code":"42501","message":"permission denied for table supporters"}
$ for i in 1 2 3 4 5 6; do curl -s -o /dev/null -w '%{http_code} ' -X POST "$FN/login" -d @senha-errada.json; done
401 401 401 401 401 429
$ psql -c "delete from supporters where created_at < '2026-01-01';"
ERROR: Mass delete em public.supporters bloqueado: tentativa de apagar 1200 linhas em uma unica statement (limite: 5).

© 2026 NODE. All rights reserved.