Six AI fraud detection patterns for fintech that SQL rules miss

AI fraud detection in fintech

Fraud detection in transaction data is mostly SQL. Velocity checks, impossible travel, amount thresholds, merchant baselines, off-hours rules. These catch the fraud shapes someone has already seen and encoded into a query. Fixel Smith has a great post on exactly this — six SQL patterns that work.

But fraud rings adapt. Card-testing bots randomize amounts and timing. Account takeovers use valid credentials. Synthetic identities build profiles over months. SQL rules, being static and explicit, miss these by design. You can only write a rule for a pattern you already know.

AI and machine learning catch the patterns you did not think to look for. Not as a replacement for SQL, but as a layer on top. This post covers six patterns we use at Lightrains, in production fintech deployments, that catch what SQL rules leave uncovered. They work alongside traditional SQL signals, not instead of them.

1. Adaptive velocity with rolling statistics

A standard velocity rule flags any card with more than 10 transactions in an hour. That works for most users. But it drowns you in false positives for vending machine operators, prepaid card reloaders, and small businesses running payroll. And fraudsters who know the threshold will stay under it.

The fix is per-user adaptive baselines. Instead of one threshold for everyone, compute each user’s rolling mean and standard deviation of transaction count per hour. Flag deviations beyond 3 sigma. It sounds simple. It catches fraud that static rules miss by half an hour.

import pandas as pd
import numpy as np

def score_velocity_anomaly(tx, user_col='user_id', ts_col='timestamp', window='1h'):
    tx = tx.sort_values([user_col, ts_col])
    hourly_counts = (
        tx.groupby([user_col, pd.Grouper(key=ts_col, freq=window)])
        .size()
        .reset_index(name='tx_count')
    )
    stats = (
        hourly_counts.groupby(user_col)['tx_count']
        .expanding()
        .agg(['mean', 'std'])
        .shift(1)
    )
    stats.index = stats.index.droplevel(0)
    hourly_counts['z_score'] = (
        (hourly_counts['tx_count'] - stats['mean']) / stats['std'].clip(lower=1e-6)
    )
    return hourly_counts[hourly_counts['z_score'] > 3]

This catches a fraudster who does 8 transactions in an hour when the legitimate user averages 2. It also lets the payroll processor who does 200 legitimate transactions per hour pass through unflagged.

The catch: new users have no history. Global thresholds for the first 7-14 days. And seasonal businesses need a longer window to establish baselines before the model is useful.

2. Graph-based fraud ring detection

SQL joins follow one path: user to transaction, transaction to merchant. Fraud rings do not. A ring of 50 accounts sharing 3 devices and 2 IP addresses, all transacting at the same set of merchants, is invisible to SQL without recursive CTEs that most warehouses cannot optimize.

The graph approach builds a bipartite graph of accounts and shared artifacts (device fingerprints, IPs, phone numbers, shipping addresses), then finds connected components.

import networkx as nx

def find_fraud_rings(records):
    G = nx.Graph()
    for record in records:
        account = f"account:{record['account_id']}"
        for artifact_type in ['device_id', 'ip', 'phone', 'shipping_address']:
            value = record.get(artifact_type)
            if value:
                artifact_node = f"{artifact_type}:{value}"
                G.add_edge(account, artifact_node)

    rings = list(nx.connected_components(G))
    rings = [r for r in rings
             if sum(1 for n in r if n.startswith('account:')) > 2]
    return rings

Any component with more than 2 accounts is worth investigating. Components with 10+ accounts sharing a single device fingerprint are almost certainly a ring.

Limit: graph construction is expensive at scale. You need a streaming graph framework or at least batch processing every 15-30 minutes. Shared devices can be legitimate (family plans, shared kiosks). Combine with velocity signals before you block anyone.

3. Behavioral sequence anomaly detection

Account takeover is the hardest fraud pattern for SQL. The credentials are valid. Login comes from a reasonable IP. The first transaction looks normal. By the third transaction, the pattern has already diverged from the legitimate user’s historical sequence.

Embed each transaction into a feature vector (amount, merchant category, time since last tx, device change, location change), then score the sequence against a global anomaly detection model.

from sklearn.ensemble import IsolationForest
import numpy as np

def encode_features(raw_tx, user_history):
    features = []
    for tx in raw_tx:
        fv = [
            tx['amount'],
            (tx['timestamp'] - user_history['last_ts']).total_seconds() / 3600,
            1 if tx['merchant_category'] != user_history['last_category'] else 0,
            1 if tx['device_id'] != user_history['last_device'] else 0,
        ]
        features.append(fv)
        user_history = {
            'last_ts': tx['timestamp'],
            'last_category': tx['merchant_category'],
            'last_device': tx['device_id'],
        }
    return np.array(features)

all_sequences = encode_features(all_tx, initial_histories)
model = IsolationForest(contamination=0.01)
model.fit(all_sequences)

Inference is a forward pass through the model: < 50ms per transaction. Compute user-specific thresholds by binning the model scores on each user’s recent transaction history.

Reality check: sequence models need history. Users with fewer than 20 prior transactions have unreliable baselines. Fall back to rules and graph signals. And retrain your models at least weekly — adversarial drift happens faster than most teams expect.

