-- ===================================================================== -- Crypto Trading Platform — Database Schema (MySQL 5.7+ / MariaDB 10.3+) -- Designed for shared hosting: InnoDB only, no stored procedures/events -- that typically require elevated privileges. -- ===================================================================== SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- --------------------------------------------------------------------- -- USERS -- --------------------------------------------------------------------- CREATE TABLE users ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, email VARCHAR(190) NOT NULL UNIQUE, phone VARCHAR(30) NULL, password_hash VARCHAR(255) NOT NULL, -- password_hash() bcrypt/argon2id full_name VARCHAR(150) NOT NULL, display_currency CHAR(3) NOT NULL DEFAULT 'USD', status ENUM('pending','active','suspended','closed') NOT NULL DEFAULT 'pending', kyc_status ENUM('unverified','pending','verified','rejected') NOT NULL DEFAULT 'unverified', two_factor_enabled TINYINT(1) NOT NULL DEFAULT 0, two_factor_secret VARCHAR(64) NULL, email_verified_at DATETIME NULL, last_login_at DATETIME NULL, last_login_ip VARCHAR(45) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE email_verification_tokens ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, token_hash VARCHAR(255) NOT NULL, purpose ENUM('signup','password_reset','login_otp','withdrawal_otp') NOT NULL, expires_at DATETIME NOT NULL, used_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, INDEX idx_user_purpose (user_id, purpose) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE sessions ( id VARCHAR(64) PRIMARY KEY, -- random token id, not sequential user_id BIGINT UNSIGNED NOT NULL, ip_address VARCHAR(45) NULL, user_agent VARCHAR(255) NULL, expires_at DATETIME NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- ASSETS (the 20 supported cryptocurrencies) -- --------------------------------------------------------------------- CREATE TABLE assets ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, symbol VARCHAR(10) NOT NULL UNIQUE, -- BTC, ETH, LTC, SOL, USDT... name VARCHAR(60) NOT NULL, chain VARCHAR(30) NOT NULL, -- bitcoin, ethereum, solana, tron... is_stablecoin TINYINT(1) NOT NULL DEFAULT 0, decimals TINYINT UNSIGNED NOT NULL DEFAULT 8, icon_url VARCHAR(255) NULL, min_deposit DECIMAL(24,8) NOT NULL DEFAULT 0, withdraw_fee DECIMAL(24,8) NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order SMALLINT NOT NULL DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Cached market data, refreshed by a cron/poller hitting a market data API CREATE TABLE market_prices ( asset_id INT UNSIGNED PRIMARY KEY, price_usd DECIMAL(24,8) NOT NULL, change_24h_pct DECIMAL(8,4) NOT NULL DEFAULT 0, high_24h_usd DECIMAL(24,8) NULL, low_24h_usd DECIMAL(24,8) NULL, volume_24h_usd DECIMAL(24,2) NULL, updated_at DATETIME NOT NULL, FOREIGN KEY (asset_id) REFERENCES assets(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Historical candles for charting (OHLC), populated by the same poller CREATE TABLE market_candles ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, asset_id INT UNSIGNED NOT NULL, interval_code ENUM('1m','5m','15m','1h','4h','1d') NOT NULL, open_time DATETIME NOT NULL, open DECIMAL(24,8) NOT NULL, high DECIMAL(24,8) NOT NULL, low DECIMAL(24,8) NOT NULL, close DECIMAL(24,8) NOT NULL, volume DECIMAL(24,2) NOT NULL DEFAULT 0, FOREIGN KEY (asset_id) REFERENCES assets(id) ON DELETE CASCADE, UNIQUE KEY uniq_candle (asset_id, interval_code, open_time) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- WALLETS (one deposit address per user per asset, from custody provider) -- --------------------------------------------------------------------- -- IMPORTANT: no private_key column. Keys live with the custody/WaaS -- provider (e.g. Tatum/Fireblocks/BitGo). This table only stores the -- provider's account/address reference so a compromised DB never leaks funds. CREATE TABLE wallets ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, asset_id INT UNSIGNED NOT NULL, provider VARCHAR(40) NOT NULL, -- e.g. 'tatum', 'fireblocks' provider_account_id VARCHAR(120) NULL, -- provider-side sub-account/vault id deposit_address VARCHAR(120) NOT NULL, address_tag VARCHAR(60) NULL, -- memo/tag for chains that need it (XRP, XLM, etc.) created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uniq_user_asset (user_id, asset_id), FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (asset_id) REFERENCES assets(id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- BALANCES (off-chain ledger balance per user per asset — the number -- shown in the app; reconciled against on-chain deposits via webhooks) -- --------------------------------------------------------------------- CREATE TABLE balances ( user_id BIGINT UNSIGNED NOT NULL, asset_id INT UNSIGNED NOT NULL, available DECIMAL(24,8) NOT NULL DEFAULT 0, locked DECIMAL(24,8) NOT NULL DEFAULT 0, -- reserved by an open order updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (user_id, asset_id), FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (asset_id) REFERENCES assets(id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- TRANSACTIONS (deposits & withdrawals — funding movement in/out) -- --------------------------------------------------------------------- CREATE TABLE transactions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, asset_id INT UNSIGNED NOT NULL, type ENUM('deposit','withdrawal') NOT NULL, amount DECIMAL(24,8) NOT NULL, fee DECIMAL(24,8) NOT NULL DEFAULT 0, status ENUM('pending','confirming','completed','failed','cancelled') NOT NULL DEFAULT 'pending', tx_hash VARCHAR(120) NULL, -- on-chain transaction hash confirmations INT UNSIGNED NOT NULL DEFAULT 0, to_address VARCHAR(120) NULL, -- withdrawal destination provider_ref VARCHAR(120) NULL, -- custody provider's transaction id created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (asset_id) REFERENCES assets(id) ON DELETE RESTRICT, INDEX idx_user_created (user_id, created_at), INDEX idx_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- TRADES / ORDERS (buy/sell one asset for another, e.g. BTC -> USDT) -- --------------------------------------------------------------------- CREATE TABLE orders ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, base_asset_id INT UNSIGNED NOT NULL, -- asset being bought/sold, e.g. BTC quote_asset_id INT UNSIGNED NOT NULL, -- priced in, e.g. USDT side ENUM('buy','sell') NOT NULL, order_type ENUM('market','limit') NOT NULL DEFAULT 'market', quantity DECIMAL(24,8) NOT NULL, limit_price DECIMAL(24,8) NULL, -- null for market orders filled_price DECIMAL(24,8) NULL, status ENUM('open','filled','partially_filled','cancelled') NOT NULL DEFAULT 'open', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, filled_at DATETIME NULL, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (base_asset_id) REFERENCES assets(id) ON DELETE RESTRICT, FOREIGN KEY (quote_asset_id) REFERENCES assets(id) ON DELETE RESTRICT, INDEX idx_user_status (user_id, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- CONVERSIONS (direct asset-to-asset swap, distinct from an order book trade) -- --------------------------------------------------------------------- CREATE TABLE conversions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, from_asset_id INT UNSIGNED NOT NULL, to_asset_id INT UNSIGNED NOT NULL, from_amount DECIMAL(24,8) NOT NULL, to_amount DECIMAL(24,8) NOT NULL, rate DECIMAL(24,8) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (from_asset_id) REFERENCES assets(id) ON DELETE RESTRICT, FOREIGN KEY (to_asset_id) REFERENCES assets(id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- NOTIFICATIONS -- --------------------------------------------------------------------- CREATE TABLE notifications ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, type VARCHAR(40) NOT NULL, -- deposit_confirmed, trade_filled, security_alert... title VARCHAR(150) NOT NULL, body TEXT NULL, read_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, INDEX idx_user_created (user_id, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- AUDIT LOG (security-relevant events — logins, withdrawals, settings changes) -- --------------------------------------------------------------------- CREATE TABLE audit_log ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NULL, action VARCHAR(60) NOT NULL, ip_address VARCHAR(45) NULL, metadata JSON NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- ADMINS -- --------------------------------------------------------------------- CREATE TABLE admins ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, email VARCHAR(190) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, full_name VARCHAR(150) NOT NULL, role ENUM('super_admin','support','ops') NOT NULL DEFAULT 'support', is_active TINYINT(1) NOT NULL DEFAULT 1, last_login_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Every admin action against a user (suspend, reactivate, balance edit, etc.) CREATE TABLE admin_actions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, admin_id BIGINT UNSIGNED NOT NULL, target_user_id BIGINT UNSIGNED NULL, action VARCHAR(60) NOT NULL, -- 'suspend_user', 'reactivate_user', 'note', ... reason TEXT NULL, metadata JSON NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE CASCADE, FOREIGN KEY (target_user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_target_user (target_user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- SUPPORT CHAT -- --------------------------------------------------------------------- CREATE TABLE support_conversations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, assigned_admin_id BIGINT UNSIGNED NULL, status ENUM('open','closed') NOT NULL DEFAULT 'open', subject VARCHAR(150) NULL, last_message_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (assigned_admin_id) REFERENCES admins(id) ON DELETE SET NULL, INDEX idx_status (status), INDEX idx_user (user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE support_messages ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, conversation_id BIGINT UNSIGNED NOT NULL, sender_type ENUM('user','admin') NOT NULL, sender_id BIGINT UNSIGNED NOT NULL, -- user_id or admin_id depending on sender_type body TEXT NOT NULL, read_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (conversation_id) REFERENCES support_conversations(id) ON DELETE CASCADE, INDEX idx_conversation_created (conversation_id, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- SEED: the 20 supported assets -- --------------------------------------------------------------------- INSERT INTO assets (symbol, name, chain, is_stablecoin, decimals, sort_order) VALUES ('BTC', 'Bitcoin', 'bitcoin', 0, 8, 1), ('ETH', 'Ethereum', 'ethereum', 0, 18, 2), ('USDT', 'Tether', 'ethereum', 1, 6, 3), ('USDC', 'USD Coin', 'ethereum', 1, 6, 4), ('BNB', 'BNB', 'bsc', 0, 18, 5), ('SOL', 'Solana', 'solana', 0, 9, 6), ('XRP', 'XRP', 'ripple', 0, 6, 7), ('ADA', 'Cardano', 'cardano', 0, 6, 8), ('DOGE', 'Dogecoin', 'dogecoin', 0, 8, 9), ('TRX', 'TRON', 'tron', 0, 6, 10), ('LTC', 'Litecoin', 'litecoin', 0, 8, 11), ('DOT', 'Polkadot', 'polkadot', 0, 10, 12), ('MATIC','Polygon', 'polygon', 0, 18, 13), ('AVAX', 'Avalanche', 'avalanche', 0, 18, 14), ('LINK', 'Chainlink', 'ethereum', 0, 18, 15), ('SHIB', 'Shiba Inu', 'ethereum', 0, 18, 16), ('DAI', 'Dai', 'ethereum', 1, 18, 17), ('ATOM', 'Cosmos', 'cosmos', 0, 6, 18), ('XLM', 'Stellar', 'stellar', 0, 7, 19), ('BCH', 'Bitcoin Cash', 'bitcoincash', 0, 8, 20); SET FOREIGN_KEY_CHECKS = 1;