Prisma Migration keeps re-creating the same index #29202
Description
Bug description
I have these indexes on a model:
@@id([id, timestamp])
@@index([tenantId], map: "idx_audit_tenant")
@@index([userId], map: "idx_audit_user")
@@index([eventType], map: "idx_audit_event_type")
@@index([eventCategory], map: "idx_audit_event_category")
@@index([module, resource], map: "idx_audit_module_resource")
@@index([resourceId], map: "idx_audit_resource_id", where: raw("\"resourceId\" IS NOT NULL"))
@@index([userId, timestamp(sort: Desc)], map: "idx_audit_user_timestamp", where: raw("\"userId\" IS NOT NULL"))
@@index([tenantId, timestamp(sort: Desc)], map: "idx_audit_tenant_timestamp", where: raw("'\"tenantId\"' IS NOT NULL"))
// problematic index, it's being recreated on each migration
// @@index([result, timestamp], map: "idx_audit_result_failures", where: raw("\"result\"<>'success'"))
// BRIN index on timestamp
@@index([timestamp(ops: TimestampTzMinMaxOps)], map: "idx_audit_timestamp_brin", type: Brin)
@@map("audit_logs")
The problematic index is this one:
@@index([result, timestamp], map: "idx_audit_result_failures", where: raw("\"result\"<>'success'"))
(others aren't causing any problems),
the problem occuring is the migration cant seem to detect that it already created this specific index, so everytime a migration is made, the generated migration always has to drop the index (that it created itself before) and recreate it, here's the result migration file every time:
DROP INDEX "idx_audit_result_failures";
-- CreateIndex
CREATE INDEX "idx_audit_result_failures" ON "audit_logs"("result", "timestamp") WHERE ("result" <>'success');
Severity
🔹 Minor: Unexpected behavior, but does not block development
Reproduction
The steps should be clear from the bug description.
Expected vs. Actual Behavior
Expected behavior is to create the index once in the migration, and not recreate unless explicitly dropped.
Actual behavior is re-creating the index on every migration even if untouched.
Frequency
Consistently reproducible
Does this occur in development or production?
Only in development (e.g., CLI tools, migrations, Prisma Studio)
Is this a regression?
Partial indexes were only introduced in v7.4.
Workaround
"No workaround found"
Prisma Schema & Queries
generator client {
provider = "prisma-client"
previewFeatures = ["partialIndexes"]
output = "../generated"
moduleFormat = "cjs"
}
datasource db {
provider = "postgresql"
}
model AuditLog {
id String @default(uuid())
timestamp DateTime @default(now()) @db.Timestamptz(3)
// Event classification
eventType String // e.g., 'auth.login.success', 'user.role.change'
eventCategory String // e.g., 'authentication', 'user_management', 'access'
// Actor info (captured at event time, not FK - survives user deletion)
userId String?
userEmail String?
userName String? @db.VarChar(255)
userRoleId String?
userRoleName String? @db.VarChar(100)
// Tenant context (captured at event time)
tenantId Int?
tenantName String? @db.VarChar(255)
// Request context
ipAddress String? @db.VarChar(45)
userAgent String? @db.VarChar(500)
requestId String? @db.VarChar(36)
// Resource details
module String // e.g., 'auth', 'users', 'sales'
resource String // e.g., 'user', 'invoice', 'role'
resourceId String?
action String // e.g., 'login', 'create', 'update', 'delete'
// Change tracking
oldValues Json?
newValues Json?
changedFields String[] @default([])
// Result/outcome
result String @default("success") // 'success', 'failure', 'error'
errorMessage String? @db.VarChar(1000)
errorCode String? @db.VarChar(50)
metadata Json?
@@id([id, timestamp])
@@index([tenantId], map: "idx_audit_tenant")
@@index([userId], map: "idx_audit_user")
@@index([eventType], map: "idx_audit_event_type")
@@index([eventCategory], map: "idx_audit_event_category")
@@index([module, resource], map: "idx_audit_module_resource")
@@index([resourceId], map: "idx_audit_resource_id", where: raw("\"resourceId\" IS NOT NULL"))
// problematic index, it's being recreated on each migration
// @@index([result, timestamp], map: "idx_audit_result_failures", where: raw("\"result\"<>'success'"))
@@index([userId, timestamp(sort: Desc)], map: "idx_audit_user_timestamp", where: raw("\"userId\" IS NOT NULL"))
@@index([tenantId, timestamp(sort: Desc)], map: "idx_audit_tenant_timestamp", where: raw("'\"tenantId\"' IS NOT NULL"))
// BRIN index on timestamp
@@index([timestamp(ops: TimestampTzMinMaxOps)], map: "idx_audit_timestamp_brin", type: Brin)
@@map("audit_logs")
}
Prisma Config
import 'dotenv/config';
import { defineConfig, env } from 'prisma/config';
export default defineConfig({
schema: 'prisma/schema',
migrations: {
path: 'prisma/migrations',
seed: 'tsx prisma/seed.ts',
},
datasource: {
url: env('DATABASE_URL'),
},
});Logs & Debug Info
Environment & Setup
- OS: ubuntu
- Database: PostgreSQL
- Node.js version: node:22-alpine
Prisma Version
7.4