Real-Time Fraud Detection with Streaming SQL: Card Authorization, Feature Engineering, and Why Not Flink

Real-time fraud detection with streaming SQL means expressing fraud rules, features, and enrichment as continuously maintained SQL queries that evaluate every transaction the moment it arrives, inside the 100 to 300 millisecond authorization window where intervention still matters. This guide builds a complete system on RisingWave, a PostgreSQL-compatible streaming database: a step-by-step tutorial with four detection rules and a unified alert view, the feature vector a fraud model scores against, enrichment with customer and account state for banking and card authorization, sequence patterns such as a probe charge followed by a large purchase, and a side-by-side look at why most teams do not need Flink to get there.

Why Batch Detection Falls Short

Most fraud pipelines still run as batch ETL. A job runs every hour or overnight, scans recent transactions, applies rules, and writes alerts to a database. Credit card authorization completes in 100 to 300 milliseconds. By the time a batch pipeline flags a stolen card, it has been used dozens of times across multiple merchants.

Three problems compound in fraud specifically. The freshness gap is the attack window: a compromised card is typically drained within minutes, so hourly velocity features see zero transactions for a card that has already been used twelve times. Training/serving skew distorts risk scores when training reads raw event logs and serving reads a feature store snapshot with slightly different window boundaries or null handling, and fraud thresholds are tuned to basis-point precision. Late-arriving events break batch windows when payment events arrive out of order after retries or processor delays.

A streaming database processes each transaction as it arrives, updates materialized views incrementally, and makes results queryable immediately. RisingWave is open source under Apache 2.0, written in Rust, and checkpoints state to S3. When a new transaction arrives, only the affected rows of each view are recomputed.

Tutorial: Four Detection Rules and a Unified Alert View

You will build two base tables, four materialized views that each encode a detection rule, and a unified dashboard. In production fraud_transactions is a Kafka source; for the walkthrough a table lets you insert sample rows with psql -h localhost -p 4566 -U root -d dev.

CREATE TABLE fraud_transactions (
    txn_id        VARCHAR,
    card_id       VARCHAR,
    user_id       VARCHAR,
    amount        DECIMAL,
    merchant_id   VARCHAR,
    merchant_cat  VARCHAR,
    country       VARCHAR,
    city          VARCHAR,
    txn_time      TIMESTAMP,
    is_online     BOOLEAN
);

CREATE TABLE fraud_cards (
    card_id       VARCHAR,
    user_id       VARCHAR,
    card_type     VARCHAR,
    credit_limit  DECIMAL,
    home_country  VARCHAR,
    issued_at     TIMESTAMP
);

Seed four cardholders, each exhibiting one fraud pattern:

INSERT INTO fraud_cards VALUES
  ('card_001', 'user_alice', 'visa',       5000.00, 'US', '2023-01-15 00:00:00'),
  ('card_002', 'user_bob',   'mastercard', 8000.00, 'UK', '2022-06-20 00:00:00'),
  ('card_003', 'user_carol', 'amex',      15000.00, 'US', '2021-11-10 00:00:00'),
  ('card_004', 'user_dave',  'visa',       3000.00, 'DE', '2024-03-05 00:00:00');

