# SubQL Docs

[TOC]

## Introduction

The indexing and querying of centrifuge chain data is based on the [SubQuery](https://subquery.network) framework. SubQuery is an open source blockchain data indexer for developers that provides fast, flexible, reliable, and decentralised APIs to power multi-chain apps.

Chain data can be queried with a GraphQL api. To learn the basics of GraphQL, you can check out this [primer guide](https://medium.com/graphprotocol/graphql-will-power-the-decentralized-web-d7443a69c69a).

## Architecture and Data Model
The latest version of the GraphQL data model can be found on GitHub under the [centrifuge-subql repository](https://github.com/embrio-tech/centrifuge-subql/blob/main/schema.graphql).

The indexer service fetches and stores current and historical data for the following entities:
* Pools
* Tranches
* Epochs
* Loans
* Investor Transactions
* Borrower Transactions
* Outstanding Orders
* Accounts (with related currencies and tranche-tokens balances)

Depending on the caracteristics of the underlying data (current or historical), properties of entities are stored in one or multiple tables, according to the principles defined in figure 1 below.

<center><strong>Figure 1:</strong> entity / snapshot architecture for historical data</center><br />

Let's see how the concept applies to the *Pool* entity, and how the data is split across different tables.

Pool / [entity]
: Stores all the data about the underlying entity. This includes the name of the pool, references to metadata, creation timestamps, the underlying currency. It further serves as an accumulator to keep track of KPIs and values evolving over time. Stores only the most actual values available. In the specific case this includes: portfolio valuation, active loans, total number of loans, inflows, outflows. Such states are used at regular intervals to perform snapshots and provide historical data. Property names ending with an capital 'R' are reset to 0 right after a snapshot is created.

PoolSnapshot / [entity]Snapshot
: Provides historical data about the evolution of `Pool` over time. The dynamics of snapshottings are shown in Figure 2.

<center><strong>Figure 2:</strong> entities snapshotting</center><br />

Relationsips can be used in GraphQL queries to access and group data. As an example it is possible to list all the Tranches related to a Pool in a single Pool query. All relationships between the entities are listed in Figure 3 below.

erDiagram
    Account ||--o{ TrancheBalance: ""
    Account ||--o{ CurrencyBalance: ""

    Loan ||..|| LoanSnapshot: "onNewPeriod"

    Pool ||..|| PoolSnapshot: "onNewPeriod"

    Tranche }o--|| Pool: ""
    Tranche ||..o{ TrancheSnapshot: "onNewPeriod"

    Epoch }|--|| Pool: ""
    Epoch }o--|| EpochState: ""
    EpochState ||--|| Tranche: ""

    InvestorTransaction }o--|| Account: ""
    InvestorTransaction ||--|| Pool: ""
    InvestorTransaction ||--|| Tranche: ""
    InvestorTransaction ||--|| Epoch: ""

    OutstandingOrder }o--|| Account: ""
    OutstandingOrder ||--|| Pool: ""
    OutstandingOrder ||--|| Tranche: ""
    OutstandingOrder ||--|| Epoch: ""

    BorrowerTransaction }o--|| Account: ""
    BorrowerTransaction ||--|| Epoch: ""
    BorrowerTransaction ||--|| Loan: ""

    Currency ||--|{ Pool: ""

<center><strong>Figure 3:</strong> entities relationships</center><br />

## Querying Data from SubQL

You can follow the official GraphQL guide [here](https://graphql.org/learn/) to learn more about GraphQL, how it works, and how to use it:

* There are libraries to help you implement GraphQL in your application.
* For an in-depth learning experience with practical tutorials, see How to GraphQL.
* Check out the free online course, Exploring GraphQL: A Query Language for APIs.

| Network | GraphQL Endpoint |
| -------- | -------- |
| Dev | https://api.subquery.network/sq/embrio-tech/centrifuge-subql |
| Altair | ... |
| Centrifuge | ... |

### Sample Queries
Queries can be tested in a dedicated [SubQL Sandbox](https://explorer.subquery.network/subquery/embrio-tech/centrifuge-subql). The [SubQL documentation](https://academy.subquery.network/run_publish/query.html) provides a some insights on how to perform queries in their subquery explorer.

Here some important hints and common pitfalls that can save you some time when working woth our data:

* Currencies and Token amounts are expressed in fixed decimal precision. As pools have different reference currencies, the amount this precision can vary. For this matter we reference the `Currency` entity in `Pools`, so that the correct amount of decimals can be queried together with each pool.
* Queries return a maximum of 100 entries per page by default. This can be increased to a maximum of 1000 entries per page in production environments. Sanbox environments are limited to 100 results.
* Entities ids are not necessary the same as on chain ids. Therefore, when querying an entity, always refer to the [GraphQL data model](https://github.com/embrio-tech/centrifuge-subql/blob/main/schema.graphql) to verify how the id is composed.

#### Get net portfolio valuation and active loans for all Pools

{
  pools {
    nodes {
      id
      currency {
        id
        decimals
      }
    portfolioValuation
    sumNumberOfActiveLoans
    }
  }
}

Hereafter you can get inspired with some sample queries.

#### Get balances and last investor transactions for an account

{
  account(id: "kALNreUp6oBmtfG87fe7MakWR8BnmQ4SmKjjfG27iVd3nuTue") {
    id
    outstandingOrders {
      nodes {
        investAmount
        redeemAmount
        trancheId
      }
    }
    investorTransactions (last: 2) {
      nodes {
        type
        currencyAmount
        tokenAmount
      }
    }
    currencyBalances {
      nodes {
        amount
      }
    }
    trancheBalances {
      nodes {
        trancheId
        sumInvestOrderedAmount
        sumInvestCollectedAmount
        sumInvestUncollectedAmount
      }
    }
  }
}

#### Get outstanding debt information for loans belongig to a pool

{
  pool(id: "2825130900") {
    id
    currency {
      id
      decimals
    }
    loans {
      nodes {
        id
        outstandingDebt
      }
    }
  }
}

#### Get historical token price and token supply evolution for a given tranche token

{
  trancheSnapshots(
    orderBy: TIMESTAMP_ASC
    filter: {
      trancheId: { equalTo: "1873519631-0xb05f8e95eaf6ffc940ab4b4fbcb6324b" }
    }
  ) {
    nodes {
      id
      timestamp
      tokenPrice
      tokenSupply
    }
  }
}

#### Get TVL for single pools or for the entire ecosystem
The TVL for each pool can be obtained with the following query:

{ pools { nodes { value } } }

The total for the entire CFG ecosystem is obtained by summing across all results.