Stablecoins lose their peg silently—oracle prices drift, reserve ratios shift, and by the time an alert fires, arbitrage windows have closed and losses have compounded. With streaming SQL you can detect depeg events within seconds of their first on-chain signal.
Why Stablecoin Depeg Detection Matters in DeFi
In May 2022, TerraUSD lost its $1 peg in under 72 hours. More quietly, USDC briefly traded at $0.87 during the Silicon Valley Bank weekend in March 2023. Each event triggered cascading liquidations across lending protocols, wiped out LP positions, and caused automated market makers to route trades through degraded price feeds.
Traditional monitoring stacks rely on polling—a cron job fetches oracle prices every minute, compares them against a threshold, and fires a webhook. At that latency, a depeg that started three blocks ago has already propagated through multiple DEX pools before any alert lands.
A peg deviation of even 0.3% on a stablecoin pair is meaningful. At 1% deviation, arbitrage bots are already active. At 2%, circuit breakers on lending protocols like Aave and Compound begin to restrict borrowing. At 5%, liquidation cascades become possible. Detecting at the 0.3% threshold, rather than the 2% threshold, gives protocols and users time to respond rather than react.
Three signals matter most for depeg detection:
- Oracle price divergence — the price reported by Chainlink or Pyth deviates from $1.00 by more than a defined threshold.
- DEX spot price divergence — on-chain swap events on Curve or Uniswap show the stablecoin trading below peg.
- Reserve ratio change — for algorithmic or partially-backed stablecoins, the ratio of collateral to circulating supply drops below a safety floor.
How Streaming SQL Solves This
RisingWave is a PostgreSQL-compatible streaming database that ingests events from Kafka, processes them continuously, and maintains materialized views that are always up to date. Instead of polling a price feed, you write a SQL query once and RisingWave keeps the result current as new data arrives.
This means your depeg detection logic runs as a standing query, not a scheduled job. When a swap event pushes the Curve 3pool price below $0.997, the alert materializes in under a second—before the next Ethereum block is finalized.
Building It Step by Step
Step 1: Connect the Data Source
Create a source that reads oracle price updates and DEX swap events from Kafka topics:
CREATE SOURCE oracle_prices (
token_address VARCHAR,
price_usd NUMERIC,
source VARCHAR, -- 'chainlink', 'pyth', 'band'
block_number BIGINT,
block_ts TIMESTAMPTZ
) WITH (
connector = 'kafka',
topic = 'oracle.price.updates',
properties.bootstrap.server = 'kafka:9092'
) FORMAT PLAIN ENCODE JSON;
CREATE SOURCE dex_swaps (
pool_address VARCHAR,
token_in VARCHAR,
token_out VARCHAR,
amount_in NUMERIC,
amount_out NUMERIC,
price_impact NUMERIC,
block_ts TIMESTAMPTZ
) WITH (
connector = 'kafka',
topic = 'dex.swap.events',
properties.bootstrap.server = 'kafka:9092'
) FORMAT PLAIN ENCODE JSON;
Step 2: Build the Core Materialized View
Compute a rolling 1-minute weighted average price for each stablecoin across all oracle sources:
CREATE MATERIALIZED VIEW stablecoin_prices AS
SELECT
token_address,
AVG(price_usd) AS avg_price,
MIN(price_usd) AS min_price,
MAX(price_usd) AS max_price,
STDDEV(price_usd) AS price_stddev,
COUNT(*) AS oracle_count,
MAX(block_ts) AS last_updated,
ABS(AVG(price_usd) - 1.0) AS peg_deviation,
ABS(AVG(price_usd) - 1.0) / 1.0 * 100 AS peg_deviation_pct
FROM oracle_prices
WHERE block_ts > NOW() - INTERVAL '1 minute'
GROUP BY token_address;
Also maintain a view of DEX spot prices derived from swap events:
CREATE MATERIALIZED VIEW dex_spot_prices AS
SELECT
pool_address,
token_in,
token_out,
amount_out / NULLIF(amount_in, 0) AS spot_price,
price_impact,
block_ts
FROM dex_swaps
WHERE block_ts > NOW() - INTERVAL '30 seconds'
AND amount_in > 10000; -- filter dust trades
Step 3: Detection Logic and Alerts
Define depeg thresholds and emit alerts when crossed:
CREATE MATERIALIZED VIEW depeg_alerts AS
WITH known_stables AS (
SELECT token_address, symbol
FROM (VALUES
('0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48', 'USDC'),
('0xdAC17F958D2ee523a2206206994597C13D831ec7', 'USDT'),
('0x6B175474E89094C44Da98b954EedeAC495271d0F', 'DAI'),
('0x4Fabb145d64652a948d72533023f6E7A623C7C53', 'BUSD')
) AS t(token_address, symbol)
)
SELECT
p.token_address,
ks.symbol,
p.avg_price,
p.peg_deviation_pct,
p.oracle_count,
p.last_updated,
CASE
WHEN p.peg_deviation_pct >= 5.0 THEN 'CRITICAL'
WHEN p.peg_deviation_pct >= 2.0 THEN 'HIGH'
WHEN p.peg_deviation_pct >= 0.5 THEN 'MEDIUM'
ELSE 'LOW'
END AS severity,
CASE
WHEN p.avg_price < 1.0 THEN 'DEPEG_BELOW'
ELSE 'DEPEG_ABOVE'
END AS direction
FROM stablecoin_prices p
JOIN known_stables ks ON p.token_address = ks.token_address
WHERE p.peg_deviation_pct >= 0.3;
CREATE SINK depeg_alert_sink AS
SELECT * FROM depeg_alerts
WITH (
connector = 'kafka',
topic = 'alerts.stablecoin.depeg',
properties.bootstrap.server = 'kafka:9092'
) FORMAT PLAIN ENCODE JSON;
Step 4: Querying Live Results
Check the current status of all monitored stablecoins in real time:
SELECT
symbol,
ROUND(avg_price, 6) AS price_usd,
ROUND(peg_deviation_pct, 4) AS deviation_pct,
severity,
direction,
oracle_count,
last_updated
FROM depeg_alerts
ORDER BY peg_deviation_pct DESC
LIMIT 10;
Query the historical depeg window for a specific token:
SELECT
DATE_TRUNC('minute', block_ts) AS minute,
AVG(price_usd) AS avg_price,
MIN(price_usd) AS min_price,
MAX(price_usd) AS max_price
FROM oracle_prices
WHERE token_address = '0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48'
AND block_ts > NOW() - INTERVAL '1 hour'
GROUP BY 1
ORDER BY 1;
Comparison Table
| Approach | Detection Latency | Alert Freshness | Engineering Effort |
| Polling cron job | 30–60 seconds | Stale by interval | Low (but unreliable) |
| WebSocket price feed + custom code | 1–5 seconds | Near-real-time | High (state management) |
| RisingWave Streaming SQL | < 1 second | Always current | Low (SQL only) |
| Block-level event subscriptions | < 1 block (~12s) | Per-block | Medium (node required) |
FAQ
What is a safe depeg threshold for production alerts? For operational monitoring, 0.3% is a reasonable early-warning level. A 0.5% threshold maps to a meaningful arbitrage signal. Circuit breakers in lending protocols typically trigger at 2–5%. Using tiered severity levels (LOW/MEDIUM/HIGH/CRITICAL) lets different consumers act on the signal appropriate to their risk tolerance.
How does RisingWave handle oracle source disagreement?
The materialized view aggregates across all sources by default. You can add a filter on the source column to build separate views per oracle provider, then join them to detect cross-oracle divergence—a pattern that often precedes a genuine depeg rather than a single-source data error.
Can this detect reserve ratio changes for algorithmic stablecoins?
Yes. Add a reserve_snapshots source that ingests on-chain reserve data, then join it against the circulating supply in a separate materialized view. Alert when the collateral ratio falls below a configurable floor (e.g., 1.05 for overcollateralized protocols, 1.0 for parity-backed ones).
Key Takeaways
- Peg deviations compound quickly; detection at 0.3% buys meaningful response time compared to the 2% threshold typical in polling-based systems.
- RisingWave materializes the aggregation result continuously so queries always read current state—no cache invalidation or polling interval to manage.
- Tiered alert severity (LOW → CRITICAL) lets downstream systems—liquidation bots, front-end dashboards, PagerDuty—react proportionally rather than flooding operators with noise.
- A single streaming SQL pipeline replaces a polling cron job, a stateful aggregation service, and a threshold evaluation layer.
