```sql -- MALiPo Crypto Payment Platform Database Schema -- Comprehensive schema capturing all attributes across the system -- Users table (combines sign-up, client-verification, dashboard, settings) CREATE TABLE users ( user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), email VARCHAR(255) UNIQUE NOT NULL, password_hash VARCHAR(255) NOT NULL, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, phone VARCHAR(25), country VARCHAR(100), address TEXT, city VARCHAR(100), state_province VARCHAR(100), postal_code VARCHAR(20), date_of_birth DATE, verification_status ENUM('unverified', 'pending', 'verified', 'rejected') DEFAULT 'unverified', verification_email_sent BOOLEAN DEFAULT FALSE, verification_email_sent_at TIMESTAMP, verification_completed_at TIMESTAMP, email_verified BOOLEAN DEFAULT FALSE, email_verified_at TIMESTAMP, two_factor_enabled BOOLEAN DEFAULT FALSE, two_factor_secret VARCHAR(255), public_profile BOOLEAN DEFAULT FALSE, transaction_history_visibility ENUM('only_me', 'contacts', 'everyone') DEFAULT 'only_me', data_sharing_analytics BOOLEAN DEFAULT FALSE, personalized_ads BOOLEAN DEFAULT FALSE, marketing_emails BOOLEAN DEFAULT FALSE, push_notifications BOOLEAN DEFAULT TRUE, email_notifications BOOLEAN DEFAULT TRUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, last_login_at TIMESTAMP, is_active BOOLEAN DEFAULT TRUE, account_deleted BOOLEAN DEFAULT FALSE, deleted_at TIMESTAMP ); -- Verification documents table (captures all ID types and facial recognition) CREATE TABLE verification_documents ( document_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID NOT NULL, document_type ENUM('passport', 'drivers_license', 'national_id') NOT NULL, document_front_path VARCHAR(500), document_back_path VARCHAR(500), document_status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending', rejection_reason TEXT, uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, reviewed_at TIMESTAMP, reviewed_by UUID, facial_recognition_photo_path VARCHAR(500), facial_recognition_status ENUM('pending', 'completed', 'failed') DEFAULT 'pending', facial_recognition_completed_at TIMESTAMP, camera_access_granted BOOLEAN DEFAULT FALSE, verification_attempts INT DEFAULT 0, FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE, INDEX idx_user_document (user_id, document_type) ); -- Wallets table (captures all wallet types and balances) CREATE TABLE wallets ( wallet_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID NOT NULL, wallet_type ENUM('btc', 'eth', 'sol', 'matic', 'bnb', 'usdc', 'usdt', 'doge', 'ada', 'xrp', 'ltc', 'link') NOT NULL, wallet_address VARCHAR(255) UNIQUE NOT NULL, balance DECIMAL(20, 8) DEFAULT 0.00000000, balance_usd DECIMAL(15, 2) DEFAULT 0.00, wallet_status ENUM('active', 'inactive', 'suspended') DEFAULT 'active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, currency_symbol VARCHAR(10), network_type VARCHAR(50), FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE, UNIQUE KEY unique_user_wallet (user_id, wallet_type), INDEX idx_wallet_address (wallet_address) ); -- Transactions table (captures all transaction types and statuses) CREATE TABLE transactions ( transaction_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), wallet_id UUID NOT NULL, transaction_type ENUM('sent', 'received', 'exchange', 'refund') NOT NULL, amount DECIMAL(20, 8) NOT NULL, amount_usd DECIMAL(15, 2), transaction_hash VARCHAR(255) UNIQUE, from_address VARCHAR(255), to_address VARCHAR(255), counterparty VARCHAR(255), transaction_status ENUM('pending', 'completed', 'failed', 'refunded', 'cancelled') DEFAULT 'pending', confirmation_count INT DEFAULT 0, network_fee DECIMAL(20, 8) DEFAULT 0.00000000, network_fee_usd DECIMAL(15, 2) DEFAULT 0.00, transaction_fee DECIMAL(20, 8) DEFAULT 0.00000000, transaction_fee_usd DECIMAL(15, 2) DEFAULT 0.00, description TEXT, invoice_number VARCHAR(100), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, completed_at TIMESTAMP, expired_at TIMESTAMP, FOREIGN KEY (wallet_id) REFERENCES wallets(wallet_id) ON DELETE CASCADE, INDEX idx_transaction_hash (transaction_hash), INDEX idx_wallet_transactions (wallet_id, created_at), INDEX idx_transaction_status (transaction_status), INDEX idx_created_at (created_at) ); -- Pricing plans table (captures all pricing tiers) CREATE TABLE pricing_plans ( plan_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), plan_name VARCHAR(50) UNIQUE NOT NULL, plan_type ENUM('free', 'pro', 'enterprise') NOT NULL, monthly_price DECIMAL(10, 2) DEFAULT 0.00, transaction_fee_percentage DECIMAL(5, 2) DEFAULT 0.00, max_monthly_volume DECIMAL(15, 2), features JSON, is_active BOOLEAN DEFAULT TRUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- User subscriptions table (links users to pricing plans) CREATE TABLE user_subscriptions ( subscription_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID NOT NULL, plan_id UUID NOT NULL, subscription_status ENUM('active', 'cancelled', 'expired', 'suspended') DEFAULT 'active', start_date DATE NOT NULL, end_date DATE, auto_renew BOOLEAN DEFAULT TRUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE, FOREIGN KEY (plan_id) REFERENCES pricing_plans(plan_id) ON DELETE CASCADE, INDEX idx_user_subscription (user_id, subscription_status) ); -- Session management table (for active sessions tracking) CREATE TABLE user_sessions ( session_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID NOT NULL, session_token VARCHAR(500) UNIQUE NOT NULL, ip_address VARCHAR(45), user_agent TEXT, device_type VARCHAR(50), browser VARCHAR(100), os VARCHAR(100), location VARCHAR(255), is_active BOOLEAN DEFAULT TRUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, last_activity_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, expires_at TIMESTAMP NOT NULL, FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE, INDEX idx_session_token (session_token), INDEX idx_user_sessions (user_id, is_active), INDEX idx_expires_at (expires_at) ); -- Notification preferences table CREATE TABLE notification_preferences ( preference_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID NOT NULL, notification_type ENUM('transaction_alert', 'security_alert', 'marketing', 'price_alert') NOT NULL, email_enabled BOOLEAN DEFAULT TRUE, push_enabled BOOLEAN DEFAULT TRUE, sms_enabled BOOLEAN DEFAULT FALSE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE, UNIQUE KEY unique_user_notification (user_id, notification_type) ); -- Cryptocurrency market data table (for live pricing) CREATE TABLE market_data ( market_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), currency_symbol VARCHAR(10) NOT NULL, currency_name VARCHAR(100) NOT NULL, current_price DECIMAL(15, 8) NOT NULL, price_change_24h DECIMAL(15, 8), price_change_percentage_24h DECIMAL(8, 4), market_cap BIGINT, volume_24h BIGINT, last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_currency_symbol (currency_symbol), INDEX idx_last_updated (last_updated) ); -- API keys table (for developer integrations) CREATE TABLE api_keys ( api_key_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID NOT NULL, api_key VARCHAR(255) UNIQUE NOT NULL, api_secret VARCHAR(255) NOT NULL, key_name VARCHAR(100), permissions JSON, is_active BOOLEAN DEFAULT TRUE, last_used_at TIMESTAMP, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, expires_at TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE, INDEX idx_api_key (api_key), INDEX idx_user_api_keys (user_id, is_active) ); -- Payment requests table (for invoice generation) CREATE TABLE payment_requests ( request_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), wallet_id UUID NOT NULL, amount DECIMAL(20, 8) NOT NULL, amount_usd DECIMAL(15, 2), currency VARCHAR(10) NOT NULL, description TEXT, payment_link VARCHAR(500) UNIQUE NOT NULL, payment_status ENUM('pending', 'paid', 'expired', 'cancelled') DEFAULT 'pending', expires_at TIMESTAMP, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, paid_at TIMESTAMP, FOREIGN KEY (wallet_id) REFERENCES wallets(wallet_id) ON DELETE CASCADE, INDEX idx_payment_link (payment_link), INDEX idx_payment_status (payment_status) ); -- Audit log table (for security tracking) CREATE TABLE audit_logs ( log_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID, action_type VARCHAR(100) NOT NULL, action_details JSON, ip_address VARCHAR(45), user_agent TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE SET NULL, INDEX idx_user_actions (user_id, created_at), INDEX idx_action_type (action_type), INDEX idx_created_at (created_at) ); -- OAuth providers table (for Google/GitHub sign-in) CREATE TABLE oauth_providers ( provider_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID NOT NULL, provider_name ENUM('google', 'github') NOT NULL, provider_user_id VARCHAR(255) NOT NULL, provider_email VARCHAR(255), access_token TEXT, refresh_token TEXT, expires_at TIMESTAMP, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE, UNIQUE KEY unique_provider_user (provider_name, provider_user_id), UNIQUE KEY unique_user_provider (user_id, provider_name) ); -- Create indexes for common queries CREATE INDEX idx_users_verification_status ON users(verification_status); CREATE INDEX idx_users_email_verified ON users(email_verified); CREATE INDEX idx_users_created_at ON users(created_at); CREATE INDEX idx_wallets_balance ON wallets(balance DESC); CREATE INDEX idx_transactions_amount ON transactions(amount DESC); CREATE INDEX idx_transactions_currency ON transactions(currency_symbol); CREATE INDEX idx_pricing_plans_active ON pricing_plans(is_active); ```