Where AI coding assistants get backends wrong

What we see

A counter, a balance, a stock level, or a status transition implemented as: read the current value, compute the new one in application code, write it back. Under sequential requests this is correct. Under two concurrent requests, one of the two updates is silently lost — and no error is raised anywhere, because both writes succeeded.

Why an assistant produces it

Read-then-write is how the operation is described in English, and generated code follows the shape of the description. Expressing it as a single atomic statement requires knowing that concurrency is a concern, which the prompt did not say.

The pattern

const account = await db.account.findUnique({ where: { id } });
await db.account.update({
  where: { id },
  data: { credits: account.credits - cost },
});

The fix

// Atomic in the database — the read and the write are one statement.
const updated = await db.account.updateMany({
  where: { id, credits: { gte: cost } },
  data: { credits: { decrement: cost } },
});
if (updated.count === 0) throw new InsufficientCredits();

The correct implementation

Push the arithmetic into the database as a single statement, with the precondition in the WHERE clause so the guard and the write cannot be separated by another transaction. Where the logic is genuinely too complex for that, use an explicit optimistic-concurrency version column and fail the write when the version has moved — but a decrement is not too complex for that.

What we see

A list endpoint that loads a collection and then, inside the serialisation loop, touches a relation on each item. The ORM makes this look like property access, so it does not read like a query. On the seed dataset of twenty rows it is 21 queries and 40ms. On a real account with 5,000 rows it is 5,001 queries and a timeout.

Why an assistant produces it

Lazy-loading relations is the ergonomic default of every ORM, and the generated code reads more naturally without an explicit include. There is nothing in the source to indicate a query is happening.

The pattern

const orders = await db.order.findMany({ where: { orgId } });
return orders.map((o) => ({
  id: o.id,
  customer: o.customer.name,  // one query per order
  lines: o.lines.length,      // and another
}));

The fix

const orders = await db.order.findMany({
  where: { orgId },
  select: {
    id: true,
    customer: { select: { name: true } },
    _count: { select: { lines: true } },
  },
});

The correct implementation

Select the shape you are going to serialise, explicitly, at the query. Then make the class of bug visible in CI: most ORMs can log query counts per request, and asserting that a list endpoint issues a constant number of queries regardless of result size is a cheap test that catches every future regression of this kind.

What we see

A retry wrapper around an outbound call — a payment capture, an email send, a webhook delivery — with exponential backoff and a sensible attempt limit. What is missing is an idempotency key. When the first attempt actually succeeded but the response was lost to a timeout, the retry executes the operation a second time. The customer is charged twice.

Why an assistant produces it

"Add retries with backoff" is a complete, well-formed request that the assistant answers completely. Idempotency is a property of the operation being retried, not of the retry logic, so it lives outside the boundary of what was asked.

The pattern

for (let i = 0; i < 3; i++) {
  try {
    return await stripe.paymentIntents.create({ amount, currency });
  } catch {
    await sleep(2 ** i * 200);
  }
}

The fix

// One key per logical operation, stable across every retry.
const key = `capture:${orderId}`;
for (let i = 0; i < 3; i++) {
  try {
    return await stripe.paymentIntents.create(
      { amount, currency },
      { idempotencyKey: key },
    );
  } catch (err) {
    if (!isRetryable(err)) throw err;   // don't retry a 400
    await sleep(2 ** i * 200 + Math.random() * 100); // jitter
  }
}

The correct implementation

Every retried operation with a side effect needs an idempotency key derived from the logical operation, not from the attempt. Two other things belong in the same wrapper: only retry errors that are actually retryable — retrying a 400 just delays the failure — and add jitter, because synchronised backoff across many clients reconstructs the thundering herd the backoff was meant to prevent.

What we see

Two variants. The first: a limit parameter taken straight from the query string with no ceiling, so a client sending ?limit=1000000 pulls the whole table into memory. The second: OFFSET-based pagination, which is correct but degrades linearly — the database scans and discards every skipped row, so page 5,000 is thousands of times more expensive than page 1.

Why an assistant produces it

LIMIT/OFFSET is the canonical pagination example everywhere. Keyset pagination is correct but wordier, and nothing in "add pagination to this endpoint" suggests the deep-page case matters.

The pattern

const limit = Number(req.query.limit ?? 20);
const offset = Number(req.query.offset ?? 0);
return db.event.findMany({ where: { orgId }, take: limit, skip: offset });

The fix

const limit = Math.min(Number(req.query.limit ?? 20), 100);
// Keyset: seek from the last row seen instead of counting past it.
const cursor = req.query.after as string | undefined;
return db.event.findMany({
  where: { orgId, ...(cursor ? { id: { lt: cursor } } : {}) },
  orderBy: { id: "desc" },
  take: limit,
});

The correct implementation

Clamp the page size server-side, always. Use keyset (cursor) pagination for anything a user can page deeply through or that a client will iterate to exhaustion — a feed, an export, an audit log. Offset is fine for a ten-page admin table and nothing larger.

What we see

A generated migration that adds a NOT NULL column with a default, adds an index without CONCURRENTLY, or changes a column type on a large table. Every one of these is correct SQL. On a 200-row development database each runs in milliseconds. On a 50-million-row production table, they take an ACCESS EXCLUSIVE lock and every read of that table blocks until the migration finishes.

Why an assistant produces it

The migration tool generates the straightforward statement, and the assistant has no knowledge of the production row count or the lock semantics of the specific Postgres version in play. It is correct at the level it is operating.

The pattern

ALTER TABLE events ADD COLUMN source text NOT NULL DEFAULT 'api';
CREATE INDEX idx_events_org ON events (org_id);

The fix

-- Separate the lock-free steps; backfill in batches between them.
ALTER TABLE events ADD COLUMN source text;              -- instant
-- (application starts writing `source`; backfill old rows in batches)
ALTER TABLE events ALTER COLUMN source SET NOT NULL;    -- after backfill

CREATE INDEX CONCURRENTLY idx_events_org ON events (org_id);

The correct implementation

Split any migration touching a large table into steps that each take a short lock, and build indexes concurrently. The general rule: the deploy must be safe with the old code and the new code both running, because during a rolling deploy they are. That rules out renaming and dropping columns in the same release as the code change.

What we see

A queue consumer that processes a job, catches errors, logs them, and acknowledges the message. The job is now gone. Nothing retried it, nothing recorded that it failed permanently, and nothing is monitoring the gap between jobs enqueued and jobs completed. The system appears healthy while quietly dropping work.

Why an assistant produces it

Catching the error and acking is what stops the consumer crash-looping, which is the visible problem the code was written to solve. A dead-letter queue is a piece of infrastructure, not a piece of code, so it is outside what a code-generation prompt produces.

The pattern

queue.process(async (job) => {
  try {
    await handle(job.data);
  } catch (err) {
    logger.error("job failed", err);  // and it's gone
  }
});

The fix

queue.process(async (job) => {
  try {
    await handle(job.data);
  } catch (err) {
    if (job.attemptsMade < MAX_ATTEMPTS) throw err;  // let the queue retry
    await deadLetter.add(job.name, { data: job.data, error: String(err) });
    metrics.increment("job.dead_lettered", { job: job.name });
  }
});

The correct implementation

A failed job goes to a dead-letter queue with the payload and the error attached, and dead-lettering increments a metric that someone alerts on. The alert should be on queue depth and on job age, not just on error rate — a consumer that has stopped consuming produces zero errors.