# Dune Sui Query Builder

> Build, debug, and optimize DuneSQL queries for Sui blockchain analytics, including pure-SQL pipelines that pull live state directly from Sui RPC nodes. Use this skill whenever the user mentions Dune Analytics, DuneSQL, Sui queries, Move events, on-chain data analysis for Sui, the Pyth oracle, Sui RPC inside SQL, or wants to pull live blockchain state into a Dune query. Also trigger when the user…

- **Type:** Skill
- **Install:** `agentstack add skill-vchrl-dune-sui-query-builder-dune-sui-query-builder`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [vchrl](https://agentstack.voostack.com/s/vchrl)
- **Installs:** 0
- **Category:** [Databases](https://agentstack.voostack.com/c/databases)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [vchrl](https://github.com/vchrl)
- **Source:** https://github.com/vchrl/dune-sui-query-builder
- **Website:** https://unchaindata.xyz/dune-dashboards

## Install

```sh
agentstack add skill-vchrl-dune-sui-query-builder-dune-sui-query-builder
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# Dune Sui Query Builder

Build, debug, and optimize DuneSQL (Trino-based) queries for Sui blockchain analytics. This skill covers Dune's Sui data model, Sui-specific quirks (object-centric architecture, binary types, event JSON parsing, package upgrades), and a class of patterns that goes beyond what the indexed tables alone can do — **chained Sui RPC calls inside SQL via Dune's LiveFetch**, plus oracle-grade pricing via the **Pyth Hermes API** for tokens that aren't in `prices.hour`.

Before writing any query, read `references/sui-data-model.md` for the table catalog and Sui-specific edge cases. For Sui DEX queries (Cetus, Bluefin, DeepBook, Aftermath, Kriya, FlowX, Momentum, BlueMove, Obric), BTCfi work, Walrus storage, or Sui chain stats — read `references/sui-curated-tables.md` first; these often have curated spell tables that bypass raw event archaeology. For Navi or Suilend protocol queries, read `references/protocol-patterns.md` — it covers package archaeology, mislabel investigations, and a fully-validated multi-market on-chain pipeline (V9) that achieves 100% asset coverage across all of Navi's markets (Main + 3 isolated, 48 reserves), priced from Navi's own on-chain oracle, with no third-party indexer. The same file carries the Suilend pack: the liquidation, obligation, and forgive event catalog, protocol-native cToken-vs-underlying pricing as the correctness core, the wrapped-token symbol map, and the IKA bad-debt case study.

For pricing any Sui asset, lead with protocol-emitted USD, then `dex_sui.trades` VWAP for tokens with no oracle feed, then Pyth; do not assume `prices.*` covers Sui (see `references/sui-data-model.md` § Pricing). To check a priced query or build a serving layer, read `references/verification-toolkit.md` and the materialized-view section in `references/sui-data-model.md`.

## Data freshness: numbers in these docs are snapshots, not cache

This skill queries Sui live through the Dune MCP. Every specific number written in these reference files (row counts, credit costs, TVL, bad debt, penalty ratios) is an illustrative snapshot from when that example was run, never a cached value to reuse. When a task needs a current figure, run the query and read the result; do not quote the docs. This applies to the verification toolkit too: the raw-events recount and the stablecoin and penalty cross-checks are procedures to re-run, not results to copy.

## Task Router

Determine which mode applies based on the user's request:

1. **Build** — User describes what data they want in natural language → construct a query from scratch
2. **Debug/Review** — User provides an existing query → analyze correctness, find issues, suggest fixes
3. **Optimize** — User has a working but slow/expensive query → improve performance
4. **Investigate** — User mentions a public Dune dashboard or query and wants to understand or differentiate from it → reverse-engineer + audit

Mode 4 deserves special attention. The most-cited "Navi Protocol" reference dashboard on Dune (Prudentia Labs / mementomori7777, 19 charts) queries **Suilend's** event package, not Navi's — a useful pedagogical example of why package-identity verification matters on Sui. Always reverse-engineer cited reference dashboards before competing with or building on top of them; the package hexes will tell you which protocol the queries actually cover.

## Build Mode

When the user asks for a new query:

1. **Clarify the goal** if ambiguous (e.g., "Do you mean unique depositors or deposit events? Cumulative supply or net flow? Snapshot today or time-series?"). Restate the goal before writing SQL.
2. **Pick the right data source.** Check `references/sui-curated-tables.md` first — 5 curated Sui spell tables exist (`dex_sui.trades`, `sui_tvl.btc_ecosystem`, `sui_daily.stats`, `sui_walrus.base_table`, `cex.addresses`). If a curated table covers the question, use it. For Sui lending and most other protocol-specific Sui analytics, no curated tables exist — decide between:
   - `sui.events` — flows and actions emitted by `event::emit`
   - `sui.objects` — historical state changes per object-version
   - `sui.move_call` — cheap function-call counting
   - `sui.transactions` — gas/success analysis
   - **Live Sui RPC via `http_post`** — when state-as-of-now matters more than historical replay (TVL today, current rates, current asset list)
   - **Pyth Hermes via `http_get`** — when you need oracle prices for tokens not in Dune's `prices.hour`

   Check `references/sui-data-model.md` for the table hierarchy and the LiveFetch section.

3. **Identify all relevant packages.** Sui protocols frequently upgrade and redeploy. A query filtered on only one package ID will silently miss history. Always check for known package upgrades. For Navi and Suilend, see `references/protocol-patterns.md`.

4. **Construct step-by-step:**
   - Start with the base table or the LiveFetch source
   - Apply WHERE filters first: **always include `WHERE date >= ...`** (Sui tables are partitioned by `date`)
   - Filter by `event_type IN (...)` for specific protocol events
   - Decode binary columns (`sender`, `package`, `owner_address`, `object_id`) with `concat('0x', lower(to_hex(col)))`
   - Parse event payloads with `json_extract_scalar(event_json, '$.field_name')`
   - Aggregate and format results
5. **Output the query** using the response format below.

## Debug/Review Mode

When the user provides a query to check:

1. **Verify table choice** — Is `sui.events` the best source, or do they actually need `sui.objects` (state) or `sui.move_call` (function counts) or LiveFetch (current state)?
2. **Check partition filter** — Does the query filter by `date`? Missing date filter = full history scan. This is the #1 cost/timeout issue on Sui queries.
3. **Check binary handling** — A query like `WHERE sender = '0x...'` fails silently (string vs binary). Correct: `WHERE sender = from_hex('...')` (no `0x` prefix inside `from_hex`) or `WHERE concat('0x', lower(to_hex(sender))) = '0x...'`.
4. **Check JSON parsing** — `event_json` is a STRING containing JSON, not native JSON. Must use `json_extract_scalar(event_json, '$.field')`.
5. **Check package coverage** — Does the query account for package upgrades? For multi-package protocols (Navi: 3 packages, Suilend: 1 package but with subtle event/lending_market split), missing packages = missing data eras.
6. **Check decimal handling** — Sui amounts in events are raw integers in native token decimals. SUI = 9, USDC = 6, BTC variants = 8. Some protocols (like Navi) **normalize all amounts to 9 decimals internally regardless of token decimals** — the `total_supply` field in Navi reserves divides by `1e9` for all assets. Verify per protocol; never assume.
7. **Verify the protocol identity.** If the query references public package hexes, cross-check them. The famous mementomori7777 "Navi" dashboard is actually Suilend (`0xf95b06141...::reserve::ReserveAssetDataEvent` is Suilend's, not Navi's). Wrong-protocol queries are surprisingly common.
8. **Output analysis** using the response format below.

## Optimize Mode

When the user has a working but slow query:

1. **Add or tighten date partition filter.** `WHERE date >= CURRENT_DATE - INTERVAL '90' DAY` prunes partitions dramatically.
2. **Replace `event_json` parsing with `event_type` filtering where possible.** String IN lists are pruned at plan time; JSON extraction happens per row.
3. **Use CTEs to pre-filter multi-package unions.** Each branch of a UNION ALL must apply its own date filter and event_type filter, not rely on an outer WHERE.
4. **Avoid `SELECT *` on `sui.transactions` and `sui.objects`** — both have multi-KB string columns (`raw_transaction`, `transaction_json`, `effects_json`, `object_json`, `bcs`).
5. **Use `APPROX_DISTINCT` for high-cardinality counts** when exact precision isn't required.
6. **Group by `date` directly**, not `DATE_TRUNC('day', from_unixtime(timestamp_ms/1000))` — the former is a column read, the latter is two function calls per row.
7. **For LiveFetch pipelines, batch RPC calls** — prefer `sui_multiGetObjects` over 35 separate `sui_getObject` calls. One http_post with 35 IDs in the params is dramatically cheaper than 35 round-trips.

## Investigate Mode

When the user references a public dashboard, query, or claim about a protocol:

1. **Pull the SQL.** Use the Dune MCP `getDuneQuery` to fetch the source. Don't trust the dashboard title.
2. **Decode the package hexes.** A query labeled "Navi" filtering on `0xf95b06141...::reserve::ReserveAssetDataEvent` is actually Suilend. Match each package hex back to the protocol it belongs to via:
   - The protocol's docs / SDK repo
   - DefiLlama protocol pages
   - Searching `sui.move_package` for the deployer
3. **Check date-range coverage.** A "Navi historical" dashboard that only shows data after Feb 2026 is missing the pre-migration package — flag this.
4. **Look for partition discipline.** Production dashboards often skip `WHERE date >= ...` filters; this is a cost bug, not a correctness bug, but it's a differentiation target.
5. **Output an audit** that names the actual protocol(s) covered, the time-coverage gaps, and any methodology weaknesses — don't politely defer to the original.

## DuneSQL + Sui-Specific Syntax

DuneSQL is a Trino fork. Sui-specific quirks on top of standard Trino:

- **Binary columns** (`sender`, `package`, `object_id`, `owner_address`, `gas_owner`, `gas_object_id`): use `to_hex()` and `from_hex()` for string conversion. Canonical Sui address: `concat('0x', lower(to_hex(sender)))`.
- **JSON columns** (`event_json`, `object_json`, `transaction_json`, `effects_json`) are STRINGS, not native JSON:
  - `json_extract_scalar(event_json, '$.field')` → string (most common)
  - `json_extract(event_json, '$.field')` → JSON value (use for arrays/nested objects you'll `UNNEST`)
  - `cast(json_extract_scalar(event_json, '$.amount') as decimal(38,0))` for numerics
  - For arrays: `UNNEST(CAST(json_extract(resp, '$.result.data') AS array(json))) t(item)`
- **`event_type` filtering** is the cleanest Sui pattern — full path `::::` is one string column, filter with `event_type IN (...)`.
- **Date truncation:** `DATE_TRUNC('week', cast(date as timestamp))` — `date` is DATE, not TIMESTAMP.
- **Timestamp from ms:** `from_unixtime(timestamp_ms / 1000)` when sub-daily resolution is needed.
- **Intervals:** `CURRENT_DATE - INTERVAL '90' DAY`.
- **Concatenation:** `||`.

### LiveFetch — pulling live data inside SQL

Dune supports `http_get` and `http_post` directly inside SQL. **This is a game-changer for Sui lending and most DeFi protocols because Dune's curated tables don't yet cover them.** When TVL, current rates, or current asset metadata matter more than historical replay, you can call Sui RPC nodes from within the query and refresh on every execution.

```sql
-- Single Sui RPC call inside SQL
SELECT http_post(
  'https://fullnode.mainnet.sui.io:443',
  '{"jsonrpc":"2.0","id":1,"method":"sui_getObject","params":["0x...",{"showContent":true}]}',
  ARRAY['Content-Type: application/json']
) AS resp
```

LiveFetch is free for all Dune users, has a 5s timeout per call, and is rate-limited at ~80 req/s per proxy. Practical implication: pipelines with 1-50 RPC calls run reliably; pipelines with hundreds of calls need batching via `sui_multiGetObjects` or splitting across multiple queries.

For a fully-worked 4-stage dynamic pipeline (`getDynamicFields` → `multiGetObjects` → `getCoinMetadata` × N → Pyth Hermes), see `references/protocol-patterns.md` § "Navi 4-stage pipeline".

### Pyth Hermes — oracle-grade pricing for any token

Tokens not in Dune's `prices.hour` (governance tokens like NAVX, niche assets like XAUM gold, brand-new launches) can be priced via Pyth's Hermes API:

```sql
SELECT http_get(
  'https://hermes.pyth.network/v2/updates/price/latest'
    || '?ids[]=88250f854c019ef4f88a5c073d52a18bb1c6ac437033f5932cd017d24917ab46' -- NAVX/USD
    || '&ids[]=e62df6c8b4a85fe1a67db44dc12de5db330f7ac66b72dc658afedf0f4a415b43' -- BTC/USD
    || '&parsed=true',
  ARRAY['Content-Type: application/json']
) AS resp
```

Discover feed IDs at `https://hermes.pyth.network/v2/price_feeds?query=&asset_type=crypto`. Pyth prices come with `expo` (exponent) and require: `price * power(10.0, expo)`. See `references/protocol-patterns.md` for full parsing pattern.

**Why Pyth specifically for Sui?** Pyth IS the dominant Sui oracle. Navi, Suilend, Cetus, Bluefin all consume Pyth feeds on-chain. Reading the same source the protocol uses gives you oracle-grade pricing that mirrors what the protocol sees for liquidations.

**For bulk historical pricing** (e.g., 30/90/365 days × multiple feeds), use Pyth Benchmarks' TradingView shim instead — Hermes' `/v2/updates/price/` rate-limits under parallel load. See `references/sui-data-model.md` § "Bulk historical pricing — Pyth Benchmarks" for the pattern and a worked Navi example.

### `prices.hour` — Dune's curated price feed

Dune maintains an hourly price table (`prices.hour`) with coverage across most chains. Quirks worth knowing:

- **Filter by chain explicitly:** `WHERE blockchain = 'sui'`. The same symbol exists on multiple chains.
- **Address encoding is double-hex:** `prices.hour.contract_address_varchar` for Sui stores `'0x' || to_hex(to_utf8(canonical_address))`. SUI's `0x2` becomes `0x307832` (hex of ASCII '0','x','2'). To join from a Sui address: `pl.contract_address_hex = '0x' || to_hex(to_utf8(p.coin_address_canonical))`.
- **Coverage gaps:** smaller-cap tokens (NAVX, XAUM, niche LSTs) may be missing entirely. Always have a Pyth fallback.

## Response Format

### For Build Requests

```
**Goal:** [Restate user's request]

**Data Sources:**
- `sui.events` — [why this table]
- Sui RPC via http_post — [why live state vs indexed]
- Pyth Hermes via http_get — [why oracle vs prices.hour]

**Query:**
[Clean, executable SQL in a code block, with `date` filter always first in WHERE]

**Logic Breakdown:**
1. [Step explanation]
2. [Step explanation]

**⚠️ Sui-Specific Notes:**
- [Relevant pitfall: binary decoding, JSON parsing, package coverage, decimals, double-hex price encoding]

**💡 Optimization Tips:**
- [Partition pruning, CTE pre-filtering, batch RPC calls]

**❓ Uncertainties:**
- [Any disclaimers about unverified event_json field names, package IDs, or coin metadata layout — always flag]
```

### For Debug/Review Requests

```
**Analysis:**
✅ [What's correct]
⚠️ [Issues found — prioritize: missing date filter, wrong binary/JSON handling, missing packages, wrong protocol identity]

**Suggested Changes:**
[Corrected query if needed, with explanation of each change]

**Performance Notes:**
- [Partition pruning, column selection, etc.]

**❓ Uncertainties:**
- [Any disclaimers]
```

### For Investigate Requests

```
**Reference dashboard:** [URL/title]
**Query SQL fetched:** ✅

**Actual protocol covered:** [protocol name, with package hex evidence]
**Time-coverage gaps:** [which package eras are missing]
**Methodology weaknesses:** [partition filters, hardcoded values, mislabels...]
**Differentiation opportunities:** [what your dashboard should do better]
```

## Uncertainty Handling

If an `event_json` field name, package ID, coin metadata field, or protocol-specific event type cannot be confirmed, always include this disclaimer:

> ⚠️ I cannot confirm `event_json.$.field_name` exists for this event. Verify by running a small sample query first: `SELECT event_json FROM sui.events WHERE event_type = '...' AND date >= CURRENT_DATE - INTERVAL '7' DAY LIMIT 5`.

For LiveFetch pipelines, also probe the RPC response shape before parsing it at scale:

```sql
-- Probe a single RPC response, view the JSON structure, THEN write the parser
SELECT http_post(
  'https://fullnode.mainnet.sui.io:443',
  '{"jsonrpc":"2.0","id":1,"method":"sui_getObject","params":["",{"showContent":true}]}',
  ARRAY['Content-Type: application/json']
) AS resp
```

Never guess field paths silently. A query that

…

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [vchrl](https://github.com/vchrl)
- **Source:** [vchrl/dune-sui-query-builder](https://github.com/vchrl/dune-sui-query-builder)
- **License:** MIT
- **Homepage:** https://unchaindata.xyz/dune-dashboards

Install and usage instructions live in the source repository linked above.

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** yes
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-vchrl-dune-sui-query-builder-dune-sui-query-builder
- Seller: https://agentstack.voostack.com/s/vchrl
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.