Scalability audit.

System: Example App (Hono API and Next.js frontend on Cloud Run, europe-west1; Supabase Postgres, eu-central-1) Date: 2026-11-04 Scope: whole repository except audit archive/; read-only measurements against production (table sizes, three curl timings, Supabase performance advisor). No load test was run. Staging was not measured. Data handled: Google OAuth tokens, Google Ads access, user emails, Stripe payments (from the code). Method: scanner (scripts/scan.sh) + owner's numbers + measurements + checklist + rubric. Not worked through item by item: the caching and queue sections of references/checklist.md.

Example report from a real audit of an AI-built app. Names, identifiers and customer details have been changed.

Summary

Production is tiny today (largest table: 91 rows), so nothing is slow now, and you report no traffic peaks. The one real scale problem already happened once: Supabase silently returns at most 1,000 rows per read, and 108 reads in the code have no limit or pagination; a 1,347-product feed was already truncated in June. Findings: 1 Critical, 1 High, 2 Medium, 4 Low. Growth in feed size, not in user count, is what breaks first.

Baseline

Top 3 actions

1. This week: paginate or count correctly every Supabase read that can exceed 1,000 rows (C1), starting with feed_products and page_enrichments.
2. This month: move rate limiting out of process memory (H1).
3. This quarter: bound the dashboard queries (M1) and decide whether prod should keep one warm instance (M2).

Findings

[C1] Reads silently stop at 1,000 rows — Critical

• Where: 108 Supabase reads without .range()/.limit(); confirmed examples: api/src/lib/filter-products.ts:156 (feed_products for an account), api/src/lib/hydrate-wf4-chunk.ts:107 (page_enrichments for a run). Full list in SCALABILITY-AUDIT-SCAN.txt (SUPABASE_NO_RANGE).
• Basis: Measured (the failure already happened) and Inferred for the other reads (assumption: feeds above 1,000 products or pages)
• Evidence: A commit in June (2026-06-15): the optimizer finalizer read hit PostgREST's 1,000-row cap, the card showed "1,000 products" and the CSV and Sheets export silently dropped 347 products from a 1,347-product feed. filter-products.ts loads every feed_products.item_url for an account in one unpaginated query and builds a lookup set; a feed over 1,000 products leaves the rest out of the set without any error. hydrate-wf4-chunk.ts loads all commercial enrichments for a run the same way.
• Severity reasoning: Impact 1 (silent data loss, no error), Horizon A (already happened), main-feature path (the adjustment would raise it, already at the top).
• Impact at scale: At about 1,001 products or pages per account or run, the result is incomplete with no error: products wrongly filtered out, keywords or ads generated for part of the feed, exports shorter than the data. Each new customer with a bigger feed hits it.
• Fix: One shared helper fetchAll(query, pageSize=1000) that pages with .range() ordered by a stable key; use it for every read that can exceed 1,000 rows. For counts use { count: 'exact', head: true }.
• Fix prompt:

  Add api/src/lib/fetch-all.ts exporting fetchAll(buildQuery, {pageSize=1000, orderBy='id'}) that loops with .range(from, to) until a page returns fewer rows than pageSize and throws on any error. Use it in api/src/lib/filter-products.ts (feed_products by account_id) and api/src/lib/hydrate-wf4-chunk.ts (page_enrichments by run_id) first, with a unit test that mocks 2,500 rows. Then list the other SUPABASE_NO_RANGE hits in SCALABILITY-AUDIT-SCAN.txt that read per-account or per-run tables, and give me a list ordered by risk. Do not change the rest yet.
  

• Verify: A test with 2,500 mocked rows returns 2,500; against staging, run a >1,000-product feed and compare the exported row count with feed_products count.

[H1] Rate limiting lives in each instance's memory — High

• Where: frontend/src/lib/auth/rate-limit.ts:2-18
• Basis: Inferred (assumption: more than one frontend instance running, up to the live maximum of 10)
• Evidence: A module-level Map holds counters; the file comment itself says to swap it for Redis on multiple Cloud Run instances. Production allows up to 10 instances (Verified live), and instances restart and scale to zero, which resets counts. Limits are 100 per hour per user on routes that start runs (POST:/api/runs, /api/cycles).
• Severity reasoning: Impact 2 (the limit stops working as intended; bounded by 10 instances, so up to ~1,000 calls per hour per user rather than 100), Horizon B; raised one level because one abusive client can trigger it on a costly route (runs call Gemini and scrape).
• Impact at scale: With several instances the effective limit multiplies by the instance count and resets on every restart, so the protection against runaway cost per user is weaker than it looks.
• Fix: Keep counters in the database (a table with an atomic upsert) or Upstash Redis; the module keeps the same interface.
• Fix prompt:

  Replace the in-memory Map in frontend/src/lib/auth/rate-limit.ts with a Postgres-backed counter: a migration (do not apply) creating rate_limits(key text, window_start timestamptz, count int, primary key(key, window_start)) and an RPC that upserts and returns the new count atomically. Keep the exported function signatures the same so no route changes. Add tests.
  

