Prisma Migrations When AI Features Touch Your Database

This post was created with AI assistance and reviewed for accuracy before publishing.

AI features arrive with a distinctive database footprint. You need somewhere to keep conversations, the messages inside them, the tool calls each message triggered, and the feedback users leave. Individually these look like ordinary tables. Collectively they are the fastest-growing thing in your schema, because a single user session writes dozens of rows where a normal feature writes one.

That growth rate is what makes migrations on these tables different. A schema change that is instant on your users table can lock a messages table with fifty million rows for long enough to take the product down.

Model messages as append-only from the start

The most useful early decision is treating message rows as immutable:

model Message {
  id             String   @id @default(cuid())
  conversationId String
  role           String   // user | assistant | tool
  content        String   @db.Text
  createdAt      DateTime @default(now())

  conversation Conversation @relation(fields: [conversationId], references: [id], onDelete: Cascade)

  @@index([conversationId, createdAt])
}

Append-only tables are far easier to migrate. There are no in-place updates to contend with, backfills can run in ranges without racing live writes, and you can archive old partitions without touching application logic.

The compound index on [conversationId, createdAt] is the one query this table actually serves: fetch a conversation's messages in order. A single-column index on conversationId still requires a sort afterwards, which on a long conversation is the difference between a fast read and a slow one.

Expand and contract, never rename

Prisma will happily generate a migration that renames or retypes a column. On a large table under load, that is how you cause an outage. The safe pattern splits it into steps that are each individually harmless.

Suppose Message.content needs to become structured JSON:

1. Expand. Add contentJson as a nullable column. Nullable additions do not rewrite the table.
2. Dual-write. Deploy code that writes both columns and reads the old one.
3. Backfill. Populate contentJson for existing rows in batches.
4. Switch reads. Deploy code that reads the new column, still writing both.
5. Contract. Once nothing reads it, drop content.

Every step is independently reversible, and at no point does a deploy require the schema and the code to change simultaneously. That last property is the real prize: it means a rollback never leaves you with code expecting a column that no longer exists.

Backfills should run in bounded batches rather than one statement:

let cursor: string | undefined;
for (;;) {
  const batch = await prisma.message.findMany({
    where: { contentJson: null },
    select: { id: true, content: true },
    take: 1000,
    ...(cursor && { cursor: { id: cursor }, skip: 1 }),
    orderBy: { id: 'asc' },
  });
  if (batch.length === 0) break;
  await Promise.all(batch.map((m) =>
    prisma.message.update({ where: { id: m.id }, data: { contentJson: parse(m.content) } })
  ));
  cursor = batch.at(-1)!.id;
}

A single UPDATE across fifty million rows holds locks and generates enough WAL to cause its own problems. Batching keeps each transaction short and lets you stop partway without losing work.

Watch for the index build that locks

Adding an index to a growing table is the other common outage. By default Postgres takes a lock that blocks writes for the duration of the build.

Prisma Migrate generates plain CREATE INDEX. On a large table you want the concurrent form, which requires editing the generated SQL by hand:

-- Edit the generated migration before applying it.
CREATE INDEX CONCURRENTLY "Message_conversationId_createdAt_idx"
  ON "Message" ("conversationId", "createdAt");

CONCURRENTLY cannot run inside a transaction, so the migration file needs its transaction wrapper removed as well. It is slower and it can fail partway, leaving an invalid index you have to drop and retry. Both are better than blocking writes on your busiest table.

This is the strongest argument for reading generated migration SQL rather than applying it unseen. The Prisma schema is a good abstraction, and the SQL it produces is where the operational consequences live.

Plan the retention policy before you need it

Conversation data grows without bound and most of it is never read again. Decide early what happens to it, because retrofitting deletion onto a table with foreign keys pointing at it is considerably harder.

onDelete: Cascade on the relation, as in the model above, means deleting a conversation removes its messages automatically. That is convenient and it is also a large transaction if the conversation is long, so batch deletions by conversation rather than by date range across the whole table.

Retention is also frequently a compliance requirement rather than a housekeeping one. If users can request deletion of their data, the tool call log is easy to forget: it often contains the prompt text, which is the thing they are asking you to delete.

Review the SQL, especially when it is generated

The practical rule that prevents most of the above: no migration reaches production without a person reading the SQL.

This matters more now that schema changes are often drafted by an assistant. A generated migration is usually correct in the sense that it produces the described schema. Whether it does so without locking a table for four minutes is a different question, and not one the generator is reasoning about.

Read the file, check for table rewrites, index builds, and NOT NULL additions on existing columns, and confirm each one is safe at your current row counts. That review takes a minute and is the cheapest insurance available.