Download database-schema.sql from Meech888/malipo: direct link, hf CLI and curl.
- Browser
- Download file 11 kB
-
https://huggingface.co/spaces/Meech888/malipo/resolve/main/database-schema.sql
- Command line
-
hf download hf://spaces/Meech888/malipo/database-schema.sql
-
curl -L -o database-schema.sql https://huggingface.co/spaces/Meech888/malipo/resolve/main/database-schema.sql
11 kB
| ```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); | |
| ``` |