All case studies
~/cases/[classified]/regua
confidential · NDAPlatforms & CRMsPolitical operationsAug 2026 → todayIn production

P-05

Régua

A WhatsApp relationship operation with human sending, immutable history and the database as the final word.

client████WhatsApp relationship operations run by community manager teams (white label, one isolated instance per client)

A white-label platform that organizes, distributes, runs and measures the work of community-manager teams on WhatsApp: staged contact cadences, a per-person daily queue, a log of every outcome, a manager-driven daily cycle and a dashboard that never confuses volume with people. Sending is always human; business rules live in tested pure functions and in Postgres, with RLS on every table, a second factor enforced in the database and trigger-enforced append-only history. One isolated instance per client, three in production, deployed from the same code.

~750
test cases (Vitest)
127
create policy statements
2.3 s → 81 ms
dashboard metrics function
24 days
timeframe
  • cloudflare
  • supabase
  • postgres
  • rls
  • react
  • mfa
  • lgpd
  • white-label
  • security-audit
architecture.en.svgenlarge ↓

01Overview

Régua is the working system for a team that talks to thousands of people over WhatsApp. Before it, the operation lived in spreadsheets, loose links and phones: nobody could say for sure who had already been messaged, who replied, who asked not to be contacted again, or how much each team member got done that day.

The platform turns that into a traceable process. Every person in the base has a permanent record, identified by phone number. Campaigns carry a cadence (régua), a staged script of first contact, re-engagements, invitation and mobilization, and each community manager (CM) gets a daily queue with the wa.me link already built and the right message for that stage. The CM sends from their own phone and logs the outcome; that log is what moves the cadence forward and feeds the management dashboard and reports.

02The problem

  • Untracked sends. Messages went out from several phones with no record of who sent what, from which number, at which stage.
  • Manual distribution. The manager split spreadsheets across the team every morning; duplicate contacts silently changed owners.
  • Fragile opt-out. "Don't contact me again" depended on someone remembering to remove the person from every list.
  • Misleading metrics. "Messages sent" and "people reached" were added up as if they were the same thing.
  • Personal data at scale. A third-party base of names and phone numbers, under Brazil's LGPD, handled by a team with very different access needs.

03The solution

The design rests on premises the code treats as non-negotiable:

  1. Sending is human. The platform prepares the link; a person sends it. There is no integration that fires messages on its own.
  2. The CM's log is the engine. Nothing advances by blind automation; time only surfaces stalled contacts, it never acts on the CM's behalf.
  3. Append-only history. Interactions, notes and the audit log reject UPDATE and DELETE, enforced by database triggers.
  4. Opt-out locks the person across every campaign, immediately.
  5. Three distinct counts: interaction volume, number of replies, and unique people engaged.

Features:

  • CM workspace: the daily queue as a table, with a two-step log (Reply yes/no, then the move along the cadence) and a gate read from the database: the contact only advances once both send and reply are recorded. The CM picks the destination stage and when the contact comes back (now, or on the cadence interval).
  • Cadences and scripts: the Strategist builds the script stage by stage, with message variations (up to 12 per stage) and stages flagged as valid journey endings.
  • Participations: the same person can be in several campaigns at once, each running its own cadence; the CM belongs to the person, not the campaign.
  • Daily cycle: the Manager opens and closes the day (or it opens itself when the first CM starts), follows each CM live (sent, replies, portfolio, % of target), and must decide what happens to pending contacts before closing: keep, transfer, or flag for tomorrow.
  • Import and the "general base": spreadsheet import with phone normalization and per-campaign duplicate rules; the general base acts as a pool the Manager hands out in equal batches to the chosen CMs.
  • WhatsApp numbers: each CM registers up to 4 numbers; every send records which number it came from, and the number used on first contact follows the person through later stages. The Accounts Center stores infrastructure credentials encrypted and governs banned numbers.
  • Dashboard and reports: metrics by any slice, daily summary by email, missed-target report, audited exports.
  • LGPD: right to erasure with log redaction and a suppression list, plus a separate administrative deletion with a mandatory reason and an audit entry written before anything is deleted.

