Published on

Indexing the Blend YieldBlox Pool in 125 Lines of Code and 8 Minutes.

Authors

If you've watched the YieldBlox indexing previews https://x.com/xyclooLabs/status/1817949476352413848 and https://x.com/xyclooLabs/status/1819011595906752753 and are waiting to see your historical actions on the YieldBlox pool, you can skip to the result.

As hinted in the tweet, we'll be building an indexer and API (to retrieve indexed data) specifically for the YieldBlox blend pool.

Why Mercury and Zephyr.

In the first tweet, I showcased how to get raw events for the YieldBlox blend pool, but what if we want more structured data that is processed, indexed and retrieved in a way that is specific to the protocol and the pool?

This is often an important requirement for bigger and more structured protocols, or in general for a more efficient and real-time data provision.

Gernerally, to truly achieve this with hands on the code protocols would have to have their own server running a stellar core node, software to connect to its data, and processing logic on raw XDR data and initiate hours-long (if not days) catchups. However, leveraging Mercury's cloud execution environment we can achieve the exact same behaviour without having to run any infrastructure or code complex adapter logic.

Imagine that you could just write a program using an SDK built for building indexers, compile, deploy and immediately start querying. Additionally, you can rely on already processed historical data making catchups much faster.

No Vendor Lock-In

Note that once you write your zephyr program it's not only bound to be executed in our servers, you can choose to self host and execute it on your infrastructure. Of course, you'd loose many of the features of our cloud environment (catchups, function invocation infra, etc), but your program can run anywhere!

Setup

To write custom indexers and APIs with Mercury, you'll need three things:

  1. Rust installed (you can skip this assuming that you already work with Soroban smart contracts)
  2. Mercury CLI installed: cargo install mercury-cli.
  3. A Mercury account. We'll be working on mainnet so you need to create a mainnet account: https://main.mercurydata.app.

Creating the project.

To create a new Mercury project, use the Mercury CLI:

mercury-cli new-project --name blend-ybx

Now we're ready to start writing our API.

Writing the API

Full code is at https://github.com/heytdep/blend-ybx-pool-indexer.

Understanding How and What to Index.

Zephyr programs have a special function, on_close, which is immediately invoked by our ingestion service as soon as a new leger closes with information about the ledger transition. As a result, the logic we'll use to build is to assume that we already are in possession of all ledger transition information (events, changes, transactions, etc).

Given that we're working with Soroban, we'll be going for an event-based indexer which brings several advantages as they are emitted sequentially and can be caught up through a data catchup. Since we want to keep all of the YieldBlox pool's borrow, repay, and collateral (deposit, withdraw) actions, we need to work with events as a trigger for processing and DB operations.

More specifically, we aren't creating any aggregation here, just indexing the actions we're interested in and organizing them in the database for easy retrieval.

Tables Setup

We need to first define how we want our indexed tables to look like. For this program, we will actually only rely on one table where we discern on the various actions through a column:

use serde::{Deserialize, Serialize};
use zephyr_sdk::{
    prelude::*,
    soroban_sdk::{xdr::{ScVal, ScString}, Address, Symbol},
    DatabaseDerive, EnvClient, PrettyContractEvent,
};

#[derive(Serialize, Deserialize, Clone, Copy)]
#[repr(u32)]
pub enum Action {
    Borrow,
    Collateral,
}

#[derive(DatabaseDerive, Serialize)]
#[with_name("actions")]
pub struct Actions {
    pub action: u32,
    pub timestamp: u64,
    pub ledger: u32,
    pub asset: String,
    pub source: String,
    pub amount: i64,
}

Since the Blend events are very well defined and are similar for every action, our Actions table is homogenized among actions, with the only factor that identifies the actions being the action field.

Note that our Action enum only includes Borrow and Collateral without discerning between borrow and repays, or collateral supply or withdraw actions. That's because we will identify repays and withdrawals with negative amounts in Actions.amount.

Now that we have defined our tables structure, we need to add the table to the soon-to-be script-generated zephyr.toml config file:

name = "blend-ybx"

[[tables]]
name = "actions"

[[tables.columns]]
name = "action"
col_type = "BYTEA"

[[tables.columns]]
name = "timestamp"
col_type = "BYTEA"

[[tables.columns]]
name = "ledger"
col_type = "BYTEA"

[[tables.columns]]
name = "asset"
col_type = "BYTEA"

[[tables.columns]]
name = "source"
col_type = "BYTEA"