INSERT INTO fraud_transactions VALUES
  -- alice: a single charge above her credit limit
  ('txn_001', 'card_001', 'user_alice',   45.00, 'merch_A1', 'grocery',     'US', 'New York',    '2026-04-01 09:00:00', false),
  ('txn_003', 'card_001', 'user_alice', 6200.00, 'merch_B1', 'jewelry',     'US', 'New York',    '2026-04-01 10:00:00', false),
  -- bob: three countries within one hour
  ('txn_010', 'card_002', 'user_bob',    200.00, 'merch_C1', 'retail',      'UK', 'London',      '2026-04-01 08:00:00', false),
  ('txn_011', 'card_002', 'user_bob',    350.00, 'merch_C2', 'retail',      'US', 'Chicago',     '2026-04-01 08:20:00', false),
  ('txn_012', 'card_002', 'user_bob',    410.00, 'merch_C3', 'retail',      'DE', 'Berlin',      '2026-04-01 08:40:00', false),
  -- carol: six purchases totaling $19,500 in under five minutes
  ('txn_020', 'card_003', 'user_carol', 4000.00, 'merch_D1', 'electronics', 'US', 'Los Angeles', '2026-04-01 14:00:00', true),
  ('txn_021', 'card_003', 'user_carol', 3500.00, 'merch_D2', 'electronics', 'US', 'Los Angeles', '2026-04-01 14:01:00', true),
  ('txn_022', 'card_003', 'user_carol', 2800.00, 'merch_D3', 'electronics', 'US', 'Los Angeles', '2026-04-01 14:02:00', true),
  ('txn_023', 'card_003', 'user_carol', 3100.00, 'merch_D4', 'electronics', 'US', 'Los Angeles', '2026-04-01 14:03:00', true),
  ('txn_024', 'card_003', 'user_carol', 2900.00, 'merch_D5', 'electronics', 'US', 'Los Angeles', '2026-04-01 14:03:30', true),
  ('txn_025', 'card_003', 'user_carol', 3200.00, 'merch_D6', 'electronics', 'US', 'Los Angeles', '2026-04-01 14:04:00', true),
  -- dave: five micro-charges in under two minutes
  ('txn_030', 'card_004', 'user_dave',     0.50, 'merch_E1', 'online',      'DE', 'Berlin',      '2026-04-01 11:00:00', true),
  ('txn_031', 'card_004', 'user_dave',     1.00, 'merch_E2', 'online',      'DE', 'Berlin',      '2026-04-01 11:00:30', true),
  ('txn_032', 'card_004', 'user_dave',     0.99, 'merch_E3', 'online',      'DE', 'Berlin',      '2026-04-01 11:01:00', true),
  ('txn_033', 'card_004', 'user_dave',     1.50, 'merch_E4', 'online',      'DE', 'Berlin',      '2026-04-01 11:01:30', true),
  ('txn_034', 'card_004', 'user_dave',     0.75, 'merch_E5', 'online',      'DE', 'Berlin',      '2026-04-01 11:01:45', true);

Rule 1: high-value transactions. A charge that is large in absolute terms or relative to the card's limit. The join with fraud_cards supplies context a raw stream lacks: $3,000 is routine on a $50,000 limit and alarming on a $3,000 limit.

CREATE MATERIALIZED VIEW fraud_high_value_alerts AS
SELECT t.txn_id, t.card_id, t.user_id, t.amount, t.merchant_cat, t.country, t.city, t.txn_time,
       c.credit_limit,
       ROUND((t.amount / c.credit_limit * 100)::NUMERIC, 1) AS pct_of_limit
FROM fraud_transactions t
JOIN fraud_cards c ON t.card_id = c.card_id
WHERE t.amount > 5000
   OR t.amount > c.credit_limit * 0.5;

Alice's $6,200 jewelry charge appears at 124 percent of her limit. Insert another transaction and query again: the view reflects it immediately, with no rescan.

Rule 2: velocity. Stolen cards are used in rapid bursts. A five-minute tumbling window fires when either count or total spend crosses a threshold:

CREATE MATERIALIZED VIEW fraud_velocity_alerts AS
SELECT card_id, user_id, window_start, window_end,
       COUNT(*)                     AS txn_count,
       SUM(amount)                  AS total_amount,
       COUNT(DISTINCT merchant_cat) AS unique_categories
FROM TUMBLE(fraud_transactions, txn_time, INTERVAL '5 MINUTES')
GROUP BY card_id, user_id, window_start, window_end
HAVING COUNT(*) >= 5 OR SUM(amount) > 10000;

Carol is flagged with six transactions totaling $19,500 in one window. Dave is flagged too, which is the point of layering rules.

Rule 3: geographic anomaly. A card cannot be in London at 8:00 and Chicago at 8:20. Two or more countries inside an hour means a cloned card or stolen stripe data:

CREATE MATERIALIZED VIEW fraud_geo_anomaly_alerts AS
SELECT card_id, user_id, window_start, window_end,
       COUNT(DISTINCT country) AS country_count,
       COUNT(DISTINCT city)    AS city_count,
       COUNT(*)                AS txn_count,
       SUM(amount)             AS total_amount
