P-12
CRM Eleitoral Dimas Gadelha
Field research, walk-in service and constituent relations in one system, with permissions decided in the database.
clientState representative Dimas Gadelha's office
A platform for state representative Dimas Gadelha's office that brings together a public site, street forms, a volunteer area and a management panel. Field survey forms are scored and classified automatically in Postgres, tracked by territory and team, and summarized in an executive PDF report. It runs on Cloudflare Workers with Supabase, RLS on all 18 tables and a minimal-access role that issues the report without seeing a single form.
- cloudflare
- supabase
- postgres
- rls
- lgpd
- react
01Overview
CRM Eleitoral Dimas Gadelha is the digital platform of state representative Dimas Gadelha's office. One system covers the public website, the forms field teams use on the street, an area for volunteers and a management panel for the coordination team. Everyone who reaches the office, whether through the site, at a neighborhood walk-in service or in a field survey interview, becomes an organized record with a source, a territory and a history.
Field research is the core. Interviewers fill out a standardized form on their phones; the database scores each form on its own and places it in configurable tiers; coordinators follow everything by neighborhood, municipality and team, and issue an executive PDF report in one click.
02The problem
- Scattered contacts. Website sign-ups, street service lists and an older institutional contact base lived in different places with no deduplication.
- Field research on paper or spreadsheets. No standard questions, no classification, no way to cross answers with territory or with the team that ran the interview.
- Sensitive personal data. Political opinion, religion and phone numbers: LGPD (Brazil's GDPR) has to be taken seriously, with proof of consent and least-privilege access.
- Audiences with very different trust levels. Forms open to anyone, authenticated volunteers, coordinators with full access, and people who only need an aggregated report.
03The solution
- Public site and supporter sign-up in steps, with a referral link per volunteer.
- Walk-in service: a street form that recognizes the phone number as it is typed, shows whether this is the 1st or Nth visit, and accepts a backdated visit for paper forms entered later. One profile per normalized phone, one row per visit.
- Field survey form: a standardized questionnaire with a team block (coordinator and interviewer), city from the official municipality list, neighborhood autocomplete and a versioned LGPD consent.
- Automatic scoring: per-answer weights, classification tiers and interviewer profile, all configurable by coordinators, with every change versioned and audited.
- Coordination panel: KPIs and charts, a records table with search and filters by tier, neighborhood and team (multi-select), CSV export that honors the active filters, clustered heatmap, choropleth by municipality and neighborhood crossed with classification, birthdays, volunteer approval and role management.
- Executive PDF report, generated in the browser, available from the panel and from a dedicated route for a minimal-access role.
- Volunteer area: personal dashboard, ranking, levels by number of referrals and outreach materials.
- RLS on all 18 tables; roles in their own table, checked by has_role with EXECUTE revoked from anon.
- A 'relatorio' role with no read policy at all: the PDF comes from a column-scoped SECURITY DEFINER RPC with the phone masked.
- Survey scoring computed by a trigger, with versioned configuration and a before/after audit trail.
- LGPD consent enforced in three layers (Zod, server and a database CHECK), with the consent text version recorded.
- The service_role client confined to .server.ts files or dynamic imports inside the handler.
- Panel and territorial map aggregations done in Postgres, not in the browser.
04Architecture
Read the diagram as a list (18 components, 21 connections)
Components
- Clients: Public<br>landing · sign-up, Field teams<br>outreach forms<br>mobile walk-in service, Volunteers<br>logged-in area · ranking, Coordination<br>admin panel, relatorio role<br>only issues the PDF
- Cloudflare Workers: TanStack Start · SSR<br>React 19, createServerFn<br>Zod on every input, requireSupabaseAuth<br>JWT via getClaims
- Postgres: 18 tables<br>RLS on all, 39 SECURITY DEFINER functions<br>has_role · aggregations, Versioned scoring<br>trigger + audit
- Supabase: Auth<br>email/password + OAuth, PostgREST<br>publishable key, 7 Edge Functions<br>approval · email · account, PG
Connections
- Public<br>landing · sign-up → TanStack Start · SSR<br>React 19
- Field teams<br>outreach forms<br>mobile walk-in service → TanStack Start · SSR<br>React 19
- Volunteers<br>logged-in area · ranking → TanStack Start · SSR<br>React 19
- Coordination<br>admin panel → TanStack Start · SSR<br>React 19
- relatorio role<br>only issues the PDF → TanStack Start · SSR<br>React 19
- TanStack Start · SSR<br>React 19 → createServerFn<br>Zod on every input
- createServerFn<br>Zod on every input → 18 tables<br>RLS on all (public forms<br>service_role server-side only)
- createServerFn<br>Zod on every input → requireSupabaseAuth<br>JWT via getClaims
- requireSupabaseAuth<br>JWT via getClaims → PostgREST<br>publishable key (user token)
- Volunteers<br>logged-in area · ranking → Auth<br>email/password + OAuth
- Coordination<br>admin panel → Auth<br>email/password + OAuth
- relatorio role<br>only issues the PDF → Auth<br>email/password + OAuth
- PostgREST<br>publishable key → 18 tables<br>RLS on all
- PostgREST<br>publishable key → 39 SECURITY DEFINER functions<br>has_role · aggregations
- 39 SECURITY DEFINER functions<br>has_role · aggregations → Versioned scoring<br>trigger + audit
- 7 Edge Functions<br>approval · email · account → Auth<br>email/password + OAuth
- 7 Edge Functions<br>approval · email · account → Resend<br>transactional email
- Coordination<br>admin panel → IBGE<br>boundaries and municipalities
- createServerFn<br>Zod on every input → Nominatim / OSM<br>cached geocoding
- relatorio role<br>only issues the PDF → jsPDF<br>in-browser report
- Coordination<br>admin panel → jsPDF<br>in-browser report
A single TanStack Start app runs on Cloudflare Workers, with SSR and typed server functions (createServerFn) the client calls as RPC. All new backend logic lives there; Supabase Edge Functions were kept only for the legacy volunteer approval, password reset and email flows. Postgres is the source of truth for permissions: roles live in a separate table and are checked by a has_role function inside policies and RPCs.
05Under the hood
- 62 versioned SQL migrations, 18 tables in
public, 3 views and 46 SQL/PL/pgSQL functions (39 of themSECURITY DEFINERwith a pinnedsearch_path). .functions.ts/.server.tsconvention: the.functions.tsfile exports the server function and is safe to import from the client; the implementation using theservice_roleclient lives in.server.tsor is imported dynamically inside the handler, so it never reaches the browser bundle. There are 13 server functions.- Walk-in service: the
gi_register_visitRPC upserts the profile (unique per normalized phone) and inserts the visit in one transaction, returning whether the profile is new and the visit count. When the profile is new and the phone is not yet in the main base, the server also creates the supporter. - Survey scoring: the model lives in tables (
pontuacao_pergunta,pontuacao_opcao,pontuacao_faixa). AnAFTER INSERTtrigger onleads_abordagemcomputes the score; saving the configuration creates a new version and an audit row with the before/after state and who changed it;pontuacao_recalcularreprocesses the base. Theleads_abordagem_scoredview serves forms with score, tier and a pending flag. - Server-side aggregation: dashboard, territorial map, analytics and birthdays are RPCs returning aggregated JSON instead of shipping rows to the browser.
- PostgREST's 1,000-row cap: a
fetchAllRangehelper pages with.range()over a deterministic order, with a 200k-row safety cap, used by exports and bulk selections. - Normalization in the database: triggers normalize municipality and state on supporters and contacts; phones are compared by digits only.
- TanStack Start + React 19, file-based routing (28 route files: 13 public pages, admin login, a protected layout and 12 panel screens).
- TanStack Query with
ensureQueryDatain loaders anduseSuspenseQueryin components; nouseEffect + fetchfor initial loads. - Tailwind CSS 4 with CSS theme tokens, shadcn/ui + Radix (47 base components), React Hook Form + Zod sharing schemas between client and server.
- Maps: Leaflet with marker clustering and heatmap; a choropleth built on IBGE's official municipal mesh.
- PDF report with jsPDF and jspdf-autotable, generated entirely in the browser from a column-scoped RPC: daily table, tier distribution, teams and in-person vs. remote participation, always in Brasília time.
- About 30k lines of TypeScript/TSX (generated files excluded).
- Cloudflare Workers with
nodejs_compat, configured throughwrangler.jsoncand the Cloudflare Vite plugin. - Deploys through Lovable, connected to the main branch: change, validate with a production build, push.
- Supabase for Postgres, Auth, Storage (bucket for outreach materials) and Deno Edge Functions.
- Resend for transactional email (volunteer approval, password reset).
- Server variables (
SUPABASE_SERVICE_ROLE_KEY,RESEND_API_KEY) exist only as environment secrets; the client gets the URL and publishable key only.
06Security
7 attacks considered, 7 defenses in production.
Skipping the interface to talk straight to the database or API with the public key in hand.
Read citizens' data by calling the API directly with the public key
RLS on all 18 tables; supporter and contact reads are admin-only, or a volunteer seeing their own record and referrals
Call internal functions and aggregations through RPC
EXECUTE revoked from PUBLIC and anon on internal functions; aggregations abort with FORBIDDEN without the admin role
Someone with some access trying to get more: becoming admin, reading what they shouldn't, keeping revoked access.
Grant yourself the admin role
Roles live in user_roles, inserts only by existing admins via has_role, a function with EXECUTE revoked from anon
Use the report account to pull individual survey forms
The role has no SELECT policy; only a SECURITY DEFINER RPC with a column slice, no name and the phone masked
Enumerating who is in the base, scraping personal data, tying questions to people, extracting secrets.
Extract the service_role key from the browser bundle
Privileged client only in .server.ts or dynamic imports in the handler; admin functions validate the JWT and role before using it
Fiddling with counts, wiping the base, editing history or denying what was authorized.
Store a survey form without consent by forging the request
Zod only accepts true, the server repeats the rule and a database CHECK rejects the row; consent time and text version are stored
Change classification weights without a trace
Every save creates a new version and an audit row with before, after and author; each result stores the version that produced it
Authorization in the database, not the UI. All 18 tables have RLS enabled. Roles (admin, ativista, relatorio) live in user_roles, never on the profile, and are checked by has_role(uid, role), a SECURITY DEFINER function whose execution was revoked from anon and PUBLIC. The panel layout gates on session and role, but that is only UX: anyone calling the API directly with the public key hits the policies.
Minimal policies. Supporter reads are admin-only, or a volunteer seeing their own record and their referrals. The institutional contact base is admin-only. Only admins write roles, and a policy stops an admin from removing their own admin role. An early permissive read policy (USING (true)) was found and dropped in a migration, with the confirmation screen switched to an RPC returning only the fields it needs.
Functions closed by default. Trigger and internal functions had EXECUTE revoked from PUBLIC, anon and authenticated (including a block that revokes in bulk). Aggregation RPCs start with IF NOT has_role(auth.uid(), 'admin') THEN RAISE EXCEPTION 'FORBIDDEN'.
A minimal-access role for the report. An account holding only the relatorio role logs in and lands on a page with a single button. It has no read policy on leads_abordagem; the data comes from leads_abordagem_relatorio_rows(), a SECURITY DEFINER RPC that accepts only admin or relatorio and returns a slice without name, phone or free-text fields. The phone becomes a presence marker (******), because the report only counts how many forms have a contact.
Service role kept server-side. The service_role client is only imported in .server.ts files or dynamically inside a handler. Admin server functions go through the requireSupabaseAuth middleware, which validates the JWT with getClaims, and then an explicit role check before touching the privileged client. A gap of this kind in the admin map was fixed in May 2026.
LGPD. The survey form requires consent in three layers: the Zod schema only accepts true, the server function repeats the rule, and a database CHECK rejects a current-version form without consent. Consent stores the timestamp and the version of the consent text, so rewording it does not invalidate consents already collected; older forms are never retroactively marked as consented. Volunteers can delete their own account through a dedicated server function.
Input validation. Every server function validates with Zod (max lengths, closed enums, bounded arrays). Field dates in the future or older than 90 days are rejected server-side, in Brasília time. The admin login has a honeypot, length limits and, as extra friction on top of Supabase Auth's native rate limiting, a 15-minute client-side lock after 5 failures with exponential backoff.
Hygiene. Panel, field form and report routes are noindex. No server key in the bundle; Google OAuth goes through a managed broker instead of exposing provider configuration to the client.
07Technical challenges
Granting a report without granting the data
Someone needed to issue the report but must not see individual forms. Instead of partial read policies, which leak through any well-built query, the answer was a role with no SELECT policy at all plus a SECURITY DEFINER RPC returning exactly the columns the report uses, mirroring the column list defined in the PDF code. The phone is masked into a presence marker before it leaves the database.
Configurable scoring without losing traceability
Coordinators wanted to tune weights and tiers during the campaign period. The model moved into tables, the computation into a trigger, and every configuration save now creates a version and a before/after audit row. Each result stores the configuration version that produced it, and a recompute reprocesses the whole base when the rules change.
Replacing the official form with data already collected
Version 2 of the form changed questions and added LGPD consent. The migration added form_versao with a CHECK applied NOT VALID and validated afterwards, made consent mandatory only for version 2, and the server writes the version explicitly so a client running an old bundle is never recorded as the new form. Panel, scoring and RPCs handle both versions.
One profile per phone across repeated visits
The same person shows up at several walk-in services, sometimes entered days later. gi_register_visit normalizes the phone, upserts the profile, inserts the visit with an optional backdate and returns the visit number, keeping first and last visit consistent. The form looks up the phone as it is typed to warn the team before submitting.
08Results
- 736 commits between April and August 2026; 62 migrations; 18 tables with RLS; 52 policies created.
- Field research moved from paper to a standardized form with automatic classification and a one-click executive report.
- 736
- commits
- 62
- SQL migrations
- 18/18
- tables with RLS
- 52
- policies created
- 39 of 46
- SECURITY DEFINER functions
- 13
- server functions
- 7
- edge functions
- ~29.9k
- TS/TSX lines
- 5,080
- SQL lines
how we measured
git rev-list --count HEAD
how we measured
ls supabase/migrations | wc -l
how we measured
grep for CREATE TABLE and ENABLE ROW LEVEL SECURITY in the migrations
how we measured
grep -ic 'create policy' in the migrations (5 later DROP POLICY)
how we measured
script over the migrations: distinct CREATE FUNCTION names with SECURITY DEFINER in the header
how we measured
grep -c 'createServerFn(' in src/lib/*.functions.ts and src/utils/*.functions.ts
how we measured
ls supabase/functions (excluding _shared)
how we measured
wc -l on git ls-files *.ts *.tsx, excluding the generated routeTree.gen.ts and types.ts
how we measured
cat supabase/migrations/*.sql | wc -l
09Timeline
- 27 Apr 2026
App scaffold, supporters table and RLS hardening, dropping a permissive read policy
- Apr 2026
Apr 29–30: volunteer area with referrals and ranking; institutional contact base with admin-only access
- May 2026
May 12–15: street form, analytics and a clustered map with a geocoding cache
- Jun 2026
Jun 2–8: walk-in service with one profile per phone, visit history and backdating
- 07 Jul 2026
Field survey form, versioned automatic scoring and a choropleth territorial map
- Jul 2026
Jul 21–30: executive PDF report and a minimal-access role to issue it
- 15 Aug 2026
Official form version with LGPD consent enforced in the database
10Stack
- React 19
- TanStack Start
- TanStack Router
- TanStack Query
- TypeScript
- Tailwind CSS 4
- shadcn/ui
- Radix
- React Hook Form
- Recharts
- Leaflet
- jsPDF
- TanStack Start server functions
- Cloudflare Workers
- Supabase Auth
- Supabase Edge Functions (Deno)
- PL/pgSQL
- Resend
- PostgreSQL
- Row Level Security
- Triggers e views
- pg_trgm
- Supabase Storage
- Cloudflare Workers
- Vite 7
- Lovable
- Supabase CLI
- Bun
- RLS em todas as tabelas
- Papéis com has_role SECURITY DEFINER
- Zod em toda entrada
- Consentimento LGPD com CHECK
- Honeypot
- service_role só no servidor
11Tech sheet
- type
- legislative office platform · civic tech
- stack
- TanStack Start · React 19 · TS
- db
- Postgres · 18 tables · RLS 18/18
- auth
- Supabase Auth · 3 roles via has_role
- deploy
- Cloudflare Workers
- modules
- field · walk-in service · volunteers · dashboard · report
- status
- in production
The platform that brought content creators into the campaign to end online betting in Brazil: nationwide sign-ups, an inflation-proof public counter and a brute-force-hardened admin panel.
Volunteer mobilization under a legal deadline and a hostile audience: every rule lives in the database.
A WhatsApp relationship operation with human sending, immutable history and the database as the final word.
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.