4. Amount distribution modeling

The standard amount pattern catches round-dollar amounts and values just below thresholds. Real signals. But once a fraud ring knows you flag $99.99, they start running $97.42 instead. Static thresholds have a half-life.

Model the per-user amount distribution using kernel density estimation. Flag amounts in the lowest percentile of the user’s historical density. The KDE catches both the $1.00 card test and the $97.42 that would slide past a fixed rule.

from sklearn.neighbors import KernelDensity

def fit_amount_kde(user_amounts):
    kde = KernelDensity(kernel='gaussian', bandwidth=0.5)
    kde.fit(np.log1p(user_amounts).reshape(-1, 1))
    return kde

def score_amount_anomaly(amount, user_amounts, kde, percentile=1):
    log_amount = np.log1p(amount)
    density = np.exp(kde.score_samples([[log_amount]]))[0]
    all_densities = np.exp(kde.score_samples(np.log1p(user_amounts).reshape(-1, 1)))
    threshold = np.percentile(all_densities, percentile)
    return density < threshold

Practical note: KDE is per-user, so cold start applies. We cluster new users by onboarding attributes (income tier, card type, region) and use a cluster-level density estimate until they accumulate 50+ transactions.

5. Merchant peer group anomaly scoring

Comparing a merchant against its own history works only for established merchants. New merchants or those with sparse history have no baseline.

Instead of historical self-comparison, cluster merchants by attributes (MCC code, average ticket size, transaction volume, geographic region) and score transactions against the merchant’s peer group.

from sklearn.cluster import KMeans
from sklearn.preprocessing import StandardScaler

merchant_features = pd.DataFrame({
    'avg_ticket': merchant_data['total_amount'] / merchant_data['tx_count'],
    'tx_volume': merchant_data['tx_count'],
    'unique_cards': merchant_data['unique_cardholders'],
    'mcc_encoded': merchant_data['mcc'].astype('category').cat.codes,
})
X_scaled = StandardScaler().fit_transform(merchant_features)
clusters = KMeans(n_clusters=20, random_state=42).fit_predict(X_scaled)

Once clustered, compute per-cluster statistics for each metric. A transaction at a merchant that deviates from its cluster’s norms gets a high anomaly score, even if the merchant itself has no history.

Watch out: merchant clustering needs daily refresh. Too few clusters and you lose discrimination. Too many and you overfit. We monitor silhouette scores weekly.

6. Streaming feature pipeline with online inference

This is not a detection pattern. It is the piping that makes patterns 1-5 work in real time instead of batch.

Every pattern above depends on features computed over sliding windows. If these features update nightly, your model catches only the longest-running fraud rings. Real-time fraud needs sub-second feature computation.

The data flow:

Transaction arrives
       |
 Kafka topic -> Feature store (Redis + PostgreSQL)
       |
 Feature vector assembled (< 5ms)
       |
 ML model inference (< 20ms)
       |
 Score + rule override -> decision engine
       |
     Approve / Decline / Review

The feature store precomputes rolling aggregates for each user and serves them via low-latency lookup. Models consume these features and return a fraud probability. A decision engine applies business logic: block if probability exceeds 0.95, review if above 0.80, approve otherwise. Human reviewers see the top contributing features for each flagged transaction.

Downside: feature stores add operational complexity. You have to manage consistency between batch and streaming paths, handle late-arriving data, monitor feature drift. Start with batch scoring and migrate to real-time only after you are confident the feature set is right.

Where to start

SQL patterns catch 60-70 percent of fraud out of the gate. They are cheap, interpretable, and should be your first layer. Layer these ML patterns on top to catch the remaining 30-40 percent that SQL rules miss by design: adaptive fraud, rings, account takeover, and subtle behavioral signals.

Score every transaction across both rule-based and ML signals. A transaction flagged by 3+ SQL rules and scoring above the 95th percentile on the ML model is almost certainly fraud. One flagged by a single signal might be an edge case.

If you are new to ML fraud detection, start with pattern 1 (adaptive velocity). It catches the most fraud with the least infrastructure. Add graph-based rings (pattern 2) next. Defer streaming infrastructure (pattern 6) until you are scoring 10,000+ transactions per day.

These patterns assume good data quality. If your transaction table has NULL device fingerprints, missing MCC codes, or inconsistent timestamps, the ML models degrade faster than SQL rules. They also assume you have labeled data for validation. If starting from scratch, use unsupervised methods (patterns 1, 2, 4, 5) and build a human review loop before attempting supervised models.

Cost is real. Pattern 6 (streaming features) especially. Start with batch scoring on a sample, estimate your compute budget, then scale.

At Lightrains, we have built fraud detection stacks like this for fintech companies — combining SQL rules with ML layers. Our AI and ML practice covers the full pipeline from feature engineering to real-time inference. If you are dealing with fraud rings, account takeover, or synthetic identity fraud that your current rules miss, that is exactly the kind of problem we work on.

For more on fintech and blockchain, read our posts on stablecoins and regulation and tokenized real-world assets.

This article originally appeared on lightrains.com