Bug: clerkUserId violates UNIQUE constraint when deleting multiple users

[kyundos]

Description
When deleting a user, the clerkUserId field is set to "deleted". However, since the database schema enforces a UNIQUE constraint on clerkUserId, this causes a conflict when deleting multiple users, resulting in a database error.

Code causing the issue:

export async function deleteUser({ clerkUserId }: { clerkUserId: string }) {
  const [deletedUser] = await db
    .update(UserTable)
    .set({
      deletedAt: new Date(),
      email: "redacted@deleted.com",
      name: "Deleted User",
      clerkUserId: "deleted", // ❌ Hardcoded value causes a UNIQUE constraint violation
      imageUrl: null,
    })
    .where(eq(UserTable.clerkUserId, clerkUserId))
    .returning();

  if (deletedUser == null) throw new Error("Failed to delete user");
  revalidateUserCache(deletedUser.id);

  return deletedUser;
}

Steps to Reproduce
Delete one user → ✅ Works fine.
Delete another user → ❌ Database error: duplicate key value violates unique constraint "user_clerkuserid_key", because of:

export const UserTable = pgTable("user", {
  id,
  clerkUserId: text().notNull().unique(), ❌ Violated constraint
  email: text().notNull(),
  name: text().notNull(),
  role: userRoleEnum().notNull().default("user"),
  imageUrl: text(),
  deletedAt: timestamp({ withTimezone: true }),
  createdAt,
  updatedAt,
})

Expected Behavior
The application should allow multiple users to be deleted without violating the UNIQUE constraint.

Suggested Fix
To avoid this issue, we can modify clerkUserId to be unique when a user is deleted.
Some possible solutions:

• Append a unique identifier (fast and straightforward):
  "clerkUserId: deleted-${new Date()}"

• Use a UUID (probably more robust solution):
  "clerkUserId: deleted-${crypto.randomUUID()}"

Would love to hear your thoughts on the best approach!

[WebDevSimplified]

Good catch! I think the UUID approach is probably the best option