A SQL-first data layer that detects suspicious transaction patterns using pure PostgreSQL — window functions, self-joins, and composite scoring — with no ML involved. This mirrors how a large amount of real-world fraud detection actually works in production: rule-based logic on top of well-modeled data, before any model ever gets involved.
Most "SQL portfolio projects" are a handful of SELECT + GROUP BY queries against a flat table. This project instead focuses on what a data/ML engineer actually does before any model touches the data: model a proper relational schema, write production-style detection logic using window functions, and measure and optimize query performance with real numbers — not just claims.
Four tables, modeled as a small relational schema rather than one denormalized dump:
- accounts — customer records, with a
home_citybaseline used for travel-anomaly detection - devices — one account can have multiple devices;
first_seen_atpowers the new-device detection rule - merchants — transaction counterparties, with category
- transactions — the fact table; references account, device, and merchant, with amount, timestamp, and city
Indexes on transactions(account_id), transactions(transaction_time), and a composite transactions(account_id, transaction_time) support the window functions used throughout the detection queries (see Optimization below for why the composite index matters).
Since real transaction data isn't available, a Python script (scripts/generate_fake_data.py) generates a realistic dataset using faker:
- 500 accounts, 50 merchants, ~1,200 devices
- ~49,500 normal transactions with realistic spending patterns (most transactions in the account's home city, normal amount ranges)
- 63 deliberately seeded fraud-pattern transactions across 15 accounts, covering all four detection patterns below — so the detection queries have real, known-answer signals to catch
Uses batch inserts (psycopg2.extras.execute_values) rather than row-by-row inserts, since generating tens of thousands of rows one insert at a time over a network connection to a cloud database is impractically slow.
Each rule is a standalone SQL file in sql/, using PostgreSQL window functions:
1. Velocity check (03_velocity_check.sql)
Flags accounts with more than 5 transactions within any rolling 10-minute window, using COUNT(*) OVER (PARTITION BY account_id ORDER BY transaction_time RANGE BETWEEN INTERVAL '10 minutes' PRECEDING AND CURRENT ROW).
2. Amount deviation (04_amount_deviation.sql)
Flags transactions more than 3 standard deviations above an account's own historical average, calculated against only that account's prior transactions (ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING). Requires at least 10 prior transactions before flagging — an early version without this guardrail produced 133 false positives from unstable statistics on accounts with very little transaction history; adding the minimum-history requirement cut that down to a handful of genuine outliers.
3. Impossible travel (05_impossible_travel.sql)
Uses LEAD() to compare each transaction to the account's next transaction, flagging cases where the city changes within an implausibly short time window. Tuned to under 15 minutes (down from an initial 60-minute threshold that produced too much noise from random city-timestamp coincidences in synthetic data) and excludes trivial purchases under a small amount threshold.
4. New device + large transaction (06_new_device_large_txn.sql)
Joins transactions to devices, flagging large transactions (over a threshold) made on a device seen for the first time within the last 48 hours — a common account-takeover signal.
07_risk_score_view.sql combines all four rules into a single risk_scores view — one row per flagged transaction, with individual flag columns and a weighted composite score (amount deviation and impossible travel weighted highest as the strongest individual signals). This is the shape a real risk-ops review queue would consume: not four separate query results, but one ranked, actionable list.
08_optimization.sql documents a genuine before/after optimization test on the impossible-travel query, using EXPLAIN ANALYZE:
| Execution Time | Plan | |
|---|---|---|
| Without composite index | 112.805 ms | Incremental Sort |
With idx_transactions_account_time |
49.944 ms | Index Scan |
~2.3x speedup from a single composite index on (account_id, transaction_time) — the exact columns every detection query partitions and orders by. This was measured as a controlled before/after test (index dropped, query re-run, index re-added, query re-run again) rather than a one-off number, to keep the comparison honest.
PostgreSQL (hosted on Neon), Python (faker, psycopg2) for synthetic data generation, DBeaver for query development and EXPLAIN ANALYZE profiling.
sql-fraud-detection-engine/
├── sql/
│ ├── 01_schema.sql
│ ├── 03_velocity_check.sql
│ ├── 04_amount_deviation.sql
│ ├── 05_impossible_travel.sql
│ ├── 06_new_device_large_txn.sql
│ ├── 07_risk_score_view.sql
│ └── 08_optimization.sql
└── scripts/
└── generate_fake_data.py