The 12 Database Mistakes That Show Up in AI-Generated Apps

Quick answer

• These twelve appear repeatedly in AI-generated apps because the code works against the small dataset the assistant can imagine.
• The most damaging are the invisible ones: a missing index and the N+1 pattern, both invisible until row counts grow.
• Two are data-integrity bugs rather than performance bugs — floating-point money and timezone-free timestamps — and they corrupt records quietly.
• Five of the twelve are safe to fix today. The rest need a data cleanup before the schema change will apply.
• Each item below names the failure, why generated code produces it, and the fix — with the linked guide for the full treatment.

1. No index on the column you filter and sort by

The query is WHERE user_id = ? ORDER BY created_at DESC, and the table has a primary key index on id and nothing else. With 200 rows it returns instantly; with 500,000 rows the database reads every one and sorts the result.

This is the single most common cause of “the app got slow and I didn’t change anything.” Generated code filters on whatever column expresses the feature, and an index exists only if someone thought to create one.

The fix: add a composite index matching the filter-then-sort order — CREATE INDEX ON orders (user_id, created_at DESC) — and confirm with EXPLAIN ANALYZE that the plan uses an index scan. See What Is Database Indexing (and Why Is My Query Slow)? and How to Add an Index to a Slow SQL Query.

2. SELECT * in application queries

SELECT * FROM orders WHERE user_id = ? returns every column, including the large text or JSON ones the page doesn’t use. It also couples the code to the schema: adding a column changes every response shape, and removing one breaks code that never referenced it deliberately.

The cost is usually bandwidth and deserialization rather than query time, which is why it survives review — but on a table with a payload jsonb column it’s substantial.

The fix: name the columns the feature needs. It documents the contract, lets the database skip reading unused columns, and makes schema changes additive instead of breaking.

3. One query per row in a loop (the N+1 problem)

The list endpoint fetches 50 records, then loops over them to load each one’s author — 51 queries where two would do. Each query is fast in isolation; the cost is 51 network round trips and 51 slots of connection time.

This is the most common performance defect in ORM-generated code, because a loop that calls a relationship accessor is the natural way to write it and looks nothing like a problem.

The fix: fetch the related records in one query — a join, or an IN lookup for the batch of IDs — and map them in memory. See What Is the N+1 Query Problem?.

4. Filtering and sorting in application code

The code fetches all rows matching a broad condition and then filters, sorts, or paginates in a language-level loop. It’s a symptom of the assistant being unsure how to express the predicate in SQL, so it moves the work somewhere it’s confident.

The failure scales badly: the database returns everything, the application holds it in memory, and both get slower as the table grows. Pagination done this way also returns wrong page counts.

The fix: push the predicate, the sort, and the limit into the query — WHERE, ORDER BY, LIMIT/OFFSET or a keyset cursor. If the query is hard to express, that difficulty is information about the schema.

5. Money stored as a floating-point number

A float or double column for prices and totals, or the application’s native float type mapped to one. Binary floating point can’t represent most decimal fractions exactly, so repeated arithmetic drifts and totals stop matching the sum of their parts.

The bug is invisible in testing — small amounts round to what looks right — and appears as off-by-a-cent discrepancies that erode trust in every financial figure the app shows.

The fix: store money as integer minor units (total_cents) or as numeric(12,2). See the type guidance in How to Design a Database Schema (That You Won’t Have to Rewrite).

6. Timestamps with no time zone

A timestamp column without a zone, written from the application’s local clock. It has no way to distinguish 14:00 in one region from 14:00 in another, and it becomes ambiguous the moment the app runs in two time zones or a scheduled job crosses a daylight-saving boundary.

The damage is subtle and cumulative: reports that don’t add up, “today” queries that include the wrong records for a few hours, and events ordered incorrectly by an hour twice a year.

The fix: use timestamptz, write UTC, and convert to local time only for display. See Why Should Servers Always Use UTC?.

7. Uniqueness enforced only in application code

A check like “does a user with this email exist?” followed by an insert. Two concurrent requests both pass the check and both insert, so the table ends up with duplicates that the application believes are impossible.

This is the classic check-then-act race, and it’s the default shape of generated registration and invite flows. No amount of application-level validation closes it, because the gap between the check and the insert is where the second request lands.

The fix: a UNIQUE constraint on the column, and code that handles the resulting error. The database serializes the decision. See What Is a Race Condition?.

8. No foreign keys, so orphaned rows accumulate

Relationships exist only as user_id integers with no REFERENCES clause. Deleting a user leaves orders pointing at nothing, and the queries that join them silently drop rows — or the application crashes on a null where an object was expected.

Generated code often skips foreign keys because it validates relationships in application code, which is a rule the database never learns and no other writer respects.

The fix: declare the foreign key with an explicit ON DELETE behavior, then clean up existing orphans before adding it — the constraint won’t apply while they exist.

9. Multi-step writes with no transaction

A transfer that debits one row and credits another, run as two statements with no transaction between them. If the process dies, the second write is lost, and the books no longer balance — with no error raised anywhere.

