-- ============================================================================ -- Ororo Markets — database schema (MySQL 5.7+/8.x, MariaDB 10.x) -- -- Derived from the UI in src/data/* and from the contract already present in -- backend/user.php (login / register / update_password). -- -- Conventions -- * Engine InnoDB, charset utf8mb4 / collation utf8mb4_unicode_ci -- * Surrogate keys: BIGINT UNSIGNED AUTO_INCREMENT -- * Money: DECIMAL(20,8) for crypto/base units, DECIMAL(20,2) for USD -- * created_at / updated_at are automatic; deleted_at is a soft delete -- * Only CREATE TABLE IF NOT EXISTS is used, so re-applying this file is safe -- (it never drops data). For a clean slate use a fresh database. -- * Access control: roles (admin | user) + permissions + role_permissions. -- Every admin-only surface is listed in seed.sql and documented in -- README.md. -- -- Apply with: mysql -u root -p your_database < schema.sql -- mysql -u root -p your_database < seed.sql (master data) -- ============================================================================ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 1; -- --------------------------------------------------------------------------- -- 1. ACCESS CONTROL -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS roles ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(32) NOT NULL, -- 'admin' | 'user' label VARCHAR(64) NOT NULL, description VARCHAR(255) NULL, is_system TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_roles_name (name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- One row per guarded screen/section, e.g. 'manage.verifications.view'. CREATE TABLE IF NOT EXISTS permissions ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(64) NOT NULL, area VARCHAR(32) NOT NULL, -- manage | settings | deposit | ... label VARCHAR(96) NOT NULL, is_admin_only TINYINT(1) NOT NULL DEFAULT 1, PRIMARY KEY (id), UNIQUE KEY uq_permissions_name (name), KEY idx_permissions_area (area) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS role_permissions ( role_id SMALLINT UNSIGNED NOT NULL, permission_id SMALLINT UNSIGNED NOT NULL, PRIMARY KEY (role_id, permission_id), KEY idx_role_permissions_permission (permission_id), CONSTRAINT fk_role_permissions_role FOREIGN KEY (role_id) REFERENCES roles (id) ON DELETE CASCADE, CONSTRAINT fk_role_permissions_permission FOREIGN KEY (permission_id) REFERENCES permissions (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- 2. REFERENCE CATALOGUES -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS countries ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, code CHAR(2) NOT NULL, -- ISO 3166-1 alpha-2 name VARCHAR(96) NOT NULL, dial_code VARCHAR(8) NULL, -- '+376' is_active TINYINT(1) NOT NULL DEFAULT 1, PRIMARY KEY (id), UNIQUE KEY uq_countries_code (code), KEY idx_countries_name (name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Fiat currency an account is denominated in (signup "Account currency"). CREATE TABLE IF NOT EXISTS currencies ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, code CHAR(3) NOT NULL, name VARCHAR(48) NOT NULL, symbol VARCHAR(8) NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, PRIMARY KEY (id), UNIQUE KEY uq_currencies_code (code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Mirrors the language picker in the header; is_translated marks the locales -- with a complete dictionary in src/i18n. CREATE TABLE IF NOT EXISTS locales ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, code VARCHAR(10) NOT NULL, name VARCHAR(64) NOT NULL, flag_code VARCHAR(8) NULL, is_translated TINYINT(1) NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, PRIMARY KEY (id), UNIQUE KEY uq_locales_code (code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Settings > Levels ("Bronze / Silver / Starter / Gold / VIP"). -- profit_limit NULL means "Unlimited Balance" in the Levels modal. CREATE TABLE IF NOT EXISTS account_levels ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, slug VARCHAR(32) NOT NULL, name VARCHAR(64) NOT NULL, minimum_balance DECIMAL(20,2) NOT NULL DEFAULT 0.00, profit_limit DECIMAL(20,2) NULL, commission_percent DECIMAL(6,2) NOT NULL DEFAULT 0.00, -- badge "Starter 1%" color VARCHAR(32) NULL, sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_account_levels_slug (slug) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- 3. USERS & SESSIONS (header menu, Manage > Users, auth pages) -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS users ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, role_id SMALLINT UNSIGNED NOT NULL, account_level_id SMALLINT UNSIGNED NOT NULL, first_name VARCHAR(64) NOT NULL, last_name VARCHAR(64) NOT NULL, username VARCHAR(64) NOT NULL, email VARCHAR(190) NOT NULL, password_hash VARCHAR(255) NOT NULL, -- as typed by the user (Account::HASH_PASSWORDS = false); password_hash() once that flips to true avatar_path VARCHAR(255) NULL, -- uploads/users/ country_code CHAR(2) NULL, phone_dial_code VARCHAR(8) NULL, -- '+376' default on the signup form phone VARCHAR(32) NULL, account_currency CHAR(3) NOT NULL DEFAULT 'USD', locale VARCHAR(10) NOT NULL DEFAULT 'en', theme VARCHAR(16) NOT NULL DEFAULT 'dark', accent_color CHAR(7) NOT NULL DEFAULT '#31acee', referral_code CHAR(10) NOT NULL, -- Helper::randomString(10) referred_by BIGINT UNSIGNED NULL, -- signup "Have a referral code" email_verified_at TIMESTAMP NULL DEFAULT NULL, two_factor_enabled TINYINT(1) NOT NULL DEFAULT 0, two_factor_secret VARCHAR(191) NULL, -- TOTP seed, encrypted by the API before storing status ENUM('active','suspended','closed') NOT NULL DEFAULT 'active', kyc_status ENUM('unverified','pending','verified','rejected') NOT NULL DEFAULT 'unverified', trading_progress TINYINT UNSIGNED NULL, -- dashboard "Trading progress" (%); NULL = derived from the account level signal_strength TINYINT UNSIGNED NULL, -- dashboard "Signal strength" (%); NULL = 0 auto_profit TINYINT(1) NOT NULL DEFAULT 0, -- cron/payouts.php credits $10–$20 to the profit account once a day last_login_at TIMESTAMP NULL DEFAULT NULL, last_login_ip VARCHAR(45) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY uq_users_email (email), UNIQUE KEY uq_users_username (username), UNIQUE KEY uq_users_referral_code (referral_code), KEY idx_users_role (role_id), KEY idx_users_level (account_level_id), KEY idx_users_referrer (referred_by), KEY idx_users_status_created (status, created_at), CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles (id) ON DELETE RESTRICT, CONSTRAINT fk_users_level FOREIGN KEY (account_level_id) REFERENCES account_levels (id) ON DELETE RESTRICT, CONSTRAINT fk_users_referrer FOREIGN KEY (referred_by) REFERENCES users (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Bearer tokens issued by the API. Only the SHA-256 hash of the token is kept. CREATE TABLE IF NOT EXISTS user_sessions ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, token_hash CHAR(64) NOT NULL, user_agent VARCHAR(255) NULL, ip_address VARCHAR(45) NULL, expires_at TIMESTAMP NOT NULL, revoked_at TIMESTAMP NULL DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_user_sessions_token (token_hash), KEY idx_user_sessions_user (user_id, expires_at), CONSTRAINT fk_user_sessions_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS password_resets ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, token_hash CHAR(64) NOT NULL, expires_at TIMESTAMP NOT NULL, used_at TIMESTAMP NULL DEFAULT NULL, ip_address VARCHAR(45) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_password_resets_token (token_hash), KEY idx_password_resets_user (user_id, expires_at), CONSTRAINT fk_password_resets_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Confirms the address a member signed up with. The account works without it — -- the token only stamps `users.email_verified_at` — so the flow never blocks a -- sign-in. `email` is kept with the token, so a link issued for an old address -- cannot verify a new one. CREATE TABLE IF NOT EXISTS email_verifications ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, email VARCHAR(190) NOT NULL, token_hash CHAR(64) NOT NULL, expires_at TIMESTAMP NOT NULL, used_at TIMESTAMP NULL DEFAULT NULL, ip_address VARCHAR(45) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_email_verifications_token (token_hash), KEY idx_email_verifications_user (user_id, expires_at), CONSTRAINT fk_email_verifications_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Feeds login throttling and the "Security" panel's sign-in history. CREATE TABLE IF NOT EXISTS login_attempts ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, email VARCHAR(190) NULL, user_id BIGINT UNSIGNED NULL, ip_address VARCHAR(45) NULL, user_agent VARCHAR(255) NULL, is_success TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_login_attempts_email (email, created_at), KEY idx_login_attempts_ip (ip_address, created_at), KEY idx_login_attempts_user (user_id), CONSTRAINT fk_login_attempts_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- 4. VERIFICATION (KYC) — Manage > Verification, profile "Verification" -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS verifications ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, document_type ENUM('id_card','passport','drivers_license') NOT NULL, document_number VARCHAR(64) NOT NULL, country_code CHAR(2) NULL, document_front_path VARCHAR(255) NULL, document_back_path VARCHAR(255) NULL, selfie_path VARCHAR(255) NULL, address_proof_path VARCHAR(255) NULL, status ENUM('pending','verified','rejected') NOT NULL DEFAULT 'pending', rejection_reason VARCHAR(255) NULL, reviewed_by BIGINT UNSIGNED NULL, reviewed_at TIMESTAMP NULL DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_verifications_user (user_id, status), KEY idx_verifications_status_created (status, created_at), CONSTRAINT fk_verifications_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_verifications_reviewer FOREIGN KEY (reviewed_by) REFERENCES users (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- 5. MARKET CATALOGUE — Markets, Assets, Cold Storage, Deposit, Settings -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS assets ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, symbol VARCHAR(16) NOT NULL, name VARCHAR(96) NOT NULL, type ENUM('crypto','fiat','stock','commodity','forex','index') NOT NULL DEFAULT 'crypto', icon_path VARCHAR(255) NULL, icon_color CHAR(7) NULL, -- '#f7931a' price_usd DECIMAL(20,8) NOT NULL DEFAULT 0.00000000, price_updated_at TIMESTAMP NULL DEFAULT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, is_depositable TINYINT(1) NOT NULL DEFAULT 0, is_withdrawable TINYINT(1) NOT NULL DEFAULT 0, is_cold_storage_enabled TINYINT(1) NOT NULL DEFAULT 0, sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_assets_symbol (symbol), KEY idx_assets_type_active (type, is_active) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS networks ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(64) NOT NULL, -- 'ERC20' slug VARCHAR(32) NOT NULL, -- 'erc20' chain VARCHAR(32) NULL, explorer_url VARCHAR(255) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, PRIMARY KEY (id), UNIQUE KEY uq_networks_slug (slug) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Deposit address + limits per asset/network (Settings > Deposit "Addresses"). CREATE TABLE IF NOT EXISTS asset_networks ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, asset_id BIGINT UNSIGNED NOT NULL, network_id SMALLINT UNSIGNED NOT NULL, deposit_address VARCHAR(128) NULL, minimum_deposit DECIMAL(20,8) NULL, withdrawal_fee DECIMAL(20,8) NOT NULL DEFAULT 0.00000000, is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY uq_asset_networks (asset_id, network_id), KEY idx_asset_networks_network (network_id), CONSTRAINT fk_asset_networks_asset FOREIGN KEY (asset_id) REFERENCES assets (id) ON DELETE CASCADE, CONSTRAINT fk_asset_networks_network FOREIGN KEY (network_id) REFERENCES networks (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Price series behind the Markets sparkline and the Trade chart. CREATE TABLE IF NOT EXISTS asset_price_history ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, asset_id BIGINT UNSIGNED NOT NULL, price_usd DECIMAL(20,8) NOT NULL, change_pct DECIMAL(10,4) NULL, recorded_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_asset_price_history (asset_id, recorded_at), CONSTRAINT fk_asset_price_history_asset FOREIGN KEY (asset_id) REFERENCES assets (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS watchlist ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, asset_id BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_watchlist (user_id, asset_id), KEY idx_watchlist_asset (asset_id), CONSTRAINT fk_watchlist_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_watchlist_asset FOREIGN KEY (asset_id) REFERENCES assets (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- 6. BALANCES & LEDGER — Dashboard, Assets, profile "Account Summary" -- --------------------------------------------------------------------------- -- The account switcher offers every wallet the member holds. -- One row per wallet. `real` is the main account (deposits, trading, cards, -- withdrawals); `signal` and `subscription` pay for their own products, `profit` -- collects payouts, and `demo` is the play balance. CREATE TABLE IF NOT EXISTS user_accounts ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, type ENUM('real','signal','subscription','profit','demo') NOT NULL DEFAULT 'real', currency CHAR(3) NOT NULL DEFAULT 'USD', balance DECIMAL(20,8) NOT NULL DEFAULT 0.00000000, locked_balance DECIMAL(20,8) NOT NULL DEFAULT 0.00000000, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_user_accounts (user_id, type), CONSTRAINT fk_user_accounts_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- "In your wallet" column (Assets page) and Cold Storage holdings. CREATE TABLE IF NOT EXISTS asset_holdings ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, asset_id BIGINT UNSIGNED NOT NULL, location ENUM('wallet','cold_storage') NOT NULL DEFAULT 'wallet', amount DECIMAL(20,8) NOT NULL DEFAULT 0.00000000, average_price_usd DECIMAL(20,8) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_asset_holdings (user_id, asset_id, location), KEY idx_asset_holdings_asset (asset_id), CONSTRAINT fk_asset_holdings_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_asset_holdings_asset FOREIGN KEY (asset_id) REFERENCES assets (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Single ledger every balance movement is written to: the dashboard's -- "Recent transactions", Manage > Payments and each product history tab. CREATE TABLE IF NOT EXISTS transactions ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, account_id BIGINT UNSIGNED NULL, asset_id BIGINT UNSIGNED NULL, type ENUM('deposit','withdrawal','transfer','trade','referral','subscription_fund','signal_fund','mining','stake','real_estate','card_fee','cold_storage_in','cold_storage_out','adjustment','auto_profit') NOT NULL, direction ENUM('credit','debit') NOT NULL, amount DECIMAL(20,8) NOT NULL DEFAULT 0.00000000, amount_usd DECIMAL(20,2) NOT NULL DEFAULT 0.00, balance_after DECIMAL(20,8) NULL, status ENUM('pending','completed','failed','reversed') NOT NULL DEFAULT 'pending', reference VARCHAR(32) NULL, description VARCHAR(255) NULL, meta JSON NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_transactions_user_created (user_id, created_at), KEY idx_transactions_type_created (type, created_at), KEY idx_transactions_status (status), KEY idx_transactions_asset (asset_id), KEY idx_transactions_account (account_id), CONSTRAINT fk_transactions_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_transactions_account FOREIGN KEY (account_id) REFERENCES user_accounts (id) ON DELETE SET NULL, CONSTRAINT fk_transactions_asset FOREIGN KEY (asset_id) REFERENCES assets (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- 7. DEPOSITS & WITHDRAWALS — Deposit page, Withdraw page, Manage tabs -- Both page filter lists mix status and method ("All / Pending / Bank -- transfer / Referral"), so method is its own column. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS deposits ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, reference VARCHAR(32) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, account_id BIGINT UNSIGNED NULL, asset_id BIGINT UNSIGNED NULL, network_id SMALLINT UNSIGNED NULL, method ENUM('crypto','bank_transfer','card','wire') NOT NULL DEFAULT 'crypto', deposit_type ENUM('regular','signal_fund','subscription_fund') NOT NULL DEFAULT 'regular', amount DECIMAL(20,8) NOT NULL DEFAULT 0.00000000, rate_usd DECIMAL(20,8) NULL, amount_usd DECIMAL(20,2) NOT NULL DEFAULT 0.00, address VARCHAR(128) NULL, tx_hash VARCHAR(128) NULL, proof_path VARCHAR(255) NULL, proof_name VARCHAR(191) NULL, status ENUM('pending','approved','declined') NOT NULL DEFAULT 'pending', admin_note VARCHAR(255) NULL, reviewed_by BIGINT UNSIGNED NULL, reviewed_at TIMESTAMP NULL DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_deposits_reference (reference), KEY idx_deposits_user_created (user_id, created_at), KEY idx_deposits_status_created (status, created_at), KEY idx_deposits_method (method), KEY idx_deposits_asset (asset_id), CONSTRAINT fk_deposits_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_deposits_account FOREIGN KEY (account_id) REFERENCES user_accounts (id) ON DELETE SET NULL, CONSTRAINT fk_deposits_asset FOREIGN KEY (asset_id) REFERENCES assets (id) ON DELETE SET NULL, CONSTRAINT fk_deposits_network FOREIGN KEY (network_id) REFERENCES networks (id) ON DELETE SET NULL, CONSTRAINT fk_deposits_reviewer FOREIGN KEY (reviewed_by) REFERENCES users (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Saved payout destinations of a user (bank account / wallet). CREATE TABLE IF NOT EXISTS payment_methods ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, type ENUM('bank','crypto','card') NOT NULL, label VARCHAR(96) NULL, details JSON NULL, -- holder, iban, address, ... is_default TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_payment_methods_user (user_id, type), CONSTRAINT fk_payment_methods_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS withdrawals ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, reference VARCHAR(32) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, account_id BIGINT UNSIGNED NULL, asset_id BIGINT UNSIGNED NULL, network_id SMALLINT UNSIGNED NULL, payment_method_id BIGINT UNSIGNED NULL, method ENUM('crypto','bank_transfer','paypal','skrill','western_union','neteller','moneygram','perfect_money','real_estate','referral') NOT NULL DEFAULT 'crypto', destination VARCHAR(255) NULL, -- wallet address or bank details amount DECIMAL(20,8) NOT NULL DEFAULT 0.00000000, fee DECIMAL(20,8) NOT NULL DEFAULT 0.00000000, amount_usd DECIMAL(20,2) NOT NULL DEFAULT 0.00, code_used VARCHAR(32) NULL, -- Settings > Withdrawal "withdrawal code" status ENUM('pending','approved','declined','completed') NOT NULL DEFAULT 'pending', admin_note VARCHAR(255) NULL, reviewed_by BIGINT UNSIGNED NULL, reviewed_at TIMESTAMP NULL DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_withdrawals_reference (reference), KEY idx_withdrawals_user_created (user_id, created_at), KEY idx_withdrawals_status_created (status, created_at), KEY idx_withdrawals_method (method), KEY idx_withdrawals_asset (asset_id), KEY idx_withdrawals_payment_method (payment_method_id), CONSTRAINT fk_withdrawals_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_withdrawals_account FOREIGN KEY (account_id) REFERENCES user_accounts (id) ON DELETE SET NULL, CONSTRAINT fk_withdrawals_asset FOREIGN KEY (asset_id) REFERENCES assets (id) ON DELETE SET NULL, CONSTRAINT fk_withdrawals_network FOREIGN KEY (network_id) REFERENCES networks (id) ON DELETE SET NULL, CONSTRAINT fk_withdrawals_payment_method FOREIGN KEY (payment_method_id) REFERENCES payment_methods (id) ON DELETE SET NULL, CONSTRAINT fk_withdrawals_reviewer FOREIGN KEY (reviewed_by) REFERENCES users (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Settings > Withdrawal: "Allow users withdraw crypto" plus the asset list. CREATE TABLE IF NOT EXISTS withdrawal_assets ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, asset_id BIGINT UNSIGNED NOT NULL, is_enabled TINYINT(1) NOT NULL DEFAULT 1, minimum DECIMAL(20,8) NULL, maximum DECIMAL(20,8) NULL, fee_percent DECIMAL(6,2) NOT NULL DEFAULT 0.00, sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY uq_withdrawal_assets (asset_id), CONSTRAINT fk_withdrawal_assets_asset FOREIGN KEY (asset_id) REFERENCES assets (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- 8. CARDS — Cards page (request flow + held cards), Settings > Cards -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS card_products ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, slug VARCHAR(32) NOT NULL, name VARCHAR(64) NOT NULL, -- 'Regular Card' / 'Virtual Card' card_type ENUM('regular','virtual','physical') NOT NULL, kyc_required TINYINT(1) NOT NULL DEFAULT 0, issuance_fee DECIMAL(20,2) NOT NULL DEFAULT 0.00, minimum_deposit VARCHAR(32) NULL, -- 'No minimum' / '$10,000' last4 CHAR(4) NULL, -- dummy last4 shown by the UI is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_card_products_slug (slug) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS card_requests ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, card_product_id SMALLINT UNSIGNED NOT NULL, holder_name VARCHAR(96) NULL, status ENUM('pending','approved','declined') NOT NULL DEFAULT 'pending', note VARCHAR(255) NULL, admin_note VARCHAR(255) NULL, reviewed_by BIGINT UNSIGNED NULL, reviewed_at TIMESTAMP NULL DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_card_requests_user (user_id, created_at), KEY idx_card_requests_status (status, created_at), KEY idx_card_requests_product (card_product_id), CONSTRAINT fk_card_requests_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_card_requests_product FOREIGN KEY (card_product_id) REFERENCES card_products (id) ON DELETE RESTRICT, CONSTRAINT fk_card_requests_reviewer FOREIGN KEY (reviewed_by) REFERENCES users (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS user_cards ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, card_product_id SMALLINT UNSIGNED NOT NULL, card_request_id BIGINT UNSIGNED NULL, holder_name VARCHAR(96) NULL, last4 CHAR(4) NULL, expiry_month TINYINT UNSIGNED NULL, expiry_year SMALLINT UNSIGNED NULL, balance_usd DECIMAL(20,2) NOT NULL DEFAULT 0.00, status ENUM('pending','active','frozen','expired','closed') NOT NULL DEFAULT 'pending', issued_at TIMESTAMP NULL DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_user_cards_user (user_id, status), KEY idx_user_cards_product (card_product_id), CONSTRAINT fk_user_cards_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_user_cards_product FOREIGN KEY (card_product_id) REFERENCES card_products (id) ON DELETE RESTRICT, CONSTRAINT fk_user_cards_request FOREIGN KEY (card_request_id) REFERENCES card_requests (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- 9. SUBSCRIPTIONS & SIGNALS — Subscribe page, Signals page, Manage tabs -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS subscription_plans ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, slug VARCHAR(32) NOT NULL, name VARCHAR(64) NOT NULL, -- 'Starter plan' minimum DECIMAL(20,2) NOT NULL DEFAULT 0.00, maximum DECIMAL(20,2) NOT NULL DEFAULT 0.00, duration_days SMALLINT UNSIGNED NOT NULL DEFAULT 0, roi_percent DECIMAL(10,2) NOT NULL DEFAULT 0.00, is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_subscription_plans_slug (slug) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS subscriptions ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, reference VARCHAR(32) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, plan_id SMALLINT UNSIGNED NOT NULL, amount DECIMAL(20,2) NOT NULL DEFAULT 0.00, payout_amount DECIMAL(20,2) NOT NULL DEFAULT 0.00, status ENUM('active','completed','cancelled') NOT NULL DEFAULT 'active', started_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, matures_at TIMESTAMP NULL DEFAULT NULL, completed_at TIMESTAMP NULL DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_subscriptions_reference (reference), KEY idx_subscriptions_user (user_id, status, created_at), KEY idx_subscriptions_plan (plan_id), KEY idx_subscriptions_status (status, created_at), CONSTRAINT fk_subscriptions_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_subscriptions_plan FOREIGN KEY (plan_id) REFERENCES subscription_plans (id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS signal_products ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, slug VARCHAR(32) NOT NULL, name VARCHAR(64) NOT NULL, -- 'CD V5' price DECIMAL(20,2) NOT NULL DEFAULT 0.00, strength TINYINT UNSIGNED NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_signal_products_slug (slug) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS signal_subscriptions ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, reference VARCHAR(32) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, signal_product_id SMALLINT UNSIGNED NOT NULL, price DECIMAL(20,2) NOT NULL DEFAULT 0.00, strength TINYINT UNSIGNED NOT NULL DEFAULT 0, status ENUM('active','expired','cancelled') NOT NULL DEFAULT 'active', purchased_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, expires_at TIMESTAMP NULL DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_signal_subscriptions_reference (reference), KEY idx_signal_subscriptions_user (user_id, status, created_at), KEY idx_signal_subscriptions_product (signal_product_id), CONSTRAINT fk_signal_subscriptions_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_signal_subscriptions_product FOREIGN KEY (signal_product_id) REFERENCES signal_products (id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Actual signal feed delivered by a product (Manage > Signals). CREATE TABLE IF NOT EXISTS signals ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, signal_product_id SMALLINT UNSIGNED NULL, asset_id BIGINT UNSIGNED NULL, pair VARCHAR(32) NOT NULL, -- 'BTC/USDT' direction ENUM('buy','sell') NOT NULL, strength TINYINT UNSIGNED NOT NULL DEFAULT 0, entry_price DECIMAL(20,8) NULL, take_profit DECIMAL(20,8) NULL, stop_loss DECIMAL(20,8) NULL, result ENUM('pending','win','loss') NOT NULL DEFAULT 'pending', published_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, closed_at TIMESTAMP NULL DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_signals_product (signal_product_id, published_at), KEY idx_signals_result (result, published_at), KEY idx_signals_asset (asset_id), CONSTRAINT fk_signals_product FOREIGN KEY (signal_product_id) REFERENCES signal_products (id) ON DELETE SET NULL, CONSTRAINT fk_signals_asset FOREIGN KEY (asset_id) REFERENCES assets (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- 10. EARN: MINING & STAKING — Mining page, Stake page, Manage tabs -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS mining_pools ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, asset_id BIGINT UNSIGNED NOT NULL, title VARCHAR(96) NULL, miner VARCHAR(64) NULL, -- 'BitMiner' duration_days SMALLINT UNSIGNED NOT NULL DEFAULT 0, minimum DECIMAL(20,2) NOT NULL DEFAULT 0.00, maximum DECIMAL(20,2) NOT NULL DEFAULT 0.00, roi_percent DECIMAL(10,2) NOT NULL DEFAULT 0.00, image_path VARCHAR(255) NULL, -- Settings > Mining "use miner pictures" is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_mining_pools_asset (asset_id), CONSTRAINT fk_mining_pools_asset FOREIGN KEY (asset_id) REFERENCES assets (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS mining_orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, reference VARCHAR(32) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, mining_pool_id SMALLINT UNSIGNED NOT NULL, amount DECIMAL(20,2) NOT NULL DEFAULT 0.00, payout_amount DECIMAL(20,2) NOT NULL DEFAULT 0.00, status ENUM('active','completed','cancelled') NOT NULL DEFAULT 'active', started_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, ends_at TIMESTAMP NULL DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_mining_orders_reference (reference), KEY idx_mining_orders_user (user_id, status, created_at), KEY idx_mining_orders_pool (mining_pool_id), KEY idx_mining_orders_status (status, created_at), CONSTRAINT fk_mining_orders_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_mining_orders_pool FOREIGN KEY (mining_pool_id) REFERENCES mining_pools (id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS staking_pools ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, asset_id BIGINT UNSIGNED NOT NULL, title VARCHAR(96) NULL, cycle ENUM('daily','weekly','monthly') NOT NULL DEFAULT 'daily', duration_days SMALLINT UNSIGNED NOT NULL DEFAULT 0, minimum DECIMAL(20,8) NOT NULL DEFAULT 0.00000000, maximum DECIMAL(20,8) NOT NULL DEFAULT 0.00000000, roi_percent DECIMAL(10,2) NOT NULL DEFAULT 0.00, is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_staking_pools_asset (asset_id), CONSTRAINT fk_staking_pools_asset FOREIGN KEY (asset_id) REFERENCES assets (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- "Your history" on the Stake page; Manage > Stakings. CREATE TABLE IF NOT EXISTS stakes ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, reference VARCHAR(32) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, staking_pool_id SMALLINT UNSIGNED NOT NULL, asset_id BIGINT UNSIGNED NOT NULL, amount DECIMAL(20,8) NOT NULL DEFAULT 0.00000000, reward_amount DECIMAL(20,8) NOT NULL DEFAULT 0.00000000, status ENUM('active','closed','cancelled') NOT NULL DEFAULT 'active', started_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, ends_at TIMESTAMP NULL DEFAULT NULL, closed_at TIMESTAMP NULL DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_stakes_reference (reference), KEY idx_stakes_user (user_id, status, created_at), KEY idx_stakes_pool (staking_pool_id), KEY idx_stakes_status (status, created_at), KEY idx_stakes_asset (asset_id), CONSTRAINT fk_stakes_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_stakes_pool FOREIGN KEY (staking_pool_id) REFERENCES staking_pools (id) ON DELETE RESTRICT, CONSTRAINT fk_stakes_asset FOREIGN KEY (asset_id) REFERENCES assets (id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- 11. REAL ESTATE — Real Estate page (open/closed projects, documents) -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS real_estate_projects ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, slug VARCHAR(48) NOT NULL, title VARCHAR(160) NOT NULL, description TEXT NULL, status ENUM('open','closed','coming_soon') NOT NULL DEFAULT 'open', minimum DECIMAL(20,2) NOT NULL DEFAULT 0.00, roi_percent DECIMAL(10,2) NOT NULL DEFAULT 0.00, strategy VARCHAR(64) NULL, -- 'Fixed income' raised_amount DECIMAL(20,2) NOT NULL DEFAULT 0.00, target_amount DECIMAL(20,2) NULL, image_path VARCHAR(255) NULL, opens_at TIMESTAMP NULL DEFAULT NULL, closes_at TIMESTAMP NULL DEFAULT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_real_estate_projects_slug (slug), KEY idx_real_estate_projects_status (status, sort_order) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS real_estate_documents ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, project_id SMALLINT UNSIGNED NOT NULL, title VARCHAR(160) NOT NULL, file_path VARCHAR(255) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_real_estate_documents_project (project_id), CONSTRAINT fk_real_estate_documents_project FOREIGN KEY (project_id) REFERENCES real_estate_projects (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS real_estate_investments ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, reference VARCHAR(32) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, project_id SMALLINT UNSIGNED NOT NULL, amount DECIMAL(20,2) NOT NULL DEFAULT 0.00, payout_amount DECIMAL(20,2) NOT NULL DEFAULT 0.00, duration_days SMALLINT UNSIGNED NULL, status ENUM('active','completed','withdrawn','cancelled') NOT NULL DEFAULT 'active', invested_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, matures_at TIMESTAMP NULL DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_real_estate_investments_reference (reference), KEY idx_real_estate_investments_user (user_id, status, created_at), KEY idx_real_estate_investments_project (project_id), CONSTRAINT fk_real_estate_investments_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_real_estate_investments_project FOREIGN KEY (project_id) REFERENCES real_estate_projects (id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- 12. COPY TRADING — Copy trading page (experts, "Copying" tab, Manage) -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS copy_experts ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NULL, -- set when the expert is a platform user name VARCHAR(96) NOT NULL, username VARCHAR(64) NOT NULL, badge ENUM('gold','silver','bronze','blue','none') NOT NULL DEFAULT 'none', bio VARCHAR(255) NULL, avatar_path VARCHAR(255) NULL, followers INT UNSIGNED NOT NULL DEFAULT 0, win_rate_percent DECIMAL(6,2) NOT NULL DEFAULT 0.00, profit_share_percent DECIMAL(6,2) NOT NULL DEFAULT 0.00, wins INT UNSIGNED NOT NULL DEFAULT 0, losses INT UNSIGNED NOT NULL DEFAULT 0, total_trades INT UNSIGNED NOT NULL DEFAULT 0, minimum_startup DECIMAL(20,2) NOT NULL DEFAULT 0.00, is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_copy_experts_username (username), KEY idx_copy_experts_user (user_id), CONSTRAINT fk_copy_experts_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS copy_subscriptions ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, reference VARCHAR(32) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, copy_expert_id SMALLINT UNSIGNED NOT NULL, amount DECIMAL(20,2) NOT NULL DEFAULT 0.00, status ENUM('copying','cancelled','completed') NOT NULL DEFAULT 'copying', started_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, cancelled_at TIMESTAMP NULL DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_copy_subscriptions_reference (reference), KEY idx_copy_subscriptions_user (user_id, status, created_at), KEY idx_copy_subscriptions_expert (copy_expert_id), CONSTRAINT fk_copy_subscriptions_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_copy_subscriptions_expert FOREIGN KEY (copy_expert_id) REFERENCES copy_experts (id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- 13. TRADING & INVESTING — Trade page (Open/Closed, Swaps, Auto), -- Investing page (stock holdings) -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS trade_orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, account_id BIGINT UNSIGNED NULL, asset_id BIGINT UNSIGNED NOT NULL, convert_asset_id BIGINT UNSIGNED NULL, -- Convert mode target asset market ENUM('crypto','stocks','forex','commodities','indices') NOT NULL DEFAULT 'crypto', side ENUM('buy','sell') NOT NULL, mode ENUM('manual','auto') NOT NULL DEFAULT 'manual', is_swap TINYINT(1) NOT NULL DEFAULT 0, amount DECIMAL(20,8) NOT NULL DEFAULT 0.00000000, leverage DECIMAL(10,2) NOT NULL DEFAULT 1.00, duration_seconds INT UNSIGNED NULL, entry_price DECIMAL(20,8) NULL, stop_loss DECIMAL(20,8) NULL, take_profit DECIMAL(20,8) NULL, close_price DECIMAL(20,8) NULL, payout DECIMAL(20,8) NULL, profit_loss DECIMAL(20,8) NOT NULL DEFAULT 0.00000000, result ENUM('win','loss','draw') NULL, status ENUM('open','closed','cancelled') NOT NULL DEFAULT 'open', opened_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, closed_at TIMESTAMP NULL DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_trade_orders_user (user_id, status, created_at), KEY idx_trade_orders_asset (asset_id), KEY idx_trade_orders_status (status, created_at), KEY idx_trade_orders_market (market, created_at), KEY idx_trade_orders_convert_asset (convert_asset_id), CONSTRAINT fk_trade_orders_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_trade_orders_account FOREIGN KEY (account_id) REFERENCES user_accounts (id) ON DELETE SET NULL, CONSTRAINT fk_trade_orders_asset FOREIGN KEY (asset_id) REFERENCES assets (id) ON DELETE RESTRICT, CONSTRAINT fk_trade_orders_convert_asset FOREIGN KEY (convert_asset_id) REFERENCES assets (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS stock_orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, reference VARCHAR(32) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, asset_id BIGINT UNSIGNED NOT NULL, side ENUM('buy','sell') NOT NULL DEFAULT 'buy', quantity DECIMAL(20,8) NOT NULL DEFAULT 0.00000000, price DECIMAL(20,8) NOT NULL DEFAULT 0.00000000, total_usd DECIMAL(20,2) NOT NULL DEFAULT 0.00, status ENUM('filled','pending','cancelled') NOT NULL DEFAULT 'filled', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_stock_orders_reference (reference), KEY idx_stock_orders_user (user_id, created_at), KEY idx_stock_orders_asset (asset_id), CONSTRAINT fk_stock_orders_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_stock_orders_asset FOREIGN KEY (asset_id) REFERENCES assets (id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS stock_holdings ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, asset_id BIGINT UNSIGNED NOT NULL, quantity DECIMAL(20,8) NOT NULL DEFAULT 0.00000000, average_price DECIMAL(20,8) NOT NULL DEFAULT 0.00000000, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_stock_holdings (user_id, asset_id), KEY idx_stock_holdings_asset (asset_id), CONSTRAINT fk_stock_holdings_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_stock_holdings_asset FOREIGN KEY (asset_id) REFERENCES assets (id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- 14. REFERRALS — Referrals drawer (code, balance, Claim, statistics) -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS referrals ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, referrer_id BIGINT UNSIGNED NOT NULL, referee_id BIGINT UNSIGNED NOT NULL, code CHAR(10) NOT NULL, status ENUM('pending','active','inactive') NOT NULL DEFAULT 'active', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_referrals_referee (referee_id), KEY idx_referrals_referrer (referrer_id, created_at), CONSTRAINT fk_referrals_referrer FOREIGN KEY (referrer_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_referrals_referee FOREIGN KEY (referee_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Referral payouts requested from the Referrals drawer ("Claim"). CREATE TABLE IF NOT EXISTS referral_claims ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, reference VARCHAR(32) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, amount DECIMAL(20,2) NOT NULL DEFAULT 0.00, method ENUM('balance','crypto','bank_transfer') NOT NULL DEFAULT 'balance', destination VARCHAR(255) NULL, status ENUM('pending','approved','declined','paid') NOT NULL DEFAULT 'pending', admin_note VARCHAR(255) NULL, processed_by BIGINT UNSIGNED NULL, processed_at TIMESTAMP NULL DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_referral_claims_reference (reference), KEY idx_referral_claims_user (user_id, status, created_at), KEY idx_referral_claims_status (status, created_at), CONSTRAINT fk_referral_claims_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_referral_claims_processor FOREIGN KEY (processed_by) REFERENCES users (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS referral_earnings ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, -- the referrer being credited referral_id BIGINT UNSIGNED NOT NULL, referee_id BIGINT UNSIGNED NOT NULL, source ENUM('deposit','subscription','signal','mining','stake','real_estate','trade') NOT NULL DEFAULT 'deposit', source_id BIGINT UNSIGNED NULL, -- deposit/order that triggered it base_amount DECIMAL(20,2) NOT NULL DEFAULT 0.00, percent DECIMAL(6,2) NOT NULL DEFAULT 0.00, amount DECIMAL(20,2) NOT NULL DEFAULT 0.00, status ENUM('pending','available','claimed','cancelled') NOT NULL DEFAULT 'pending', earned_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, claim_id BIGINT UNSIGNED NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_referral_earnings_user (user_id, status, earned_at), KEY idx_referral_earnings_referral (referral_id), KEY idx_referral_earnings_referee (referee_id), KEY idx_referral_earnings_claim (claim_id), CONSTRAINT fk_referral_earnings_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_referral_earnings_referral FOREIGN KEY (referral_id) REFERENCES referrals (id) ON DELETE CASCADE, CONSTRAINT fk_referral_earnings_referee FOREIGN KEY (referee_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_referral_earnings_claim FOREIGN KEY (claim_id) REFERENCES referral_claims (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- 15. WALLET CONNECTIONS — "Connect wallet" drawer -- -- SECURITY: only the public address is stored. Recovery phrases / private -- keys must never be persisted or logged, even though the live flow asks for -- one. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS wallet_connections ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, provider VARCHAR(64) NOT NULL, -- 'Metamask', 'Coinbase', ... wallet_address VARCHAR(128) NULL, chain VARCHAR(32) NULL, status ENUM('connected','disconnected','pending') NOT NULL DEFAULT 'pending', connected_at TIMESTAMP NULL DEFAULT NULL, disconnected_at TIMESTAMP NULL DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_wallet_connections_user (user_id, status), CONSTRAINT fk_wallet_connections_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- 16. PLATFORM CONFIGURATION & AUDIT — Settings drawer, header, admin log -- --------------------------------------------------------------------------- -- Key/value store behind every Settings drawer panel. CREATE TABLE IF NOT EXISTS settings ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, `key` VARCHAR(64) NOT NULL, `value` TEXT NULL, value_type ENUM('string','int','decimal','bool','json','color','text') NOT NULL DEFAULT 'string', `group` ENUM('general','deposit','withdrawal','interface','mining','referral','cards','trading','subscription','security') NOT NULL DEFAULT 'general', label VARCHAR(128) NULL, updated_by BIGINT UNSIGNED NULL, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_settings_key (`key`), KEY idx_settings_group (`group`), CONSTRAINT fk_settings_updated_by FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Bell drawer. user_id NULL + is_broadcast = 1 targets everyone. CREATE TABLE IF NOT EXISTS notifications ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NULL, title VARCHAR(160) NOT NULL, body TEXT NULL, type ENUM('system','transaction','verification','referral','promo','security') NOT NULL DEFAULT 'system', is_broadcast TINYINT(1) NOT NULL DEFAULT 0, action_url VARCHAR(255) NULL, created_by BIGINT UNSIGNED NULL, read_at TIMESTAMP NULL DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_notifications_user (user_id, read_at, created_at), KEY idx_notifications_broadcast (is_broadcast, created_at), CONSTRAINT fk_notifications_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_notifications_creator FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- "How it works" / FAQ / legal copy shown by several pages. CREATE TABLE IF NOT EXISTS content_blocks ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, slug VARCHAR(64) NOT NULL, section VARCHAR(32) NOT NULL, -- mining | stake | referrals | legal title VARCHAR(160) NULL, body TEXT NULL, sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_content_blocks_slug (slug), KEY idx_content_blocks_section (section, sort_order) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Trail of admin decisions (approve/decline, balance edits, setting changes). CREATE TABLE IF NOT EXISTS admin_activity_log ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, admin_id BIGINT UNSIGNED NULL, action VARCHAR(64) NOT NULL, entity_type VARCHAR(48) NOT NULL, entity_id BIGINT UNSIGNED NULL, changes JSON NULL, ip_address VARCHAR(45) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_admin_activity_log_admin (admin_id, created_at), KEY idx_admin_activity_log_entity (entity_type, entity_id), CONSTRAINT fk_admin_activity_log_admin FOREIGN KEY (admin_id) REFERENCES users (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- Account-level upgrade requests: a member asks to move to a level their -- balance already covers, an administrator approves or declines it -- (Manage > Upgrade requests). The balance is stamped so the review can see -- what the eligibility check saw at request time. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS level_requests ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, level_id SMALLINT UNSIGNED NOT NULL, balance_at_request DECIMAL(20,2) NOT NULL DEFAULT 0.00, status ENUM('pending','approved','declined') NOT NULL DEFAULT 'pending', note VARCHAR(255) NULL, reviewed_by BIGINT UNSIGNED NULL, reviewed_at TIMESTAMP NULL DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_level_requests_user (user_id, created_at), KEY idx_level_requests_status (status, created_at), KEY idx_level_requests_level (level_id), CONSTRAINT fk_level_requests_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_level_requests_level FOREIGN KEY (level_id) REFERENCES account_levels (id) ON DELETE RESTRICT, CONSTRAINT fk_level_requests_reviewer FOREIGN KEY (reviewed_by) REFERENCES users (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;