Live data · Fraud Detection Pipeline
Fraud, scored daily.
These are the real outputs of my fraud-detection pipeline, refreshed every day. Its newest chapter: since November 14 a real-time model scores every theme-park webstore checkout as it lands, and the first section below puts it head to head with the rules that scored them before. A generator plays out the checkouts, including five kinds of fraud, and the results land in Postgres on Supabase. A daily job runs one read-only SQL query over those tables and publishes only the totals you see here.
The latest numbers didn’t load. Try refreshing, or read the JSON file directly.
Chapter 2 · The real-time model
New model vs the rules
Same checkouts, scored by both
| Measure | Rules | New model |
|---|
SQL source
SELECT scorer, checkouts, fraud, legit, flagged, caught, cutoff, p50_ms, p95_ms
FROM fraud_compare
ORDER BY scorer DESC;
- raw Supabase checkout tables
- raw Supabase model scores
- staging Scored checkouts, last 30 days
- staging Checkouts both scorers saw
- mart
fraud_compare
This query writes what’s shown above, from the same aggregates-only snapshot the page draws; a check fails the build if it ever stops matching. See its checks.
Where the numbers come from
with base as (
select
x.transaction_id,
x.event_ts,
(x.event_ts at time zone 'America/New_York')::date as day,
d.decision,
d.score,
r.fraud_scoring_ms,
r.total_latency_ms,
coalesce(r.fraud_timeout, false) as fraud_timeout,
coalesce(t.is_fraud, false) as is_fraud,
t.fraud_pattern,
coalesce(x.total, 0) as total
from public.raw_transactions x
join public.raw_fraud_decisions d using (transaction_id)
left join public.raw_fraud_truth t using (transaction_id)
left join public.raw_checkout_responses r using (transaction_id)
where x.event_ts >= now() - interval '30 days'
and d.model_version = 'rules-v0'
),
model_scores as (
-- The real-time model's latest score per checkout (it can re-score one;
-- transaction_id is text in this table). Its one-off batch run over the
-- older checkouts on Nov 14 records about 0.003 ms a row (the batch's
-- time split across its rows) and gave every order nearly the same tiny
-- score, so the comparison keeps only orders scored one at a time, which
-- record a real per-order time of a few milliseconds.
select distinct on (a.transaction_id)
a.transaction_id,
a.decision = 'FRAUD_FLAGGED' as model_flagged,
a.fraud_probability,
a.latency_ms
from public.realtime_fraud_audit a
where a.latency_ms >= 1
order by a.transaction_id, a.processed_at desc, a.id desc
),
compare_base as (
-- Checkouts in the window that the model scored one at a time, each with
-- both scorers' verdicts.
select b.*, m.model_flagged, m.fraud_probability, m.latency_ms
from base b
join model_scores m on m.transaction_id = b.transaction_id::text
),
compare as (
select
'rules' as scorer,
count(*) as checkouts,
count(*) filter (where is_fraud) as fraud,
count(*) filter (where not is_fraud) as legit,
count(*) filter (where decision <> 'approve') as flagged,
count(*) filter (where is_fraud and decision <> 'approve') as caught,
round(min(score) filter (where decision <> 'approve')::numeric, 4)::float8 as cutoff,
round((percentile_cont(0.5) within group (order by fraud_scoring_ms))::numeric, 1)::float8 as p50_ms,
round((percentile_cont(0.95) within group (order by fraud_scoring_ms))::numeric, 1)::float8 as p95_ms
from compare_base
union all
select
'model',
count(*),
count(*) filter (where is_fraud),
count(*) filter (where not is_fraud),
count(*) filter (where model_flagged),
count(*) filter (where is_fraud and model_flagged),
round(min(fraud_probability) filter (where model_flagged)::numeric, 4)::float8,
round((percentile_cont(0.5) within group (order by latency_ms))::numeric, 1)::float8,
round((percentile_cont(0.95) within group (order by latency_ms))::numeric, 1)::float8
from compare_base
)
select coalesce(json_agg(compare order by scorer desc), '[]'::json) as compare
from compare;
Part of the read-only query that runs once a day on Supabase and publishes the snapshot.
Fraud caught, by pattern
SQL source
SELECT pattern, scorer, fraud, caught
FROM fraud_compare_patterns
ORDER BY fraud DESC, pattern, scorer DESC;
- raw Supabase checkout tables
- raw Supabase model scores
- staging Scored checkouts, last 30 days
- staging Checkouts both scorers saw
- mart
fraud_compare_patterns
This query writes what’s shown above, from the same aggregates-only snapshot the page draws; a check fails the build if it ever stops matching. See its checks.
Where the numbers come from
with base as (
select
x.transaction_id,
x.event_ts,
(x.event_ts at time zone 'America/New_York')::date as day,
d.decision,
d.score,
r.fraud_scoring_ms,
r.total_latency_ms,
coalesce(r.fraud_timeout, false) as fraud_timeout,
coalesce(t.is_fraud, false) as is_fraud,
t.fraud_pattern,
coalesce(x.total, 0) as total
from public.raw_transactions x
join public.raw_fraud_decisions d using (transaction_id)
left join public.raw_fraud_truth t using (transaction_id)
left join public.raw_checkout_responses r using (transaction_id)
where x.event_ts >= now() - interval '30 days'
and d.model_version = 'rules-v0'
),
model_scores as (
-- The real-time model's latest score per checkout (it can re-score one;
-- transaction_id is text in this table). Its one-off batch run over the
-- older checkouts on Nov 14 records about 0.003 ms a row (the batch's
-- time split across its rows) and gave every order nearly the same tiny
-- score, so the comparison keeps only orders scored one at a time, which
-- record a real per-order time of a few milliseconds.
select distinct on (a.transaction_id)
a.transaction_id,
a.decision = 'FRAUD_FLAGGED' as model_flagged,
a.fraud_probability,
a.latency_ms
from public.realtime_fraud_audit a
where a.latency_ms >= 1
order by a.transaction_id, a.processed_at desc, a.id desc
),
compare_base as (
-- Checkouts in the window that the model scored one at a time, each with
-- both scorers' verdicts.
select b.*, m.model_flagged, m.fraud_probability, m.latency_ms
from base b
join model_scores m on m.transaction_id = b.transaction_id::text
),
compare_patterns as (
select fraud_pattern as pattern, 'rules' as scorer, count(*) as fraud,
count(*) filter (where decision <> 'approve') as caught
from compare_base
where is_fraud and fraud_pattern is not null
group by 1
union all
select fraud_pattern, 'model', count(*), count(*) filter (where model_flagged)
from compare_base
where is_fraud and fraud_pattern is not null
group by 1
)
select coalesce(json_agg(compare_patterns
order by fraud desc, pattern collate "C", scorer desc), '[]'::json) as compare_patterns
from compare_patterns;
Part of the read-only query that runs once a day on Supabase and publishes the snapshot.
At other cutoffs
SQL source
SELECT scorer, cutoff, flagged, caught
FROM fraud_compare_sweep
ORDER BY scorer DESC, cutoff DESC;
- raw Supabase checkout tables
- raw Supabase model scores
- staging Scored checkouts, last 30 days
- staging Checkouts both scorers saw
- mart
fraud_compare_sweep
This query writes what’s shown above, from the same aggregates-only snapshot the page draws; a check fails the build if it ever stops matching. See its checks.
Where the numbers come from
with base as (
select
x.transaction_id,
x.event_ts,
(x.event_ts at time zone 'America/New_York')::date as day,
d.decision,
d.score,
r.fraud_scoring_ms,
r.total_latency_ms,
coalesce(r.fraud_timeout, false) as fraud_timeout,
coalesce(t.is_fraud, false) as is_fraud,
t.fraud_pattern,
coalesce(x.total, 0) as total
from public.raw_transactions x
join public.raw_fraud_decisions d using (transaction_id)
left join public.raw_fraud_truth t using (transaction_id)
left join public.raw_checkout_responses r using (transaction_id)
where x.event_ts >= now() - interval '30 days'
and d.model_version = 'rules-v0'
),
model_scores as (
-- The real-time model's latest score per checkout (it can re-score one;
-- transaction_id is text in this table). Its one-off batch run over the
-- older checkouts on Nov 14 records about 0.003 ms a row (the batch's
-- time split across its rows) and gave every order nearly the same tiny
-- score, so the comparison keeps only orders scored one at a time, which
-- record a real per-order time of a few milliseconds.
select distinct on (a.transaction_id)
a.transaction_id,
a.decision = 'FRAUD_FLAGGED' as model_flagged,
a.fraud_probability,
a.latency_ms
from public.realtime_fraud_audit a
where a.latency_ms >= 1
order by a.transaction_id, a.processed_at desc, a.id desc
),
compare_base as (
-- Checkouts in the window that the model scored one at a time, each with
-- both scorers' verdicts.
select b.*, m.model_flagged, m.fraud_probability, m.latency_ms
from base b
join model_scores m on m.transaction_id = b.transaction_id::text
),
compare_sweep as (
-- What each scorer would flag at other cutoffs: the rules on their 0-1
-- risk score, the model on its fraud probability.
select 'rules' as scorer, c.cutoff::float8 as cutoff,
count(*) filter (where b.score >= c.cutoff) as flagged,
count(*) filter (where b.score >= c.cutoff and b.is_fraud) as caught
from compare_base b
cross join unnest(array[0.86, 0.7, 0.5, 0.3, 0.2]) as c(cutoff)
group by c.cutoff
union all
select 'model', c.cutoff::float8,
count(*) filter (where b.fraud_probability >= c.cutoff),
count(*) filter (where b.fraud_probability >= c.cutoff and b.is_fraud)
from compare_base b
cross join unnest(array[0.9, 0.5, 0.1, 0.01, 0.005]) as c(cutoff)
group by c.cutoff
)
select coalesce(json_agg(compare_sweep order by scorer desc, cutoff desc), '[]'::json) as compare_sweep
from compare_sweep;
Part of the read-only query that runs once a day on Supabase and publishes the snapshot.
Flagged means the rules sent the order to review or declined it, or the new model marked it as fraud. Only checkouts the new model scored live, one at a time, are compared, so this is an early read on a small sample. A one-off batch rescore of older orders on Nov 14 is left out, because it gave nearly every order the same score. Checkouts also paused on Nov 15 from about 8:30 AM to 3 PM ET.
Chapter 1 · The rules
The last 30 days
Every checkout the pipeline scoredCheckouts scored
–
Fraud caught
–
Good customers declined
–
Fraud dollars stopped
–
Scoring time, 95th percentile
–
“Caught” means the model sent the order to review or declined it. Fraud is measured against the generator’s answer key; in production those labels arrive weeks later, as chargebacks.
SQL source
SELECT
checkouts, flagged, fraud, fraud_caught, legit, false_declines,
fraud_dollars_stopped, fraud_dollars_approved,
p95_scoring_ms, scoring_timeouts, active_days
FROM fraud_totals;
- raw Supabase checkout tables
- staging Scored checkouts, last 30 days
- mart
fraud_totals
This query writes what’s shown above, from the same aggregates-only snapshot the page draws; a check fails the build if it ever stops matching. See its checks.
Where the numbers come from
with base as (
select
x.transaction_id,
x.event_ts,
(x.event_ts at time zone 'America/New_York')::date as day,
d.decision,
d.score,
r.fraud_scoring_ms,
r.total_latency_ms,
coalesce(r.fraud_timeout, false) as fraud_timeout,
coalesce(t.is_fraud, false) as is_fraud,
t.fraud_pattern,
coalesce(x.total, 0) as total
from public.raw_transactions x
join public.raw_fraud_decisions d using (transaction_id)
left join public.raw_fraud_truth t using (transaction_id)
left join public.raw_checkout_responses r using (transaction_id)
where x.event_ts >= now() - interval '30 days'
and d.model_version = 'rules-v0'
),
totals as (
select
count(*) as checkouts,
count(*) filter (where decision <> 'approve') as flagged,
count(*) filter (where decision = 'decline') as declined,
count(*) filter (where decision = 'review') as reviewed,
count(*) filter (where is_fraud) as fraud,
count(*) filter (where is_fraud and decision <> 'approve') as fraud_caught,
count(*) filter (where not is_fraud and decision = 'decline') as false_declines,
count(*) filter (where not is_fraud) as legit,
round(sum(total) filter (where is_fraud and decision <> 'approve'), 2)::float8 as fraud_dollars_stopped,
round(sum(total) filter (where is_fraud and decision = 'approve'), 2)::float8 as fraud_dollars_approved,
round((percentile_cont(0.5) within group (order by fraud_scoring_ms))::numeric, 1)::float8 as p50_scoring_ms,
round((percentile_cont(0.95) within group (order by fraud_scoring_ms))::numeric, 1)::float8 as p95_scoring_ms,
round((percentile_cont(0.95) within group (order by total_latency_ms))::numeric, 0)::float8 as p95_checkout_ms,
count(*) filter (where fraud_timeout) as scoring_timeouts,
count(distinct day) as active_days
from base
)
select row_to_json(totals) as totals from totals;
Part of the read-only query that runs once a day on Supabase and publishes the snapshot.
Fraud per day
Caught versus missed- Caught
- Missed
See the daily numbers as a table
| Day | Checkouts | Reviewed | Declined | Fraud | Caught | Good customers declined | Fraud $ stopped |
|---|
SQL source
SELECT
day, checkouts, reviewed, declined,
fraud, fraud_caught, false_declines, fraud_dollars_stopped
FROM fraud_daily
ORDER BY day;
- raw Supabase checkout tables
- staging Scored checkouts, last 30 days
- mart
fraud_daily
This query writes what’s shown above, from the same aggregates-only snapshot the page draws; a check fails the build if it ever stops matching. See its checks.
Where the numbers come from
with base as (
select
x.transaction_id,
x.event_ts,
(x.event_ts at time zone 'America/New_York')::date as day,
d.decision,
d.score,
r.fraud_scoring_ms,
r.total_latency_ms,
coalesce(r.fraud_timeout, false) as fraud_timeout,
coalesce(t.is_fraud, false) as is_fraud,
t.fraud_pattern,
coalesce(x.total, 0) as total
from public.raw_transactions x
join public.raw_fraud_decisions d using (transaction_id)
left join public.raw_fraud_truth t using (transaction_id)
left join public.raw_checkout_responses r using (transaction_id)
where x.event_ts >= now() - interval '30 days'
and d.model_version = 'rules-v0'
),
daily as (
select
day,
count(*) as checkouts,
count(*) filter (where decision = 'approve') as approved,
count(*) filter (where decision = 'review') as reviewed,
count(*) filter (where decision = 'decline') as declined,
count(*) filter (where is_fraud) as fraud,
count(*) filter (where is_fraud and decision <> 'approve') as fraud_caught,
count(*) filter (where not is_fraud and decision = 'decline') as false_declines,
round(sum(total) filter (where is_fraud and decision <> 'approve'), 2)::float8 as fraud_dollars_stopped,
round(sum(total) filter (where is_fraud and decision = 'approve'), 2)::float8 as fraud_dollars_approved,
round((percentile_cont(0.95) within group (order by fraud_scoring_ms))::numeric, 1)::float8 as p95_scoring_ms
from base
group by day
)
select coalesce(json_agg(daily order by day), '[]'::json) as daily from daily;
Part of the read-only query that runs once a day on Supabase and publishes the snapshot.
Where the model’s scores fall
Share of each group by risk score- Good customers
- Fraud
SQL source
SELECT score_from, score_to, legit, fraud
FROM fraud_score_bins
ORDER BY score_from;
This query writes what’s shown above, from the same aggregates-only snapshot the page draws; a check fails the build if it ever stops matching. See its checks.
Where the numbers come from
with base as (
select
x.transaction_id,
x.event_ts,
(x.event_ts at time zone 'America/New_York')::date as day,
d.decision,
d.score,
r.fraud_scoring_ms,
r.total_latency_ms,
coalesce(r.fraud_timeout, false) as fraud_timeout,
coalesce(t.is_fraud, false) as is_fraud,
t.fraud_pattern,
coalesce(x.total, 0) as total
from public.raw_transactions x
join public.raw_fraud_decisions d using (transaction_id)
left join public.raw_fraud_truth t using (transaction_id)
left join public.raw_checkout_responses r using (transaction_id)
where x.event_ts >= now() - interval '30 days'
and d.model_version = 'rules-v0'
),
bins as (
select
least(floor(score * 20)::int, 19) as bin,
count(*) filter (where not is_fraud) as legit,
count(*) filter (where is_fraud) as fraud
from base
group by 1
)
select coalesce(json_agg(json_build_object(
'from', round(bin / 20.0, 2)::float8, 'to', round((bin + 1) / 20.0, 2)::float8,
'legit', legit, 'fraud', fraud) order by bin), '[]'::json) as score_bins
from bins;
Part of the read-only query that runs once a day on Supabase and publishes the snapshot.
Catch rate by fraud pattern
Where the rules fell short
SQL source
SELECT pattern, fraud, caught
FROM fraud_patterns
ORDER BY fraud DESC, pattern;
- raw Supabase checkout tables
- staging Scored checkouts, last 30 days
- mart
fraud_patterns
This query writes what’s shown above, from the same aggregates-only snapshot the page draws; a check fails the build if it ever stops matching. See its checks.
Where the numbers come from
with base as (
select
x.transaction_id,
x.event_ts,
(x.event_ts at time zone 'America/New_York')::date as day,
d.decision,
d.score,
r.fraud_scoring_ms,
r.total_latency_ms,
coalesce(r.fraud_timeout, false) as fraud_timeout,
coalesce(t.is_fraud, false) as is_fraud,
t.fraud_pattern,
coalesce(x.total, 0) as total
from public.raw_transactions x
join public.raw_fraud_decisions d using (transaction_id)
left join public.raw_fraud_truth t using (transaction_id)
left join public.raw_checkout_responses r using (transaction_id)
where x.event_ts >= now() - interval '30 days'
and d.model_version = 'rules-v0'
),
patterns as (
select
fraud_pattern as pattern,
count(*) as fraud,
count(*) filter (where decision <> 'approve') as caught
from base
where is_fraud and fraud_pattern is not null
group by 1
)
select coalesce(json_agg(patterns order by fraud desc, pattern collate "C"), '[]'::json) as patterns
from patterns;
Part of the read-only query that runs once a day on Supabase and publishes the snapshot.
How this page stays current
- Generate and score. The pipeline plays out webstore checkouts, scores each one, simulates the payment response with a fail-open timeout, and writes four raw tables to Postgres in a single transaction.
- Aggregate, read-only. Once a day a scheduled GitHub Action runs one SQL query inside a read-only transaction. It returns counts, rates, and a score histogram, never a single order, email, card, or IP address.
- Publish a snapshot. The Action checks the result against an allow-list of fields, commits it as fraud-snapshot.json, and the site redeploys. This page draws its charts from that file in your browser; no database key ever reaches it.