All case studies
~/cases/crm-eleitoral-dimas-gadelha
Internal systemsChamber and AssemblyApr 2026 → Aug 2026In production

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.

18/18
tables with RLS
39 of 46
SECURITY DEFINER functions
52
policies created
62
SQL migrations
  • cloudflare
  • supabase
  • postgres
  • rls
  • lgpd
  • react
architecture.en.svgenlarge ↓

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

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

Backend and data
  • 62 versioned SQL migrations, 18 tables in public, 3 views and 46 SQL/PL/pgSQL functions (39 of them SECURITY DEFINER with a pinned search_path).
  • .functions.ts / .server.ts convention: the .functions.ts file exports the server function and is safe to import from the client; the implementation using the service_role client lives in .server.ts or is imported dynamically inside the handler, so it never reaches the browser bundle. There are 13 server functions.
  • Walk-in service: the gi_register_visit RPC 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). An AFTER INSERT trigger on leads_abordagem computes the score; saving the configuration creates a new version and an audit row with the before/after state and who changed it; pontuacao_recalcular reprocesses the base. The leads_abordagem_scored view 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 fetchAllRange helper 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.
Frontend
  • 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 ensureQueryData in loaders and useSuspenseQuery in components; no useEffect + fetch for 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).
Infrastructure and deploy
  • Cloudflare Workers with nodejs_compat, configured through wrangler.jsonc and 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.

attackdefense
API shortcut

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

Privilege escalation

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

Data leakage and tracking

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

Tampering and audit trail

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
how we measured

git rev-list --count HEAD

62
SQL migrations
how we measured

ls supabase/migrations | wc -l

18/18
tables with RLS
how we measured

grep for CREATE TABLE and ENABLE ROW LEVEL SECURITY in the migrations

52
policies created
how we measured

grep -ic 'create policy' in the migrations (5 later DROP POLICY)

39 of 46
SECURITY DEFINER functions
how we measured

script over the migrations: distinct CREATE FUNCTION names with SECURITY DEFINER in the header

13
server functions
how we measured

grep -c 'createServerFn(' in src/lib/*.functions.ts and src/utils/*.functions.ts

7
edge functions
how we measured

ls supabase/functions (excluding _shared)

~29.9k
TS/TSX lines
how we measured

wc -l on git ls-files *.ts *.tsx, excluding the generated routeTree.gen.ts and types.ts

5,080
SQL lines
how we measured

cat supabase/migrations/*.sql | wc -l

09Timeline

  1. 27 Apr 2026

    App scaffold, supporters table and RLS hardening, dropping a permissive read policy

  2. Apr 2026

    Apr 29–30: volunteer area with referrals and ranking; institutional contact base with admin-only access

  3. May 2026

    May 12–15: street form, analytics and a clustered map with a geocoding cache

  4. Jun 2026

    Jun 2–8: walk-in service with one profile per phone, visit history and backdating

  5. 07 Jul 2026

    Field survey form, versioned automatic scoring and a choropleth territorial map

  6. Jul 2026

    Jul 21–30: executive PDF report and a minimal-access role to issue it

  7. 15 Aug 2026

    Official form version with LGPD consent enforced in the database

10Stack

Frontend
  • React 19
  • TanStack Start
  • TanStack Router
  • TanStack Query
  • TypeScript
  • Tailwind CSS 4
  • shadcn/ui
  • Radix
  • React Hook Form
  • Recharts
  • Leaflet
  • jsPDF
Backend
  • TanStack Start server functions
  • Cloudflare Workers
  • Supabase Auth
  • Supabase Edge Functions (Deno)
  • PL/pgSQL
  • Resend
Data
  • PostgreSQL
  • Row Level Security
  • Triggers e views
  • pg_trgm
  • Supabase Storage
Infra
  • Cloudflare Workers
  • Vite 7
  • Lovable
  • Supabase CLI
  • Bun
Security
  • 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

node@crm-eleitoral-dimas-gadelha: ~/cases/crm-eleitoral-dimas-gadelha
node@crm-eleitoral-dimas-gadelha ─────────────
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
$ ls supabase/migrations | wc -l
62
$ grep -h 'ENABLE ROW LEVEL SECURITY' supabase/migrations/*.sql | sort -u | wc -l
18 # 18 tabelas, 18 com RLS
$ psql -c "select public.leads_abordagem_relatorio_rows();" # como usuário sem papel
ERROR: FORBIDDEN
$ psql -c "insert into leads_abordagem (form_versao, nome_abordado, lgpd_aceite) values (2, 'Teste', false);"
ERROR: new row for relation "leads_abordagem" violates check constraint "leads_abordagem_lgpd_v2_chk"
$ git log --oneline | wc -l
736

© 2026 NODE. All rights reserved.