L. Mansfield

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.

Simulated data Rules: rules-v0 Loading the latest numbers…

Chapter 1 · The rules

The last 30 days

Every checkout the pipeline scored

Checkouts 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;
  1. raw Supabase checkout tables
  2. staging Scored checkouts, last 30 days
  3. 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;
  1. raw Supabase checkout tables
  2. staging Scored checkouts, last 30 days
  3. 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;
  1. raw Supabase checkout tables
  2. staging Scored checkouts, last 30 days
  3. mart fraud_score_bins

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;
  1. raw Supabase checkout tables
  2. staging Scored checkouts, last 30 days
  3. 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

  1. 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.
  2. 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.
  3. 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.