FROM TUMBLE(fraud_transactions, txn_time, INTERVAL '1 HOUR')
GROUP BY card_id, user_id, window_start, window_end
HAVING COUNT(DISTINCT country) >= 2;

Rule 4: card testing. Fraudsters verify a stolen number with a burst of tiny charges before a large purchase. A tight two-minute window with a low average separates it from parking meters and transit taps:

CREATE MATERIALIZED VIEW fraud_card_testing_alerts AS
SELECT card_id, user_id, window_start, window_end,
       COUNT(*)              AS txn_count,
       SUM(amount)           AS total_amount,
       ROUND(AVG(amount), 2) AS avg_amount,
       MAX(amount)           AS max_amount
FROM TUMBLE(fraud_transactions, txn_time, INTERVAL '2 MINUTES')
GROUP BY card_id, user_id, window_start, window_end
HAVING COUNT(*) >= 3 AND AVG(amount) < 2.00;

The unified alert view. Analysts and downstream systems need one place to look. A UNION ALL of the four rules, normalized to a common shape, is that place:

CREATE MATERIALIZED VIEW fraud_unified_alerts AS
SELECT 'high_value' AS alert_type, txn_id AS event_id, card_id, user_id, amount, city AS location,
       txn_time AS detected_at, 'Amount exceeds 50% of credit limit or absolute threshold' AS reason
FROM fraud_high_value_alerts
UNION ALL
SELECT 'velocity', card_id || '@' || window_start::VARCHAR, card_id, user_id, total_amount, 'multiple merchants',
       window_start, txn_count::VARCHAR || ' transactions in 5 min window'
FROM fraud_velocity_alerts
UNION ALL
SELECT 'geo_anomaly', card_id || '@' || window_start::VARCHAR, card_id, user_id, total_amount,
       country_count::VARCHAR || ' countries', window_start,
       'Card used in ' || country_count::VARCHAR || ' countries within 1 hour'
FROM fraud_geo_anomaly_alerts
UNION ALL
SELECT 'card_testing', card_id || '@' || window_start::VARCHAR, card_id, user_id, total_amount, 'online',
       window_start, txn_count::VARCHAR || ' micro-transactions averaging $' || avg_amount::VARCHAR
FROM fraud_card_testing_alerts;

All four cardholders are caught, and Dave appears twice. That overlap is useful: counting distinct rules fired per card in the last hour gives a confidence score, so multi-rule hits route to human review while single-rule hits go to an automated hold.

SELECT card_id, user_id, COUNT(DISTINCT alert_type) AS rule_count, SUM(amount) AS total_flagged
FROM fraud_unified_alerts
WHERE detected_at >= NOW() - INTERVAL '1 HOUR'
GROUP BY card_id, user_id
ORDER BY rule_count DESC, total_flagged DESC;

In production, replace the table with a source and push the unified view to Kafka. Every view above works unchanged against the source:

