Postgres/Thalos

This page covers the postgres tables for the Token Farms Indexer, and their purpose/behavior. First, a quick note about rollbacks.

A "rollback" is another term for a fork in the blockchain. Since we process data in real time, it means that the actions we index are not yet irreversible. There is a chance that there will be a fork, and the indexed data becomes invalidated.

Handling Rollbacks

The way we handle rollbacks is by indexing every table delta when we INSERT or UPDATE a postgres record. In other words, when we make a change to a row, there is the "old data" and the "new data".

For example, let's say at block 100, we INSERT a row for a staker. In this case, we will also add a record of this delta in the tokenfarms_staker_deltas table. We will store the old data (in this case, an empty row), and the block number where the INSERT was executed.

Now, let's say at block 110, there is a fork - and the rollback goes back to block 99. What we will do here, is fetch all deltas that took place on or after block 99. We will then loop through each of these deltas, and reverse them (in this case, reversal means deleting the row we added at block 100).

In order to keep database operations atomic, when a rollback is in progress, we create a local variable in postgres called rollback_is_in_progress. Whenever this variable is true, any new transactions will not be indexed until the rollback is fully processed. Then, operations will continue as normal.

Furthermore, since Thalos messages are handled asynchronously and there is no guarantee that they will be processed in the correct order, the postgres tables are set up to reject any updates where the global_sequence of the new data is less than the global_sequence of the old data. This ensures that any time we update a row, we always have the most up to date information.

---

Tables

tokenfarms_health

The main purpose of the health table is to store the last block that was processed by our indexer. This allows us to start/stop Thalos using a script, which will pick up exactly where it left off the last time it was ran.

This helps avoid the process of needing to manually edit config files, or specify a start_block whenever you restart thalos. You can simply run the start.sh script, which will check the health table for the last_updated_block, and start reading from that block.

The health table also keeps track of the head block on whatever node you read from, which allows you to keep track of whether or not your Thalos instance is lagging behind the chain state (although, this functionality is not implemented yet).

In a future release, we will also add a /health endpoint to the Express server, so users can check if the API is in sync with the network.

tokenfarms_farms

The farms table keeps track of data related to every farm that has been created on the tf.waxdao contract. Whenever the state of a farm is updated on the blockchain, this table will also be updated. This is the table that is used by the express server when serving requests for farm details.

tokenfarms_stakers

The stakers table keeps track of the state of every user who has tokens staked on the tf.waxdao contract. This is used by the Express server for providing leaderboards, "staked only" requests, etc.

tokenfarms_farm_deltas

The farm deltas table keeps a record of every change that has been made to any rows in the farms table. This is how we determine which actions to reverse when handling a chain rollback.

tokenfarms_staker_deltas

This works the same way as the farm deltas table, except it keeps track of deltas on the stakers table. When processing chain rollbacks, this determines which staker rows need to be adjusted/removed.