All case studies
~/cases/[classified]/cadencia
confidential · NDAPlatforms & CRMsOther sectorsFeb 2026 → Oct 2026Completed

P-15

Cadência

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

client████Small fitness consultancy (personal trainers and nutritionists) with paying students

Engineer responsible for bringing the app up to production standard: hosting migration, security, CI, fix plan and performance audit

Fitness-coaching PWA with three areas (student, trainer and admin): workout and diet prescriptions, set and load logging, photo check-ins, body metrics, questionnaires, a social arena with a leaderboard, billing, bulk notices and report exports. The app was born on an AI app-generation platform and taken over as an engineering project in September 2026: migration to Cloudflare Workers, a five-front audit, fixes in nine phases and a performance audit on a synthetic dataset of 100 trainers and 10,000 students. Supabase with 41 tables under RLS, 44 SQL functions, 7 Deno edge functions, 4 pg_cron jobs and a six-job CI that builds the database from scratch, runs 36 RLS checks and fails if any query exceeds 500 ms at target scale.

41
tables under RLS
281
unit tests
7
edge functions
90
migrations
  • pwa
  • react
  • supabase
  • postgres
  • rls
  • edge
  • pg-cron
  • cloudflare
  • ci
  • csp
  • lgpd
architecture.en.svgenlarge ↓

01Overview

Cadência is the platform a fitness consultancy uses to serve its clients: the trainer builds workouts and diets, the client logs what they did from their phone, and the business tracks adherence, billing and questionnaires. It is a PWA (installable web app) with three areas, one per role: student, trainer and admin. Students see today's workout, log sets and loads, follow their diet, check in daily, record weight and photos and take part in a social arena with a monthly leaderboard. Trainers prescribe from a library of exercises, foods and templates and only ever see the students linked to them. Admins manage plans, subscriptions, payments, bulk notices, report exports and the team.

The app was born in February 2026 on an AI app-generation platform (Lovable), which produced the screens and the database in two months. In September 2026 it was taken over as an engineering project: moved to Cloudflare Workers, audited on five fronts (security, product flows, architecture, App Store readiness and bugs), fixed in nine phases and finally put through a performance audit against a synthetic dataset of 100 trainers and 10,000 students, the scale the business is aiming for once the app reaches the app stores.

This case is about that second stage: what it takes to inherit an AI-generated app with real people's data in production and make it ready to grow 700× without losing a workout, leaking a student's data or charging anyone twice.

02The problem

AI-generated apps have a deceptive kind of quality: the screens exist, the buttons respond and the database has the right tables. The September audit found what that hides:

  • Lost student work. A fifty-minute workout lived only in memory and vanished when the screen locked. Exercises marked as done were never saved, so trainers could not see adherence, which is the very thing the consultancy sells.
  • Multi-step writes with no transaction. Editing a workout template deleted its exercises and could fail to reinsert them. Assigning a diet ignored the error of one meal and reported success with an incomplete plan.
  • Authorization only in the UI. A trainer with no link to a student could prescribe them a diet with a direct POST. A student could reactivate their own plan and switch off the mandatory password change by editing their own profile.
  • Wrong numbers presented with confidence. None of the 28 screens handled query errors. The finance dashboard showed zero revenue because of a 400 nobody saw.
  • Scale. On the synthetic dataset, lists were silently cut at 1,000 rows, the admin's revenue card showed 14% of the real value, opening a workout took 9 seconds and the previous-loads query ran past 42 seconds and timed out.
  • Operations. One environment (production), manual deploys from the generator, migrations that did not run from scratch and no tests for access policies.

03The solution

The app kept its stack (React + Supabase); there was no rewrite. The work was engineering on top of what existed: a data boundary, transactions in the database, correct policies, a CI that exercises the database, and a before/after performance audit.

