Introduction
A user opens three AI agents on a Tuesday morning. The shopping agent has a $100 session budget for groceries. The travel agent has a $150 session budget for a weekend trip. The finance agent has an $80 session budget for paying bills. Each agent respects its own session cap. By lunchtime, the user has spent $235 total, comfortably under each session limit, but $35 over their personal $200 daily cap.
Single-session budget enforcement misses the global view. As soon as a user runs more than one AI agent in parallel, the only number that matters is the sum across every session, every agent, every merchant category. That number changes every time any agent makes a charge, so the enforcement layer has to recompute it in real time, before the next pre-auth call returns.
This is exactly the kind of problem streaming SQL was built for. A streaming database maintains incrementally updated materialized views over the payment stream, so the global daily spend per user is always fresh. The pre-auth endpoint becomes a single SQL query that compares the proposed charge against the user's remaining budget, and the answer is correct down to the last hold or refund.
This post walks through a working pipeline on RisingWave v2.8.0. We model hierarchical budgets, ingest payment events with hold, captured, and refunded states, build four materialized views, and embed real verified output. By the end you will have a pre-auth budget check that fires in milliseconds and an alert view that flags any user approaching their global cap.
Why Single-Session Limits Are Insufficient
AI agent platforms typically launch with session-scoped budgets. The product reasoning is intuitive: a session is the unit of intent (book a trip, buy groceries, pay bills), so a budget tied to a session is easy to explain and easy to display in the UI.
The problem appears as soon as users run sessions in parallel. Consider a user with a $200 daily personal cap who opens three agents:
- Shopping agent, session cap $100, spends $80
- Travel agent, session cap $150, spends $135
- Finance agent, session cap $80, spends $20
Total committed: $235. Each session is well under its own cap. The user is $35 over their personal daily limit, and nobody noticed.
Three structural issues cause this:
- Locality of state. Session budgets live with the session worker. There is no shared view of total spend across sessions unless something explicitly aggregates the events.
- Race conditions on parallel charges. Two agents can both call pre-auth in the same millisecond. If each only checks its own session cap, both succeed even when the combined charge would breach the global cap.
- Refunds and holds drift the books. A held charge that later releases should free up budget. A captured charge that later refunds should free up budget. Without a stream-aware aggregation, the per-user "spent today" value is always either stale or wrong.
The fix is straightforward in concept: maintain one source of truth for per-user spend in real time, and check it on every pre-auth. The implementation is where most teams stall, because hand-rolling a "stateful, fault-tolerant, low-latency aggregation across millions of events" service is a real engineering project.
The Multi-Session Spending Problem
Let's make the problem concrete. Here are the actors in a typical AI agent payment flow:
- User. Owns the global daily, weekly, and monthly cap. The user's identity persists across sessions and across agents.
- Agent. A specific AI agent type: shopping, travel, finance, food, and so on. May have its own per-agent cap (different from per-session).
- Session. A bounded interaction with one agent. Has a session cap. Lives for minutes to hours.
- Payment. A money movement initiated by an agent inside a session. Has a status: hold (auth only, money reserved), captured (final), or refunded (money returned).
The events that flow through this system are not just "a charge happened." They include:
- Pre-auth requests from agents asking "may I charge $X?"
- Hold events when a charge is authorized but not yet captured
- Capture events when the merchant settles
- Refund events when the merchant or user reverses
- Session lifecycle events when a session opens or closes
Single-session enforcement only sees session-scoped events. The streaming SQL approach sees every event from every session and aggregates them on the user dimension, so the answer to "how much has user_001 committed today, across every agent they are running?" is always one materialized view lookup away.
A Hierarchical Budget Model
Production agent platforms layer budgets so that no single tier becomes the only line of defense. A four-tier model that works well:
| Tier | Owner | Example cap | Purpose |
| Session | Single conversation | $100 / session | Stops a single runaway agent task |
| Per-agent | One agent type for the user | $400 / day for shopping | Limits any one agent kind |
| Per-user | The user globally | $200 / day | The wallet-level guarantee |
| Per-merchant-category | Spend in one MCC | $50 / day for gambling | Compliance and risk policies |
A pre-auth call must pass every tier. The cheapest tier (per-user daily) is usually the binding constraint, because it has the smallest absolute number relative to potential parallel spend. The most expensive tier (per-merchant-category) catches edge cases where compliance rules differ from spend rules.
The schema for this post focuses on three of the four tiers (session, user-daily, user-weekly, user-monthly). Per-merchant-category is a straightforward extension; we sketch it in the conclusion.
Tables
We use three input tables, all prefixed aap13_ so they don't collide with anything else:
CREATE TABLE aap13_user_budgets (
user_id VARCHAR PRIMARY KEY,
daily_cap DECIMAL NOT NULL,
weekly_cap DECIMAL NOT NULL,
monthly_cap DECIMAL NOT NULL
);
CREATE TABLE aap13_agent_session_caps (
session_id VARCHAR PRIMARY KEY,
user_id VARCHAR NOT NULL,
agent_id VARCHAR NOT NULL,
session_cap DECIMAL NOT NULL,
created_at TIMESTAMPTZ NOT NULL
);
CREATE TABLE aap13_payments (
payment_id VARCHAR PRIMARY KEY,
user_id VARCHAR NOT NULL,
agent_id VARCHAR NOT NULL,
session_id VARCHAR NOT NULL,
amount DECIMAL NOT NULL,
status VARCHAR NOT NULL, -- 'hold' | 'captured' | 'refunded'
payment_time TIMESTAMPTZ NOT NULL
);
In production, aap13_payments is fed by a Kafka source connected to the payment processor's event stream. For this tutorial, we insert directly so the pipeline can be reproduced without infrastructure setup.
Sample data
Eight users with realistic mixed cap sizes:
INSERT INTO aap13_user_budgets VALUES
('user_001', 200.00, 1000.00, 3500.00),
('user_002', 500.00, 2500.00, 8000.00),
('user_003', 100.00, 500.00, 1800.00),
('user_004', 300.00, 1500.00, 5000.00),
('user_005', 1000.00, 5000.00, 15000.00),
('user_006', 150.00, 750.00, 2500.00),
('user_007', 400.00, 2000.00, 6500.00),
('user_008', 250.00, 1200.00, 4000.00);
Sixteen agent sessions across the eight users, covering shopping, travel, finance, and food agents:
INSERT INTO aap13_agent_session_caps VALUES
('sess_a01', 'user_001', 'shopping_agent', 100.00, '2026-05-06 09:00:00+00'),
('sess_a02', 'user_001', 'travel_agent', 150.00, '2026-05-06 10:30:00+00'),
('sess_a03', 'user_001', 'finance_agent', 80.00, '2026-05-06 12:00:00+00'),
('sess_b01', 'user_002', 'shopping_agent', 300.00, '2026-05-06 08:00:00+00'),
('sess_b02', 'user_002', 'travel_agent', 400.00, '2026-05-06 11:00:00+00'),
('sess_c01', 'user_003', 'shopping_agent', 60.00, '2026-05-06 09:30:00+00'),
('sess_c02', 'user_003', 'food_agent', 50.00, '2026-05-06 13:00:00+00'),
('sess_d01', 'user_004', 'shopping_agent', 200.00, '2026-05-06 07:45:00+00'),
('sess_d02', 'user_004', 'travel_agent', 250.00, '2026-05-06 11:30:00+00'),
('sess_e01', 'user_005', 'finance_agent', 500.00, '2026-05-06 06:00:00+00'),
('sess_e02', 'user_005', 'shopping_agent', 600.00, '2026-05-06 10:00:00+00'),
('sess_f01', 'user_006', 'food_agent', 80.00, '2026-05-06 12:30:00+00'),
('sess_f02', 'user_006', 'shopping_agent', 100.00, '2026-05-06 14:00:00+00'),
('sess_g01', 'user_007', 'travel_agent', 300.00, '2026-05-06 08:30:00+00'),
('sess_h01', 'user_008', 'shopping_agent', 150.00, '2026-05-06 09:15:00+00'),
('sess_h02', 'user_008', 'finance_agent', 120.00, '2026-05-06 11:45:00+00');
Thirty-two payments covering holds, captures, and refunds. Several users have spend that approaches or exceeds their daily cap when summed across sessions:
INSERT INTO aap13_payments VALUES
-- user_001: $200 cap, three sessions, ends up over
('pay_001', 'user_001', 'shopping_agent', 'sess_a01', 45.00, 'captured', '2026-05-06 09:05:00+00'),
('pay_002', 'user_001', 'shopping_agent', 'sess_a01', 35.00, 'captured', '2026-05-06 09:25:00+00'),
('pay_003', 'user_001', 'travel_agent', 'sess_a02', 120.00, 'captured', '2026-05-06 10:45:00+00'),
('pay_004', 'user_001', 'finance_agent', 'sess_a03', 20.00, 'hold', '2026-05-06 12:10:00+00'),
-- user_002: well under cap, with one refund
('pay_005', 'user_002', 'shopping_agent', 'sess_b01', 180.00, 'captured', '2026-05-06 08:15:00+00'),
('pay_006', 'user_002', 'travel_agent', 'sess_b02', 220.00, 'captured', '2026-05-06 11:20:00+00'),
('pay_007', 'user_002', 'shopping_agent', 'sess_b01', 40.00, 'refunded', '2026-05-06 08:45:00+00'),
-- ... 25 more rows covering the remaining users
;
The full insert is in the verification section below; it includes 32 events spanning every status type so the materialized views can demonstrate the hold-vs-captured-vs-refunded math.
Real-Time Aggregation with Streaming SQL
Now the materialized views. Each one is incrementally maintained: when a new row hits aap13_payments, RisingWave updates the affected aggregate in milliseconds, with no scheduled refresh and no batch job.
Session-level spend
The first view rolls payments up by session. We track three flavors of spend: committed_spend (holds plus captures, which is what counts against the cap), captured_spend (already settled), and refunded_amount (for visibility):
CREATE MATERIALIZED VIEW aap13_session_spend_mv AS
SELECT
p.session_id,
s.user_id,
s.agent_id,
s.session_cap,
SUM(CASE WHEN p.status IN ('hold','captured') THEN p.amount ELSE 0 END) AS committed_spend,
SUM(CASE WHEN p.status = 'captured' THEN p.amount ELSE 0 END) AS captured_spend,
SUM(CASE WHEN p.status = 'refunded' THEN p.amount ELSE 0 END) AS refunded_amount,
COUNT(*) AS payment_count
FROM aap13_agent_session_caps s
LEFT JOIN aap13_payments p ON p.session_id = s.session_id
GROUP BY p.session_id, s.user_id, s.agent_id, s.session_cap, s.session_id;
Verified output (16 rows for our 16 sessions):
session_id | user_id | agent_id | session_cap | committed_spend | captured_spend | refunded_amount | payment_count
------------+----------+----------------+-------------+-----------------+----------------+-----------------+---------------
sess_a01 | user_001 | shopping_agent | 100 | 80.00 | 80.00 | 10.00 | 3
sess_a02 | user_001 | travel_agent | 150 | 135.00 | 120.00 | 0 | 2
sess_a03 | user_001 | finance_agent | 80 | 20.00 | 0 | 0 | 1
sess_b01 | user_002 | shopping_agent | 300 | 240.00 | 180.00 | 40.00 | 3
sess_b02 | user_002 | travel_agent | 400 | 220.00 | 220.00 | 0 | 1
sess_c01 | user_003 | shopping_agent | 60 | 63.00 | 55.00 | 0 | 2
sess_c02 | user_003 | food_agent | 50 | 42.00 | 42.00 | 0 | 1
sess_d01 | user_004 | shopping_agent | 200 | 190.00 | 190.00 | 0 | 2
sess_d02 | user_004 | travel_agent | 250 | 135.00 | 100.00 | 0 | 2
sess_e01 | user_005 | finance_agent | 500 | 380.00 | 380.00 | 55.00 | 2
sess_e02 | user_005 | shopping_agent | 600 | 515.00 | 425.00 | 0 | 2
sess_f01 | user_006 | food_agent | 80 | 62.00 | 62.00 | 0 | 1
sess_f02 | user_006 | shopping_agent | 100 | 96.00 | 78.00 | 10.00 | 3
sess_g01 | user_007 | travel_agent | 300 | 215.00 | 190.00 | 0 | 3
sess_h01 | user_008 | shopping_agent | 150 | 130.00 | 130.00 | 12.00 | 2
sess_h02 | user_008 | finance_agent | 120 | 110.00 | 95.00 | 0 | 2
(16 rows)
Notice sess_c01 for user_003: committed_spend is $63 against a session cap of $60. That session has already breached its own cap. The hold-state payment that pushed it over should have been rejected at pre-auth time; the view shows what would happen without enforcement.
User-level daily spend
This view is the heart of the global enforcement story. It rolls every session up to the user dimension, filtered to today's payments only:
CREATE MATERIALIZED VIEW aap13_user_daily_spend_mv AS
SELECT
user_id,
SUM(CASE WHEN status IN ('hold','captured') THEN amount ELSE 0 END) AS committed_today,
SUM(CASE WHEN status = 'captured' THEN amount ELSE 0 END) AS captured_today,
SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END) AS refunded_today,
COUNT(DISTINCT session_id) AS active_sessions,
COUNT(*) AS payment_count
FROM aap13_payments
WHERE payment_time >= '2026-05-06 00:00:00+00'
AND payment_time < '2026-05-07 00:00:00+00'
GROUP BY user_id;
Verified output:
user_id | committed_today | captured_today | refunded_today | active_sessions | payment_count
----------+-----------------+----------------+----------------+-----------------+---------------
user_001 | 235.00 | 200.00 | 10.00 | 3 | 6
user_002 | 460.00 | 400.00 | 40.00 | 2 | 4
user_003 | 105.00 | 97.00 | 0 | 2 | 3
user_004 | 325.00 | 290.00 | 0 | 2 | 4
user_005 | 895.00 | 805.00 | 55.00 | 2 | 4
user_006 | 158.00 | 140.00 | 10.00 | 2 | 4
user_007 | 215.00 | 190.00 | 0 | 1 | 3
user_008 | 240.00 | 225.00 | 12.00 | 2 | 4
(8 rows)
This is the global view that single-session enforcement would never produce. user_001 has spent $235 across three sessions; user_004 has spent $325 across two; user_006 has spent $158 across two. Each of those numbers is the sum of every hold and capture from every session that user has open.
Budget status with utilization
Now we join aap13_user_daily_spend_mv with aap13_user_budgets to compute remaining budget and utilization percentage:
CREATE MATERIALIZED VIEW aap13_budget_status_mv AS
SELECT
b.user_id,
b.daily_cap,
b.weekly_cap,
b.monthly_cap,
COALESCE(d.committed_today, 0) AS committed_today,
COALESCE(d.captured_today, 0) AS captured_today,
COALESCE(d.refunded_today, 0) AS refunded_today,
COALESCE(d.active_sessions, 0) AS active_sessions,
b.daily_cap - COALESCE(d.committed_today, 0) AS daily_remaining,
ROUND(
COALESCE(d.committed_today, 0) / b.daily_cap * 100,
1
) AS daily_utilization_pct
FROM aap13_user_budgets b
LEFT JOIN aap13_user_daily_spend_mv d ON d.user_id = b.user_id;
The LEFT JOIN means users with zero spend today still appear with committed_today = 0. This is important for the pre-auth endpoint, which should always find a row for any valid user.
Verified output:
user_id | daily_cap | committed_today | daily_remaining | daily_utilization_pct | active_sessions
----------+-----------+-----------------+-----------------+-----------------------+-----------------
user_001 | 200.00 | 235.00 | -35.00 | 117.5 | 3
user_004 | 300.00 | 325.00 | -25.00 | 108.3 | 2
user_006 | 150.00 | 158.00 | -8.00 | 105.3 | 2
user_003 | 100.00 | 105.00 | -5.00 | 105.0 | 2
user_008 | 250.00 | 240.00 | 10.00 | 96.0 | 2
user_002 | 500.00 | 460.00 | 40.00 | 92.0 | 2
user_005 | 1000.00 | 895.00 | 105.00 | 89.5 | 2
user_007 | 400.00 | 215.00 | 185.00 | 53.8 | 1
(8 rows)
Four users are over their daily cap. Three more are above 90 percent utilization. Only user_007 is comfortably below the warning threshold. Without cross-session aggregation, none of this would have been visible until end-of-day reconciliation.
Breach alerts
For monitoring and proactive notifications, a separate view filters down to users at >=75% utilization and tags them with an alert level:
CREATE MATERIALIZED VIEW aap13_breach_alerts_mv AS
SELECT
user_id,
daily_cap,
committed_today,
daily_remaining,
daily_utilization_pct,
active_sessions,
CASE
WHEN daily_utilization_pct >= 100 THEN 'BREACH'
WHEN daily_utilization_pct >= 90 THEN 'CRITICAL'
ELSE 'WARNING'
END AS alert_level
FROM aap13_budget_status_mv
WHERE daily_utilization_pct >= 75;
Verified output:
user_id | daily_cap | committed_today | daily_utilization_pct | active_sessions | alert_level
----------+-----------+-----------------+-----------------------+-----------------+-------------
user_001 | 200.00 | 235.00 | 117.5 | 3 | BREACH
user_004 | 300.00 | 325.00 | 108.3 | 2 | BREACH
user_006 | 150.00 | 158.00 | 105.3 | 2 | BREACH
user_003 | 100.00 | 105.00 | 105.0 | 2 | BREACH
user_008 | 250.00 | 240.00 | 96.0 | 2 | CRITICAL
user_002 | 500.00 | 460.00 | 92.0 | 2 | CRITICAL
user_005 | 1000.00 | 895.00 | 89.5 | 2 | WARNING
(7 rows)
This view sinks naturally to a Kafka topic that feeds your alerting and notification system. Customer support gets a heads-up before the user hits a hard reject; the user can be prompted to raise their cap or close stale sessions.
Pre-Auth Budget Check Pipeline
The point of all this aggregation is to power a sub-second pre-auth decision. When an agent calls the payment service to ask "may I charge $X for user U?", the service runs a single query against aap13_budget_status_mv and returns APPROVE or REJECT.
Here is the pre-auth query for a $50 charge against three of the users in our dataset:
SELECT
bs.user_id,
50.00 AS proposed_amount,
bs.daily_remaining,
CASE
WHEN bs.daily_remaining >= 50.00 THEN 'APPROVE'
ELSE 'REJECT_BUDGET_EXCEEDED'
END AS decision
FROM aap13_budget_status_mv bs
WHERE bs.user_id IN ('user_001','user_002','user_005');
Verified output:
user_id | proposed_amount | daily_remaining | decision
----------+-----------------+-----------------+------------------------
user_001 | 50.00 | -35.00 | REJECT_BUDGET_EXCEEDED
user_005 | 50.00 | 105.00 | APPROVE
user_002 | 50.00 | 40.00 | REJECT_BUDGET_EXCEEDED
(3 rows)
user_001 is already over cap, so rejected. user_002 has $40 left and a $50 charge would push them over, so rejected. user_005 has $105 left, comfortably approved.
Production wiring
In a production AI agent platform, the pre-auth flow looks like this:
- Agent emits a pre-auth request:
{user_id, amount, agent_id, session_id, merchant_category}. - Payment service queries the budget materialized views by user_id (point lookup, sub-millisecond).
- Payment service runs the four-tier check: session_cap, per-agent_cap, per-user daily/weekly/monthly, per-merchant-category.
- If APPROVE, payment service inserts a
holdrow intoaap13_paymentsand returns success. - The materialized views update incrementally; the next pre-auth call sees the new committed amount.
- When the merchant settles, an event flips the row to
captured. When the merchant refunds, an event flips it torefunded.
The whole loop completes in tens of milliseconds because RisingWave's incremental view maintenance does the hard work in advance: every payment event already updated the aggregates the moment it arrived.
You can connect to the database from your payment service the same way you connect to Postgres, since RisingWave speaks the Postgres wire protocol. Existing client libraries (psycopg, JDBC, pgx) work out of the box.
Handling Holds, Captures, and Refunds in the Aggregation
Real payment systems distinguish three states. The committed-spend math has to handle each one correctly:
- Hold. Money is reserved on the user's funding source but not yet captured. Counts against the budget because we have committed to it.
- Captured. The merchant has settled; money has actually moved. Still counts against the budget.
- Refunded. Money has been returned. Should NOT count against the budget; in fact, it frees up budget if the original charge had been captured.
The pattern we use in aap13_session_spend_mv and aap13_user_daily_spend_mv is a CASE expression inside a SUM:
SUM(CASE WHEN status IN ('hold','captured') THEN amount ELSE 0 END) AS committed_today
This is the safe definition for budget enforcement: any payment that is currently committed (held or captured but not refunded) counts. Refunded payments are subtracted by the simple act of not adding them.
A subtlety: if a payment goes from captured to refunded, the row is updated, not deleted. The materialized view sees the row's new state because RisingWave correctly handles upserts on the source table. Test this in your own environment by updating a payment row and watching the budget status view shift.
For platforms that issue refunds as separate rows (rather than updating the original), use a slightly different rollup that subtracts refunded amounts:
SUM(amount) FILTER (WHERE status IN ('hold','captured'))
- SUM(amount) FILTER (WHERE status = 'refunded') AS net_committed
Either approach works; pick the one that matches how your payment processor models refunds.
FAQ
Why aren't per-session spending limits enough for AI agents?
A user typically runs several AI agent sessions in parallel: a shopping agent, a travel agent, a finance agent, each with its own session-scoped budget. Per-session caps only see one slice of activity. If three sessions each spend 80 percent of their session cap, the user can blow far past a global daily cap without any single session noticing. Real-world budget enforcement requires aggregating spend across every session a user has, in real time, before each charge is authorized.
How do hierarchical agent budgets work?
Hierarchical budgets stack multiple constraints: a session cap (lowest tier), a per-agent cap (sums sessions for one agent kind), a per-user cap (sums every agent for the user, the global view), and optional per-merchant-category caps. A payment is authorized only when it passes every tier. Streaming SQL maintains one materialized view per tier, joins them at the decision endpoint, and rejects the charge if any tier would breach its cap.
How is real-time spending aggregated across multiple agent sessions?
Each payment event flows into a payments stream. A streaming database such as RisingWave maintains incremental materialized views that group spend by session, by agent, and by user. When a new payment arrives, the relevant rows update in milliseconds, with no scheduled job and no batch refresh. The same machinery handles holds, captures, and refunds by switching the SUM expression on the status column.
How can a streaming database enforce budgets at payment time?
Expose the budget status materialized view through the database's SQL interface. The pre-auth endpoint runs a single point-lookup against the view, compares the user's remaining daily budget to the proposed charge, and returns APPROVE or REJECT in tens of milliseconds. Because the view is incrementally maintained, the remaining-budget value already reflects every prior payment from every session, including holds and refunds.
Conclusion
Single-session budgets are easy to implement and easy to explain, but they are not enough once users run AI agents in parallel. The only correct enforcement model is one that aggregates spend across every session, every agent, every merchant category, in real time, on every pre-auth call.
Streaming SQL collapses this from a custom distributed system project into a handful of materialized views. The pipeline in this post:
- Models hierarchical budgets (session, per-agent, per-user, per-merchant-category)
- Aggregates spend incrementally as payment events arrive
- Handles hold, capture, and refund states correctly
- Powers a sub-second pre-auth decision endpoint
- Surfaces breach alerts before users hit a hard reject
The same machinery extends naturally to weekly and monthly caps (just change the time filter), per-merchant-category caps (add a column and a GROUP BY), and per-agent caps (group by agent_id instead of user_id). Every new tier is one more materialized view and one more clause in the pre-auth check.
All SQL in this post runs on RisingWave v2.8.0 and produces exactly the output shown. You can replicate the pipeline on your own instance by following the quickstart guide. For high-throughput payment platforms, see the scaling guide for recommendations on partitioning the payments source by user_id.
Ready to enforce global agent budgets in real time? Try RisingWave Cloud free →
Join our Slack community to ask questions and connect with other stream processing engineers building AI agent infrastructure.
