Credit Card Authorization Decisioning with Streaming SQL

Credit card authorization decisioning with streaming SQL means enriching every authorization request with live account state, spending velocity, and behavioral context in milliseconds — enabling issuers to approve more legitimate transactions, decline more fraudulent ones, and reduce false declines that cost merchants and cardholders alike.

Authorization: A Millisecond Business

Credit card authorizations must complete in under 300 milliseconds end-to-end across networks. Within that window, the issuing bank must verify account standing, check available credit, apply fraud scores, and make an approve/decline decision. For large issuers processing millions of transactions daily, this is an extraordinarily demanding real-time computation problem.

Traditional authorization systems use in-memory rule engines preloaded with static account data refreshed on slow cycles. This creates a class of errors: accounts closed hours ago are still approved; spending limits updated by the cardholder in the app don't reflect immediately; fraud velocity patterns accumulate unseen until the next refresh.

A streaming SQL layer backed by continuously updated materialized views solves this by maintaining always-current account state and spending context directly queryable by the authorization engine.

Architecture

A streaming authorization enrichment system with RisingWave:

1. Authorization request arrives at the issuer's processing system
2. Enrichment lookup: the authorization engine queries RisingWave for live account state, spending velocity, and risk signals
3. Decision: the engine combines enriched context with its scoring model
4. Feedback loop: authorization outcomes are written back to Kafka, updating behavioral models

RisingWave serves as the real-time context store — always-fresh materialized views powered by continuous ingestion of transaction events and account changes.

Setting Up Account State Tracking

-- Transaction events from card network processing
CREATE SOURCE card_transactions (
    transaction_id  VARCHAR,
    card_number_hash VARCHAR,
    account_id      VARCHAR,
    merchant_id     VARCHAR,
    merchant_mcc    VARCHAR,
    amount          NUMERIC,
    currency        VARCHAR,
    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;

-- Account state from core banking via CDC
CREATE SOURCE account_state (
    account_id       VARCHAR,
    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'
);

Spending Velocity Materialized View

Maintain a continuously updated velocity profile per account across multiple time windows:

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,
    MAX(country_code) FILTER (
        WHERE txn_time = MAX(txn_time)
            OVER (PARTITION BY account_id)
    )                                              AS last_country
FROM card_transactions
WHERE auth_outcome = 'approved'
GROUP BY account_id;

Authorization engines query this view on every incoming request to retrieve a cardholder's current spend context — instantly, without scanning raw transaction history.

Risk Signal Aggregation with Temporal Joins

Enrich authorization signals with current account state:

CREATE MATERIALIZED VIEW authorization_risk_context AS
SELECT
    v.account_id,
    v.txns_last_1h,
    v.spend_last_1h,
    v.txns_last_24h,
    v.spend_last_24h,
    v.distinct_countries_6h,
    a.credit_limit,
    a.available_credit,
    a.account_status,
    a.risk_score,
    CASE
        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 FOR SYSTEM_TIME AS OF NOW() AS a
    ON v.account_id = a.account_id;

The authorization system queries authorization_risk_context with a simple WHERE account_id = ? and receives a complete risk profile in single-digit milliseconds.

Batch vs. Streaming Authorization Enrichment

FAQ

Q: How does RisingWave handle the sub-millisecond latency requirement for authorization? A: RisingWave is used as a pre-computed context store. The authorization engine reads from materialized views via the PostgreSQL interface — these reads are single-row lookups indexed by account_id, typically completing in 1–3ms. The view itself is maintained continuously in the background.

Q: Can we serve authorization decisions directly from RisingWave? A: RisingWave serves as an enrichment and scoring context layer. The actual authorization decision (approve/decline) is made by the authorization engine, which queries RisingWave for context. The engine combines this with its own scoring models and compliance rules.

Q: How do we handle high-volume events during peak shopping periods? A: RisingWave is horizontally scalable. During high-volume periods, additional compute nodes can be added to the cluster, and the streaming ingestion pipeline scales with Kafka partition count.

Q: What happens if the RisingWave cluster has downtime during authorization? A: The authorization engine should treat RisingWave enrichment as a best-effort layer with a tight timeout. If the lookup fails or times out, the engine falls back to base account state from the core banking system. This degrades enrichment quality briefly but does not block authorization processing.

Q: How do we handle card-not-present vs. card-present transactions differently? A: The channel field in the transaction source distinguishes transaction types. Separate materialized views or conditional logic within a single view can apply different velocity thresholds and risk signals based on channel, since CNP fraud patterns differ significantly from in-person fraud.

Get Started

Modernize your authorization enrichment stack with always-fresh streaming context:

• Get started in minutes: docs.risingwave.com/get-started
• Join the fintech community: risingwave.com/slack