Historical Transfers Backend

[rhlsthrm]

Purpose

We need an internally hosted database that we can use to drive different tools such as explorers, SDKs, UIs. The database needs to persistent the data forever so that historical data can easily be accessed, aggregated, and calculated.

Centralization

Yes, this adds a degree of centralization. For this reason, it's critical that the data stored is simply a reflection of what's onchain and acts as a supplement to the onchain data for better user experience. Also this will not be relied on by systems such as the router, which absolutely need to use real-time chain data and need to be fully decentralized.

Populating the Database

The database should be populated by subgraph queries. We will need a pipeline where a cron task periodically reads the subgraph and writes into the database.

Database Type

I propose Postgres as the database of choice since it has the most flexibility and support. However there are other implementations these days that might be better/faster/more cloud native.

Schema

The database should have the following tables with the following schema:

Transfer

• TransferId
• Status (XCalled, Executed, Reconciled)
• Initiator (who called XCall)
• Origin Domain
• Origin Asset
• Destination Domain
• Destination Asset
• Sent Amount
• Received Amount
• Recipient (To?)
• Router
• Calldata
• Execute Relayer (who called Execute)
• XCall Transaction Hash
• Execute Transaction Hash
• Reconcile Transaction Hash
• Timestamps

TODO: Do we need any more tables/relations? User/Router could be relational, but I'm not sure what other data we need in those tables besides addresses.

Use Cases

The backend should support the following types of queries:

• Transfers by user.
• Transfers by status.
• Transfers by domain.
• Total volume by user.
• Total volume by domain per week.
• Inflows/outflows by domain.

API

The backend should expose this data through a REST/GraphQL API. We can design the API around what data the explorers require.

[LayneHaber]

nit: this should be an Idea if it doesn't have test cases and task break outs

also is this just a stop-gap measure until composable subgraphs are in play?

[rhlsthrm]

also is this just a stop-gap measure until composable subgraphs are in play?

I think even with composable subgraphs if we want to do better analytics and have good performance from the front ends, we need our own backend. For example, doing aggregate queries such as adding up all volume for a user/chain. I think we can deeply increase the performance by having a SQL backend.

[pjenviri]

The current data displayed on connextscan:

• dayMetric: volume, transaction by (day & chain & asset)

Below are some new metrics that maybe good to have:
(just a thought. totally depends on the team to finalize)

• for bridge user

  • volume, transaction of each user by (granularity & chain & asset)
  • top bridge route (from, to chain) by volume & transaction
  • top bridge asset by volume & transaction

• for router

  • volume, transaction of each router by (granularity & chain & asset)
  • transaction fee received by (chain & asset)
  • avg/min/max transaction bridge time of each router by (granularity & chain & asset)

• for overall/general users

  • new user by granularity
  • new router by granularity
  • avg/min/max volume of each (chain & asset) by granularity
  • avg/min/max transaction fee of each (chain & asset) by granularity
  • avg/min/max transaction bridge time of each (chain & asset) by granularity
  • top bridge route (from, to chain, asset) by volume & transaction

• leaderboard

  • user
    • weight factors (volume + $next hold amount???) of each chain by granularity
  • router
    • weight factors (avg bridge time + volume + asset balance + number of supported chain + $next hold amount???) of each chain by granularity

[rhlsthrm]

I think one thing really cool to do that will be easier with this backend is to show inflows and outflows between chains. We can add some APIs around that as well. Would you guys prefer REST API or GraphQL?

[pjenviri]

I think one thing really cool to do that will be easier with this backend is to show inflows and outflows between chains. We can add some APIs around that as well. Would you guys prefer REST API or GraphQL?

We prefer REST API, but GraphQL is ok for us too.