Extending the substrate-archive with more on-chain data

Question

Substrate community!

Recently I came across the substrate-archive project. It does its indexing job extremely well, and I wanted to utilize it with some modifications here and there. Things I wanted to change are the following:

1. In table storage, I would like to add two varchar columns corresponding to the section and method of Substrate events. How do I decode these two out of the raw storage key?
2. For every Substrate event that is in storage table, I need to additionally store its numeric index. Can this information be taken from the on-chain data?
3. For every extrinsic, I would like to additionally store its section and method.
4. For every extrinsic, I would like to store the success flag: whether the transaction was successful or not.
5. For every extrinsic, I would like to store the signed flag: whether the transaction was signed or not.

Any pointers of how to achieve the above are greatly appreciated!

Answer 1

So, I'm not super familiar with substrate-archive, but perhaps I can point you towards some example code to help you with your modifications!

For substrate event decoding (useful for your 1 and 2), one example is seen here:

https://github.com/paritytech/subxt/blob/82f304005b700fabc6632f96f7945595f4da6673/subxt/src/events/events_type.rs#L50

This code:

• gets hold of the events from storage for a given block hash.
• reads the number of events from the beginning of these bytes
• Puts the remaining event bytes into https://github.com/paritytech/subxt/blob/82f304005b700fabc6632f96f7945595f4da6673/subxt/src/events/events_type.rs#L93
• Allows for these event bytes to be iterated over, keeping track of the current event index (your 2), at https://github.com/paritytech/subxt/blob/82f304005b700fabc6632f96f7945595f4da6673/subxt/src/events/events_type.rs#L181
• The above decodes each event using https://github.com/paritytech/subxt/blob/82f304005b700fabc6632f96f7945595f4da6673/subxt/src/events/events_type.rs#L333, which decodes the event phase, pallet and variant index (or in other words your 1).

The phase of an event points at the specific extrinsic index that it was emitted from (if it came from an extrinsic at all). If an extrinsic succeeded or failed, there will be an ExtrinsicSuccess or ExtrinsicFailure event associated with it (subxt uses those to determine whether an extrinsic has succeeded or failed here: https://github.com/paritytech/subxt/blob/82f304005b700fabc6632f96f7945595f4da6673/subxt/src/transaction.rs#L388 (your 4 and 5)).

In actuality, substrate-archive uses desub under the hood to help decode things. as of V14 metadata, decoding an extrinsic looks like this:

https://github.com/paritytech/desub/blob/f5377bc9946b4b0a89a6b89b0ae58d915683f9c8/desub-current/src/decoder/mod.rs#L159

Getting the pallet and call index from the extrinsic (what you called "section" and "method" in 3) is done here:

https://github.com/paritytech/desub/blob/f5377bc9946b4b0a89a6b89b0ae58d915683f9c8/desub-current/src/decoder/mod.rs#L335

Without looking at substrate-archive I can't recall what is currently stored and so on, so I'm not sure how helpful all of the above really is, but I hope it at least provides some pointers to code that does similar, which may be of use!

Answer 2

Piggybacking on @jsdw answer:

In table storage, I would like to add two varchar columns corresponding to the section and method of Substrate events. How do I decode these two out of the raw storage key?

Storage Keys are transparent (this blog post by Shawn explains this well, and most of it still applies today even if substrate has changed). Therefore each storage key is made up of something like hash(module)+hash(method). The easiest way I have found to reverse this is to create a lookup-table using the metadata and attach each hash to it's corresponding module/method. Since hashes are fixed-length you would be able to separate out the parts from the indexed storage key in SQL and figure out the location based on this.

For every Substrate event that is in storage table, I need to additionally store its numeric index. Can this information be taken from the on-chain data?

@jsdw has a good answer for this; event index should be able to be grokked from it's combination with metadata

For every extrinsic, I would like to additionally store its section and method. For every extrinsic, I would like to store the success flag: whether the transaction was successful or not. For every extrinsic, I would like to store the signed flag: whether the transaction was signed or not.

All this extrinsic data should all be stored in the JSONB field of the extrinsics table in SQL (it has already been decoded with desub), which can be directly queried. You can tell a transaction is 'signed' because it will have an additional 'signature' field in this JSON. No signature means not signed

Answer 3

I think this is just a matter of correctly constructed query, I'm not very familiar with substrate-archive, but I use subsquid archive (indexer) to achieve the same thing.

This query will yield what you described:

query Events {
  substrate_event(limit: 10) {
    indexInBlock
    name
    blockNumber
    extrinsic {
      name
      substrate_events(where: {section: {_eq: "system"}}) {
        name
      }
    }
  }
}

You can play with it here.