I am building a service where users can make new requests. Nothing is stopping a user from submitting the same request multiple times. So after receiving a new request, I am checking to see if it is a duplicate. If so, I want to mark only the duplicate request as inactive. I am using Supabase as my DB.
What is the error message (if any)?
Please share your workflow
The relevant part of my flow is (not sharing actual flow due to confidentiality concerns):
Supabase: Get Many Rows
Summarize (Count) Id field returned in 1)
If Count > 1 (checking to see if multiple rows have overlapping data to consider the entries as duplicates.
I am trying to figure out how to mark all but one of the duplicates as inactive. How would you go about doing that?
getting rows and checking is not scalable as you may face memory issues.
what you can do is make a hash of incoming request ley say using sha-1 algorithm. then check database if that hash exist and return true or false. and you can apply if condition on it.
example sql query: SELECT EXISTS (SELECT 1 FROM table_name WHERE id = :hash) AS hash_exists;
Thanks, yeah, I know it would be preferably to avoid duplicates in the first place rather than try to remove them later. That would just require more effort in view of the logic I already have in place. But you’re not wrong…
The best way to handle this issue is to enforce uniqueness on the database level - with UNIQUE constraints or with INDEXing. An attempt to insert new data which is not unique should be an illegal operation.
If enforcing uniqueness is not an option (and I honestly think that efforts to dance around the problem instead of fixing only works in a short run and will eventually bite you in a rear), then searching in the database should be done on indexed data only.
You could integrate your logic inside the BEFORE INSERT trigger, which would try to find duplicates before the new data is inserted. This way you can mark all found as “inactive” or “duplicate” and then insert the new one as “active”.
So I’m switching to the “avoid duplicates in the first place” method, but that is introducing its own issues. I’m pretty new to n8n, but I have experience with Decisions.com (a similar platform, although MUCH more epensive). The issue is, I first query the Supabase database for rows that match the newly requested item. If it returns none, then I know there are no dups, but how do I act on that? I was trying a “If” node but there is no data output by my Supabase “Get Rows” call, so I have nothing to work with. Any thoughts on that one?