Six roles (Admin, Manager, Community Manager, Strategist, Accounts Operator, Leadership), one role per user, with the permission matrix in a single file consumed by both front-end navigation and API authorization.

  • The second factor is enforced twice: in API middleware and by a restrictive RLS policy on every table.
  • Interactions, notes and the audit log reject UPDATE and DELETE via triggers, with only two named valves (LGPD erasure and administrative deletion).
  • Operational writes are out of the client's reach: RLS decides rows, not columns, so only the API writes, after checking scope.
  • Credentials in AES-256-GCM and an HMAC-based LGPD suppression list, with the key in Cloudflare and the data in Postgres: separate custody.
  • One codebase, N deployments: each client gets its own database, Worker and domain, and a tool refuses to finish if instances serve different versions.
  • A seven-phase security audit, proven by a verifier that measures what the system refuses.

04Architecture

real architecture · hover over the nodes
Read the diagram as a list (18 components, 19 connections)

Components

  • Six roles · one user = one role: Community Manager<br>Workspace, Manager / Admin<br>daily cycle · distribution, Strategist<br>cadences and scripts, Leadership / Operator<br>dashboard · Accounts hub
  • Cloudflare · one instance per client: Pages<br>React 19 + Vite SPA<br>CSP generated per instance, Turnstile, Rate Limiting<br>per IP and per user, Worker · Hono<br>~140 endpoints<br>permission matrix · 2FA · validation, Hourly Cron Trigger<br>daily report · goal
  • Postgres: RLS on every table<br>restrictive 2FA policy, SECURITY DEFINER functions<br>audited deletions · operating day, Append-only triggers<br>immutable history and log
  • Supabase · own project per instance: Auth (GoTrue)<br>TOTP · Send Email hook, DB, Private storage<br>attachments · 300 s signed URL

