All case studies
~/cases/[classified]/vinculo
confidential · NDAInternal systemsPolitical operationsJul 2026 → todayIn production

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.

526
automated tests
80
RLS policies
53
triggers
1.5 ms
activity log page
  • supabase
  • postgres
  • rls
  • react
  • mfa
  • audit-log
  • edge
  • cloudflare
  • etl
architecture.en.svgenlarge ↓

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

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

Backend and data
  • Postgres on Supabase: 58 versioned migrations (0001–0045; b/c/d suffixes 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 realizado from the URL (or the "no link" flag) inside a normalization trigger.
  • RPCs where a view stops scaling: fn_contatos_filtrados/fn_contatos_csv take filters in the request body (POST) instead of a list of ids in the URL, and fn_projeto_contatos merges two sources (campaigns + manual links) deduplicated per person.
  • Two complementary trails: auditoria (a generic trigger on every write, storing only the diff on UPDATE) and eventos (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.
Frontend
  • 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.ts data 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.fails to document known bugs not yet fixed).
  • 20 routes, guarded by RotaAutenticada and RotaAdmin, with heavy pages lazy-loaded.
Infrastructure and deploy
  • 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), test and build (tsc -b is 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 aal2 requirement, a pinned search_path and no grant to anon. It caught a regression where a function drop/create had silently wiped its hardening.

06Security

8 attacks considered, 8 defenses in production.

attackdefense
Accounts and login

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.

Privilege escalation

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.

Injection and hostile content

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.

Cost and availability abuse

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).

Tampering and audit trail

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: false on 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 localStorage and sessionStorage; 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; anon can reach nothing except one RPC that records failed logins.
  • Targeted aal2 enforcement: the fn_e_admin_aal2() predicate guards the log RPCs, the eventos and auditoria policies and all account management. The admin-usuarios edge function reads the aal claim from the Bearer token and refuses without it.
  • No privilege escalation: authenticated can only UPDATE usuarios.nome; level and status go through the edge function, which re-checks admin + 2FA server-side, refuses to demote the last active admin and validates usuario_id as 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

  • eventos and auditoria are append-only: nobody, not even an admin, has an UPDATE/DELETE policy. Writes go through security definer RPCs 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; no dangerouslySetInnerHTML, no eval.
  • security definer functions with a pinned search_path, verified by script after each migration.
  • Generated CSVs escape = + - @ against formula injection.
  • The service_role key 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_cron jobs.
  • 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, and npm audit brought to zero (4 → 0).
  • Activity log measured at 1.5 ms per page over 100k rows.
58
migrations
how we measured

ls supabase/migrations | wc -l (0001–0045, with b/c/d suffixes)

26
tables with RLS
how we measured

28 create table − 2 drop table in the migrations; enable row level security on all

80
RLS policies
how we measured

replaying create/drop policy in order over the migrations

69
SQL functions
how we measured

distinct names in create [or replace] function in the migrations

53
triggers
how we measured

distinct names in create trigger in the migrations

526
automated tests
how we measured

npx vitest run: 516 passed | 10 expected fail, 11 files

2
edge functions
how we measured

supabase/functions/ (admin-usuarios, acesso-emergencia)

63
commits
how we measured

git rev-list --count HEAD (Jul 27 to Oct 5, 2026)

~26k
TypeScript lines
how we measured

non-empty lines in tracked *.ts/*.tsx (12,909 + 15,088), including ~2k of generated types

6,183
SQL lines
how we measured

non-empty lines in tracked *.sql

1.5 ms
activity log page
how we measured

measured with 100k rows, recorded in migration 0017 and in CLAUDE.md §14

09Timeline

  1. Jul 2026

    First version live on Cloudflare Pages, with CSV/XLSX import and the activity log.

  2. Jul 2026

    Passwordless code login, recovery codes and mandatory 2FA for admins.

  3. Jul 2026

    Model reshaped: the campaign becomes the unit of work, with multiple channels and per-channel tracking.

  4. Aug 2026

    Configurable per-campaign contact priority scoring.

  5. Aug 2026

    Contact filtering moved to a server-side RPC, removing the id list from the URL.

  6. Oct 2026

    Public election data load, tags and a page showing what the load changed.

  7. Oct 2026

    Internal audit: CI with 526 tests, SQL hardening verification and ownership/aal2 fixes.

10Stack

Frontend
  • React 19
  • Vite 6
  • TypeScript
  • React Router 7
  • TanStack Query 5
  • Tailwind CSS 4
  • Radix UI
  • Recharts
Backend
  • Supabase
  • PostgREST
  • Supabase Auth (OTP + TOTP)
  • Edge Functions (Deno)
  • PL/pgSQL
Data
  • PostgreSQL
  • Row Level Security
  • pg_cron
  • pg_trgm
  • unaccent
  • Python (carga de dados públicos)
Infra
  • Cloudflare Pages
  • GitHub Actions
  • Vitest
  • ESLint
Security
  • 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

node@vinculo: ~/cases/vinculo
node@vinculo ─────────────
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
$ ls supabase/migrations | wc -l
58
$ npm test
Test Files 11 passed (11)
Tests 516 passed | 10 expected fail (526)
Duration 652ms
$ npm run lint
✖ 98 problems (0 errors, 98 warnings)
$ psql -f supabase/testes/verificar_funcoes_sensiveis.sql
nome | ok | problema
fn_dependencias_usuario | t |
fn_log_atividade | t |
registrar_evento_publico | t |
... # 20 funções + policies de leitura de eventos/auditoria, todas ok = t
$ grep -ci 'enable row level security' supabase/migrations/*.sql | awk -F: '{s+=$2} END {print s}'
28 # 26 tabelas vivas + 2 removidas na reorganização
$ git log --oneline -1
c8b89d1 Auditoria etapa 1: posse de projeto/campanha, aal2 de volta, edge functions e cache

© 2026 NODE. All rights reserved.