Lending Subgraph Standard
I am working on a project and I need a lot of lending data. But the Subgraph data is unorganized and hard to get. Here are some problems that I face:
- There are many protocols to grab from, with different implementations:
- Aave & Comp - Pooled. Global risk. Multi-asset. Borrow assets come from deposits.
- Maker & Abracadabra - Collateralized Debt Positions (CDPs). Isolated. Borrow a single synthetic asset minted, such as DAI or MIM.
- Synthetix - Pooled. Global risk. Single asset as collateral (SNX). sUSD and many other synthetic assets minted.
- RAI, Liquity - CDP, single asset as collateral, ETH. Borrow a single synthetic asset, RAI or LUSD.
- It is not always clear which subgraph to use, e.g. there are +10 Maker Subgraphs.
- Many Subgraphs are purpose-built for front ends. These are not good for data analytics.
- Every Subgraph is implemented differently, which requires reading the Subgraph schema and mappings to figure out what the developer did.
- This also requires you to clean up the data when ingesting it all, since all the entities and fields are different
This lead me to the fact that a Lending Subgraph Standard should be created.
What is a Lending Subgraph Standard?
It is a straight-forward Subgraph Schema that would allow for all lending protocols to fit basic lending data inside. For example, all lending protocols have the following events:
DepositWithdrawBorrowRepayLiquidate
And each protocol is implemented differently. But the Subgraph mappings can be used to generalize them, and provide all data in a clean and standard format.
What are the benefits?
There are many benefits:
- It is very useful for data analysts and data companies.
- It shows the strength of Subgraphs - in how you can combine data across many protocols, and provide a view into the entire decentralized economy. I think today most Subgraphs are only focused on a single protocol.
- It should save data analysts who do not know Solidity a ton of time. They will not have to analyze each Subgraph and search through Solidity code to understand what the data is.
- I believe this Subgraph could also be a huge driver for queries, and for the decentralized network. It is very relevant information for so many different people and financial entities, as lending is the backbone of DeFi. As DeFi grows more and more, this Subgraph could become the main source for all data analytics on DeFi lending.
The Schema
I spent many hours designing this schema to be as accurate and condensed as possible. You can find the repo here, but I have pasted the schema at the bottom of this post for convenience.
I am looking for feedback from data analysts, subgraph experts, defi experts, or anyone else interested in this. This schema is not finalized, I don’t believe I have thought it through from every angle.
Important Details on the Schema
- CDPs have been aggregated into a single
Market. - Price of assets in ETH is left out on purpose. This is to keep it general enough to include other L1s.
- USD price is used as the base asset. There are two ways it is calculated:
- For live values, it must be calculated at query time.
- For lifetime values, it is calculated at event time (instead of adding up the asset amount over time and then applying the USD price at query time).
- You can see more details on the decisions in the repo.
Limitations
The limitations are the classic things we experience with Subgraphs today:
- Most lending protocols calculate interest optimistically with the block delta since the user has deposited or borrowed in the protocol. Until Query Time Computation is available for Subgraphs, this information will have to be aggregated outside of the subgraph. A solution could be to build an off-subgraph aggregator that anyone could use, or at least provide a script that will help people build their own.
- We also have to query time compute live USD prices.
- Indexing - Will we be able to deploy the Subgraph as a single Subgraph, or will we have to split it into multiple to speed up indexing? Time will tell, but there are a lot of improvements coming to
graph-node. I would assume one day it will be possible if it isn’t already today. A solution right now would be to create a script that can edit the manifest(s) to deploy as a single Subgraph, or as separate Subgraphs.
Conclusion
- I believe there are many instances of Subgraphs that will be built like this - aggregating across protocols, instead of Subgraphs built for a single protocol.
- Lending will be one of the biggest. DEX trading is another one.
- It would be great to get more cross-protocol data Subgraphs on the decentralized network. It should drive a lot of queries to the protocol.
- I would like help and input from anyone who is interested in this standard!
The Schema
note - it is much easier to read the schema on github
enum LendingType {
CDP
Pooled
}
enum RiskType {
GlobalRisk
IsolatedRisk
}
"""
Protocol represents all aggregated information of a single protocol on a single network.
"""
type Protocol @entity {
"The Name of the Protocol"
id: ID!
"The Network the protocol exists on - Ethereum, Polygon, etc."
network: String!
"Type of lending protocol"
type: LendingType!
"Type of risk"
riskType: RiskType!
"All Markets within this Protocol"
markets: [Markets!]! @derivedFrom(field: "protocol")
"All Accounts that have used this Protocol"
accounts: [AccountInProtocol!]! @derivedFrom(field: "protocol")
"Accumulated deposited amount in USD. Conversion into USD done at event time"
lifetimeDepositedUSD: BigInt!
"Accumulated withdrawn amount in USD. Conversion into USD done at event time"
lifetimeWithdrawnUSD: BigInt!
"Accumulated borrowed amount in USD. Conversion into USD done at event time"
lifetimeBorrowedUSD: BigDecimal
"Accumulated repaid amount in USD. Conversion into USD done at event time"
lifetimeRepaidUSD: BigDecimal!
"Accumulated liquidated amount in USD. Conversion into USD done at event time"
lifetimeLiquidatedUSD: BigDecimal!
"The number of time Deposits have occurred"
depositCount: BigInt!
"The number of time Withdraws have occurred"
withdrawCount: BigInt!
"The number of time Borrows have occurred"
borrowCount: BigInt!
"The number of time Repays have occurred"
repayCount: BigInt!
"The number of time Liquidations have occurred"
liquidatedCount: BigInt!
"All Deposit events"
deposits: [Deposit!]! @derivedFrom(field: "protocol")
"All Withdraw events"
withdraws: [Withdraw!]! @derivedFrom(field: "protocol")
"All Borrow events"
borrows: [Borrow!]! @derivedFrom(field: "protocol")
"All Repay events"
repays: [Repay!]! @derivedFrom(field: "protocol")
"All Liquidation events"
liquidations: [Liquidate!]! @derivedFrom(field: "protocol")
}
"""
An asset used in a lending protocol
"""
type Asset @entity {
"Contract address"
id: ID!
"Token symbol"
symbol: String!
}
"""
A single Market within a Protocol. A Market can be a single pool where all assets are pooled together. It can also be a combination of Collateralized Debt Positions (CDPs). The CDPs are aggregated together to represent a uniform Market. A Market can only contain 1 depositAsset and 1 borrowAsset.
"""
type Market @entity {
"Contract address"
id: ID!
"Name of the Market"
name: String!
"The Protocol the Market is a part of"
protocol: Protocol!
"All Accounts that have used this Market"
accounts: [AccountInMarket!]! @derivedFrom(field: "market")
"Asset deposited in the market as collateral"
depositAsset: Asset!
"Asset borrowed. CDP style is native asset - i.e. DAI. Aave style borrowed asset == deposit Asset"
borrowAsset: Asset!
"Current deposited amount in depositAsset"
deposited: BigDecimal!
"Current borrowed amount in borrowAsset"
borrowed: BigDecimal!
"The current price of the depositAsset in USD"
depositPriceUSD: BigDecimal
"The current price of the borrowAsset in USD"
borrowPriceUSD: BigDecimal
"Accumulated deposited amount in depositAsset"
lifetimeDeposited: BigInt!
"Accumulated deposited amount in USD. Conversion into USD done at event time"
lifetimeDepositedUSD: BigInt!
"Accumulated withdrawn amount in depositAsset"
lifetimeWithdrawn: BigInt!
"Accumulated withdrawn amount in USD. Conversion into USD done at event time"
lifetimeWithdrawnUSD: BigInt!
"Accumulated borrowed amount in borrowAsset"
lifetimeBorrowed: BigInt!
"Accumulated borrowed amount in USD. Conversion into USD done at event time"
lifetimeBorrowedUSD: BigDecimal
"Accumulated repaid amount in borrowAsset"
lifetimeRepaid: BigDecimal
"Accumulated repaid amount in USD. Conversion into USD done at event time"
lifetimeRepaidUSD: BigDecimal!
"Accumulated liquidated amount in depositAsset"
lifetimeLiquidated: BigDecimal!
"Accumulated liquidated amount in USD. Conversion into USD done at event time"
lifetimeLiquidatedUSD: BigDecimal!
"The number of times Deposits have occurred"
depositCount: BigInt!
"The number of times Withdraws have occurred"
withdrawCount: BigInt!
"The number of times Borrows have occurred"
borrowCount: BigInt!
"The number of times Repays have occurred"
repayCount: BigInt!
"The number of times Liquidations have occurred"
liquidatedCount: BigInt!
"All Deposit events"
deposits: [Deposit!]! @derivedFrom(field: "market")
"All Withdraw events"
withdraws: [Withdraw!]! @derivedFrom(field: "market")
"All Borrow events"
borrows: [Borrow!]! @derivedFrom(field: "market")
"All Repay events"
repays: [Repay!]! @derivedFrom(field: "market")
"All Liquidation events"
liquidations: [Liquidate!]! @derivedFrom(field: "market")
}
"""
An account that has interacted with at least 1 lending protocol tracked in the Subgraph
"""
type Account @entity {
"Contract address"
id: ID!
"All Protocols this account is in"
protocols: [AccountInProtocol!]! @derivedFrom(field: "account")
"All Markets this Account is in"
markets: [AccountInMarket!]! @derivedFrom(field: "account")
"Accumulated deposited amount in USD in all Protocols. Conversion into USD done at event time"
lifetimeDepositedUSD: BigInt!
"Accumulated withdrawn amount in USD in all Protocols. Conversion into USD done at event time"
lifetimeWithdrawnUSD: BigInt!
"Accumulated borrowed amount in USD in all Protocols. Conversion into USD done at event time"
lifetimeBorrowedUSD: BigDecimal
"Accumulated repaid amount in USD in all Protocols. Conversion into USD done at event time"
lifetimeRepaidUSD: BigDecimal!
"Accumulated liquidated amount in USD in all Protocols. Conversion into USD done at event time"
lifetimeLiquidatedUSD: BigDecimal!
"Accumulated interest earned on lending in USD in all Protocols. Conversion into USD done at event time"
lifetimeInterestEarnedUSD: BigDecimal!
"Accumulated interest paid on borrowing in USD in all Protocols. Conversion into USD done at event time"
lifetimeInterestPaidUSD: BigDecimal!
"The number of times the Account has deposited into any market"
depositCount: BigInt!
"The number of times the Account has withdrawn into any market"
withdrawCount: BigInt!
"The number of times the Account has borrowed into any market"
borrowCount: BigInt!
"The number of times the Account has repaid into any market"
repayCount: BigInt!
"The number of times the Account has been liquidated in any market"
liquidatedCount: BigInt!
"All Deposit events"
deposits: [Deposit!]! @derivedFrom(field: "from")
"All Withdraw events"
withdraws: [Withdraw!]! @derivedFrom(field: "to")
"All Borrow events"
borrows: [Borrow!]! @derivedFrom(field: "to")
"All Repay events"
repays: [Repay!]! @derivedFrom(field: "from")
"All Liquidation events"
liquidations: [Liquidate!]! @derivedFrom(field: "from")
}
"""
History of an Account within a single Protocol
"""
type AccountInProtocol @entity {
"Account ID concatenated with Protocol ID"
id: ID!
"Protocol the Account is active in"
protocol: Protocol!
"Account which has participated in the Protocol"
account: Account!
"Accumulated deposited amount in USD. Conversion into USD done at event time"
lifetimeDepositedUSD: BigInt!
"Accumulated withdrawn amount in USD. Conversion into USD done at event time"
lifetimeWithdrawnUSD: BigInt!
"Accumulated borrowed amount in USD. Conversion into USD done at event time"
lifetimeBorrowedUSD: BigDecimal
"Accumulated repaid amount in USD. Conversion into USD done at event time"
lifetimeRepaidUSD: BigDecimal!
"Accumulated liquidated amount in USD. Conversion into USD done at event time"
lifetimeLiquidatedUSD: BigDecimal!
"The number of time Deposits have occurred in all Protocols by this Account"
depositCount: BigInt!
"The number of time Withdraws have occurred in all Protocols by this Account"
withdrawCount: BigInt!
"The number of time Borrows have occurred in all Protocols by this Account"
borrowCount: BigInt!
"The number of time Repays have occurred in all Protocols by this Account"
repayCount: BigInt!
"The number of time Liquidations have occurred in all Protocols by this Account"
liquidatedCount: BigInt!
}
"""
Actions and history of an Account within a single Market
"""
type AccountInMarket @entity {
"Account ID concatenated with Market ID"
id: ID!
"Market the account is involved with"
market: Market!
"Account involved in this market"
account: Account!
"Current deposit amount in depositAsset"
deposited: BigDecimal!
"Current borrow amount in borrowAsset"
borrowed: BigDecimal!
"Accumulated deposited amount in depositAsset"
lifetimeDeposited: BigInt!
"Accumulated deposited amount in USD. Conversion into USD done at event time"
lifetimeDepositedUSD: BigInt!
"Accumulated withdrawn amount in depositAsset"
lifetimeWithdrawn: BigInt!
"Accumulated withdrawn amount in USD. Conversion into USD done at event time"
lifetimeWithdrawnUSD: BigInt!
"Accumulated borrowed amount in borrowAsset"
lifetimeBorrowed: BigInt!
"Accumulated borrowed amount in USD. Conversion into USD done at event time"
lifetimeBorrowedUSD: BigDecimal
"Accumulated repaid amount in borrowAsset"
lifetimeRepaid: BigDecimal
"Accumulated repaid amount in USD. Conversion into USD done at event time"
lifetimeRepaidUSD: BigDecimal!
"Accumulated liquidated amount in depositAsset"
lifetimeLiquidated: BigDecimal!
"Accumulated liquidated amount in USD. Conversion into USD done at event time"
lifetimeLiquidatedUSD: BigDecimal!
"The number of times Deposits have occurred in this Market by this Account"
depositCount: BigInt!
"The number of times Withdraws have occurred in this Market by this Account"
withdrawCount: BigInt!
"The number of times Borrows have occurred in this Market by this Account"
borrowCount: BigInt!
"The number of times Repays have occurred in this Market by this Account"
repayCount: BigInt!
"The number of times Liquidations have occurred in this Market by this Account"
liquidatedCount: BigInt!
}
"""
An event is a general action that occurs in a Lending Protocol
"""
interface Event {
"Transaction hash concatenated with log index"
id: ID!
"Asset transferred"
asset: Asset!
"Amount of Tokens transferred"
amount: BigDecimal!
"Amount transferred in USD. Conversion into USD done at event time"
amountUSD: BigDecimal
"Account that received tokens"
to: Account!
"Account that sent tokens"
from: Account!
"Block number"
blockNumber: Int!
"Block timestamp"
blockTime: Int!
"The Protocol the event originated from"
protocol: Protocol!
"The Market within a Protocol the event originated from"
market: Market!
}
"""
Deposit collateral
"""
type Deposit implements Event @entity {
"Transaction hash concatenated with log index"
id: ID!
"Asset deposited"
asset: Asset!
"Amount of tokens deposited into the Protocol"
amount: BigDecimal!
"Amount deposited in USD. Conversion into USD done at event time"
amountUSD: BigDecimal
"Protocol contract that received tokens"
to: Account!
"Account that deposited tokens"
from: Account!
"Block number"
blockNumber: Int!
"Block timestamp"
blockTime: Int!
"The Protocol the event originated from"
protocol: Protocol!
"The Market within a Protocol that the event originated from"
market: Market!
}
"""
Withdraw collateral
"""
type Withdraw implements Event @entity {
"Transaction hash concatenated with log index"
id: ID!
"Asset withdrawn"
asset: Asset!
"Amount of tokens being withdrawn from the Protocol"
amount: BigDecimal!
"Amount withdrawn in USD. Conversion into USD done at event time"
amountUSD: BigDecimal
"Account that withdrew tokens"
to: Account!
"Protocol contract that sent tokens"
from: Account!
"Block number"
blockNumber: Int!
"Block timestamp"
blockTime: Int!
"The Protocol the event originated from"
protocol: Protocol!
"The Market within a Protocol the event originated from"
market: Market!
}
"""
Borrow an asset
"""
type Borrow implements Event @entity {
"Transaction hash concatenated with log index"
id: ID!
"Asset borrowed"
asset: Asset!
"Amount of tokens borrowed from the Protocol"
amount: BigDecimal!
"Amount borrowed in USD. Conversion into USD done at event time"
amountUSD: BigDecimal
"Account that borrowed tokens"
to: Account!
"Protocol contract that sent borrowed tokens"
from: Account!
"Block number"
blockNumber: Int!
"Block timestamp"
blockTime: Int!
"The Protocol the event originated from"
protocol: Protocol!
"The Market within a Protocol the event originated from"
market: Market!
}
"""
Repay a borrowed asset
"""
type Repay implements Event @entity {
"Transaction hash concatenated with log index"
id: ID!
"Asset repaid"
asset: Asset!
"Amount of tokens repaid to the Protocol"
amount: BigDecimal!
"Amount repaid in USD. Conversion into USD done at event time"
amountUSD: BigDecimal
"Protocol contract that received repayment"
to: Account!
"Account that owed the asset"
from: Account!
"Block number"
blockNumber: Int!
"Block timestamp"
blockTime: Int!
"The Protocol the event originated from"
protocol: Protocol!
"The Market within a Protocol the event originated from"
market: Market!
"Payer of the debt. Payer is not always the same as borrower. i.e. a liquidation"
payer: Account!
}
"""
Liquidate an Account that went below the safe borrowing threshold
"""
type Liquidate implements Event @entity {
"Transaction hash concatenated with log index"
id: ID!
"Asset repaid"
asset: Asset!
"Amount of tokens repaid to the Protocol"
amount: BigDecimal!
"Amount repaid in USD. Conversion into USD done at event time"
amountUSD: BigDecimal
"Protocol contract that received repayment"
to: Account!
"Account that owed the asset and is being liquidated"
from: Account!
"Block number"
blockNumber: Int!
"Block timestamp"
blockTime: Int!
"The Protocol the event originated from"
protocol: Protocol!
"The market within a Protocol the event originated from"
market: Market!
"Liquidator that paid the debt"
liquidator: Account!
"Amount of collateral asset received by the Liquidator"
received: BigDecimal!
"Profit of the liquidator based on the Protocol liquidation fee"
profit: BigDecimal!
}
p.s. Shout out to @juanmardefago, who I had a conversation with a few weeks ago and he brought up the idea of a standard schema to me!
)