Security audit.

System: Example App (Next.js frontend + Hono API on Google Cloud Run, Supabase Postgres) Date: 2026-11-04 Scope: whole repository except audit archive/ (excluded at your request). 1,182 files scanned. Read-only live check of the production Supabase project (advisors only). Not examined: the staging Supabase project, Stripe dashboard, Google Cloud IAM and Secret Manager (this gcloud login lacks permission). Data handled: Google OAuth access and refresh tokens (table google_tokens), Google Ads access on behalf of users, user email addresses, Stripe payments and subscriptions (frontend/src/app/api/stripe/*). You said there is no other sensitive data; the code shows Stripe billing and stored Google tokens, so this report treats both as in scope. Stripe card data itself never touches your code (Stripe hosts it). Dependencies: checked with npm audit on 2026-11-04: 1 critical, 11 high (production dependencies) Method: scanner (scripts/scan.sh) + manual verification + checklist + rubric. The checklist in references/checklist.md was applied to the areas listed under "Checked and found fine"; it was not worked through item by item for all 65 endpoint files.

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

Summary

The basics are sound: row level security is enabled on all 55 tables, the Stripe webhook verifies its signature, and the API routes I read check a shared secret. The weak spots are outdated dependencies (Next.js has a known critical advisory with a non-major fix available), database functions that anonymous callers can execute, and one shared secret protecting a publicly reachable API. Findings: 0 Critical, 2 High, 3 Medium, 2 Low. The most urgent issue is upgrading Next.js.

Top 3 actions

1. This week: upgrade next to 16.4.5 (non-breaking, npm audit fix) and redeploy the frontend (H1).
2. This week: revoke EXECUTE on the four SECURITY DEFINER functions from anon (H2).
3. This month: run npm audit fix for the remaining packages (M1) and move the API off the single shared secret (M2).

Findings

[H1] Next.js has known vulnerabilities (npm audit: critical) — High

• Where: frontend/package.json:22 ("next": "16.2.2")
• Evidence: npm audit --omit=dev marks next critical, affected range up to 16.4.0, with advisories for denial of service in Server Components, cache poisoning of middleware redirects, and cross-site scripting in App Router apps that use CSP nonces. npm reports the fix as next@16.4.5, not a major jump.
• Severity reasoning: Reach A (public website, no login needed), Impact 2 (mostly denial of service and limited XSS or cache effects, not direct takeover). No adjustment.
• Impact: Anyone on the internet could crash or degrade the app, and in some configurations inject script into pages or poison cached redirects.
• Fix: cd frontend && npm install next@16.4.5, rebuild, test login, and redeploy with scripts/deploy-frontend.sh.
• Assessor note: npm labels this critical; under this rubric the realistic worst case is High. The advisory text was not tested against your CSP and middleware setup.
• Fix prompt:

  In frontend/package.json upgrade next from 16.2.2 to 16.4.5 (and any matching eslint-config-next / @next/* packages), run npm install, then npm run build and the existing frontend tests. Fix any type or build errors without changing behaviour. Do not deploy.
  

• Verify: npm audit --omit=dev no longer lists next or its postcss/sharp children.

[H2] Database functions can be executed by anonymous visitors — High

• Where: production Supabase project (public.get_accessible_account_ids, public.get_accessible_manager_ids, public.handle_new_user, public.rls_auto_enable)
• Evidence: Verified live. The Supabase security advisor reports all four are SECURITY DEFINER and executable by the anon and authenticated roles through /rest/v1/rpc/<name>. Three also have a mutable search_path. get_accessible_account_ids(p_user_id uuid) takes any user id as a parameter.
• Severity reasoning: Reach A (anon key is public), Impact 2 (the two lookup functions can reveal which accounts a given user can reach; I did not call them, so the exact data returned is unverified). No adjustment.
• Impact: Someone who has the public anon key and a guessed or leaked user id could probe account and manager membership.
• Fix: Revoke execute from anon (and authenticated for the trigger and maintenance functions), set search_path on all three functions, and make the lookup functions read the caller from auth.uid() instead of a parameter.
• Fix prompt:

  Write a new migration in sql/migrations that: REVOKE EXECUTE ON FUNCTION public.get_accessible_account_ids(uuid), public.get_accessible_manager_ids(uuid), public.handle_new_user(), public.rls_auto_enable() FROM PUBLIC, anon; also revoke from authenticated for handle_new_user and rls_auto_enable; ALTER FUNCTION ... SET search_path = public, pg_temp for the three functions with a mutable search_path. First check that RLS policies calling the two lookup functions still work for the authenticated role. Do not apply it; show me the SQL.
  

• Verify: Re-run the Supabase security advisor; the anon_security_definer_function_executable and function_search_path_mutable lints disappear.

[M1] Other vulnerable dependencies — Medium

• Where: api/package.json (hono ^4.7.0, ws ^8.20.0), plus transitive protobufjs, @grpc/grpc-js, form-data, nanoid, brace-expansion, fast-xml-builder, source-map-js
• Evidence: npm audit high severity: Hono (JWT claim validation, cache middleware ignoring Vary, bodyLimit bypass), ws (memory disclosure, memory exhaustion), protobufjs (code injection through generated code), grpc-js (crash on malformed message). Whether your code uses the affected Hono features (JWT, cache, bodyLimit) was not checked.
• Severity reasoning: Reach A, Impact 3 (not shown to be reachable in your code, so counted as weakening). No adjustment.
• Impact: Denial of service or crashes on the API under crafted requests; the more serious items need features you may not use.
• Fix: npm audit fix in the repo root, rerun tests, redeploy the API.
• Fix prompt:

  Run npm audit fix at the repo root and in api/. Review the resulting package-lock.json diff, run all tests (npm test in each workspace) and the API type check. List any vulnerabilities that remain and which need a major upgrade. Do not deploy.
  

• Verify: npm audit --omit=dev reports 0 high or critical, or lists only items you have decided to accept.

[M2] API protected only by one shared secret, compared in a non-constant-time way — Medium

• Where: api/src/routes/scrape.ts:33, brand-brief.ts:25, cycles.ts:32, tasks.ts:50, tasks/activate.ts:42, ads-client.ts:27, cron.ts:312
• Evidence: The Cloud Run API is deployed with --allow-unauthenticated (scripts/deploy-api.sh, line 51 of the deploy command) and ingress all (Verified live; IAM shows allUsers as invoker). Every internal route checks one header x-api-secret with !==. The same secret is also sent by Cloud Tasks and the frontend.
• Severity reasoning: Reach C (needs the secret), Impact 1 (the routes start scrapes, LLM runs and Google Ads sync with the service-role database behind them). No adjustment.
• Impact: If the secret leaks (logs, a laptop, a screenshot) anyone on the internet can start costly runs and write to your data; there is no second barrier and no way to rotate without redeploying every caller.
• Fix: Use crypto.timingSafeEqual now. Then make the API private (remove allUsers, grant roles/run.invoker to the frontend and Cloud Tasks service accounts, and have callers send Google-signed ID tokens).
• Fix prompt:

  Create one helper api/src/lib/verify-api-secret.ts that compares the x-api-secret header with process.env.API_SECRET using crypto.timingSafeEqual (handle length mismatch and a missing env var). Replace the inline comparisons in api/src/routes/scrape.ts, brand-brief.ts, cycles.ts, tasks.ts, tasks/activate.ts, ads-client.ts and cron.ts with it, keeping their current status codes. Add unit tests. Do not change deploy scripts.
  

• Verify: Tests pass; a request with a wrong secret of the same length still gets 401.

[M3] Google OAuth tokens stored in plain text — Medium

• Where: sql/migrations/011_google_tokens.sql:18 (access_token TEXT, refresh_token), written by frontend/src/lib/google/token-store.ts:62-86
• Evidence: Tokens are stored unencrypted in a normal table. RLS is on and users can read only their own row; the service role bypasses it. No pgcrypto, Vault or application-level encryption exists in frontend/src/lib/google or sql.
• Severity reasoning: Reach C (needs database or service-role access), Impact 1 (refresh tokens grant access to users' Google Ads accounts). No adjustment (Google Ads accounts are not payment data).
• Impact: A database leak, a backup leak, or a leaked service-role key would hand over long-lived access to every connected user's ad accounts.
• Fix: Encrypt refresh tokens with a key kept in Secret Manager (application-level AES-GCM) or Supabase Vault; migrate existing rows.
• Fix prompt:

  In frontend/src/lib/google/token-store.ts add encrypt/decrypt of access_token and refresh_token with AES-256-GCM using a key from process.env.GOOGLE_TOKEN_ENC_KEY (32 bytes, base64). Store ciphertext with a version prefix, read both old plaintext and new ciphertext, and write a one-off script (not run automatically) that re-encrypts existing rows. Add tests. Do not change secrets or apply migrations.
  

• Verify: select refresh_token from google_tokens limit 1 returns ciphertext, and the Google connection still works.

[L1] Containers run as root — Low

• Where: api/Dockerfile, frontend/Dockerfile, frontend/Dockerfile.staging
• Evidence: No USER instruction (scanner DOCKER_ROOT, confirmed by reading frontend/Dockerfile stages: only FROM).
• Severity reasoning: Reach C, Impact 3. No adjustment.
• Impact: If the app were compromised, the attacker starts with root inside the container, which makes escalation easier. Cloud Run's sandbox limits the damage.
• Fix: Add USER node after the install and build steps (the node base images include it).
• Also reported in: INFRASTRUCTURE-AUDIT-REPORT.md, M3
• Fix prompt:

  In api/Dockerfile, frontend/Dockerfile and frontend/Dockerfile.staging add a non-root user in the final stage (USER node, with chown on /app as needed) without changing the build output. Build each image locally to confirm it starts.
  

• Verify: docker run --rm <image> id -u prints a non-zero id.

[L2] Leaked-password protection disabled in Supabase Auth — Low

• Where: production Supabase Auth settings
• Evidence: Verified live via the advisor lint auth_leaked_password_protection.
• Severity reasoning: Reach B, Impact 3. No adjustment.
• Impact: Only matters if email and password sign-up is enabled; you said users sign in with Google.
• Fix: Enable the setting in the Supabase dashboard, or disable email/password sign-up if unused.
• Fix prompt:

  Check frontend/src/app for any email+password sign-up or sign-in code (supabase.auth.signUp, signInWithPassword). Report whether it exists. If it does not, tell me to disable the email provider in the Supabase dashboard; do not change code.
  

• Verify: Re-run the Supabase security advisor.

Checked and found fine

• Stripe webhook verifies the signature with constructEvent before acting (frontend/src/app/api/stripe/webhook/route.ts:40-63).
• Row level security is enabled on all 55 tables created in SQL files (scanner) and google_tokens has an own-row read policy.
• ROUTE_NO_AUTH_SIGNAL on api/src/routes/health.ts: false positive, it returns only status, service name and time.
• SECRET_LOGGED (9 leads): I read token-store.ts:62,82,86,159,228,246,252, auth/callback/route.ts:77 and feed-fetch-executor.ts:165. They log user ids, booleans and error messages, not token values.
• SHELL_EXEC in scripts/check-schema-vs-code.ts: a developer script, not reachable from the web.
• Secrets in git: only .env.example is tracked; a pattern search for API key and private key formats found none in source.
• Internal API routes read (scrape, brand-brief, cycles, tasks, ads-client, cron) all check the secret and fail closed when it is unset; POST /api/cron/sweep-stale-runs without a secret returned 404 on the live API.

Not checked or not verified

• Frontend route handlers (65 endpoint files in total): I did not read each for authorization and CSRF; only the Stripe webhook and rate-limit.ts were read. Needs a follow-up pass.
• Google Cloud IAM, Secret Manager and the service account's roles: this login lacks permission. The owner can confirm (see INFRASTRUCTURE-AUDIT-REPORT.md, H3).
• Staging Supabase project and Stripe dashboard settings (webhook endpoint, restricted keys).
• Whether the Hono features named in M1 are used.
• Penetration testing: this audit is code review only.

Remediation timeline

• Today: nothing is critical. No live credential was found in the code.
• This week: H1 (Next.js), H2 (database functions).
• This month: M1, M2, M3.
• When convenient: L1, L2.

Appendix: scanner output

SUMMARY|HIGH|NPM_AUDIT|1
SUMMARY|HIGH|ROUTE_NO_AUTH_SIGNAL|1
SUMMARY|MEDIUM|DOCKER_ROOT|3
SUMMARY|MEDIUM|SECRET_LOGGED|9
SUMMARY|MEDIUM|SHELL_EXEC|2

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

This report is guidance produced with an AI assistant. It is not a penetration test or a compliance certification. Because the app handles payments and Google account access, consider a professional penetration test before significant paying traffic.