mev-beta/docs/spec/DATABASE_SCHEMA.md

Market Manager Database Schema

Overview

This document describes the database schema for persisting market data collected by the Market Manager. The schema is designed to support efficient storage and retrieval of market data with versioning between sequencer and on-chain data.

Tables

1. Markets Table

Stores the core market information.

CREATE TABLE markets (
    key VARCHAR(66) PRIMARY KEY,           -- keccak256 hash of market identifiers
    factory_address VARCHAR(42) NOT NULL,  -- DEX factory contract address
    pool_address VARCHAR(42) NOT NULL,     -- Pool contract address
    token0_address VARCHAR(42) NOT NULL,   -- First token in pair
    token1_address VARCHAR(42) NOT NULL,   -- Second token in pair
    fee INTEGER NOT NULL,                  -- Pool fee in basis points
    ticker VARCHAR(50) NOT NULL,           -- Formatted as <symbol>_<symbol>
    raw_ticker VARCHAR(90) NOT NULL,       -- Formatted as <token0>_<token1>
    protocol VARCHAR(20) NOT NULL,         -- DEX protocol (UniswapV2, UniswapV3, etc.)
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

2. MarketData Table

Stores price and liquidity data for markets with versioning support.

CREATE TABLE market_data (
    id SERIAL PRIMARY KEY,
    market_key VARCHAR(66) NOT NULL REFERENCES markets(key) ON DELETE CASCADE,
    price NUMERIC NOT NULL,                -- Current price of token1/token0
    liquidity NUMERIC NOT NULL,            -- Current liquidity in the pool
    sqrt_price_x96 NUMERIC,                -- sqrtPriceX96 from Uniswap V3
    tick INTEGER,                          -- Current tick from Uniswap V3
    status VARCHAR(20) NOT NULL,           -- Status (possible, confirmed, stale, invalid)
    timestamp BIGINT NOT NULL,             -- Last update timestamp
    block_number BIGINT NOT NULL,          -- Block number of last update
    tx_hash VARCHAR(66) NOT NULL,          -- Transaction hash of last update
    source VARCHAR(10) NOT NULL,           -- Data source (sequencer, onchain)
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_market_key_timestamp (market_key, timestamp),
    INDEX idx_status (status),
    INDEX idx_block_number (block_number)
);

3. ArbitrageOpportunities Table

Stores detected arbitrage opportunities for analysis and backtesting.

CREATE TABLE arbitrage_opportunities (
    id SERIAL PRIMARY KEY,
    market_key_1 VARCHAR(66) NOT NULL REFERENCES markets(key) ON DELETE CASCADE,
    market_key_2 VARCHAR(66) NOT NULL REFERENCES markets(key) ON DELETE CASCADE,
    path TEXT NOT NULL,                    -- JSON array of token addresses
    profit NUMERIC NOT NULL,               -- Estimated profit in wei
    gas_estimate NUMERIC NOT NULL,         -- Estimated gas cost in wei
    roi DECIMAL(10, 6) NOT NULL,           -- Return on investment percentage
    status VARCHAR(20) NOT NULL,           -- Status (detected, executed, failed)
    detection_timestamp BIGINT NOT NULL,   -- When opportunity was detected
    execution_timestamp BIGINT,            -- When opportunity was executed
    tx_hash VARCHAR(66),                   -- Transaction hash if executed
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_detection_timestamp (detection_timestamp),
    INDEX idx_status (status),
    INDEX idx_market_keys (market_key_1, market_key_2)
);

4. MarketEvents Table

Stores parsed events for markets (swaps, liquidity changes).

CREATE TABLE market_events (
    id SERIAL PRIMARY KEY,
    market_key VARCHAR(66) NOT NULL REFERENCES markets(key) ON DELETE CASCADE,
    event_type VARCHAR(20) NOT NULL,       -- swap, mint, burn
    amount0 NUMERIC,                       -- Amount of token0
    amount1 NUMERIC,                       -- Amount of token1
    transaction_hash VARCHAR(66) NOT NULL,
    block_number BIGINT NOT NULL,
    log_index INTEGER NOT NULL,
    timestamp BIGINT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_market_key_timestamp (market_key, timestamp),
    INDEX idx_event_type (event_type),
    INDEX idx_block_number (block_number)
);

Indexes

Performance Indexes

1. markets.raw_ticker: For fast lookup by raw ticker
2. market_data.timestamp: For time-based queries
3. market_data.status: For filtering by status
4. arbitrage_opportunities.detection_timestamp: For time-based analysis
5. market_events.timestamp: For event-based analysis

Foreign Key Constraints

All tables maintain referential integrity through foreign key constraints to ensure data consistency.

Data Versioning

The schema supports data versioning through:

1. Source field in MarketData: Distinguishes between sequencer and on-chain data
2. Timestamp tracking: Records when each data version was created
3. Status field: Tracks verification status of market data

Serialization Format

JSON Schema for Market Data

{
  "key": "string",
  "factory": "string",
  "poolAddress": "string",
  "token0": "string",
  "token1": "string",
  "fee": "number",
  "ticker": "string",
  "rawTicker": "string",
  "protocol": "string",
  "price": "string",
  "liquidity": "string",
  "sqrtPriceX96": "string",
  "tick": "number",
  "status": "string",
  "timestamp": "number",
  "blockNumber": "number",
  "txHash": "string"
}

JSON Schema for Arbitrage Opportunities

{
  "marketKey1": "string",
  "marketKey2": "string",
  "path": ["string"],
  "profit": "string",
  "gasEstimate": "string",
  "roi": "number",
  "status": "string",
  "detectionTimestamp": "number",
  "executionTimestamp": "number",
  "txHash": "string"
}

Data Retention Policy

1. MarketData: Keep latest 30 days of data
2. ArbitrageOpportunities: Keep latest 90 days of data
3. MarketEvents: Keep latest 7 days of data
4. Markets: Keep indefinitely (relatively small data size)

Performance Considerations

1. Partitioning: Consider partitioning large tables by date for better query performance
2. Caching: Implement Redis/Memcached for frequently accessed market data
3. Batch Operations: Use batch inserts for high-volume data ingestion
4. Connection Pooling: Implement database connection pooling for efficient resource usage