-- Drixem Palm Oil B2B E-Marketplace — MySQL 8 / MariaDB 10.4+ schema -- Engine InnoDB, utf8mb4. Loaded by database/migrations/2026_01_01_000000_create_drixem_schema.php -- (or import directly through phpMyAdmin). SET NAMES utf8mb4; -- --------------------------------------------------------------------------- -- USERS (sellers, buyers, affiliates, admins — one table, role-based) -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS users ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150) NOT NULL, email VARCHAR(190) NOT NULL, password VARCHAR(255) NOT NULL, role ENUM('seller','buyer','affiliate','admin') NOT NULL, status ENUM('pending','approved','rejected','suspended') NOT NULL DEFAULT 'pending', seller_type ENUM('mill','refinery') NULL, company_name VARCHAR(190) NULL, company_reg_no VARCHAR(80) NULL, country CHAR(2) NOT NULL, phone VARCHAR(40) NULL, affiliate_code VARCHAR(16) NULL, referred_by_affiliate_id BIGINT UNSIGNED NULL, approved_by BIGINT UNSIGNED NULL, approved_at TIMESTAMP NULL, rejection_reason VARCHAR(500) NULL, email_verified_at TIMESTAMP NULL, remember_token VARCHAR(100) NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, UNIQUE KEY users_email_unique (email), UNIQUE KEY users_affiliate_code_unique (affiliate_code), KEY users_role_status_idx (role, status), CONSTRAINT users_referrer_fk FOREIGN KEY (referred_by_affiliate_id) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT users_approver_fk FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS password_reset_tokens ( email VARCHAR(190) PRIMARY KEY, token VARCHAR(255) NOT NULL, created_at TIMESTAMP NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- KYB / KYC DOCUMENTS (files live in private storage, never web-accessible) -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS kyc_documents ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, doc_type VARCHAR(40) NOT NULL, -- see config/drixem.php 'kyc_documents' original_name VARCHAR(255) NOT NULL, storage_path VARCHAR(255) NOT NULL, -- relative to storage/app/private mime_type VARCHAR(100) NOT NULL, size_bytes INT UNSIGNED NOT NULL, sha256 CHAR(64) NOT NULL, status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending', expires_on DATE NULL, reviewed_by BIGINT UNSIGNED NULL, reviewed_at TIMESTAMP NULL, review_note VARCHAR(500) NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, KEY kyc_user_type_idx (user_id, doc_type), KEY kyc_status_idx (status), CONSTRAINT kyc_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT kyc_reviewer_fk FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- MILL-DIRECT INVENTORY LISTINGS -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS mills_inventory ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, seller_id BIGINT UNSIGNED NOT NULL, product ENUM('CPO','RBD_OLEIN','PKO','PFAD','RBD_PALM_OIL','RBD_STEARIN') NOT NULL, title VARCHAR(190) NOT NULL, grade VARCHAR(60) NULL, -- e.g. CP8, CP10, Standard ffa_pct DECIMAL(5,2) NULL, -- Free Fatty Acid % moisture_impurities_pct DECIMAL(5,2) NULL, -- M&I % iodine_value DECIMAL(5,2) NULL, -- IV (Wijs) cert_rspo TINYINT(1) NOT NULL DEFAULT 0, cert_mspo TINYINT(1) NOT NULL DEFAULT 0, cert_ispo TINYINT(1) NOT NULL DEFAULT 0, cert_halal TINYINT(1) NOT NULL DEFAULT 0, origin_country CHAR(2) NOT NULL, -- MY / ID / TH ... loading_port VARCHAR(120) NULL, available_mt DECIMAL(12,2) NOT NULL, moq_mt DECIMAL(12,2) NOT NULL, incoterm ENUM('FOB','CIF','CNF') NOT NULL, price_usd_per_mt DECIMAL(10,2) NULL, -- set by the seller ONLY price_valid_until DATE NULL, packaging VARCHAR(120) NULL, -- bulk, flexitank, drums, jerrycan financing_exim_available TINYINT(1) NOT NULL DEFAULT 0, status ENUM('draft','active','paused','sold_out') NOT NULL DEFAULT 'active', created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, KEY inv_browse_idx (status, product, origin_country, incoterm), KEY inv_quality_idx (ffa_pct, moisture_impurities_pct, iodine_value), KEY inv_moq_idx (moq_mt), KEY inv_seller_idx (seller_id), CONSTRAINT inv_seller_fk FOREIGN KEY (seller_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- RFQs, MATCHES, QUOTES -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS rfqs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, buyer_id BIGINT UNSIGNED NOT NULL, inventory_id BIGINT UNSIGNED NULL, -- set when raised from a listing product ENUM('CPO','RBD_OLEIN','PKO','PFAD','RBD_PALM_OIL','RBD_STEARIN') NOT NULL, grade VARCHAR(60) NULL, volume_mt DECIMAL(12,2) NOT NULL, max_ffa_pct DECIMAL(5,2) NULL, require_rspo TINYINT(1) NOT NULL DEFAULT 0, require_mspo TINYINT(1) NOT NULL DEFAULT 0, require_halal TINYINT(1) NOT NULL DEFAULT 0, preferred_origin CHAR(2) NULL, incoterm ENUM('FOB','CIF','CNF') NOT NULL, destination_port VARCHAR(120) NOT NULL, destination_country CHAR(2) NOT NULL, delivery_from DATE NOT NULL, delivery_to DATE NOT NULL, payment_method ENUM('LC','SBLC','TT','EXIM_FINANCING') NOT NULL, target_price_usd_per_mt DECIMAL(10,2) NULL, notes TEXT NULL, status ENUM('open','quoted','awarded','cancelled','expired') NOT NULL DEFAULT 'open', expires_at TIMESTAMP NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, KEY rfq_buyer_idx (buyer_id, status), KEY rfq_match_idx (status, product), CONSTRAINT rfq_buyer_fk FOREIGN KEY (buyer_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT rfq_inventory_fk FOREIGN KEY (inventory_id) REFERENCES mills_inventory(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS rfq_matches ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, rfq_id BIGINT UNSIGNED NOT NULL, seller_id BIGINT UNSIGNED NOT NULL, inventory_id BIGINT UNSIGNED NULL, score SMALLINT UNSIGNED NOT NULL DEFAULT 0, notified_at TIMESTAMP NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, UNIQUE KEY rfq_match_unique (rfq_id, seller_id), KEY rfq_match_seller_idx (seller_id), CONSTRAINT rfqm_rfq_fk FOREIGN KEY (rfq_id) REFERENCES rfqs(id) ON DELETE CASCADE, CONSTRAINT rfqm_seller_fk FOREIGN KEY (seller_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT rfqm_inventory_fk FOREIGN KEY (inventory_id) REFERENCES mills_inventory(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS quotes ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, rfq_id BIGINT UNSIGNED NOT NULL, seller_id BIGINT UNSIGNED NOT NULL, inventory_id BIGINT UNSIGNED NULL, price_usd_per_mt DECIMAL(10,2) NOT NULL, -- authored by the seller; immutable after acceptance volume_mt DECIMAL(12,2) NOT NULL, incoterm ENUM('FOB','CIF','CNF') NOT NULL, loading_port VARCHAR(120) NULL, shipment_from DATE NULL, shipment_to DATE NULL, payment_terms VARCHAR(255) NULL, valid_until DATE NOT NULL, notes TEXT NULL, status ENUM('submitted','accepted','declined','withdrawn','expired') NOT NULL DEFAULT 'submitted', created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, UNIQUE KEY quote_rfq_seller_unique (rfq_id, seller_id), KEY quote_seller_idx (seller_id, status), CONSTRAINT quote_rfq_fk FOREIGN KEY (rfq_id) REFERENCES rfqs(id) ON DELETE CASCADE, CONSTRAINT quote_seller_fk FOREIGN KEY (seller_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT quote_inventory_fk FOREIGN KEY (inventory_id) REFERENCES mills_inventory(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- DEAL ROOMS, CHAT, DOCUMENT VAULT -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS deal_rooms ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, reference VARCHAR(24) NULL, -- DRX-2026-000001 rfq_id BIGINT UNSIGNED NOT NULL, quote_id BIGINT UNSIGNED NOT NULL, buyer_id BIGINT UNSIGNED NOT NULL, seller_id BIGINT UNSIGNED NOT NULL, affiliate_id BIGINT UNSIGNED NULL, -- attribution only; has no access to price fields product VARCHAR(20) NOT NULL, volume_mt DECIMAL(12,2) NOT NULL, price_usd_per_mt DECIMAL(10,2) NOT NULL, -- copied from the accepted quote total_value_usd DECIMAL(16,2) NOT NULL, incoterm ENUM('FOB','CIF','CNF') NOT NULL, payment_method ENUM('LC','SBLC','TT','EXIM_FINANCING') NOT NULL, financing_status ENUM('none','requested','submitted','approved','declined') NOT NULL DEFAULT 'none', financing_ref VARCHAR(80) NULL, stage ENUM('negotiation','pi_issued','spa_signed','payment_secured','shipped','completed','cancelled','disputed') NOT NULL DEFAULT 'negotiation', bl_number VARCHAR(60) NULL, vessel_name VARCHAR(120) NULL, etd DATE NULL, eta DATE NULL, completed_at TIMESTAMP NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, UNIQUE KEY deal_reference_unique (reference), UNIQUE KEY deal_quote_unique (quote_id), KEY deal_buyer_idx (buyer_id, stage), KEY deal_seller_idx (seller_id, stage), KEY deal_affiliate_idx (affiliate_id), CONSTRAINT deal_rfq_fk FOREIGN KEY (rfq_id) REFERENCES rfqs(id), CONSTRAINT deal_quote_fk FOREIGN KEY (quote_id) REFERENCES quotes(id), CONSTRAINT deal_buyer_fk FOREIGN KEY (buyer_id) REFERENCES users(id), CONSTRAINT deal_seller_fk FOREIGN KEY (seller_id) REFERENCES users(id), CONSTRAINT deal_affiliate_fk FOREIGN KEY (affiliate_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS deal_messages ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, deal_room_id BIGINT UNSIGNED NOT NULL, sender_id BIGINT UNSIGNED NULL, -- NULL = system event body TEXT NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- append-only, no updates KEY dmsg_room_idx (deal_room_id, id), CONSTRAINT dmsg_room_fk FOREIGN KEY (deal_room_id) REFERENCES deal_rooms(id) ON DELETE CASCADE, CONSTRAINT dmsg_sender_fk FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS deal_documents ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, deal_room_id BIGINT UNSIGNED NOT NULL, uploaded_by BIGINT UNSIGNED NULL, doc_type ENUM('PI','SPA','BL','LC','SBLC','COA','INVOICE','OTHER') NOT NULL, original_name VARCHAR(255) NOT NULL, storage_path VARCHAR(255) NOT NULL, sha256 CHAR(64) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY ddoc_room_idx (deal_room_id), CONSTRAINT ddoc_room_fk FOREIGN KEY (deal_room_id) REFERENCES deal_rooms(id) ON DELETE CASCADE, CONSTRAINT ddoc_user_fk FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- AFFILIATE COMMISSIONS (paid by the platform, never added to the deal price) -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS affiliate_commissions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, deal_room_id BIGINT UNSIGNED NOT NULL, affiliate_id BIGINT UNSIGNED NOT NULL, volume_mt DECIMAL(12,2) NOT NULL, deal_value_usd DECIMAL(16,2) NOT NULL, rate_usd_per_mt DECIMAL(8,2) NOT NULL, amount_usd DECIMAL(14,2) NOT NULL, status ENUM('payable','paid','void') NOT NULL DEFAULT 'payable', paid_at TIMESTAMP NULL, paid_by BIGINT UNSIGNED NULL, payout_reference VARCHAR(120) NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, UNIQUE KEY comm_deal_unique (deal_room_id), -- one commission per deal, ever KEY comm_affiliate_idx (affiliate_id, status), CONSTRAINT comm_deal_fk FOREIGN KEY (deal_room_id) REFERENCES deal_rooms(id), CONSTRAINT comm_affiliate_fk FOREIGN KEY (affiliate_id) REFERENCES users(id), CONSTRAINT comm_payer_fk FOREIGN KEY (paid_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- LIVE / HISTORICAL PRICES (benchmark references + platform indications) -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS live_prices ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, product ENUM('CPO','RBD_OLEIN','PKO','PFAD','RBD_PALM_OIL','RBD_STEARIN') NOT NULL, market ENUM('MPOB','BMD','PLATFORM') NOT NULL, contract_month VARCHAR(7) NOT NULL DEFAULT '', -- e.g. 2027-01 for futures; '' for spot price DECIMAL(10,2) NOT NULL, currency CHAR(3) NOT NULL DEFAULT 'MYR', change_abs DECIMAL(10,2) NULL, price_date DATE NOT NULL, source_note VARCHAR(190) NULL, created_by BIGINT UNSIGNED NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, UNIQUE KEY price_point_unique (product, market, contract_month, price_date), KEY price_latest_idx (price_date, product), CONSTRAINT price_creator_fk FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- SUPPORT: in-app notifications + admin audit trail -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS notifications ( id CHAR(36) PRIMARY KEY, type VARCHAR(190) NOT NULL, notifiable_type VARCHAR(190) NOT NULL, notifiable_id BIGINT UNSIGNED NOT NULL, data TEXT NOT NULL, read_at TIMESTAMP NULL, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, KEY notif_notifiable_idx (notifiable_type, notifiable_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS audit_logs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, actor_id BIGINT UNSIGNED NULL, action VARCHAR(80) NOT NULL, subject_type VARCHAR(80) NULL, subject_id BIGINT UNSIGNED NULL, meta JSON NULL, ip VARCHAR(45) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY audit_subject_idx (subject_type, subject_id), KEY audit_actor_idx (actor_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;