> **Can't find what you're looking for?** Use `search_docs` on the docs MCP server at `https://docs.ampdb.io/api/mcp` to find what you need.

# Substrate Functions

Status: Experimental.

Substrate chains, like Bittensor, encode their events, calls, and storage in SCALE, a compact binary format, and show
accounts as SS58 addresses, a text form of their 32-byte account ids. `scale_decode` turns SCALE bytes into columns,
from a layout you describe, and `ss58_encode` and `ss58_decode` convert account ids to addresses and back.

## Prerequisites

* A [connection](/querying/connecting) to a query server. The examples use `ampsql`.
* Nothing else for most examples: they decode values copied from Bittensor's mainnet. The ones that decode chain data
  query the Bittensor dataset that [Bittensor](/providers/bittensor#create-a-dataset) creates,
  `acme/bittensor_finney`, with blocks 7,000,200 to 7,000,209.

## What to decode

In a [Bittensor dataset](/datasets/tables/bittensor), these columns hold SCALE bytes and account ids:

| Column                          | Type                  | Holds                                        | Decode it with |
| ------------------------------- | --------------------- | -------------------------------------------- | -------------- |
| `events.event_payload`          | `Binary`              | An event's fields                            | `scale_decode` |
| `extrinsics.args_payload`       | `Binary`              | The arguments of the call an extrinsic makes | `scale_decode` |
| `storage_entries.storage_value` | `Binary`              | A value from the chain's storage             | `scale_decode` |
| `extrinsics.signer_account`     | `FixedSizeBinary(32)` | The account that signed the extrinsic        | `ss58_encode`  |

SCALE bytes don't say what they hold, so you give their layout, in a [descriptor](/querying/functions/substrate#scale-descriptors). It's in the
chain's runtime metadata, which explorers and wallets like polkadot.js show. A runtime upgrade can change it: use the
`pallet_name`, `event_name` or `call_name`, and `spec_version` columns to select the rows a layout applies to.

The descriptor is a string literal, so Amp knows the result's type before it reads any rows: every row decodes to that
type, and a row whose bytes don't fit the layout returns `NULL`.

## Functions

### `scale_decode`

Decodes SCALE bytes with the layout a [descriptor](/querying/functions/substrate#scale-descriptors) gives.

```sql
scale_decode(data, descriptor)
```

| Argument     | Type           | Description                                                         |
| ------------ | -------------- | ------------------------------------------------------------------- |
| `data`       | `Binary`       | The bytes, like an event's `event_payload`.                         |
| `descriptor` | string literal | Their layout, like `'{ hotkey: [u8; 32], amount: Compact<u128> }'`. |

It returns the [type the descriptor maps to](/querying/functions/substrate#scale-descriptors), like a struct for `{ … }`, or `NULL` when the bytes
don't match.

Here, the fields of a `StakeAdded` event, in which a coldkey staked 63,475,000 rao (0.063475 TAO) to a hotkey on subnet
80, and got the subnet's alpha token in return:

```sql
SELECT stake.tao AS tao, stake.alpha AS alpha, stake.netuid AS netuid, stake.fee AS fee
FROM (
  SELECT scale_decode(
    X'f22f6428d969378f70af08256b40a6225435771c3d332f4133380940686c8b5620b0f8ac1d5416d32f5a552f98b570f06e8392ccb803029e04f63fbe0553c954388dc80300000000b68a47ee000000005000da7c000000000000',
    '{ coldkey: [u8; 32], hotkey: [u8; 32], tao: u64, alpha: u64, netuid: u16, fee: u64 }'
  ) AS stake
)
```

```text [Output]
+----------+------------+--------+-------+
| tao      | alpha      | netuid | fee   |
+----------+------------+--------+-------+
| 63475000 | 3997665974 | 80     | 31962 |
+----------+------------+--------+-------+
```

Read a field with a dot, like `stake.tao`, or brackets, like `stake['tao']`. Structs can nest. This storage value, from
`System.Account`, is an account's nonce and balances:

```sql
SELECT account.nonce AS nonce, account.data.free AS free, account.data.reserved AS reserved
FROM (
  SELECT scale_decode(
    X'bd070000000000000100000000000000c2487904000000000000000000000000000000000000000000000000000000000000000000000080',
    '{
      nonce: u32, consumers: u32, providers: u32, sufficients: u32,
      data: { free: u64, reserved: u64, frozen: u64, flags: u128 }
    }'
  ) AS account
)
```

```text [Output]
+-------+----------+----------+
| nonce | free     | reserved |
+-------+----------+----------+
| 1981  | 75057346 | 0        |
+-------+----------+----------+
```

**Bad input.** A row returns `NULL` when its data is `NULL`, or doesn't match the descriptor exactly: too few bytes,
bytes left over, a `bool` or an `Option` tag other than 0 or 1, or a `Compact` value too large for its type. The last
column here has a byte left over:

```sql
SELECT
  scale_decode(X'a10f', 'Compact<u32>') AS compact,
  scale_decode(X'012a', 'Option<u8>') AS some,
  scale_decode(X'00', 'Option<u8>') AS none,
  scale_decode(X'0c010002000300', 'Vec<u16>') AS list,
  scale_decode(X'2a00', 'u8') AS too_long
```

```text [Output]
+---------+------+------+-----------+----------+
| compact | some | none | list      | too_long |
+---------+------+------+-----------+----------+
| 1000    | 42   |      | [1, 2, 3] |          |
+---------+------+------+-----------+----------+
```

An `Option` that's `None` is `NULL` too, so for a descriptor that's an `Option` itself, `NULL` doesn't tell you which.

The query fails before it runs when:

| Cause                                        | Message                                                                                                                                                         |
| -------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| The descriptor isn't a string literal        | `scale_decode: expected a string literal for the SCALE type descriptor`                                                                                         |
| The descriptor has a type it doesn't support | `scale_decode: unsupported SCALE descriptor 'u256'`                                                                                                             |
| The descriptor's syntax is wrong             | `scale_decode: expected ',' at byte 7`                                                                                                                          |
| A `[u8; N]` has a length of 0                | `scale_decode: fixed byte array length must be between 1 and i32::MAX, got 0`                                                                                   |
| `data` is another type                       | `Failed to coerce arguments to satisfy a call to 'scale_decode' function: coercion from FixedSizeBinary(32), Utf8 to the signature Exact(Binary, Utf8) failed.` |

To decode a `FixedSizeBinary` column, like a hash, cast it first: `scale_decode(arrow_cast(block_hash, 'Binary'), …)`.

On a Bittensor dataset, select the event's rows. When they span a runtime upgrade that changed the event's fields, add a
condition on `spec_version` too. Here, the dataset's first five `StakeAdded` events:

```sql
SELECT block_num, ss58_encode(stake.coldkey, 42) AS coldkey, stake.tao AS tao, stake.netuid AS netuid
FROM (
  SELECT block_num, event_index,
    scale_decode(event_payload, '{ coldkey: [u8; 32], hotkey: [u8; 32], tao: u64, alpha: u64, netuid: u16, fee: u64 }') AS stake
  FROM "acme/bittensor_finney".events
  WHERE pallet_name = 'SubtensorModule' AND event_name = 'StakeAdded'
)
ORDER BY block_num, event_index
LIMIT 5
```

```text [Output]
+-----------+--------------------------------------------------+------------+--------+
| block_num | coldkey                                          | tao        | netuid |
+-----------+--------------------------------------------------+------------+--------+
| 7000200   | 5FKfQ3hnVM1tkUMk4sDQw659yzwsHdqJvT25Mz452qic2x1n | 850289714  | 103    |
| 7000200   | 5DcVtXFVMsvPMiwwG6w3yUZLUGJAdFK1S2X4QPtyLdMJ6tzF | 300000001  | 98     |
| 7000200   | 5CMcngJawjS8iCPrAfFLGdi4ag3fqYeK2qdZRQayp1dhQyz9 | 3317556578 | 98     |
| 7000200   | 5CMcngJawjS8iCPrAfFLGdi4ag3fqYeK2qdZRQayp1dhQyz9 | 697802556  | 91     |
| 7000200   | 5D2msakpthwJogCTEmV7wV2psLk4TcrUJm4joRwdnVLxztmD | 5955012358 | 98     |
+-----------+--------------------------------------------------+------------+--------+
```

### `ss58_encode`

Turns an account id into an SS58 address.

```sql
ss58_encode(account_id, prefix)
```

| Argument     | Type                  | Description                                                                                            |
| ------------ | --------------------- | ------------------------------------------------------------------------------------------------------ |
| `account_id` | `FixedSizeBinary(32)` | The account id, like `signer_account`, or a `[u8; 32]` field `scale_decode` returns.                   |
| `prefix`     | integer, 0 to 16383   | The chain's network prefix (see [below](/querying/functions/substrate#choose-the-prefix)). A constant, not a column. Can't be NULL. |

It returns the address, as `Utf8`, or `NULL` when `account_id` is `NULL`.

Here, the accounts in the `StakeAdded` event above:

```sql
SELECT
  ss58_encode(stake.coldkey, 42) AS coldkey,
  ss58_encode(stake.hotkey, 42) AS hotkey
FROM (
  SELECT scale_decode(
    X'f22f6428d969378f70af08256b40a6225435771c3d332f4133380940686c8b5620b0f8ac1d5416d32f5a552f98b570f06e8392ccb803029e04f63fbe0553c954388dc80300000000b68a47ee000000005000da7c000000000000',
    '{ coldkey: [u8; 32], hotkey: [u8; 32], tao: u64, alpha: u64, netuid: u16, fee: u64 }'
  ) AS stake
)
```

```text [Output]
+--------------------------------------------------+--------------------------------------------------+
| coldkey                                          | hotkey                                           |
+--------------------------------------------------+--------------------------------------------------+
| 5HYFXJyKwBwhQn9E3JSr29HuGvkb2LqQCoukBmgN8oMoXrsJ | 5CoZxgtfhcJKX2HmkwnsN18KbaT9aih9eF3b6qVPTgAUbifj |
+--------------------------------------------------+--------------------------------------------------+
```

The query fails when:

| Cause                                          | Message                                                                                                                                                          |
| ---------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| The prefix is above 16383                      | `ss58_encode: SS58 prefix 16384 is above the maximum of 16383`                                                                                                   |
| The prefix is negative                         | `ss58_encode: SS58 prefix -1 is outside the range 0 to 16383`                                                                                                    |
| The prefix is 46 or 47, which no chain can use | `ss58_encode: SS58 prefix 46 is reserved and cannot be used`                                                                                                     |
| The prefix is `NULL`                           | `ss58_encode: prefix cannot be null`                                                                                                                             |
| The prefix is a column                         | `ss58_encode: prefix must be a constant, not a column`                                                                                                           |
| `account_id` is another type                   | `Failed to coerce arguments to satisfy a call to 'ss58_encode' function: coercion from Binary, Int64 to the signature Exact(FixedSizeBinary(32), Int64) failed.` |

To encode a `Binary` column of 32-byte values, cast it first:
`ss58_encode(arrow_cast(account, 'FixedSizeBinary(32)'), 42)`.

#### Choose the prefix

The prefix picks which chain's address form to write, and the same account id has a different address on each. Use the
prefix the chain declares, its `ss58Format`:

| Chain     | Prefix |
| --------- | ------ |
| Bittensor | `42`   |
| Polkadot  | `0`    |
| Kusama    | `2`    |

The SS58 registry lists `13116` for Bittensor, but Bittensor declares `42`, and its wallets, explorers, and SDKs don't
recognize addresses with `13116`. Here, the coldkey above as Polkadot and Kusama addresses:

```sql
SELECT
  ss58_encode(ss58_decode('5HYFXJyKwBwhQn9E3JSr29HuGvkb2LqQCoukBmgN8oMoXrsJ'), 0) AS polkadot,
  ss58_encode(ss58_decode('5HYFXJyKwBwhQn9E3JSr29HuGvkb2LqQCoukBmgN8oMoXrsJ'), 2) AS kusama
```

```text [Output]
+--------------------------------------------------+-------------------------------------------------+
| polkadot                                         | kusama                                          |
+--------------------------------------------------+-------------------------------------------------+
| 16UYfeEPnyDArK9jzwVrAJ848YkEiePYHJeEM4figtPKiTBD | J3sBdKCZYxdARxfp1Ftv6euRX2pq1eafBkVaRxKcbaJGqLd |
+--------------------------------------------------+-------------------------------------------------+
```

### `ss58_decode`

Turns an SS58 address into its account id.

```sql
ss58_decode(address)
```

| Argument  | Type   | Description                     |
| --------- | ------ | ------------------------------- |
| `address` | `Utf8` | An SS58 address, of any prefix. |

It returns the account id, as `FixedSizeBinary(32)`, or `NULL` when `address` isn't a valid address. The prefix is
checked, then dropped, so an account's addresses on every chain decode to the same id:

```sql
SELECT address, ss58_decode(address) AS account_id
FROM (VALUES
  ('5HYFXJyKwBwhQn9E3JSr29HuGvkb2LqQCoukBmgN8oMoXrsJ'),
  ('16UYfeEPnyDArK9jzwVrAJ848YkEiePYHJeEM4figtPKiTBD'),
  ('5HYFXJyKwBwhQn9E3JSr29HuGvkb2LqQCoukBmgN8oMoXrsK')
) AS addresses(address)
```

```text [Output]
+--------------------------------------------------+------------------------------------------------------------------+
| address                                          | account_id                                                       |
+--------------------------------------------------+------------------------------------------------------------------+
| 5HYFXJyKwBwhQn9E3JSr29HuGvkb2LqQCoukBmgN8oMoXrsJ | f22f6428d969378f70af08256b40a6225435771c3d332f4133380940686c8b56 |
| 16UYfeEPnyDArK9jzwVrAJ848YkEiePYHJeEM4figtPKiTBD | f22f6428d969378f70af08256b40a6225435771c3d332f4133380940686c8b56 |
| 5HYFXJyKwBwhQn9E3JSr29HuGvkb2LqQCoukBmgN8oMoXrsK |                                                                  |
+--------------------------------------------------+------------------------------------------------------------------+
```

The last address has a typo, so its checksum doesn't match. An address returns `NULL` when it:

* Is longer than 64 bytes, or isn't base58, like `0x` hexadecimal.
* Has a malformed prefix, or the reserved `46` or `47`.
* Doesn't hold a 32-byte account id, like the shorter addresses of account indices.
* Has a checksum that doesn't match.

Use it to find an account's rows by its address. Here, the extrinsics of the first staker above:

```sql
SELECT block_num, extrinsic_index, call_name
FROM "acme/bittensor_finney".extrinsics
WHERE signer_account = ss58_decode('5FKfQ3hnVM1tkUMk4sDQw659yzwsHdqJvT25Mz452qic2x1n')
```

```text [Output]
+-----------+-----------------+-----------------+
| block_num | extrinsic_index | call_name       |
+-----------+-----------------+-----------------+
| 7000200   | 5               | add_stake_limit |
+-----------+-----------------+-----------------+
```

## SCALE descriptors

A descriptor is the layout of SCALE bytes, in a syntax Amp defines, close to Rust's:

| Descriptor                      | Bytes                                                                  | Arrow type                              |
| ------------------------------- | ---------------------------------------------------------------------- | --------------------------------------- |
| `bool`                          | 1 byte: 0 or 1                                                         | `Boolean`                               |
| `u8`, `u16`, `u32`, `u64`       | An unsigned integer of 1, 2, 4, or 8 bytes, little-endian              | `UInt8`, `UInt16`, `UInt32`, `UInt64`   |
| `i8`, `i16`, `i32`, `i64`       | A signed integer of the same sizes                                     | `Int8`, `Int16`, `Int32`, `Int64`       |
| `u128`, `i128`                  | An integer of 16 bytes                                                 | `Utf8`, in base 10, to keep every digit |
| `Compact<u8>` to `Compact<u64>` | An unsigned integer in SCALE's compact form, of 1, 2, 4, or more bytes | `UInt8` to `UInt64`                     |
| `Compact<u128>`                 | The same, up to 128 bits                                               | `Utf8`, in base 10                      |
| `[u8; N]`                       | `N` bytes, like `[u8; 32]` for account ids and hashes                  | `FixedSizeBinary(N)`                    |
| `Vec<u8>`                       | A compact length, then that many bytes                                 | `Binary`                                |
| `Vec<T>`                        | A compact count, then that many `T`s, like `Vec<u16>`                  | A `List` of `T`'s type                  |
| `Option<T>`                     | 1 byte, 0 for `None` or 1 for `Some`, then a `T` if it's 1             | `T`'s type, `NULL` for `None`           |
| `{ name: T, … }`                | Each field's bytes, in order, like `{ who: [u8; 32], amount: u64 }`    | A `Struct`, with the fields in order    |

* A `[u8; N]` has no length before it, unlike `Vec<u8>`. Use it for a runtime type that's a fixed-size byte array, like
  an account id, or wraps one.
* Field names only name the struct's fields: decoding follows their order. They're letters, digits, and underscores, and
  spaces don't matter.
* Type names can also start with a capital, like `U64` or `Bool`.
* Descriptors can't have fixed arrays of types other than `u8`, enums, tuples, `u256` or `i256`, or `Compact` of
  anything but unsigned integers.