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.
- cloudflare
- supabase
- postgres
- rls
- react
- mfa
- lgpd
- white-label
- security-audit
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:
- Sending is human. The platform prepares the link; a person sends it. There is no integration that fires messages on its own.
- 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.
- Append-only history. Interactions, notes and the audit log reject UPDATE and DELETE, enforced by database triggers.
- Opt-out locks the person across every campaign, immediately.
- 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
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
- 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 DEFINERfunctions 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.tsmodule 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-Timingwith 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.
- 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.
- 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>]inwrangler.tomlwith its own Worker, route, vars and rate-limit namespaces; the front end isvite build --mode <name>, and the build refuses to run without that instance's env file. The top level ofwrangler.tomlis dev-only: a deploy without--envcreates an unreachable Worker instead of overwriting someone's production. - CSP generated at build time: a Vite plugin writes each instance's
_headerswith its own Supabase URL inconnect-src. publicar.mjschecks the four places that must agree, runs types and tests, deploys, then asks the domain who answered:/api/saudereturns the instance, the commit and the latest applied migration.--todasdeploys all instances and refuses to finish if they serve different code or schema versions.migrar.mjsapplies 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--statusflagging anything changed by hand in the dashboard.
06Security
8 attacks considered, 8 defenses in production.
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
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
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
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
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
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 withadmin.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
aal1token 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-writableuser_metadata.
Authorization
- Single permission matrix (
permissoes.ts) for navigation and API; role checks are never a strayrole === 'admin'inside a handler. - The role is read from
public.usuarioson every request (deactivation takes effect immediately), not from the JWT; the token'ssubonly prefetches, it never authorizes. - RLS on every
publictable, with the role read through aSECURITY DEFINERfunction 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 privilegesrevokes every new table fromanonand every new function fromanon/authenticated; a function only becomes an RPC through agrant executein 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-DDdates, 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.devand 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). ZerodangerouslySetInnerHTML, zeroeval. - Attachments in a private bucket, served through 300-second signed URLs.
Proof
verificar-seguranca.mjsmeasures what the system refuses: a CM writing straight to PostgREST, the read-only role trying to undo actions,anoncalling 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
- 24 days
- timeframe
- 96
- SQL migrations
- 35
- public tables with RLS
- 127
- create policy statements
- ~140
- API endpoints
- 6
- access roles
- ~750
- test cases (Vitest)
- 30
- end-to-end verification tools
- 17 → 4
- DB round trips in the CM queue
- 2.3 s → 81 ms
- dashboard metrics function
- ~56,100
- TS/TSX lines
- ~8,400
- SQL lines
how we measured
git rev-list --count HEAD
how we measured
git log: first commit 2026-08-03, last 2026-08-26 (18 days with commits)
how we measured
ls supabase/migrations/*.sql | wc -l
how we measured
appendix B of docs/tecnico/plano-seguranca-2026-08-25.md (RLS on all)
how we measured
grep -ci 'create policy' in the migrations
how we measured
grep for route .get/.post/.put/.patch/.delete in apps/api/src (140); the Aug 25 audit enumerated 137
how we measured
packages/shared/src/perfis.ts and permissoes.ts
how we measured
grep for it(/test( in 34 tracked *.test.ts files
how we measured
ls ferramentas/verificar-*.mjs
how we measured
CLAUDE.md, decision US-CM-12 (2026-08-18)
how we measured
CLAUDE.md and docs/tecnico/auditoria-performance-2026-08-25.md
how we measured
non-empty lines in tracked .ts/.tsx files, without database.types.ts
how we measured
non-empty lines in tracked .sql files
09Timeline
- 03 Aug 2026
First commit, Cloudflare deployment and passwordless access-code login
- 04 Aug 2026
Turnstile, TOTP with recovery codes and the second factor pushed into RLS
- 07 Aug 2026
New model: a contact's participation in several campaigns at once
- Aug 2026
Aug 17–18: Main Flow: two-step logging, distribution on spreadsheet upload and a day without shifts
- 25 Aug 2026
Performance and security audits, seven remediation phases shipped
- 26 Aug 2026
Real white label: one codebase, three instances, migrations and deploys checked across them
10Stack
- React 19
- TypeScript
- Vite 8
- Tailwind CSS 4
- Radix UI (shadcn/ui)
- React Router 8
- Hono
- Cloudflare Workers
- Cron Triggers
- Supabase Auth
- PL/pgSQL SECURITY DEFINER
- Resend
- PostgreSQL (Supabase)
- Row Level Security
- PostgREST
- Supabase Storage
- Cloudflare Pages
- Workers Smart Placement
- Workers Rate Limiting
- Supabase Management API
- npm workspaces
- 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
- 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
The platform that brought content creators into the campaign to end online betting in Brazil: nationwide sign-ups, an inflation-proof public counter and a brute-force-hardened admin panel.
Volunteer mobilization under a legal deadline and a hostile audience: every rule lives in the database.
A public political sign-up, printed links that never break, and a database that answers to nothing but the Functions.
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.