For students

  • Today's workout with set and load logging, previous loads per exercise and a draft that survives the screen locking.
  • Idempotent workout logging with an offline queue: with no network, the workout is stored on the device and sent on the next launch, even if the app was closed or updated in between.
  • Active diet with macros, meal check-ins with photos (downscaled on the device before upload: 5.7 MB to 454 kB).
  • Daily check-in, body metrics (weight, height, body fat, photos), scheduled questionnaires, in-app notifications and Web Push.
  • Arena: monthly feed of workout posts, likes, comments, points leaderboard, reporting and blocking.
  • Onboarding, mandatory password change on first login and self-service account deletion.

For trainers

  • Linked students with server-side search, full student record, assignment of workout and diet templates, and notices restricted to their own students.

For admins

  • Students, trainers and administrators, plans and subscriptions, paginated payments, finance dashboard aggregated in the database.
  • Bulk notices resolved on the server ("notify everyone" reaches 10,000 students in 312 ms) and automatic trigger-based notifications (payment due, inactive student, new workout, new diet, check-in reminder).
  • Ten CSV and PDF reports, with the volume computed before the click and a per-file ceiling (200,000 CSV rows, 5,000 PDF rows).
  • Per-platform configuration: minimum app version and feature switches without shipping a new binary.
  • AI-generated app (Lovable, Feb–Mar 2026) taken over as an engineering project and brought to production standard with no rewrite
  • RLS on 41 tables evaluated once per query: database time for one pass through the screens from 11,918 to 392 ms at 10,000-student scale
  • Idempotent, transactional workout logging with an offline queue that survives deploys: with no network, from 0 to 1 workout saved
  • Key-based idempotency on finance and copy RPCs with a per-student lock: double tap from 2 charges to 1
  • CI that builds Supabase from scratch, diffs generated types, runs 36 RLS checks and 47 integration tests against the real API
  • Weekly guardrail with 100 trainers and 10,007 students: a query over 500 ms or a response cut at 1,000 rows fails CI
  • No SQL function executable without a session, with checks that fail closed; fix applied in production with logs showing no exploitation
  • CSP with no unsafe-inline in script-src; the only inline script is allowed by a SHA-256 hash verified by a test
  • App Store readiness: account deletion, report and block, per-platform minimum version, feature switches and a test that forbids selling in the student area
  • Complete cursor-based exports: a month of check-ins from 1,000 nameless rows to 146,529 named rows in 2 s

04Architecture

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

Components

  • Clients: Student app (PWA)<br>workout · diet · check-in · arena, Trainer panel<br>linked students · prescription, Admin panel<br>billing · export · notices
  • Cloudflare Workers: React 18 + Vite SPA<br>37 lazy routes · hash-based CSP<br>service worker (precache + push), _headers<br>CSP · nosniff · frame-ancestors
  • Device: IndexedDB<br>query cache · offline queue
  • Supabase Edge Functions · Deno (7): create-student / create-staff<br>requireRole(admin), delete-account<br>confirmation + record, send-notification<br>Web Push RFC 8291 · ≤500 per batch, promote-to-admin<br>reset-student-password
  • Postgres (Supabase): 41 tables · RLS on all<br>44 SQL functions · 26 triggers, user_roles + has_role()<br>ids_dos_meus_alunos()<br>(select …) once per query, Transactional RPCs<br>log_workout · subscriptions<br>key-based idempotency, pg_cron (4 jobs)<br>questionnaires · notices · purges, Storage (4 buckets)<br>2 private · 1 h signed URL, Realtime<br>feed_posts only
  • GitHub Actions: 6 jobs: typecheck · lint · 281 tests<br>migrations from scratch · 36 RLS checks<br>47 integration tests · CSP/PWA in Chrome, Weekly volume<br>100 trainers · 10k students<br>&gt; 500 ms or 1,000 rows = fail