Connections

  • Community Manager<br>Workspace → Pages<br>React 19 + Vite SPA<br>CSP generated per instance
  • Manager / Admin<br>daily cycle · distribution → Pages<br>React 19 + Vite SPA<br>CSP generated per instance
  • Strategist<br>cadences and scripts → Pages<br>React 19 + Vite SPA<br>CSP generated per instance
  • Leadership / Operator<br>dashboard · Accounts hub → Pages<br>React 19 + Vite SPA<br>CSP generated per instance
  • Community Manager<br>Workspace → CM's WhatsApp<br>wa.me link · human sending (prepares the link)
  • Pages<br>React 19 + Vite SPA<br>CSP generated per instance → Turnstile
  • Pages<br>React 19 + Vite SPA<br>CSP generated per instance → Rate Limiting<br>per IP and per user (/api/* same-origin)
  • Rate Limiting<br>per IP and per user → Worker · Hono<br>~140 endpoints<br>permission matrix · 2FA · validation
  • Pages<br>React 19 + Vite SPA<br>CSP generated per instance → Auth (GoTrue)<br>TOTP · Send Email hook (session)
  • Worker · Hono<br>~140 endpoints<br>permission matrix · 2FA · validation → RLS on every table<br>restrictive 2FA policy (user JWT)
  • Worker · Hono<br>~140 endpoints<br>permission matrix · 2FA · validation → SECURITY DEFINER functions<br>audited deletions · operating day (service role after scope check)
  • Worker · Hono<br>~140 endpoints<br>permission matrix · 2FA · validation → Private storage<br>attachments · 300 s signed URL
  • Worker · Hono<br>~140 endpoints<br>permission matrix · 2FA · validation → Resend<br>access code · daily digest
  • Worker · Hono<br>~140 endpoints<br>permission matrix · 2FA · validation → Secrets in Cloudflare<br>AES-256-GCM · HMAC
  • Worker · Hono<br>~140 endpoints<br>permission matrix · 2FA · validation → packages/shared<br>cadence engine · metrics · permissions<br>pure rules + Vitest (imports rules)
  • Pages<br>React 19 + Vite SPA<br>CSP generated per instance → packages/shared<br>cadence engine · metrics · permissions<br>pure rules + Vitest (imports rules)
  • Hourly Cron Trigger<br>daily report · goal → Worker · Hono<br>~140 endpoints<br>permission matrix · 2FA · validation
  • Auth (GoTrue)<br>TOTP · Send Email hook → Worker · Hono<br>~140 endpoints<br>permission matrix · 2FA · validation (signed hook)
  • SECURITY DEFINER functions<br>audited deletions · operating day → Append-only triggers<br>immutable history and log

(The rendered architecture.svg uses the Portuguese labels from the codebase.)

npm-workspaces monorepo:

apps/web         React + Vite SPA (Cloudflare Pages)
apps/api         Hono on Cloudflare Workers (API + Cron Trigger)
packages/shared  types, permission matrix and pure business rules, with tests
supabase/        versioned migrations (mirror of what was applied)
ferramentas/     per-instance publishing, cross-instance migrations, end-to-end verifiers

Sensitive rules (cadence advancement, re-engagement dates, the daily queue, portfolio counts, metrics, distribution, permissions) are pure functions in packages/shared, tested without a database and imported by both API and front end. The UI is never the last word: the API decides, and Postgres RLS checks again.

05Under the hood

Backend and data
  • API: Hono on Cloudflare Workers, ~140 endpoints across 24 route modules, front end and API on the same origin (/api/*).
  • Database: Postgres on Supabase, 96 migrations, ~35 tables in public, all with RLS. Table and column names are in Portuguese, in the domain's own vocabulary.
  • Core model: the contact is the person (normalized phone, unique in the database); the participation is that contact inside a campaign, holding cadence, stage, status and dates; the interaction is the append-only log of every touch.
  • SECURITY DEFINER functions handle acts that need transactional guarantees and auditing: LGPD erasure, administrative deletion of contacts/cadences/campaigns (log written before the delete), idempotent opening of the operating day.
  • Dynamic-SQL metrics: the dashboard function is PL/pgSQL that only adds the predicates for filters actually present: same logic, from 2.3 s to 81 ms once it stopped misleading the planner with (p_x is null or col = p_x). "What counts as a reply" exists as SQL functions mirroring the TypeScript ones, and a verifier compares both over the same data.
  • No cron for what can be derived: rollover and "stale contacts" are computed from current state; a failing job could otherwise make contacts vanish from the queue. The single hourly Cron Trigger only sends the daily report at the configured time and the missed-target report.
  • Pagination by default: a paginacao.ts module works around PostgREST's 1,000-row cap with declared ordering and an explicit ceiling that reports when it is hit; in(...) filters are chunked by 200 so URLs don't blow up.
  • Observability: every API response carries Server-Timing with the time and the number of database round trips for that request: the metric that stays comparable across versions regardless of where it's measured from.
Frontend
  • React 19 + TypeScript + Vite 8, Tailwind CSS 4, shadcn/ui-style components on Radix.
  • 26 screens, lazy-loaded per route with vendors split into their own chunks; a CM's initial bundle dropped from 253 KB to ~157 KB gzipped.
  • Only Supabase's auth client ships to the browser. The front end never talks to PostgREST; all data goes through the API.
  • The workspace refreshes a single row after each action, through a route built by the same code as the queue, instead of reloading the whole day.
  • Entire UI in Brazilian Portuguese, per-instance branding (white label), color contrast checked by tests.
Infrastructure and deploy
  • Cloudflare Workers (API, Smart Placement so it runs close to the database) and Cloudflare Pages (SPA), with Supabase Postgres in São Paulo.
  • One codebase, N deployments. Each instance is an [env.<name>] in wrangler.toml with its own Worker, route, vars and rate-limit namespaces; the front end is vite build --mode <name>, and the build refuses to run without that instance's env file. The top level of wrangler.toml is dev-only: a deploy without --env creates an unreachable Worker instead of overwriting someone's production.
  • CSP generated at build time: a Vite plugin writes each instance's _headers with its own Supabase URL in connect-src.
  • publicar.mjs checks the four places that must agree, runs types and tests, deploys, then asks the domain who answered: /api/saude returns the instance, the commit and the latest applied migration. --todas deploys all instances and refuses to finish if they serve different code or schema versions.
  • migrar.mjs applies the same migration to every instance via the Management API, with identical version and name, stopping at the first failure; client-specific data migrations are flagged and refused elsewhere.
  • Auth configuration as code (configurar-auth.mjs): hooks, TOTP and session settings applied to every instance, with --status flagging anything changed by hand in the dashboard.

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.

  • Use one's own JWT and the public key to write straight to PostgREST (clear opt-out, skip stages, backdate sends)

    Direct-write policies removed: only the API writes, after a scope check; triggers stamp author and clock and block undoing an opt-out

Accounts and login

Brute force, rogue account creation, password reset and account takeover.

  • Steal a manager's session without the second factor and read the base

    Mandatory TOTP on the highest-reach roles, enforced in middleware and by a restrictive RLS policy on every table

  • Brute-force the access code or abuse code requests

    8-digit code, 10-minute TTL, single use, 3 attempts, progressive lockout per email and IP, Turnstile and a 30/min per-IP edge limit

  • Bypass login through Supabase Auth's native endpoints (magic link, password recovery)

    A signed Send Email hook refuses both; public sign-up disabled; users created without passwords

Privilege escalation

Someone with some access trying to get more: becoming admin, reading what they shouldn't, keeping revoked access.

  • A read-only role triggering writes, or a CM seeing another CM's portfolio

    A single permission matrix in the API, role re-read from the database on every request, and role-scoped RLS

Injection and hostile content

XSS, iframes, CSV formulas, disguised uploads and AI prompt manipulation.

  • Inject a formula into a CSV export or PostgREST syntax into a search

    Cells neutralized on export and syntax characters stripped from every value entering an .or() filter

Data leakage and tracking

Enumerating who is in the base, scraping personal data, tying questions to people, extracting secrets.

  • Get a database dump and extract account credentials or the opt-out list

    AES-256-GCM and HMAC with the key in Cloudflare, outside the database; encrypted columns have no read grant

Tampering and audit trail

Fiddling with counts, wiping the base, editing history or denying what was authorized.

  • Delete or rewrite history to hide an action

    Append-only triggers refuse UPDATE and DELETE for every role; the two exceptions write the log before acting

The project went through a full audit (OWASP API Top 10 and Web Top 10), cross-checking the code against the live database state and served headers; findings became seven remediation phases, all shipped and proven by a dedicated verifier.

Authentication

  • Passwordless. Login with an 8-digit emailed code, valid for 10 minutes, single-use, at most 3 attempts per code, stored as an HMAC.
  • Progressive lockout per email and per IP (an escalating wait ladder) plus Cloudflare Turnstile on code requests.
  • The session still belongs to Supabase Auth (auth.uid() underpins RLS): the Worker mints the token with admin.generateLink, exchanges it for a session server-side, and hands the browser only the tokens.
  • Mandatory TOTP for Admin, Manager, Accounts Operator and Leadership (optional for the rest), with single-use recovery codes and admin unlock. The second factor is enforced in middleware and by a restrictive RLS policy on every table: an aal1 token reads nothing that requires it.
  • Supabase Auth side doors closed: a signed Send Email hook (Standard Webhooks) refuses the magic-link and password-recovery endpoints anyone could hit with the public key; public sign-up disabled; users created without passwords.
  • A new user's role comes from server-controlled app_metadata, never from user-writable user_metadata.

Authorization

  • Single permission matrix (permissoes.ts) for navigation and API; role checks are never a stray role === 'admin' inside a handler.
  • The role is read from public.usuarios on every request (deactivation takes effect immediately), not from the JWT; the token's sub only prefetches, it never authorizes.
  • RLS on every public table, with the role read through a SECURITY DEFINER function wrapped in (select …) so it becomes an InitPlan. A CM sees only their own contacts; the Strategist never sees individual contacts; the Accounts Operator never sees relationship content.
  • RLS decides rows, not columns: so operational writes were taken out of the client's reach: direct-write policies were removed and only the API writes, with the admin client, after checking scope. Where the API writes with the user's JWT, a trigger stamps author and clock (nobody can log a backdated send in someone else's name).
  • Explicit privileges: alter default privileges revokes every new table from anon and every new function from anon/authenticated; a function only becomes an RPC through a grant execute in its own migration. Account credentials use column-level grants: encrypted columns are not selectable.

Data and LGPD

  • Infrastructure credentials in AES-256-GCM with the key in Cloudflare, outside the database (separate custody: a DB dump reveals no credential), versioned format for key rotation.
  • HMAC suppression list of phone numbers with a derived key: someone who exercised erasure after opting out never returns via a future import, and the list can't be brute-forced.
  • Append-only via triggers, with exactly two named valves (right to erasure and administrative deletion), each logging the act before acting. Opt-out can't be undone, not even by service_role.
  • Exports are audited, paginated, flag truncation, and neutralize CSV formula injection.

Hardening

  • Edge rate limiting (Workers binding): 30 req/min per IP on entry routes and 10 req/min per user on exports, import and 2FA recovery, in front of the database-backed limiter.
  • Centralized input validation: UUID path params (400 before touching the database), YYYY-MM-DD dates, an 8 KB cap per text field and 10 MB per body, stripping PostgREST syntax characters from .or() filters.
  • The access log doesn't record query strings (searches carry names and phone numbers).
  • workers.dev and preview URLs disabled: the API only answers on the instance's domain. CORS by exact allowlist.
  • Front end served with HSTS, X-Frame-Options: DENY, Permissions-Policy, COOP and a CSP (in report-only before promotion). Zero dangerouslySetInnerHTML, zero eval.
  • Attachments in a private bucket, served through 300-second signed URLs.

Proof

  • verificar-seguranca.mjs measures what the system refuses: a CM writing straight to PostgREST, the read-only role trying to undo actions, anon calling functions, unsigned hooks, Auth side endpoints, CSV cells starting with =, front-end headers. It creates temporary users and data and removes everything through the administrative path. Verifiers refuse to target production without an explicit flag.

07Technical challenges

The contact that silently disappeared

The daily queue said "comes back when the next-touch date arrives", but that date only exists once the CM logs the outcome. Anyone who was messaged but never had the outcome logged dropped out of the queue for good the next day, with no error. The fix generalized the rule: the case becomes "sent, awaiting reply", with the send box already ticked so the CM doesn't message again. A deeper version of the same bug followed: the queue measured stage progress by "today", so midnight wiped the marks of cycles still open. The window became the stage cycle, derived from data that already existed, with no migration.

The 1,000-row ceiling nobody saw

PostgREST returns at most a thousand rows with a 206 status and no error. That single cause explained a distribution that delivered less than requested, a monitoring view showing a third of the operation as if it were all of it, and a queue blind to part of each CM's portfolio, made worse by an embed filter without !inner that looked like a filter but fetched the whole table. The fix was a pagination pattern with declared ordering and an explicit ceiling, applied to every high-volume route, plus batched transactional writes.

One rule, many implementations

Three screens showed three different numbers for "the CM's portfolio", and the dashboard reported zero replies for four days because the SQL function still counted replies by a field a product change had made optional. The pattern adopted: the rule lives in one pure TypeScript function and, when it must also exist in SQL, in a mirrored function, with a verifier comparing both on the same data.

White label without forks

Bringing the system to new clients without copying the repository: named Wrangler environments, mode-based front-end builds guarded by file presence, per-instance CSP, migrations applied to all instances at once, and a deploy tool that checks, after deploying, that the domain serves the right bundle, pointing at the right database, on the right schema version.

08Results

  • In production since the first day of development, used by real CM teams; three isolated instances on the same code, with code and schema versions checked against each other.
  • 126 commits over 24 days (Aug 3–26, 2026).
  • Security audit with ~35 classified findings (5 high, 10 medium, ~20 low), none critical or exploitable anonymously; all seven remediation phases shipped and verified.
  • Performance audit: the CM queue route went from ~17 sequential database round trips to 4; the dashboard metrics function from 2.3 s to 81 ms; the initial front-end bundle from 253 KB to ~157 KB gzipped.
  • 34 test files with ~750 cases on the pure rules (cadence, re-engagement, queue, metrics, permissions, import, distribution), plus 30 end-to-end verifiers against the real API and database.
126
commits
how we measured

git rev-list --count HEAD

24 days
timeframe
how we measured

git log: first commit 2026-08-03, last 2026-08-26 (18 days with commits)

96
SQL migrations
how we measured

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

35
public tables with RLS
how we measured

appendix B of docs/tecnico/plano-seguranca-2026-08-25.md (RLS on all)

127
create policy statements
how we measured

grep -ci 'create policy' in the migrations

~140
API endpoints
how we measured

grep for route .get/.post/.put/.patch/.delete in apps/api/src (140); the Aug 25 audit enumerated 137

6
access roles
how we measured

packages/shared/src/perfis.ts and permissoes.ts

~750
test cases (Vitest)
how we measured

grep for it(/test( in 34 tracked *.test.ts files

30
end-to-end verification tools
how we measured

ls ferramentas/verificar-*.mjs

17 → 4
DB round trips in the CM queue
how we measured

CLAUDE.md, decision US-CM-12 (2026-08-18)

2.3 s → 81 ms
dashboard metrics function
how we measured

CLAUDE.md and docs/tecnico/auditoria-performance-2026-08-25.md

~56,100
TS/TSX lines
how we measured

non-empty lines in tracked .ts/.tsx files, without database.types.ts

~8,400
SQL lines
how we measured

non-empty lines in tracked .sql files

09Timeline

  1. 03 Aug 2026

    First commit, Cloudflare deployment and passwordless access-code login

  2. 04 Aug 2026

    Turnstile, TOTP with recovery codes and the second factor pushed into RLS

  3. 07 Aug 2026

    New model: a contact's participation in several campaigns at once

  4. Aug 2026

    Aug 17–18: Main Flow: two-step logging, distribution on spreadsheet upload and a day without shifts

  5. 25 Aug 2026

    Performance and security audits, seven remediation phases shipped

  6. 26 Aug 2026

    Real white label: one codebase, three instances, migrations and deploys checked across them

10Stack

Frontend
  • React 19
  • TypeScript
  • Vite 8
  • Tailwind CSS 4
  • Radix UI (shadcn/ui)
  • React Router 8
Backend
  • Hono
  • Cloudflare Workers
  • Cron Triggers
  • Supabase Auth
  • PL/pgSQL SECURITY DEFINER
  • Resend
Data
  • PostgreSQL (Supabase)
  • Row Level Security
  • PostgREST
  • Supabase Storage
Infra
  • Cloudflare Pages
  • Workers Smart Placement
  • Workers Rate Limiting
  • Supabase Management API
  • npm workspaces
Security
  • Login sem senha (código HMAC)
  • TOTP com política RLS restritiva
  • Cloudflare Turnstile
  • AES-256-GCM com chave fora do banco
  • Auth Hooks assinados
  • Triggers append-only
  • CSP e HSTS

11Tech sheet

node@regua: ~/cases/regua
node@regua ─────────────
type
whatsapp ops platform
stack
React 19 · Hono · Workers
db
Postgres · RLS on all tables
auth
passwordless code · TOTP
roles
6 profiles · single matrix
deploy
1 codebase · 1 instance per client
tests
Vitest · 30 e2e verifiers
status
in production
$ npm test
✓ packages/shared motor-regua · fila-do-dia · metricas · permissoes
Test Files 34 passed
$ curl -s https://<instancia>/api/saude
{"ok":true,"servico":"api","instancia":"<nome>","versao":"<commit>","schema":{"versao":"0096","nome":"versao_do_schema"}}
$ curl -s -o /dev/null -w '%{http_code}' https://<instancia>/api/contatos/nao-e-uuid
400 Identificador inválido. # recusado antes de tocar o banco
$ node ferramentas/verificar-seguranca.mjs
✓ CM escrevendo direto no PostgREST: recusado
✓ /auth/v1/otp e /recover: recusados pelo gancho
✓ anon em metricas_painel: sem permissão
34 conferências

© 2026 NODE. All rights reserved.