-- ===================================================================== -- Fleet Service Link (NTA ADMIN) — MySQL schema -- MySQL 8.0+ / MariaDB 10.5+ Engine: InnoDB Charset: utf8mb4 -- Usage: mysql -u root -p < schema.sql -- ===================================================================== DROP DATABASE IF EXISTS fleet_service_link; CREATE DATABASE fleet_service_link CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE fleet_service_link; -- --------------------------------------------------------------------- -- Roles & users (RBAC) -- --------------------------------------------------------------------- CREATE TABLE roles ( id VARCHAR(32) NOT NULL PRIMARY KEY, name VARCHAR(80) NOT NULL, slug VARCHAR(80) NOT NULL UNIQUE, description VARCHAR(255) NOT NULL DEFAULT '', permissions JSON NOT NULL, is_system TINYINT(1) NOT NULL DEFAULT 0 ) ENGINE=InnoDB; CREATE TABLE users ( id VARCHAR(32) NOT NULL PRIMARY KEY, name VARCHAR(120) NOT NULL, email VARCHAR(160) NOT NULL UNIQUE, phone VARCHAR(40) NOT NULL DEFAULT '', password_hash VARCHAR(255) NOT NULL, status ENUM('active','inactive','suspended','pending') NOT NULL DEFAULT 'active', avatar_url VARCHAR(255) NULL, last_login_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_users_status (status) ) ENGINE=InnoDB; -- A user can hold multiple roles; effective permissions are the union. CREATE TABLE user_roles ( user_id VARCHAR(32) NOT NULL, role_id VARCHAR(32) NOT NULL, PRIMARY KEY (user_id, role_id), CONSTRAINT fk_ur_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_ur_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE password_resets ( id BIGINT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(160) NOT NULL, token CHAR(64) NOT NULL, used TINYINT(1) NOT NULL DEFAULT 0, expires_at DATETIME NOT NULL, INDEX idx_reset_token (token) ) ENGINE=InnoDB; CREATE TABLE login_otps ( id BIGINT AUTO_INCREMENT PRIMARY KEY, challenge_id CHAR(32) NOT NULL, user_id CHAR(36) NOT NULL, code_hash CHAR(64) NOT NULL, attempts INT NOT NULL DEFAULT 0, remember TINYINT(1) NOT NULL DEFAULT 0, used TINYINT(1) NOT NULL DEFAULT 0, expires_at DATETIME NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_login_otp_challenge (challenge_id), CONSTRAINT fk_otp_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- Core fleet entities -- --------------------------------------------------------------------- CREATE TABLE companies ( id VARCHAR(32) NOT NULL PRIMARY KEY, code VARCHAR(32) NOT NULL UNIQUE, name VARCHAR(160) NOT NULL, registration_number VARCHAR(60) NOT NULL DEFAULT '', contact_person VARCHAR(120) NOT NULL DEFAULT '', email VARCHAR(160) NOT NULL DEFAULT '', billing_email VARCHAR(160) NOT NULL DEFAULT '', phone VARCHAR(40) NOT NULL DEFAULT '', address VARCHAR(255) NOT NULL DEFAULT '', city VARCHAR(80) NOT NULL DEFAULT '', state VARCHAR(80) NOT NULL DEFAULT '', country VARCHAR(80) NOT NULL DEFAULT 'India', postal_code VARCHAR(20) NOT NULL DEFAULT '', tax_number VARCHAR(60) NOT NULL DEFAULT '', credit_limit DECIMAL(14,2) NOT NULL DEFAULT 0, payment_terms VARCHAR(40) NOT NULL DEFAULT 'Net 30', status ENUM('active','inactive','suspended','pending') NOT NULL DEFAULT 'active', notes TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_companies_status (status) ) ENGINE=InnoDB; CREATE TABLE suppliers ( id VARCHAR(32) NOT NULL PRIMARY KEY, code VARCHAR(32) NOT NULL UNIQUE, name VARCHAR(160) NOT NULL, type ENUM('Fuel Station','Service Center','Workshop','Parts Supplier','Other') NOT NULL DEFAULT 'Fuel Station', registration_number VARCHAR(60) NOT NULL DEFAULT '', tax_number VARCHAR(60) NOT NULL DEFAULT '', contact_person VARCHAR(120) NOT NULL DEFAULT '', email VARCHAR(160) NOT NULL DEFAULT '', phone VARCHAR(40) NOT NULL DEFAULT '', address VARCHAR(255) NOT NULL DEFAULT '', city VARCHAR(80) NOT NULL DEFAULT '', state VARCHAR(80) NOT NULL DEFAULT '', country VARCHAR(80) NOT NULL DEFAULT 'India', payment_terms VARCHAR(40) NOT NULL DEFAULT 'Net 30', bank_details VARCHAR(255) NOT NULL DEFAULT '', status ENUM('active','inactive','suspended','pending') NOT NULL DEFAULT 'active', notes TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB; CREATE TABLE drivers ( id VARCHAR(32) NOT NULL PRIMARY KEY, code VARCHAR(32) NOT NULL UNIQUE, first_name VARCHAR(80) NOT NULL, last_name VARCHAR(80) NOT NULL, email VARCHAR(160) NOT NULL DEFAULT '', phone VARCHAR(40) NOT NULL DEFAULT '', date_of_birth DATE NULL, license_number VARCHAR(60) NOT NULL DEFAULT '', license_expiry DATE NULL, address VARCHAR(255) NOT NULL DEFAULT '', company_id VARCHAR(32) NULL, vehicle_id VARCHAR(32) NULL, card_id VARCHAR(32) NULL, status ENUM('active','inactive','suspended','pending') NOT NULL DEFAULT 'active', notes TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_drivers_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL, INDEX idx_drivers_company (company_id) ) ENGINE=InnoDB; CREATE TABLE vehicles ( id VARCHAR(32) NOT NULL PRIMARY KEY, code VARCHAR(32) NOT NULL UNIQUE, registration_number VARCHAR(40) NOT NULL, company_id VARCHAR(32) NULL, type VARCHAR(40) NOT NULL DEFAULT 'Truck', make VARCHAR(60) NOT NULL DEFAULT '', model VARCHAR(60) NOT NULL DEFAULT '', year SMALLINT NOT NULL DEFAULT 2020, fuel_type ENUM('Diesel','Petrol','Electric','Hybrid','CNG') NOT NULL DEFAULT 'Diesel', vin VARCHAR(60) NOT NULL DEFAULT '', driver_id VARCHAR(32) NULL, card_id VARCHAR(32) NULL, status ENUM('active','inactive','suspended','pending') NOT NULL DEFAULT 'active', notes TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_vehicles_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL, INDEX idx_vehicles_company (company_id), INDEX idx_vehicles_reg (registration_number) ) ENGINE=InnoDB; CREATE TABLE fleet_cards ( id VARCHAR(32) NOT NULL PRIMARY KEY, card_number VARCHAR(32) NOT NULL, masked VARCHAR(32) NOT NULL, company_id VARCHAR(32) NULL, driver_id VARCHAR(32) NULL, vehicle_id VARCHAR(32) NULL, card_type ENUM('Fuel','Universal','Service') NOT NULL DEFAULT 'Fuel', issue_date DATE NOT NULL, expiry_date DATE NOT NULL, status ENUM('active','blocked','suspended','expired','lost','cancelled','pending') NOT NULL DEFAULT 'pending', spending_limit DECIMAL(14,2) NOT NULL DEFAULT 0, used_amount DECIMAL(14,2) NOT NULL DEFAULT 0, CONSTRAINT fk_cards_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL, CONSTRAINT fk_cards_driver FOREIGN KEY (driver_id) REFERENCES drivers(id) ON DELETE SET NULL, CONSTRAINT fk_cards_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE SET NULL, INDEX idx_cards_status (status) ) ENGINE=InnoDB; CREATE TABLE card_status_history ( id BIGINT AUTO_INCREMENT PRIMARY KEY, card_id VARCHAR(32) NOT NULL, status VARCHAR(20) NOT NULL, changed_by VARCHAR(120) NOT NULL DEFAULT 'system', note VARCHAR(255) NOT NULL DEFAULT '', changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_history_card FOREIGN KEY (card_id) REFERENCES fleet_cards(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- Cross links added after both tables exist ALTER TABLE drivers ADD CONSTRAINT fk_drivers_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE SET NULL, ADD CONSTRAINT fk_drivers_card FOREIGN KEY (card_id) REFERENCES fleet_cards(id) ON DELETE SET NULL; ALTER TABLE vehicles ADD CONSTRAINT fk_vehicles_driver FOREIGN KEY (driver_id) REFERENCES drivers(id) ON DELETE SET NULL, ADD CONSTRAINT fk_vehicles_card FOREIGN KEY (card_id) REFERENCES fleet_cards(id) ON DELETE SET NULL; -- --------------------------------------------------------------------- -- Transactions -- --------------------------------------------------------------------- CREATE TABLE transactions ( id VARCHAR(32) NOT NULL PRIMARY KEY, reference VARCHAR(40) NOT NULL UNIQUE, occurred_at DATETIME NOT NULL, company_id VARCHAR(32) NULL, driver_id VARCHAR(32) NULL, card_id VARCHAR(32) NULL, vehicle_id VARCHAR(32) NULL, supplier_id VARCHAR(32) NULL, location VARCHAR(160) NOT NULL DEFAULT '', type ENUM('Fuel','Service','Maintenance','Toll','Other') NOT NULL DEFAULT 'Fuel', product VARCHAR(80) NOT NULL DEFAULT '', quantity DECIMAL(12,2) NOT NULL DEFAULT 0, unit_price DECIMAL(12,2) NOT NULL DEFAULT 0, tax DECIMAL(12,2) NOT NULL DEFAULT 0, total DECIMAL(14,2) NOT NULL DEFAULT 0, status ENUM('completed','pending','failed','reversed','refunded') NOT NULL DEFAULT 'completed', CONSTRAINT fk_txn_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL, CONSTRAINT fk_txn_driver FOREIGN KEY (driver_id) REFERENCES drivers(id) ON DELETE SET NULL, CONSTRAINT fk_txn_card FOREIGN KEY (card_id) REFERENCES fleet_cards(id) ON DELETE SET NULL, CONSTRAINT fk_txn_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE SET NULL, CONSTRAINT fk_txn_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL, INDEX idx_txn_date (occurred_at), INDEX idx_txn_company (company_id), INDEX idx_txn_status (status) ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- Billing -- --------------------------------------------------------------------- CREATE TABLE invoices ( id VARCHAR(32) NOT NULL PRIMARY KEY, number VARCHAR(40) NOT NULL UNIQUE, company_id VARCHAR(32) NULL, invoice_date DATE NOT NULL, due_date DATE NOT NULL, billing_period VARCHAR(40) NOT NULL DEFAULT '', subtotal DECIMAL(14,2) NOT NULL DEFAULT 0, tax DECIMAL(14,2) NOT NULL DEFAULT 0, discount DECIMAL(14,2) NOT NULL DEFAULT 0, total DECIMAL(14,2) NOT NULL DEFAULT 0, paid DECIMAL(14,2) NOT NULL DEFAULT 0, outstanding DECIMAL(14,2) NOT NULL DEFAULT 0, status ENUM('draft','sent','partially_paid','paid','overdue','cancelled') NOT NULL DEFAULT 'draft', notes TEXT NULL, CONSTRAINT fk_invoice_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL, INDEX idx_invoice_status (status), INDEX idx_invoice_date (invoice_date) ) ENGINE=InnoDB; CREATE TABLE invoice_items ( id VARCHAR(48) NOT NULL PRIMARY KEY, invoice_id VARCHAR(32) NOT NULL, description VARCHAR(255) NOT NULL, quantity DECIMAL(12,2) NOT NULL DEFAULT 1, unit_price DECIMAL(12,2) NOT NULL DEFAULT 0, tax_rate DECIMAL(6,2) NOT NULL DEFAULT 0, total DECIMAL(14,2) NOT NULL DEFAULT 0, CONSTRAINT fk_items_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE payments ( id VARCHAR(32) NOT NULL PRIMARY KEY, reference VARCHAR(40) NOT NULL UNIQUE, invoice_id VARCHAR(32) NULL, company_id VARCHAR(32) NULL, paid_at DATETIME NOT NULL, amount DECIMAL(14,2) NOT NULL DEFAULT 0, method ENUM('Bank Transfer','Card','Cash','Cheque','Other') NOT NULL DEFAULT 'Bank Transfer', reference_number VARCHAR(60) NOT NULL DEFAULT '', status ENUM('completed','pending','failed') NOT NULL DEFAULT 'completed', created_by VARCHAR(120) NOT NULL DEFAULT 'system', notes TEXT NULL, CONSTRAINT fk_pay_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE SET NULL, CONSTRAINT fk_pay_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL, INDEX idx_pay_date (paid_at) ) ENGINE=InnoDB; CREATE TABLE pricing_rules ( id VARCHAR(32) NOT NULL PRIMARY KEY, code VARCHAR(32) NOT NULL UNIQUE, company_id VARCHAR(32) NULL, supplier_id VARCHAR(32) NULL, category ENUM('Fuel','Service','Product','Fee','Tax','Discount') NOT NULL DEFAULT 'Fuel', product VARCHAR(120) NOT NULL, base_price DECIMAL(12,2) NOT NULL DEFAULT 0, markup DECIMAL(6,2) NOT NULL DEFAULT 0, discount DECIMAL(6,2) NOT NULL DEFAULT 0, tax_rate DECIMAL(6,2) NOT NULL DEFAULT 0, final_price DECIMAL(12,2) NOT NULL DEFAULT 0, effective_from DATE NOT NULL, effective_to DATE NULL, status ENUM('active','inactive','scheduled') NOT NULL DEFAULT 'active', updated_by VARCHAR(120) NOT NULL DEFAULT 'system', updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_pricing_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL, CONSTRAINT fk_pricing_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- Platform tables -- --------------------------------------------------------------------- CREATE TABLE notifications ( id VARCHAR(32) NOT NULL PRIMARY KEY, title VARCHAR(160) NOT NULL, message VARCHAR(500) NOT NULL, type ENUM('company','driver','vehicle','card','invoice','payment','transaction','system') NOT NULL DEFAULT 'system', severity ENUM('info','success','warning','error') NOT NULL DEFAULT 'info', is_read TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB; CREATE TABLE audit_logs ( id VARCHAR(48) NOT NULL PRIMARY KEY, logged_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, user_name VARCHAR(120) NOT NULL, action VARCHAR(60) NOT NULL, module VARCHAR(60) NOT NULL, record VARCHAR(120) NOT NULL DEFAULT '', ip VARCHAR(45) NOT NULL DEFAULT '', description VARCHAR(500) NOT NULL DEFAULT '', INDEX idx_audit_date (logged_at) ) ENGINE=InnoDB; CREATE TABLE settings ( setting_key VARCHAR(80) NOT NULL PRIMARY KEY, setting_value VARCHAR(500) NOT NULL DEFAULT '' ) ENGINE=InnoDB;