[[tables.columns]]
name = "amount"
col_type = "BYTEA"

Writing the Ingestion Logic

Now that our database is taken care of, we can proceed writing the on_close function, i.e the function that will take care of real-time ingestion.

The logic is quite simple:

  1. We get the newly events, if any, of the YieldBlox pool contract.
  2. For each of the event, we understand whether it's a
    • supply_collateral, if so, we insert a new Action::Collateral action with a positive amount.
    • withdraw_collateral, if so, we insert a new Action::Collateral action with a negative amount.
    • borrow, if so, we insert a new Action::Borrow action with a positive amount.
    • repay, if so, we insert a new Action::Borrow action with a negative amount.
const CONTRACT: &'static str = "CBP7NO6F7FRDHSOFQBT2L2UWYIZ2PU76JKVRYAQTG3KZSQLYAOKIF2WB";

#[no_mangle]
pub extern "C" fn on_close() {
    let env = EnvClient::new();
    let ybx_contract = stellar_strkey::Contract::from_string(&CONTRACT).unwrap().0;
    let searched_events: Vec<PrettyContractEvent> = {
        let events = env.reader().pretty().soroban_events();
        events
            .iter()
            .filter_map(|x| {
                if x.contract == ybx_contract {
                    Some(x.clone())
                } else {
                    None
                }
            })
            .collect()
    };

    for event in searched_events {
        let action: Symbol = env.from_scval(&event.topics[0]);
        if action == Symbol::new(env.soroban(), "supply_collateral") {
            Actions::add(&env, Action::Collateral, event, true);
        } else if action == Symbol::new(env.soroban(), "withdraw_collateral") {
            Actions::add(&env, Action::Collateral, event, false);
        } else if action == Symbol::new(env.soroban(), "borrow") {
            Actions::add(&env, Action::Borrow, event, true);
        } else if action == Symbol::new(env.soroban(), "repay") {
            Actions::add(&env, Action::Borrow, event, false);
        }
    }
}

