-- ===================================================================== -- Provider-backed financial modules — run AFTER schema.sql -- Adds: reserved accounts, dynamic accounts, virtual cards + their -- transaction log, crypto deposit addresses + deposits, beneficiaries, -- and a bill_payments log. All money columns are BIGINT minor units, -- consistent with schema.sql. Every table links back to `providers` -- so provider-switching is reflected in stored data, not just code. -- ===================================================================== SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- --------------------------------------------------------------------- -- reserved_accounts — permanent, KYC-gated virtual accounts -- --------------------------------------------------------------------- CREATE TABLE reserved_accounts ( id CHAR(36) NOT NULL PRIMARY KEY, user_id CHAR(36) NOT NULL, wallet_id CHAR(36) NOT NULL, provider_id CHAR(36) NULL, account_reference VARCHAR(150) NOT NULL COMMENT 'unique reference we send the provider; used to reconcile webhooks', bank_code VARCHAR(20) NOT NULL, bank_name VARCHAR(150) NOT NULL, account_number VARCHAR(30) NOT NULL, account_name VARCHAR(150) NOT NULL, currency CHAR(3) NOT NULL DEFAULT 'NGN', status ENUM('active','inactive','closed') NOT NULL DEFAULT 'active', kyc_verified TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, UNIQUE KEY uq_reserved_accounts_reference (account_reference), KEY idx_reserved_accounts_user (user_id), KEY idx_reserved_accounts_account_number (account_number), CONSTRAINT fk_reserved_accounts_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_reserved_accounts_wallet FOREIGN KEY (wallet_id) REFERENCES wallets(id) ON DELETE RESTRICT, CONSTRAINT fk_reserved_accounts_provider FOREIGN KEY (provider_id) REFERENCES providers(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- dynamic_accounts — temporary, auto-expiring, single-use accounts -- --------------------------------------------------------------------- CREATE TABLE dynamic_accounts ( id CHAR(36) NOT NULL PRIMARY KEY, user_id CHAR(36) NOT NULL, wallet_id CHAR(36) NOT NULL, provider_id CHAR(36) NULL, reference VARCHAR(150) NOT NULL, account_number VARCHAR(30) NOT NULL, account_name VARCHAR(150) NOT NULL, bank_code VARCHAR(20) NOT NULL, bank_name VARCHAR(150) NOT NULL, currency CHAR(3) NOT NULL DEFAULT 'NGN', amount_minor BIGINT NOT NULL, fee_minor BIGINT NOT NULL DEFAULT 0, total_payable_minor BIGINT NOT NULL, status ENUM('pending','paid','expired','cancelled') NOT NULL DEFAULT 'pending', expires_at DATETIME NOT NULL, settled_transaction_id CHAR(36) NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, UNIQUE KEY uq_dynamic_accounts_reference (reference), KEY idx_dynamic_accounts_user (user_id), KEY idx_dynamic_accounts_status_expiry (status, expires_at), KEY idx_dynamic_accounts_account_number (account_number), CONSTRAINT fk_dynamic_accounts_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_dynamic_accounts_wallet FOREIGN KEY (wallet_id) REFERENCES wallets(id) ON DELETE RESTRICT, CONSTRAINT fk_dynamic_accounts_provider FOREIGN KEY (provider_id) REFERENCES providers(id) ON DELETE SET NULL, CONSTRAINT fk_dynamic_accounts_txn FOREIGN KEY (settled_transaction_id) REFERENCES transactions(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- virtual_cards — USD card issuance. Funding is always NGN -> USD -- converted at debit time; we never hold a USD wallet balance. -- --------------------------------------------------------------------- CREATE TABLE virtual_cards ( id CHAR(36) NOT NULL PRIMARY KEY, user_id CHAR(36) NOT NULL, provider_id CHAR(36) NULL, provider_customer_code VARCHAR(100) NULL, card_code VARCHAR(100) NOT NULL COMMENT 'provider card identifier', card_brand VARCHAR(30) NOT NULL DEFAULT 'Visa', card_type VARCHAR(30) NOT NULL DEFAULT 'virtual', card_name VARCHAR(150) NULL, last4 CHAR(4) NULL, expiry_month_year VARCHAR(7) NULL COMMENT 'MM/YYYY', status ENUM('pending','active','frozen','closed') NOT NULL DEFAULT 'pending', cached_balance_minor_usd BIGINT NOT NULL DEFAULT 0 COMMENT 'display cache only; provider is source of truth', created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, UNIQUE KEY uq_virtual_cards_code (card_code), KEY idx_virtual_cards_user (user_id), CONSTRAINT fk_virtual_cards_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_virtual_cards_provider FOREIGN KEY (provider_id) REFERENCES providers(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- card_transactions — statement lines pulled from / pushed by provider -- --------------------------------------------------------------------- CREATE TABLE card_transactions ( id CHAR(36) NOT NULL PRIMARY KEY, virtual_card_id CHAR(36) NOT NULL, wallet_transaction_id CHAR(36) NULL COMMENT 'the NGN-side transactions.id for topups/withdrawals we funded', provider_reference VARCHAR(150) NULL, type ENUM('topup','withdrawal','debit','credit','reversal','declined') NOT NULL, amount_minor_usd BIGINT NOT NULL, fee_minor_usd BIGINT NOT NULL DEFAULT 0, description VARCHAR(255) NULL, status VARCHAR(30) NOT NULL DEFAULT 'success', occurred_at DATETIME NOT NULL, created_at DATETIME NOT NULL, KEY idx_card_transactions_card (virtual_card_id, occurred_at), KEY idx_card_transactions_provider_ref (provider_reference), CONSTRAINT fk_card_transactions_card FOREIGN KEY (virtual_card_id) REFERENCES virtual_cards(id) ON DELETE CASCADE, CONSTRAINT fk_card_transactions_wallet_txn FOREIGN KEY (wallet_transaction_id) REFERENCES transactions(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- crypto_addresses — USDT (TRC20) deposit addresses per user. -- No balance column: crypto never rests as a balance in this system. -- --------------------------------------------------------------------- CREATE TABLE crypto_addresses ( id CHAR(36) NOT NULL PRIMARY KEY, user_id CHAR(36) NOT NULL, provider_id CHAR(36) NULL, asset_type VARCHAR(30) NOT NULL DEFAULT 'usdt', network VARCHAR(30) NOT NULL DEFAULT 'TRC20', address VARCHAR(150) NOT NULL, provider_code VARCHAR(100) NULL, status ENUM('active','inactive') NOT NULL DEFAULT 'active', created_at DATETIME NOT NULL, UNIQUE KEY uq_crypto_addresses_address (address), KEY idx_crypto_addresses_user (user_id), CONSTRAINT fk_crypto_addresses_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_crypto_addresses_provider FOREIGN KEY (provider_id) REFERENCES providers(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- crypto_deposits — one row per on-chain deposit, tracked through: -- webhook received -> confirmed on-chain -> swapped to NGN -> wallet -- credited. `ngn_amount_minor` and `wallet_ledger_id` are only ever -- populated after the swap; there is no intermediate crypto balance. -- --------------------------------------------------------------------- CREATE TABLE crypto_deposits ( id CHAR(36) NOT NULL PRIMARY KEY, user_id CHAR(36) NOT NULL, crypto_address_id CHAR(36) NOT NULL, tx_hash VARCHAR(150) NOT NULL, asset_type VARCHAR(30) NOT NULL DEFAULT 'usdt', network VARCHAR(30) NOT NULL DEFAULT 'TRC20', amount_crypto DECIMAL(24,8) NOT NULL COMMENT 'raw on-chain amount; informational only, never a spendable balance', confirmations INT UNSIGNED NOT NULL DEFAULT 0, ngn_amount_minor BIGINT NULL COMMENT 'set once swapped to NGN', swap_reference VARCHAR(150) NULL, wallet_ledger_id CHAR(36) NULL, status ENUM('pending','confirmed','converted','credited','failed') NOT NULL DEFAULT 'pending', created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, UNIQUE KEY uq_crypto_deposits_hash (tx_hash), KEY idx_crypto_deposits_user (user_id), KEY idx_crypto_deposits_status (status), CONSTRAINT fk_crypto_deposits_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_crypto_deposits_address FOREIGN KEY (crypto_address_id) REFERENCES crypto_addresses(id) ON DELETE RESTRICT, CONSTRAINT fk_crypto_deposits_ledger FOREIGN KEY (wallet_ledger_id) REFERENCES wallet_ledger(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- beneficiaries — saved transfer recipients for faster repeat payouts -- --------------------------------------------------------------------- CREATE TABLE beneficiaries ( id CHAR(36) NOT NULL PRIMARY KEY, user_id CHAR(36) NOT NULL, bank_code VARCHAR(20) NOT NULL, bank_name VARCHAR(150) NOT NULL, account_number VARCHAR(30) NOT NULL, account_name VARCHAR(150) NOT NULL, currency CHAR(3) NOT NULL DEFAULT 'NGN', country CHAR(2) NOT NULL DEFAULT 'NG', nickname VARCHAR(100) NULL, created_at DATETIME NOT NULL, UNIQUE KEY uq_beneficiary_user_account (user_id, bank_code, account_number), KEY idx_beneficiaries_user (user_id), CONSTRAINT fk_beneficiaries_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- bill_payments — airtime/data/electricity/cable purchase log, -- one-to-one with a `transactions` row (type='bill_payment') that -- owns the wallet debit; this table holds the provider-specific detail. -- --------------------------------------------------------------------- CREATE TABLE bill_payments ( id CHAR(36) NOT NULL PRIMARY KEY, user_id CHAR(36) NOT NULL, transaction_id CHAR(36) NOT NULL, category ENUM('airtime','data','electricity','cabletv') NOT NULL, provider_code VARCHAR(50) NOT NULL, provider_plan_code VARCHAR(50) NULL, recipient VARCHAR(100) NOT NULL COMMENT 'phone number, meter number, or smartcard number', customer_name VARCHAR(150) NULL, amount_minor BIGINT NOT NULL, provider_reference VARCHAR(150) NULL, provider_token VARCHAR(255) NULL COMMENT 'e.g. electricity prepaid token', status ENUM('pending','success','failed') NOT NULL DEFAULT 'pending', created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, KEY idx_bill_payments_user (user_id, created_at), KEY idx_bill_payments_category (category), CONSTRAINT fk_bill_payments_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_bill_payments_transaction FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; SET FOREIGN_KEY_CHECKS = 1; -- Seed the Cashwyre provider row so provider_id FKs resolve immediately. INSERT INTO providers (id, name, slug, type, is_active, created_at, updated_at) VALUES (UUID(), 'Cashwyre', 'cashwyre', 'payment', 1, NOW(), NOW()); INSERT INTO system_settings (id, `key`, value, type, description, created_at, updated_at) VALUES (UUID(), 'active_financial_provider', 'cashwyre', 'string', 'Slug of the FinancialProviderInterface implementation currently in use', NOW(), NOW());