• Verify: With two local instances, 101 requests split across them are rejected after 100.

[M1] Dashboard queries read whole tables per request — Medium

• Where: frontend/src/app/dashboard/observability/[cycle_id]/page.tsx:143-178 (9 queries), frontend/src/lib/auth/get-user-accounts.ts:15,30, frontend/src/app/dashboard/labels/page.tsx:44, frontend/src/app/api/accounts/[accountId]/prompt-overrides/route.ts:35
• Basis: Inferred (depends on table size; today every table is under 100 rows)
• Evidence: Scanner UNBOUNDED_QUERY (12 hits), read for the observability page: nine sequential queries by cycle_id with no limit.
• Severity reasoning: Impact 2 (slower pages, then truncation at 1,000 rows as in C1), Horizon B. No adjustment.
• Impact at scale: Observability for a cycle with thousands of rows becomes slow and then silently incomplete.
• Fix: Add limits or pagination, and run the nine queries in parallel with Promise.all.
• Fix prompt:

  In frontend/src/app/dashboard/observability/[cycle_id]/page.tsx run the nine independent Supabase queries with Promise.all and add .limit(500) plus a visible "showing first 500" note where the result is capped. Keep the page output unchanged for small cycles.
  

• Verify: Page timing on staging with a seeded cycle of 5,000 rows is under 2 s and shows the cap note.

[M2] Production scales to zero, so the first visitor waits ~5 s — Medium

• Where: scripts/deploy-api.sh (comment "DO NOT add --min-instances=1 ... without explicit user signoff"), live services app-api and app-frontend (no minScale)
• Basis: Measured (first curl to /health after idle: 5.17 s; frontend / 2.93 s; later requests 0.10-0.13 s)
• Evidence: See baseline. Staging already runs minScale=1. This is a deliberate cost decision for a product with no paying customers yet.
• Severity reasoning: Impact 3 (slow first load, everything works), Horizon A (happens now). No adjustment.
• Impact at scale: Every visit after idle starts with a multi-second delay; for a login page on a paid product it costs trust.
• Fix: Set --min-instances=1 on the frontend (cheap) when customers arrive; keep the API at zero if its cold start is acceptable. Cloud Scheduler hitting /health every few minutes is a cheaper stopgap.
• Fix prompt:

  In scripts/deploy-frontend.sh add --min-instances=1 behind an env flag PROD_MIN_INSTANCES (default 0) so I can opt in at deploy time, and document the monthly cost in the script header. Do not run the script.
  

• Verify: After idle, curl -w '%{time_total}' on the frontend returns under 1 s.

[L1] Cleanup job makes several calls per expired run — Low

• Where: api/src/routes/cron.ts:834 (loop over expiredRuns: storage list, remove, delete per run), also :892
• Basis: Inferred (assumption: hundreds of expired runs per sweep)
• Evidence: A storage list and remove plus a delete in a for loop. The other 8 N_PLUS_ONE leads (cost-watcher.ts:106, find-completed-stage-run.ts:167, hop0-projection-reader.ts:86, hydrate-wf4-chunk.ts:145, load-parent-run-pages.ts:56, run-completion.ts:181, stage-context-builder.ts:75, ads-item-performance-sync.ts:230) were not individually read.
• Severity reasoning: Impact 3 (a background job takes longer), Horizon C. No adjustment.
• Impact at scale: At thousands of expired runs a sweep could outlast its schedule; today there are 8 runs.
• Fix: Batch deletes with IN and cap each sweep at N runs.
• Fix prompt:

  In api/src/routes/cron.ts around line 834, process expired runs in batches of 50 and cap one sweep at 200 runs, logging how many remain. Keep behaviour identical for small numbers.
  

• Verify: Sweep logs show the batch count, and a test with 500 fake runs finishes in several batches.

[L2] Policy and index hygiene flagged by Supabase — Low