We outsourced the database insertion to Actions::add, which simply extracts the actual amount (blend event's data is a tuple, were the first element is the amount), understands whether the amount should be positive or negative, decodes the ScVals to strings and integers, and lastly puts the action in the actions table:

impl Actions {
    fn new(
        env: &EnvClient,
        action: Action,
        timestamp: u64,
        ledger: u32,
        asset: ScVal,
        amount: i128,
        source: ScVal,
    ) -> Self {
        let asset = address_to_alloc_string(env, env.from_scval(&asset));
        let source = address_to_alloc_string(env, env.from_scval(&source));
        Self {
            action: action as u32,
            timestamp,
            ledger,
            asset,
            amount: amount as i64,
            source,
        }
    }

    fn add(env: &EnvClient, action: Action, event: PrettyContractEvent, increase: bool) {
        let (amount, _): (i128, i128) = env.from_scval(&event.data);
        let delta = if increase { amount } else { -amount };
        let supply = Actions::new(
            env,
            action,
            env.reader().ledger_timestamp(),
            env.reader().ledger_sequence(),
            event.topics[1].clone(),
            delta,
            event.topics[2].clone(),
        );
        env.put(&supply);
    }
}

Data Retrieval Through A Custom API.

Now that we have sorted out the indexer part, we need to retrieve the data. We could either use directly the graphql API, but that would require decoding the values (or indexing them as XDR and using stellar tooling to decode them). A much faster path is to create an API directly inside the Mercury program. More specifically, we want to allow retrieving all borrow and collateral actions, but also allow to retrieve them by address.

When building an API with Mercury, you have two approaches:

  1. Single function, bigger request object with more variants.
  2. Multiple functions, simpler request object.

Both efficiency and size can generally be ignored for what regards Mercury, but we'll take the first approach in this tutorial.

How do Mercury APIs Work?

Mercury custom APIs are based on additional exported functions in your binary that can be invoked through our cloud infra. The workflow is quite simple:

  1. Define a request object.
  2. Use env.read_request_body() to get the request.
  3. Perform queries, processing, or any other task.
  4. Return a response through env.conclude(&response).

Defining the Request Object

Since we want to retrieve our actions depending on the action type, and potentially have the API specify an address as filter, we define the request simply as:

#[derive(Serialize, Deserialize)]
pub struct Request {
    kind: Action,
    address: Option<String>
}

Note that the Action object is the very same enum we're using for the ingestion.

Building the retrieve function API.

We'll call our single API function retrieve, and its task will simply be to query the database applying the request-specified filters:

#[no_mangle]
pub extern "C" fn retrieve() {
    let env = EnvClient::empty();
    let request: Request = env.read_request_body();

    let actions: Vec<Actions> = if let Some(address) = request.address {
        env.read_filter()
            .column_equal_to("action", request.kind as u32)
            .column_equal_to("source", address)
            .read()
            .unwrap()
    } else {
        env.read_filter()
            .column_equal_to("action", request.kind as u32)
            .read()
            .unwrap()
    };

    env.conclude(&actions)
}

Local Testing

Before we can test or deploy, we need to build our program. Assuming that you're on the latest mercury-cli release:

mercury-cli build

Zephyr programs can also be locally tested by fabricating artificial inputs such as contract events or other ledger transition data. For example, we can test craft collateral deposits and withdrawals as follows:


#[cfg(test)]
mod test {
    use ledger_meta_factory::TransitionPretty;
    use stellar_xdr::next::{Hash, Int128Parts, Limits, ScSymbol, ScVal, ScVec, WriteXdr};
    use zephyr_sdk::testutils::TestHost;

    fn add_collateral(transition: &mut TransitionPretty) {
        transition.inner.set_sequence(2000);
        transition
            .contract_event(
                "CBP7NO6F7FRDHSOFQBT2L2UWYIZ2PU76JKVRYAQTG3KZSQLYAOKIF2WB",
                vec![
                    ScVal::Symbol(ScSymbol("supply_collateral".try_into().unwrap())),
                    ScVal::Address(stellar_xdr::next::ScAddress::Contract(Hash([8; 32]))),
                    ScVal::Address(stellar_xdr::next::ScAddress::Contract(Hash([1; 32]))),
                ],
                ScVal::Vec(Some(ScVec(vec![ScVal::I128(Int128Parts {
                    hi: 0,
                    lo: 1000000000,
                }), ScVal::I128(Int128Parts {
                    hi: 0,
                    lo: 500000000,
                })].try_into().unwrap()))),
            )
            .unwrap();
    }

    fn withdraw_collateral(transition: &mut TransitionPretty) {
        transition.inner.set_sequence(2010);
        transition
            .contract_event(
                "CBP7NO6F7FRDHSOFQBT2L2UWYIZ2PU76JKVRYAQTG3KZSQLYAOKIF2WB",
                vec![
                    ScVal::Symbol(ScSymbol("withdraw_collateral".try_into().unwrap())),
                    ScVal::Address(stellar_xdr::next::ScAddress::Contract(Hash([8; 32]))),
                    ScVal::Address(stellar_xdr::next::ScAddress::Contract(Hash([1; 32]))),
                ],
                ScVal::Vec(Some(ScVec(vec![ScVal::I128(Int128Parts {
                    hi: 0,
                    lo: 1000000000,
                }), ScVal::I128(Int128Parts {
                    hi: 0,
                    lo: 500000000,
                })].try_into().unwrap()))),
            )
            .unwrap();
    }

    // ... more code below
}

And then use these functions within the actual test to verify that our program is processing data as intended:

#[tokio::test]
async fn withdraw() {
    let env = TestHost::default();
    let mut program = env.new_program("./target/wasm32-unknown-unknown/release/blend_ybx.wasm");

    // Below is a test connection string. Feel free to use this or other connection strings.
    let mut db = env.database("postgres://postgres:postgres@localhost:5432");
    db.load_table(0, "actions", vec!["action", "timestamp", "ledger", "asset", "source", "amount"])
        .await;

    assert_eq!(db.get_rows_number(0, "actions").await.unwrap(), 0);

    let mut empty = TransitionPretty::new();
    program.set_transition(empty.inner.clone());

    let invocation = program.invoke_vm("on_close").await;
    assert!(invocation.is_ok());
    let inner_invocation = invocation.unwrap();
    assert!(inner_invocation.is_ok());

    assert_eq!(db.get_rows_number(0, "actions").await.unwrap(), 0);

    // After deposit

    add_deposit(&mut empty);
    program.set_transition(empty.inner.clone());

    let invocation = program.invoke_vm("on_close").await;
    assert!(invocation.is_ok());
    let inner_invocation = invocation.unwrap();
    assert!(inner_invocation.is_ok());

    assert_eq!(db.get_rows_number(0, "actions").await.unwrap(), 1);

    // After deposit + withdrawal

    add_withdraw(&mut empty);
    program.set_transition(empty.inner);

    let invocation = program.invoke_vm("on_close").await;
    assert!(invocation.is_ok());
    let inner_invocation = invocation.unwrap();
    assert!(inner_invocation.is_ok());

    assert_eq!(db.get_rows_number(0, "actions").await.unwrap(), 3);
    
    db.close().await
}

Note that testing zephyr programs requires a postgres setup and a valid db connection string as the code comment above the db setup hints.

For more information about testing and a complete walkthrough you can check out the dedicated docs section.

Deploying

To deploy the program on mainnet, use the mercury cli:

mercury-cli --jwt "$MERCURY_JWT" --mainnet true deploy

Assuming that the JWT is correct, you should see a successful deployment message.

You can monitor and pause your program in the dedicated page in the Mercury webapp.

Catching Up

Since our indexer is event-based, we can rely on Mercury's cloud infra to execute catchups that would take hours with stellar core in minutes. For example, for the YieldBlox pool we can use a scoped catchup to only catchup with borrow, repay, supply_collateral and withdraw_collateral events:

mercury-cli --jwt "$MERCURY_JWT" --mainnet true catchup --contracts "CBP7NO6F7FRDHSOFQBT2L2UWYIZ2PU76JKVRYAQTG3KZSQLYAOKIF2WB" --topic1s "AAAADwAAABFzdXBwbHlfY29sbGF0ZXJhbAAAAA==" --topic1s "AAAADwAAABN3aXRoZHJhd19jb2xsYXRlcmFsAA==" --topic1s "AAAADwAAAAZib3Jyb3cAAA==" --topic1s "AAAADwAAAAVyZXBheQAAAA=="

This will return us a catchup id, allowing us to check the status of the catchup with:

curl https://mainnet.mercurydata.app/catchup/ID

Furthermore, if we added logs in our program, we can see them under the "User Logs" section in the Mercury webapp.

Once the catchup is complete and the program resumes its ingestion work, it's time to test out the API and start retrieving the data.

About public functions

Before we get to the result, I'll briefly mention public zephyr funcitons which are one of the newest features that were introduced in Mercury. Since every invocation to a Mercury function, such as for example the retrieve function spins up a new VM, and executes arbitrary code, such functions are guarded behind an authentication mechanism that allows only the program's owner to execute such functions. However, some other functions such as the retrieve function that are meant for the public can now be opened and invoked by anyone if the owner sets the function within the program's public functions.

To set your program's public funcitons, you can use the Mercury API:

curl -X POST https://mainnet.mercurydata.app/zephyr/pubfunctions -H "Authorization: Bearer $MERCURY_JWT" -H "Content-Type: application/json" -d '{"public":["retrieve"]}'

More information about public functions can be found in the documentation.

Result

We can now call the (now public) retrieve function with the various request combinations:

  • All collateral operations:
curl -X POST https://mainnet.mercurydata.app/zephyr/execute/30 -H 'Content-Type: application/json' -d '{"mode":{"Function": {"fname": "retrieve", "arguments": "{\"kind\": \"Collateral\"}"}}}'
  • Collateral operations for a certain user (in this case we randomly chose GDJSH2NU2WF6J4P5DL4522DUCABWSTZOKFQ7BHBCFYQ3QKC6FRYWP6OL):
curl -X POST https://mainnet.mercurydata.app/zephyr/execute/30 -H 'Content-Type: application/json' -d '{"mode":{"Function": {"fname": "retrieve", "arguments": "{\"kind\": \"Collateral\", \"address\": \"GDJSH2NU2WF6J4P5DL4522DUCABWSTZOKFQ7BHBCFYQ3QKC6FRYWP6OL\"}"}}}'
  • All borrow/repay operations:
curl -X POST https://mainnet.mercurydata.app/zephyr/execute/30 -H 'Content-Type: application/json' -d '{"mode":{"Function": {"fname": "retrieve", "arguments": "{\"kind\": \"Borrow\"}"}}}'
  • Borrow/repay operations for a certain user (in this case we randomly chose GDJSH2NU2WF6J4P5DL4522DUCABWSTZOKFQ7BHBCFYQ3QKC6FRYWP6OL):
curl -X POST https://mainnet.mercurydata.app/zephyr/execute/30 -H 'Content-Type: application/json' -d '{"mode":{"Function": {"fname": "retrieve", "arguments": "{\"kind\": \"Borrow\", \"address\": \"GDJSH2NU2WF6J4P5DL4522DUCABWSTZOKFQ7BHBCFYQ3QKC6FRYWP6OL\"}"}}}'