MALiPo Database Schema

Comprehensive database structure for storing client information, transactions, and wallet balances

Database Overview

Users & Profiles

Stores user accounts, personal information, and verification status

Wallets & Balances

Manages cryptocurrency wallets and real-time balance tracking

Transactions

Tracks all incoming and outgoing transactions with fees

Users Table

users
user_id UUID (Primary Key)
email VARCHAR(255) UNIQUE NOT NULL
password_hash VARCHAR(255) NOT NULL
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
is_verified BOOLEAN DEFAULT FALSE
verification_status ENUM('pending', 'approved', 'rejected')

User Profiles Table

user_profiles
profile_id UUID (Primary Key)
user_id UUID REFERENCES users(user_id)
first_name VARCHAR(100) NOT NULL
last_name VARCHAR(100) NOT NULL
date_of_birth DATE NOT NULL
address TEXT
city VARCHAR(100)
state VARCHAR(100)
postal_code VARCHAR(20)
country VARCHAR(100)
id_front_image_url VARCHAR(500)
id_back_image_url VARCHAR(500)
facial_scan_data TEXT

Wallets Table

wallets
wallet_id UUID (Primary Key)
user_id UUID REFERENCES users(user_id)
wallet_address VARCHAR(255) UNIQUE NOT NULL
currency VARCHAR(10) NOT NULL
balance DECIMAL(20, 8) DEFAULT 0.0
is_active BOOLEAN DEFAULT TRUE
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Transactions Table

transactions
transaction_id UUID (Primary Key)
from_wallet_id UUID REFERENCES wallets(wallet_id)
to_wallet_id UUID REFERENCES wallets(wallet_id)
amount DECIMAL(20, 8) NOT NULL
currency VARCHAR(10) NOT NULL
transaction_fee DECIMAL(20, 8) NOT NULL
network_fee DECIMAL(20, 8)
total_amount DECIMAL(20, 8) NOT NULL
status ENUM('pending', 'completed', 'failed', 'cancelled')
transaction_hash VARCHAR(255)
description TEXT
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
completed_at TIMESTAMP

Supported Currencies Table

supported_currencies
currency_id UUID (Primary Key)
currency_code VARCHAR(10) UNIQUE NOT NULL
currency_name VARCHAR(100) NOT NULL
is_stablecoin BOOLEAN DEFAULT FALSE
transaction_fee_rate DECIMAL(5, 4) NOT NULL
minimum_transaction DECIMAL(20, 8)
maximum_transaction DECIMAL(20, 8)
is_active BOOLEAN DEFAULT TRUE

Fee Structure Table

fee_structure
fee_id UUID (Primary Key)
currency_id UUID REFERENCES supported_currencies(currency_id)
incoming_fee_rate DECIMAL(5, 4) NOT NULL
outgoing_fee_rate DECIMAL(5, 4) NOT NULL
minimum_fee DECIMAL(20, 8)
maximum_fee DECIMAL(20, 8)
effective_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
is_active BOOLEAN DEFAULT TRUE

Audit Log Table

audit_logs
log_id UUID (Primary Key)
user_id UUID REFERENCES users(user_id)
action_type VARCHAR(100) NOT NULL
action_details TEXT
ip_address VARCHAR(45)
user_agent TEXT
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Key API Endpoints

POST /api/v1/users Create new user account
GET /api/v1/users/{userId}/wallets Get user wallets and balances
POST /api/v1/transactions Create new transaction
GET /api/v1/users/{userId}/transactions Get user transaction history