The problem: you have a table of payments and a feeling that some of them are wrong. Nobody has labelled any of them as fraud, and by the time a chargeback tells you, the money has gone. You want a list of the transactions worth a second look, using the database you already have.
We build SynapCores, and one section runs the model and the ring search in it. Everything before that is plain SQL (run on SQLite) and Python with scikit-learn, so you can try it without installing anything of ours.
When this is useful
- You run a marketplace, a SaaS billing system, a wallet or a loyalty program, and you own the transaction table.
- You have no labels, or too few to train on.
- You want a ranked list for a person to review each day: refunds, payouts, sign-up bonuses, card payments.
- You already have a few rules and want to know what they miss.
The four tools, and what each one sees
┌──────────── one row at a time ────────────┐
│ │
payments ──► 1. rules 2. z-score 3. anomaly model
"amount > 1000 "how far is "how unusual is
AND 3am AND this amount this combination
abroad" from normal" of features"
│ │ │
└────────┬────────┴─────────────────┘
▼
review queue ◄── 4. shared-identifier walk
(a person decides) chargeback ─ card ─ account
└── device ─ account
(links between accounts)
Rules and z-scores are the cheapest and easiest to explain. The model catches combinations you didn't anticipate. The walk catches what no per-row method can.
The data
One small table that we use for every example: 40 accounts, three ordinary payments each ($20 to $199, daytime, domestic). Two things are planted:
- A lone fraudster.
ACC-33also makes three payments of about $3,000 at 3am, cross-border. - A ring.
ACC-11toACC-15look like everyone else in the payments table. Their link is in a second table: who uses which device and card. They share them in a chain.ACC-21andACC-22share a household tablet, which is honest.
This is the same data as our fraud-ring recipe, with the identity links stored as a plain table so that any SQL database can walk them.
CREATE TABLE learn_tx (
tx_id INT PRIMARY KEY,
account_id TEXT,
amount DOUBLE,
hour_of_day INT,
cross_border INT
);
INSERT INTO learn_tx (tx_id, account_id, amount, hour_of_day, cross_border) VALUES
(1,'ACC-01',57,14,0),(2,'ACC-01',110,17,0),(3,'ACC-01',163,9,0),(4,'ACC-02',94,19,0),(5,'ACC-02',147,11,0),(6,'ACC-02',20,14,0),
(7,'ACC-03',131,13,0),(8,'ACC-03',184,16,0),(9,'ACC-03',57,19,0),(10,'ACC-04',168,18,0),(11,'ACC-04',41,10,0),(12,'ACC-04',94,13,0),
(13,'ACC-05',25,12,0),(14,'ACC-05',78,15,0),(15,'ACC-05',131,18,0),(16,'ACC-06',62,17,0),(17,'ACC-06',115,9,0),(18,'ACC-06',168,12,0),
(19,'ACC-07',99,11,0),(20,'ACC-07',152,14,0),(21,'ACC-07',25,17,0),(22,'ACC-08',136,16,0),(23,'ACC-08',189,19,0),(24,'ACC-08',62,11,0),
(25,'ACC-09',173,10,0),(26,'ACC-09',46,13,0),(27,'ACC-09',99,16,0),(28,'ACC-10',30,15,0),(29,'ACC-10',83,18,0),(30,'ACC-10',136,10,0),
(31,'ACC-11',67,9,0),(32,'ACC-11',120,12,0),(33,'ACC-11',173,15,0),(34,'ACC-12',104,14,0),(35,'ACC-12',157,17,0),(36,'ACC-12',30,9,0),
(37,'ACC-13',141,19,0),(38,'ACC-13',194,11,0),(39,'ACC-13',67,14,0),(40,'ACC-14',178,13,0),(41,'ACC-14',51,16,0),(42,'ACC-14',104,19,0),
(43,'ACC-15',35,18,0),(44,'ACC-15',88,10,0),(45,'ACC-15',141,13,0),(46,'ACC-16',72,12,0),(47,'ACC-16',125,15,0),(48,'ACC-16',178,18,0),
(49,'ACC-17',109,17,0),(50,'ACC-17',162,9,0),(51,'ACC-17',35,12,0),(52,'ACC-18',146,11,0),(53,'ACC-18',199,14,0),(54,'ACC-18',72,17,0),
(55,'ACC-19',183,16,0),(56,'ACC-19',56,19,0),(57,'ACC-19',109,11,0),(58,'ACC-20',40,10,0),(59,'ACC-20',93,13,0),(60,'ACC-20',146,16,0),
(61,'ACC-21',77,15,0),(62,'ACC-21',130,18,0),(63,'ACC-21',183,10,0),(64,'ACC-22',114,9,0),(65,'ACC-22',167,12,0),(66,'ACC-22',40,15,0),
(67,'ACC-23',151,14,0),(68,'ACC-23',24,17,0),(69,'ACC-23',77,9,0),(70,'ACC-24',188,19,0),(71,'ACC-24',61,11,0),(72,'ACC-24',114,14,0),
(73,'ACC-25',45,13,0),(74,'ACC-25',98,16,0),(75,'ACC-25',151,19,0),(76,'ACC-26',82,18,0),(77,'ACC-26',135,10,0),(78,'ACC-26',188,13,0),
(79,'ACC-27',119,12,0),(80,'ACC-27',172,15,0),(81,'ACC-27',45,18,0),(82,'ACC-28',156,17,0),(83,'ACC-28',29,9,0),(84,'ACC-28',82,12,0),
(85,'ACC-29',193,11,0),(86,'ACC-29',66,14,0),(87,'ACC-29',119,17,0),(88,'ACC-30',50,16,0),(89,'ACC-30',103,19,0),(90,'ACC-30',156,11,0),
(91,'ACC-31',87,10,0),(92,'ACC-31',140,13,0),(93,'ACC-31',193,16,0),(94,'ACC-32',124,15,0),(95,'ACC-32',177,18,0),(96,'ACC-32',50,10,0),
(97,'ACC-33',161,9,0),(98,'ACC-33',34,12,0),(99,'ACC-33',87,15,0),(100,'ACC-34',198,14,0),(101,'ACC-34',71,17,0),(102,'ACC-34',124,9,0),
(103,'ACC-35',55,19,0),(104,'ACC-35',108,11,0),(105,'ACC-35',161,14,0),(106,'ACC-36',92,13,0),(107,'ACC-36',145,16,0),(108,'ACC-36',198,19,0),
(109,'ACC-37',129,18,0),(110,'ACC-37',182,10,0),(111,'ACC-37',55,13,0),(112,'ACC-38',166,12,0),(113,'ACC-38',39,15,0),(114,'ACC-38',92,18,0),
(115,'ACC-39',23,17,0),(116,'ACC-39',76,9,0),(117,'ACC-39',129,12,0),(118,'ACC-40',60,11,0),(119,'ACC-40',113,14,0),(120,'ACC-40',166,17,0),
(121,'ACC-33',2840,3,1),(122,'ACC-33',3120,3,1),(123,'ACC-33',2975,3,1);
-- Who uses which device or card. Most accounts have their own; a few share.
CREATE TABLE account_links (account_id TEXT, identifier TEXT);
INSERT INTO account_links (account_id, identifier) VALUES
('ACC-11','DEV-777'),('ACC-12','DEV-777'),('ACC-12','CARD-4242'),('ACC-13','CARD-4242'),
('ACC-13','DEV-778'),('ACC-14','DEV-778'),('ACC-14','CARD-9001'),('ACC-15','CARD-9001'),
('ACC-21','DEV-500'),('ACC-21','CARD-1021'),('ACC-22','DEV-500'),('ACC-22','CARD-1022'),
('ACC-33','DEV-133'),('ACC-33','CARD-1033'),('ACC-07','DEV-107'),('ACC-07','CARD-1007'),
('ACC-36','DEV-136'),('ACC-36','CARD-1036');
The plain-SQL queries below were run on SQLite 3.43.2 (macOS) and the Python on Python 3.9.6, scikit-learn 1.5.2, numpy 2.0.2, pandas 2.2.3. The outputs are pasted from those runs. The SQL is standard (CTEs, WITH RECURSIVE, SQRT), but we haven't run it on Postgres or MySQL. One known difference: in MySQL, || means OR unless PIPES_AS_CONCAT is set, so use CONCAT() there.
1. Rules: the patterns you already know
If you already know what bad looks like, write it down:
SELECT tx_id, account_id, amount, hour_of_day, cross_border
FROM learn_tx
WHERE amount > 1000
AND hour_of_day < 6
AND cross_border = 1
ORDER BY tx_id;
tx_id account_id amount hour_of_day cross_border
----- ---------- ------ ----------- ------------
121 ACC-33 2840.0 3 1
122 ACC-33 3120.0 3 1
123 ACC-33 2975.0 3 1
All three of ACC-33's payments, and nothing else. On this data, a rule is enough for the lone fraudster, and a rule has one big advantage: a reviewer or an auditor can read exactly why a row was flagged.
The cost is that a rule only finds what you wrote. A $190 payment at 3am from abroad passes amount > 1000. Anyone testing your limits can stay just under them, and each new pattern means a new rule.
2. Z-scores: how far from normal
A z-score is the number of standard deviations a value sits from the mean. You don't need to pick a threshold for the amount itself. You compute it, sort, and read from the top:
WITH stats AS (
SELECT AVG(amount) AS mean_amount,
SQRT(AVG(amount * amount) - AVG(amount) * AVG(amount)) AS sd_amount
FROM learn_tx
)
SELECT t.tx_id, t.account_id, t.amount,
ROUND((t.amount - s.mean_amount) / s.sd_amount, 2) AS z
FROM learn_tx t CROSS JOIN stats s
ORDER BY z DESC
LIMIT 5;
tx_id account_id amount z
----- ---------- ------ ----
122 ACC-33 3120.0 6.6
123 ACC-33 2975.0 6.27
121 ACC-33 2840.0 5.97
53 ACC-18 199.0 0.04
100 ACC-34 198.0 0.04
The SQRT(AVG(x*x) - AVG(x)*AVG(x)) form is the population standard deviation, written so it runs where there's no STDDEV function. In Postgres, STDDEV_POP(amount) gives the same thing.
Two things to know before you rely on it:
- Outliers inflate their own baseline. Over all 123 rows the mean is 180.0 and the standard deviation 445.8. Without
ACC-33's three payments they are 110.0 and 51.8. Three rows made "normal" nine times wider. On real data, compute the baseline from a clean past window (or use the median), not from the rows you are scoring. - It looks at one column. That $190 payment at 3am from abroad has z = 0.02 on amount. Its hour and country are what's odd, and a per-column z-score can't add them up.
3. An anomaly model: unusual combinations, no labels
An isolation forest builds many random trees that split the data on random features at random values. Unusual rows get isolated in few splits, so they get a high score. It looks at all the features together, and it needs no labels: you hand it the shape of normal traffic.
Here it is in Python with scikit-learn, reading the same SQLite table:
import sqlite3
import pandas as pd
from sklearn.ensemble import IsolationForest
con = sqlite3.connect("learn.db")
df = pd.read_sql_query("SELECT * FROM learn_tx", con)
features = ["amount", "hour_of_day", "cross_border"]
model = IsolationForest(n_estimators=100, random_state=42)
model.fit(df[features])
# score_samples is higher for normal rows; flip it so higher = more unusual
df["score"] = (-model.score_samples(df[features])).round(2)
print(df.sort_values("score", ascending=False).head(5).to_string(index=False))
ring = df[df.account_id.isin(["ACC-11", "ACC-12", "ACC-13", "ACC-14", "ACC-15"])]
print("ring rows:", len(ring), "score range:", ring.score.min(), "-", ring.score.max())
print("all other rows:", df[~df.index.isin(ring.index) & (df.account_id != "ACC-33")].score.agg(["min", "max"]).tolist())
probe = pd.DataFrame([[190, 3, 1], [2900, 14, 0], [45, 14, 0]], columns=features)
probe["score"] = (-model.score_samples(probe[features])).round(2)
print(probe.to_string(index=False))
tx_id account_id amount hour_of_day cross_border score
122 ACC-33 3120.0 3 1 0.82
121 ACC-33 2840.0 3 1 0.80
123 ACC-33 2975.0 3 1 0.78
108 ACC-36 198.0 19 0 0.60
83 ACC-28 29.0 9 0 0.57
ring rows: 15 score range: 0.43 - 0.57
all other rows: [0.43, 0.6]
amount hour_of_day cross_border score
190 3 1 0.68
2900 14 0 0.63
45 14 0 0.45
How to read it:
- The lone fraudster is on top, at 0.78 to 0.82. Every ordinary payment scores 0.43 to 0.60.
- The $190 at 3am from abroad scores 0.68: above every ordinary row, even though the rule and the amount z-score both passed it. That's what the model adds: it noticed a combination nobody wrote a rule for.
- The scores are a ranking, not a probability. 0.45 for a normal lunchtime payment is expected. Read 0.68 as "more unusual than anything ordinary in this table", not "68% fraud".
- The ring scores 0.43 to 0.57, inside the ordinary range. The model isn't wrong: each of those payments is ordinary.
4. Rings: follow the shared identifiers
A ring isn't a property of any one transaction, so no per-row method, not rules, not z-scores and not a model, can find it. The signal is in the second table: who shares a device or card with whom.
Start from what you know. ACC-13 has a confirmed chargeback. Walk out from it: account → shared identifier → account, up to two account hops:
WITH RECURSIVE walk (account_id, path, hops) AS (
SELECT CAST('ACC-13' AS TEXT), CAST('ACC-13' AS TEXT), 0
UNION ALL
SELECT b.account_id,
w.path || ' > ' || a.identifier || ' > ' || b.account_id,
w.hops + 1
FROM walk w
JOIN account_links a ON a.account_id = w.account_id
JOIN account_links b ON b.identifier = a.identifier
AND b.account_id <> a.account_id
WHERE w.hops < 2
AND w.path NOT LIKE '%' || b.account_id || '%'
)
SELECT account_id, path, hops
FROM walk
WHERE hops > 0
ORDER BY hops, account_id;
account_id path hops
---------- ---------------------------------------------- ----
ACC-12 ACC-13 > CARD-4242 > ACC-12 1
ACC-14 ACC-13 > DEV-778 > ACC-14 1
ACC-11 ACC-13 > CARD-4242 > ACC-12 > DEV-777 > ACC-11 2
ACC-15 ACC-13 > DEV-778 > ACC-14 > CARD-9001 > ACC-15 2
Four accounts, each with the chain that links it to the chargeback. The household pair, ACC-21 and ACC-22, isn't returned. Sharing a device isn't fraud. Being connected to a confirmed case is worth a look.
This works in plain SQL, and for one or two hops over one link table it's fine. It gets harder as the question grows: more identifier types (addresses, IPs, bank accounts), more hops, and the NOT LIKE cycle check on a growing text path. That's the point where a graph query is easier to write and read.
How the four compare
| Rules | Z-score | Isolation forest (Python) | Shared-identifier walk | |
|---|---|---|---|---|
| Needs fraud labels | No | No | No | One confirmed case to start from |
| Found the lone fraudster here | Yes | Yes | Yes | No (not linked to the chargeback) |
| Flagged the $190 / 3am / abroad probe | No | No (z = 0.02) | Yes (0.68 vs ≤ 0.60) | No |
| Found the ring here | No | No | No | Yes, 4 of 4 |
| Why a row was flagged | Exact rule | Distance on one column | A score; harder to explain | The path |
| Where it runs | In the database | In the database | Export rows to Python, score, write back | In the database |
When rules are enough: you know the patterns, there are few of them, and you must explain every flag. Many teams never need more than rules plus a z-score.
When Python is enough: you already have a feature pipeline and a scheduled job, a nightly batch is fresh enough, and you want scikit-learn's full set of algorithms and tuning. Nothing on this page needs a different database.
What the in-database version changes is where the score lives, not what it can find. The next section runs the same isolation forest inside SynapCores, so the score is an expression in a SELECT and goes straight into WHERE, ORDER BY and joins, with no export job. We have not measured whether it scores better or faster than scikit-learn, and this page makes no claim that it does.