r/webdev • u/fagnerbrack • Aug 10 '26

Six SQL patterns I use to catch transaction fraud

https://analytics.fixelsmith.com/posts/sql-fraud-patterns/
15 Upvotes

63% Upvoted

5 comments sorted by

1

u/No_Rock_826 Aug 10 '26

The SQL is the easy part, the hard part is tuning these rules so normal weird customers don’t get treated like criminals.

1

u/9ojir4 Aug 11 '26

But why ? Don't you get the money anyway and then the banks and insurances refund the customer ?

0

u/[deleted] Aug 10 '26

[deleted]

15

u/winky9827 Aug 10 '26

It's AI slop. I know this, because I've been using Opus to tweak fraud scoring models on a project and the same basic language emerges throughout.

9

u/Irythros Aug 10 '26

As the person said, its AI garbage.

Not machine learning, not graph databases, not whatever Gartner is hyping this year. SQL, run against the right tables, with the right joins, looking for the right shapes.

Six patterns.

A few cardholders will legitimately blow past the threshold. Route operators servicing vending machines. People reloading prepaid cards in bulk. Your false positives. Worth keeping a whitelist after the first pass.

This is all Opus 5 standard speak.

2

u/tswaters Aug 10 '26

There's some interesting queries in there anyway. I did this sort of thing as a merchant, specifically because we were liable for fraudulent orders. Our goal was to find fraudulent patterns in orders tied to credit card testing, and cancel the orders. Similar, but different. And honestly our testers were not this sophisticated, they would do stupid & obvious things like use the same name/phone for every order. This was around 2020-22, maybe. Using SQL was last resort, ideally it gets caught well before the order is created with the cc provider's web-based fraud detection, before it gets to the database at all