Single-statement writes are atomic on their own, which makes this look unnecessary. It becomes necessary the moment a logical operation touches two rows that must change together.

The fix: wrap the sequence in a transaction so it commits entirely or not at all. See What Is a Database Transaction (ACID)? and What Is Idempotency (and Why Does It Matter for APIs)? for the retry side.

10. A connection per request instead of a pool

The code opens a database connection when a request arrives and closes it when the response finishes. That’s fine with one user, and with fifty concurrent users it exhausts the server’s connection limit and starts returning “too many connections.”

Each connection costs memory on the database server, and process-per-connection engines have a low ceiling compared to the request concurrency a small app can generate.

The fix: use a connection pool sized deliberately, and a pooled proxy if you have many application processes. See What Is a Connection Pool? and How to Fix ‘Too Many Connections’ in Postgres.

11. Destructive migrations applied in one step

A single migration that adds a NOT NULL column without a default, drops a column the running code still writes to, or renames a table — applied against production while the previous application version is still serving traffic.

The migration either fails outright (adding NOT NULL to a populated table) or succeeds and breaks the running app (dropping a column still in use). Both outcomes arrive at deploy time, which is the worst moment to discover them.

The fix: split into expand and contract phases — add nullable, backfill, enforce; for removals, stop writing, deploy, then drop. See What Are Database Migrations (and Why Do They Break Deploys)?.

12. Tables that grow forever, with an untested backup

No retention policy on the log, event, or session table the assistant created “just in case,” and a backup job whose restore has never been attempted. The table’s indexes grow with it, queries over it get slower every week, and the backup is a hypothesis rather than a guarantee.

Neither problem produces an error until the day it does — a query timeout, a disk limit, or a restore that fails when it’s the only remaining copy.

The fix: define a retention window and enforce it with a scheduled job, and perform a restore successfully at least once so the backup is a verified capability rather than an assumption. See How to Set Up Automated Database Backups and What Is Log Rotation (and Why Do Your Logs Keep Disappearing)?.

Where this bites vibecoders

These twelve share a cause: the assistant writes code that is correct against a database it can picture, and what it pictures is a development seed with ten rows, one user, one request at a time, and no timezone. Every mistake above is invisible under those conditions and expensive under real ones. The practical consequence is that a review checklist tuned for a human developer — naming, structure, tests — misses them entirely, because nothing in the diff looks wrong. Reviewing generated database code means asking a different set of questions: what happens at a million rows, what happens when two requests arrive at once, what happens when this write fails halfway, and what happens when the process restarts mid-deploy. Those four questions catch most of the list.

Checklist

• Index every column your queries filter and sort on, then verify the plan.
• Select named columns, not *.
• Batch related loads instead of querying inside a loop.
• Filter, sort, and paginate in SQL, not in application code.
• Store money as integer minor units or numeric, never a float.
• Use timestamptz, write UTC, convert for display only.
• Enforce uniqueness with a constraint, not a check-then-insert.
• Declare foreign keys and pick the ON DELETE behavior deliberately.
• Wrap multi-step writes in a transaction.
• Use a connection pool and size it on purpose.
• Split migrations into add-nullable, backfill, enforce.
• Set a retention window for log and event tables, and test a restore.

FAQ

What is the most common database mistake in AI-generated code?

No index on the column the query filters and sorts by. Development data is small enough that a full table scan feels instant, so nothing in the assistant’s experience signals a problem, and the query ships unchanged. It becomes visible only when the table grows, at which point the fix is one index that nobody thought to add.

How do I find these mistakes in an app that already shipped?

Start with the slow endpoints and run EXPLAIN ANALYZE on the queries behind them; a sequential scan over a large table points at a missing index. Then grep the codebase for SELECT *, check logs for connection errors, and look at what happens on a retry — duplicate rows mean a missing constraint or a non-idempotent write.

Should I fix all of these at once?

No. Fix in order of impact and risk. Missing indexes, N+1 queries, and connection pooling are safe changes you can make today. Constraint and type fixes need data cleaned up first — you can’t add a unique constraint while duplicates exist, and changing a money column’s type requires a migration with a backfill.

Why does my AI assistant keep making these mistakes?

Three reasons. It writes code that works against the data it can imagine, which is a handful of rows. It usually can’t see your schema, so it guesses at column names and relationships. And it optimizes for the feature in front of it rather than the query load six months out. None of that is fixable by asking for better code in general — it’s fixable by reviewing the specific items above.

Related topics

• Databases for AI Builders: Why Your AI-Generated App Gets Slow and Loses Data
• How to Design a Database Schema (That You Won’t Have to Rewrite)
• How to Choose a Database for Your App: A Decision Guide
• What Are the Different Types of Databases (and What Is Each One For)?
• What Is Database Indexing (and Why Is My Query Slow)?
• What Is the N+1 Query Problem?

Sources

• PostgreSQL Wiki — Don’t Do This
• Use The Index, Luke — SQL indexing and tuning
• PostgreSQL Documentation — Numeric Types
• PostgreSQL Documentation — Date/Time Types
• Martin Fowler — ParallelChange (expand/contract migrations)