Blockchain Indexers III: Account Indexer

In the previous article, we built an application that indexes a smart contract, stores it in an SQL database, and creates a GraphQL interface to query the data you need.

In this article, we will build a program that gets the balance of an account over time. This is a feature commonly offered by wallets, exchanges, and paid services. In theory, the only way to do this accurately is to index every transaction from the very first block to the current block.

As discussed in the last article, some services have done this; however, you have to pay to access their data. We will use one of such services in this article.

The alternative will be to get all the information from the chain into a database, and they will query it the way you want (similar to how we did in the smart contract indexer). But that's even more costly and, since we don't need all that information, counterintuitive.

Account Indexer

You notice your wallet balance is finishing so fast compared to the amount you actually get paid. You need to be able to track your spending. See where you spent a lot and where it all went.

A reactive indexer that fetches the ETH balance of an account over some time is exactly what you need. We'll be building this indexer on the Base Chain, but this can be replicated on any EVM-supported chain that is supported by Moralis.

Prerequisites

• Node.js/npm
• Moralis API Key(it's free)

You can decide to use a database to persist your data as you wish, but to keep this article simple, we'll save the data in a local JSON file.

Initialize Repo

npm init -y
npm install moralis viem @moralisweb3/common-evm-utils dotenv
touch index.js

Pseudocode

Before we paste or write any code, we have to talk about how it actually works in plain English. Also, you can decide to swap any of the services and processes as you see fit once you understand the basics.

1. Get the Data From the Blockchain

This is the first and most important step. We'll use Moralis to get the data from the chain. It's free once you create an account. For the scale we need, we don't go beyond the free tier.

• 1.1 . Filter the data and remove transactions where the value is 0.

To reduce the number of queries and time, you can remove transactions where the user has not sent or received any ETH.

However, this would hurt the accuracy since it ignores internal transactions where the user may have received ETH.

Internal transactions are very common with contracts involving ERC20 token swaps. If this trade-off is acceptable, you can filter the data and remove transactions where the value is 0.

2. Get the balance of the user every block in the transaction data.

For all the user's transactions, get the balance at that particular block. Getting the user's transactions helps us narrow only blocks where the user may have spent something, and we can ignore the rest.

3. Manipulate the data as you see fit.

After getting the data from the chain, you have to decide what database you want to use, what information you want to store, how you want to retrieve it, etc. The ball is in your court.

For this article, we do the following:

• Sort from oldest to newest.

• Save the following data:

  1. timestamp: The block timestamp when the transaction occurred.
  2. transactionHash: The hash of the transaction that cause the change in balance.
  3. balance Wei: The user's balance after this transaction occurred, but in wei.
  4. balance Eth: Readable ETH value of the balance

• Save the data in a JSON file corresponding to the user's address. e.g., <user_address>. JSON

Actual Code

Every step in the pseudocode above is implemented in the code below.

1. Get the data from the blockchain.

import { EvmChain } from '@moralisweb3/common-evm-utils';
import Moralis from "moralis";

const MORALIS_API_KEY = "<your-moralis-api-key>" // https://admin.moralis.io/api-keys
const address = "<the-indexed-account-address>" 

await Moralis.start({
    apiKey: MORALIS_API_KEY,
});
const chain = EvmChain.BASE; //The chain we're fetching from 

let cursor;
const response = await Moralis.EvmApi.transaction.getWalletTransactions({
                address: address, // The user address
                chain: chain,
                limit: 100,
                cursor: cursor, //Currently undefined but will exist as we loop
                order: 'ASC' //Sort from oldest to newest
            });

const responseJson = response.toJSON();

cursor = responseJson.cursor;

const result = responseJson.result;
//This returns 100 transactions per query, so you have to use the returned cursor to keep fetching until you get all the transactions 

/*
do{
//  get user transaction data
}
while(cursor)
*/

//Documentation: https://docs.moralis.com/web3-data-api/evm/reference/get-wallet-transactions?address=0x1f9090aaE28b8a3dCeaDf281B0F12828e676c326&chain=eth&order=DESC

• 1.1. Filter the data and remove transactions where the value is 0.

//if speed > accuracy for your use case
const filteredData = result.filter((tx) => tx.value > 0);

2. Get the balance of the user every block in the data + Manipulate the data

import { createPublicClient, formatEther, http, type Address } from "viem";
import { base } from "viem/chains";

// Use your own RPC endpoint to reduce rate limiting.
const ALCHEMY_API_KEY = "<your-alchemy-api-key>" // https://dashboard.alchemy.com/?a=settings/api-keys

  //Initialize the client
const baseClient = createPublicClient({
    chain: base,
    transport: http(),
    // transport: `http("https://base-mainnet.g.alchemy.com/v2/${YOUR_ALCHEMY_API_KEY}`),
});

const i = 0
for(const tx of txData){
  const balanceWeiAtThatBlock =  await baseClient.getBalance({
                address: accountAddress, blockNumber
            })
 
  const balanceEthAtThatBlock = parseFloat(formatEther(balanceWeiAtBlockNumber))
  //Wait 10 seconds after 30 queries to prevent rate limiting
  if(i % 30 === 0 && i !== 0) await new Promise(resolve => setTimeout(resolve, 10000));

  //Manipulate the data how you see fit.
  balanceHistory.push({
    timestamp: tx.timestamp,
    transactionHash: tx.hash,
    balanceWei: balanceWeiAtThatBlock.toString(),
    balanceEth: balanceEthAtThatBlock
  })
  i++
  }

4. Save the data in your database.

As stated earlier, we're going to save the data in a JSON file.

//...other imports
import fs from 'fs';

const data = JSON.stringify(balanceHistory);
if(!fs.existsSync('./data')) fs.mkdirSync('./data');
fs.writeFileSync(`./data/${address}.json`, data);

Full Source Code on Github

Since this article will get longer than it should, the code shown above is all you need if you already have a basic understanding of JavaScript and Node.js. However, there's still more code to be written.

For reference, you can check out the full code on GitHub.

EVM Account Indexer Github

Challenge Yourself

Here are a few ways you can build on this project:

• You can build an indexer that shows the gas price used by an address so far.

• Build a frontend that displays the data in tables and charts.

• Build an app that goes beyond just ETH balances but rather shows the aggregated value of all tokens in the wallet over time (see Debank Historical Graph).

Conclusion

This article aimed at showing how to build an account indexer from a mid level. On a lower level, you would index the entire blockchain yourself and on a higher level, you would use a service like Zeroin Api and hopefully you understand the work involved that makes it a paid service. But under the hood, it's all the same.