P-06
Vínculo
An institutional relationship CRM where every rule, permission and audit trail lives in Postgres.
client████Political liaison team (internal, invite-only system)
An internal system for political outreach teams to organize contacts, relationship history, projects and mobilization campaigns with a funnel, per-channel tracking and configurable priority. No custom backend: the SPA talks straight to Postgres, and RLS, triggers and RPCs decide everything. Passwordless login, mandatory 2FA for admins, an append-only activity log and an internal security audit already applied.
- supabase
- postgres
- rls
- react
- mfa
- audit-log
- edge
- cloudflare
- etl
01Overview
Vínculo is a CRM for political outreach teams. It brings together the contacts a team works with (public leaders and organizations), the history of each relationship, projects (workstreams) and campaigns: concrete actions such as "get this group of leaders to post on two social networks by date X".
Before it, that knowledge lived in spreadsheets, WhatsApp threads and in the heads of whoever made each call. The system turns it into a queryable base: who has been contacted, on which channel, with what outcome, who owns the follow-up, what the combined reach of everyone who agreed is, and who should be approached first.
Access is invite-only, with two user levels. Operators are never blocked from doing their job: what differs between users is scope (which projects they can see), not capability. Sensitive functions (account management, the activity log, deletion decisions) belong to administrators, who must use a second factor.
02The problem
- Scattered data with no owner. The same leader showed up in several spreadsheets under different spellings, and nobody knew who had last spoken to them.
- The wrong unit of work. The first version tracked status and audience at the project level. Testing with the team made it clear that real work happens in the action (campaign), where there is a target audience, a funnel and a result. The model had to be reorganized while the system was already in use.
- A sensitive base, a small team. This is a political relationship database: a leak, a silent edit or insider misuse would be costly. Everything had to leave a trail without turning into an unreadable log.
- Bulk data entry. Contacts arrive as CSV/XLSX spreadsheets in every format imaginable: "1,2 mil" followers, party names spelled out, states by full name, phone numbers with and without country code.
03The solution
- Contacts (leaders and organizations): a full profile, reach per channel, electoral history (a list, not a field), up to three office contacts with their role, free-form normalized tags, a versioned notes timeline and a per-contact audit card.
- Server-side filtered contact list: state, party, office, alignment, age bracket, tags, project/campaign; CSV export hardened against CSV injection.
- Campaigns as the unit of work: a closed 9-step funnel (shared by every campaign, so results add up across them), up to 5 configurable sub-statuses per step, per-channel tracking (post URL, or a "no link" flag for stories and lives), an owner per contact, timeline-style notes, a ready-made WhatsApp message and bulk actions.
- Per-campaign configurable priority: each campaign scores contact metadata ("for this action, state X is worth 2, over 50k followers is worth 3") and the table sorts by the total, with the reason for every point in a tooltip.
- Potential vs. delivered reach: the sum of the campaign channels' audience, computed once per person at project level so the same follower is not counted once per action.
- Guided import (CSV/XLSX): a 5-step wizard with synonym-based column auto-mapping, Brazilian-Portuguese-aware normalization, batch vocabulary resolution, duplicate detection (tax ID → e-mail → name + state) and an undoable batch.
- Public election data load: scripts that read official results, match them against the base and write vote history and tags idempotently, plus a page showing exactly what the load changed.
- "War Room" dashboard: KPIs, charts and a map of Brazil as plain SVG (paths pre-generated with
d3-geo, no mapping library in the bundle). - Administration: user invites, levels, deactivation, deletion with a diagnosis of what blocks it, a note-deletion queue and a unified activity log.
- RLS on all 26 tables, with project scope resolved by shared helper functions; anon reaches nothing but one rate-limited failed-login RPC.
- Targeted 2FA: the aal2 predicate guards logs, audit and account management without getting in the way of daily work.
- A unified, append-only, client-proof activity log with cursor pagination: 1.5 ms per page over 100k rows.
- Per-campaign configurable contact priority, computed by a pure function and explained point by point in a tooltip.
- CSV/XLSX import with Brazilian-Portuguese normalization, duplicate detection and an undoable batch.
- A post-migration SQL check that verifies the hardening of 20 sensitive functions and has already caught a real regression.
04Architecture
Read the diagram as a list (15 components, 18 connections)
Components
- Browser (SPA): Operator<br>per-project scope, Admin<br>mandatory second factor
- Cloudflare: Pages<br>build Vite · SPA fallback, Turnstile<br>captcha on login
- Edge Functions (Deno): admin-usuarios<br>re-checks admin + aal2<br>service_role, acesso-emergencia<br>captcha + rate limit<br>recovery code
- Postgres: 26 tables with RLS<br>scoped by project/campaign, Read RPCs<br>server-side filters · cursor, Triggers<br>audit · invariants · ownership guard, events + audit<br>append-only, pg_cron<br>reminders · log retention
- Supabase: Auth<br>email OTP · TOTP (aal2)<br>24 h / 7 day session, PostgREST<br>no anon access, EF, PG
Connections
- Operator<br>per-project scope → Pages<br>build Vite · SPA fallback
- Admin<br>mandatory second factor → Pages<br>build Vite · SPA fallback
- Operator<br>per-project scope → Turnstile<br>captcha on login
- Admin<br>mandatory second factor → Turnstile<br>captcha on login
- Pages<br>build Vite · SPA fallback → PostgREST<br>no anon access (user JWT)
- Pages<br>build Vite · SPA fallback → Auth<br>email OTP · TOTP (aal2)<br>24 h / 7 day session
- Admin<br>mandatory second factor → admin-usuarios<br>re-checks admin + aal2<br>service_role (Bearer aal2)
- Pages<br>build Vite · SPA fallback → acesso-emergencia<br>captcha + rate limit<br>recovery code
- acesso-emergencia<br>captcha + rate limit<br>recovery code → Turnstile<br>captcha on login
- admin-usuarios<br>re-checks admin + aal2<br>service_role → Auth<br>email OTP · TOTP (aal2)<br>24 h / 7 day session
- acesso-emergencia<br>captcha + rate limit<br>recovery code → Auth<br>email OTP · TOTP (aal2)<br>24 h / 7 day session
- PostgREST<br>no anon access → 26 tables with RLS<br>scoped by project/campaign
- PostgREST<br>no anon access → Read RPCs<br>server-side filters · cursor
- 26 tables with RLS<br>scoped by project/campaign → Triggers<br>audit · invariants · ownership guard
- Triggers<br>audit · invariants · ownership guard → events + audit<br>append-only
- pg_cron<br>reminders · log retention → events + audit<br>append-only
- Open election<br>data (offline load) → 26 tables with RLS<br>scoped by project/campaign (Python scripts<br>idempotent)
- GitHub<br>CI: lint · 526 tests · build → Pages<br>build Vite · SPA fallback (push to main → auto-deploy)
The core decision is no custom backend: the SPA talks straight to PostgREST with the user's JWT, and every rule that matters lives in Postgres (RLS, triggers, CHECK constraints, RPCs). The only code that runs with elevated privileges sits in two edge functions, and each one re-validates the caller before touching the service_role.
05Under the hood
- Postgres on Supabase: 58 versioned migrations (0001–0045;
b/c/dsuffixes are fixes within the same round), 26 live tables, 15 enums, 69 functions, 53 triggers and 80 RLS policies in the final state. - RLS on every table, with policies only for
authenticated. Scope is resolved by shared helper functions (app.acessa_projeto,acessa_campanha,acessa_participante_campanha,gerencia_projeto) reused across all policies. - Invariants in the database, not the UI: at most 5 sub-statuses per step, 10 criteria × 6 brackets for priority, 3 office contacts, 20 tags per contact; changing the main status drops sub-status marks that no longer apply; per-channel tracking derives
realizadofrom the URL (or the "no link" flag) inside a normalization trigger. - RPCs where a view stops scaling:
fn_contatos_filtrados/fn_contatos_csvtake filters in the request body (POST) instead of a list of ids in the URL, andfn_projeto_contatosmerges two sources (campaigns + manual links) deduplicated per person. - Two complementary trails:
auditoria(a generic trigger on every write, storing only the diff on UPDATE) andeventos(login, logout, export, profile views, account management), both append-only. pg_cron: daily reminders at 09:00 and a monthly log purge driven by the retention policy.- Election data load: Python scripts (658 lines) read the public result JSONs, drop any personal identifiers the source carries at read time, match civil/ballot name + state against the base and write behind a partial unique index, so running them twice duplicates nothing.
- React 19 + Vite 6 + TypeScript, React Router 7 and TanStack Query 5 for caching and invalidation. 180 files in
src/, organized by domain (features/*, each with its screens and a*Api.tsdata module). - In-house design system ("Command Center"): dark-first, HSL CSS tokens with Tailwind v4 (
@theme inline), accessible primitives on top of Radix UI, Space Grotesk / Inter / JetBrains Mono. - Bundle discipline: the XLSX parser only loads through a dynamic
import()on the import route, and the dashboard map is pre-generated SVG. - Testable pure logic: priority scoring, filters, normalization, duplicate detection, import validation, log grouping, age, CSV, dates, tags and sanitization live in modules with no React/Supabase dependency, covered by 526 Vitest tests (10 marked
it.failsto document known bugs not yet fixed). - 20 routes, guarded by
RotaAutenticadaandRotaAdmin, with heavy pages lazy-loaded.
- Cloudflare Pages, auto-deploying on push to
main, SPA fallback via_redirects. Cloudflare only holds publishable values (Supabase URL and anon key, Turnstile site key). - Supabase: Postgres, Auth, Edge Functions (Deno),
pg_cron. Migrations are applied and versioned; TypeScript types are generated from the schema. - GitHub Actions CI:
lint(0 errors),testandbuild(tsc -bis the type gate) on every push and PR, with Node pinned via.nvmrc. - Post-migration check: a SQL script verifies, for 20 sensitive functions, the
aal2requirement, a pinnedsearch_pathand no grant toanon. It caught a regression where a functiondrop/createhad silently wiped its hardening.
06Security
8 attacks considered, 8 defenses in production.
Brute force, rogue account creation, password reset and account takeover.
Create an account by typing any e-mail on the login screen
Sign-up disabled and shouldCreateUser: false; accounts only come from the admin edge function, and unknown e-mails get the same response as valid ones.
Take over an admin's e-mail and open logs or manage accounts
Logs, audit and account management require aal2 (TOTP) in the database and in the edge function; removing the factor also needs aal2, and enrolling one sends a notification e-mail.
Brute-forcing recovery codes
Edge function with Turnstile, a 5-attempt limit per IP and per e-mail counting only database-written events, fail-closed (503), and codes stored as bcrypt hashes.
Someone with some access trying to get more: becoming admin, reading what they shouldn't, keeping revoked access.
An operator promoting themselves to admin through the API
authenticated can only UPDATE usuarios.nome; level and status go through the edge function, which re-checks admin + aal2 and won't demote the last active admin.
A user with access to a project making themselves its owner and hiding it from others
Ownership-guard triggers: only an admin can change the owner, and only a manager can change a project's access or a campaign's project.
XSS, iframes, CSV formulas, disguised uploads and AI prompt manipulation.
Inject a formula into the exported spreadsheet
Generated CSVs escape = + - @ and exports above 500 contacts alert the admins.
Blowing up the bill, exhausting quotas, flooding logs and knocking functions down with load.
Flood the log with fake failed logins
The only anon-callable RPC is rate-limited to 20/min per IP with a closed type catalog; the attack alert de-duplicates by type (1 per hour).
Fiddling with counts, wiping the base, editing history or denying what was authorized.
Delete or edit the trail after acting
eventos and auditoria are append-only with no UPDATE/DELETE policy for anyone; writes go through security definer RPCs that resolve author, IP and user agent server-side.
Authentication
- Passwordless login: users get a one-time code by e-mail (
signInWithOtp→verifyOtp). Old passwords were deliberately invalidated so no parallel path with a reusable secret remains. - No public sign-up:
shouldCreateUser: falseon the client, sign-ups disabled, and accounts only created through the admin edge function. - Anti-enumeration: an e-mail without access gets the same response as a valid one; the attempt is logged (admin-only).
- Cloudflare Turnstile captcha on login, verified server-side.
- Server-enforced session limits: 24 h of inactivity and a 7-day hard cap. "Remember this device" only picks between
localStorageandsessionStorage; the real limit is enforced by Auth. - Mandatory TOTP for administrators. An admin with no factor is held on the security page until they enroll one. Security-key (WebAuthn) support is implemented but kept behind a flag until the platform enables it.
- Recovery codes: 8 single-use codes, stored only as bcrypt hashes and shown once. Redemption goes through an edge function with captcha, a rate limit of 5 attempts per IP and per e-mail, and fails closed: if counting fails it returns 503 instead of letting the attempt through. Each use is logged and alerts the admins.
Authorization
- RLS on 100% of tables;
anoncan reach nothing except one RPC that records failed logins. - Targeted
aal2enforcement: thefn_e_admin_aal2()predicate guards the log RPCs, theeventosandauditoriapolicies and all account management. Theadmin-usuariosedge function reads theaalclaim from the Bearer token and refuses without it. - No privilege escalation:
authenticatedcan only UPDATEusuarios.nome; level and status go through the edge function, which re-checks admin + 2FA server-side, refuses to demote the last active admin and validatesusuario_idas a UUID. - Ownership guard (October internal audit): triggers stop someone who merely has access to a project from becoming its owner, changing its access setting or moving a campaign to another project, a path that previously allowed hiding or deleting other people's work.
- Sensitive deletions are admin-only: profiles are soft-deleted behind a trigger guard; note deletion goes through a request queue; users with history are deactivated rather than deleted, preserving authorship.
Audit trail and detection
eventosandauditoriaare append-only: nobody, not even an admin, has an UPDATE/DELETE policy. Writes go throughsecurity definerRPCs that resolve author, IP and user agent on the server and validate the event type against a closed catalog.- The public failed-login RPC is rate-limited to 20/min per IP.
- In-app alerts: 5+ failed logins in 15 min, exports of more than 500 contacts and account changes.
Data and privacy
- Data minimization: contacts store a birth year, not a date; the CSV export is minimal identification with no sensitive personal data; in the election load, personal identifiers from the source are dropped at read time and never stored or committed.
- Defined retention: read events for 6 months, other events and audit rows for 24 months, purged on a schedule.
Validation and hardening
- Input sanitization (
sanitizarTexto,urlOuNulo, etc.) and database CHECKs; nodangerouslySetInnerHTML, noeval. security definerfunctions with a pinnedsearch_path, verified by script after each migration.- Generated CSVs escape
= + - @against formula injection. - The
service_rolekey only exists inside edge functions; the React Query cache is cleared on sign-out or account switch.
07Technical challenges
Reshaping the model while it was in use
Status, sub-status and audience moved from the project to the campaign (migrations 0023–0029). The main status became a closed 9-value vocabulary precisely so "Delivered" could be summed across campaigns; what each campaign configures is the sub-statuses. The manual project × contact link survived without a status, and the project's contact table now shows each person's origin (campaign, manual or both), because visible ambiguity beats implicit ambiguity. Triggers make sure changing a status never leaves orphaned marks.
A log you can read, not just scroll
The Auth service's native audit log was not populated on the hosted platform, so the app records its own events. With 100k test rows, the view that unioned auditoria and eventos took 125 ms to 1.5 s for the first page. It was replaced by an RPC that applies LIMIT inside each UNION ALL branch, joins only the final 50 rows and paginates by a (criado_em, id) cursor, bringing it to 1.5 ms / 17 buffers. The JSONB diff only travels when the user expands a row. Against noise: windowed de-duplication of read events in the database, removing worthless records at the source, and grouping legitimate bursts (an import, a batch of sub-statuses) only at display time, never deleting trail.
When hardening disappears by itself
A function drop/create rewrites the whole body, and twice a migration silently stripped a protection: an anon revoke on one RPC and the aal2 requirement on another. The answer was process: a verification SQL listing every sensitive function with its expected properties, which must return ok = true on every row. During the October audit it found the second regression, and the same round closed a project-takeover path and a vector that let anyone lock out the emergency access of any e-mail by inflating its attempt counter.
Stopping the URL from growing
The contact list sent the filtered ids in the query string: about 15 KB with ~400 contacts and +37 bytes per new contact, on course for a blank screen from a protocol error with no warning at all. Filtering moved to an RPC (POST, filters in the body) and the URL became constant-size. An in-house lint rule now flags .in() with a dynamic list.
08Results
- In production since July 2026; 63 commits between 2026-07-27 and 2026-10-05, across seven rounds of changes driven by user-team feedback.
- 58 migrations, 26 tables with RLS, 80 policies, 2 edge functions, 2
pg_cronjobs. - 526 automated tests on the pure modules (516 passing, 10 expected failures documenting known bugs); 0 lint errors; CI on every push.
- An internal security audit in October 2026, with the stage-1 fixes (project/campaign ownership, restored
aal2, hardened edge functions) already shipped, andnpm auditbrought to zero (4 → 0). - Activity log measured at 1.5 ms per page over 100k rows.
- 58
- migrations
- 26
- tables with RLS
- 80
- RLS policies
- 69
- SQL functions
- 53
- triggers
- 526
- automated tests
- 2
- edge functions
- 63
- commits
- ~26k
- TypeScript lines
- 6,183
- SQL lines
- 1.5 ms
- activity log page
how we measured
ls supabase/migrations | wc -l (0001–0045, with b/c/d suffixes)
how we measured
28 create table − 2 drop table in the migrations; enable row level security on all
how we measured
replaying create/drop policy in order over the migrations
how we measured
distinct names in create [or replace] function in the migrations
how we measured
distinct names in create trigger in the migrations
how we measured
npx vitest run: 516 passed | 10 expected fail, 11 files
how we measured
supabase/functions/ (admin-usuarios, acesso-emergencia)
how we measured
git rev-list --count HEAD (Jul 27 to Oct 5, 2026)
how we measured
non-empty lines in tracked *.ts/*.tsx (12,909 + 15,088), including ~2k of generated types
how we measured
non-empty lines in tracked *.sql
how we measured
measured with 100k rows, recorded in migration 0017 and in CLAUDE.md §14
09Timeline
- Jul 2026
First version live on Cloudflare Pages, with CSV/XLSX import and the activity log.
- Jul 2026
Passwordless code login, recovery codes and mandatory 2FA for admins.
- Jul 2026
Model reshaped: the campaign becomes the unit of work, with multiple channels and per-channel tracking.
- Aug 2026
Configurable per-campaign contact priority scoring.
- Aug 2026
Contact filtering moved to a server-side RPC, removing the id list from the URL.
- Oct 2026
Public election data load, tags and a page showing what the load changed.
- Oct 2026
Internal audit: CI with 526 tests, SQL hardening verification and ownership/aal2 fixes.
10Stack
- React 19
- Vite 6
- TypeScript
- React Router 7
- TanStack Query 5
- Tailwind CSS 4
- Radix UI
- Recharts
- Supabase
- PostgREST
- Supabase Auth (OTP + TOTP)
- Edge Functions (Deno)
- PL/pgSQL
- PostgreSQL
- Row Level Security
- pg_cron
- pg_trgm
- unaccent
- Python (carga de dados públicos)
- Cloudflare Pages
- GitHub Actions
- Vitest
- ESLint
- MFA TOTP obrigatório para admin (aal2)
- Login sem senha por código
- Cloudflare Turnstile
- Códigos de recuperação com bcrypt
- Rate limiting em RPC e edge function
- Auditoria append-only
11Tech sheet
- type
- internal CRM · invite-only
- stack
- React 19 · Vite · TypeScript
- db
- Postgres · 26 tables · 80 RLS policies
- auth
- email OTP · TOTP for admins (aal2)
- deploy
- Cloudflare Pages · Supabase
- audit
- append-only · cursor pagination
- tests
- 526 vitest · CI on every push
- status
- in production
An AI-generated fitness-coaching app brought up to production standard: RLS without per-row evaluation, workouts that survive offline and a CI that tests the real database
A public reporting channel with a triage panel where authorization lives in the database: role-based RLS, contacts only through an audited function, instant session revocation and a bot that saves evidence before it expires.
The platform behind the campaign to end Brazil's 6x1 work schedule: nationwide sign-ups, a volunteer network and 7-layer anti-bot defense
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.