• Where: production Supabase: 20 RLS policies re-evaluate auth.<function>() per row (auth_rls_initplan); 8 foreign keys without an index (account_members, invites, manager_accounts, manager_members, runs)
• Basis: Measured (advisor output, 2026-11-04)
• Evidence: Verified live performance advisor. Tables hold under 100 rows, so no query is slow.
• Severity reasoning: Impact 3, Horizon C. No adjustment.
• Impact at scale: Policy checks and joins get slower once these tables reach tens of thousands of rows.
• Fix: Wrap auth.uid() in (select auth.uid()) in the policies and add indexes on the listed foreign keys, via a migration.
• Fix prompt:

  Write a migration (do not apply) that recreates the 20 RLS policies flagged by the Supabase auth_rls_initplan lint using (select auth.uid()) and adds CREATE INDEX CONCURRENTLY-style indexes (as separate non-transactional statements) for the 8 unindexed foreign keys on account_members, invites, manager_accounts, manager_members and runs. Show me the SQL.
  

• Verify: Re-run the performance advisor: the two lints are gone.

[L3] Outbound calls without a timeout — Low

• Where: 40 files (scanner FETCH_NO_TIMEOUT), e.g. frontend/src/app/api/cycles/[id]/start/route.ts, frontend/src/app/api/brand-briefs/generate-from-feed/route.ts
• Basis: Inferred (assumption: the API is slow or hangs for minutes under load)
• Evidence: Files call fetch with no AbortSignal; the API runs with a 3,600 s timeout and CPU always allocated, so a hung request keeps an instance billed. Not read file by file.
• Severity reasoning: Impact 2, Horizon C. No adjustment.
• Impact at scale: A slow dependency ties up instances and raises cost instead of failing fast.
• Fix: One fetchWithTimeout helper used by these routes.
• Fix prompt:

  Add frontend/src/lib/fetch-with-timeout.ts (AbortSignal.timeout, default 30 s, configurable) and use it in the frontend API routes that call the internal API, starting with the cycles/[id]/start route. Keep error responses unchanged except for a 504 on timeout.
  

• Verify: A test that stalls the upstream returns 504 within the timeout.

[L4] Module-level state in a worker — Low

• Where: api/src/workers/wf3-executor.ts:1491 (lastProgressUpdateByRun map); also frontend/src/lib/google/ads-customers-cache.ts:47, packages/cost-estimator/src/core/registry.ts:19
• Basis: Inferred
• Evidence: The map is written per run and never emptied; the other two are caches per instance. A leak of a few bytes per run on a 4 GiB instance.
• Severity reasoning: Impact 3, Horizon C. No adjustment.
• Impact at scale: Negligible leak; per-instance caches mean different instances can show different Google customer lists briefly.
• Fix: Delete the run key when the run finishes.
• Fix prompt:

  In api/src/workers/wf3-executor.ts delete the run's entry from lastProgressUpdateByRun in the finalize and failRun paths.
  

• Verify: A test shows the map is empty after a run completes.

Growth runway

All growth inputs are unknown ("no peaks"), so only C1 has a measured date.

Checked and found fine

• Database size: largest user table is 10 rows; no query is slow today.
• Supabase client creation: no CLIENT_PER_REQUEST lead.
• Cloud Run API is sized for long jobs (--no-cpu-throttling, 4 GiB, Cloud Tasks fan-out, max 5-10 instances).
• Warm response times are about 0.1 s.

Not checked or not measured

• Scanner leads not read individually: 8 of the 10 N_PLUS_ONE, HTTP_IN_LOOP (6), POLLING (4), UNCAPPED_PAGE_SIZE (4), BASE64_PAYLOAD (1) and the remaining ~100 SUPABASE_NO_RANGE reads (locations in SCALABILITY-AUDIT-SCAN.txt).
• Query plans (EXPLAIN): not run, because production tables are too small to give meaningful plans.
• Load test: not run; needs your yes for staging or local.
• Gemini quota and rate limits, Cloud Tasks queue limits, Supabase plan limits (connections, size).
• Real growth: the number of customers and feed sizes you expect in 12 months would settle every Horizon in this report.

Remediation timeline

• This week: C1
• This month: H1
• This quarter: M1, M2
• When touching the area: L1-L4

Appendix: scanner output

SUMMARY|LOW|BASE64_PAYLOAD|1
SUMMARY|LOW|FETCH_NO_TIMEOUT|40
SUMMARY|LOW|HTTP_IN_LOOP|6
SUMMARY|LOW|POLLING|4
SUMMARY|LOW|UNCAPPED_PAGE_SIZE|4
SUMMARY|MEDIUM|IN_MEMORY_RATE_LIMIT|2
SUMMARY|MEDIUM|IN_MEMORY_STATE|4
SUMMARY|MEDIUM|N_PLUS_ONE|10
SUMMARY|MEDIUM|SUPABASE_NO_RANGE|108
SUMMARY|MEDIUM|UNBOUNDED_QUERY|12

The full scanner output is in SCALABILITY-AUDIT-SCAN.txt.

This report is guidance produced with an AI assistant. Findings marked Inferred come from reading the code. Confirm real capacity with load tests on your own environment.