Most Blockchain Loan explorer Dashboards use real-time data directly queried from the blockchain via either running their own Nodes or querying Graph APIs. While Flipside's Database limited as it is still in its evolutionary stage, I will explore the possibility and feasibility of building such a dashboard using Flipside Data as a backend.
Some of the popular names when it comes to DeFi Dashboards and Management services are zerion.io , zapper.fi , and defisaver.com. These provide very good user experience in terms of the frontend for simplifying the complex world of DeFi, while providing APIs and a variety of means for a skilled developer to use their backend APIs to build other applications on top of them.
However, not all people fit into those two categories. Flipside's main target audience, the analysts, are a set of people stuck right in between the two ends of the spectrum. While the Frontend of those services above, does make it easy for a novice user, an analyst wants a slightly more robust means to get and visualize data. At the same time, the analyst doesn't want to indulge himself in dealing with the complexities of learning Javascript, which is the go-to query language for these backend APIs.
This is where Flipside shines. By having the data formatted into SQL tables, querying is easy for the majority of analysts, who use SQL to build their Tableau or PowerBI dashboards. By providing a simple API to their queries, Flipside allows the user to be robust in terms of the backend. On top of this Flipside also provides a simple Dashboard integration for those without enough programming experience. A novice programmer can incorporate complex data into his simple python knowledge or use it for even more complicated projects. Thus Flipside caters to a wide range of people who are in the middle range of the spectrum.
Moreover, these services mostly provide snapshot of the current state of the EVM, i.e only the current status of a loan can be retrieved. As an analyst, history plays an important role. Tracking metrics like APR change with respect to Market activity like ETH or BTC price can be done easily using Flipside data, albeit with some processing.
All the data required inorder to make this dashboard possible is available on Flipside.
aave.borrows and aave.repayments have the data required.aave.market_stats table.However, there is one problem with the data available.
The earliest aave tables data is from December 1, 2020. This means we could properly track only events after this. This also means, to ensure correct data usage, we should consider loans created after this date only.
Understanding how Loans work in DeFi is important before you take a step in building a dashboard for it.
Loans in DeFi can be taken as borrows against deposited collateral. A certain APR, either variable or stable is levied on this borrow. All the data regarding a borrow is stored in the Ethereum network, under the address. In AAVE for example, though the borrows and repays are saved as separate transactions, the protocol doesn't keep track of a correlation between them.
That is, for example, if a user takes three 100 DAI loans, and makes two 100 DAI repayments, the protocol doesn't keep track of which loans were closed. It simply updates the outstanding amount to be repaid and calculates the interest on top of it. This is done to improve the efficiency of the whole process as repaying two loans separately would require two net transactions and isn't giving much robustness.
This means, the possibility of tracking a single loan is a hard task, possible but probably not worth the hassle. This is because, say a user makes two borrows of 100 DAI and 150 DAI, then makes repayment of 50 DAI. Deciding which borrow transaction was repaid is a tough process.