Records found that don't exist

I've got a very wierd bug I can't figure out. I've deleted all my records in Supabase for table upvote, but I am somehow able to find a non-existant record and delete it!

When adding an upvote, I'm checking for a record. If one exists already, I delete it, if not I create one. Even after clearing the table, it is able to find an existing record and try to delete it (even with a success message) although no record should even exist.

I'm baffled.

On top of this, I have bizarre random 500 internal server error when I create or delete a record (same method as above). It works most of the time, but sometimes it fails on either create or delete. It is NOT due to a race condition as I've already troubleshot that possibility out.

This is a simple CRUD operation and I don't understand where the issue is. Perhaps something with Supabase serving old records? I've tried to wait awhile after clearing the table, but that didn't fix the issue of deleting records that don't exist.

I've restarted the server after clearing the records and I have no local data that is conflicting with this (as I'm querying the table directly).

The first attached image shows it finding a record, but I assure you my table is empty! The second image shows my current methods, in case I'm doing something wrong.

Help with either of the strange above issues would be very helpful.

I'M USING:

    "prisma": "5.17.0",
    "@prisma/client": "5.17.0",
    "@prisma/extension-accelerate": "^1.1.0",
    "nuxt": "^3.13.0",
    "@nuxtjs/supabase": "^1.3.5",

CODE:

  const existingUpvote = await prisma.upvote.findFirst({
    where: {
      suggestion_id: suggestion_id,
      upvoter_id: userId
    }
  })
  console.log('**********************************')
  console.log('existingUpvote', existingUpvote)

  if (existingUpvote?.id) {
    // If the upvote exists, remove it (un-upvote)

    console.log('**********************************')
    console.log('deleting upvote')
    console.log('existingUpvote', existingUpvote)
    const removedUpvote = await prisma.upvote.delete({
      where: {
        id: existingUpvote.id
      }
    })
    return { message: 'Upvote removed', upvote: removedUpvote, removed: true }
  } else {
    // If no upvote exists, create one (upvote)
    console.log('**********************************')
    console.log('creating new upvote')
    console.log('suggestion_id', suggestion_id)
    console.log('userId', userId)
    const newUpvote = await prisma.upvote.create({
      data: {
        suggestion_id: suggestion_id!,
        upvoter_id: userId
      }
    })
    return { message: 'Upvoted', upvote: newUpvote, removed: false }
  }

SOLVED:

#1 I realized the issue was caused by multiple fetches, which was triggered by useFetch on a ~.client.vue component. Switching to $fetch solved this.

#2 To solve the issue of collisions (random 500 errors) I've added the following function and wrapped it around the start of my transaction:

function isRetryableError(error: any) {
  return error.message.includes(
    'Transaction failed due to a write conflict or a deadlock'
  )
}

async function retryTransaction(
  fn: (prisma: PrismaClient) => Promise<any>,
  maxRetries = 3
) {
  let attempt = 0
  while (attempt < maxRetries) {
    try {
      return await prisma.$transaction(async (client) => {
        return await fn(client as any)
      })
    } catch (error: any) {
      attempt++
      if (attempt >= maxRetries || !isRetryableError(error)) {
        throw error
      }
      // Optional: Exponential backoff
      await new Promise((resolve) =>
        setTimeout(resolve, 100 * Math.pow(2, attempt))
      )
    }
  }
}

export default defineEventHandler(async (event) => {
  const requestData = await readBody(event) // payload
  const suggestion_id = event.context.params?.suggestion_id // path param

  const userId = requestData.userId