Connections

  • Student app (PWA)<br>workout · diet · check-in · arena → React 18 + Vite SPA<br>37 lazy routes · hash-based CSP<br>service worker (precache + push)
  • Trainer panel<br>linked students · prescription → React 18 + Vite SPA<br>37 lazy routes · hash-based CSP<br>service worker (precache + push)
  • Admin panel<br>billing · export · notices → React 18 + Vite SPA<br>37 lazy routes · hash-based CSP<br>service worker (precache + push)
  • _headers<br>CSP · nosniff · frame-ancestors → React 18 + Vite SPA<br>37 lazy routes · hash-based CSP<br>service worker (precache + push)
  • React 18 + Vite SPA<br>37 lazy routes · hash-based CSP<br>service worker (precache + push) → IndexedDB<br>query cache · offline queue
  • React 18 + Vite SPA<br>37 lazy routes · hash-based CSP<br>service worker (precache + push) → 41 tables · RLS on all<br>44 SQL functions · 26 triggers (anon key + JWT · RLS)
  • React 18 + Vite SPA<br>37 lazy routes · hash-based CSP<br>service worker (precache + push) → create-student / create-staff<br>requireRole(admin) (JWT)
  • React 18 + Vite SPA<br>37 lazy routes · hash-based CSP<br>service worker (precache + push) → delete-account<br>confirmation + record
  • React 18 + Vite SPA<br>37 lazy routes · hash-based CSP<br>service worker (precache + push) → send-notification<br>Web Push RFC 8291 · ≤500 per batch
  • React 18 + Vite SPA<br>37 lazy routes · hash-based CSP<br>service worker (precache + push) → promote-to-admin<br>reset-student-password
  • React 18 + Vite SPA<br>37 lazy routes · hash-based CSP<br>service worker (precache + push) → Realtime<br>feed_posts only (WebSocket)
  • React 18 + Vite SPA<br>37 lazy routes · hash-based CSP<br>service worker (precache + push) → Storage (4 buckets)<br>2 private · 1 h signed URL (signed URL)
  • create-student / create-staff<br>requireRole(admin) → 41 tables · RLS on all<br>44 SQL functions · 26 triggers (service_role)
  • send-notification<br>Web Push RFC 8291 · ≤500 per batch → 41 tables · RLS on all<br>44 SQL functions · 26 triggers
  • send-notification<br>Web Push RFC 8291 · ≤500 per batch → Browser<br>push services (VAPID)
  • 41 tables · RLS on all<br>44 SQL functions · 26 triggers → user_roles + has_role()<br>ids_dos_meus_alunos()<br>(select …) once per query
  • 41 tables · RLS on all<br>44 SQL functions · 26 triggers → Transactional RPCs<br>log_workout · subscriptions<br>key-based idempotency
  • pg_cron (4 jobs)<br>questionnaires · notices · purges → 41 tables · RLS on all<br>44 SQL functions · 26 triggers
  • 6 jobs: typecheck · lint · 281 tests<br>migrations from scratch · 36 RLS checks<br>47 integration tests · CSP/PWA in Chrome → CF (wrangler deploy)
  • Weekly volume<br>100 trainers · 10k students<br>&gt; 500 ms or 1,000 rows = fail → DB

There is no custom server. The front end is a static SPA served by a Cloudflare Worker that talks directly to Supabase: PostgREST with RLS for reads and writes, PL/pgSQL RPCs for anything that needs a transaction or an aggregation, seven Deno edge functions for what requires the service key (creating accounts, promoting admins, sending push, deleting accounts) and four pg_cron jobs.

