Skip to content

Database Models Documentation

Overview

The DSTA project uses Django ORM with PostgreSQL to store cryptocurrency market data. This document describes the data models and database schema.

Models

Candlestick Model

Stores OHLCV (Open, High, Low, Close, Volume) candlestick data for cryptocurrency trading pairs.

Table Name: candlesticks

Fields: - id: Auto-generated primary key - exchange: Exchange name (binance, huobi, gateio) - symbol: Trading pair symbol (e.g., 'BTCUSDT') - interval: Timeframe (10s, 1m, 2m, 5m, 15m, 30m, 1h, 4h, 1d, 7d) - timestamp: Opening time of the candlestick - open_price: Opening price (Decimal, 20 digits, 8 decimal places) - high_price: Highest price during interval - low_price: Lowest price during interval - close_price: Closing price - volume: Trading volume in base currency (Decimal, 30 digits, 8 decimal places) - quote_volume: Trading volume in quote currency (optional) - trades_count: Number of trades during interval (optional) - created_at: Record creation timestamp - updated_at: Record last update timestamp

Indexes: 1. candle_lookup_idx: Composite index on (exchange, symbol, interval, timestamp) 2. symbol_time_idx: Index on (symbol, timestamp) 3. exchange_time_idx: Index on (exchange, timestamp)

Constraints: - Unique constraint on (exchange, symbol, interval, timestamp) to prevent duplicates

Usage Example: 

from api.models import Candlestick
from decimal import Decimal
from datetime import datetime, timezone

# Create a candlestick
candle = Candlestick.objects.create(
    exchange='binance',
    symbol='BTCUSDT',
    interval='1h',
    timestamp=datetime(2023, 1, 1, 12, 0, 0, tzinfo=timezone.utc),
    open_price=Decimal('42000.50'),
    high_price=Decimal('42500.75'),
    low_price=Decimal('41800.25'),
    close_price=Decimal('42300.00'),
    volume=Decimal('123.456'),
)

# Query candlesticks
btc_candles = Candlestick.objects.filter(
    exchange='binance',
    symbol='BTCUSDT',
    interval='1h',
    timestamp__gte=datetime(2023, 1, 1, tzinfo=timezone.utc)
).order_by('timestamp')

Trade Model

Stores individual trade execution records from exchanges for granular market analysis.

Table Name: trades

Fields: - id: Auto-generated primary key - exchange: Exchange name (binance, huobi, gateio) - symbol: Trading pair symbol - trade_id: Unique trade ID from exchange - timestamp: Trade execution time - price: Trade price (Decimal, 20 digits, 8 decimal places) - quantity: Trade quantity in base currency (Decimal, 30 digits, 8 decimal places) - quote_quantity: Trade quantity in quote currency (optional) - is_buyer_maker: True if buyer was maker, False if seller was maker (optional) - created_at: Record creation timestamp

Indexes: 1. trade_lookup_idx: Composite index on (exchange, symbol, timestamp) 2. trade_symbol_time_idx: Index on (symbol, timestamp)

Constraints: - Unique constraint on (exchange, symbol, trade_id) to prevent duplicates

Usage Example: 

from api.models import Trade
from decimal import Decimal
from datetime import datetime, timezone

# Create a trade
trade = Trade.objects.create(
    exchange='binance',
    symbol='ETHUSDT',
    trade_id='12345678',
    timestamp=datetime(2023, 1, 1, 12, 30, 0, tzinfo=timezone.utc),
    price=Decimal('3000.50'),
    quantity=Decimal('2.5'),
    is_buyer_maker=True,
)

# Query trades
recent_trades = Trade.objects.filter(
    exchange='binance',
    symbol='ETHUSDT',
    timestamp__gte=datetime.now(timezone.utc) - timedelta(hours=1)
)

OrderBook Model

Stores order book snapshot data for support/resistance level analysis.

Table Name: order_books

Fields: - id: Auto-generated primary key - exchange: Exchange name (binance, huobi, gateio) - symbol: Trading pair symbol - timestamp: Snapshot timestamp - bids: JSON array of [price, quantity] bid levels - asks: JSON array of [price, quantity] ask levels - last_update_id: Last update ID from exchange (optional) - created_at: Record creation timestamp

Indexes: 1. orderbook_lookup_idx: Composite index on (exchange, symbol, timestamp)

Usage Example: 

from api.models import OrderBook
from datetime import datetime, timezone

# Create an order book snapshot
orderbook = OrderBook.objects.create(
    exchange='binance',
    symbol='BTCUSDT',
    timestamp=datetime(2023, 1, 1, 12, 0, 0, tzinfo=timezone.utc),
    bids=[
        ['42000.00', '1.5'],
        ['41999.00', '2.3'],
        ['41998.00', '0.8'],
    ],
    asks=[
        ['42001.00', '1.2'],
        ['42002.00', '2.1'],
        ['42003.00', '1.7'],
    ],
    last_update_id=123456789,
)

# Query order books
latest_orderbook = OrderBook.objects.filter(
    exchange='binance',
    symbol='BTCUSDT'
).order_by('-timestamp').first()

MarketMetric Model

Stores market-wide metrics like BTC dominance, total market cap, and Fear & Greed Index.

Table Name: market_metrics

Fields: - id: Auto-generated primary key - metric_type: Type of metric (btc_dominance, total_market_cap, fear_greed_index, other) - timestamp: Metric timestamp - value: Metric value (Decimal, 20 digits, 8 decimal places) - metadata: Additional metadata as JSON (optional) - created_at: Record creation timestamp

Indexes: 1. metric_lookup_idx: Index on (metric_type, timestamp)

Constraints: - Unique constraint on (metric_type, timestamp) to prevent duplicates

Usage Example: 

from api.models import MarketMetric
from decimal import Decimal
from datetime import datetime, timezone

# Create BTC dominance metric
btc_dom = MarketMetric.objects.create(
    metric_type='btc_dominance',
    timestamp=datetime(2023, 1, 1, 12, 0, 0, tzinfo=timezone.utc),
    value=Decimal('45.67'),
    metadata={'source': 'coinmarketcap'},
)

# Query metrics
fear_greed_history = MarketMetric.objects.filter(
    metric_type='fear_greed_index',
    timestamp__gte=datetime(2023, 1, 1, tzinfo=timezone.utc)
).order_by('timestamp')

Database Migrations

The models have been defined with corresponding Django migrations in src/api/migrations/0001_initial.py.

To apply migrations: 

cd src
python manage.py migrate api

To create a new migration after model changes: 

cd src
python manage.py makemigrations api

Performance Considerations

  1. Time-Series Queries: The models are optimized for time-series queries with appropriate composite indexes on (exchange, symbol, timestamp).

  2. Data Volume: Expected to handle millions of records. Consider:

  3. Regular data archival for old records
  4. Partitioning tables by date for very large datasets

  5. Using materialized views for common aggregations

  6. Query Optimization:

  7. Always filter by exchange and symbol when querying specific data
  8. Use timestamp ranges to limit result sets

  9. Leverage the composite indexes for optimal performance

  10. Data Integrity: Unique constraints prevent duplicate data from being inserted, ensuring data quality.

Future Enhancements

  1. Time-Series Optimizations: Consider using PostgreSQL's TimescaleDB extension for better time-series performance
  2. Partitioning: Implement table partitioning by date for candlesticks and trades
  3. Compression: Enable PostgreSQL compression for older data
  4. Materialized Views: Create materialized views for common aggregations (daily summaries, etc.)