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.
Score and walk in one database (SynapCores)
SynapCores trains and serves the isolation forest inside the engine and has a graph engine (Cypher) on the same data. So the model score, the ring walk and the review queue live in one database, and the score is an ordinary SQL expression. Load the same two tables from the setup block above, then:
Train the model. No target_column: nothing tells the engine which rows are fraud.
CREATE EXPERIMENT learn_tx_exp AS
SELECT amount, hour_of_day, cross_border
FROM learn_tx
WITH (
task_type = 'anomaly_detection',
algorithms = ['isolation_forest'],
max_trials = 5,
random_seed = 42
);
DEPLOY MODEL learn_tx_model FROM EXPERIMENT learn_tx_exp;
CREATE EXPERIMENT → {"status":"success","experiment_name":"learn_tx_exp",
"optimization_metric":"tail_separation","best_score":0.11215449496543428,
"score_basis":"internal_validation","total_trials":4, ...}
DEPLOY MODEL → {"status":"success","model_name":"learn_tx_model",
"message":"Model 'learn_tx_model' deployed successfully from experiment 'learn_tx_exp'"}
The result reports score_basis: internal_validation. With no labels to hold out, that's a measure of how cleanly the model separated dense regions from sparse ones, not an accuracy. Don't report it as one.
Score every transaction in a SELECT. ANOMALY_SCORE takes the model name and the feature columns in training order. A different order is refused with a feature mismatch error rather than scored wrongly.
SELECT tx_id, account_id, amount, hour_of_day, cross_border,
ROUND(ANOMALY_SCORE('learn_tx_model', amount, hour_of_day, cross_border), 2) AS score
FROM learn_tx
ORDER BY score DESC
LIMIT 5;
tx_id account_id amount hour_of_day cross_border score
----- ---------- ------ ----------- ------------ -----
121 ACC-33 2840.0 3 1 0.82
122 ACC-33 3120.0 3 1 0.82
123 ACC-33 2975.0 3 1 0.81
108 ACC-36 198.0 19 0 0.53
115 ACC-39 23.0 17 0 0.52
Score the probe rows. ANOMALY_SCORE takes columns, not literals, so put the probes in a table:
CREATE TABLE learn_probe (label TEXT, amount DOUBLE, hour_of_day INT, cross_border INT);
INSERT INTO learn_probe (label, amount, hour_of_day, cross_border) VALUES
('small, 3am, abroad', 190, 3, 1),
('large, afternoon, domestic', 2900, 14, 0),
('ordinary lunch', 45, 14, 0);
SELECT label,
ROUND(ANOMALY_SCORE('learn_tx_model', amount, hour_of_day, cross_border), 2) AS score
FROM learn_probe
ORDER BY score DESC;
label score
-------------------------- -----
small, 3am, abroad 0.69
large, afternoon, domestic 0.59
ordinary lunch 0.46
Walk the ring in Cypher. Build the identity graph once. Cypher runs through the same /v1/query/execute endpoint as SQL, and the graph belongs to the database named in that request. Send the MERGE below and the walk after it to the same database you loaded the tables into. A graph built in one database isn't visible from another.
MERGE (acc_11:Account {id: "ACC-11", src: "learn-a04"})
MERGE (dev_777:Device {id: "DEV-777", src: "learn-a04"})
MERGE (acc_12:Account {id: "ACC-12", src: "learn-a04"})
MERGE (card_4242:Card {id: "CARD-4242", src: "learn-a04"})
MERGE (acc_13:Account {id: "ACC-13", src: "learn-a04"})
MERGE (dev_778:Device {id: "DEV-778", src: "learn-a04"})
MERGE (acc_14:Account {id: "ACC-14", src: "learn-a04"})
MERGE (card_9001:Card {id: "CARD-9001", src: "learn-a04"})
MERGE (acc_15:Account {id: "ACC-15", src: "learn-a04"})
MERGE (acc_21:Account {id: "ACC-21", src: "learn-a04"})
MERGE (dev_500:Device {id: "DEV-500", src: "learn-a04"})
MERGE (card_1021:Card {id: "CARD-1021", src: "learn-a04"})
MERGE (acc_22:Account {id: "ACC-22", src: "learn-a04"})
MERGE (card_1022:Card {id: "CARD-1022", src: "learn-a04"})
MERGE (acc_33:Account {id: "ACC-33", src: "learn-a04"})
MERGE (dev_133:Device {id: "DEV-133", src: "learn-a04"})
MERGE (card_1033:Card {id: "CARD-1033", src: "learn-a04"})
MERGE (acc_07:Account {id: "ACC-07", src: "learn-a04"})
MERGE (dev_107:Device {id: "DEV-107", src: "learn-a04"})
MERGE (card_1007:Card {id: "CARD-1007", src: "learn-a04"})
MERGE (acc_36:Account {id: "ACC-36", src: "learn-a04"})
MERGE (dev_136:Device {id: "DEV-136", src: "learn-a04"})
MERGE (card_1036:Card {id: "CARD-1036", src: "learn-a04"})
MERGE (acc_11)-[:USES]->(dev_777)
MERGE (acc_12)-[:USES]->(dev_777)
MERGE (acc_12)-[:PAYS_WITH]->(card_4242)
MERGE (acc_13)-[:PAYS_WITH]->(card_4242)
MERGE (acc_13)-[:USES]->(dev_778)
MERGE (acc_14)-[:USES]->(dev_778)
MERGE (acc_14)-[:PAYS_WITH]->(card_9001)
MERGE (acc_15)-[:PAYS_WITH]->(card_9001)
MERGE (acc_21)-[:USES]->(dev_500)
MERGE (acc_21)-[:PAYS_WITH]->(card_1021)
MERGE (acc_22)-[:USES]->(dev_500)
MERGE (acc_22)-[:PAYS_WITH]->(card_1022)
MERGE (acc_33)-[:USES]->(dev_133)
MERGE (acc_33)-[:PAYS_WITH]->(card_1033)
MERGE (acc_07)-[:USES]->(dev_107)
MERGE (acc_07)-[:PAYS_WITH]->(card_1007)
MERGE (acc_36)-[:USES]->(dev_136)
MERGE (acc_36)-[:PAYS_WITH]->(card_1036);
Then the same walk as the recursive CTE, as one pattern: any path of two to four relationships from the chargeback account to another account.
MATCH p = (s:Account {id: 'ACC-13'})-[*2..4]-(x:Account)
WHERE x.id <> 'ACC-13'
RETURN x.id AS linked_account, [n IN nodes(p) | n.id] AS via
ORDER BY length(p);
linked_account via
-------------- ----------------------------------------------------
ACC-14 ["ACC-13", "DEV-778", "ACC-14"]
ACC-12 ["ACC-13", "CARD-4242", "ACC-12"]
ACC-15 ["ACC-13", "DEV-778", "ACC-14", "CARD-9001", "ACC-15"]
ACC-11 ["ACC-13", "CARD-4242", "ACC-12", "DEV-777", "ACC-11"]
Adding an identifier type is one more relationship in the data ((:Account)-[:SHIPS_TO]->(:Address)), and the query doesn't change. In the recursive CTE, it's another link table or another UNION.
One review queue: model and graph together. On 2.3.2 you can't join a Cypher result in SQL directly: a MATCH isn't accepted as a subquery, a CTE or a table function. There are two ways that work.
Option 1: save the walk's result to a table. Copy the four linked accounts (plus the chargeback account) into learn_ring, then keep every account the model or the graph flagged. To aggregate the score per account, compute it in a subquery first.
CREATE TABLE learn_ring (account_id TEXT PRIMARY KEY, linked_via TEXT);
INSERT INTO learn_ring (account_id, linked_via) VALUES
('ACC-13', 'chargeback'),
('ACC-12', 'CARD-4242'),
('ACC-14', 'DEV-778'),
('ACC-11', 'CARD-4242 > DEV-777'),
('ACC-15', 'DEV-778 > CARD-9001');
SELECT s.account_id, ROUND(s.max_anomaly, 2) AS max_anomaly, r.linked_via
FROM (
SELECT account_id, MAX(score) AS max_anomaly
FROM (
SELECT account_id,
ANOMALY_SCORE('learn_tx_model', amount, hour_of_day, cross_border) AS score
FROM learn_tx
) t
GROUP BY account_id
) s
LEFT JOIN learn_ring r ON r.account_id = s.account_id
WHERE s.max_anomaly > 0.7 OR r.account_id IS NOT NULL
ORDER BY s.max_anomaly DESC;
account_id max_anomaly linked_via
---------- ----------- -------------------
ACC-33 0.82 NULL
ACC-15 0.51 DEV-778 > CARD-9001
ACC-12 0.51 CARD-4242
ACC-13 0.49 chargeback
ACC-14 0.48 DEV-778
ACC-11 0.48 CARD-4242 > DEV-777
Look at the max_anomaly column: five of the six accounts would never cross a 0.7 threshold. The model found one fraudster and the graph found five. The 0.7 cut-off is a choice for this small example. On your data, set it from how many cases your team can review in a day.
Here the rows are typed in by hand so the steps stay visible. In an application, write the Cypher result into learn_ring from your code, and refresh it when a new chargeback arrives.
Option 2: walk in SQL and score in the same statement. SynapCores runs the recursive CTE from section 4 unchanged (same four rows as SQLite), and ANOMALY_SCORE can sit in the same query. No separate table:
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 w.account_id, w.hops,
ROUND(MAX(ANOMALY_SCORE('learn_tx_model', t.amount, t.hour_of_day, t.cross_border)), 2) AS max_anomaly
FROM walk w JOIN learn_tx t ON t.account_id = w.account_id
GROUP BY w.account_id, w.hops
ORDER BY w.hops, w.account_id;
account_id hops max_anomaly
---------- ---- -----------
ACC-13 0 0.49
ACC-12 1 0.51
ACC-14 1 0.48
ACC-11 2 0.48
ACC-15 2 0.51
The same five accounts and scores as the queue above. Pick option 1 when the graph grows past what a link table walks comfortably. Pick option 2 when one link table and a couple of hops are enough.
Clean up when you're done:
DROP TABLE IF EXISTS learn_probe;
DROP TABLE IF EXISTS learn_ring;
DROP TABLE IF EXISTS account_links;
DROP TABLE IF EXISTS learn_tx;
MATCH (n:Account {src: 'learn-a04'}) DETACH DELETE n;
MATCH (n:Device {src: 'learn-a04'}) DETACH DELETE n;
MATCH (n:Card {src: 'learn-a04'}) DETACH DELETE n;
A checklist before you ship a fraud queue
- Start with rules and a z-score. Add a model when you can name what the rules miss.
- Compute baselines from a clean past window, not from the rows you're scoring.
- Store identifiers (device, card, address, IP) per account, even before you plan to walk them.
- Rank, don't decide. A person reviews; the score orders the queue.
- Set the cut-off from review capacity, and revisit it monthly.
- Log why each row was flagged: the rule, the score, or the path.
- When chargebacks arrive, keep them. They're the labels for a supervised model later.
Where to go next
- Next: How to Predict Which Customers Will Cancel, Using SQLComing soon: the labelled, supervised side of in-database ML, and when Python is the better choice.
- Related: How to Build "Customers Who Bought This Also Bought" RecommendationsComing soon: the same graph-walk idea, used for recommendations.
- Before this: Which Technique Solves My Problem? compares rules, traditional ML, LLMs and vector search by job.
- Run it end to end: the fraud-ring recipe (about 12 minutes) and our video, Fraud Detection Machine Learning Project: Isolation Forest + Graph (SQL & Cypher).
Try it: download SynapCores Community Edition, load the two tables above, and score them with ANOMALY_SCORE. Then compare the top of the list with your rules.