[CLOSED][Bug?] row_already_exists (409) after manual deletion, stale unique index?

Hi, I'm experiencing a confusing issue with Appwrite Cloud

Setup: A tournaments table with a composite UNIQUE index on (tournament_name, date).

Steps to reproduce:

Delete a row manually via the Console Verify deletion: getRow(deletedId) → 404 ✅ listRows by tournament_name + date → 0 results ✅

Try to createRow with the same tournament_name + date values → 409 row_already_exists: "Row with the requested ID <OLD_ID> already exists."

The error references the old deleted row's ID, which proves the unique index still holds a stale reference to it.

Is there a known TTL or async cleanup delay on unique index entries after a row is deleted via the Console? Is this a known limitation? Is the behavior different when deleting via SDK vs Console? Is there a way to force-clear a stale index entry (other than trying to deleteRow the ghost ID and hoping it flushes the index)?

I've seen nothing about it in the docs

Thanks

TL;DR

There was a bug with stale unique index causing a 409 error even after manual deletion. The fix is to attempt to delete the 'Ghost ID' mentioned in the error. This should force the worker to re-verify and flush the index entry. The issue might be due to delays in the database cleanup process. The user ended up resolving the problem by removing the index.

Hi 👋 , This definitely sounds like a Ghost Index issue. I'm on the Appwrite Quality Team, and what you're describing—where a 404 exists for the ID but a 409 exists for the unique constraint—usually points to a delay in the Index Bloom Filter or the underlying database's cleanup process.

To answer your specific questions:

TTL/Async Delay: While there isn't a documented TTL, Appwrite Cloud uses a distributed architecture. Occasionally, the deletion of the document and the purging of the unique index entry can desync if the 'Cleanup' worker is under high load.

Console vs. SDK: Under the hood, they use the same API, but the Console sometimes triggers a 'soft' view update before the backend has fully finished the index purge.

The Fix: Usually, attempting to delete the Ghost ID (the one mentioned in the 409 error) via the SDK can sometimes force the worker to re-verify and flush that index entry.

I am escalating this to our Database team to see if there’s a specific lag currently affecting unique index purges on Cloud. Can you let me know which Region your project is in (e.g., Frankfurt, New York)?

Thanks you so much for your very clear answer 😄 i appreciate and better understand my issue

I'm in Frankfurt région ✌️ (french developper in France area)

I will retry tonight, if the clean up is done on the index

So, Bonjour

Bonjour 🙂

If this problem is solved kindly changed the title to the [CLOSED] to let everyone know that this problem is solved

Like this [CLOSED][Bug?] row_already_exists (409) after manual deletion, stale unique index?

it's not solved, i continue to get the 409, the index is stale =/

i investigate on it

ok fixe it, i've also another index that i didn't see that block so a very missleading error, because my index is based on name,slug,tag so I would have expected an error that mentions my index not a generic 409 error. My index have the name: key_index_uniqueness

The appwrite error i have: [code=409 type=row_already_exists]: Row with the requested ID '69cd2585001c6a345124' already exists. Try again with a different ID or use ID.unique() to generate a unique ID.