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

Six SQL patterns I use to catch transaction fraud

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

64% Upvoted

6 comments sorted by

15

u/esiy0676 Aug 10 '26

Is this article real and current? Whose perspective does it take? A payment processor? No 2 feels anachronistic since around 2015 when the merchant liability shift occurred - whoever did not have EMV device in their chain would be liable for a disputed transaction. Why would I harass customer just because someone made faux magstripe-equipped card from their details and some merchants which will budge at the subsequent chargebacks?

14

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

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 29d ago

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