The single most important architectural decision is the data layer split into 16 domains (src/api/*): every read and write goes through a domain hook, and an ESLint rule forbids pages and components from importing the Supabase client or TanStack Query directly. That boundary is what made it possible to swap queries for RPCs, paginate lists and change policies without touching screens, and it is what makes a native (Capacitor) app feasible without duplicating logic.

05Under the hood

Backend and data
  • Postgres 17 on Supabase: 41 tables, 90 migrations (45 of them from the September 2026 hardening phase), 44 SQL functions, 26 triggers and 79 indexes. Extensions: pg_cron, pg_net, pg_trgm.
  • Write rule: anything with more than one step becomes a transactional RPC. Saving a workout template, assigning a workout or diet, creating a subscription with its first payment, changing plans, cancelling a student and logging a workout all run in PL/pgSQL, in a single transaction.
  • Idempotency in the database. Finance and copy RPCs take p_idempotency_key uuid default null; the first call records the operation in an idempotencia table and the second returns the first result without writing. Subscription RPCs also take a per-student lock so two simultaneous plan changes cannot leave two active subscriptions. Workout logging uses the id generated when the workout started, so resending never duplicates. A weekly job purges keys older than 30 days.
  • Aggregate in the database, not the client. The admin dashboard, students per trainer, questionnaire status, previous loads, the cursor-paginated student list and this week's birthdays are all RPCs. PostgREST's 1,000-row limit stopped being a silent trap.
  • Compatibility with old app versions. With the app in the stores, old versions keep calling the new backend for weeks. The documented rule is expand, migrate, then contract: new parameters always have a default, a new return shape becomes a new function, and an old column is only dropped after the minimum version in app_config has been raised.
  • pg_cron jobs, each a SECURITY DEFINER SQL function with a lock against concurrent runs: scheduled questionnaires (daily), automatic notifications (daily), purge of old notifications and of idempotency keys (weekly).
  • Dates in the São Paulo timezone. The most widespread error class in the audit was reading a DATE column as UTC and losing a day; column defaults were moved to local time and the test suite deliberately runs with TZ=America/Sao_Paulo so CI cannot hide the case.
  • Storage: four buckets. Avatars and arena images are public; meal photos and questionnaire files are private, served through one-hour signed URLs with a policy that only admits the owner, an admin or the linked trainer.
  • Realtime only on the feed posts table, with denormalized counters: a like or comment produces an UPDATE on the post carrying the new numbers, and nothing else invalidates the whole feed.
Frontend
  • React 18 + Vite 5 + TypeScript 5 in strict mode, Tailwind 3, shadcn/ui on Radix, TanStack Query 5, react-hook-form with zod, Recharts.
  • One page per chunk: 37 lazily loaded routes; the shared skeleton is 185 kB gzipped and CI fails above 190 kB, or if any route exceeds 320 kB. Charts (Recharts) left the critical path of screens that do not use them: the student record dropped from 414 kB to 309 kB.
  • PWA with injectManifest: a hand-written service worker with precaching, prompt-based updates (not automatic, so a deploy with code splitting does not break open tabs), caches for thumbnails and Storage, and Web Push whose click only opens internal app paths.
  • On-device query cache (IndexedDB) owned by one user: the app opens instantly with what it had last time and refreshes in the background. Another account's cache is discarded; signing out wipes everything. Whole admin lists are deliberately excluded.
  • Offline mutation queue for workout logging. A new deploy discards the query cache but keeps the queue, because the RPC is idempotent.
  • Version gate: on launch, the app compares its own version with the platform minimum in app_config and shows "update the app" instead of breaking on API errors. The query runs after first paint so it does not compete for connections with the screen's data.
  • Errors never pass silently: QueryCache.onError, MutationCache.onError and an outer error boundary all go through a single observability entry point, with user-facing warnings deduplicated over 8 seconds.
  • A visual identity of its own (Sept 2026) with a token system documented in DESIGN.md: electric-blue ambient background, rounded surfaces, Inter Tight for display type, dark mode.
  • Accessibility: zoom is never blocked in the viewport (WCAG 1.4.4), manifest in pt-BR, accessibility tests in src/lib.
  • A test that guards a business decision: the student area must never show a price, "subscribe", "buy" or a payment gateway name. The test scans the student screens' source files and fails if someone adds "your plan costs R$ X", because that would force Apple's in-app purchase (15 to 30% of revenue).
Infrastructure and deploy
  • Cloudflare Workers with static assets (wrangler.jsonc), SPA fallback and Worker observability enabled. Security headers come from public/_headers. Hashed assets get Cache-Control: immutable for a year: a revisit without the service worker went from 6 server round trips to 0.
  • GitHub Actions with six jobs on every push and PR: typecheck, lint, 281 unit tests and a build with a bundle budget; a local Supabase built from scratch with all 90 migrations, a diff of the generated types against the schema, 36 RLS checks in plain SQL and 47 integration tests against real PostgREST and Auth; validation of all 196 select strings against the schema; PWA and CSP checks in headless Chrome served by the Worker rather than vite preview; deno check, deno lint and 18 edge-function tests. The production deploy only runs after all of them.
  • Weekly volume job: boots a local Supabase, seeds 100 trainers, 10,007 students and two years of history (3.7 GB, about 13 minutes) and fails if any main-route query exceeds 500 ms or comes back cut at 1,000 rows. On its first run it caught an RPC truncating the student list.
  • Pinned Supabase CLI version in CI: an update changed the generated types' format and would have broken the comparison with no database change at all.
  • Build variables (URL, publishable key, VAPID public key) come from repository variables. No service key lives in the code: function secrets are set in the Supabase dashboard.
  • Region: the database runs in us-west-2; a step-by-step migration to sa-east-1 (São Paulo) is planned, with the Pro plan already approved. Measured from the development machine, connection latency drops from 211 ms to 38 ms.

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.

  • Call a listing RPC with the public key alone, without a session

    execute revoked from public and anon on every function; SECURITY DEFINER checks use 'is not true' (fail closed); a CI test fails if any public function is executable by anon

Privilege escalation

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

  • A trainer with no link prescribes a diet or reads another trainer's student record

    RLS on every table with the link in staff_students read via ids_dos_meus_alunos(); WITH CHECK on writes; 36 CI checks, including 'trainer A cannot read trainer B's student workouts'

  • A student reactivates their own plan or disables the mandatory password change with a PATCH on their own profile

    A trigger on profiles rejects changes to status, plan end date and must_change_password by anyone who is not an admin or the service role

  • A regular user promotes themselves to admin or creates accounts through the edge function

    verify_jwt on all 7 functions, requireRole(admin) before the service key is used, roles in user_roles apart from the profile; the last admin cannot demote or delete themselves

Forged origin and requests

CSRF, fake webhooks, reused tokens, signed links and open redirects.

  • A notification with an external link used for phishing from inside the app

    The link may only be an internal path (/app/...), validated in the edge function and again on push click in the service worker

Injection and hostile content

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

  • An XSS reads the session token stored in the browser

    CSP with no unsafe-inline in script-src (the only inline script is hash-allowed and tested), object-src none, base-uri self, frame-ancestors none, nosniff; zero dangerouslySetInnerHTML and zero eval

Cost and availability abuse

Blowing up the bill, exhausting quotas, flooding logs and knocking functions down with load.

  • Slow queries hold the 10-connection pool and take the API down for everyone

    statement_timeout of 4 s for authenticated and 3 s for anon, RLS without per-row evaluation, database-paginated lists and a weekly guardrail that fails above 500 ms

Tampering and audit trail

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

  • A double tap or a retry after a timeout creates two charges or two workouts

    Idempotency keys on finance and copy RPCs with a per-student lock; workout logging uses the id generated at start and ignores repeats

Authentication. Supabase Auth with email and password. There is no public sign-up in the app: students and trainers are created by an edge function with a strong temporary password from crypto.getRandomValues, and a must_change_password flag forces a change on first login. That gate fails closed: if the profile read fails, the app assumes the change is required instead of unlocking navigation while the temporary password is still valid. The last session's roles are stored on the device only to decide which screen to open; the data is protected by RLS.

Authorization in three layers, all in the database.

  • Roles in a separate table (user_roles), never in the profile, read by a SECURITY DEFINER has_role() with a fixed search_path. The trainer-student link lives in staff_students and is read through ids_dos_meus_alunos().
  • RLS enabled on all 41 tables, with TO authenticated policies (except the logo read on the login screen). Trainers only read and write their linked students; students only their own data; the template library is shared for use and private for editing.
  • A trigger on profiles rejects any attempt by users to change administrative columns (status, plan end date, must_change_password), because a per-column GRANT UPDATE would not survive the table's evolution.
  • Policies are evaluated once per query, not per row: has_role goes inside (select …) (an InitPlan) and the link check becomes a semi-join. A structural check in CI fails if any policy regresses to the old pattern. This is security and performance at once: on the synthetic dataset, index reads on user_roles during one pass through the screens dropped from 4.5 million to 113.

SQL functions out of anonymous reach. Every new function revokes execute from public and anon in the same migration, and a CI test fails if any function in the public schema is executable without a session. Checks inside SECURITY DEFINER functions use if (...) is not true, because with a null auth.uid() the condition is null and if not null would let it through.

Edge functions. All seven require a JWT (verify_jwt = true), go through requireRole before touching the service key, validate every body field (email, UUID, length, forbidden characters in names) and return a generic 500 without leaking details. send-notification takes at most 500 recipients per call, resolves "everyone" in the database, and trainers can only reach their own students. A notification link may only be an internal app path, enforced on the server and on push click, so a notification cannot become phishing from inside the product. Web Push is implemented with pure WebCrypto (RFC 8030, 8291 and 8292), with no external dependency. CORS uses a closed origin list and Vary: Origin.

Account deletion (LGPD and App Store). Users delete their own account with an explicit confirmation; the request is recorded in a table outside the cascade before auth.users is deleted, which takes profile, workouts, diets, check-ins, metrics and posts with it through on delete cascade. The last remaining admin cannot delete or demote themselves.

User-generated content. Reporting of posts or comments (one per person per item) and user blocking, which hides the blocked user's content in the feed and comment RPCs.

Front end and delivery. CSP with no unsafe-inline in script-src: the only inline script (theme before first paint) is allowed by SHA-256 hash, and a test recomputes the hash from index.html and fails if it drifts. object-src 'none', base-uri 'self', frame-ancestors 'none', X-Frame-Options: DENY, nosniff, Referrer-Policy and a closed Permissions-Policy. Zero dangerouslySetInnerHTML and zero eval in the codebase.

Blast-radius limits. statement_timeout of 4 s for authenticated and 3 s for anon: PostgREST has 10 connections for everyone, and a stuck query holds one until it times out. A heavy reconciliation RPC is restricted to the service key. Meal photos and questionnaire files (health data) are private behind signed URLs.

Policy tests as part of CI. supabase/tests/rls.sql assumes each role's identity and tries what it must not be able to do: trainer A reading the profile, workouts, diets and loads of B's student; a student changing their own status or extending their own plan; a student registering as a professional; a function called without a session. It runs on plain psql inside a transaction rolled back at the end, and it blocks the merge.

Fix applied in production (Sept 2026). The audit itself found that an earlier migration had granted execute on every function to anon, and that the access check in a student-listing RPC failed open without a session. The fix closed 25 functions to the anonymous role and rewrote the check to fail closed; the unauthenticated call went from 200 with the list to 401. The API logs available for the period showed no use of the hole.

07Technical challenges

RLS that cost 4.5 million index reads per pass

Policies already wrapped auth.uid() in (select …), but not the role and link functions. Since those are SECURITY DEFINER, the planner does not inline them, and permissive policies combine with OR: the admin policy was tested on every row, for every role. With 127,000 payments, an admin count took 1,370 ms. The rewrite was mechanical and changed nothing about what each role sees: has_role inside (select …) and the link as column in (select ids_dos_meus_alunos()). Database time for one pass through the 16 routes went from 11,918 ms to 392 ms, and a rollback script was generated from pg_policies before the migration.

Workouts lost without a network

The product's central flow wrote two loose inserts with a fresh id on every attempt: resending after a timeout duplicated the workout, and if the second insert failed a workout with no exercises remained. The fix has three parts: the app generates the id when the workout starts and keeps it in the draft; the log_workout RPC writes the log and its items in one transaction and ignores the call if the id already exists; and the mutation goes into a queue persisted in IndexedDB, resumed on the next launch even if the component that fired it no longer exists. The trickiest case was deployment: TanStack's cache buster discarded the queue along with the queries, and a workout logged before a new build disappeared. restoreClient was rewritten to drop the queries and keep the queue.

Complete exports without knocking over the database

Reports loaded whole tables and stopped at 1,000 rows, 90% of them nameless because of an invalid embed. The first idea (embedding workouts(nome) per page) cost 1,708 ms versus 213 ms; offset pagination took 1.6 s at page 199,000. The result: a cursor on the date column plus id, a name map loaded once, and the volume computed before the click with what will be refused highlighted. One month of check-ins went from 1,000 nameless rows to 146,529 rows with names, in 2 seconds. And the authenticated statement_timeout could drop from 8 s to 4 s once the long queries were out of the way.

A cron job that stopped for three weeks

The recurring-questionnaire job computed the next date as date + 7 even when that date had already passed: stopped for three weeks, a weekly schedule became one notice per day until it caught up with today, and for "all students" that meant 10,000 notices per missed day. Now the next date is the first one after today, duplicate schedules are marked together, and "everyone" skips inactive students. Measured: cron stopped for three weeks, from 3 notices down to 1.

08Results

Everything measured locally, A/B on the same machine, with the synthetic dataset of 100 trainers and 10,007 students and the same method on both sides (docs/audit/07-relatorio-final.md).

What Before After
Database time summed over one pass through 16 routes 11,918 ms 392 ms
Index reads on user_roles in that pass 4,539,385 113
Previous loads for a workout (EXPLAIN) 42,682 ms, timeout 2.98 ms
Workout screen until idle 9,158 ms 753 ms
/admin/students: Lighthouse · TBT · reachable students 65 · 1,807 ms · 1,000 of 10,007 99 · 95 ms · 10,007
Monthly revenue on the admin card 14% of the real value equal to SQL
Login screen 579 kB 333 kB
"Notify everyone" HTTP 400 10,007 students in 312 ms
Unit · integration · Deno · RLS tests 193 · 0 · 0 · 20 281 · 47 · 18 · 36

Nothing regressed: data visibility per role was compared before and after (10,007 of 10,007 students with the same status), and package-lock.json and deno.lock stayed identical to the baseline.

Real production figures (users, revenue, usage) are not part of this case.

41
tables under RLS
how we measured

Tables in src/integrations/supabase/types.ts; every one appears in ALTER TABLE ... ENABLE ROW LEVEL SECURITY in the migrations

90
migrations
how we measured

ls supabase/migrations | wc -l (45 timestamped Sep 2026)

44
SQL functions
how we measured

distinct CREATE [OR REPLACE] FUNCTION in the migrations (39 exposed in types.ts)

≈99
RLS policies
how we measured

distinct CREATE POLICY by name and table in the migrations (205 statements in the history, 31 DROP); a live count requires pg_policies

26 · 79
triggers · indexes
how we measured

distinct CREATE TRIGGER and CREATE INDEX in the migrations

7
edge functions
how we measured

ls supabase/functions (folders), all with verify_jwt = true in config.toml

4
pg_cron jobs
how we measured

cron.schedule in the migrations minus later cron.unschedule

43 · 37
routes · lazy chunks
how we measured

<Route> and lazy() in src/App.tsx

16
data-layer domains
how we measured

ls src/api (boundary enforced by no-restricted-imports in eslint.config.js)

281
unit tests
how we measured

npx vitest run: 40 files, 281 tests passing (run on 2026-10-29)

47 · 18 · 36
integration · Deno · RLS tests
how we measured

it()/test() in src/test/integracao (13 files); deno test (18 passed); verificar() calls in supabase/tests/rls.sql

855
commits
how we measured

git rev-list --count HEAD (Feb–Oct 2026; 719 from the generator bot, 128 from Bernardo since Sep 2026)

≈50k
TS/TSX lines
how we measured

wc -l on tracked .ts/.tsx files (18,164 + 31,730, including 2,272 of generated types)

≈8k
SQL lines
how we measured

wc -l on tracked .sql files (7,007 in the migrations)

11,918 → 392 ms
DB time per pass through the screens (target scale)
how we measured

docs/audit/07-relatorio-final.md, pg_stat_statements, 16 routes, synthetic dataset of 10,007 students

09Timeline

  1. Feb 2026

    Kickoff on an AI app-generation platform: Supabase, admin, trainer and student areas, workouts, diets and billing

  2. Mar 2026

    Dynamic questionnaires with scheduling, meal check-ins with photos and student access delivery

  3. Sep 2026

    Hosting moved to Cloudflare Workers, CI with deploy, centralized edge-function auth and RLS hardening

  4. Sep 2026

    Five-front audit and F0–F9 fix plan: transactions, database authorization, dates, errors, App Store, RLS tests in CI

  5. Sep 2026

    New visual identity and student onboarding

  6. Sep 2026

    Performance audit on a 10,000-student synthetic dataset: RLS without per-row evaluation, pagination, idempotency, offline queue, version gate and weekly guardrails

  7. Sep 2026

    Security fix applied in production: no SQL function executable without a session

  8. Oct 2026

    Pro plan approved and migration plan for moving the database to São Paulo (connection from 211 to 38 ms)

10Stack

Frontend
  • React 18
  • Vite 5 (SWC)
  • TypeScript 5 strict
  • Tailwind CSS 3
  • shadcn/ui (Radix)
  • TanStack Query 5 + IndexedDB
  • react-router 6
  • react-hook-form + zod
  • Recharts
  • Workbox 7 (service worker próprio)
Backend
  • PostgREST
  • PL/pgSQL (RPCs transacionais e idempotentes)
  • Supabase Edge Functions (Deno 2)
  • Web Push em WebCrypto puro
Data
  • PostgreSQL 17 (Supabase)
  • Row Level Security
  • pg_cron
  • pg_net
  • pg_trgm
  • Supabase Storage
  • Supabase Realtime
Infra
  • Cloudflare Workers (static assets)
  • wrangler 4
  • GitHub Actions (6 jobs + volume semanal)
  • Supabase CLI fixa no CI
  • Node 22
Security
  • Supabase Auth
  • user_roles + has_role() SECURITY DEFINER
  • RLS sem avaliação por linha
  • trigger de colunas administrativas
  • revoke execute from anon
  • verify_jwt + requireRole
  • validação de entrada
  • CSP por hash
  • statement_timeout por papel
  • URLs assinadas
  • testes de RLS no CI

11Tech sheet

node@cadencia: ~/cases/cadencia
node@cadencia ─────────────
type
fitness coaching PWA · 3 roles
stack
React 18 · Vite · Deno edge · PL/pgSQL
db
Postgres 17 · 41 tables · RLS on all
auth
Supabase Auth · user_roles · fail-closed
offline
IndexedDB cache · idempotent queue
jobs
pg_cron ×4 · weekly 10k-student guardrail
deploy
Cloudflare Workers · 6-job CI
status
completed · store-ready
$ ls supabase/migrations | wc -l && ls -d supabase/functions/*/ | wc -l
90
7
$ npx vitest run 2>&1 | grep -E 'Test Files|Tests '
Test Files 40 passed (40)
Tests 281 passed (281)
$ psql "$DB_URL" -v ON_ERROR_STOP=1 -f supabase/tests/rls.sql | tail -4
ok | professor A não lê os treinos do aluno de B
ok | nenhuma política chama has_role por linha
ok | nenhuma função do public executável por anon
ok | authenticated com statement_timeout de 4 s
$ curl -s -o /dev/null -w '%{http_code}\n' "$API/rest/v1/rpc/staff_students_list" -H "apikey: $ANON" -d '{"p_staff_id":"..."}'
401
$ psql "$DB_URL" -c "select jobname, schedule from cron.job order by 1"
process-auto-notifications | 0 12 * * *
process-questionnaire-schedules | 0 11 * * *
purge-old-idempotency-keys | 30 7 * * 0
purge-old-notifications | 0 7 * * 0
$ node scripts/guardrails-volume.mjs
students_page 30 ms ok
admin_dashboard_metrics 47 ms ok
workout_last_loads 4 ms ok
18 de 18 dentro do limite (< 500 ms, < 1.000 linhas)

© 2026 NODE. All rights reserved.