Database fixes for AI-built apps

The most common failures

Five database-category failure modes appear in every AI-built app rescue intake. Each is a predictable consequence of the generator optimizing for the single-user demo path and never stressing the multi-user production path.

• RLS enabled without matching policies. The most common symptom is 42501 permission denied on select, or a silent PGRST116 on insert. The table has RLS turned on but no WITH CHECK policy covering INSERT, so every write fails. See Supabase RLS blocking insert.
• Insert returns 200 but the row never appears. RLS with a SELECT policy but no INSERT policy, a .select() filter that excludes the new row, or the anon key writing to a table only the service-role can see. The client reads success; the database never wrote. See Database saves with no error and no row.
• N+1 queries on every list page. AI tools generate list/detail patterns as separate useEffect hooks. 20 rows, 21 queries, 1.4 seconds of synchronous latency. Fix with Prisma include, Supabase nested select, or a Dataloader. See N+1 query slow page load.
• Connection pool exhausted under load. Prisma on serverless instantiates a new client per concurrent request. The Supabase free tier caps at 60 connections; 30 concurrent users with default settings exhausts the cap and throws P1001 or FATAL: too many clients. Switch to the pooled DATABASE_URL (pgbouncer, port 6543) with connection_limit=1. See App crashes under load.
• Supabase storage 403 on upload. Storage buckets are RLS-governed separately from tables. AI-scaffolded uploads miss the matching storage.objects policy on the bucket or push to a path prefix that does not match auth.uid(). See Supabase storage upload 403.
• Service-role key in a client bundle. Not its own leaf because it is a pattern, not a symptom. A NEXT_PUBLIC_SUPABASE_SERVICE_ROLE in .envexposes the key to every visitor's devtools and grants full database access. Rotate immediately and move server-only keys out of any NEXT_PUBLIC_ variable.

Shared root causes

Database symptoms cluster into four root causes. Rule each out before reading individual queries.

• Security model written in a hurry, or not at all. RLS disabled, left at the default permissive, or enabled without the corresponding INSERT/UPDATE policies. Storage buckets without object-level policies. Service-role key used where the anon key would be correct (or worse, the other way around).
• Query patterns that assume one user. N+1 list/detail loops, no pagination, select * returning joined multi-megabyte rows, and missing indexes on the filter columns the app actually uses.
• Connection topology wrong for serverless. Direct Postgres connections from Vercel functions, no pooler, default Prisma connection_limit=10. The architecture works for one test user and breaks at the first ten concurrent requests.
• Client/server key confusion. Anon key used where service-role is required (silent permission failures) or service-role used in client code (a catastrophic leak). Both stem from AI tools not distinguishing the two surfaces clearly in the scaffold.

Prevention checklist

Merge these before the next database-touching deploy. Each one closes a class of silent failure.

1. Enable RLS on every public table. Write one USING policy for SELECT and one WITH CHECK policy for INSERT/UPDATE/DELETE matching auth.uid() to the ownership column.
2. Write an integration test that inserts a row as User A and asserts User B cannot read it. Run it on every migration.
3. Route all Prisma traffic through the pooled DATABASE_URL (port 6543 on Supabase, pgbouncer equivalent on Neon). Set connection_limit=1 in serverless.
4. Keep the service-role key in server-only env vars. Never prefix with NEXT_PUBLIC_. Never log it, never send it to a logging service unsanitized.
5. Add an index on every foreign key column and every column used in a where filter the app actually ships.
6. Replace list/detail useEffect patterns with Prisma include, Supabase nested select (select("*, detail(*)")), or a Dataloader.
7. Add a Supabase storage bucket policy for every bucket before the first upload hits production.
8. Run the app under a load test of at least 50 concurrent requests against a fresh deploy. Connection-pool exhaustion only appears under load.
9. Audit the SQL log (Supabase Dashboard → Database → Logs) after every deploy for unexpected error codes — especially 42501, PGRST116, and FATAL entries.
10. Back up the database on a schedule independent of the platform's default snapshots, and test the restore path before the first incident.