Game Fraud and Account Takeover Detection with Streaming SQL

RisingWave detects game fraud and account takeovers in real time by streaming login, transaction, and game-state events through continuously updated materialized views that flag suspicious behavioral patterns—impossible logins, abnormal trade velocity, and credential stuffing attempts—within seconds of occurrence.

The Fraud Problem in Live Games

Online games are high-value targets for fraud. Account takeovers (ATO) allow attackers to steal rare items, drain in-game currency, and sell compromised accounts on gray markets. Item duplication exploits crash game economies. Payment fraud via stolen credit cards generates chargebacks that cost studios revenue and merchant reputation.

The challenge is that fraudulent behavior often looks like legitimate player activity—until you examine it in context. A player logging in from a new country isn't suspicious. A player logging in from Brazil five minutes after logging in from Germany is a clear ATO signal.

Traditional fraud detection runs daily or hourly batch jobs against a SIEM. By the time a flagged account is reviewed, the attacker has already liquidated the stolen inventory. Real-time streaming detection with RisingWave closes this window to seconds.

Ingesting Authentication and Transaction Events

CREATE SOURCE auth_events (
    player_id       BIGINT,
    session_id      VARCHAR,
    event_type      VARCHAR,    -- 'login', 'logout', 'password_change', 'failed_login'
    ip_address      VARCHAR,
    country_code    VARCHAR,
    device_id       VARCHAR,
    user_agent      VARCHAR,
    event_time      TIMESTAMPTZ
)
WITH (
    connector = 'kafka',
    topic = 'auth-events',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON;

CREATE SOURCE trade_events (
    player_id       BIGINT,
    trade_id        VARCHAR,
    event_type      VARCHAR,    -- 'trade_initiated', 'trade_completed', 'item_listed', 'item_sold'
    item_id         VARCHAR,
    item_value_usd  DOUBLE PRECISION,
    counterparty_id BIGINT,
    trade_time      TIMESTAMPTZ
)
WITH (
    connector = 'kafka',
    topic = 'trade-events',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON;

Impossible Login Detection (Account Takeover Signal)

A player cannot physically travel between continents in under 10 minutes. This materialized view uses a SESSION window to group login events for each player into activity sessions and flags impossible geographic transitions:

CREATE MATERIALIZED VIEW suspicious_logins AS
SELECT
    window_start,
    window_end,
    player_id,
    COUNT(DISTINCT country_code)    AS countries_in_session,
    COUNT(DISTINCT ip_address)      AS distinct_ips,
    COUNT(DISTINCT device_id)       AS distinct_devices,
    COUNT(*) FILTER (WHERE event_type = 'failed_login')  AS failed_attempts,
    MAX(event_time)                 AS last_event,
    MIN(event_time)                 AS first_event
FROM SESSION(auth_events, event_time, INTERVAL '10 minutes')
WHERE event_type IN ('login', 'failed_login')
GROUP BY window_start, window_end, player_id
HAVING COUNT(DISTINCT country_code) > 1
    OR COUNT(*) FILTER (WHERE event_type = 'failed_login') >= 5;

When a player appears in multiple countries within the same 10-minute session, or accumulates 5+ failed logins, this view captures the signal immediately.

Abnormal Trade Velocity Detection

Item farming bots and economy exploiters exhibit characteristic behavior: unusually high trade volume in a short window. This view tracks trade velocity per player per 15-minute window:

CREATE MATERIALIZED VIEW trade_velocity_anomalies AS
SELECT
    window_start,
    window_end,
    player_id,
    COUNT(*) FILTER (WHERE event_type = 'trade_completed')              AS trades_completed,
    COUNT(DISTINCT counterparty_id)                                     AS unique_counterparties,
    SUM(item_value_usd) FILTER (WHERE event_type = 'trade_completed')   AS trade_value_usd,
    COUNT(DISTINCT item_id)                                             AS unique_items_traded,
    ROUND(
        COUNT(*) FILTER (WHERE event_type = 'trade_completed')::NUMERIC
        / NULLIF(COUNT(DISTINCT counterparty_id), 0),
    2)                                                                  AS trades_per_counterparty
FROM TUMBLE(trade_events, trade_time, INTERVAL '15 minutes')
GROUP BY window_start, window_end, player_id
HAVING COUNT(*) FILTER (WHERE event_type = 'trade_completed') > 20
    OR SUM(item_value_usd) FILTER (WHERE event_type = 'trade_completed') > 500;

A human player rarely completes 20+ trades in 15 minutes. When trades_per_counterparty is very high (a player repeatedly trading with themselves or a small network), it suggests a gold-farming ring or item duplication exploit.

Credential Stuffing Detection

Credential stuffing attacks generate bursts of failed logins across many accounts from a small set of IP addresses:

CREATE MATERIALIZED VIEW ip_threat_signals AS
SELECT
    window_start,
    window_end,
    ip_address,
    COUNT(DISTINCT player_id)                                           AS targeted_accounts,
    COUNT(*) FILTER (WHERE event_type = 'failed_login')                 AS failed_logins,
    COUNT(*) FILTER (WHERE event_type = 'login')                        AS successful_logins,
    ROUND(
        COUNT(*) FILTER (WHERE event_type = 'failed_login')::NUMERIC
        / NULLIF(COUNT(*), 0) * 100,
    2)                                                                  AS failure_rate_pct
FROM TUMBLE(auth_events, event_time, INTERVAL '5 minutes')
GROUP BY window_start, window_end, ip_address
HAVING COUNT(DISTINCT player_id) > 10
    AND COUNT(*) FILTER (WHERE event_type = 'failed_login') > 20;

An IP targeting 10+ accounts with 20+ failed logins in 5 minutes is almost certainly credential stuffing. Flag the IP, trigger CAPTCHA, and alert your security team—all via a downstream Kafka sink.

Comparison: Fraud Detection Approaches

Routing Fraud Signals to Your Response Platform

CREATE SINK fraud_signals_to_kafka
FROM suspicious_logins
WITH (
    connector = 'kafka',
    topic = 'fraud-signals',
    properties.bootstrap.server = 'kafka:9092'
)
FORMAT PLAIN ENCODE JSON;

Your fraud response platform consumes the fraud-signals topic and triggers automatic account holds, forces re-authentication, and creates review tickets—all without human intervention for high-confidence signals.

Building a Fraud Risk Score Reference

Store player risk scores updated by multiple signals:

CREATE TABLE player_risk_profiles (
    player_id           BIGINT PRIMARY KEY,
    ato_risk_score      INT,     -- 0-100
    economy_risk_score  INT,
    payment_risk_score  INT,
    last_updated        TIMESTAMPTZ
);

Downstream services update this table as signals fire, and your fraud team queries it directly via the PostgreSQL interface to prioritize investigation queues.

FAQ

Q: How do I avoid false positives for legitimate VPN users? A: Add a vpn_detected boolean to auth events using an IP intelligence service. Filter out known VPN/proxy IPs from the impossible login detection logic. Also consider grace-listing players with a history of multi-country logins (frequent travelers) using a whitelist table.

Q: Can RisingWave automatically ban accounts, or just flag them? A: RisingWave is the analytics engine—it surfaces signals. Automated account actions happen in your game platform via a consumer of the fraud signals Kafka topic. Keep the response logic separate from the detection logic for auditability and easy threshold tuning.

Q: How do we handle item duplication exploits that don't show up in trades? A: Publish inventory change events (item created, item deleted) to a separate Kafka topic. A materialized view that tracks net item creation per item_id will flag item IDs where COUNT(created) >> COUNT(destroyed)—a clear duplication signal.

Q: What about ML-based fraud detection—does RisingWave support that? A: RisingWave handles the feature computation layer (aggregations, velocity metrics, behavioral signals). Feed these features via the PostgreSQL interface into an ML model serving layer (Seldon, BentoML, or a custom service), then publish ML risk scores back to RisingWave via Kafka for correlation with rule-based signals.

Q: How do we ensure the fraud detection system itself isn't a performance bottleneck? A: RisingWave's incremental materialized view maintenance means only changed rows are recomputed. The fraud views add minimal overhead to the main game data pipeline. For very high-traffic games, partition Kafka topics by player_id hash to distribute computation across RisingWave compute nodes.

Get Started

Protect your game economy and players with real-time fraud detection:

• Documentation: docs.risingwave.com/get-started
• Community: Join the conversation on RisingWave Slack