<!-- llms-full.txt: complete Microquery documentation for LLM ingestion -->
<!-- Sections: API Reference · Datasets · Examples · FAQ -->

# Microquery API Reference

Base URL: `https://microquery.dev`

All amounts are in **micro-USDC** (1 USDC = 1,000,000 micro-USDC).

______________________________________________________________________

## Getting Started

```bash
# 1. Register — get an API key and $0.10 free trial credit
curl -X POST https://microquery.dev/v1/register \
  -H "Content-Type: application/json" \
  -d '{"name": "my-agent"}'
# → { "id": "...", "api_key": "a3f1b2c9...", "balance": 100000 }

# 2. Query
curl -G https://microquery.dev/query \
  -H "Authorization: Bearer YOUR_API_KEY" \
  --data-urlencode "database=nvd" \
  --data-urlencode "query=SELECT id, description FROM cve WHERE cvss_score > 9 LIMIT 5"
# → ndjson rows + X-Microquery-Cost-MicroUSDC and X-Microquery-Balance-MicroUSDC headers
```

No wallet required. The trial credit covers thousands of small queries. When the
trial runs out, [add a wallet and deposit USDC](#deposits).

______________________________________________________________________

## Registration

`POST /v1/register` — public, no authentication required.

| Field         | Type   | Required | Description                                                                                                                                 |
| ------------- | ------ | -------- | ------------------------------------------------------------------------------------------------------------------------------------------- |
| `name`        | string | yes      | Display name, max 64 characters                                                                                                             |
| `wallet_addr` | string | no       | Ethereum wallet address (`0x` + 40 hex chars). Optional at registration — enables account recovery and deposits. See [Deposits](#deposits). |

**Response `201 Created`:**

```json
{
  "id": "acct_...",
  "api_key": "...",
  "name": "my-agent",
  "balance": 100000,
  "wallet_addr": "0x...",
  "first_query": {
    "database": "nvd",
    "sql": "SELECT id, description FROM cve WHERE cvss_score > 9 LIMIT 3",
    "note": "Trial balance covers all example scripts. Execute via GET /query?database=nvd&query=..."
  },
  "deposit_instructions": {
    "chain": "Base",
    "token": "USDC",
    "minimum_micro_usdc": 250000,
    "endpoint": "POST /v1/deposit",
    "method": "EIP-2612 permit: sign permit off-chain, send amount+deadline+v+r+s with Bearer auth",
    "docs_url": "https://microquery.dev/docs.md#deposits"
  },
  "quickstart_url": "https://microquery.dev/v1/agent-quickstart"
}
```

- Every new account starts with **100,000 micro-USDC ($0.10)** free trial
  credit.
- Rate limited to **3 accounts per IP per 24 hours**.
- Store the `api_key` securely — it is shown only once. Agents with a linked
  wallet can recover their account ID via `GET /v1/wallets/{addr}`.

### Agent bootstrap endpoint

`GET /v1/agent-quickstart` — public, no authentication required.

Returns a self-contained JSON document designed to be injected directly into an
agent's context. The response describes every step needed for an autonomous
agent to register, run its first query, and deposit funds — without consulting
external documentation.

```bash
curl https://microquery.dev/v1/agent-quickstart
```

The response includes `service`, `registration`, `authentication`, `query`,
`first_query`, `pricing`, `deposit`, `datasets`, and a `discovery` section
documenting `GET /v1/databases`. This endpoint is the canonical MCP tool
manifest entry point.

______________________________________________________________________

## Authentication

### Bearer token

```
Authorization: Bearer YOUR_API_KEY
```

The standard method. Use this for all queries once you have an API key from
`POST /v1/register`. The Bearer token is a stable credential — no per-query
signing required.

### EIP-712 signed authorization (advanced — per-query spending caps)

```
Authorization: EIP712 BASE64_ENCODED_PAYLOAD
```

For agents that want a cryptographic per-query spending cap. Each request
carries a signed authorization binding the query to a maximum cost — the server
rejects if the actual scan would exceed `max_cost`. Requires a linked wallet.
Most agents do not need this; the deposit balance is the effective spending cap.

The payload is a base64-encoded JSON object:

```json
{
  "consumer":   "0xYOUR_WALLET",
  "max_cost":   "5000",
  "database":   "nvd",
  "query_hash": "0x...",
  "nonce":      1,
  "deadline":   1234567890,
  "signature":  "0x..."
}
```

______________________________________________________________________

## Querying

`GET /query` or `POST /query`

| Parameter  | Description                           |
| ---------- | ------------------------------------- |
| `database` | Database name (e.g., `nvd`, `pubmed`) |
| `query`    | SQL query string                      |

**Example:**

```bash
curl -G https://microquery.dev/query \
  -H "Authorization: Bearer YOUR_API_KEY" \
  --data-urlencode "database=pubmed" \
  --data-urlencode "query=SELECT title FROM baseline LIMIT 10"
```

Response is **newline-delimited JSON** (`application/x-ndjson`), one object per
row.

For agents without a pre-funded account that want to pay per query without
registration, see [Advanced: x402](#advanced-x402).

### Response headers

| Header                           | Description                                                                                                           |
| -------------------------------- | --------------------------------------------------------------------------------------------------------------------- |
| `X-Microquery-Cost-MicroUSDC`    | Actual cost of this query in micro-USDC                                                                               |
| `X-Microquery-Balance-MicroUSDC` | Remaining balance after this query in micro-USDC                                                                      |
| `X-Microquery-Bytes-Scanned`     | Decompressed bytes scanned                                                                                            |
| `X-Microquery-Query-ID`          | Opaque query identifier                                                                                               |
| `X-Microquery-Auto-Limit`        | Set to `1000` when a LIMIT clause was automatically injected — add an explicit `LIMIT` to your query to suppress this |
| `X-Microquery-Hard-Limit`        | Set to `10000` when your explicit LIMIT was clamped to the server maximum                                             |
| `X-Payment-Transaction`          | On-chain settlement tx hash (x402 per-query only)                                                                     |
| `X-Payment-Account-Key`          | `api_key` for this wallet — returned on first x402 per-query payment only                                             |

### HTTP status codes

| Code  | Meaning                                                                                                                    |
| ----- | -------------------------------------------------------------------------------------------------------------------------- |
| `200` | Query executed; results follow                                                                                             |
| `402` | Either insufficient balance (`{"error":"insufficient balance",...}`) or x402 challenge (`{"x402Version":1,"accepts":[…]}`) |
| `401` | Missing authorization                                                                                                      |
| `403` | Invalid token or signature                                                                                                 |

______________________________________________________________________

## Pricing

**150 micro-USDC per GiB** of decompressed bytes scanned (~$0.15 / TB).

- Trial credit: **100,000 micro-USDC ($0.10)** on every new account (no deposit
  needed)
- Minimum deposit: **250,000 micro-USDC (0.25 USDC)**
- `GET /v1/pricing` returns the current rate as JSON

```json
{ "micro_usdc_per_gib": 150, "description": "..." }
```

### Cost estimation

`GET /v1/estimate` or `POST /v1/estimate` — authenticated, **free** (no charge
deducted).

Returns the estimated maximum bytes that would be scanned and the corresponding
cost for a query, without executing it. Useful for checking affordability or
comparing query variants before committing.

```bash
curl -G https://microquery.dev/v1/estimate \
  -H "Authorization: Bearer YOUR_API_KEY" \
  --data-urlencode "database=pubmed" \
  --data-urlencode "query=SELECT * FROM baseline WHERE MedlineCitation.Article.ArticleTitle ~ 'CRISPR'"
```

```json
{
  "database": "pubmed",
  "estimated_bytes_scanned": 53687091200,
  "estimated_cost_micro_usdc": 7500
}
```

`estimated_bytes_scanned` is a conservative upper bound — actual scan size at
execution time will be equal or lower.

______________________________________________________________________

## Databases

`GET /v1/databases` — public, no authentication required.

Returns an object with a `databases` array (each entry has `name`, `tables`, and
per-table `fields` and `partitioning` summary) and a `_hint` field describing
available query parameters.

**Query parameters**

| Parameter    | Description                                                                                                                                                                                     |
| ------------ | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `partitions` | Set to `true` to include a per-partition byte breakdown for each table. Default: omitted. Response grows to ~500 KB uncompressed; use only when reasoning about specific date or year coverage. |

**Default response shape**

```json
{
  "databases": [
    {
      "name": "pubmed",
      "tables": [
        {
          "name": "baseline",
          "fields": ["MedlineCitation.PMID", "..."],
          "partitioning": {
            "key": "pub_year",
            "type": "int",
            "total_bytes": 137737797632,
            "updated_at": "2026-04-15T18:17:35Z"
          }
        }
      ]
    }
  ],
  "_hint": "Add ?partitions=true to include per-partition byte breakdown for each table."
}
```

With `?partitions=true` each `partitioning` object also includes a `partitions`
array of `{"value": "...", "bytes": N}` entries sorted by partition value.

**Available databases**

| Database         | Tables                                                                                  | Total size |
| ---------------- | --------------------------------------------------------------------------------------- | ---------- |
| `nvd`            | `cve`                                                                                   | ~0.7 GB    |
| `osv`            | `advisories`                                                                            | ~0.4 GB    |
| `sec`            | `edgar`                                                                                 | ~6 GB      |
| `clinicaltrials` | `studies`                                                                               | ~0.7 GB    |
| `fda`            | `faers`                                                                                 | ~6.5 GB    |
| `pubmed`         | `baseline`                                                                              | ~138 GB    |
| `btc`            | `blocks`, `outputs`                                                                     | ~121 GB    |
| `eth`            | `blocks`, `contracts`, `dex_swaps`, `lending`, `lp_events`, `transactions`, `transfers` | ~234 GB    |

______________________________________________________________________

## Deposits

**You do not need a wallet to start.** The $0.10 trial credit requires only
`POST /v1/register`. A wallet is only needed when you want to top up.

### Wallet setup

Generate a dedicated wallet for the agent in code, then send USDC to it from
your personal wallet. Do not share your personal wallet's private key with an
agent.

```javascript
import { ethers } from "ethers";

// Generate once, store the private key securely
const wallet = ethers.Wallet.createRandom();
console.log("address:     ", wallet.address);
console.log("private_key: ", wallet.privateKey);

// Load at startup
const wallet = new ethers.Wallet(process.env.AGENT_PRIVATE_KEY);
```

Send USDC (on Base) to the agent address from Coinbase, MetaMask, Rabby, or any
Base-compatible wallet. Minimum **$0.25 USDC**; $1–5 covers thousands of
queries. To get USDC on Base: withdraw from Coinbase exchange directly to Base,
bridge via [bridge.base.org](https://bridge.base.org), or use any CEX that
supports Base withdrawals.

In production, give each agent instance its own wallet — independent balances,
per-agent spend visibility, and isolation if one is compromised.

### Gasless permit deposit (EIP-2612)

Top up your account balance with USDC on Base. The operator sponsors gas — no
ETH required.

### Gasless permit deposit (EIP-2612)

Sign a permit off-chain, send it to `POST /v1/deposit` with Bearer auth. The
server submits `depositWithPermit()` on-chain using the operator key.

**Step 1** — sign an EIP-2612 permit off-chain:

```python
domain = {
    "name": "USD Coin", "version": "2",
    "chainId": 8453,
    "verifyingContract": "0x833589fCD6eDb6E08f4c7C32D4f71b54bdA02913"
}
types = {"Permit": [
    {"name": "owner",   "type": "address"},
    {"name": "spender", "type": "address"},
    {"name": "value",   "type": "uint256"},
    {"name": "nonce",   "type": "uint256"},
    {"name": "deadline","type": "uint256"}
]}
message = {
    "owner":    YOUR_WALLET,
    "spender":  ESCROW_ADDRESS,
    "value":    amount,        # micro-USDC (1 USDC = 1_000_000)
    "nonce":    usdc.nonces(YOUR_WALLET),   # call USDC contract
    "deadline": int(time.time()) + 600
}
v, r, s = sign_typed_data(domain, types, message, private_key)
```

**Step 2** — POST to the server (Bearer auth required):

`POST /v1/deposit`

```json
{
  "amount":   1000000,
  "deadline": 1734567890,
  "v": 28,
  "r": "0x...",
  "s": "0x..."
}
```

Response:

```json
{
  "tx_hash":        "0x...",
  "amount":         1000000,
  "balance":        1005000,
  "transaction_id": "..."
}
```

The account is credited once the transaction is mined (~2 s on Base).

Escrow contract: `0xb1f8eE89bc8E51558a3C2A216620aBa1b7B2d01A` (Base mainnet)

______________________________________________________________________

## Advanced: x402

The [x402 protocol](https://x402.org) is an alternative payment path for agents
that are already crypto-native and prefer on-chain authorization without
pre-registration. Most agents should use the Bearer + deposit path above.

### x402 deposit — auto-register on first payment

An x402 deposit auto-creates an account on first use. No prior registration
required. Sign an EIP-3009 `transferWithAuthorization` and POST to `/v1/deposit`
with an `X-PAYMENT` header.

**Step 1** — sign EIP-3009 `transferWithAuthorization`:

```python
from eth_account import Account
from eth_account.messages import encode_typed_data
import time, secrets

domain = {
    "name": "USD Coin", "version": "2",
    "chainId": 8453,
    "verifyingContract": "0x833589fCD6eDb6E08f4c7C32D4f71b54bdA02913",
}
types = {
    "EIP712Domain": [
        {"name": "name",              "type": "string"},
        {"name": "version",           "type": "string"},
        {"name": "chainId",           "type": "uint256"},
        {"name": "verifyingContract", "type": "address"},
    ],
    "TransferWithAuthorization": [
        {"name": "from",        "type": "address"},
        {"name": "to",          "type": "address"},
        {"name": "value",       "type": "uint256"},
        {"name": "validAfter",  "type": "uint256"},
        {"name": "validBefore", "type": "uint256"},
        {"name": "nonce",       "type": "bytes32"},
    ],
}
message = {
    "from":        YOUR_WALLET,
    "to":          "0x5Da586b29c3bb0f86cf820b9ac4331B289a6e50B",  # operator
    "value":       250000,          # minimum deposit: 250,000 micro-USDC
    "validAfter":  0,
    "validBefore": int(time.time()) + 300,
    "nonce":       "0x" + secrets.token_hex(32),
}
acct = Account.from_key(YOUR_PRIVATE_KEY)
signed = acct.sign_message(encode_typed_data(full_message={
    "types": types, "domain": domain,
    "primaryType": "TransferWithAuthorization", "message": message,
}))
signature = "0x" + signed.signature.hex()
```

**Step 2** — build the x402 payment payload and POST:

```python
import base64, json, urllib.request

payload = {
    "x402Version": 1, "scheme": "exact", "network": "base",
    "payload": {
        "signature": signature,
        "authorization": {
            "from":        message["from"],
            "to":          message["to"],
            "value":       str(message["value"]),
            "validAfter":  str(message["validAfter"]),
            "validBefore": str(message["validBefore"]),
            "nonce":       message["nonce"],
        },
    },
}
x_payment = base64.urlsafe_b64encode(
    json.dumps(payload).encode()
).decode().rstrip("=")

req = urllib.request.Request(
    "https://microquery.dev/v1/deposit",
    method="POST",
    headers={"X-PAYMENT": x_payment, "Content-Type": "application/json"},
    data=b"{}",
)
with urllib.request.urlopen(req) as resp:
    print(json.loads(resp.read()))
```

**First-deposit response `201 Created`:**

```json
{
  "api_key":            "...",
  "balance_micro_usdc": 250000,
  "transaction":        "0x...",
  "network":            "base"
}
```

Save the returned `api_key` — use it as a Bearer token for subsequent queries.

### x402 per-query — no registration, no pre-deposit

Each query is a self-contained payment. The server returns a 402 challenge with
the exact cost; the agent signs EIP-3009 for that amount and retries. **Minimum:
10,000 micro-USDC ($0.01) per query.**

Note: at microquery's typical query costs ($0.0001–$0.012), the x402 facilitator
fee ($0.001/tx) represents significant overhead. This path is best suited for
higher-value queries or agents already integrated with the x402 protocol.

**Step 1 — send the query without auth to get the cost:**

```bash
curl -s -o challenge.json -w "%{http_code}" \
  -X POST "https://microquery.dev/query?database=defi" \
  -H "Content-Type: text/plain" \
  --data "SELECT name, tvl FROM protocols ORDER BY tvl DESC LIMIT 5"
# prints: 402
```

```json
{
  "x402Version": 1,
  "accepts": [{
    "scheme": "exact", "network": "base",
    "maxAmountRequired": "10000",
    "payTo": "0x5da586b29c3bb0f86cf820b9ac4331b289a6e50b",
    "asset": "0x833589fCD6eDb6E08f4c7C32D4f71b54bdA02913",
    "maxTimeoutSeconds": 60
  }]
}
```

**Step 2 — sign EIP-3009 and retry:**

```python
import base64, json, secrets, time
from eth_account import Account
from eth_account.messages import encode_typed_data

amount    = int(challenge["accepts"][0]["maxAmountRequired"])
pay_to    = challenge["accepts"][0]["payTo"]
usdc_addr = challenge["accepts"][0]["asset"]

acct  = Account.from_key(PRIVATE_KEY)
nonce = "0x" + secrets.token_hex(32)

signed = acct.sign_message(encode_typed_data(full_message={
    "types": {
        "EIP712Domain": [
            {"name": "name",              "type": "string"},
            {"name": "version",           "type": "string"},
            {"name": "chainId",           "type": "uint256"},
            {"name": "verifyingContract", "type": "address"},
        ],
        "TransferWithAuthorization": [
            {"name": "from",        "type": "address"},
            {"name": "to",          "type": "address"},
            {"name": "value",       "type": "uint256"},
            {"name": "validAfter",  "type": "uint256"},
            {"name": "validBefore", "type": "uint256"},
            {"name": "nonce",       "type": "bytes32"},
        ],
    },
    "domain": {
        "name": "USD Coin", "version": "2",
        "chainId": 8453, "verifyingContract": usdc_addr,
    },
    "primaryType": "TransferWithAuthorization",
    "message": {
        "from": acct.address, "to": pay_to, "value": amount,
        "validAfter": 0, "validBefore": int(time.time()) + 300, "nonce": nonce,
    },
}))

payload = {
    "x402Version": 1, "scheme": "exact", "network": "base",
    "payload": {
        "signature": "0x" + signed.signature.hex(),
        "authorization": {
            "from": acct.address, "to": pay_to, "value": str(amount),
            "validAfter": "0", "validBefore": str(int(time.time()) + 300),
            "nonce": nonce,
        },
    },
}
x_payment = base64.urlsafe_b64encode(
    json.dumps(payload).encode()
).decode().rstrip("=")

import requests
resp = requests.post(
    "https://microquery.dev/query?database=defi",
    headers={"Content-Type": "text/plain", "X-PAYMENT": x_payment},
    data="SELECT name, tvl FROM protocols ORDER BY tvl DESC LIMIT 5",
)
# resp.headers["X-Payment-Transaction"] — on-chain settlement tx hash
# resp.headers.get("X-Payment-Account-Key") — api_key (first payment only)
```

On first payment from a wallet the server auto-creates an account and returns
the `api_key` in `X-Payment-Account-Key`. Save it — use it as a Bearer token for
subsequent queries.

______________________________________________________________________

## Account Management

### Get account details

`GET /v1/accounts/{id}` — requires Bearer auth (your API key).

Returns `id`, `name`, `balance`, `wallet_addr`, `created_at`.

### List transactions

`GET /v1/accounts/{id}/transactions?limit=50&offset=0` — requires Bearer auth.

### Recover account by wallet

`GET /v1/wallets/{addr}` — public.

Returns account `id`, `name`, `balance` for a linked wallet address. Does not
return the API key. Use this if an autonomous agent needs to rediscover its
account ID using only its wallet key.

### Link a wallet after registration

Linking a wallet proves ownership via a challenge/response — the server recovers
the signer from the signature and rejects if it doesn't match the claimed
address.

**Step 1** — request a challenge nonce (public, no auth):

`POST /v1/wallet-challenge`

```json
{ "wallet_addr": "0xYOUR_WALLET" }
```

Response:

```json
{
  "nonce":      "a3f1c8...",
  "message":    "microquery wallet verification\nnonce: a3f1c8...",
  "expires_in": 300
}
```

**Step 2** — sign the `message` field with `eth_personalSign` (MetaMask,
ethers.js `signer.signMessage()`, web3.py `eth.sign()`), then submit:

`POST /v1/accounts/{id}/link-wallet` — requires Bearer auth.

```json
{
  "wallet_addr": "0xYOUR_WALLET",
  "nonce":       "a3f1c8...",
  "signature":   "0x..."
}
```

The nonce expires after **5 minutes** and is consumed on first use.

### Rate limits

| Endpoint            | Limit                          |
| ------------------- | ------------------------------ |
| `POST /v1/register` | 3 accounts per IP per 24 hours |
| `GET/POST /query`   | None — balance is the throttle |

______________________________________________________________________

## Further Reading

- [Dataset schemas and example queries](https://microquery.dev/datasets.md)
- [Runnable cross-dataset examples](https://microquery.dev/examples.md)
- [FAQ](https://microquery.dev/faq.md)
- [Sample autonomous agent](https://github.com/microqueryhq/microquery-agent)

______________________________________________________________________

# Microquery Datasets

All datasets are queryable via standard SQL against
`https://microquery.dev/query`. Pricing: **150 micro-USDC per GiB** decompressed
bytes scanned (~$0.15/TB).

______________________________________________________________________

## NVD — National Vulnerability Database

**Database:** `nvd`  |  **Table:** `cve`  |  **Rows:** ~333,000

Every CVE published by NIST, including CVSS scores, severity, weaknesses, and
references. Updated periodically from the NVD API v2.0.

| Column                                           | Type   | Description                                 |
| ------------------------------------------------ | ------ | ------------------------------------------- |
| `id`                                             | string | CVE identifier (e.g. `CVE-2024-12345`)      |
| `published`                                      | string | Publication date                            |
| `lastModified`                                   | string | Last modification date                      |
| `vulnStatus`                                     | string | `Analyzed`, `Modified`, `Awaiting Analysis` |
| `descriptions[0].value`                          | string | English description                         |
| `metrics.cvssMetricV31[0].cvssData.baseScore`    | float  | CVSS v3.1 base score (0–10)                 |
| `metrics.cvssMetricV31[0].cvssData.baseSeverity` | string | `LOW` / `MEDIUM` / `HIGH` / `CRITICAL`      |
| `metrics.cvssMetricV31[0].cvssData.vectorString` | string | CVSS vector string                          |
| `weaknesses[0].description[0].value`             | string | Primary CWE identifier                      |
| `references`                                     | array  | Advisory URLs and sources                   |

```sql
-- Critical CVEs published in 2024
SELECT id, published,
       metrics.cvssMetricV31[0].cvssData.baseScore AS score,
       descriptions[0].value AS description
FROM   cve
WHERE  metrics.cvssMetricV31[0].cvssData.baseSeverity = 'CRITICAL'
  AND  published >= `2024-01-01T00:00:00Z`
ORDER BY score DESC
LIMIT 20
```

> **Note:** Timestamp columns (e.g. `published`, `lastModified`) require
> backtick-quoted ISO 8601 literals for range comparisons. Single-quoted strings
> return zero rows. See [docs/SNELLER.md](../docs/SNELLER.md).

______________________________________________________________________

## OSV — Open Source Vulnerabilities

**Database:** `osv`  |  **Table:** `advisories`  |  **Rows:** ~4,140,000

OSV.dev advisories flattened to one row per `(advisory, affected package)`.
Covers PyPI, npm, Go, Maven, Rust, Debian, Alpine, and more. Superset of NVD —
includes ecosystem-specific advisories (GHSA, PYSEC, RUSTSEC, etc.) with precise
package-version ranges.

| Column           | Type   | Description                                              |
| ---------------- | ------ | -------------------------------------------------------- |
| `id`             | string | Advisory ID (`CVE-...`, `GHSA-...`, `PYSEC-...`)         |
| `published`      | string | First published date                                     |
| `modified`       | string | Last modified date                                       |
| `withdrawn`      | string | Withdrawal date if retracted (optional)                  |
| `aliases`        | array  | Related IDs (e.g. CVE alias for a GHSA)                  |
| `summary`        | string | Short description                                        |
| `severity_type`  | string | CVSS type string (e.g. `CVSS_V3`)                        |
| `severity_score` | string | Full CVSS vector string                                  |
| `cwe_ids`        | array  | CWE weakness identifiers                                 |
| `ecosystem`      | string | `PyPI`, `npm`, `Go`, `Maven`, `crates.io`, ...           |
| `package_name`   | string | Package name within the ecosystem                        |
| `purl`           | string | Package URL (optional)                                   |
| `introduced`     | string | First affected version (`0` = all versions before fixed) |
| `fixed`          | string | First fixed version (absent if not yet fixed)            |

```sql
-- All advisories for a specific npm package
SELECT id, introduced, fixed, summary
FROM   advisories
WHERE  ecosystem = 'npm' AND package_name = 'lodash'

-- Unfixed critical advisories in PyPI
SELECT id, package_name, summary
FROM   advisories
WHERE  ecosystem = 'PyPI'
  AND  severity_score ~ 'AV:N.*C:H'
  AND  fixed IS MISSING
LIMIT 50

-- Advisory counts by ecosystem
SELECT ecosystem, COUNT(*) AS n
FROM   advisories
GROUP BY ecosystem
ORDER BY n DESC
```

______________________________________________________________________

## SEC EDGAR — Financial Facts

**Database:** `sec`  |  **Table:** `edgar`  |  **Rows:** ~97,800,000  |
**Companies:** ~8,900

Structured XBRL financial facts from SEC filings (10-K, 10-Q, 8-K). One row per
reported fact: every line item a public company files — revenue, earnings,
assets, shares outstanding, and thousands of other GAAP/IFRS concepts.

> **Note:** The `end` column (period end date) is a reserved keyword in the
> Sneller SQL engine. Do not reference it in SELECT or ORDER BY — use `filed` or
> `fy` to filter by time instead.

| Column     | Type   | Description                                              |
| ---------- | ------ | -------------------------------------------------------- |
| `cik`      | string | SEC Central Index Key                                    |
| `ticker`   | string | Stock ticker symbol                                      |
| `company`  | string | Company name                                             |
| `taxonomy` | string | `us-gaap`, `ifrs-full`, `dei`, etc.                      |
| `concept`  | string | XBRL concept name (e.g. `Revenues`, `NetIncomeLoss`)     |
| `label`    | string | Human-readable label for the concept                     |
| `unit`     | string | `USD`, `shares`, `pure`, etc.                            |
| `end`      | string | Period end date (**reserved keyword — avoid in SELECT**) |
| `val`      | float  | Reported value                                           |
| `form`     | string | Filing form (`10-K`, `10-Q`, `8-K`, ...)                 |
| `fy`       | string | Fiscal year                                              |
| `fp`       | string | Fiscal period (`FY`, `Q1`, `Q2`, `Q3`)                   |
| `filed`    | string | Filing date                                              |
| `accn`     | string | SEC accession number                                     |

```sql
-- Annual revenue for a company across fiscal years
SELECT ticker, fy, fp, val AS revenue, filed
FROM   edgar
WHERE  ticker = 'AAPL'
  AND  concept = 'RevenueFromContractWithCustomerExcludingAssessedTax'
  AND  form = '10-K'
ORDER BY fy DESC
LIMIT 10

-- Compare net income across companies for latest fiscal year
SELECT ticker, company, val AS net_income, fy
FROM   edgar
WHERE  concept = 'NetIncomeLoss'
  AND  form = '10-K'
  AND  fy = 2024
  AND  unit = 'USD'
ORDER BY net_income DESC
LIMIT 20

-- Balance sheet: total assets vs total liabilities for one company
SELECT ticker, fy, fp, concept, val, filed
FROM   edgar
WHERE  ticker = 'MSFT'
  AND  concept IN ('Assets', 'Liabilities')
  AND  form = '10-K'
ORDER BY fy DESC
LIMIT 10

-- Discover which concepts a company has reported (useful before writing queries)
SELECT concept, label, unit, COUNT(*) AS filings
FROM   edgar
WHERE  ticker = 'TSLA'
GROUP BY concept, label, unit
ORDER BY filings DESC
LIMIT 50

-- Quarterly earnings per share trend
SELECT ticker, fy, fp, val AS eps, filed
FROM   edgar
WHERE  ticker = 'NVDA'
  AND  concept = 'EarningsPerShareBasic'
  AND  form = '10-Q'
ORDER BY filed DESC
LIMIT 12
```

______________________________________________________________________

## Ethereum — On-chain Data

**Database:** `eth`  |  **Tables:** `transfers`, `blocks`, `contracts`,
`transactions`, `dex_swaps`, `lending`, `lp_events`, `mev`

On-chain Ethereum data sourced from the AWS Public Blockchain dataset. All
tables are partitioned by date with native `block_timestamp` indexing.

### Table: `transfers`

Decoded ERC-20 and ERC-721 Transfer events.

| Column             | Type   | Description                                |
| ------------------ | ------ | ------------------------------------------ |
| `block_number`     | int    | Block height                               |
| `block_timestamp`  | string | Block timestamp (ISO 8601)                 |
| `transaction_hash` | string | Transaction hash                           |
| `log_index`        | int    | Log position within the transaction        |
| `token_address`    | string | Contract address of the token              |
| `from_address`     | string | Sender address (zero-padded 32 bytes)      |
| `to_address`       | string | Recipient address                          |
| `value`            | string | Transfer amount (raw, token decimals vary) |

```sql
-- Recent USDC transfers over $1M (USDC has 6 decimals)
SELECT transaction_hash, from_address, to_address, value
FROM   transfers
WHERE  token_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'
  AND  CAST(value AS FLOAT) > 1000000000000
  AND  block_timestamp >= '2025-01-01'
LIMIT 20
```

### Table: `blocks`

One row per block (header, gas, base fee). 365 days ingested, ~770 MB scanned.

| Column              | Type   | Description                              |
| ------------------- | ------ | ---------------------------------------- |
| `number`            | int    | Block height                             |
| `timestamp`         | string | Block timestamp (ISO 8601)               |
| `hash`              | string | Block hash                               |
| `miner`             | string | Validator / miner address                |
| `gas_used`          | int    | Gas consumed by all transactions         |
| `gas_limit`         | int    | Block gas limit                          |
| `base_fee_per_gas`  | int    | EIP-1559 base fee (wei); null pre-London |
| `transaction_count` | int    | Number of transactions in the block      |
| `difficulty`        | int    | Block difficulty (0 post-Merge)          |
| `size`              | int    | Block size in bytes                      |

```sql
-- Average gas price and block utilisation over the last 7 days
SELECT DATE(timestamp) AS day,
       AVG(base_fee_per_gas) / 1e9 AS avg_base_fee_gwei,
       AVG(gas_used * 100.0 / gas_limit) AS avg_utilisation_pct
FROM   blocks
WHERE  timestamp >= '2026-04-06'
GROUP BY day ORDER BY day
```

### Table: `contracts`

One row per contract deployment. Full history 2015 – present, ~9.6 GB scanned.

| Column               | Type   | Description                                   |
| -------------------- | ------ | --------------------------------------------- |
| `address`            | string | Deployed contract address                     |
| `deployer_address`   | string | Address that deployed the contract            |
| `block_timestamp`    | string | Deployment timestamp (ISO 8601)               |
| `block_number`       | int    | Block height of deployment                    |
| `transaction_hash`   | string | Deployment transaction hash                   |
| `is_erc20`           | bool   | Detected ERC-20 token contract                |
| `is_erc721`          | bool   | Detected ERC-721 NFT contract                 |
| `function_sighashes` | array  | 4-byte function selectors present in bytecode |

```sql
-- Who deployed a contract and when?
SELECT deployer_address, block_timestamp, transaction_hash,
       is_erc20, is_erc721
FROM   contracts
WHERE  address = '0xdac17f958d2ee523a2206206994597c13d831ec7'
```

### Table: `transactions`

One row per transaction (native ETH transfers, gas spend, contract calls). ~136
GB scanned, last 365 days ingested.

| Column                        | Type   | Description                                       |
| ----------------------------- | ------ | ------------------------------------------------- |
| `transaction_hash`            | string | Transaction hash (unique identifier)              |
| `block_number`                | int    | Block height                                      |
| `block_timestamp`             | string | Block timestamp (ISO 8601)                        |
| `transaction_index`           | int    | Position of tx within the block                   |
| `from_address`                | string | Sender address                                    |
| `to_address`                  | string | Recipient address (null for contract creation)    |
| `value`                       | int    | Native ETH transferred (wei; 1 ETH = 1e18)        |
| `gas`                         | int    | Gas limit set by sender                           |
| `gas_price`                   | int    | Gas price (wei/gas; legacy type 0/1 txns)         |
| `nonce`                       | int    | Sender nonce                                      |
| `transaction_type`            | int    | 0=legacy, 1=access-list, 2=EIP-1559               |
| `max_fee_per_gas`             | int    | EIP-1559 max total fee (wei/gas)                  |
| `max_priority_fee_per_gas`    | int    | EIP-1559 tip to validator (wei/gas)               |
| `receipt_status`              | int    | 1=success, 0=reverted                             |
| `receipt_gas_used`            | int    | Actual gas consumed                               |
| `receipt_effective_gas_price` | int    | Price actually paid (base fee + tip, wei/gas)     |
| `receipt_contract_address`    | string | Deployed contract address; null if not a creation |
| `receipt_cumulative_gas_used` | int    | Cumulative gas used in block up to this tx        |

```sql
-- Top 10 gas spenders yesterday (total ETH paid in fees)
SELECT from_address,
       COUNT(*) AS tx_count,
       SUM(receipt_gas_used * receipt_effective_gas_price) / 1e18 AS eth_fees
FROM   transactions
WHERE  block_timestamp >= '2026-04-12'
  AND  block_timestamp <  '2026-04-13'
GROUP BY from_address
ORDER BY eth_fees DESC
LIMIT 10
```

### Table: `dex_swaps`

Decoded Uniswap v2 and v3 Swap events. 365 daily partitions.

| Column                       | Type   | Description                                           |
| ---------------------------- | ------ | ----------------------------------------------------- |
| `pool_address`               | string | Uniswap pool contract address                         |
| `transaction_hash`           | string | Transaction hash                                      |
| `log_index`                  | int    | Log position within the transaction                   |
| `block_number`               | int    | Block height                                          |
| `block_timestamp`            | string | Block timestamp (ISO 8601)                            |
| `protocol`                   | string | `v2` or `v3`                                          |
| `sender`                     | string | Swap initiator address                                |
| `recipient`                  | string | Swap recipient address                                |
| `amount0`                    | int    | v3: signed token0 delta (negative = token0 left pool) |
| `amount1`                    | int    | v3: signed token1 delta; null for v2                  |
| `amount0_in` / `amount0_out` | int    | v2: unsigned token0 in/out; null for v3               |
| `amount1_in` / `amount1_out` | int    | v2: unsigned token1 in/out; null for v3               |
| `sqrt_price_x96`             | int    | v3: post-swap sqrt(price) × 2^96; null for v2         |
| `liquidity`                  | int    | v3: active liquidity at time of swap; null for v2     |
| `tick`                       | int    | v3: current tick after swap; null for v2              |

```sql
-- Daily swap count and net USDC flow on the USDC/WETH 0.05% pool
-- amount0 is in USDC raw units (6 dec); positive = USDC into pool (ETH bought)
SELECT partition_date,
       COUNT(*) AS swap_count,
       SUM(amount0) / 1e6 AS net_usdc_flow
FROM   dex_swaps
WHERE  pool_address = '0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640'
  AND  block_timestamp >= '2026-04-07'
  AND  block_timestamp <  '2026-04-14'
GROUP BY partition_date ORDER BY partition_date LIMIT 10
```

### Table: `lending`

Decoded Aave v2/v3 and Compound v3 Supply, Borrow, Repay, and Liquidation
events. 365 daily partitions.

| Column              | Type   | Description                                                                                        |
| ------------------- | ------ | -------------------------------------------------------------------------------------------------- |
| `contract_address`  | string | Lending pool contract address                                                                      |
| `transaction_hash`  | string | Transaction hash                                                                                   |
| `log_index`         | int    | Log position within the transaction                                                                |
| `block_number`      | int    | Block height                                                                                       |
| `block_timestamp`   | string | Block timestamp (ISO 8601)                                                                         |
| `protocol`          | string | `aave_v2`, `aave_v3`, or `compound_v3`                                                             |
| `event_type`        | string | `supply`, `withdraw`, `borrow`, `repay`, `liquidation`, `supply_collateral`, `withdraw_collateral` |
| `asset`             | string | Token contract address (reserve asset)                                                             |
| `user`              | string | User address                                                                                       |
| `on_behalf_of`      | string | Beneficiary address (may differ from user)                                                         |
| `amount`            | int    | Raw token amount (apply asset decimals)                                                            |
| `borrow_rate_mode`  | int    | Aave borrow rate: 1=stable, 2=variable; null for non-borrow events                                 |
| `collateral_asset`  | string | Collateral token address; non-null for liquidation events only                                     |
| `collateral_amount` | int    | Collateral seized; non-null for liquidation events only                                            |

```sql
-- Daily USDC supply volume by protocol (Aave v3, Compound v3)
SELECT partition_date,
       protocol,
       COUNT(*) AS supply_events,
       SUM(amount) / 1e6 AS volume_usdc
FROM   lending
WHERE  event_type = 'supply'
  AND  asset = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'
  AND  block_timestamp >= '2026-04-07'
  AND  block_timestamp <  '2026-04-14'
GROUP BY partition_date, protocol ORDER BY partition_date LIMIT 20
```

### Table: `mev`

MEV-boost relay payloads sourced from the ultra_sound relay API. One row per
block auction bid. 365 daily partitions, ~2.2M rows.

> **Note:** The `date` partition field is stored as a datetime internally. Use
> backtick ISO 8601 literals for date range filters:
> `` `2026-03-16T00:00:00Z` ``

| Column                   | Type   | Description                                   |
| ------------------------ | ------ | --------------------------------------------- |
| `block_number`           | int    | Block height                                  |
| `block_hash`             | string | Block hash                                    |
| `slot`                   | int    | Beacon chain slot number                      |
| `timestamp`              | int    | Unix timestamp of the block                   |
| `date`                   | date   | Partition key (stored as datetime internally) |
| `relay`                  | string | Relay name (e.g. `ultra_sound`, `flashbots`)  |
| `proposer_fee_recipient` | string | Validator fee recipient address               |
| `builder_pubkey`         | string | Block builder BLS public key                  |
| `gas_used`               | int    | Gas consumed by the block                     |
| `gas_limit`              | int    | Block gas limit                               |
| `num_txs`                | int    | Number of transactions in the block           |
| `value_wei`              | int    | Block value paid to proposer (wei)            |
| `value_eth`              | float  | Block value paid to proposer (ETH)            |

```sql
-- Relay market share — block count and MEV earned (last 30 days)
SELECT relay,
       COUNT(*)                                  AS blocks,
       TRUNC(AVG(value_eth)*1000000)/1000000     AS avg_mev_eth,
       TRUNC(SUM(value_eth)*10000)/10000         AS total_mev_eth
FROM mev
WHERE date >= `2026-03-16T00:00:00Z`
GROUP BY relay
ORDER BY blocks DESC
LIMIT 10

-- Top 10 highest-value MEV blocks all time
SELECT date, block_number, relay, value_eth, num_txs
FROM mev
ORDER BY value_eth DESC
LIMIT 10
```

### Table: `lp_events`

Decoded Uniswap v3 Mint (add liquidity) and Burn (remove liquidity) events. 365
daily partitions.

| Column             | Type   | Description                                 |
| ------------------ | ------ | ------------------------------------------- |
| `pool_address`     | string | Uniswap v3 pool contract address            |
| `owner`            | string | Address that owns the position              |
| `tick_lower`       | int    | Lower bound of the price range (tick)       |
| `tick_upper`       | int    | Upper bound of the price range (tick)       |
| `transaction_hash` | string | Transaction hash                            |
| `log_index`        | int    | Log position within the transaction         |
| `block_number`     | int    | Block height                                |
| `block_timestamp`  | string | Block timestamp (ISO 8601)                  |
| `event_type`       | string | `mint` (add liquidity) or `burn` (remove)   |
| `sender`           | string | Caller address for mint; null for burn      |
| `amount`           | int    | Liquidity units added or removed            |
| `amount0`          | int    | Token0 deposited (mint) or withdrawn (burn) |
| `amount1`          | int    | Token1 deposited (mint) or withdrawn (burn) |

```sql
-- Net liquidity minted vs burned in USDC/WETH 0.05% pool (last 7 days)
SELECT event_type,
       COUNT(*) AS events,
       SUM(amount0) / 1e6  AS usdc_total,
       SUM(amount1) / 1e18 AS weth_total
FROM   lp_events
WHERE  pool_address = '0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640'
  AND  block_timestamp >= '2026-04-07'
GROUP BY event_type
```

______________________________________________________________________

## Base — On-chain Data

**Database:** `base` | **Tables:** `transfers`, `blocks`, `transactions` |
**Source:** AWS Public Blockchain (SonarX)

Base is an OP-Stack L2 rollup operated by Coinbase. Data is sourced from the
same AWS Public Blockchain dataset as Ethereum. All tables are partitioned by
`partition_date` (one partition per calendar day). ~7 days of publishing lag
applies — the most recent available date is typically 7 days ago.

### Table: `transfers`

Decoded ERC-20 Transfer events on Base. ~70 GB for 30 days.

| Column             | Type   | Description                                |
| ------------------ | ------ | ------------------------------------------ |
| `block_number`     | int    | Block height                               |
| `block_timestamp`  | string | Block timestamp (ISO 8601)                 |
| `transaction_hash` | string | Transaction hash                           |
| `log_index`        | int    | Log position within the transaction        |
| `token_address`    | string | Contract address of the token              |
| `from_address`     | string | Sender address (42-char, 0x-prefixed)      |
| `to_address`       | string | Recipient address                          |
| `value`            | int    | Transfer amount (raw, token decimals vary) |
| `partition_date`   | string | Partition key (YYYY-MM-DD, from S3 path)   |

```sql
-- Recent USDC transfers on Base over $100k (USDC has 6 decimals)
SELECT transaction_hash, from_address, to_address, value
FROM   transfers
WHERE  token_address = '0x833589fcd6edb6e08f4c7c32d4f71b54bda02913'
  AND  CAST(value AS FLOAT) > 100000000000
  AND  partition_date >= '2026-03-21'
LIMIT 20
```

### Table: `blocks`

One row per block. ~0.6 GB for 30 days.

| Column              | Type   | Description                              |
| ------------------- | ------ | ---------------------------------------- |
| `number`            | int    | Block height                             |
| `timestamp`         | string | Block timestamp (ISO 8601)               |
| `hash`              | string | Block hash                               |
| `miner`             | string | Sequencer / fee recipient address        |
| `gas_used`          | int    | Gas consumed by all transactions         |
| `gas_limit`         | int    | Block gas limit                          |
| `base_fee_per_gas`  | int    | EIP-1559 base fee (wei)                  |
| `transaction_count` | int    | Number of transactions in the block      |
| `size`              | int    | Block size in bytes                      |
| `partition_date`    | string | Partition key (YYYY-MM-DD, from S3 path) |

```sql
-- Average base fee and block utilisation over the last 7 days on Base
SELECT DATE(timestamp) AS day,
       AVG(base_fee_per_gas) / 1e9 AS avg_base_fee_gwei,
       AVG(gas_used * 100.0 / gas_limit) AS avg_utilisation_pct
FROM   blocks
WHERE  partition_date >= '2026-04-06'
GROUP BY day ORDER BY day
```

### Table: `transactions`

One row per transaction. Includes OP-Stack L2-specific fee fields not present in
Ethereum transactions. ~67 GB for 30 days.

| Column                        | Type   | Description                                              |
| ----------------------------- | ------ | -------------------------------------------------------- |
| `transaction_hash`            | string | Transaction hash                                         |
| `block_number`                | int    | Block height                                             |
| `block_timestamp`             | string | Block timestamp (ISO 8601)                               |
| `transaction_index`           | int    | Position of tx within the block                          |
| `from_address`                | string | Sender address                                           |
| `to_address`                  | string | Recipient address (null for contract creation)           |
| `value`                       | int    | Native ETH transferred (wei; 1 ETH = 1e18)               |
| `gas`                         | int    | Gas limit set by sender                                  |
| `gas_price`                   | int    | Gas price (wei/gas)                                      |
| `nonce`                       | int    | Sender nonce                                             |
| `transaction_type`            | int    | 0=legacy, 2=EIP-1559, 126=L1-to-L2 deposit               |
| `max_fee_per_gas`             | int    | EIP-1559 max total fee (wei/gas)                         |
| `max_priority_fee_per_gas`    | int    | EIP-1559 tip (wei/gas)                                   |
| `receipt_status`              | int    | 1=success, 0=reverted                                    |
| `receipt_gas_used`            | int    | Actual gas consumed                                      |
| `receipt_effective_gas_price` | int    | Price actually paid (wei/gas)                            |
| `receipt_contract_address`    | string | Deployed contract address; null if not a creation        |
| `l1_fee`                      | int    | L1 data fee paid to Ethereum (wei); OP-Stack only        |
| `l1_gas_price`                | int    | L1 gas price at time of inclusion (wei/gas)              |
| `l1_fee_scalar`               | string | Scalar used to compute L1 fee; OP-Stack only             |
| `l2_fee`                      | int    | L2 execution fee (wei); OP-Stack only                    |
| `mint`                        | int    | ETH minted by L1-to-L2 deposit; null for regular txns    |
| `source_hash`                 | string | Deposit source hash; non-null for L1-to-L2 deposits only |
| `partition_date`              | string | Partition key (YYYY-MM-DD, from S3 path)                 |

```sql
-- Top gas spenders on Base by total L1 + L2 fees paid
SELECT from_address,
       COUNT(*) AS tx_count,
       SUM(l2_fee + COALESCE(l1_fee, 0)) / 1e18 AS total_eth_fees
FROM   transactions
WHERE  partition_date >= '2026-04-06'
GROUP BY from_address
ORDER BY total_eth_fees DESC
LIMIT 10
```

______________________________________________________________________

## Bitcoin

**Database:** `btc`  |  **Tables:** `blocks`, `outputs`  |  **Source:** AWS
Public Blockchain

### Table: `blocks`

One row per block. Full history 2009 – present, ~6.6 GB scanned.

| Column              | Type   | Description                                  |
| ------------------- | ------ | -------------------------------------------- |
| `number`            | int    | Block height                                 |
| `timestamp`         | string | Block timestamp (ISO 8601); natively indexed |
| `mediantime`        | string | Median time of last 11 blocks (ISO 8601)     |
| `hash`              | string | Block hash                                   |
| `transaction_count` | int    | Number of transactions in the block          |
| `difficulty`        | float  | Mining difficulty at this block              |
| `size`              | int    | Block size in bytes                          |
| `weight`            | int    | Block weight (SegWit units)                  |
| `miner`             | string | Coinbase nonce (miner tag)                   |
| `previousblockhash` | string | Parent block hash                            |

```sql
-- Bitcoin difficulty trend over the last 90 days
SELECT partition_date,
       AVG(difficulty) AS avg_difficulty,
       SUM(transaction_count) AS daily_txns
FROM   blocks
WHERE  timestamp >= `2026-01-13T00:00:00Z`
GROUP BY partition_date ORDER BY partition_date
```

### Table: `outputs`

One row per transaction output. 730 daily partitions (2024 – present), ~115 GB
scanned. Partition key: `block_timestamp`.

> **Note:** `"value"` must be quoted in SQL — it is a reserved word in Sneller.
> This table contains outputs only; UTXO spent/unspent state is not available.

| Column            | Type   | Description                                             |
| ----------------- | ------ | ------------------------------------------------------- |
| `txid`            | string | Transaction ID                                          |
| `block_number`    | int    | Block height containing this transaction                |
| `block_timestamp` | string | Block timestamp (ISO 8601); natively indexed            |
| `output_index`    | int    | Output index within the transaction (vout)              |
| `address`         | string | Recipient address                                       |
| `"value"`         | int    | Output value in satoshis (1 BTC = 100,000,000 satoshis) |
| `type`            | string | Script type: P2PKH, P2SH, P2WPKH, P2WSH, P2TR, etc.     |
| `is_coinbase`     | bool   | True if this output is a miner block reward             |

```sql
-- Total BTC received by an address over the last 2 years
SELECT SUM("value") / 1e8  AS total_btc,
       COUNT(*)             AS output_count
FROM   outputs
WHERE  address = 'bc1qxy2kgdygjrsqtzq2n0yrf2493p83kkfjhx0wlh'
```

______________________________________________________________________

## PubMed — Biomedical Literature

**Database:** `pubmed`  |  **Table:** `baseline`  |  **Rows:** ~36,000,000

Full PubMed baseline: titles, abstracts, authors, journals, MeSH terms, and
publication metadata for biomedical literature. Data is the raw NLM XML
converted to JSON; nested fields follow the original XML element names.

| Column                                                 | Type   | Description                                   |
| ------------------------------------------------------ | ------ | --------------------------------------------- |
| `MedlineCitation.PMID`                                 | string | PubMed identifier                             |
| `MedlineCitation.Article.ArticleTitle`                 | string | Article title                                 |
| `MedlineCitation.Article.Abstract.AbstractText`        | string | Abstract (may be structured)                  |
| `MedlineCitation.Article.Journal.Title`                | string | Journal name                                  |
| `MedlineCitation.Article.Journal.JournalIssue.PubDate` | object | Publication year/month                        |
| `MedlineCitation.Article.AuthorList.Author`            | array  | Author list (LastName, ForeName, Affiliation) |
| `MedlineCitation.MeshHeadingList.MeshHeading`          | array  | MeSH controlled vocabulary terms              |
| `MedlineCitation.DateCompleted.Year`                   | string | Year indexing was completed                   |
| `MedlineCitation.ChemicalList.Chemical`                | array  | Chemical substances mentioned                 |
| `PubmedData.ArticleIdList.ArticleId`                   | array  | DOI, PMC ID, and other identifiers            |

```sql
-- Recent articles on a topic (title search)
SELECT MedlineCitation.PMID,
       MedlineCitation.Article.ArticleTitle AS title,
       MedlineCitation.Article.Journal.Title AS journal,
       MedlineCitation.DateCompleted.Year AS year
FROM   baseline
WHERE  pub_year = 2023
  AND  MedlineCitation.Article.ArticleTitle ~ '(?i)CRISPR'
LIMIT 20

-- Articles with abstracts in a specific journal
SELECT MedlineCitation.PMID,
       MedlineCitation.Article.ArticleTitle AS title
FROM   baseline
WHERE  pub_year = 2023
  AND  MedlineCitation.Article.Journal.Title = 'Nature'
  AND  MedlineCitation.Article.Abstract IS NOT MISSING
LIMIT 10
```

______________________________________________________________________

## ClinicalTrials.gov — Clinical Studies

**Database:** `clinicaltrials`  |  **Table:** `studies`  |  **Rows:** ~580,000
|  **Size:** ~663 MB scanned

All registered clinical studies from ClinicalTrials.gov, sourced via the v2 API.
One row per study. Pairs with PubMed via `nct_id`.

| Column                       | Type   | Description                                            |
| ---------------------------- | ------ | ------------------------------------------------------ |
| `nct_id`                     | string | ClinicalTrials.gov identifier (e.g. NCT00123456)       |
| `brief_title`                | string | Short study title                                      |
| `official_title`             | string | Full protocol title                                    |
| `overall_status`             | string | RECRUITING, COMPLETED, ACTIVE_NOT_RECRUITING, etc.     |
| `start_date`                 | string | Study start date (YYYY-MM-DD)                          |
| `completion_date`            | string | Primary completion date (YYYY-MM-DD)                   |
| `study_first_submitted_date` | string | Date first submitted to registry                       |
| `study_type`                 | string | INTERVENTIONAL or OBSERVATIONAL                        |
| `phases`                     | array  | Trial phase(s): PHASE1, PHASE2, PHASE3, PHASE4, NA     |
| `enrollment`                 | int    | Target or actual enrollment count                      |
| `lead_sponsor`               | string | Name of the lead sponsor                               |
| `sponsor_class`              | string | INDUSTRY, NIH, OTHER_GOV, OTHER                        |
| `conditions`                 | array  | Disease/condition names                                |
| `keywords`                   | array  | Free-text keywords                                     |
| `interventions`              | array  | Objects with `type` (DRUG, DEVICE, ...) and `name`     |
| `brief_summary`              | string | Plain-language study summary (truncated at 2000 chars) |
| `sex`                        | string | Eligibility: ALL, FEMALE, MALE                         |
| `minimum_age`                | string | Minimum participant age (e.g. "18 Years")              |
| `maximum_age`                | string | Maximum participant age                                |
| `has_results`                | bool   | True if results have been posted                       |

```sql
-- Trials mentioning GLP-1 (regex search on title)
SELECT nct_id, brief_title, overall_status
FROM   studies
WHERE  brief_title ~ '(?i)GLP-?1'
LIMIT 20
```

______________________________________________________________________

## FDA FAERS — Adverse Event Reports

**Database:** `fda`  |  **Table:** `faers`  |  **Rows:** ~63,000,000  |
**Coverage:** 2012Q4 – 2025Q4 (53 quarters)  |  **Size:** ~6.5 GB scanned

FDA Adverse Event Reporting System (FAERS). **One row per (case, drug)** —
reactions and outcomes are stored as arrays. Drug names are verbatim reporter
text; use regex to match across brand names, generics, and dosage variants.
Partition key: `year` (integer).

> **Note:** Pre-2012 LAERS-era data is not included. Reports are deduplicated to
> the highest `caseversion` per case. Voluntary reporting introduces bias —
> counts reflect reporting volume, not true incidence rates.

| Column         | Type   | Description                                                      |
| -------------- | ------ | ---------------------------------------------------------------- |
| `primaryid`    | int    | Unique case-version identifier                                   |
| `caseid`       | int    | Case identifier (stable across versions)                         |
| `event_dt`     | string | Date of adverse event (YYYY-MM-DD; may be partial)               |
| `year`         | int    | Year of adverse event; partition key                             |
| `age_years`    | float  | Patient age in years (normalised)                                |
| `sex`          | string | M / F / UNK                                                      |
| `occr_country` | string | Country where event occurred (ISO 2-letter)                      |
| `drug_seq`     | int    | Drug sequence number within the case                             |
| `drugname`     | string | Verbatim drug name as reported                                   |
| `drug_role`    | string | PS=primary suspect, SS=secondary, C=concomitant, I=interacting   |
| `route`        | string | Route of administration (Oral, Intravenous, etc.)                |
| `reactions`    | array  | MedDRA preferred terms for all reactions in this case            |
| `outcomes`     | array  | DE=death, HO=hospitalisation, LT=life-threatening, DS=disability |
| `indications`  | array  | MedDRA preferred terms for the drug's indication(s)              |

```sql
-- Most common reactions for a drug (regex matches brand + generic names)
SELECT reaction, COUNT(*) AS n
FROM   faers, UNNEST(reactions) AS reaction
WHERE  drugname ~ '(?i)ozempic|semaglutide'
  AND  drug_role = 'PS'
GROUP BY reaction
ORDER BY n DESC
LIMIT 20

-- Fatal cases involving a drug class
SELECT drugname,
       COUNT(*) AS fatal_reports
FROM   faers
WHERE  drugname ~ '(?i)fentanyl|oxycodone|hydrocodone'
  AND  ARRAY_CONTAINS(outcomes, 'DE')
GROUP BY drugname
ORDER BY fatal_reports DESC
LIMIT 15
```

______________________________________________________________________

## FEC Campaign Finance

**Database:** `fec`  |  **Table:** `contributions`  |  **Rows:** 279,415,061  |
**Partitions:** 24 cycle partitions (1980–2026)  |  **Size:** ~37 GB scanned

Individual campaign contributions reported to the FEC, covering every US federal
election cycle from 1980 to 2026. One row per contribution, enriched with
committee name, type, party affiliation, and candidate name. Filter by `cycle`
(4-digit even year) to scan only relevant cycles.

| Column                        | Type   | Description                                               |
| ----------------------------- | ------ | --------------------------------------------------------- |
| `cycle`                       | int    | Election cycle year (1980, ..., 2026) — partition key     |
| `sub_id`                      | string | Unique FEC record identifier                              |
| `cmte_id`                     | string | FEC committee ID receiving the contribution               |
| `cmte_name`                   | string | Committee name (enriched from committee master)           |
| `cmte_type`                   | string | H=House, S=Senate, P=Presidential, Q/N/W=PAC, ...         |
| `cmte_party`                  | string | Party affiliation (DEM, REP, ...)                         |
| `cand_id`                     | string | FEC candidate ID (when targeting a specific candidate)    |
| `cand_name`                   | string | Candidate name (enriched from candidate master)           |
| `entity_tp`                   | string | Contributor entity type (IND=individual, PAC, ORG, ...)   |
| `name`                        | string | Contributor name                                          |
| `city` / `state` / `zip_code` | string | Contributor location                                      |
| `employer`                    | string | Contributor employer                                      |
| `occupation`                  | string | Contributor occupation                                    |
| `transaction_dt`              | string | Contribution date (YYYY-MM-DD)                            |
| `transaction_amt`             | int    | Amount in dollars                                         |
| `transaction_tp`              | string | Transaction type code                                     |
| `other_id`                    | string | Other committee ID (set for transfers between committees) |
| `tran_id`                     | string | Transaction identifier within the filing                  |
| `memo_text`                   | string | Optional memo / description                               |

```sql
-- Top employers of donors to a committee (2024 cycle)
SELECT   employer, COUNT(*) AS n, SUM(transaction_amt) AS total_usd
FROM     contributions
WHERE    cycle = 2024
  AND    cmte_name ~ '(?i)actblue|winred'
  AND    employer != ''
GROUP BY employer
ORDER BY total_usd DESC
LIMIT    20

-- Political exposure check: donations by employees of a company (2024 cycle)
SELECT   name, cycle, cmte_name, cmte_party, transaction_amt, transaction_dt
FROM     contributions
WHERE    employer ~ '(?i)goldman sachs'
  AND    cycle = 2024
  AND    transaction_amt >= 2500
ORDER BY transaction_amt DESC
LIMIT    25
```

______________________________________________________________________

## DeFi — Protocol Data

**Database:** `defi`  |  **Tables:** `protocols`, `tvl`  |  **Source:**
DeFiLlama API

DeFi protocol data sourced from the DeFiLlama API. The `protocols` table is a
current snapshot of all tracked protocols; `tvl` contains daily
total-value-locked history for protocols with TVL >= $10M at fetch time.

> **Note:** The `date` partition field in `tvl` is stored as a datetime
> internally. Use backtick ISO 8601 literals for date range filters:
> `` `2026-04-15T00:00:00Z` ``

### Table: `protocols`

Snapshot of all DeFiLlama protocols. ~7,300 rows, ~2 MB scanned.

| Column        | Type   | Description                                        |
| ------------- | ------ | -------------------------------------------------- |
| `slug`        | string | DeFiLlama protocol slug (unique identifier)        |
| `name`        | string | Protocol display name                              |
| `category`    | string | Category (e.g. `Lending`, `DEX`, `Liquid Staking`) |
| `chain`       | string | Primary chain                                      |
| `chains`      | array  | All chains where the protocol is deployed          |
| `tvl`         | float  | Total value locked (USD)                           |
| `change_1h`   | float  | TVL change over last 1 hour (%)                    |
| `change_1d`   | float  | TVL change over last 24 hours (%)                  |
| `change_7d`   | float  | TVL change over last 7 days (%)                    |
| `mcap`        | float  | Market capitalisation (USD; optional)              |
| `fdv`         | float  | Fully diluted valuation (USD; optional)            |
| `description` | string | Short protocol description                         |
| `url`         | string | Protocol website URL                               |
| `twitter`     | string | Twitter handle (optional)                          |
| `gecko_id`    | string | CoinGecko identifier (optional)                    |

```sql
-- Top 20 protocols by TVL
SELECT name, category, chain,
       TRUNC(tvl/1e9*1000)/1000  AS tvl_billions
FROM protocols
ORDER BY tvl DESC
LIMIT 20

-- TVL by category
SELECT category,
       COUNT(*)                        AS protocols,
       TRUNC(SUM(tvl)/1e9*100)/100     AS total_tvl_billions
FROM protocols
WHERE tvl > 0
GROUP BY category
ORDER BY total_tvl_billions DESC
LIMIT 20
```

### Table: `tvl`

Daily TVL history per protocol. 365 daily partitions, ~0.4 GB scanned.

| Column     | Type   | Description                                               |
| ---------- | ------ | --------------------------------------------------------- |
| `date`     | date   | Partition key (stored as datetime; use backtick literals) |
| `protocol` | string | Protocol display name                                     |
| `slug`     | string | DeFiLlama protocol slug (joins to `protocols`)            |
| `category` | string | Protocol category                                         |
| `chain`    | string | Chain for this TVL record                                 |
| `chains`   | array  | All chains where the protocol is deployed                 |
| `tvl`      | float  | Total value locked on this date (USD)                     |

```sql
-- Total DeFi TVL trend — last 30 days
SELECT date,
       TRUNC(SUM(tvl)/1e9*100)/100  AS total_tvl_billions,
       COUNT(*)                      AS protocol_count
FROM tvl
WHERE date >= `2026-03-16T00:00:00Z`
GROUP BY date
ORDER BY date
LIMIT 30

-- Aave V3 TVL history (last 90 days; each version has its own slug)
SELECT date,
       TRUNC(SUM(tvl)/1e9*1000)/1000  AS tvl_billions
FROM tvl
WHERE slug = 'aave-v3'
  AND date >= `2026-01-15T00:00:00Z`
GROUP BY date
ORDER BY date
LIMIT 90
```

______________________________________________________________________

## FDA Orange Book — Approved Drug Products

**Database:** `fda` | **Table:** `orangebook` | **Rows:** ~51,000 | **Size:** ~5
MB scanned

FDA-approved drug products from the Orange Book via the openFDA bulk export. One
row per product strength/form. Includes therapeutic equivalence (TE) codes that
determine generic substitutability. Pairs with FDA FAERS for pharmacovigilance
and approval-status lookups.

| Column               | Type   | Description                                                    |
| -------------------- | ------ | -------------------------------------------------------------- |
| `application_number` | string | NDA or ANDA number (e.g. `NDA009700`)                          |
| `appl_type`          | string | `N` = NDA (brand), `A` = ANDA (generic)                        |
| `sponsor_name`       | string | Applicant / sponsor company name                               |
| `product_number`     | string | Product sequence within the application                        |
| `brand_name`         | string | Trade / brand name                                             |
| `active_ingredients` | array  | `[{name, strength}]` — active ingredient names and strengths   |
| `dosage_form`        | string | e.g. `TABLET`, `CAPSULE`                                       |
| `route`              | string | e.g. `ORAL`, `INTRAVENOUS`                                     |
| `marketing_status`   | string | e.g. `Prescription`, `OTC`                                     |
| `te_code`            | string | Therapeutic equivalence code (AB = substitutable; may be null) |

```sql
-- All approved generics for a drug (AB-rated = substitutable)
SELECT application_number, sponsor_name, brand_name, te_code
FROM   orangebook
WHERE  ARRAY_CONTAINS(active_ingredients[0].name, 'METFORMIN')
  AND  te_code = 'AB'
ORDER BY sponsor_name
LIMIT 20

-- All products by a given sponsor
SELECT application_number, brand_name, dosage_form, route, te_code
FROM   orangebook
WHERE  sponsor_name ~ '(?i)pfizer'
  AND  appl_type = 'N'
ORDER BY brand_name
LIMIT 25
```

______________________________________________________________________

## FRED — Federal Reserve Economic Data

**Database:** `fred` | **Table:** `series` | **Rows:** ~357,000 | **Series:**
~115 curated | **Size:** ~30 MB scanned

Curated macroeconomic time series from the St. Louis Fed FRED database: GDP,
inflation, employment, interest rates, money supply, housing, fiscal, markets,
and trade. One row per (series, date) observation. Series with redistribution
restrictions are excluded.

> This product uses the FRED® API but is not endorsed or certified by the
> Federal Reserve Bank of St. Louis.

| Column                | Type   | Description                                                            |
| --------------------- | ------ | ---------------------------------------------------------------------- |
| `series_id`           | string | FRED series identifier (e.g. `UNRATE`, `GDP`)                          |
| `category`            | string | Thematic group: gdp, inflation, employment, rates, money, housing, ... |
| `title`               | string | Full series name (e.g. `Unemployment Rate`)                            |
| `units`               | string | Unit of measure (e.g. `%`, `Bil. of $`)                                |
| `frequency`           | string | D=daily, M=monthly, Q=quarterly, A=annual                              |
| `seasonal_adjustment` | string | SA = seasonally adjusted; NSA = not                                    |
| `date`                | string | Observation date (YYYY-MM-DD)                                          |
| `value`               | float  | Observation value in the series units                                  |

```sql
-- US unemployment rate since 2020
SELECT date, value
FROM   series
WHERE  series_id = 'UNRATE'
  AND  date >= '2020-01-01'
ORDER BY date
LIMIT 100

-- Compare CPI and core PCE year-over-year
SELECT series_id, title, date, value
FROM   series
WHERE  series_id IN ('CPIAUCSL', 'PCEPILFE')
  AND  date >= '2021-01-01'
ORDER BY date, series_id
LIMIT 200

-- All available series IDs with titles
SELECT DISTINCT series_id, category, title, units, frequency
FROM   series
ORDER BY category, series_id
LIMIT 200
```

______________________________________________________________________

## End of Life — Runtime & Framework Lifecycle Dates

**Database:** `eol` | **Table:** `cycles` | **Rows:** ~7,900 | **Products:**
~451 | **Size:** \<1 MB scanned

End-of-life and support status for 450+ software products — languages, runtimes,
frameworks, databases, operating systems, and cloud services. One row per
release cycle. Source: [endoflife.date](https://endoflife.date). Ideal for
agents checking dependency support status or auditing infrastructure.

| Column         | Type   | Description                                                            |
| -------------- | ------ | ---------------------------------------------------------------------- |
| `product`      | string | Product slug (e.g. `python`, `nodejs`, `ubuntu`)                       |
| `cycle`        | string | Release cycle label (e.g. `3.11`, `20.04`)                             |
| `release_date` | string | General availability date (YYYY-MM-DD)                                 |
| `eol`          | string | EOL date, or `false` (still supported), or `true` (EOL, no exact date) |
| `latest`       | string | Latest patch release in this cycle                                     |
| `latest_date`  | string | Release date of the latest version                                     |
| `lts`          | string | LTS status: `true`, `false`, or LTS end date                           |
| `support`      | string | Active support end date (before EOL), or `true`/`false`                |
| `link`         | string | URL for the release notes / changelog                                  |

```sql
-- Is Python 3.8 still supported?
SELECT product, cycle, eol, lts, latest
FROM   cycles
WHERE  product = 'python'
ORDER BY cycle DESC
LIMIT 10

-- All products reaching EOL in 2025
SELECT product, cycle, eol, latest
FROM   cycles
WHERE  eol >= '2025-01-01'
  AND  eol < '2026-01-01'
ORDER BY eol
LIMIT 50
```

______________________________________________________________________

## arXiv — Preprint Metadata

**Database:** `arxiv` | **Table:** `papers` | **Rows:** ~2,400,000 |
**Coverage:** 1991 – present | **Size:** ~4 GB scanned

Metadata for all arXiv preprints — titles, abstracts, authors, categories, and
submission dates. Covers CS, math, physics, quantitative finance, statistics,
biology, and more. Papers appear on arXiv months before journal publication,
making this the primary source for the latest research. Partitioned by
submission month.

| Column        | Type   | Description                                          |
| ------------- | ------ | ---------------------------------------------------- |
| `id`          | string | arXiv ID (e.g. `2301.00001`)                         |
| `title`       | string | Paper title (whitespace normalised)                  |
| `abstract`    | string | Full abstract text                                   |
| `categories`  | array  | arXiv category codes (e.g. `["cs.LG", "stat.ML"]`)   |
| `primary_cat` | string | Primary category (first in list)                     |
| `authors`     | array  | `[{name, affiliation}]`                              |
| `submitted`   | string | Original submission date (YYYY-MM-DD); partition key |
| `updated`     | string | Last-updated date                                    |
| `doi`         | string | DOI if published; null otherwise                     |
| `journal_ref` | string | Journal reference if published; null otherwise       |
| `comments`    | string | Author comments (page count, code links, etc.)       |
| `license`     | string | License URL (e.g. CC BY 4.0)                         |

```sql
-- Recent ML papers mentioning transformers
SELECT id, submitted, title, primary_cat
FROM   papers
WHERE  primary_cat = 'cs.LG'
  AND  submitted >= '2024-01-01'
  AND  title ~ '(?i)transformer'
ORDER BY submitted DESC
LIMIT 25

-- Papers by an author across all categories
SELECT id, submitted, title, categories
FROM   papers, UNNEST(authors) AS a
WHERE  a.name ~ '(?i)lecun'
ORDER BY submitted DESC
LIMIT 20
```

______________________________________________________________________

## ClinPGx — Pharmacogenomics Knowledge Base

**Database:** `clinpgx` | **Source:** ClinPGx / PharmGKB | **License:** CC BY-SA
4.0

Curated pharmacogenomics data covering gene–drug interactions, clinical
annotations, dosing guidelines (CPIC, DPWG), and FDA/EMA drug label PGx
requirements. Eight tables from the ClinPGx download bundle, refreshed from the
source as updates are released.

### Table: `clinical_variants` (~5,200 rows)

Curated clinical annotations linking genomic variants (or star alleles) to drugs
and phenotypes, with evidence level.

| Column              | Type   | Description                                   |
| ------------------- | ------ | --------------------------------------------- |
| `variant`           | string | Variant name or star allele (e.g. `CYP2C9*3`) |
| `gene`              | string | Gene symbol (e.g. `CYP2C9`)                   |
| `type`              | string | Annotation type (`Metabolism/PK`, `Toxicity`) |
| `level_of_evidence` | string | Evidence level (`1A`, `1B`, `2A`, `2B`, `3`)  |
| `chemicals`         | array  | Associated drug names                         |
| `phenotypes`        | array  | Associated phenotype names                    |

```sql
-- Level 1A variants (highest evidence)
SELECT variant, gene, chemicals, phenotypes
FROM   clinical_variants
WHERE  level_of_evidence = '1A'
ORDER BY gene
LIMIT 20
```

### Table: `guidelines` (~217 rows)

Dosing guideline annotations from CPIC, DPWG, and other bodies. One row per
gene–drug guideline.

| Column                     | Type   | Description                               |
| -------------------------- | ------ | ----------------------------------------- |
| `id`                       | string | PharmGKB accession ID                     |
| `name`                     | string | Guideline annotation name                 |
| `source`                   | string | Issuing body (`CPIC`, `DPWG`, `FDA`, ...) |
| `recommendation`           | bool   | Includes a dosing recommendation          |
| `dosing_information`       | bool   | Includes dosing information               |
| `has_testing_info`         | bool   | Includes genetic testing information      |
| `alternate_drug_available` | bool   | Alternative drug available                |
| `chemicals`                | array  | Involved drug names                       |
| `genes`                    | array  | Involved gene symbols                     |
| `alleles`                  | array  | Specific alleles referenced               |
| `pmids`                    | array  | Supporting PubMed IDs                     |

```sql
-- All CPIC guidelines with dosing recommendations
SELECT name, chemicals, genes, alleles
FROM   guidelines
WHERE  source = 'CPIC'
  AND  recommendation = true
ORDER BY name
```

### Table: `drug_labels` (~1,400 rows)

FDA, EMA, and Health Canada drug label PGx annotations — which labels require or
recommend genetic testing and what action is specified.

| Column                 | Type   | Description                                             |
| ---------------------- | ------ | ------------------------------------------------------- |
| `id`                   | string | PharmGKB ID                                             |
| `name`                 | string | Annotation name                                         |
| `source`               | string | Regulatory body (`FDA`, `EMA`, `HCSC`)                  |
| `testing_level`        | string | `Testing Required`, `Actionable PGx`, `Informative PGx` |
| `has_prescribing_info` | bool   | Label includes prescribing guidance                     |
| `has_dosing_info`      | bool   | Label includes dosing guidance                          |
| `has_alternate_drug`   | bool   | Alternate drug recommended                              |
| `chemicals`            | array  | Drug names                                              |
| `genes`                | array  | Gene symbols                                            |
| `variants`             | array  | Relevant variants or haplotypes                         |
| `latest_history_date`  | string | Date of most recent annotation update (`YYYY-MM-DD`)    |

```sql
-- FDA labels requiring genetic testing
SELECT name, chemicals, genes, testing_level
FROM   drug_labels
WHERE  source = 'FDA'
  AND  testing_level = 'Testing Required'
ORDER BY latest_history_date DESC
LIMIT 20
```

### Table: `relationships` (~127,700 rows)

Gene/chemical/phenotype association network — every curated relationship between
pharmacogenomics entities.

| Column         | Type   | Description                                  |
| -------------- | ------ | -------------------------------------------- |
| `entity1_id`   | string | PharmGKB ID of first entity                  |
| `entity1_name` | string | Name of first entity                         |
| `entity1_type` | string | Type (`Gene`, `Chemical`, `Disease`, ...)    |
| `entity2_id`   | string | PharmGKB ID of second entity                 |
| `entity2_name` | string | Name of second entity                        |
| `entity2_type` | string | Type of second entity                        |
| `evidence`     | array  | Evidence sources (`ClinicalAnnotation`, ...) |
| `association`  | string | `associated`, `not associated`, `ambiguous`  |
| `pk`           | bool   | Pharmacokinetic relationship                 |
| `pd`           | bool   | Pharmacodynamic relationship                 |
| `pmids`        | array  | Supporting PubMed IDs                        |

```sql
-- All confirmed gene–drug associations for CYP2D6
SELECT entity2_name AS drug, association, evidence, pmids
FROM   relationships
WHERE  entity1_name = 'CYP2D6'
  AND  entity1_type = 'Gene'
  AND  entity2_type = 'Chemical'
  AND  association = 'associated'
ORDER BY entity2_name
```

### Tables: `genes`, `drugs`, `variants`, `phenotypes`

Reference entity tables.

**`genes`** (~25,000 rows) — Pharmacogene reference: NCBI Gene ID, Ensembl ID,
chromosomal coordinates (GRCh37 and GRCh38), VIP status, CPIC guideline flag.

**`drugs`** (~3,700 rows) — Drug/chemical reference: SMILES, InChI, RxNorm and
ATC identifiers, dosing guideline sources, annotation counts.

**`variants`** (~7,600 rows) — Genomic variant reference: chromosomal location,
synonyms, annotation counts per type.

**`phenotypes`** (~1,600 rows) — Phenotype/disease reference: alternate names,
MeSH/OMIM/UMLS cross-references.

```sql
-- VIP genes with CPIC dosing guidelines
SELECT id, symbol, name, chromosome, start_grch38, stop_grch38
FROM   genes
WHERE  is_vip = true
  AND  has_cpic_guideline = true
ORDER BY symbol

-- Drugs with the most clinical annotations
SELECT name, type, clinical_annotation_count, dosing_guideline_sources
FROM   drugs
WHERE  clinical_annotation_count > 0
ORDER BY clinical_annotation_count DESC
LIMIT 20
```

______________________________________________________________________

## Illumina — SNP Array Manifests

**Database:** `illumina` | **Source:** Illumina product files | **License:**
Illumina product file licence

Probe-level manifests for two Illumina SNP genotyping arrays, both mapped to
GRCh38. One row per assay locus — variant coordinates, probe sequence, alleles,
strand orientation, and source annotation. Useful for pharmacogenomics variant
lookup, array comparison, and probe-sequence retrieval.

| Table     | Array       |      Loci | Decompressed |
| --------- | ----------- | --------: | -----------: |
| `gda_pgx` | GDA+ePGx v1 | 1,933,117 |      800 MiB |
| `gsa_pgx` | GSA-PGx v4  |   683,054 |      274 MiB |

Both tables share the same schema.

| Column            | Type   | Description                                        |
| ----------------- | ------ | -------------------------------------------------- |
| `ilmn_id`         | string | Illumina internal probe ID                         |
| `name`            | string | Variant name (position-based or rsID)              |
| `ilmn_strand`     | string | Probe strand relative to TOP/BOT designation       |
| `alleles`         | array  | Two alleles, e.g. `["A","C"]`                      |
| `address_a`       | string | Bead address for allele A                          |
| `probe_seq_a`     | string | 50-mer probe sequence for allele A                 |
| `address_b`       | string | Bead address for allele B (two-colour probes only) |
| `probe_seq_b`     | string | 50-mer probe sequence for allele B (optional)      |
| `chr`             | string | Chromosome                                         |
| `position`        | int    | GRCh38 genomic position (1-based)                  |
| `source`          | string | Variant source (`PAGE`, `1000genomes`, `CNV`, ...) |
| `source_strand`   | string | Source strand (`TOP` / `BOT`)                      |
| `source_seq`      | string | ~120 bp flanking sequence with allele in brackets  |
| `top_genomic_seq` | string | TOP-strand genomic context with allele in brackets |
| `ref_strand`      | string | Strand relative to reference (`+` / `-`)           |
| `bead_set_id`     | int    | Bead set identifier                                |
| `exp_clusters`    | int    | Expected cluster count (2 = mono, 3 = biallelic)   |
| `intensity_only`  | bool   | Intensity-only probe (no genotype call)            |

```sql
-- All probes on chromosome 22 in gda_pgx
SELECT name, position, alleles, source, ref_strand
FROM   gda_pgx
WHERE  chr = '22'
ORDER BY position
LIMIT 20

-- Probes shared between both arrays (by name)
SELECT g.name, g.position, g.alleles
FROM   gda_pgx AS g, gsa_pgx AS s
WHERE  g.name = s.name
  AND  g.chr  = '7'
ORDER BY g.position
LIMIT 20

-- CNV probes in gsa_pgx
SELECT name, chr, position, probe_seq_a
FROM   gsa_pgx
WHERE  source = 'CNV'
ORDER BY chr, position
LIMIT 20
```

______________________________________________________________________

## GWAS Catalog — Genome-Wide Association Studies

**Database:** `gwas` | **Table:** `associations` | **Rows:** ~1,095,000 |
**Partitions:** 19 years (2008–2026) | **Size:** ~0.3 GB scanned

NHGRI-EBI GWAS Catalog — curated genome-wide association study results. One row
per variant–trait association. Joins ClinVar on `rsid` / `dbsnp_id` and PubMed
on `pubmed_id`. Partition key: `date_added`.

| Column             | Type   | Description                                                            |
| ------------------ | ------ | ---------------------------------------------------------------------- |
| `rsid`             | string | dbSNP rsID (e.g. `rs1801131`)                                          |
| `snp_risk_allele`  | string | Risk allele in `rsID-allele` format (e.g. `rs1801131-C`)               |
| `risk_allele_freq` | float  | Risk allele frequency in discovery sample                              |
| `p_value`          | float  | Association p-value                                                    |
| `pvalue_mlog`      | number | −log₁₀(p-value)                                                        |
| `or_beta`          | float  | Odds ratio (binary traits) or beta coefficient (quantitative traits)   |
| `ci_95`            | string | 95% confidence interval                                                |
| `disease_trait`    | string | Disease or trait name as reported by study                             |
| `mapped_trait`     | string | EFO-mapped trait label                                                 |
| `mapped_gene`      | string | Nearest gene(s) mapped by ENSEMBL                                      |
| `reported_genes`   | array  | Genes reported by the study authors                                    |
| `chromosome`       | string | Chromosome                                                             |
| `chr_pos`          | int    | Chromosomal position (GRCh38)                                          |
| `region`           | string | Cytogenetic region                                                     |
| `context`          | string | Variant functional context (e.g. `intron_variant`, `missense_variant`) |
| `pubmed_id`        | int    | PubMed ID of the source study                                          |
| `pub_date`         | string | Publication date                                                       |
| `first_author`     | string | First author surname                                                   |
| `journal`          | string | Journal name                                                           |
| `study_accession`  | string | GWAS Catalog study accession (e.g. `GCST000001`)                       |
| `date_added`       | string | Date added to the GWAS Catalog (partition key)                         |
| `initial_sample`   | string | Discovery sample description (ancestry, size)                          |

```sql
-- Top associations for Type 2 Diabetes
SELECT rsid, snp_risk_allele, mapped_gene, p_value, or_beta, risk_allele_freq
FROM   associations
WHERE  disease_trait ~ '(?i)type 2 diabetes'
ORDER BY pvalue_mlog DESC
LIMIT 25

-- Join with ClinVar to find GWAS hits that are also ClinVar pathogenic
SELECT g.rsid, g.disease_trait, g.mapped_gene, g.p_value,
       c.significance, c.gene_symbol
FROM   gwas.associations AS g
JOIN   clinvar.variants   AS c
  ON   g.rsid = c.dbsnp_id AND c.assembly = 'GRCh38' AND c.sig_simple = 1
WHERE  g.pvalue_mlog > 50
ORDER BY g.pvalue_mlog DESC
LIMIT 20

-- Most-studied traits by association count
SELECT disease_trait, COUNT(*) AS n_associations
FROM   associations
GROUP BY disease_trait
ORDER BY n_associations DESC
LIMIT 20
```

______________________________________________________________________

## ClinVar — Genetic Variant Classifications

**Database:** `clinvar` | **Table:** `variants` | **Rows:** ~8,919,000 |
**Assemblies:** GRCh37 + GRCh38 | **Size:** ~2.1 GB scanned

NCBI ClinVar variant classifications — pathogenicity, associated phenotypes,
chromosomal coordinates, and review status. One row per variant × assembly
(GRCh37 and GRCh38 each have their own row). Source: NCBI FTP, updated weekly.

| Column           | Type   | Description                                                              |
| ---------------- | ------ | ------------------------------------------------------------------------ |
| `variation_id`   | int    | ClinVar VariationID                                                      |
| `allele_id`      | int    | Stable ClinVar AlleleID                                                  |
| `type`           | string | Variant type (SNV, Indel, Deletion, Duplication, …)                      |
| `name`           | string | HGVS-like variant name                                                   |
| `gene_symbol`    | string | Gene symbol (e.g. `BRCA1`)                                               |
| `gene_id`        | int    | NCBI GeneID; null if intergenic                                          |
| `significance`   | string | Clinical significance (Pathogenic, Likely pathogenic, Benign, …)         |
| `sig_simple`     | int    | −1 conflicting, 0 uncertain/other, 1 pathogenic, 2 benign, 3 risk-factor |
| `last_evaluated` | string | Date last evaluated by submitter                                         |
| `dbsnp_id`       | string | dbSNP rsID (e.g. `rs80357713`); null if none                             |
| `phenotypes`     | array  | Associated disease/phenotype names                                       |
| `phenotype_ids`  | array  | MedGen/OMIM/Orphanet IDs for phenotypes                                  |
| `rcv_accessions` | array  | RCV accession numbers                                                    |
| `origin`         | string | Allele origin (germline, somatic, …)                                     |
| `assembly`       | string | Reference assembly (`GRCh38`, `GRCh37`, `NCBI36`)                        |
| `chromosome`     | string | Chromosome                                                               |
| `start`          | int    | Start position (1-based)                                                 |
| `stop`           | int    | Stop position (1-based)                                                  |
| `ref_allele`     | string | Reference allele                                                         |
| `alt_allele`     | string | Alternate allele                                                         |
| `review_status`  | string | Review status (e.g. "criteria provided, multiple submitters")            |
| `n_submitters`   | int    | Number of submitters                                                     |

```sql
-- Pathogenic variants in BRCA1 (GRCh38)
SELECT allele_id, name, significance, chromosome, start, dbsnp_id
FROM   variants
WHERE  gene_symbol = 'BRCA1'
  AND  assembly   = 'GRCh38'
  AND  sig_simple = 1
ORDER BY start
LIMIT 50

-- Variants associated with a phenotype
SELECT gene_symbol, significance, name, review_status, n_submitters
FROM   variants, UNNEST(phenotypes) AS ph
WHERE  assembly   = 'GRCh38'
  AND  ph ~ '(?i)breast cancer'
  AND  sig_simple = 1
ORDER BY n_submitters DESC
LIMIT 25

-- Pathogenic variant count per gene (well-reviewed only)
SELECT gene_symbol, COUNT(*) AS pathogenic_variants
FROM   variants
WHERE  assembly     = 'GRCh38'
  AND  sig_simple   = 1
  AND  n_submitters >= 2
GROUP BY gene_symbol
ORDER BY pathogenic_variants DESC
LIMIT 20
```

______________________________________________________________________

## Sanctions — U.S. Consolidated Screening List

**Database:** `sanctions` | **Table:** `entities` | **Rows:** ~25,400 |
**Lists:** OFAC SDN + BIS + State Dept | **Size:** ~8 MB scanned

The U.S. government Consolidated Screening List (CSL) — OFAC SDN (Treasury), BIS
Entity List (Commerce), State Dept Nonproliferation, ITAR Debarred, and several
other sanctions and export-control lists. One row per sanctioned individual,
entity, vessel, or aircraft. Source: trade.gov, updated daily.

| Column                | Type   | Description                                                            |
| --------------------- | ------ | ---------------------------------------------------------------------- |
| `id`                  | string | Stable hash ID from trade.gov                                          |
| `source`              | string | Source list (e.g. "Specially Designated Nationals (SDN) - Treasury")   |
| `name`                | string | Primary name                                                           |
| `type`                | string | Individual, Entity, Vessel, or Aircraft                                |
| `programs`            | array  | Sanctions program codes (e.g. `["UKRAINE-EO13685", "RUSSIA-EO14024"]`) |
| `alt_names`           | array  | Aliases and alternate name spellings                                   |
| `addresses`           | array  | Known addresses                                                        |
| `ids`                 | array  | Identity documents: passport, tax ID, registration                     |
| `nationalities`       | array  | Nationality countries                                                  |
| `dobs`                | array  | Dates of birth (YYYY or YYYY-MM-DD)                                    |
| `pobs`                | array  | Places of birth                                                        |
| `start_date`          | string | Listing date (YYYY-MM-DD)                                              |
| `license_requirement` | string | BIS license requirement (BIS entries only)                             |
| `remarks`             | string | Additional remarks                                                     |
| `source_list_url`     | string | URL to the source list page                                            |

```sql
-- Look up an entity by name (partial match)
SELECT name, type, source, programs, addresses
FROM   entities
WHERE  name ~ '(?i)vtb bank'
LIMIT 10

-- All individuals sanctioned under Russia programs
SELECT name, type, start_date, alt_names
FROM   entities, UNNEST(programs) AS prog
WHERE  prog ~ '(?i)russia'
  AND  type = 'Individual'
ORDER BY start_date DESC
LIMIT 25

-- Count entities per source list
SELECT source, COUNT(*) AS total
FROM   entities
GROUP BY source
ORDER BY total DESC
```

______________________________________________________________________

## MalwareBazaar — Malware Sample Repository

**Database:** `malwarebazaar` | **Table:** `samples` | **Rows:** ~985,000 |
**Partitions:** 68 months (2020-02–2025-09) | **Size:** ~0.4 GB scanned

abuse.ch MalwareBazaar — crowdsourced malware sample metadata. One row per
submitted sample. Includes file hashes, family signatures, VirusTotal detection
rates, and fuzzy hashes for similarity matching. License: CC0. Partition key:
`first_seen_utc`.

| Column            | Type      | Description                                                 |
| ----------------- | --------- | ----------------------------------------------------------- |
| `sha256_hash`     | string    | SHA-256 hash (primary identifier)                           |
| `md5_hash`        | string    | MD5 hash                                                    |
| `sha1_hash`       | string    | SHA-1 hash                                                  |
| `file_name`       | string    | Original file name                                          |
| `file_type_guess` | string    | Guessed file type (e.g. `Win32 EXE`, `PDF Document`, `ZIP`) |
| `mime_type`       | string    | MIME type                                                   |
| `first_seen_utc`  | timestamp | Date first submitted to MalwareBazaar (partition key)       |
| `reporter`        | string    | Reporting researcher/feed                                   |
| `signature`       | string    | Malware family name / AV detection name                     |
| `vtpercent`       | number    | VirusTotal detection rate 0–100 (null if not scanned)       |
| `clamav`          | string    | ClamAV signature name                                       |
| `imphash`         | string    | PE import hash (Windows executables)                        |
| `ssdeep`          | string    | ssdeep fuzzy hash for similarity clustering                 |
| `tlsh`            | string    | TLSH fuzzy hash                                             |

```sql
-- All samples for a malware family
SELECT sha256_hash, file_name, file_type_guess, first_seen_utc, vtpercent
FROM   samples
WHERE  signature ~ '(?i)emotet'
ORDER BY first_seen_utc DESC
LIMIT 25

-- Most prevalent malware families (last 6 months)
SELECT signature, COUNT(*) AS samples, AVG(vtpercent) AS avg_vt
FROM   samples
WHERE  first_seen_utc >= '2025-03-01'
  AND  signature != ''
GROUP BY signature
ORDER BY samples DESC
LIMIT 20

-- Lookup a hash across all three hash types
SELECT sha256_hash, file_name, signature, first_seen_utc, vtpercent
FROM   samples
WHERE  md5_hash = 'd41d8cd98f00b204e9800998ecf8427e'
   OR  sha1_hash = 'da39a3ee5e6b4b0d3255bfef95601890afd80709'
LIMIT 5
```

______________________________________________________________________

## Open Food Facts — Global Food Product Database

**Database:** `openfoodfacts` | **Table:** `products` | **Rows:** ~556,000 |
**Partitions:** 7 years (2012–2017) | **Size:** ~0.4 GB scanned

Open Food Facts 2019 Kaggle snapshot (DbCL 1.0) — crowdsourced nutritional data
for food products sold globally. One row per product. Includes ingredients,
allergens, Nutri-Score grades, and 12 key nutrients per 100g. Partition key:
`created`.

| Column               | Type      | Description                                |
| -------------------- | --------- | ------------------------------------------ |
| `code`               | string    | Barcode (EAN/UPC)                          |
| `product_name`       | string    | Product name                               |
| `generic_name`       | string    | Generic description                        |
| `brands`             | string    | Brand names (comma-separated)              |
| `categories`         | string    | Product categories (comma-separated)       |
| `main_category`      | string    | Primary category                           |
| `countries`          | string    | Countries where sold (comma-separated)     |
| `quantity`           | string    | Net quantity / weight as labelled          |
| `ingredients`        | string    | Ingredients text                           |
| `allergens`          | string    | Declared allergens                         |
| `traces`             | string    | May-contain-traces declarations            |
| `additives`          | string    | E-number food additives                    |
| `additives_n`        | int       | Count of additives                         |
| `nutrition_grade`    | string    | Nutri-Score grade (a–e)                    |
| `nutrition_score_fr` | int       | French nutrition score (lower = healthier) |
| `energy_100g`        | number    | Energy per 100g (kcal)                     |
| `fat_100g`           | number    | Total fat per 100g (g)                     |
| `sat_fat_100g`       | number    | Saturated fat per 100g (g)                 |
| `carbs_100g`         | number    | Carbohydrates per 100g (g)                 |
| `sugars_100g`        | number    | Sugars per 100g (g)                        |
| `fiber_100g`         | number    | Dietary fiber per 100g (g)                 |
| `proteins_100g`      | number    | Protein per 100g (g)                       |
| `salt_100g`          | number    | Salt per 100g (g)                          |
| `created`            | timestamp | Date product was added (partition key)     |

```sql
-- Lowest-sugar breakfast cereals with Nutri-Score A or B
SELECT product_name, brands, sugars_100g, nutrition_grade, countries
FROM   products
WHERE  categories ~ '(?i)cereal'
  AND  nutrition_grade IN ('a', 'b')
  AND  sugars_100g IS NOT MISSING
ORDER BY sugars_100g
LIMIT 20

-- Products containing peanuts with high protein
SELECT product_name, brands, proteins_100g, allergens, countries
FROM   products
WHERE  allergens ~ '(?i)peanut'
  AND  proteins_100g > 20
ORDER BY proteins_100g DESC
LIMIT 20

-- Average Nutri-Score and macros by main category
SELECT main_category,
       COUNT(*) AS products,
       AVG(energy_100g) AS avg_kcal,
       AVG(sugars_100g) AS avg_sugars,
       AVG(proteins_100g) AS avg_protein
FROM   products
WHERE  main_category != ''
GROUP BY main_category
ORDER BY products DESC
LIMIT 20
```

______________________________________________________________________

## NADAC — National Average Drug Acquisition Cost

**Database:** `nadac` | **Table:** `prices` | **Rows:** ~18,173,000 |
**Partitions:** by `effective_date` | **Size:** ~0.8 GB scanned

CMS weekly drug acquisition cost survey — the price pharmacies actually pay for
drugs. One row per NDC code per weekly report. Covers generic and brand-name
drugs, both retail and non-retail pharmacies. Pairs with FDA FAERS and Orange
Book. Source: data.medicaid.gov, public domain. Partition key: `effective_date`.

| Column                        | Type   | Description                                            |
| ----------------------------- | ------ | ------------------------------------------------------ |
| `ndc`                         | string | National Drug Code (11-digit, format `NNNNNNNNNNN`)    |
| `ndc_description`             | string | Drug name, strength, and dosage form                   |
| `nadac_per_unit`              | string | Acquisition cost per unit (tablet, mL, gram, etc.)     |
| `pricing_unit`                | string | Unit type: `TAB`, `ML`, `GM`, `EA`                     |
| `classification`              | string | `Generic` or `Brand Name`                              |
| `otc`                         | bool   | Over-the-counter (`true`) or prescription (`false`)    |
| `pharmacy_type`               | string | `Retail` or `Non-Retail`                               |
| `as_of_date`                  | string | Date of the weekly survey report                       |
| `effective_date`              | string | Price effective date (partition key)                   |
| `explanation_code`            | string | Code indicating reason for price change or data source |
| `corresponding_generic_nadac` | string | For brand drugs: comparable generic NADAC price        |
| `corresponding_generic_date`  | string | Date of the corresponding generic price                |

```sql
-- Current acquisition cost for metformin generics
SELECT ndc_description, nadac_per_unit, pricing_unit, pharmacy_type, as_of_date
FROM   prices
WHERE  ndc_description ~ '(?i)metformin'
  AND  classification = 'Generic'
  AND  as_of_date >= '2025-01-01'
ORDER BY as_of_date DESC, ndc_description
LIMIT 20

-- Brand vs generic price gap for a drug class
SELECT classification,
       AVG(CAST(nadac_per_unit AS float)) AS avg_cost,
       COUNT(DISTINCT ndc) AS ndcs
FROM   prices
WHERE  ndc_description ~ '(?i)atorvastatin'
  AND  as_of_date >= '2025-01-01'
GROUP BY classification

-- Most expensive drugs by unit cost (retail, recent)
SELECT ndc_description, nadac_per_unit, pricing_unit, classification
FROM   prices
WHERE  pharmacy_type = 'Retail'
  AND  as_of_date >= '2025-06-01'
ORDER BY CAST(nadac_per_unit AS float) DESC
LIMIT 20
```

______________________________________________________________________

## World Bank — Global Commodity Prices

**Database:** `worldbank` | **Table:** `commodity_prices` | **Rows:** ~795 |
**Coverage:** 1960–2026 | **Size:** ~5 MB scanned

World Bank Pink Sheet — monthly prices for ~70 global commodities: energy,
metals, agriculture, and fertilizers. One row per month, one column per
commodity. Prices are nominal USD. Source: World Bank, CC BY 4.0. Partition key:
`year`.

| Column               | Type   | Description                                        |
| -------------------- | ------ | -------------------------------------------------- |
| `period`             | string | Month label in `YYYY-Mmm` format (e.g. `2024-Jan`) |
| `year`               | int    | Year (partition key)                               |
| `month`              | int    | Month number (1–12)                                |
| `crude_oil_brent`    | number | Brent crude oil ($/bbl)                            |
| `crude_oil_wti`      | number | WTI crude oil ($/bbl)                              |
| `crude_oil_dubai`    | number | Dubai crude oil ($/bbl)                            |
| `natural_gas_us`     | number | US natural gas ($/mmbtu)                           |
| `natural_gas_europe` | number | European natural gas ($/mmbtu)                     |
| `coal_australian`    | number | Australian thermal coal ($/mt)                     |
| `gold`               | number | Gold ($/troy oz)                                   |
| `silver`             | number | Silver (¢/troy oz)                                 |
| `platinum`           | number | Platinum ($/troy oz)                               |
| `copper`             | number | Copper ($/mt)                                      |
| `aluminum`           | number | Aluminum ($/mt)                                    |
| `nickel`             | number | Nickel ($/mt)                                      |
| `maize`              | number | Maize / corn ($/mt)                                |
| `wheat_us_hrw`       | number | US hard red winter wheat ($/mt)                    |
| `soybeans`           | number | Soybeans ($/mt)                                    |
| `soybean_oil`        | number | Soybean oil ($/mt)                                 |
| `sugar_world`        | number | World sugar (¢/kg)                                 |
| `coffee_arabica`     | number | Arabica coffee (¢/kg)                              |
| `cocoa`              | number | Cocoa ($/mt)                                       |
| `palm_oil`           | number | Palm oil ($/mt)                                    |

_Plus ~50 additional commodity columns (fertilizers, other metals, foods,
timber, rubber). Use `GET /v1/databases` for the full column list._

```sql
-- Brent crude oil price history since 2020
SELECT period, crude_oil_brent, crude_oil_wti, natural_gas_us
FROM   commodity_prices
WHERE  year >= 2020
ORDER BY year, month
LIMIT 100

-- Gold vs copper ratio over time (risk-off indicator)
SELECT period, gold, copper, gold / NULLIF(copper, 0) AS gold_copper_ratio
FROM   commodity_prices
WHERE  year >= 2010
ORDER BY year, month
LIMIT 200

-- Agricultural commodity prices for a given year
SELECT period, maize, wheat_us_hrw, soybeans, soybean_oil, sugar_world
FROM   commodity_prices
WHERE  year = 2022
ORDER BY month
```

______________________________________________________________________

## FHFA — House Price Index

**Database:** `fhfa` | **Table:** `hpi` | **Rows:** ~133,000 | **Coverage:**
1975–2026 | **Size:** ~5 MB scanned

Federal Housing Finance Agency (FHFA) House Price Index — quarterly and monthly
home price indices for the U.S., all 50 states, and ~400 metropolitan
statistical areas (MSAs). Covers purchase-only and all-transactions variants,
with seasonally-adjusted and not-seasonally-adjusted series. Source: FHFA,
public domain. Partition key: `yr`.

| Column       | Type   | Description                                                       |
| ------------ | ------ | ----------------------------------------------------------------- |
| `yr`         | int    | Year (partition key)                                              |
| `period`     | int    | Quarter (1–4) or month (1–12) depending on `frequency`            |
| `frequency`  | string | `quarterly` or `monthly`                                          |
| `hpi_type`   | string | `purchase-only` or `all-transactions`                             |
| `hpi_flavor` | string | `traditional` or `expanded-data`                                  |
| `level`      | string | Geographic level: `USA`, `state`, `MSA`                           |
| `place_id`   | string | FIPS code or state abbreviation                                   |
| `place_name` | string | State name or MSA name (e.g. `San Francisco-Oakland-Fremont, CA`) |
| `index_nsa`  | number | Not-seasonally-adjusted HPI (base = 100 at 1991 Q1)               |
| `index_sa`   | number | Seasonally-adjusted HPI (null for MSA-level series)               |

```sql
-- National quarterly HPI since 2000 (purchase-only, seasonally adjusted)
SELECT yr, period, index_sa, index_nsa
FROM   hpi
WHERE  level    = 'USA'
  AND  hpi_type = 'purchase-only'
  AND  frequency = 'quarterly'
  AND  yr >= 2000
ORDER BY yr, period

-- Top 10 metros by HPI growth since 2010
SELECT place_name,
       MAX(index_nsa) - MIN(index_nsa) AS index_gain,
       MAX(index_nsa) / NULLIF(MIN(index_nsa), 0) AS growth_ratio
FROM   hpi
WHERE  level     = 'MSA'
  AND  hpi_type  = 'purchase-only'
  AND  frequency = 'quarterly'
  AND  yr BETWEEN 2010 AND 2024
GROUP BY place_name
ORDER BY growth_ratio DESC
LIMIT 10

-- State-level HPI comparison (most recent quarter)
SELECT place_name, index_nsa, index_sa
FROM   hpi
WHERE  level     = 'state'
  AND  hpi_type  = 'purchase-only'
  AND  frequency = 'quarterly'
  AND  yr = 2025 AND period = 4
ORDER BY index_nsa DESC
```

______________________________________________________________________

## See Also

- [API Reference](docs.md) — authentication, querying, pricing, and account
  management
- [Runnable cross-dataset examples](examples.md) — company diligence, drug
  safety, security audit, and research pipeline scripts
- `GET /v1/databases` — machine-readable list of available databases and table
  names (JSON)

______________________________________________________________________

# Microquery Examples

Runnable Python examples that combine multiple datasets in a single script.
Each example uses the shared `client.py` helper and can be run with a single
command after setting `MICROQUERY_TOKEN`.

Interactive version with syntax highlighting and sample output:
[microquery.dev/examples](https://microquery.dev/examples)

______________________________________________________________________

## Setup — client.py

All examples import a minimal HTTP client. Download it once and place it in
the same directory as the example script.

```
curl -O https://microquery.dev/examples/client.py
export MICROQUERY_TOKEN=your_token
```

Get a token: `POST /v1/register` — see [docs.md](docs.md) for the full
registration flow. New accounts receive 100,000 micro-USDC ($0.10) trial
credit — enough to run all example scripts.

______________________________________________________________________

## 1. Company Due Diligence

**Datasets:** `sec.edgar` · `fec.contributions` · `sanctions.entities` ·
`fred.series`

Cross-references four independent datasets to answer: *"Is this company
financially healthy, politically exposed, compliance-clean, and operating in
a favorable macro environment?"*

- **SEC EDGAR** — audited financials: revenue trend, net income, debt, cash
- **FEC campaign finance** — political donation patterns from employees and
  PACs, by election cycle and committee type
- **Sanctions screening** — U.S. Consolidated Screening List for counterparty
  risk (primary name + alternate aliases)
- **FRED macro data** — Fed Funds Rate, CPI, GDP, unemployment for the filing
  period context

```
curl -O https://microquery.dev/examples/company_diligence.py
python3 company_diligence.py --company "Apple" --ticker AAPL
python3 company_diligence.py --company "Lockheed Martin" --ticker LMT
```

> All four dataset sections are independent and fire in parallel via
> `concurrent.futures.ThreadPoolExecutor`. Typical wall time: 5–12 s.

______________________________________________________________________

## 2. U.S. Macro Economy Dashboard

**Datasets:** `fred.series` · `fred.catalog`

Pulls ~20 FRED series in parallel to produce a five-section snapshot of
the U.S. economy, with year-partition pruning limiting each scan to the
requested window.

- **Monetary Policy** — Fed Funds Rate, IORB, SOFR, Fed balance sheet
- **Yield Curve** — 2Y/5Y/10Y/30Y Treasuries; flags inversion on the
  10Y-2Y and 10Y-3M spreads
- **Inflation** — CPI, Core CPI, PCE, Core PCE, PPI, 10Y breakeven
- **Labour Market** — Unemployment rate, nonfarm payrolls, initial
  jobless claims
- **Housing & Growth** — Housing starts, building permits, real GDP
- **Recession Signals** — yield-curve inversion check, Sahm-Rule proxy

Use `--search` to query the 539,000-series FRED catalog by keyword —
useful for discovering any of the ~250 hand-curated series or searching
the full universe.

```
curl -O https://microquery.dev/examples/macro_economy.py
python3 macro_economy.py
python3 macro_economy.py --since 2020
python3 macro_economy.py --search "housing starts"
python3 macro_economy.py --search "consumer price"
```

> ~20 series fire in parallel via `ThreadPoolExecutor(max_workers=16)`.
> Year-partition pruning (`obs_year >= since`) limits each scan to the
> relevant partitions. Typical cost: ~$0.001; covered by trial credit.

______________________________________________________________________

## 3. Drug Safety Report

**Datasets:** `fda.faers` · `fda.orangebook` · `clinicaltrials.studies` ·
`clinvar.variants` · `pubmed.baseline`

Builds a pharmacovigilance profile to answer: *"Is this drug safe for my
patient population, what genetic risk factors exist, and what is the current
research trajectory?"*

- **FDA FAERS** — real-world adverse event reports post-approval, ranked by
  frequency; outcomes coded as DE (death), HO (hospitalization), LT
  (life-threatening)
- **Orange Book** — FDA approval status and generic (ANDA) availability
- **ClinicalTrials** — actively recruiting or ongoing trials
- **ClinVar** — pathogenic variants in genes linked to the target condition,
  identifying at-risk patient subpopulations
- **PubMed** — annual publication volume trend since 2020

```
curl -O https://microquery.dev/examples/drug_safety_report.py
python3 drug_safety_report.py --drug aspirin --condition "heart disease"
python3 drug_safety_report.py --drug metformin --condition diabetes
python3 drug_safety_report.py --drug warfarin --condition "atrial fibrillation"
```

> All five queries fire in parallel. PubMed (filtered to 2020+, ~60 GB) runs
> in the background while FAERS and Orange Book run in the foreground. Total
> cost ~$0.010; covered by trial credit.

______________________________________________________________________

## 4. Software Security Audit

**Datasets:** `nvd.cve` · `osv.advisories` · `eol.cycles`

Audits a dependency for known vulnerabilities and runtime end-of-life status
to answer: *"What is vulnerable right now, how severe is it, is there a fixed
version, and is the underlying runtime still receiving patches?"*

- **NVD** — official CVSS-scored CVE records: severity, attack vector, scope,
  and description; schema uses nested paths
  (`metrics.cvssMetricV31[0].cvssData.baseScore`)
- **OSV** — ecosystem-aware advisories (PyPI, npm, Go, Maven, crates.io),
  flat schema: one row per affected `package_name` / `ecosystem` pair, with
  `introduced`, `fixed`, and `aliases` fields
- **EOL.date** — runtime lifecycle data; flags versions no longer receiving
  security patches

```
curl -O https://microquery.dev/examples/security_audit.py
python3 security_audit.py --package requests --ecosystem PyPI --version 2.28.0
python3 security_audit.py --package "golang.org/x/net" --ecosystem Go
python3 security_audit.py --package openssl --ecosystem "crates.io"
python3 security_audit.py --runtime python --cycle 3.11
python3 security_audit.py --runtime go --cycle 1.21
```

______________________________________________________________________

## 5. Research-to-Clinic Pipeline

**Datasets:** `arxiv.papers` · `pubmed.baseline` · `clinicaltrials.studies` ·
`clinvar.variants`

Traces a research topic from preprint frontier to clinical application to
answer: *"How mature is the research on topic X, is it moving toward clinical
application, and which genes/variants are central to it?"*

- **arXiv** — preprint activity by year and category; appears months before
  peer review
- **PubMed** — peer-reviewed publication volume trend; arXiv/PubMed ratio
  indicates field velocity
- **ClinicalTrials** — trial count by status and phase; gap between papers and
  trial starts reveals translation lag
- **ClinVar** — pathogenic variants linked to the condition; identifies the
  genetic architecture and key genes

```
curl -O https://microquery.dev/examples/research_pipeline.py
python3 research_pipeline.py --topic "CRISPR"
python3 research_pipeline.py --topic "GLP-1 obesity"
python3 research_pipeline.py --topic "Alzheimer tau"
```

> All four queries fire in parallel (phase 1), then arXiv top-categories and
> ClinicalTrials recent-trials fire as a conditional phase 2. Total cost
> ~$0.014; covered by trial credit.

______________________________________________________________________

## 6. Blockchain Compliance & AML Screening

**Datasets:** `eth.transfers` · `eth.dex_swaps` · `eth.lending` ·
`eth.lp_events` · `eth.mev`

Cross-references five Ethereum datasets to answer: *"Does this wallet show
AML red flags — layering, structuring, wash trading, high-risk
counterparties, or automated behaviour?"*

- **eth.transfers** — counterparty concentration; high unique-sender or
  unique-recipient counts are layering/structuring signals
- **eth.dex_swaps** — cross-protocol DeFi layering; high swaps-per-block
  ratio indicates automated behaviour
- **eth.lending** — leverage and liquidation risk; `liquidation_call` events
  are a financial-stress signal
- **eth.lp_events** — wash-trading indicators; frequent mint/burn cycles in
  the same pool inflate apparent volume
- **eth.mev** — addresses in >50% MEV-extracted blocks are likely MEV bots
  or persistent MEV targets

```
curl -O https://microquery.dev/examples/blockchain_aml.py
python3 blockchain_aml.py --address 0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045
python3 blockchain_aml.py --address 0x... --days 30
```

> Six queries fire in parallel (phase 1); the MEV lookup fires immediately
> after block numbers are known (phase 2). All eth tables use `block_timestamp`
> pruning — a 30-day window prunes transfers ~72%, dex_swaps ~95%. Total cost
> ~$0.014; covered by trial credit.

______________________________________________________________________

## See Also

- [API Reference](docs.md) — authentication, querying, pricing, and account
  management
- [Dataset schemas](datasets.md) — field names, partition keys, and example
  queries for every table
- [FAQ](faq.md) — billing, trust model, and common questions
- `GET /v1/databases` — machine-readable table list with field names (JSON)

______________________________________________________________________

# Microquery FAQ

## Why Microquery?

**Q:** Why pay when the data is publicly available?

**A:** The raw data is free; the work is not. Microquery handles ingestion,
normalisation, schema design, indexing, and keeps everything current. You get
SQL over a clean, consistent schema in a single API call — no pipeline to
build, no rate limits to fight, no pagination to handle. The public APIs for
PubMed, SEC EDGAR, or NVD were built for human browsing, not for agents running
hundreds of queries per loop.

**Q:** Why not just call the source API directly?

**A:** Source APIs have hard rate limits (NVD: 5 req/s, PubMed: 10 req/s),
pagination that adds round-trips, inconsistent schemas across years, and no
aggregation. You cannot run `GROUP BY` or `REGEXP_MATCH` against the NVD REST
API — you download everything and post-process it yourself. Microquery runs the
aggregation server-side on columnar storage, so a query that would take minutes
of API calls and local compute returns in under a second.

**Q:** What if I only need one query?

**A:** That is exactly the point. Register in 30 seconds, get $0.10 free
credit, run your query. A single CVE lookup costs $0.0002. No subscription, no
contract, no minimum spend. If one query is all you need, you pay for one query.

## Billing & Funds

**Q:** How does billing work?

**A:** Microquery charges based on the amount of data scanned to answer your
query, not the number of rows returned. The rate is 150 micro-USDC per GiB
scanned. A typical query costs around 3 micro-USDC. The exact charge for each
query is shown in the response headers.

**Q:** How do I know I was charged correctly?

**A:** Every query response includes headers showing the bytes scanned and the
cost deducted. You can also retrieve a full transaction history via the account
endpoint. If an amount looks wrong, the headers and history together give you a
complete audit trail.

**Q:** Can I check what a query will cost before running it?

**A:** Yes. `GET /v1/estimate?database=...&query=...` (or POST with a JSON body)
returns `estimated_bytes_scanned` and `estimated_cost_micro_usdc` without
executing the query or deducting any charge. The estimate is a conservative
upper bound from the Sneller query planner — actual cost at execution time will
be equal or lower. This is useful for comparing query variants or verifying that
your balance is sufficient before committing.

**Q:** What happens if I run out of balance mid-query?

**A:** If your balance is insufficient when a query is submitted, the API
returns HTTP 402 (Payment Required) and the query is aborted. No partial charge
is applied. Top up your balance by depositing additional USDC into the escrow
contract on Base.

**Q:** Can I get my unspent deposited funds back?

**A:** Yes. Call `withdraw(amount)` directly on the escrow contract using your
own wallet key — no API call is required. The off-chain ledger syncs within
approximately 15 seconds via an event watcher. Settlement runs in hourly
batches; withdrawals requested before a batch clears are handled gracefully, and
any small shortfall is absorbed by the operator by design.

**Q:** What is trial credit and can I withdraw it?

**A:** When you register via `POST /v1/register`, your account receives 5,000
micro-USDC in free trial credit. This credit exists only on the off-chain
ledger; it cannot be withdrawn. Only USDC that you have deposited on-chain into
the escrow contract is eligible for withdrawal.

**Q:** What is the settlement window and why does it matter?

**A:** Query charges accumulate off-chain and are settled to the escrow contract
in hourly batches. During the window between a query and settlement, your
on-chain balance has not yet been reduced. If you withdraw before the batch
clears, the operator absorbs any resulting shortfall — this is intentional and
the exposure is minimal at current per-query pricing.

**Q:** What is the minimum deposit?

**A:** The minimum deposit is 250,000 micro-USDC (0.25 USDC). Deposits are made
in USDC on Base, Ethereum's L2, by sending funds directly to the escrow
contract.

## Authentication

**Q:** Do I need an Ethereum wallet to use Microquery?

**A:** No. You can authenticate with a Bearer token (API key) issued at
registration and never interact with a wallet at all. A wallet becomes useful if
you want EIP-712 per-query spending caps, or if you need to recover access to
your account using `GET /v1/wallets/{addr}`.

**Q:** What if I lose my API key?

**A:** If you registered with an Ethereum wallet, you can recover your account
by calling `GET /v1/wallets/{addr}` with your wallet address. The endpoint
returns the account associated with that address, allowing you to obtain a new
token. Without a linked wallet there is no recovery path, so agents are
encouraged to link a wallet at registration.

**Q:** How does EIP-712 auth work and why should agents use it?

**A:** Instead of a Bearer token, you can sign each request with an EIP-712
structured-data signature that encodes a spending cap for that query. The server
verifies the signature on-chain-compatible logic and rejects any charge above
the cap you signed. This gives agents trustless, per-query budget control
without relying on the operator to honour a spending limit — the constraint is
cryptographically enforced.

## Data & Queries

**Q:** What SQL dialect is supported?

**A:** Microquery uses SnellerDB, which supports a PartiQL-compatible subset of
SQL. Standard `SELECT`, `WHERE`, `GROUP BY`, `ORDER BY`, and aggregate functions
work as expected. Features that require full ANSI SQL or procedural extensions
are not available.

**Q:** What data is available?

**A:** The following datasets are currently available:

| Dataset                  | Approximate size |
| ------------------------ | ---------------- |
| NVD/CVE vulnerabilities  | ~5 GB            |
| OSV (open-source vulns)  | ~400 MB          |
| SEC EDGAR financials     | ~8 GB            |
| PubMed abstracts         | ~350 GB          |
| Ethereum token transfers | ~1 TB            |

**Q:** Is there a query timeout or row limit?

**A:** If a query does not include a `LIMIT` clause, Microquery automatically
applies a limit of 1,000 rows and sets the response header
`X-Microquery-Auto-Limit: 1000` to indicate this. There is no hard wall-clock
timeout, but charges accumulate with bytes scanned, so unbounded full-table
scans will consume balance proportionally.

**Q:** What format are query results returned in?

**A:** Results are returned as Newline-Delimited JSON (NDJSON), with one JSON
object per line. Cost and scan metadata are provided in the response headers
rather than the body, so agents can parse results with a simple line-by-line
reader.

## Performance & Cost Optimisation

**Q:** I need both inbound and outbound rows for the same address. Do I need two
queries?

**A:** No — and you should avoid it. Two queries with opposite `WHERE`
predicates on the same table (e.g. `WHERE to_address = X` and
`WHERE from_address = X`) each trigger a full partition scan, doubling the bytes
scanned and doubling your cost. Combine them into one query with an `OR` filter
and use a `CASE` expression inside `COUNT(DISTINCT ...)` to split the
aggregation by direction:

```sql
SELECT CASE WHEN to_address = '0xABC' THEN 'in' ELSE 'out' END AS dir,
       token_address,
       COUNT(*)                                                   AS tx_count,
       COUNT(DISTINCT CASE WHEN to_address = '0xABC'
                           THEN from_address
                           ELSE to_address END)                   AS counterparties
FROM   transfers
WHERE  (from_address = '0xABC' OR to_address = '0xABC')
  AND  block_timestamp >= `2026-03-01T00:00:00Z`
  AND  block_timestamp <  `2026-04-01T00:00:00Z`
GROUP BY dir, token_address
ORDER BY dir, tx_count DESC
LIMIT 16
```

One scan, one charge. In Python, split on the `dir` field after the query
returns. This pattern reduces data scanned and cost in proportion to the number
of queries merged.

**Q:** Are there other common patterns that cause unnecessary double-scans?

**A:** Yes. Any time your code fires two futures against the same table with
complementary filters (`sender = X` / `recipient = X`, `event_type = 'deposit'`
/ `event_type = 'withdrawal'`, etc.) you are scanning the same data twice. The
fix is always the same: merge the predicates with `OR` and use `CASE` or
`GROUP BY` on the distinguishing field to separate the results server-side.

## Trust & Safety

**Q:** Who controls the deposited funds?

**A:** Funds sit in an escrow smart contract on Base. The agent's wallet is the
sole authorised withdrawer — the operator cannot move your deposited USDC
without your signature. The operator deducts query charges off-chain against
your balance, with on-chain settlement happening hourly.

**Q:** What stops the operator from over-charging?

**A:** Every query response includes the exact bytes scanned and the charge
applied in its headers. Agents using EIP-712 auth embed a per-query spending cap
in their signed payload, so the server cannot charge more than the agent
explicitly authorised for that request. Bearer-token users can audit charges via
the transaction history endpoint and correlate them against response headers.