Integrating with the "indexer sweeper"

[mikedotexe]

There are two aspects to the CronCat indexer:

1. Rust indexer
2. Indexer ✌️ Sweeper ✌️

Rust indexer

The Rust indexer (the repo where this issue is created, obvs) is in charge of being fast, efficient, and able to handle the scenario of a world where we have hundreds, thousands, or more transactions per block.

There are a ton of smart contract queries needed to fully index what's happening with CronCat, but those don't need to be done in Rust. Instead, the Rust indexer will be ingesting information about new blocks coming in.

When a new block comes in — either detected via websockets or polling — there is a limited amount of information we get. We get the block height, timestamp, and transaction info in the block. The transaction info doesn't have everything we need, but it does have info on what messages are in the transaction.

For our purposes, we only care about blocks where end users/apps/protocols/agents are calling CronCat contracts. (The primary one being the CronCat Manager)

So we'll want to ingest a new block, filter to see if there are transactions with messages that are WasmExecute ones going to one of our contracts, and then capture the message info.

Once we've written (limited) rows to our database, the NodeJS Indexer Sweeper will use the handy tools from CosmJS in order to query the blockchain for account balances and contract state at that given height.

Talking brass tax, here's the only thing we want the Rust indexer to be writing, and nothing else:

Writing a row to the blocks table, and getting the id that's returned from that INSERT, but only if this block interacts with one of the CronCat contracts.

Writing row(s) to the transactions table using the id from the block a second ago, similarly returning the transaction tables' primary key, 'id.

Writing message(s) to the messages table, using the transaction id

Writing the contract address and chain ID prefix (like uni for uni-5) to the contracts table.

---

Note: we just added the smart contract's address but no other information about it. The sweeper will fill in the rest, making sure all the database tables have useful foreign keys to enable useful and deep querying later. It does this for the contracts table as described just now, but will also fill in the entities inside our smart contract storage.

The entities in our smart contract storage are:

1. Config
2. Agents
3. Tasks

Tasks also contains what's called multi-valued attributes. For instance, a task that has multiple rules will need to store those in another database table, and relate to its task. So there are a few entities inside of tasks, you could say, and they all get their own database table. It looks like this:

Roughly the top half of that chart are tables the Rust indexer will to insertions on. It will not touch any tables including or below agents, config, and tasks, as those will be filled in by the indexer sweeper CosmJS code. (There are many helpful libraries in CosmJS that make this part of the process easier to encode/decode messages, protobufs, oh my. The Rust indexer gets the initial block info written as efficient and scalable as possible.)

Regarding the database and the relationships, here's the schema that's fairly mature:

https://github.com/CronCats/indexer-sweep/blob/6b170d007f599ac2f30d2e2113f2e6036822946d/db/schema.sql

---

Indexer Sweeper

The indexer sweeper lives in this repository:

https://github.com/CronCats/indexer-sweep
Please see the README there for usage instructions.

It's a simple TypeScript project that uses CosmJS to query the blockchain for smart contract state and protocol-level values. Example: we keep track of each CronCat agent's protocol balances as well.

Aim and direction

At this time, there are a handful of useful queries we can use to start capturing important information. One of the first things we'll look for is:

Are all the fees working exactly as we expect.

We can test this out as follows:

1. Instantiate a new CronCat Manager on Juno testnet, one that only we care about.
2. Run the indexer(s) so they're "watching" the chain
3. Run an agent that (first registers and then) starts running, pointing at the our new contract.
4. Create a recurring task, maybe a bank payment message to one person.
5. Let the task run out naturally, either because the agent runs out of funds (this might be fixed now, not sure) or the task is removed because it has depleted the balance that was initially sent to it upon task creation.

Then we can analyze the results of the payments, and learn more about how much is going to gas, how we are adding up the rewards to the agent, and more.

[mikedotexe]

Filled out this README, which may be helpful as well:
https://github.com/CronCats/indexer-sweep

[mikedotexe]

--- Custom queries

--- get all agents' info at a block height

--- So first let's get these from the user:
--- - chain_id_prefix
--- - contracts.address (contractAddress)
--- - start height
--- - end height
--- And we know the start and end will need to be queried before anything, and look for the first, highest-known

SELECT * FROM agents INNER JOIN agent_balances ON agents.id = agent_balances.fk_agent_id WHERE agents.id IN
(SELECT DISTINCT(a.id) FROM agents as a INNER JOIN contract_block_piv as cb ON a.fk_cb_id = cb.id INNER JOIN blocks as b ON cb.fk_block_id = b.id INNER JOIN contracts as c ON c.id = cb.fk_contract_id INNER JOIN chain_network as cn ON cn.id = c.fk_chain_network_id INNER JOIN chain_network ON chain_network.id = b.fk_chain_network_id WHERE cn.chain_id_prefix = 'uni' AND b.height BETWEEN 669130 AND 669197 AND c.address = 'juno1ds4zngqcmaxyxp39zp40lphsezwu553mr5m2jtnxykpuu6z5g03sm9zqqu');

--- get all config

SELECT * FROM config INNER JOIN config_balances ON config.id = config_balances.fk_config_id WHERE config.id IN
(SELECT DISTINCT(config.id) FROM config INNER JOIN contract_block_piv as cb ON config.fk_cb_id = cb.id INNER JOIN blocks as b ON cb.fk_block_id = b.id INNER JOIN contracts as c ON c.id = cb.fk_contract_id INNER JOIN chain_network as cn ON cn.id = c.fk_chain_network_id INNER JOIN chain_network ON chain_network.id = b.fk_chain_network_id WHERE cn.chain_id_prefix = 'uni' AND b.height BETWEEN 669130 AND 669197 AND c.address = 'juno1ds4zngqcmaxyxp39zp40lphsezwu553mr5m2jtnxykpuu6z5g03sm9zqqu');

--- get all tasks (lots of task_* tables not finished atm)

SELECT * FROM tasks WHERE tasks.id IN
(SELECT DISTINCT(tasks.id) FROM tasks INNER JOIN contract_block_piv as cb ON tasks.fk_cb_id = cb.id INNER JOIN blocks as b ON cb.fk_block_id = b.id INNER JOIN contracts as c ON c.id = cb.fk_contract_id INNER JOIN chain_network as cn ON cn.id = c.fk_chain_network_id INNER JOIN chain_network ON chain_network.id = b.fk_chain_network_id WHERE cn.chain_id_prefix = 'uni' AND b.height BETWEEN 669130 AND 669197 AND c.address = 'juno1ds4zngqcmaxyxp39zp40lphsezwu553mr5m2jtnxykpuu6z5g03sm9zqqu');