CREATE SOURCE fraud_transactions_live (
    txn_id VARCHAR, card_id VARCHAR, user_id VARCHAR, amount DECIMAL,
    merchant_id VARCHAR, merchant_cat VARCHAR, country VARCHAR, city VARCHAR,
    txn_time TIMESTAMP, is_online BOOLEAN
) WITH (
    connector = 'kafka',
    topic = 'credit-card-transactions',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

CREATE SINK fraud_alerts_sink FROM fraud_unified_alerts
WITH (connector = 'kafka', topic = 'fraud-alerts', properties.bootstrap.server = 'kafka:9092', primary_key = 'event_id')
FORMAT UPSERT ENCODE JSON;

Sequence Patterns: Probe Then Spike, and Impossible Travel

Some patterns are about order, not counts. A $1 test charge followed by a $4,000 purchase on the same card within 30 minutes is a classic signature, and a stream-to-stream self-join with a time bound catches it continuously:

CREATE MATERIALIZED VIEW probe_then_spike AS
SELECT a.card_id,
       a.txn_id AS probe_txn_id, b.txn_id AS spike_txn_id,
       a.amount AS probe_amount, b.amount AS spike_amount,
       a.txn_time AS probe_time, b.txn_time AS spike_time
FROM fraud_transactions a
JOIN fraud_transactions b
  ON a.card_id = b.card_id
 AND a.amount < 5.00
 AND b.amount > 500.00
 AND b.txn_time > a.txn_time
 AND b.txn_time < a.txn_time + INTERVAL '30 MINUTES';

The same shape, with a country inequality, flags impossible travel at the pair level rather than the window level, which gives the analyst both locations and the seconds between them:

CREATE MATERIALIZED VIEW impossible_travel AS
SELECT a.card_id, a.country AS country_1, b.country AS country_2,
       a.txn_time AS time_1, b.txn_time AS time_2,
       EXTRACT(EPOCH FROM (b.txn_time - a.txn_time)) AS seconds_apart
FROM fraud_transactions a
JOIN fraud_transactions b
  ON a.card_id = b.card_id
 AND a.country <> b.country
 AND b.txn_time > a.txn_time
 AND b.txn_time < a.txn_time + INTERVAL '1 HOUR';

Patterns that need strictly ordered multi-step matching (A then B then C with no intervening events) are the domain of MATCH_RECOGNIZE, which RisingWave does not support today. Self-joins with time bounds and window functions such as LAG cover the large majority of production fraud patterns.

Feature Engineering for a Fraud Model

Rules catch the known patterns. A model catches the rest, and it is only as good as the features it sees at scoring time. The same transaction stream, with device and geolocation fields, yields four feature families as views. NOW() in a FILTER clause is a temporal filter in RisingWave, so several horizons live in one view without self-joins.

CREATE SOURCE raw_transactions (
    transaction_id     VARCHAR,
    user_id            VARCHAR,
    card_id            VARCHAR,
    amount             DECIMAL,
    merchant_id        VARCHAR,
    merchant_category  VARCHAR,
    device_id          VARCHAR,
    ip_address         VARCHAR,
    latitude           DOUBLE PRECISION,
    longitude          DOUBLE PRECISION,
    country_code       VARCHAR,
    event_time         TIMESTAMP
) WITH (
    connector = 'kafka',
    topic = 'payments.transactions',
    properties.bootstrap.server = 'broker:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

-- Velocity
CREATE MATERIALIZED VIEW mv_velocity_features AS
SELECT user_id, card_id,
       COUNT(*) FILTER (WHERE event_time > NOW() - INTERVAL '5 MINUTES')   AS txn_count_5min,
       COUNT(*) FILTER (WHERE event_time > NOW() - INTERVAL '30 MINUTES')  AS txn_count_30min,
       COUNT(*) FILTER (WHERE event_time > NOW() - INTERVAL '1 HOUR')      AS txn_count_1h,
       COUNT(*) FILTER (WHERE event_time > NOW() - INTERVAL '24 HOURS')    AS txn_count_24h,
       MAX(event_time)                                                      AS last_txn_time
FROM raw_transactions
GROUP BY user_id, card_id;

-- Amounts
CREATE MATERIALIZED VIEW mv_amount_features AS
SELECT user_id, card_id,
       SUM(amount) FILTER (WHERE event_time > NOW() - INTERVAL '5 MINUTES')          AS total_amount_5min,
       SUM(amount) FILTER (WHERE event_time > NOW() - INTERVAL '1 HOUR')             AS total_amount_1h,
       AVG(amount) FILTER (WHERE event_time > NOW() - INTERVAL '1 HOUR')             AS avg_amount_1h,
       MAX(amount) FILTER (WHERE event_time > NOW() - INTERVAL '1 HOUR')             AS max_amount_1h,
       STDDEV_SAMP(amount) FILTER (WHERE event_time > NOW() - INTERVAL '1 HOUR')     AS stddev_amount_1h,
       MAX(amount) FILTER (WHERE event_time > NOW() - INTERVAL '1 HOUR')
         / NULLIF(AVG(amount) FILTER (WHERE event_time > NOW() - INTERVAL '1 HOUR'), 0) AS max_to_avg_ratio_1h
FROM raw_transactions
GROUP BY user_id, card_id;

-- Devices and IPs
CREATE MATERIALIZED VIEW mv_device_features AS
SELECT user_id, card_id,
       COUNT(DISTINCT device_id)  FILTER (WHERE event_time > NOW() - INTERVAL '1 HOUR')   AS distinct_devices_1h,
       COUNT(DISTINCT device_id)  FILTER (WHERE event_time > NOW() - INTERVAL '24 HOURS') AS distinct_devices_24h,
       COUNT(DISTINCT ip_address) FILTER (WHERE event_time > NOW() - INTERVAL '1 HOUR')   AS distinct_ips_1h,
       COUNT(DISTINCT device_id)  FILTER (WHERE event_time > NOW() - INTERVAL '1 HOUR') > 2 AS multi_device_flag
FROM raw_transactions
GROUP BY user_id, card_id;

Geolocation features pair each transaction with the previous one on the same card using LAG, then aggregate cross-country hops and an impossible-travel flag:

CREATE MATERIALIZED VIEW mv_geo_pairs AS
SELECT transaction_id, user_id, card_id, country_code, event_time,
       LAG(country_code) OVER (PARTITION BY user_id, card_id ORDER BY event_time) AS prev_country,
       LAG(event_time)   OVER (PARTITION BY user_id, card_id ORDER BY event_time) AS prev_event_time
FROM raw_transactions;

CREATE MATERIALIZED VIEW mv_geo_anomaly_features AS
SELECT user_id, card_id,
       COUNT(*) FILTER (WHERE prev_country IS NOT NULL AND country_code <> prev_country
                          AND event_time - prev_event_time < INTERVAL '10 MINUTES')  AS cross_country_txns_10min,
       COUNT(DISTINCT country_code) FILTER (WHERE event_time > NOW() - INTERVAL '1 HOUR') AS distinct_countries_1h,
       BOOL_OR(prev_country IS NOT NULL AND country_code <> prev_country
               AND event_time - prev_event_time < INTERVAL '5 MINUTES')              AS impossible_travel_flag
FROM mv_geo_pairs
GROUP BY user_id, card_id;

The model needs one row per card at scoring time. Join the groups into a feature vector and the inference service reads it with any PostgreSQL driver in single-digit milliseconds:

CREATE MATERIALIZED VIEW mv_fraud_feature_vector AS
SELECT v.user_id, v.card_id,
       v.txn_count_5min, v.txn_count_30min, v.txn_count_1h, v.txn_count_24h,
       a.total_amount_5min, a.total_amount_1h, a.avg_amount_1h, a.max_amount_1h, a.stddev_amount_1h, a.max_to_avg_ratio_1h,
       d.distinct_devices_1h, d.distinct_devices_24h, d.distinct_ips_1h, d.multi_device_flag,
       g.cross_country_txns_10min, g.distinct_countries_1h, g.impossible_travel_flag
FROM mv_velocity_features v
JOIN mv_amount_features a      ON v.user_id = a.user_id AND v.card_id = a.card_id
JOIN mv_device_features d      ON v.user_id = d.user_id AND v.card_id = d.card_id
JOIN mv_geo_anomaly_features g ON v.user_id = g.user_id AND v.card_id = g.card_id;

CREATE SINK fraud_features_to_kafka FROM mv_fraud_feature_vector
WITH (connector = 'kafka', topic = 'fraud.features', properties.bootstrap.server = 'broker:9092', primary_key = 'user_id,card_id')
FORMAT UPSERT ENCODE JSON;

A card that fires a $1 probe and then $16,000 across four countries on four devices in three minutes shows txn_count_5min = 5, distinct_devices_1h = 4, and impossible_travel_flag = true in this vector before the fourth authorization completes. Because the same SQL can run over the historical log for training, the model sees identical feature definitions in both places, which removes training/serving skew by construction.

Enrichment for Banking: Customer Profiles and Temporal Joins

Banks add a layer the raw stream cannot supply: who the customer is. A profile table holds home country, typical spend, and risk tier, and a temporal join (FOR SYSTEM_TIME AS OF) enriches each transaction with the profile as it stood at transaction time, so a profile update does not retroactively change past alerts:

CREATE TABLE customer_profiles (
    account_id        VARCHAR PRIMARY KEY,
    home_country      VARCHAR,
    avg_daily_spend   NUMERIC,
    risk_tier         VARCHAR
);

CREATE MATERIALIZED VIEW enriched_fraud_signals AS
SELECT t.txn_id, t.card_id AS account_id, t.amount, t.country, t.txn_time,
       cp.home_country, cp.avg_daily_spend, cp.risk_tier,
       t.country <> cp.home_country              AS geo_mismatch,
       t.amount > cp.avg_daily_spend * 3          AS amount_anomaly
FROM fraud_transactions_live t
LEFT JOIN customer_profiles FOR SYSTEM_TIME AS OF t.txn_time AS cp
  ON t.card_id = cp.account_id
WHERE t.country <> cp.home_country
   OR t.amount > cp.avg_daily_spend * 3;

A seven-day baseline per user turns "large" into a z-score, which generalizes across customers with very different spending levels:

CREATE MATERIALIZED VIEW user_baseline_7d AS
SELECT user_id, AVG(amount) AS avg_txn_amount, STDDEV(amount) AS stddev_amount, COUNT(*) AS txn_count_7d
FROM fraud_transactions_live
WHERE txn_time > NOW() - INTERVAL '7 DAYS'
GROUP BY user_id;

CREATE MATERIALIZED VIEW fraud_signals AS
SELECT t.txn_id, t.user_id, t.card_id, t.amount, t.country, t.txn_time,
       CASE WHEN b.stddev_amount > 0 THEN (t.amount - b.avg_txn_amount) / b.stddev_amount ELSE 0 END AS amount_z_score,
       t.amount > b.avg_txn_amount + 3 * b.stddev_amount AS amount_spike
FROM fraud_transactions_live t
LEFT JOIN user_baseline_7d b ON t.user_id = b.user_id;

Card Authorization Decisioning

Issuers face the strictest version of the problem: approve or decline in under 300 milliseconds end to end. Traditional authorization engines preload static account data on slow refresh cycles, so accounts closed hours ago still approve, a spending limit the cardholder changed in the app is not yet visible, and velocity accumulates unseen until the next refresh. False declines cost merchants and cardholders as much as fraud does.

RisingWave becomes the real-time context store the authorization engine queries. Account state arrives through CDC from core banking, approved transactions arrive from the card network, and one view combines them into a risk flag:

CREATE SOURCE card_transactions (
    transaction_id    VARCHAR,
    account_id        VARCHAR,
    merchant_id       VARCHAR,
    merchant_mcc      VARCHAR,
    amount            NUMERIC,
    country_code      VARCHAR,
    channel           VARCHAR,     -- 'chip', 'swipe', 'contactless', 'ecommerce'
    auth_outcome      VARCHAR,     -- 'approved', 'declined', 'reversed'
    txn_time          TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'card.transactions',
    properties.bootstrap.server = 'broker:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

CREATE TABLE account_state (
    account_id        VARCHAR PRIMARY KEY,
    credit_limit      NUMERIC,
    available_credit  NUMERIC,
    account_status    VARCHAR,     -- 'active', 'frozen', 'closed', 'over_limit'
    risk_score        INTEGER,
    updated_at        TIMESTAMPTZ
) WITH (
    connector = 'postgres-cdc',
    hostname = 'core-banking.internal', port = '5432',
    username = 'rw_reader', password = 'secret',
    database.name = 'accounts', schema.name = 'public', table.name = 'account_state'
);

CREATE MATERIALIZED VIEW account_spending_velocity AS
SELECT account_id,
       COUNT(*)    FILTER (WHERE txn_time >= NOW() - INTERVAL '1 HOUR')               AS txns_last_1h,
       SUM(amount) FILTER (WHERE txn_time >= NOW() - INTERVAL '1 HOUR')               AS spend_last_1h,
       COUNT(*)    FILTER (WHERE txn_time >= NOW() - INTERVAL '24 HOURS')             AS txns_last_24h,
       SUM(amount) FILTER (WHERE txn_time >= NOW() - INTERVAL '24 HOURS')             AS spend_last_24h,
       COUNT(DISTINCT country_code) FILTER (WHERE txn_time >= NOW() - INTERVAL '6 HOURS') AS distinct_countries_6h,
       COUNT(DISTINCT merchant_mcc) FILTER (WHERE txn_time >= NOW() - INTERVAL '24 HOURS') AS distinct_mcc_24h,
       MAX(txn_time)                                                                   AS last_txn_time
FROM card_transactions
WHERE auth_outcome = 'approved'
GROUP BY account_id;

CREATE MATERIALIZED VIEW authorization_risk_context AS
SELECT v.*, a.credit_limit, a.available_credit, a.account_status, a.risk_score,
       CASE
           WHEN a.account_status <> 'active'                 THEN 'BLOCK'
           WHEN v.distinct_countries_6h > 1                   THEN 'HIGH'
           WHEN v.txns_last_1h > 20                           THEN 'HIGH'
           WHEN v.spend_last_24h > a.credit_limit * 0.9       THEN 'MEDIUM'
           WHEN a.risk_score > 700                            THEN 'MEDIUM'
           ELSE 'LOW'
       END AS auth_risk_flag
FROM account_spending_velocity v
JOIN account_state a ON v.account_id = a.account_id;

The authorization engine issues SELECT ... FROM authorization_risk_context WHERE account_id = $1 on every request and receives the full context in single-digit milliseconds. A freeze committed in core banking propagates through CDC and flips auth_risk_flag to BLOCK within seconds, with no cache refresh cycle. Authorization outcomes written back to Kafka close the loop and feed the velocity view.

Flink Java vs Streaming SQL

Both Apache Flink and RisingWave can evaluate these patterns inside the authorization window. The difference is how much code it takes and what operations look like afterward.

The velocity check above is eight lines of SQL. In Flink Java it is a Transaction POJO, a Kafka consumer with a custom deserialization schema, a watermark strategy, a ProcessWindowFunction that loops over the window and collects an alert, a VelocityAlert output POJO, and a sink. That is roughly 55 to 65 lines before any results are queryable, and querying them means writing to an external database first. Adding the merchant anomaly pattern (same user, same merchant, four or more times in ten minutes) in SQL means adding merchant_id to the GROUP BY and changing two numbers. In Flink it means a new window function class and a new output POJO, another 50 lines, because the logic cannot be parameterized the way SQL is.

The consequence is iteration speed. When the fraud team identifies a new pattern on Tuesday morning, a SQL view is running by Tuesday afternoon. Flink is the right choice when detection requires ML inference inside the stream operator, complex event processing beyond what self-joins and window functions express, or volumes beyond a single streaming database deployment. Most fintech teams are not there, and they pay Flink's operational overhead long before they need its power.

Tuning for Production

• Window sizes. Five-minute velocity and one-hour geography are starting points. Study normal burst behavior in your portfolio; tighten windows to cut false negatives, widen them to cut false positives.
• Thresholds. Use the credit-limit ratio and the per-user z-score rather than absolute amounts so one rule set spans card tiers.
• Layered confidence. Route cards that trip two or more independent rules to analysts and single-rule flags to automated holds.
• Late data. Define watermarks on the source timestamp so out-of-order payment events land in the right window instead of the next one.
• Freshness monitoring. Track the gap between txn_time on the source and the time alerts appear in the unified view, and inspect rw_catalog.rw_materialized_views for view health.

Frequently Asked Questions

Can RisingWave return a decision inside the authorization window? RisingWave serves precomputed context; the authorization engine makes the decision. A point lookup on authorization_risk_context or mv_fraud_feature_vector returns in single-digit milliseconds, leaving most of the 100 to 300 millisecond budget for scoring and network hops.

How do I avoid double-counting a transaction that arrives twice? Deduplicate on transaction_id with SELECT DISTINCT ON (transaction_id) in a view before the rules and features, and build everything else on that view.

How do rules and the model fit together? Rules in fraud_unified_alerts catch known patterns with explainable reasons. The feature vector feeds a model that catches novel combinations. Many teams score the model and then apply rules as hard overrides, with both reading from the same stream.

Does a temporal join slow down the pipeline? FOR SYSTEM_TIME AS OF looks up the dimension row as of the event's timestamp without materializing a full stream-to-stream join, so it is the cheaper option for enriching with profile and account tables.

What about ML inference inside RisingWave? Python and Java user-defined functions can call a model endpoint from a view for lightweight scoring. Heavy models usually run in a separate service that reads the feature vector; the stream stays responsible for keeping features and rules current.