The problem: you want a review queue for odd payments, but you don't have fraud labels, and your rules only catch the patterns you already thought of. A $3,000 payment at 3am from abroad is easy to write a rule for. The next odd combination isn't.
An anomaly model learns what normal looks like and scores how far each row is from it. Usually that means exporting rows to Python, training, scoring and writing the scores back on a schedule. In SynapCores the model trains and scores inside the database, and the score is a SQL expression: ANOMALY_SCORE(...) goes straight into SELECT, WHERE and ORDER BY. This recipe has Claude Code build the queue for you.
We build SynapCores. Everything here runs on the free Community Edition.
What you need
- SynapCores Community Edition 2.4.8 or later, running locally. Download it.
- Claude Code, opened in an empty folder.
SYNAPCORES_URL and SYNAPCORES_API_KEY set in your shell. The memory recipe shows how to create the key.- The sample payments, saved as
payments.sql in that folder: download payments.sql. It's 123 payments from 40 accounts: 120 ordinary ones (under $200, daytime, domestic) and three from ACC-33 of about $3,000, at 3am, cross-border.
Step 1: Paste this prompt into Claude Code
Build a review queue for unusual payments on SynapCores, an AI-native database
that trains and serves anomaly models inside SQL. Train and score in the database
with the statements below; don't export rows to Python or use scikit-learn.
Connection: SynapCores is running at $SYNAPCORES_URL. Send each SQL statement as
POST $SYNAPCORES_URL/v1/query/execute
Headers: Authorization: Bearer $SYNAPCORES_API_KEY, Content-Type: application/json
Body: {"database": "payments_db", "sql": "<one statement>"}
Results come back in data.columns (a list of {"name", "data_type"}) and data.rows
(a list of rows, each a list of values in column order). Create the database
first with CREATE DATABASE IF NOT EXISTS payments_db (send that one with "database": "main").
SQL you'll need:
- Train (no labels needed):
CREATE EXPERIMENT payments_exp AS
SELECT amount, hour_of_day, cross_border FROM payments
WITH (task_type = 'anomaly_detection', algorithms = ['isolation_forest'],
max_trials = 5, random_seed = 42)
- Deploy: DEPLOY MODEL payments_model FROM EXPERIMENT payments_exp
- Score: ANOMALY_SCORE('payments_model', amount, hour_of_day, cross_border)
It's an ordinary SQL expression: use it in SELECT, WHERE, ORDER BY and UPDATE.
Pass the columns in the same order you trained on. Higher means more unusual.
Build:
1. Load payments.sql from this folder (one statement per request).
2. Train and deploy the model. Show me the CREATE EXPERIMENT result.
3. Add a column anomaly DOUBLE to payments and fill it for every row with
UPDATE payments SET anomaly = ANOMALY_SCORE(...).
4. review.py (Python 3, standard library only) with
top(n=5) -> the n most unusual payments with their scores
flagged(threshold=0.7) -> payments with anomaly above the threshold
add_payment(tx_id, account_id, amount, hour_of_day, cross_border)
-> inserts the row, stores its score in anomaly, returns the score
Read the URL and key from the environment and never print the key.
5. demo.py that prints top(5), flagged(0.7), then adds two new payments and
prints their scores:
(124, 'ACC-05', 2650, 4, 1) -- large, 4am, cross-border
(125, 'ACC-05', 64, 13, 0) -- an ordinary lunch
Run demo.py and show me the output.
Step 2: What Claude Code builds
In our run on 2.4.8, Claude Code wrote three files: setup.py (loads the payments, trains and deploys the model, adds and fills the anomaly column), review.py and demo.py.
The CREATE EXPERIMENT result from our run on 2.4.8, trimmed to the main fields:
{"status": "success", "best_score": 0.11215449496543428,
"optimization_metric": "tail_separation", "score_basis": "internal_validation",
"total_trials": 4, "trials_attempted": 4, "failed_trials": []}
best_score is the best trial's tail separation score (higher is better). The model trained without labels.
Here's add_payment() as Claude Code wrote it (your run may differ in the details). The score is computed by the database, for that one row, in an ordinary UPDATE:
def add_payment(tx_id, account_id, amount, hour_of_day, cross_border):
"""Insert a payment, score it in-database, and return its anomaly score."""
tx_id = int(tx_id)
account = str(account_id).replace("'", "''")
execute(
"INSERT INTO payments (tx_id, account_id, amount, hour_of_day, cross_border) "
"VALUES ({}, '{}', {}, {}, {})".format(
tx_id, account, float(amount), int(hour_of_day), int(cross_border))
)
execute(
"UPDATE payments SET anomaly = ANOMALY_SCORE('{}', amount, hour_of_day, cross_border) "
"WHERE tx_id = {}".format(MODEL, tx_id)
)
rows = _rows(execute("SELECT anomaly FROM payments WHERE tx_id = {}".format(tx_id)))
return rows[0]["anomaly"]
Step 3: The demo output
From our run:
Top 5 most unusual payments:
tx_id account amount hour cross_border anomaly
-------------------------------------------------------
121 ACC-33 2840.00 3 1 0.8167
122 ACC-33 3120.00 3 1 0.8167
123 ACC-33 2975.00 3 1 0.8064
108 ACC-36 198.00 19 0 0.5267
115 ACC-39 23.00 17 0 0.5181
Flagged payments (anomaly > 0.7):
tx_id account amount hour cross_border anomaly
-------------------------------------------------------
121 ACC-33 2840.00 3 1 0.8167
122 ACC-33 3120.00 3 1 0.8167
123 ACC-33 2975.00 3 1 0.8064
Adding two new payments:
tx_id=124 ACC-05 amount=2650 hour=4 cross_border=1 -> anomaly score 0.7938
tx_id=125 ACC-05 amount=64 hour=13 cross_border=0 -> anomaly score 0.4526
What it shows:
- The model found ACC-33 on its own. Nothing told it which rows were fraud. It learned what the 120 ordinary payments look like, and the three that don't fit came out on top, at about 0.81–0.82. The next row scores 0.53.
- The score is a column you query.
flagged() is a plain WHERE anomaly > 0.7, and it returns exactly the three ACC-33 payments. Join it to customers, count it per day, sort a dashboard by it: it's SQL.
- New payments are scored as they arrive. The large 4am cross-border payment (124) scores 0.79, close to the ACC-33 rows. The lunch (125) scores 0.45, like the ordinary rows.
add_payment stores the score with the row, so the review queue is current without a nightly batch job.
Step 4: Put the queue in front of a person
Add a command `python review.py queue` that prints flagged payments newest first
(highest tx_id first), with account, amount, hour, cross_border and score, one per line.
The queue after the demo, from our run:
tx_id=124 account=ACC-05 amount=2650.00 hour=4 cross_border=1 score=0.7938
tx_id=123 account=ACC-33 amount=2975.00 hour=3 cross_border=1 score=0.8064
tx_id=122 account=ACC-33 amount=3120.00 hour=3 cross_border=1 score=0.8167
tx_id=121 account=ACC-33 amount=2840.00 hour=3 cross_border=1 score=0.8167
Payment 124 wasn't in the training data, and it's already at the top of the queue. The query behind it is WHERE anomaly > 0.7 ORDER BY tx_id DESC.
Why this needs SynapCores
CREATE EXPERIMENT trains the isolation forest inside the engine, DEPLOY MODEL serves it under a name, and ANOMALY_SCORE('payments_model', ...) is a SQL function you can use anywhere an expression goes. Training, scoring and the review queue use one database and one copy of the data, and your app only sends SQL.
Next step
Download SynapCores Community Edition, paste the prompt, then swap in your own payments table and features.
Related: How to find unusual transactions in your data with SQL · Fraud rings that ML misses