Skip to main content

Database Schema & Data Flow

Overview​

The MyTradeX platform uses PostgreSQL as the primary database with a well-structured schema designed for high-performance trading operations. The database design follows normalization principles while optimizing for read-heavy operations typical in trading systems.

Database Design Philosophy​

Core Principles​

  • ACID Compliance: Full transactional support for trading operations
  • Performance Optimization: Strategic indexing and query optimization
  • Data Integrity: Foreign key constraints and validation rules
  • Audit Trail: Complete change tracking for regulatory compliance
  • Scalability: Partitioning strategies for large data volumes

Technology Stack​

  • Database: PostgreSQL 15+
  • Migration Tool: Liquibase
  • Connection Pooling: HikariCP
  • ORM: Spring Data JPA

Database Schema​

1. Core Entities​

Users Table​

Purpose: User authentication and profile management

CREATE TABLE users (
    id UUID PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    role VARCHAR(20) NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_role ON users(role);
CREATE INDEX idx_users_status ON users(status);

Field Descriptions:

  • id: Primary key (UUID for global uniqueness)
  • email: Unique identifier for login
  • password_hash: Bcrypt hashed password
  • name: User's display name
  • role: TRADER, ADMIN, BROKER
  • status: ACTIVE, SUSPENDED, INACTIVE

Orders Table​

Purpose: Trading order lifecycle management

CREATE TABLE orders (
    id UUID PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES users(id),
    symbol VARCHAR(20) NOT NULL,
    side VARCHAR(4) NOT NULL, -- BUY, SELL
    order_type VARCHAR(20) NOT NULL, -- MARKET, LIMIT, STOP, STOP_LIMIT
    quantity DECIMAL(19,8) NOT NULL,
    price DECIMAL(19,8),
    stop_price DECIMAL(19,8),
    time_in_force VARCHAR(10) NOT NULL, -- DAY, GTC, IOC, FOK
    status VARCHAR(20) NOT NULL, -- NEW, PARTIAL, FILLED, CANCELLED, REJECTED
    remaining_quantity DECIMAL(19,8) NOT NULL,
    filled_quantity DECIMAL(19,8) DEFAULT 0,
    average_fill_price DECIMAL(19,8),
    protocol_order_id VARCHAR(50),
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    
    -- Constraints
    CONSTRAINT chk_order_quantity CHECK (quantity > 0),
    CONSTRAINT chk_order_price CHECK (order_type != 'LIMIT' OR price IS NOT NULL),
    CONSTRAINT chk_order_stop_price CHECK (order_type != 'STOP_LIMIT' OR stop_price IS NOT NULL)
);

-- Indexes
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_symbol ON orders(symbol);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_protocol_id ON orders(protocol_order_id);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
CREATE INDEX idx_orders_symbol_status ON orders(symbol, status);

Order Lifecycle States:

  • NEW: Order created, awaiting execution
  • PARTIAL: Partially filled, remaining quantity
  • FILLED: Completely executed
  • CANCELLED: User cancelled the order
  • REJECTED: Order rejected by exchange/system

Trades Table​

Purpose: Completed trade records

CREATE TABLE trades (
    id UUID PRIMARY KEY,
    order_id UUID NOT NULL REFERENCES orders(id),
    symbol VARCHAR(20) NOT NULL,
    side VARCHAR(4) NOT NULL, -- BUY, SELL
    quantity DECIMAL(19,8) NOT NULL,
    price DECIMAL(19,8) NOT NULL,
    buyer_id UUID NOT NULL REFERENCES users(id),
    seller_id UUID NOT NULL REFERENCES users(id),
    settlement_status VARCHAR(20) NOT NULL, -- PENDING, SETTLED, FAILED
    protocol_trade_id VARCHAR(50),
    trade_time TIMESTAMP NOT NULL DEFAULT NOW(),
    settlement_date DATE,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    
    -- Constraints
    CONSTRAINT chk_trade_quantity CHECK (quantity > 0),
    CONSTRAINT chk_trade_price CHECK (price > 0),
    CONSTRAINT chk_trade_parties CHECK (buyer_id != seller_id)
);

-- Indexes
CREATE INDEX idx_trades_order_id ON trades(order_id);
CREATE INDEX idx_trades_symbol ON trades(symbol);
CREATE INDEX idx_trades_buyer_id ON trades(buyer_id);
CREATE INDEX idx_trades_seller_id ON trades(seller_id);
CREATE INDEX idx_trades_settlement_status ON trades(settlement_status);
CREATE INDEX idx_trades_trade_time ON trades(trade_time DESC);
CREATE INDEX idx_trades_symbol_time ON trades(symbol, trade_time DESC);

Positions Table​

Purpose: Portfolio positions and P&L tracking

CREATE TABLE positions (
    id UUID PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES users(id),
    symbol VARCHAR(20) NOT NULL,
    quantity DECIMAL(19,8) NOT NULL,
    average_cost DECIMAL(19,8) NOT NULL,
    current_price DECIMAL(19,8),
    unrealized_pnl DECIMAL(19,8) DEFAULT 0,
    realized_pnl DECIMAL(19,8) DEFAULT 0,
    last_updated TIMESTAMP NOT NULL DEFAULT NOW(),
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    
    -- Constraints
    CONSTRAINT uk_positions_user_symbol UNIQUE (user_id, symbol)
);

-- Indexes
CREATE INDEX idx_positions_user_id ON positions(user_id);
CREATE INDEX idx_positions_symbol ON positions(symbol);
CREATE INDEX idx_positions_quantity ON positions(quantity);
CREATE INDEX idx_positions_pnl ON positions(unrealized_pnl);

Market Data Table​

Purpose: Real-time and historical market data

CREATE TABLE market_data (
    id UUID PRIMARY KEY,
    symbol VARCHAR(20) NOT NULL,
    last_price DECIMAL(19,8) NOT NULL,
    bid_price DECIMAL(19,8),
    ask_price DECIMAL(19,8),
    bid_quantity DECIMAL(19,8),
    ask_quantity DECIMAL(19,8),
    volume BIGINT DEFAULT 0,
    high_24h DECIMAL(19,8),
    low_24h DECIMAL(19,8),
    change_24h DECIMAL(10,4),
    change_percent_24h DECIMAL(10,4),
    timestamp TIMESTAMP NOT NULL DEFAULT NOW(),
    
    -- Constraints
    CONSTRAINT uk_market_data_symbol UNIQUE (symbol)
);

-- Indexes
CREATE INDEX idx_market_data_symbol ON market_data(symbol);
CREATE INDEX idx_market_data_timestamp ON market_data(timestamp DESC);
CREATE INDEX idx_market_data_last_price ON market_data(last_price);

Audit Logs Table​

Purpose: System activity tracking for compliance

CREATE TABLE audit_logs (
    id UUID PRIMARY KEY,
    user_id UUID REFERENCES users(id),
    action VARCHAR(100) NOT NULL,
    resource_type VARCHAR(50) NOT NULL,
    resource_id VARCHAR(100),
    old_values JSONB,
    new_values JSONB,
    ip_address INET,
    user_agent TEXT,
    timestamp TIMESTAMP NOT NULL DEFAULT NOW(),
    
    -- Indexes for common query patterns
    CREATE INDEX idx_audit_logs_user_id ON audit_logs(user_id);
    CREATE INDEX idx_audit_logs_action ON audit_logs(action);
    CREATE INDEX idx_audit_logs_resource ON audit_logs(resource_type, resource_id);
    CREATE INDEX idx_audit_logs_timestamp ON audit_logs(timestamp DESC);
);

2. Supporting Tables​

Session Management​

CREATE TABLE user_sessions (
    id UUID PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES users(id),
    session_token VARCHAR(255) NOT NULL,
    refresh_token VARCHAR(255),
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    
    CONSTRAINT uk_sessions_token UNIQUE (session_token)
);

CREATE INDEX idx_sessions_user_id ON user_sessions(user_id);
CREATE INDEX idx_sessions_expires ON user_sessions(expires_at);

Order History (Partitioned)​

-- Orders partitioned by date for performance
CREATE TABLE orders_history (
    LIKE orders INCLUDING ALL
) PARTITION BY RANGE (created_at);

-- Monthly partitions
CREATE TABLE orders_2024_01 PARTITION OF orders_history
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE orders_2024_02 PARTITION OF orders_history
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

Data Relationships​

Entity Relationship Diagram​

Data Flow Architecture​

1. Order Processing Flow​

2. Trade Execution Flow​

3. Market Data Flow​

Performance Optimization​

1. Indexing Strategy​

Primary Indexes:

  • All foreign keys indexed
  • Status columns for filtering
  • Created timestamp for chronological queries
  • Composite indexes for common query patterns

Query Optimization:

-- Efficient order queries
SELECT * FROM orders 
WHERE user_id = ? 
AND status IN ('NEW', 'PARTIAL')
ORDER BY created_at DESC
LIMIT 50;

-- Market data lookup
SELECT * FROM market_data 
WHERE symbol = ? 
ORDER BY timestamp DESC 
LIMIT 1;

2. Partitioning Strategy​

Time-based Partitioning:

  • Orders table partitioned monthly
  • Trades table partitioned monthly
  • Audit logs table partitioned by year
  • Automatic partition creation

Benefits:

  • Improved query performance on recent data
  • Efficient data archival
  • Better index maintenance
  • Reduced lock contention

3. Caching Strategy​

Multi-level Caching:

  • L1: Application-level cache (EhCache)
  • L2: Database query cache
  • L3: Redis for shared data

Cached Data:

  • User sessions
  • Market data snapshots
  • Order book data
  • User preferences

4. Connection Management​

HikariCP Configuration:

spring:
  datasource:
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      idle-timeout: 600000
      max-lifetime: 1800000
      connection-timeout: 30000
      validation-timeout: 5000

Data Integrity & Constraints​

1. Business Rules Enforcement​

Order Validation:

  • Quantity must be positive
  • Limit orders require price
  • Stop-limit orders require stop price
  • Market orders cannot have price

Trade Validation:

  • Buyer and seller must be different
  • Trade quantity cannot exceed order remaining quantity
  • Settlement must occur within regulatory timeframes

Position Validation:

  • Position quantity can be positive (long) or negative (short)
  • Average cost updated on each trade
  • P&L calculated on position changes

2. Referential Integrity​

Foreign Key Constraints:

  • All relationships properly enforced
  • Cascade delete for dependent records
  • ON DELETE RESTRICT for critical entities

Check Constraints:

  • Enum value validation
  • Numeric range validation
  • Business rule enforcement

3. Transaction Management​

ACID Properties:

  • All trading operations atomic
  • Isolation level: READ_COMMITTED
  • Consistent state throughout transactions
  • Durability guaranteed through WAL

Deadlock Prevention:

  • Consistent lock ordering
  • Short transaction durations
  • Optimistic locking where appropriate

Backup & Recovery​

1. Backup Strategy​

Continuous Backup:

  • Point-in-time recovery (PITR)
  • Daily full backups
  • Hourly incremental backups
  • Transaction log shipping

Retention Policy:

  • 7 days of hourly backups
  • 30 days of daily backups
  • 12 months of weekly backups
  • 7 years of monthly backups (regulatory requirement)

2. Recovery Procedures​

Disaster Recovery:

  • RTO: 15 minutes
  • RPO: 5 minutes
  • Cross-region replication
  • Automated failover

Data Recovery:

  • Point-in-time recovery
  • Table-level restore
  • Incremental recovery
  • Validation procedures

Monitoring & Maintenance​

1. Performance Monitoring​

Key Metrics:

  • Query response times
  • Connection pool utilization
  • Lock wait times
  • Index usage statistics

Alerting:

  • Slow query detection
  • Connection pool exhaustion
  • Deadlock detection
  • Disk space warnings

2. Data Maintenance​

Regular Tasks:

  • VACUUM and ANALYZE
  • Index rebuilds
  • Partition management
  • Statistics updates

Archival Procedures:

  • Historical data archiving
  • Partition rotation
  • Backup verification
  • Compliance reporting

Next: Real-time Communication​

Continue to Real-time Communication for detailed WebSocket and event-driven architecture documentation.