File size: 10,954 Bytes
02e7676 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 | ```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);
``` |