Fraud Pattern Detector

Project roadmap

1. 01

   Design the detector

   ~2h

   Write DESIGN.md: the transactions table (id, card, customer, merchant, amount, currency, time, city with latitude and longitude, channel), each rule with its exact definition and thresholds (stored in a rules table so they can be tuned), the alerts table (transaction, rule, score and the evidence as JSON), how rule scores combine into low, medium and high risk, and how new transactions are processed in batches. Done when: DESIGN.md defines every rule precisely with an example.

2. 02

   Generate realistic data

   ~2h

   Generate a few months of transactions for 2,000 cards with realistic habits (home city, usual merchants and amounts, more activity on weekends) using PL/pgSQL. Then inject labeled fraud patterns into a separate labels table: bursts of transactions, repeated charges at one merchant, unusually large amounts and impossible travel. Done when: the data loads, and the labels table lists every injected fraud case.

3. 03

   Write the velocity and merchant rules

   ~2h

   Write the velocity rule with COUNT(*) OVER (PARTITION BY card ORDER BY time RANGE BETWEEN INTERVAL '10 minutes' PRECEDING AND CURRENT ROW), and the repeated merchant rule counting charges to the same merchant within an hour. Each rule is a function that returns flagged transactions with evidence (for example the count and the time window). Done when: both rules find the injected bursts and repeated charges.

4. 04

   Write the amount and travel rules

   ~2.5h

   Flag amounts far above the card's normal spending (for example more than 4 standard deviations above its average over the previous 90 days, only using earlier transactions). For impossible travel, compare each transaction with the card's previous one (LAG), compute the distance with the haversine formula, and flag speeds above 900 km/h. Add indexes for these queries. Done when: both rules find the injected cases without flagging normal behavior.

5. 05

   Score and process in batches

   ~2h

   Write process_new_transactions() that takes unprocessed transactions (locking them with FOR UPDATE SKIP LOCKED so two runs never overlap), runs all rules, writes alerts with evidence, computes each transaction's risk level, and marks them processed, all in one transaction. Running it twice must not create duplicate alerts. Done when: processing a new batch creates alerts only for new transactions.

6. 06

   Measure how well it works

   ~1.5h

   Compare alerts with the labels: for each rule and overall, calculate precision (how many alerts were real fraud) and recall (how much fraud was caught). Tune the thresholds in the rules table and record the before and after numbers in README.md, explaining the trade-off between catching more fraud and raising more false alarms. Done when: README.md shows precision and recall per rule.

7. 07

   Test with psql

   ~1.5h

   Write test.sql that, in a transaction, inserts hand-made transactions for each rule's edge cases (exactly at the threshold, just over, and a first transaction with no history), runs the processing, checks the alerts and that a second run adds nothing, then rolls back. Done when: psql -f test.sql passes.

Resources

• DocsPostgreSQL: window functions
• DocsPostgreSQL: PL/pgSQL
• ArticleHaversine formula (Wikipedia)
• ArticlePrecision and recall (Wikipedia)
• Docspsql documentation