Database Schema Configuration

Overview

The Uniswap V2 adapter uses a well-structured database schema to manage pool configurations, state, and user balances. This schema ensures data integrity and supports efficient querying.

GraphQL Schema Definition

type Token @entity {
  id: ID!
  address: String! @index
  decimals: Int!
  coingeckoId: String
}
 
type PoolConfig @entity {
  id: ID!
  token0: Token!
  token1: Token!
  lpToken: Token!
}
 
type PoolState @entity {
  id: ID!
  pool: PoolConfig! @index
  reserve0: BigInt!
  reserve1: BigInt!
  totalSupply: BigInt!
  lastBlock: Int!
  lastTsMs: BigInt!
  lastInterpolatedTs: BigInt
  updatedAt: DateTime!
}
 
type ActiveBalances @entity {
  id: ID!
  activeBalancesMap: JSON!
}
 
type PoolProcessState @entity {
  id: ID!
  pool: PoolConfig! @index
  lastInterpolatedTs: BigInt
}

Entity Relationships

erDiagram
    PoolConfig ||--|| Token : "token0"
    PoolConfig ||--|| Token : "token1"
    PoolConfig ||--|| Token : "lpToken"
    PoolState ||--|| PoolConfig : "pool"
    PoolProcessState ||--|| PoolConfig : "pool"
 
    PoolConfig {
        string id PK
        Token token0 FK
        Token token1 FK
        Token lpToken FK
    }
 
    Token {
        string id PK
        string address
        int decimals
        string coingeckoId
    }
 
    PoolState {
        string id PK
        PoolConfig pool FK
        bigint reserve0
        bigint reserve1
        bigint totalSupply
        int lastBlock
        bigint lastTsMs
        datetime updatedAt
    }
 
    PoolProcessState {
        string id PK
        PoolConfig pool FK
        bigint lastInterpolatedTs
    }
 
    ActiveBalances {
        string id PK
        json activeBalancesMap
    }

Entity Details

Token Entity

Purpose: Represents any ERC-20 token (underlying tokens or LP tokens)

interface Token {
  id: string; // Unique identifier: "{address}-{type}"
  address: string; // Contract address (indexed for fast lookups)
  decimals: number; // Token decimal places (6 for USDC, 18 for WETH)
  coingeckoId?: string; // CoinGecko ID for price fetching (null for LP tokens)
}

Examples:

// USDC Token
{
  id: "0xA0b86a33E6441E8bD80E4F3a0124E53461a0f4b5-token0",
  address: "0xA0b86a33E6441E8bD80E4F3a0124E53461a0f4b5",
  decimals: 6,
  coingeckoId: "usd-coin"
}
 
// LP Token (no CoinGecko ID)
{
  id: "0xB4e16d0168e52d35CaCD2c6185b44281Ec28C9Dc-lp",
  address: "0xB4e16d0168e52d35CaCD2c6185b44281Ec28C9Dc",
  decimals: 18,
  coingeckoId: null
}

PoolConfig Entity

Purpose: Immutable configuration linking a pool to its three tokens

interface PoolConfig {
  id: string; // "{poolAddress}-config"
  token0: Token; // First underlying token (lower address)
  token1: Token; // Second underlying token (higher address)
  lpToken: Token; // LP token representing pool shares
}

Relationships:

One PoolConfig has exactly 3 Tokens (token0, token1, lpToken)
Created once during pool initialization
Never modified after creation

PoolState Entity

Purpose: Current blockchain state of the pool (updated frequently)

interface PoolState {
  id: string; // "{poolAddress}-state"
  pool: PoolConfig; // Reference to pool configuration (indexed)
  reserve0: bigint; // Current token0 reserves
  reserve1: bigint; // Current token1 reserves
  totalSupply: bigint; // Current LP token total supply
  lastBlock: number; // Last processed block number
  lastTsMs: bigint; // Last processed timestamp (milliseconds)
  lastInterpolatedTs?: bigint; // Unused field (legacy)
  updatedAt: Date; // Database update timestamp
}

Update Pattern:

Updated every time LP token price is calculated
Synchronized with blockchain via updatePoolStateFromOnChain()
Critical for accurate pricing calculations

PoolProcessState Entity

Purpose: Tracks processing state for periodic operations

interface PoolProcessState {
  id: string; // "{poolAddress}-process-state"
  pool: PoolConfig; // Reference to pool configuration (indexed)
  lastInterpolatedTs?: bigint; // Last time balance flush was performed
}

Usage:

Controls timing for EXHAUSTED balance window creation
Ensures proper time boundaries across processor restarts
Updated during periodic balance flush operations

ActiveBalances Entity

Purpose: Serialized storage of in-memory user balance state

interface ActiveBalances {
  id: string; // "{poolAddress}-active-balances"
  activeBalancesMap: JSON; // Serialized Map<string, ActiveBalance>
}

JSON Structure:

{
  "0x742d35Cc1F54834567...": {
    "balance": "1500000000000000000",
    "updatedBlockTs": 1640995200,
    "updatedBlockHeight": 13916166
  },
  "0x8ba1f109551bD432...": {
    "balance": "50000000000000000000",
    "updatedBlockTs": 1640998800,
    "updatedBlockHeight": 13916200
  }
}

Database Operations

Initialization Flow

// 1. Load existing entities
const poolConfig = await ctx.store.findOne(PoolConfig, {
  where: { id: `${contractAddress}-config` },
  relations: { token0: true, token1: true, lpToken: true }
});
 
// 2. Create if missing
if (!poolConfig) {
  const newConfig = new PoolConfig({
    id: `${contractAddress}-config`,
    token0: new Token({...}),
    token1: new Token({...}),
    lpToken: new Token({...})
  });
}

State Updates

// Update pool state with fresh blockchain data
poolState.reserve0 = newReserve0;
poolState.reserve1 = newReserve1;
poolState.totalSupply = newTotalSupply;
poolState.updatedAt = new Date();
 
// Persist to database
await ctx.store.upsert(poolState);

Active Balance Persistence

// Convert Map to JSON for storage
const activeBalancesEntity = new ActiveBalances({
  id: `${contractAddress}-active-balances`,
  activeBalancesMap: mapToJson(activeBalancesMap),
});
 
await ctx.store.upsert(activeBalancesEntity);

Schema Design Principles

1. Separation of Concerns

Config: Immutable pool/token metadata
State: Mutable blockchain state
Process: Processing timing state
Balances: User balance snapshots

2. Efficient Indexing

@index on frequently queried fields (addresses, pool references)
Composite IDs for fast lookups
Minimal relations to reduce query complexity

3. Data Integrity

Required fields marked with !
Foreign key relationships ensure referential integrity
BigInt for precise financial amounts

4. Performance Optimization

JSON storage for dynamic balance maps
Separate entities to minimize update scope
Indexed pool references for fast filtering

This schema provides a robust foundation for tracking Uniswap V2 pool state, user balances, and processing metadata while maintaining high performance and data integrity.