Allium

View saved version →

---
name: beam-pipelines
description: |
  **Required for Beam pipeline tools: create_beam_config, deploy_beam_pipeline, etc.**
  **Beam** is Allium's custom real-time data pipeline product, built on top of
  [Allium Datastreams](https://app.allium.so/build/datastreams). It lets you tap into
  any of Allium's real-time blockchain data streams — covering 80+ chains — and apply
  your own filters and JavaScript transformations before delivering the results to
  Kafka, SNS, or other destinations.

  Common use cases: real-time alerts on contract events, custom data feeds filtered
  to specific wallets or protocols, and streaming enriched data to your own pipeline.

  Browse available data streams at [app.allium.so/build/datastreams](https://app.allium.so/build/datastreams).
  To get access or learn more, reach out to support@allium.so.

  Read this skill for detailed configuration guides and examples.
gate: app__custom_transforms
---

# Beam Pipeline Configuration Guide

## What is Beam?

Beam is Allium's custom real-time data pipeline product, built on top of [Allium Datastreams](https://app.allium.so/build/datastreams). It lets you tap into any of Allium's real-time blockchain data streams — covering 80+ chains — and apply your own filters and transformations before delivering the results to your preferred destination.

```text
Source (any Allium Datastream) → Transforms (filter/transform) → Sinks (Kafka, SNS, and more)
```

With Beam, you can:

- **Filter** high-volume blockchain streams down to just the events you care about (specific contracts, addresses, event signatures)
- **Transform** data in-flight using JavaScript to parse, enrich, or reshape records before delivery
- **Deliver** processed data to Kafka topics or SNS (with more sink types coming soon) for consumption by your applications

Browse the full catalog of available data streams at [app.allium.so/build/datastreams](https://app.allium.so/build/datastreams).

**Common use cases:**

- Real-time alerts on contract events (e.g., large token transfers, liquidations)
- Custom data feeds filtered to specific wallet addresses or protocols
- Live monitoring and anomaly detection for DeFi activity
- Streaming enriched trade data to your own analytics pipeline

**Interested in Beam?** Reach out to <support@allium.so> to get access.

## Quick Start Workflow

1. **Create config** → `create_beam_config`
2. **Deploy pipeline** → `deploy_beam_pipeline`
3. **Verify deployment** → `get_beam_deployment_stats`

Always check deployment stats after deploying to confirm workers are healthy.

After deploying, you'll receive:

- **Kafka connection credentials** (bootstrap server, username, password)
- **Consumer code snippets** in Python and TypeScript, ready to copy-paste
- A **UI link** to manage your pipeline at `app.allium.so/build/beam/{config_id}`

**Updating a pipeline:** Just call `deploy_beam_pipeline` again after updating the config.
Deployment is idempotent and applies changes in place with zero downtime.
Do NOT teardown before redeploying—that causes unnecessary downtime.

## Configuration Reference

### Source

Beam sources connect to Allium's Datastreams. You select a chain and entity type to stream.

```json
{
  "chain": "polygon",
  "entity": "log",
  "is_zerolag": false
}
```

| Field        | Required | Description                              |
| ------------ | -------- | ---------------------------------------- |
| `chain`      | Yes      | Blockchain to source data from           |
| `entity`     | Yes      | Entity type to stream (see table below)  |
| `is_zerolag` | No       | Low-latency source mode (default: false) |

#### Supported Chains & Entities

Check the [Datastreams catalog](https://app.allium.so/build/datastreams) for the latest availability, or reach out to <support@allium.so> to request a specific chain or entity.

### Transforms

Two transform types are available. You can chain multiple transforms together — data flows through them in order.

#### redis_set_filter

Filter data by matching field values against a set. Only records whose extracted field value exists in your set will pass through.

```json
{
  "type": "redis_set_filter",
  "set_values": ["0x1234...", "0x5678..."],
  "filter_expr": "root = this.address"
}
```

| Field         | Description                                                  |
| ------------- | ------------------------------------------------------------ |
| `set_values`  | Array of values to match against                             |
| `filter_expr` | Bloblang expression to extract the field value for filtering |

**Important:** When filtering by addresses, labels, or symbols, use **lowercase** values.
This is how these values are normalized in our system.

**Bloblang `filter_expr` examples:**

```bloblang
root = this.address        # Filter by contract address
root = this.topic0         # Filter by first topic (event signature)
root = this.from_address   # Filter by sender address
```

**Log entity field reference:**

| Field          | Description                                |
| -------------- | ------------------------------------------ |
| `address`      | Contract address that emitted the log      |
| `topic0`       | First topic (usually event signature hash) |
| `topic1`       | Second topic (first indexed parameter)     |
| `topic2`       | Third topic (second indexed parameter)     |
| `topic3`       | Fourth topic (third indexed parameter)     |
| `from_address` | Transaction sender                         |
| `to_address`   | Transaction recipient                      |
| `data`         | Non-indexed event data                     |

#### v8

Transform data using JavaScript. Your function receives each record and can modify, enrich, or reshape it. Return `null` to drop a record.

```json
{
  "type": "v8",
  "script": "function transform(record) { return record; }"
}
```

| Field    | Description                                |
| -------- | ------------------------------------------ |
| `script` | JavaScript code that processes each record |

### Sinks

Sinks define where your processed data is delivered.

```json
{
  "type": "kafka",
  "name": "my-output-topic"
}
```

| Field  | Description                                            |
| ------ | ------------------------------------------------------ |
| `type` | Output type: `kafka` or `sns` (more sinks coming soon) |
| `name` | Topic name suffix for the output                       |

**Kafka sinks:** After deployment, you'll receive connection credentials and ready-to-use consumer code snippets in Python and TypeScript.

**SNS sinks:** Delivers data to an SNS topic. Reach out to <support@allium.so> if you need help setting up SNS delivery or are interested in other sink types.

## Quick Start Examples

### Example 1: Filter Logs by Contract Address

Stream logs from specific contract addresses using `redis_set_filter`:

```json
{
  "name": "USDC Transfer Monitor",
  "description": "Monitor USDC transfers on Polygon",
  "source": {
    "chain": "polygon",
    "entity": "log"
  },
  "transforms": [
    {
      "type": "redis_set_filter",
      "set_values": [
        "0x3c499c542cef5e3811e1192ce70d8cc03d5c3359"
      ],
      "filter_expr": "root = this.address"
    }
  ],
  "sinks": [
    {
      "type": "kafka",
      "name": "usdc-logs"
    }
  ]
}
```

### Example 2: Transform Log Data with JavaScript

Parse and enrich log data using a v8 transform:

```json
{
  "name": "DEX Trade Parser",
  "description": "Parse DEX swap events and extract trade details",
  "source": {
    "chain": "polygon",
    "entity": "log"
  },
  "transforms": [
    {
      "type": "redis_set_filter",
      "set_values": [
        "0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822"
      ],
      "filter_expr": "root = this.topic0"
    },
    {
      "type": "v8",
      "script": "function transform(record) { record.parsed = true; return record; }"
    }
  ],
  "sinks": [
    {
      "type": "kafka",
      "name": "dex-trades"
    }
  ]
}
```

### Example 3: Monitor ERC-20 Transfers on Base

Track token transfers on Base using the `erc20_token_transfer` entity:

```json
{
  "name": "Base Token Transfer Tracker",
  "description": "Monitor ERC-20 token transfers on Base",
  "source": {
    "chain": "base",
    "entity": "erc20_token_transfer"
  },
  "transforms": [
    {
      "type": "redis_set_filter",
      "set_values": [
        "0x833589fcd6edb6e08f4c7c32d4f71b54bda02913"
      ],
      "filter_expr": "root = this.address"
    }
  ],
  "sinks": [
    {
      "type": "kafka",
      "name": "base-usdc-transfers"
    }
  ]
}
```

### Example 4: Multiple Contract Addresses

Monitor multiple contracts in a single pipeline:

```json
{
  "name": "Multi-Token Monitor",
  "description": "Monitor USDC and USDT on Polygon",
  "source": {
    "chain": "polygon",
    "entity": "log"
  },
  "transforms": [
    {
      "type": "redis_set_filter",
      "set_values": [
        "0x3c499c542cef5e3811e1192ce70d8cc03d5c3359",
        "0xc2132d05d31c914a87c6611c10748aeb04b58e8f"
      ],
      "filter_expr": "root = this.address"
    }
  ],
  "sinks": [
    {
      "type": "kafka",
      "name": "stablecoin-logs"
    }
  ]
}
```

## Tool Reference

### Configuration Management

| Tool                 | Description                                         |
| -------------------- | --------------------------------------------------- |
| `create_beam_config` | Create a new pipeline configuration                 |
| `get_beam_config`    | Get full details of a config by ID                  |
| `list_beam_configs`  | List all your pipeline configurations               |
| `update_beam_config` | Update an existing configuration                    |
| `delete_beam_config` | Delete a configuration (also tears down deployment) |

### Deployment

| Tool                        | Description                               |
| --------------------------- | ----------------------------------------- |
| `deploy_beam_pipeline`      | Deploy a config to Kubernetes             |
| `teardown_beam_pipeline`    | Remove deployed infrastructure            |
| `get_beam_deployment_stats` | Check deployment status and worker health |

### Web Interface

Every pipeline has a management page at `app.allium.so/build/beam/{config_id}`.
The UI URL is returned by `create_beam_config`, `deploy_beam_pipeline`, and `get_beam_deployment_stats`.

## Consuming Your Data

After deploying a Beam pipeline with a Kafka sink, the `deploy_beam_pipeline` tool returns everything you need to start consuming:

- **Kafka connection credentials**: bootstrap server, username, and password
- **Code snippets**: Ready-to-use consumer code in Python (`confluent-kafka`) and TypeScript (`kafkajs`)
- **Topic names**: Automatically generated as `beam.{config_id}.{sink_name}`

Just copy the provided code snippet, install the relevant Kafka client library, and start receiving data.

## Monitoring

### Checking Deployment Health

After deploying, use `get_beam_deployment_stats` to verify:

```json
{
  "config_id": "abc123",
  "status": "deployed",
  "workers_health": {
    "total_workers": 2,
    "healthy_workers": 2,
    "unhealthy_workers": 0,
    "crashing_workers": 0,
    "oom_killed_workers": 0
  }
}
```

### Worker Health Fields

| Field                | Description                  |
| -------------------- | ---------------------------- |
| `total_workers`      | Number of worker pods        |
| `healthy_workers`    | Workers running normally     |
| `unhealthy_workers`  | Workers not ready            |
| `crashing_workers`   | Workers in crash loop        |
| `oom_killed_workers` | Workers killed due to memory |

## Troubleshooting

### Unhealthy Workers

If `unhealthy_workers > 0`:

1. Check if the pipeline config is valid
2. Verify source data is available
3. Update the config and redeploy (no teardown needed)

### OOM Killed Workers

If `oom_killed_workers > 0`:

1. Simplify transforms to reduce memory usage
2. Add more aggressive filtering earlier in the pipeline
3. Contact <support@allium.so> for resource limit adjustments

### Crashing Workers

If `crashing_workers > 0`:

1. Check v8 script for syntax errors
2. Verify filter expressions are valid
3. Review transform logic for runtime errors

## Best Practices

1. **Filter early**: Apply `redis_set_filter` before `v8` transforms to reduce data volume
2. **Start simple**: Begin with basic filtering, add transforms incrementally
3. **Monitor after deploy**: Always check `get_beam_deployment_stats` after deployment
4. **Test transforms**: Validate v8 scripts handle edge cases
5. **Use descriptive names**: Clear names help track multiple pipelines
6. **Lowercase addresses/labels/symbols**: Use lowercase when filtering by these values
7. **Redeploy, don't teardown**: When updating, just redeploy—it's idempotent with zero downtime

View saved version →

---
name: customer-support
description: |
  **Customer support and solutions engineering guidance using internal runbooks.**

  Read this skill when a user:
  - Reports errors, timeouts, or unexpected query results
  - Asks how to get started with Allium or choose a product
  - Needs help integrating Allium APIs, datashares, or datastreams
  - Has questions about data coverage, schemas, or chain-specific behavior
  - Wants guidance on a use case (DEX, NFT, bridge, stablecoin, etc.)
  - Asks about account access, API keys, billing, or permissions
  - Needs help with AI/MCP setup or troubleshooting
  - Mentions data discrepancies or missing data
---

# Customer Support & Solutions Engineering

You are acting as a support engineer and solutions engineer for Allium. Use the internal support runbooks to answer customer questions accurately and consistently.

## Identify the mode

Determine whether the customer needs **support** or **solutions** help:

- **Support** (something is broken or confusing): errors, timeouts, missing data, unexpected results, access issues
- **Solutions** (building something new): product selection, integration guidance, use-case architecture, onboarding

This affects tone and approach. Support is diagnostic and reassuring. Solutions is consultative and forward-looking.

## How to use runbooks

The runbooks live at `internal/support-runbooks` in the docs system. **Navigate progressively** - never guess file paths.

### Step 1: Browse the top-level structure

```text
browse_docs(path="internal/support-runbooks")
```

This returns the README with the full directory layout and category descriptions.

### Step 2: Browse the relevant category

```text
browse_docs(path="internal/support-runbooks/{category}")
```

### Step 3: Read the specific runbook

```text
browse_docs(path="internal/support-runbooks/{category}/{file}.md")
```

### Fallback: search

If the category isn't obvious, use `search_docs` with relevant keywords to find the right runbook.

## Intent routing table

Map customer intent to the right runbook category:

| Intent                                                         | Category path         | Examples                                                                                               |
| -------------------------------------------------------------- | --------------------- | ------------------------------------------------------------------------------------------------------ |
| Onboarding, product selection, first query, billing questions  | `getting-started/`    | "How do I get started?", "Which product should I use?", "How does pricing work?"                       |
| Explorer, Realtime APIs, Datastreams, Datashares product help  | `products/`           | "How do I use Explorer?", "How do I set up a datashare?", "What realtime APIs are available?"          |
| Schema questions, data coverage, data quality                  | `data/`               | "What chains do you support?", "What tables have DEX data?", "Why is this column null?"                |
| Solana, Hyperliquid, Bitcoin, EVM, Cosmos chain-specific help  | `chains/`             | "How do I query Solana transactions?", "Do you support Hyperliquid?", "How are EVM traces structured?" |
| Bridge, DEX, NFT, stablecoin, DeFi use cases                   | `use-cases/`          | "How do I track bridge transfers?", "Can I get DEX volume by protocol?"                                |
| AI agent setup, MCP server configuration                       | `ai-and-mcp/`         | "How do I set up the MCP server?", "Can I use Allium with my AI agent?"                                |
| API keys, permissions, team access, security                   | `account-and-access/` | "How do I rotate my API key?", "How do I add a teammate?", "What permissions does my key have?"        |
| Data discrepancies, query failures, error messages, escalation | `troubleshooting/`    | "My query is timing out", "The data doesn't match Etherscan", "I'm getting a 403 error"                |
| Glossary, status page, changelog                               | `reference/`          | "What does 'block_timestamp' mean?", "Is there a status page?", "What changed recently?"               |

## Behavioral guidelines

### Information gathering

Before diving into runbooks, make sure you understand the problem:

1. What product are they using? (Explorer, API, Datashare, Datastream, MCP)
2. What chain and data type?
3. What specific error or question?
4. What have they already tried?

Use `ask_user_question` if any of these are unclear. Don't guess.

### Tone

- Be direct and helpful. Don't over-apologize or use filler phrases
- If something is broken, acknowledge it clearly
- If a feature doesn't exist, say so and suggest alternatives
- Use concrete examples, SQL snippets, and links to docs when possible

### Use other tools

Runbooks are your primary source, but combine with other tools as needed:

- `browse_docs` / `search_docs` - public documentation for schemas, API specs, examples
- `search_schemas` - validate table and column names
- `run_sql_query` / `prepare_sql` - test queries or demonstrate solutions
- `get_skill` - fetch complementary skills (see below)

### Cross-reference other skills

Read these skills when the conversation shifts into their domain:

- **Query performance** (slow queries, timeouts, optimization) -> `get_skill(name="sql-optimization")`
- **Streaming pipelines** (Beam config, transforms, deployment) -> `get_skill(name="beam-pipelines")`
- **Dashboard help** (creating/updating dashboards) -> `get_skill(name="dashboard-design")`

## Escalation criteria

Direct the customer to human support (`support@allium.so`) when:

- There is a confirmed service **outage** or infrastructure issue
- The issue involves **billing**, invoicing, or contract terms
- The customer reports a **security** concern or vulnerability
- The problem requires **infrastructure changes** (resource limits, access provisioning, custom deployments)
- The customer **explicitly asks** to speak with a human
- You've exhausted the runbooks and still can't resolve the issue

When escalating, summarize what you've already investigated so the support team has context.

View saved version →

---
name: pdf-reports
description: |
  **Required for generate_pdf_report tool.**

  Read this skill BEFORE calling generate_pdf_report to understand
  how to write professional analytical reports with narrative depth.
---

# PDF Report Generation

## Report Architecture

Every report MUST follow this structure. No exceptions.

### 1. Executive Summary (text section)

- 2-3 paragraph narrative. Lead with a thesis statement about the key finding.
- Bold key statistics inline: **$266.3 billion**, **+317% YoY**.
- Provide context for every number (vs prior period, vs benchmark, vs market total).
- NOT a bullet list. Write flowing prose that tells the story.

### 2. Key Insights (text section)

- 3-5 numbered insights. The "if you read nothing else" page.
- Each insight: **Bold headline (10 words max)** followed by supporting data and one-sentence implication.
- Example: "**1. Stablecoin payments surpassed credit card volume.** Monthly payment volume hit **$48.3B** in February, exceeding Visa's average merchant settlement volume for the first time. This signals stablecoins are crossing from speculation into real commerce."

### 3. Themed Analytical Sections (3-6 sections)

Each theme follows a repeating pattern of three sections:

1. **Context text** (titled) - Why this data matters. Frame the question being answered. 1-2 sentences.
2. **Chart or table** (titled) - The visualization itself.
3. **Interpretation text** (empty title `""`) - What the data reveals. Bold the key takeaway. Call out anomalies, benchmarks, or surprising patterns. 2-4 sentences.

This interleaved pattern is critical. Charts must never appear without surrounding context and interpretation.

### 4. Methodology (text section, for reports with 3+ data sections)

- Data sources and APIs used.
- Time periods and filters applied.
- Known limitations or caveats.
- Keep it brief but honest about what the data does and doesn't cover.

## Content Writing Rules

### Before every chart/table

Write 1-2 sentences framing what the reader is about to see and why it matters.

### After every chart/table

Use an empty-title text section (`"title": ""`) for interpretation:

- 2-4 sentences interpreting findings.
- **Bold the key takeaway** so a skimmer catches it.
- Call out anomalies, inflection points, or surprising patterns.
- Provide context: vs prior period, vs benchmark, vs total.

### Statistics and numbers

- Bold key statistics inline: **$266.3 billion**, **+317%**.
- Always contextualize: "representing **42%** of total DEX volume, up from 28% last quarter."
- Don't just report WHAT. Explain WHY it matters.

### Section titles

- Use insight-driven titles, not metric-driven titles.
- Good: "Payment Adoption Outpaces Speculation"
- Bad: "Daily Trading Volume"

### Cross-references

- When data in one section relates to another, say so: "Consistent with the supply shift noted above..."

## Anti-Patterns (avoid these)

- **Bullet-list executive summaries** - Write prose paragraphs instead.
- **Naked charts** - Every chart needs context before AND interpretation after.
- **Data-descriptive section titles** - "Daily Trading Volume" tells the reader nothing. Use insight-driven titles.
- **Reports without methodology** - Include data sources and limitations for any report with 3+ data sections.
- **Restating numbers without interpretation** - Don't say "Volume was $10B." Say "Volume hit **$10B**, a **+34%** increase that coincided with the ETH ETF approval."
- **Flat structure** - Don't dump a sequence of charts. Group related data into themed sections with narrative flow.

## Empty-Title Sections

Set `"title": ""` on a section to render it without a header or TOC entry. The content flows directly below the previous section. Use this for chart/table interpretations:

```json
{"title": "Payment Adoption Outpaces Speculation", "type": "text", "data": {"content": "The shift from speculative trading to real payment usage is the defining trend of 2026..."}},
{"title": "Monthly Payment Volume", "type": "chart", "data": {"chart_type": "line", "labels": ["Jan", "Feb", "Mar"], "values": [32.1, 48.3, 51.7], "y_label": "Volume ($B)"}, "source": "Allium API"},
{"title": "", "type": "text", "data": {"content": "Payment volume grew **60%** in Q1, reaching **$51.7B** in March. The acceleration began in February when two major e-commerce platforms integrated USDC checkout. **This is the first quarter where payment volume exceeded speculative trading volume.**"}}
```

## Full Example

```json
{
  "title": "Stablecoins Infrastructure Report",
  "sections": [
    {
      "title": "Executive Summary",
      "type": "text",
      "data": {
        "content": "The stablecoin market reached a combined market capitalization of **$266.3 billion** in Q1 2026, representing a **+41% increase** year-over-year and surpassing the previous all-time high set in late 2024. This growth was driven primarily by payment adoption rather than speculative trading, marking a structural shift in how stablecoins are used.\n\nUSDT maintained its dominant position with **$142B** in circulation, but USDC grew at nearly **3x the rate**, narrowing the gap from 3.2:1 to 2.4:1. The most significant development was the emergence of institutional payment rails, with **$48.3B** in monthly payment volume processed through stablecoin networks in February alone.\n\nThis report examines supply dynamics, payment adoption, chain-level infrastructure shifts, and the competitive landscape across the top stablecoin issuers. Data is sourced from Allium's cross-chain analytics covering 15 networks."
      }
    },
    {
      "title": "Key Insights",
      "type": "text",
      "data": {
        "content": "**1. Payment volume surpassed speculative trading for the first time.** Monthly payment volume hit **$48.3B** in February, exceeding trading-related transfer volume. This signals stablecoins are crossing from speculation into real commerce.\n\n**2. USDC is closing the gap with USDT at an accelerating rate.** USDC supply grew **+89%** YoY vs USDT's **+31%**, driven by regulatory clarity in the US and EU. The ratio narrowed from 3.2:1 to 2.4:1.\n\n**3. Arbitrum and Base captured 62% of new stablecoin deployment.** L2 networks absorbed the majority of new supply, with average transaction costs under **$0.003** making micropayments viable.\n\n**4. Institutional on-ramps grew 4x in Q1.** The number of verified institutional wallets holding >$1M in stablecoins increased from 1,200 to 4,800, driven by new custody integrations.\n\n**5. Average transfer size dropped 73%, signaling retail adoption.** Median transfer fell from **$4,200** to **$1,150**, consistent with payment use cases rather than treasury management."
      }
    },
    {
      "title": "Supply Growth Signals Structural Demand",
      "type": "text",
      "data": {
        "content": "Total stablecoin supply is the most fundamental indicator of ecosystem health. Unlike trading volume, which can be inflated by wash trading or arbitrage, supply growth reflects genuine demand for dollar-denominated digital assets."
      }
    },
    {
      "title": "Total Stablecoin Supply (Q1 2025 - Q1 2026)",
      "type": "chart",
      "data": {
        "chart_type": "line",
        "labels": ["Q1 2025", "Q2 2025", "Q3 2025", "Q4 2025", "Q1 2026"],
        "values": [188.7, 205.3, 224.1, 248.9, 266.3],
        "y_label": "Market Cap ($B)"
      },
      "source": "Allium Cross-Chain Analytics"
    },
    {
      "title": "",
      "type": "text",
      "data": {
        "content": "Supply grew consistently across all four quarters, with **no quarter showing negative growth** for the first time since 2021. **The Q1 2026 figure of $266.3B represents a new all-time high**, surpassing the previous peak of $188B in late 2024. The steady growth pattern, rather than spike-and-crash, suggests structural demand rather than speculative cycles."
      }
    },
    {
      "title": "Payment Adoption Outpaces Speculation",
      "type": "text",
      "data": {
        "content": "The ratio of payment volume to speculative trading volume has been trending upward since mid-2025. This section examines whether the crossover observed in February represents a permanent shift or a temporary anomaly."
      }
    },
    {
      "title": "Monthly Volume by Use Case",
      "type": "chart",
      "data": {
        "chart_type": "bar",
        "labels": ["Oct 2025", "Nov 2025", "Dec 2025", "Jan 2026", "Feb 2026"],
        "values": [38.2, 41.5, 43.8, 45.1, 48.3],
        "y_label": "Payment Volume ($B)"
      },
      "source": "Allium Payment Classification Model"
    },
    {
      "title": "",
      "type": "text",
      "data": {
        "content": "Payment volume increased every month in the observation period, reaching **$48.3B in February**. The acceleration in January and February coincided with two major e-commerce platform integrations (Shopify USDC and Stripe stablecoin settlement). **This is the first sustained period where payment volume exceeded speculative transfer volume**, though the classification model carries a ~5% margin of error on the payment/speculation boundary."
      }
    },
    {
      "title": "Chain-Level Infrastructure Shifts",
      "type": "text",
      "data": {
        "content": "Where stablecoins live matters as much as how much exists. Chain selection reflects cost sensitivity, speed requirements, and ecosystem maturity."
      }
    },
    {
      "title": "Stablecoin Supply by Chain",
      "type": "chart",
      "data": {
        "chart_type": "pie",
        "labels": ["Ethereum", "Tron", "Arbitrum", "Base", "Solana", "Other"],
        "values": [112.5, 58.2, 38.4, 27.1, 18.9, 11.2]
      },
      "source": "Allium Cross-Chain Analytics"
    },
    {
      "title": "",
      "type": "text",
      "data": {
        "content": "Ethereum remains the largest host at **$112.5B (42%)**, but its share declined from 51% a year ago. **Arbitrum and Base together captured 62% of new supply deployment in Q1**, reflecting the migration to lower-cost L2 infrastructure. Tron's **$58.2B** remains concentrated in emerging market remittance corridors, a use case largely separate from the DeFi ecosystem."
      }
    },
    {
      "title": "Methodology",
      "type": "text",
      "data": {
        "content": "**Data sources:** Allium cross-chain analytics covering 15 EVM and non-EVM networks. Supply figures include USDT, USDC, DAI, FRAX, and PYUSD with market cap >$100M.\n\n**Time period:** Q1 2025 through Q1 2026 (April 1, 2025 - March 31, 2026). Monthly figures use end-of-month snapshots.\n\n**Payment classification:** Allium's proprietary model classifies transfers as payment vs speculative based on wallet clustering, counterparty analysis, and transaction patterns. Margin of error: ~5%.\n\n**Limitations:** Cross-chain bridge transfers may be double-counted in supply totals. Tron data excludes unverified contract deployments. L2 supply figures include bridged assets from Ethereum."
      }
    }
  ],
  "options": {
    "subtitle": "Q1 2026 Analysis",
    "author": "Allium Research",
    "date": "March 2026",
    "include_toc": true
  }
}
```

## Workflow

1. Gather data using `run_sql_query`
2. Plan your report architecture: identify 3-5 themes from the data
3. Write the executive summary and key insights FIRST (forces you to identify the story)
4. Build themed sections with the text > chart > interpretation pattern
5. Add methodology section
6. Call `generate_pdf_report` with the structured sections

## Section Types Reference

All section types support an optional `source` field for data attribution (rendered as small gray text below the content).

### Table

```json
{
  "title": "Token Holdings",
  "type": "table",
  "data": {
    "columns": ["Token", "Balance", "USD Value"],
    "rows": [["ETH", "1.5", "$5,000"], ["USDC", "1,000", "$1,000"]]
  },
  "source": "Allium API"
}
```

### Chart

```json
{
  "title": "Portfolio Allocation",
  "type": "chart",
  "data": {
    "chart_type": "pie",
    "labels": ["ETH", "USDC"],
    "values": [5000, 1000]
  },
  "source": "CoinGecko"
}
```

Chart types: `pie` (allocation), `line` (time series), `bar` (comparison)

Optional: `x_label`, `y_label` for line/bar charts.

### Text

```json
{
  "title": "Summary",
  "type": "text",
  "data": {
    "content": "Total portfolio value: $6,000 across 2 tokens. Supports **bold** and *italic* markdown."
  }
}
```

## Options

- `orientation`: "portrait" (default) or "landscape" for wide tables
- `include_timestamp`: true (default) or false
- `subtitle`: subtitle displayed on the cover page
- `author`: author name on cover page
- `date`: date string on cover page
- `include_toc`: false (default) or true - adds a table of contents page. Use for reports with 4+ sections.

View saved version →

---
name: product-guide
description: |
  Guide to Allium's four core products: Explorer, Realtime APIs, Datastreams,
  and Datashares. Explains what each product does, who it's for, and how to
  choose the right one for a customer's use case.

  Read this skill when a customer asks about Allium's products, wants to
  understand the difference between them, needs help choosing a product, or
  asks questions like "which product should I use?" or "what does Allium offer?"
  It also carries the canonical, dated list of supported blockchains — read it
  for any "what chains / how many chains does Allium support?" question and cite
  that list instead of answering from memory.
---

# Allium Product Guide

Allium offers four core products for accessing blockchain data. Each serves a
different access pattern, latency requirement, and integration style. Explorer,
Datashares, and Datastreams share one historical data platform; the Realtime
APIs serve a smaller, separate set of chains.

> **Answering "what chains does Allium support?" (grounding rule)**
>
> Chain names and counts must come from the canonical list, never from memory —
> generating them from memory produces unstable totals and invented chains.
>
> 1. Fetch the **Allium Supported Chains — Canonical List** reference file (see
>    "Reference files" below) and quote it. For an up-to-the-minute Realtime
>    figure, call the `realtime_get_supported_chains` tool.
> 2. Always say **which product** a count refers to — the historical data
>    platform (Explorer / Datashares / Datastreams) and the Realtime APIs have
>    different totals.
> 3. The headline total must equal the number of rows for that product in the
>    canonical list. Do not round to a vague "80+/130+/150+" — cite the exact
>    count and state the list's snapshot date in the answer, so a reader can see
>    how current it is (e.g. "**[N]** chains on the historical data platform, as
>    of **[snapshot date]**"). Pull both **[N]** and the date from the fetched
>    reference file — never from this instruction or from memory.
> 4. If a chain is not in the canonical list, say it is not currently supported
>    rather than guessing.

## Product Overview

| Product           | Access Pattern                      | Latency                                | Pricing Model              | Best For                                            |
| ----------------- | ----------------------------------- | -------------------------------------- | -------------------------- | --------------------------------------------------- |
| **Explorer**      | SQL queries via web UI or API       | ~1 hour freshness, ~4-5s query time    | Query compute time         | Ad-hoc analysis, dashboards, research               |
| **Realtime APIs** | REST API (pull)                     | 50-100ms response, 3-5s data freshness | API call volume            | Application backends, wallets, trading UIs          |
| **Datastreams**   | Push via Kafka/PubSub/SNS/WebSocket | 3-5s data freshness                    | Data destinations & egress | Event-driven architectures, real-time pipelines     |
| **Datashares**    | Native tables in your warehouse     | 1-3 hours batch, sub-minute streaming  | Number of chains & schemas | Institutional analytics, joining with internal data |

## Explorer

**What it is:** A SQL-based analytics workspace at `app.allium.so/explorer` that
lets users query, visualize, and share blockchain data across every chain on the
historical data platform (see the canonical chain list for the exact count). Powered
by Snowflake (OLAP).

**Key capabilities:**

- Full SQL interface with cross-chain queries (e.g., `crosschain.dex.trades`)
- 1,000+ enriched schemas: raw, decoded, DEX, DeFi, NFTs, stablecoins, wallet 360, metrics, bridges, prices, PnL
- Interactive chart builder with public sharing and embed links
- CSV and API data upload — join your own data with on-chain data
- Parameterized queries for reusable, dynamic SQL
- AI Assistant for natural-language-to-SQL
- Explorer API — programmatic query lifecycle (create, run, fetch results, cancel)
- Curated metrics dashboards for stablecoins, DEXs, bridges, etc.

**Target users:**

- Analytics teams at crypto companies (foundations, protocols, wallets)
- Data analysts and researchers
- Audit, accounting, and compliance teams (Big 4 firms)
- Growth and marketing teams tracking ecosystem metrics

**Common use cases:**

- User behavior analytics and wallet activity patterns
- Ecosystem metrics and competitive intelligence
- Sybil detection for airdrop eligibility (used by Jito, Wormhole)
- Account reconciliation and auditing (used by Big 4 firms)
- DEX adoption dashboards (Uniswap Foundation)
- Financial monitoring and tax reporting (TaxBit)

**When to recommend Explorer:**

- Customer wants to explore data interactively with SQL
- Ad-hoc analysis, research, or one-off investigations
- Building shareable dashboards and visualizations
- Uploading proprietary data to join with on-chain data
- Needs the broadest chain coverage (the full historical data platform; see the
  canonical chain list) and deepest schema library (1,000+)
- Hourly data freshness is acceptable

## Realtime APIs

**What it is:** Production-grade REST APIs delivering real-time, enriched
blockchain data with 50-100ms response times and 3-5 second data freshness.
Realtime covers fewer chains than the historical data platform — quote the
canonical chain list (or the live `realtime_get_supported_chains` tool) for the
exact set and count.

**Key capabilities:**

- **Prices** — real-time and historical token prices from on-chain DEX trades (not centralized exchanges). OHLC candles, VWAP, z-score outlier filtering. New tokens priced within seconds of first DEX trade (including pump.fun launches)
- **Tokens** — metadata, type, price, decimals, FDV, volume, trade count, holders, ATH/ATL, liquidity, creation time. Sortable and searchable
- **Wallets** — current balances (native + ERC-20/SPL), historical balances at any point in time, full transaction history with activity labels (swaps, transfers, DEX trades)
- **Holdings** — real-time and historical USD portfolio holdings with built-in PnL using average cost basis. Multi-granularity (15s/5m/1h/1d)
- **Assets** — batch-fetch asset details by chain + address
- **Hyperliquid** — dedicated endpoints for the Hyperliquid protocol
- Custom endpoints backed by your own SQL queries

**Performance:**

- 50-100ms response time
- 1-1.2s raw block freshness, 3-5s enriched data freshness
- Tested to 90,000 RPS (Phantom/Jupiter airdrop: 477M requests in 4 hours)
- 99.9% uptime SLA

**Authentication:** API key via `X-API-KEY` header. Generate at Settings > API Keys.

**Target users:**

- Application developers building crypto products
- Wallet teams (Phantom, MetaMask)
- DEX/trading platforms (Uniswap, Fomo)
- Payment providers (MoonPay, Bridge.xyz)
- Fraud detection systems (Cube3.ai, Blowfish)
- AI agent builders

**When to recommend Realtime APIs:**

- Customer is building an application that needs to pull data on demand
- Needs sub-second response times for user-facing features
- Wallet balances, transaction history, token prices, or portfolio PnL
- Request-response pattern fits their architecture
- Needs instant coverage of new tokens (long-tail/pump.fun)
- Building token screeners, trading interfaces, or portfolio trackers

## Datastreams

**What it is:** Real-time push-based data delivery via enterprise message brokers.
Enriched, decoded blockchain data from the historical data platform (see the
canonical chain list for the exact count) streamed with 3-5 second latency and
guaranteed delivery.

**Key capabilities:**

- Delivery via **Kafka**, **Google Cloud Pub/Sub**, **Amazon SNS** (coming soon), **WebSockets**, and **webhooks**
- Guaranteed delivery and message ordering (Kafka/PubSub)
- Historical replay via retention policies
- **Stream Transformations** — managed filter-and-route pipelines:
  - Data source filters (dynamic address/contract lists, updateable without restart)
  - Declarative JSON filters with `=`, `!=`, `>`, `<`, `in`, `exists`, `AND`/`OR`
  - Workflows: `source → filter → destination`
- **Beam** (custom transforms) — JavaScript v8 transforms and redis set filters on any stream, with Kafka/SNS sinks. See `beam-pipelines` skill for details
- Compression (lz4, zstd, gzip)
- WebSocket streaming of all Kafka topics for simpler integration

**Data entities:** Raw (blocks, transactions, logs, traces), decoded logs/traces, DEX trades, token transfers, balances — across the historical data platform (see the canonical chain list for the exact count).

**Target users:**

- Teams building event-driven blockchain infrastructure
- Wallet backends (Phantom)
- Market intelligence platforms (Messari)
- Fraud detection engines (Blowfish)
- Back-office reconciliation systems (Bridge)
- Trading platforms needing real-time token/trade feeds

**When to recommend Datastreams:**

- Customer needs push-based, event-driven data delivery
- Building real-time alerts, monitoring, or anomaly detection
- Wants guaranteed delivery with replay capability
- Needs to filter high-volume streams to specific contracts, addresses, or events
- Architecture is built around Kafka, PubSub, or message queues
- Needs custom transformations on streaming data (→ Beam)
- Wants data pushed rather than polling an API

## Datashares

**What it is:** Managed delivery of production-ready blockchain data as native
tables directly into your own data warehouse or data lake. Allium handles bulk
ingestion, incremental updates, schema migrations, reorg handling, and data quality.

**Key capabilities:**

- **Snowflake** — native Data Shares (zero-copy). Primary region: GCP US Central 1. Worldwide delivery with 3-hour freshness
- **BigQuery** — via Google Analytics Hub. Regions: US Central 1, EU West 2
- **Databricks** — via Delta Sharing. Sub-minute streaming available. Recommended: AWS us-east-2
- **Amazon S3** / **Google Cloud Storage** — Iceberg format data dumps
- Apache Iceberg table format with backward-compatible schema evolution
- SOC 1 & SOC 2 (Type I & II) certified pipelines
- Full privacy — Allium cannot see your queries, joins, or results
- No query metering — you control and pay for your own compute
- Native BI tool connectors: Tableau, Looker, Power BI, Hex, Sigma, Omni

**Data coverage:** the full historical data platform (see the canonical chain list for the exact count), 1,000+ enriched schemas (raw, decoded, DEX, DeFi, NFTs, stablecoins, wallet 360, metrics, bridges, staking).

**Target users:**

- Institutional analytics teams needing data in their own environment
- Audit, accounting, and compliance teams at regulated institutions
- Data science teams building models on blockchain data
- Companies that must join on-chain data with proprietary internal data

**Notable customers:** Visa, Coinbase, Grayscale, Paradigm, Stripe, Uniswap Foundation, MoonPay, Electric Capital.

**When to recommend Datashares:**

- Customer already has a data warehouse (Snowflake, BigQuery, Databricks)
- Needs to join blockchain data with internal/proprietary data
- Privacy and data sovereignty are requirements (regulated industries)
- Running heavy analytical workloads where controlling compute costs matters
- Building ML/AI models on blockchain data
- Needs petabyte-scale historical data for backtesting or research
- Compliance, audit, or accounting use cases at institutions

## How to Choose the Right Product

### Decision Framework

**Start with the access pattern:**

1. **"I want to explore and analyze data interactively"** → **Explorer**
2. **"I'm building an app and need to fetch data on demand"** → **Realtime APIs**
3. **"I need data pushed to my systems in real-time"** → **Datastreams**
4. **"I want blockchain data in my own warehouse"** → **Datashares**

### By Use Case

| Customer Need                                       | Recommended Product  |
| --------------------------------------------------- | -------------------- |
| Ad-hoc SQL analysis and dashboards                  | Explorer             |
| Research and data exploration                       | Explorer             |
| Shareable charts and visualizations                 | Explorer             |
| Wallet balances and transaction history for an app  | Realtime APIs        |
| Token prices for a trading interface                | Realtime APIs        |
| Portfolio PnL in a consumer product                 | Realtime APIs        |
| Real-time alerts on smart contract events           | Datastreams          |
| Streaming DEX trades to an analytics pipeline       | Datastreams          |
| Custom filtered feeds (specific wallets, contracts) | Datastreams (+ Beam) |
| Institutional-grade historical analytics            | Datashares           |
| Joining on-chain + internal data for compliance     | Datashares           |
| ML model training on blockchain data                | Datashares           |
| BI dashboards in Tableau/Looker/Power BI            | Datashares           |

### By Latency Requirement

| Requirement               | Product                                                                            |
| ------------------------- | ---------------------------------------------------------------------------------- |
| Sub-second response time  | Realtime APIs (50-100ms)                                                           |
| Low-second streaming      | Datastreams (3-5s)                                                                 |
| Near-real-time analytics  | Explorer (~1 hour) or Datashares (1-3 hour batch, sub-minute Databricks streaming) |
| Batch/historical analysis | Explorer or Datashares                                                             |

### By Team Profile

| Team                               | Start With                                                                                   |
| ---------------------------------- | -------------------------------------------------------------------------------------------- |
| Data analysts writing SQL          | Explorer                                                                                     |
| Backend engineers building APIs    | Realtime APIs                                                                                |
| Infrastructure/platform engineers  | Datastreams                                                                                  |
| Data engineering / warehouse teams | Datashares                                                                                   |
| Compliance / audit teams           | Datashares (for privacy + joining internal data) or Explorer (for interactive investigation) |

### Common Multi-Product Patterns

Many customers use multiple products together:

- **Explorer + Datashares**: Explore and prototype queries in Explorer, then productionize with Datashares in their warehouse
- **Realtime APIs + Datastreams**: APIs for user-facing request-response, Datastreams for backend event processing
- **Datashares + Explorer**: Datashares for heavy analytics in their warehouse, Explorer for ad-hoc investigation and sharing
- **Datastreams + Datashares**: Datastreams for real-time event processing, Datashares for historical backfill and batch analytics

### Pricing Comparison

| Product       | Model                 | Implication                                        |
| ------------- | --------------------- | -------------------------------------------------- |
| Explorer      | Query compute time    | Cost scales with query complexity and frequency    |
| Realtime APIs | API call volume       | Cost scales with request volume                    |
| Datastreams   | Destinations & egress | Cost scales with number of streams and data volume |
| Datashares    | Chains & schemas      | Predictable cost based on data coverage selected   |

All products require contacting Allium for specific pricing. Customers can sign up for a free trial at `app.allium.so/join` for API access, or book a demo for enterprise needs.

Referenced files: 1

View saved version →

---
name: sql-optimization
description: |
  **Required reading before writing or running any SQL.** Patterns for Snowflake
  query performance and chain-specific pitfalls.

  Covers: default time ranges, partition-pruning rules, CTE filtering, `QUALIFY`
  for dedup, `UNION ALL` over `UNION`, `APPROX_COUNT_DISTINCT` for exploration,
  pre-aggregated `*.metrics.*` tables vs raw aggregation, EVM address lowercasing
  (and when not to), per-chain vs `crosschain.*` tables, verifying guessed
  categorical filter values with `SELECT DISTINCT`, and Solana voting/non-voting
  optimized views.

  Call `get_skill(name="sql-optimization")` before writing SQL —
  especially for large tables, long time ranges, or crosschain queries.
---

# SQL Optimization for Snowflake

Apply these patterns when writing or optimizing SQL against Allium's Snowflake
warehouse, especially for multi-billion-row tables (`*.raw.transactions`,
`*.raw.transfers`, `*.raw.logs`, `*.dex.trades`).

## Verify Guessed Categorical Filters

Before committing to guessed categorical values in a `WHERE` clause, run a
quick exploration query to verify the values present in the table. Use schema
descriptions to choose the table and column; use data exploration when the
exact stored value is not already confirmed.

**For categorical/text columns** you plan to filter on (e.g., `coin`,
`market_type`, `pair`, `status`, `type`, `dex_name`, `chain`):

- Run `SELECT DISTINCT <column> FROM <table> [WHERE <recent_timestamp_filter>] LIMIT 50` first
- On large tables, scope with a recent timestamp filter (e.g., `WHERE timestamp >= DATEADD('day', -7, CURRENT_DATE())`) to keep exploration cheap
- For small metadata/dimension tables, a bare `SELECT DISTINCT` is fine

If schema content or sample rows show the exact stored value, use that spelling.
If they do not, verify the value from the table before using it as a filter.

**If a query unexpectedly returns 0 rows, check categorical filters before
concluding the data is missing.** Re-run with `SELECT DISTINCT` or a `LIKE
'%term%'` pattern.

## SQL Pitfalls (Always Apply)

**Date-filter every query on large tables.** Most large tables
(`*.raw.transactions`, `*.raw.transfers`, `*.dex.trades`, `*.raw.logs`) are
clustered on `block_timestamp::date` or `timestamp::date`. Without a date
filter, queries scan the entire table — billions of rows, slow and expensive.
Always include a bounded date range (e.g., `WHERE block_timestamp >=
'2026-01-01'`). If the user didn't specify a range, pick a default (last 30
days for activity, last 1 day for current state) and state the assumption.

**Default to recent time windows when the user is silent:**

- **7 days** (1 week) — for exploratory queries, quick analysis
- **30 days** (1 month) — for trend analysis, aggregations

```sql
-- 1 week (default for exploration)
WHERE block_timestamp >= DATEADD(day, -7, CURRENT_DATE())

-- 1 month (for trends)
WHERE block_timestamp >= DATEADD(day, -30, CURRENT_DATE())
```

If the user needs data beyond 30 days: confirm first ("This query covers a
large time range and may be slow. Is that okay?"), consider breaking into
smaller batches (e.g., monthly), or use more aggressive filtering on other
columns.

**Lowercase EVM addresses before filtering or joining — but only EVM.** Allium
stores EVM addresses (`0x...` hex) lowercase, while users and block explorers
often paste them checksummed. `WHERE from_address = '0xAbC123...'` on an EVM
table silently returns 0 rows; apply `LOWER()` to user-supplied EVM addresses.
**Do NOT lowercase non-EVM addresses.** Solana, Tron, Sui, and other non-EVM
chains store addresses in their native case-sensitive form (e.g., Solana
base58 `HyperSPG8w4j...`); lowercasing changes the address's meaning. In
`crosschain.*` tables, key on `chain` to decide whether to apply `LOWER()` per
row.

**Prefer per-chain tables; crosschain joins need `(chain, address)`.** The
`crosschain.*` schemas are convenient but significantly slower than per-chain
tables, especially when Solana is involved. When the user's question targets a
single chain, use the per-chain table (e.g., `ethereum.dex.trades`) instead of
`crosschain.dex.trades`. Reach for `crosschain.*` only when actually comparing
across 3+ chains. When you do use crosschain tables, the same address (e.g.,
USDC) exists on many chains with different state — always join on `(chain,
address)`, not address alone, and lowercase the address only when the row's
chain is EVM.

## Prefer Pre-Aggregated Metrics Tables

When a question can be answered from a `*.metrics.*` table, use it instead of
aggregating from raw. Daily aggregates (e.g., `hyperliquid.metrics.overview`,
`ethereum.metrics.dex_overview`, `crosschain.metrics.overview`) are orders of
magnitude faster and cheaper than `SUM(...) GROUP BY day` over raw trades,
transfers, or transactions.

When the user asks for a metric — volume, TVL, trade count, active users,
fees, market cap, open interest, etc. — `search_schemas` for a `metrics` table
first. Only fall back to raw tables when the metrics table lacks the dimension
or granularity the question needs (e.g., per-wallet breakdown, sub-daily
resolution).

## Snowflake Performance Patterns

Apply these patterns when writing queries against multi-billion-row tables.

### Filter in CTEs before joining

Pre-reduce each table in its own CTE before the join. Joining two
already-filtered datasets is vastly cheaper than filtering the joined result.
Especially effective when filtering on the timestamp column.

```sql
-- bad: query engine *might not* push block_timestamp down to table_b
with
cte_a as (select * from table_a where block_timestamp between '2025-01-01' and '2025-01-02'),
cte_b as (select * from table_b)
select * from cte_a join cte_b using (block_timestamp);

-- good: filter both sides explicitly
with
cte_a as (select * from table_a where block_timestamp between '2025-01-01' and '2025-01-02'),
cte_b as (
  select * from table_b
  where block_timestamp between '2025-01-01' and '2025-01-02'
)
select * from cte_a join cte_b using (block_timestamp);
```

### Partition pruning does NOT work with subquery values

Snowflake's partition pruning only fires on literal values, not subquery
results ([snowflake docs](https://docs.snowflake.com/en/user-guide/tables-clustering-micropartitions#query-pruning)).

```sql
-- bad: pruning doesn't fire
select * from table_a where block_timestamp >= (select min(timestamp) from table_b);

-- good: precompute and inline a literal
select * from table_a where block_timestamp >= '2025-01-01';
```

### Join the big table once

When looking up multiple address sets against a large table, `UNION ALL` the
lookups into a single CTE first, then join the big table only once — rather
than running multiple joins or repeated queries.

### Use `QUALIFY` for latest-per-group / dedup

Snowflake's `QUALIFY` filters window function results inline — no wrapper CTE
needed, often faster:

```sql
SELECT token_address, block_timestamp, price
FROM ethereum.dex.trades
WHERE block_timestamp >= DATEADD('day', -7, CURRENT_DATE())
QUALIFY ROW_NUMBER() OVER (PARTITION BY token_address ORDER BY block_timestamp DESC) = 1;
```

### `UNION ALL`, not `UNION`

Plain `UNION` runs an implicit `DISTINCT` across the full result — an
expensive shuffle. Use `UNION ALL` unless you specifically need to dedupe
across the two sides.

### `APPROX_COUNT_DISTINCT` for exploration

Exact `COUNT(DISTINCT ...)` on billions of rows is slow (full shuffle). HLL
approximation is orders of magnitude faster with ~1% error — fine for
exploratory analysis where a ballpark is enough.

### Batch large time ranges

When querying a large time range, concurrent queries of smaller time intervals
might work better — e.g., when querying a year's data, 12 batches of 1 month
each is probably faster than one yearly query.

### Clustering and join conditions

Most time-based tables are clustered by `block_timestamp::date`, so it's
useful to have `block_timestamp` in join conditions. Filter on the clustered
column first:

```sql
-- good: filters on clustered column first
SELECT * FROM ethereum.raw.transactions
WHERE block_timestamp >= '2024-01-01'
  AND from_address = '0x...';
```

### Select only the columns you need

Avoid `SELECT *` on wide tables. Snowflake is columnar — pruning unread
columns directly reduces scan cost.

## Ecosystem-specific tips

### Solana

1. When querying raw entities such as transactions, instructions,
   inner_instructions, if you do not need voting data, use one of the
   corresponding optimized views:

   - transactions:
     - `success_nonvoting_transactions`
     - `nonvoting_transactions`
   - instructions:
     - `success_nonvoting_instructions`
     - `nonvoting_instructions`
   - inner_instructions:
     - `success_nonvoting_inner_instructions`
     - `nonvoting_inner_instructions`
   - inner_outer_instructions:
     - `success_nonvoting_inner_outer_instructions`
     - `nonvoting_inner_outer_instructions`

2. Filter out failed/success and voting/nonvoting records:

   - for transactions, use `success` and `is_voting`
   - for (inner)instructions, use `parent_tx_success` and `is_voting`

3. For transaction meta columns, the rpc returns pre/post token/native
   balances. We have reformatted these cols to be more user-friendly in the
   columns `sol_amounts`, `mint_to_decimals`, `token_accounts` for your
   convenience (only available for `success_nonvoting_transactions`). For
   more info, refer to [transaction-level columns](/historical-data/supported-blockchains/solana#transaction-level-columns).

4. For transaction fees, see [solana.raw.fees](https://docs.allium.so/historical-data/supported-blockchains/solana/raw/fees#fees);
   they are also available in [transaction-level columns](/historical-data/supported-blockchains/solana#transaction-level-columns)
   for convenience.

## More Snowflake patterns

### Join on clustered columns

When a query `JOIN`s two tables, prefer joining on **clustered columns**. Most
time-based tables are clustered on `block_timestamp` (or `timestamp`), so
including that column in the join condition lets Snowflake prune partitions on
both sides. Check the schema/metadata to confirm which columns are clustered
before choosing a join key.

### Prefer `ASOF JOIN` for nearest-time / padding joins

When joining two tables on the nearest preceding value (e.g. attaching the most
recent price to each trade, or filling padding values across time), use
Snowflake's `ASOF JOIN`. It is simpler and usually more efficient than
hand-rolled window-function or range-partitioning approaches.

### Avoid recursive CTEs

Avoid recursion in SQL. Recursive queries are hard to reason about without
knowledge of table cluster mappings and tend to perform poorly. Re-express the
logic with ordinary (non-recursive) CTEs instead.

### Convert hex strings to numbers with the secure UDFs

To convert a hex string to a number in Snowflake:

1. Strip the `0x` prefix from the hex string.
2. Use `COMMON.UDFS.JS_HEXTOINT_SECURE(<varchar>)` to convert it.
3. If the value is **little-endian** encoded, use
   `COMMON.UDFS.JS_HEXTOINT_LITTLEENDIAN_SECURE(<varchar>)` instead.

### dbt incremental models and partition pruning

Snowflake partition pruning does **not** fire on values from a subquery. In a
dbt model with `materialization='incremental'`, don't put
`max(timestamp) from {{ this }}` directly in the incremental `WHERE` — precompute
the timestamp into a literal first (e.g. a macro that queries `{{ this }}` and
returns a single value), then use that literal everywhere a time filter applies.

## SQL style conventions

These keep generated SQL correct and readable.

### Always specify `NULLS LAST` in `ORDER BY`

Snowflake sorts `NULL`s first by default for descending order, which surfaces
empty rows at the top. Add `NULLS LAST` to `ORDER BY` clauses so nulls don't
lead the result set:

```sql
ORDER BY usd_amount DESC NULLS LAST
```

### Comment every CTE

For each CTE, add a short comment at the top describing its purpose, the data it
processes, and any important transformations or filters. This makes multi-CTE
queries maintainable:

```sql
with
-- recent_trades: last 7 days of ethereum DEX trades, filtered early on the
-- clustered block_timestamp column to keep the scan small
recent_trades as (
    select * from ethereum.dex.trades
    where block_timestamp >= dateadd(day, -7, current_date())
)
select * from recent_trades;
```

View saved version →

---
name: vercel-app-deployment
description: |
  **Required for Vercel app tools.**

  Read this skill BEFORE using create_vercel_app to understand
  app types, configuration options, and the iteration workflow.
---

# Vercel App Deployment

Generate and deploy Next.js apps powered by Allium data APIs.

## App Types

### wallet_tracker

Track wallet holdings, PnL, and transaction history.

**Features:**

- Token holdings with USD values
- PnL chart showing portfolio value over time
- Transaction history with send/receive indicators
- Multi-chain support

**Options:**

```json
{
  "show_pnl": true,           // Show PnL chart (default: true)
  "show_transactions": true   // Show transaction history (default: true)
}
```

**Example:**

```json
{
  "app_type": "wallet_tracker",
  "title": "My Wallet Tracker",
  "chains": ["ethereum", "polygon", "arbitrum"],
  "theme": "dark",
  "options": {"show_pnl": true, "show_transactions": true}
}
```

### token_analytics

Analyze token prices and market stats.

**Features:**

- Price chart with time range selection (24H, 7D, 30D, 90D)
- Token stats cards (price, price change 24h)

**Options:**

```json
{
  "show_stats": true    // Show stats cards (default: true)
}
```

**Example:**

```json
{
  "app_type": "token_analytics",
  "title": "Token Analyzer",
  "chains": ["ethereum", "base"],
  "theme": "light"
}
```

### price_chart

Dedicated OHLCV price chart with dynamic coloring and detailed stats.

**Features:**

- OHLCV (Open, High, Low, Close, Volume) price chart
- Dynamic green/red coloring based on price direction
- Detailed OHLC tooltip showing all price data points
- Stats card with current price, 24h change, 24h high/low
- Time range selection (24H, 7D, 30D, 90D)
- Optional pre-configured token address

**Options:**

```json
{
  "default_token_address": null,  // Pre-configured token (default: null)
  "default_chain": null,          // Pre-configured chain (default: first chain)
  "show_ohlc_details": true,      // Show OHLC in tooltip (default: true)
  "show_stats_card": true,        // Show price stats card (default: true)
  "show_token_selector": true     // Allow changing token (default: true)
}
```

**Example:**

```json
{
  "app_type": "price_chart",
  "title": "WETH Price Tracker",
  "chains": ["ethereum", "base", "arbitrum"],
  "theme": "dark",
  "options": {
    "default_token_address": "0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2",
    "show_ohlc_details": true
  }
}
```

**When to use price_chart vs token_analytics:**

- Use `price_chart` when the focus is on detailed price visualization with OHLCV data and dynamic coloring
- Use `token_analytics` for general token analysis with market cap, volume, and liquidity stats

### wallet_flows

Visualize wallet inflows and outflows as a Sankey diagram.

**Features:**

- Sankey flow chart showing token transfers
- Table view with detailed inflow/outflow data
- Aggregates transfers by counterparty

**Example:**

```json
{
  "app_type": "wallet_flows",
  "title": "Wallet Flow Analyzer",
  "chains": ["ethereum", "polygon"],
  "theme": "dark"
}
```

### custom_dashboard

Custom SQL dashboard with configurable widgets.

**Features:**

- Data tables from Allium Explorer queries
- Charts (line, bar, pie) from query results
- Metric cards for single values

**Options:**

```json
{
  "widgets": [
    {
      "type": "metric",
      "title": "Total Users",
      "query_id": "abc123"
    },
    {
      "type": "chart",
      "title": "Daily Active Users",
      "query_id": "def456",
      "chart_type": "line"
    },
    {
      "type": "table",
      "title": "Top Wallets",
      "query_id": "ghi789"
    }
  ]
}
```

Widget types:

- `metric`: Single value (first cell of query result)
- `chart`: Line, bar, or pie chart
- `table`: Data table with all results

## Supported Chains

- `ethereum` - Ethereum Mainnet
- `polygon` - Polygon PoS
- `arbitrum` - Arbitrum One
- `optimism` - Optimism
- `base` - Base
- `avalanche` - Avalanche C-Chain
- `bsc` - BNB Smart Chain
- `solana` - Solana

## Workflow

### Initial Creation

1. Call `create_vercel_app` with app type and options
2. Tool generates Next.js app and deploys to Vercel
3. Returns `app_id`, `deployment_url`, and `claim_url`
4. User clicks `claim_url` to take ownership on Vercel
5. User adds `ALLIUM_API_KEY` in Vercel project settings

### Iteration

1. `list_vercel_app_files` - See file structure
2. `read_vercel_app_file` - Read files to modify
3. `write_vercel_app_file` - Update files (can batch multiple writes)
4. `deploy_vercel_app` - Deploy changes

**Example iteration:**

```text
User: "Add a dark mode toggle"

1. list_vercel_app_files(app_id="abc123")
   -> See: app/layout.tsx, components/...

2. read_vercel_app_file(app_id="abc123", file_path="app/layout.tsx")
   -> Get current layout code

3. write_vercel_app_file(app_id="abc123", file_path="components/ThemeToggle.tsx", content="...")
   -> Create new component

4. write_vercel_app_file(app_id="abc123", file_path="app/layout.tsx", content="...")
   -> Update layout to include toggle

5. deploy_vercel_app(app_id="abc123")
   -> Deploy updated app
```

## Post-Deployment Setup

After claiming the app, the user must:

1. Go to Vercel project settings
2. Navigate to Environment Variables
3. Add: `ALLIUM_API_KEY` = their API key from <https://app.allium.so/settings/api-keys>
4. Redeploy the app (or wait for next deployment)

## File Structure

Generated apps follow this structure:

```text
app/
  layout.tsx      # Root layout with metadata
  page.tsx        # Main page component
  globals.css     # Global styles
  api/            # API routes (proxy to Allium)
    wallet/
      balances/route.ts
      pnl/route.ts
      transactions/route.ts
components/
  Holdings.tsx    # Feature components
  WalletInput.tsx
  ChainSelector.tsx
  ...
package.json      # Dependencies
tsconfig.json     # TypeScript config
tailwind.config.js
next.config.js
```

## Tips

- **Theme**: `dark` works best for blockchain apps
- **Chains**: Include chains your users are most likely to use
- **Custom Dashboard**: Create queries in Allium Explorer first, then reference their IDs
- **Iteration**: Make multiple file changes before deploying to batch updates