Prisma Migrations for AI-Powered Databases
Краткое содержание
Pixel shares guidance on managing Prisma migrations as AI features add conversations, messages, tool calls, and feedback to a database. The post recommends immutable, append-only message records, an index on conversation and creation time, and safe expand-and-contract changes to avoid locking large tables and disrupting the product.
DEV Community
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: Expand. Add contentJson as a nullable column. Nullable additions do not rewrite the table. Dual-write. Deploy code that writes both columns and reads the old one. Backfill. Populate contentJson for existing rows in batches. Switch reads. Deploy code that reads the new column, still writing both. 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 d...

Powered by Readybot.io