malipo / database-schema.sql
Meech888's picture
create a database schema that captures every attribute in this system - Follow Up Deployment
02e7676 verified
Raw History Blame Contribute Delete
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);
```