-- ============================================================ -- MVP SCHEMA + FIFO — 19 tables -- Changes from MVP base: -- items : removed purchase_cost (replaced by layers) -- inventory_cost_layers : NEW table -- inventory_adjustment_lines : NEW table (needed to track layer consumption on adjustments) -- ============================================================ SET FOREIGN_KEY_CHECKS = 0; SET SQL_MODE = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; -- ------------------------------------------------------------ -- 1. companies -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS `companies` ( `company_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(200) NOT NULL, `email` VARCHAR(150) NULL, `phone` VARCHAR(30) NULL, `address` TEXT NULL, `tax_number` VARCHAR(50) NULL COMMENT 'TIN', `currency` CHAR(3) NOT NULL DEFAULT 'LKR', `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 (`company_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 2. users -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS `users` ( `user_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `company_id` BIGINT UNSIGNED NOT NULL, `name` VARCHAR(150) NOT NULL, `email` VARCHAR(150) NOT NULL, `password_hash` VARCHAR(255) NOT NULL, `role` ENUM('ADMIN','MANAGER','CASHIER','ACCOUNTANT') NOT NULL DEFAULT 'CASHIER', `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 (`user_id`), UNIQUE KEY `uq_users_company_email` (`company_id`, `email`), KEY `idx_users_company` (`company_id`), CONSTRAINT `fk_users_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 3. accounts (Chart of Accounts) -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS `accounts` ( `account_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `company_id` BIGINT UNSIGNED NOT NULL, `account_number` VARCHAR(20) NOT NULL, `name` VARCHAR(200) NOT NULL, `account_type` ENUM('ASSET','LIABILITY','EQUITY','INCOME','EXPENSE') NOT NULL, `account_sub_type` ENUM( 'CASH','BANK','ACCOUNTS_RECEIVABLE', 'INVENTORY_ASSET','FIXED_ASSET', 'ACCOUNTS_PAYABLE','VAT_PAYABLE', 'INPUT_VAT','SALES_REVENUE','COGS', 'OPERATING_EXPENSE','WRITE_OFF','OTHER' ) NOT NULL DEFAULT 'OTHER', `parent_account_id` BIGINT UNSIGNED NULL, `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 (`account_id`), UNIQUE KEY `uq_account_number_company` (`company_id`, `account_number`), KEY `idx_accounts_company` (`company_id`), KEY `idx_accounts_parent` (`parent_account_id`), CONSTRAINT `fk_accounts_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE, CONSTRAINT `fk_accounts_parent` FOREIGN KEY (`parent_account_id`) REFERENCES `accounts` (`account_id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 4. customers -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS `customers` ( `customer_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `company_id` BIGINT UNSIGNED NOT NULL, `display_name` VARCHAR(200) NOT NULL, `email` VARCHAR(150) NULL, `phone` VARCHAR(30) NULL, `address` TEXT NULL, `customer_type` ENUM('RETAIL','WHOLESALE','CORPORATE','GOVERNMENT') NOT NULL DEFAULT 'RETAIL', `payment_terms` ENUM('NET_30','NET_60','NET_90','IMMEDIATE') NOT NULL DEFAULT 'IMMEDIATE', `balance` DECIMAL(20,4) NOT NULL DEFAULT 0.0000 COMMENT 'Running AR balance', `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 (`customer_id`), KEY `idx_customers_company` (`company_id`), CONSTRAINT `fk_customers_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 5. vendors -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS `vendors` ( `vendor_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `company_id` BIGINT UNSIGNED NOT NULL, `display_name` VARCHAR(200) NOT NULL, `email` VARCHAR(150) NULL, `phone` VARCHAR(30) NULL, `address` TEXT NULL, `payment_terms` ENUM('NET_30','NET_60','NET_90','IMMEDIATE') NOT NULL DEFAULT 'NET_30', `balance` DECIMAL(20,4) NOT NULL DEFAULT 0.0000 COMMENT 'Running AP balance', `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 (`vendor_id`), KEY `idx_vendors_company` (`company_id`), CONSTRAINT `fk_vendors_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 6. tax_codes -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS `tax_codes` ( `tax_code_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `company_id` BIGINT UNSIGNED NOT NULL, `name` VARCHAR(50) NOT NULL COMMENT 'e.g. VAT18, EXEMPT, ZERO_RATED', `rate_percent` DECIMAL(6,2) NOT NULL DEFAULT 0.00, `is_taxable` TINYINT(1) NOT NULL DEFAULT 1, `active` TINYINT(1) NOT NULL DEFAULT 1, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`tax_code_id`), UNIQUE KEY `uq_tax_code_company` (`company_id`, `name`), KEY `idx_tax_codes_company` (`company_id`), CONSTRAINT `fk_tax_codes_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 7. items -- FIFO change: removed purchase_cost -- Cost is now derived from inventory_cost_layers, not a -- single average field. Use SUM(qty_remaining * unit_cost) -- / SUM(qty_remaining) for a display cost if needed. -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS `items` ( `item_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `company_id` BIGINT UNSIGNED NOT NULL, `name` VARCHAR(250) NOT NULL, `sku` VARCHAR(80) NOT NULL, `item_type` ENUM('INVENTORY','SERVICE','NON_INVENTORY') NOT NULL DEFAULT 'INVENTORY', `unit_of_measure` ENUM('UNIT','BOX','SET','PAIR','KG','LITRE','METRE') NOT NULL DEFAULT 'UNIT', `unit_price` DECIMAL(20,4) NOT NULL DEFAULT 0.0000 COMMENT 'Selling price', `qty_on_hand` DECIMAL(14,4) NOT NULL DEFAULT 0.0000 COMMENT 'Updated on every bill receive and invoice post', `reorder_point` DECIMAL(14,4) NOT NULL DEFAULT 0.0000, `taxable` TINYINT(1) NOT NULL DEFAULT 1, `active` TINYINT(1) NOT NULL DEFAULT 1, `income_account_id` BIGINT UNSIGNED NULL COMMENT 'Sales Revenue account', `expense_account_id` BIGINT UNSIGNED NULL COMMENT 'COGS account', `asset_account_id` BIGINT UNSIGNED NULL COMMENT 'Inventory Asset account', `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`item_id`), UNIQUE KEY `uq_item_sku_company` (`company_id`, `sku`), KEY `idx_items_company` (`company_id`), KEY `idx_items_income_account` (`income_account_id`), KEY `idx_items_expense_account` (`expense_account_id`), KEY `idx_items_asset_account` (`asset_account_id`), CONSTRAINT `fk_items_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE, CONSTRAINT `fk_items_income_account` FOREIGN KEY (`income_account_id`) REFERENCES `accounts` (`account_id`) ON DELETE SET NULL, CONSTRAINT `fk_items_expense_account` FOREIGN KEY (`expense_account_id`) REFERENCES `accounts` (`account_id`) ON DELETE SET NULL, CONSTRAINT `fk_items_asset_account` FOREIGN KEY (`asset_account_id`) REFERENCES `accounts` (`account_id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 8. transactions (master registry spine) -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS `transactions` ( `txn_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `company_id` BIGINT UNSIGNED NOT NULL, `txn_type` ENUM('BILL','INVOICE','PAYMENT','BILL_PAYMENT','JOURNAL_ENTRY','EXPENSE') NOT NULL, `status` ENUM('DRAFT','OPEN','PARTIAL','PAID','VOIDED') NOT NULL DEFAULT 'DRAFT', `created_by_user_id` BIGINT UNSIGNED NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`txn_id`), KEY `idx_txn_company` (`company_id`), KEY `idx_txn_type` (`txn_type`), KEY `idx_txn_status` (`status`), KEY `idx_txn_created_by` (`created_by_user_id`), CONSTRAINT `fk_txn_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE, CONSTRAINT `fk_txn_created_by` FOREIGN KEY (`created_by_user_id`) REFERENCES `users` (`user_id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 9. inventory_cost_layers *** FIFO TABLE *** -- -- Created when: bill is marked received_into_inventory = 1 -- Consumed when: invoice is posted (oldest date_acquired first) -- One row per purchase batch per item. -- qty_remaining counts down as stock is sold. -- When qty_remaining = 0 the layer is fully consumed. -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS `inventory_cost_layers` ( `layer_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `item_id` BIGINT UNSIGNED NOT NULL, `company_id` BIGINT UNSIGNED NOT NULL, `reference_txn_id` BIGINT UNSIGNED NOT NULL COMMENT 'The bill txn_id that created this layer', `bill_line_id` BIGINT UNSIGNED NOT NULL COMMENT 'Exact bill line — for void/reversal tracing', `qty_purchased` DECIMAL(14,4) NOT NULL COMMENT 'Original qty received in this batch', `qty_remaining` DECIMAL(14,4) NOT NULL COMMENT 'Decremented on each sale — 0 = fully consumed', `unit_cost` DECIMAL(20,4) NOT NULL COMMENT 'Cost per unit for this batch', `total_cost` DECIMAL(20,4) NOT NULL COMMENT 'qty_purchased × unit_cost — for fast reporting', `date_acquired` DATE NOT NULL COMMENT 'Bill txn_date — determines FIFO order', `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`layer_id`), KEY `idx_layers_item_date` (`item_id`, `date_acquired`), -- FIFO consumption query KEY `idx_layers_company` (`company_id`), KEY `idx_layers_ref_txn` (`reference_txn_id`), KEY `idx_layers_bill_line` (`bill_line_id`), CONSTRAINT `fk_layers_item` FOREIGN KEY (`item_id`) REFERENCES `items` (`item_id`) ON DELETE CASCADE, CONSTRAINT `fk_layers_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE, CONSTRAINT `fk_layers_ref_txn` FOREIGN KEY (`reference_txn_id`) REFERENCES `transactions` (`txn_id`) ON DELETE CASCADE, CONSTRAINT `fk_layers_bill_line` FOREIGN KEY (`bill_line_id`) REFERENCES `bill_lines` (`line_id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 10. invoices -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS `invoices` ( `invoice_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `txn_id` BIGINT UNSIGNED NOT NULL, `company_id` BIGINT UNSIGNED NOT NULL, `invoice_number` VARCHAR(50) NOT NULL, `customer_id` BIGINT UNSIGNED NOT NULL, `customer_snapshot` JSON NOT NULL COMMENT 'Frozen customer name/address at invoice time', `txn_date` DATE NOT NULL, `due_date` DATE NULL, `payment_terms` ENUM('NET_30','NET_60','NET_90','IMMEDIATE') NOT NULL DEFAULT 'IMMEDIATE', `sub_total` DECIMAL(20,4) NOT NULL DEFAULT 0.0000, `discount_total` DECIMAL(20,4) NOT NULL DEFAULT 0.0000, `tax_total` DECIMAL(20,4) NOT NULL DEFAULT 0.0000, `total_amount` DECIMAL(20,4) NOT NULL DEFAULT 0.0000, `balance` DECIMAL(20,4) NOT NULL DEFAULT 0.0000, `status` ENUM('DRAFT','OPEN','PARTIAL','PAID','VOIDED') NOT NULL DEFAULT 'DRAFT', `memo` TEXT NULL COMMENT 'Printed on invoice', `notes` TEXT NULL COMMENT 'Internal notes only', `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`invoice_id`), UNIQUE KEY `uq_invoice_number_company` (`company_id`, `invoice_number`), KEY `idx_invoice_txn` (`txn_id`), KEY `idx_invoice_customer` (`customer_id`), KEY `idx_invoice_company` (`company_id`), KEY `idx_invoice_status` (`status`), KEY `idx_invoice_due_date` (`due_date`), CONSTRAINT `fk_invoice_txn` FOREIGN KEY (`txn_id`) REFERENCES `transactions` (`txn_id`) ON DELETE CASCADE, CONSTRAINT `fk_invoice_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`) ON DELETE RESTRICT, CONSTRAINT `fk_invoice_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 11. invoice_lines -- FIFO change: added cogs_amount (computed during FIFO -- consumption, stored here so voiding can reverse exactly) -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS `invoice_lines` ( `line_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `invoice_id` BIGINT UNSIGNED NOT NULL, `txn_id` BIGINT UNSIGNED NOT NULL, `item_id` BIGINT UNSIGNED NULL, `item_snapshot` JSON NOT NULL COMMENT 'Frozen item name/price at invoice time', `line_number` SMALLINT UNSIGNED NOT NULL, `qty` DECIMAL(14,4) NOT NULL, `unit_price` DECIMAL(20,4) NOT NULL, `discount_percent` DECIMAL(6,2) NOT NULL DEFAULT 0.00, `discount_amount` DECIMAL(20,4) NOT NULL DEFAULT 0.0000, `tax_code_id` BIGINT UNSIGNED NULL, `tax_amount` DECIMAL(20,4) NOT NULL DEFAULT 0.0000, `line_total` DECIMAL(20,4) NOT NULL DEFAULT 0.0000, `cogs_amount` DECIMAL(20,4) NOT NULL DEFAULT 0.0000 COMMENT 'FIFO cost consumed for this line — used for COGS journal entry and void reversal', PRIMARY KEY (`line_id`), KEY `idx_inv_lines_invoice` (`invoice_id`), KEY `idx_inv_lines_item` (`item_id`), KEY `idx_inv_lines_tax_code` (`tax_code_id`), CONSTRAINT `fk_inv_lines_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`invoice_id`) ON DELETE CASCADE, CONSTRAINT `fk_inv_lines_txn` FOREIGN KEY (`txn_id`) REFERENCES `transactions` (`txn_id`) ON DELETE CASCADE, CONSTRAINT `fk_inv_lines_item` FOREIGN KEY (`item_id`) REFERENCES `items` (`item_id`) ON DELETE RESTRICT, CONSTRAINT `fk_inv_lines_tax_code` FOREIGN KEY (`tax_code_id`) REFERENCES `tax_codes` (`tax_code_id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 12. invoice_line_layers *** FIFO TABLE *** -- -- Records exactly which cost layers were consumed per -- invoice line and how many units came from each layer. -- Essential for: COGS accuracy, void reversal, audit trail. -- One invoice line may span multiple layers. -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS `invoice_line_layers` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `invoice_line_id` BIGINT UNSIGNED NOT NULL, `layer_id` BIGINT UNSIGNED NOT NULL, `qty_consumed` DECIMAL(14,4) NOT NULL COMMENT 'Units taken from this layer for this line', `unit_cost` DECIMAL(20,4) NOT NULL COMMENT 'Snapshot of layer unit_cost at time of sale', `total_cost` DECIMAL(20,4) NOT NULL COMMENT 'qty_consumed × unit_cost', `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_ill_invoice_line` (`invoice_line_id`), KEY `idx_ill_layer` (`layer_id`), CONSTRAINT `fk_ill_invoice_line` FOREIGN KEY (`invoice_line_id`) REFERENCES `invoice_lines` (`line_id`) ON DELETE CASCADE, CONSTRAINT `fk_ill_layer` FOREIGN KEY (`layer_id`) REFERENCES `inventory_cost_layers` (`layer_id`) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 13. bills -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS `bills` ( `bill_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `txn_id` BIGINT UNSIGNED NOT NULL, `company_id` BIGINT UNSIGNED NOT NULL, `vendor_id` BIGINT UNSIGNED NOT NULL, `vendor_snapshot` JSON NOT NULL COMMENT 'Frozen vendor name/address at bill time', `bill_number` VARCHAR(50) NOT NULL, `txn_date` DATE NOT NULL, `due_date` DATE NULL, `sub_total` DECIMAL(20,4) NOT NULL DEFAULT 0.0000, `discount_total` DECIMAL(20,4) NOT NULL DEFAULT 0.0000, `tax_total` DECIMAL(20,4) NOT NULL DEFAULT 0.0000, `total_amount` DECIMAL(20,4) NOT NULL DEFAULT 0.0000, `balance` DECIMAL(20,4) NOT NULL DEFAULT 0.0000, `status` ENUM('DRAFT','OPEN','PARTIAL','PAID','VOIDED') NOT NULL DEFAULT 'DRAFT', `received_into_inventory` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Flip to 1 when stock received — creates cost layers and locks qty edits', `memo` TEXT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`bill_id`), UNIQUE KEY `uq_bill_number_company` (`company_id`, `bill_number`), KEY `idx_bill_txn` (`txn_id`), KEY `idx_bill_vendor` (`vendor_id`), KEY `idx_bill_company` (`company_id`), KEY `idx_bill_status` (`status`), CONSTRAINT `fk_bill_txn` FOREIGN KEY (`txn_id`) REFERENCES `transactions` (`txn_id`) ON DELETE CASCADE, CONSTRAINT `fk_bill_vendor` FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`vendor_id`) ON DELETE RESTRICT, CONSTRAINT `fk_bill_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 14. bill_lines -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS `bill_lines` ( `line_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `bill_id` BIGINT UNSIGNED NOT NULL, `txn_id` BIGINT UNSIGNED NOT NULL, `item_id` BIGINT UNSIGNED NULL, `item_snapshot` JSON NOT NULL COMMENT 'Frozen item data at bill time', `qty` DECIMAL(14,4) NOT NULL, `unit_price` DECIMAL(20,4) NOT NULL, `discount_percent` DECIMAL(6,2) NOT NULL DEFAULT 0.00, `tax_code_id` BIGINT UNSIGNED NULL, `tax_amount` DECIMAL(20,4) NOT NULL DEFAULT 0.0000, `line_total` DECIMAL(20,4) NOT NULL DEFAULT 0.0000, `notes` VARCHAR(500) NULL, PRIMARY KEY (`line_id`), KEY `idx_bill_lines_bill` (`bill_id`), KEY `idx_bill_lines_item` (`item_id`), KEY `idx_bill_lines_tax_code` (`tax_code_id`), CONSTRAINT `fk_bill_lines_bill` FOREIGN KEY (`bill_id`) REFERENCES `bills` (`bill_id`) ON DELETE CASCADE, CONSTRAINT `fk_bill_lines_txn` FOREIGN KEY (`txn_id`) REFERENCES `transactions` (`txn_id`) ON DELETE CASCADE, CONSTRAINT `fk_bill_lines_item` FOREIGN KEY (`item_id`) REFERENCES `items` (`item_id`) ON DELETE RESTRICT, CONSTRAINT `fk_bill_lines_tax_code` FOREIGN KEY (`tax_code_id`) REFERENCES `tax_codes` (`tax_code_id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 15. payments -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS `payments` ( `payment_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `txn_id` BIGINT UNSIGNED NOT NULL, `company_id` BIGINT UNSIGNED NOT NULL, `payment_number` VARCHAR(50) NOT NULL, `customer_id` BIGINT UNSIGNED NOT NULL, `txn_date` DATE NOT NULL, `total_amount` DECIMAL(20,4) NOT NULL, `payment_method` ENUM('CASH','CARD','BANK_TRANSFER','CHEQUE','ONLINE') NOT NULL DEFAULT 'CASH', `ref_number` VARCHAR(100) NULL COMMENT 'Cheque no / transfer ref', `memo` TEXT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`payment_id`), UNIQUE KEY `uq_payment_number_company` (`company_id`, `payment_number`), KEY `idx_payment_txn` (`txn_id`), KEY `idx_payment_customer` (`customer_id`), KEY `idx_payment_company` (`company_id`), CONSTRAINT `fk_payment_txn` FOREIGN KEY (`txn_id`) REFERENCES `transactions` (`txn_id`) ON DELETE CASCADE, CONSTRAINT `fk_payment_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`) ON DELETE RESTRICT, CONSTRAINT `fk_payment_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 16. payment_allocations -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS `payment_allocations` ( `allocation_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `payment_txn_id` BIGINT UNSIGNED NOT NULL, `invoice_txn_id` BIGINT UNSIGNED NOT NULL, `amount_applied` DECIMAL(20,4) NOT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`allocation_id`), KEY `idx_pa_payment` (`payment_txn_id`), KEY `idx_pa_invoice` (`invoice_txn_id`), CONSTRAINT `fk_pa_payment` FOREIGN KEY (`payment_txn_id`) REFERENCES `transactions` (`txn_id`) ON DELETE CASCADE, CONSTRAINT `fk_pa_invoice` FOREIGN KEY (`invoice_txn_id`) REFERENCES `transactions` (`txn_id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 17. bill_payments -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS `bill_payments` ( `bp_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `txn_id` BIGINT UNSIGNED NOT NULL, `company_id` BIGINT UNSIGNED NOT NULL, `payment_number` VARCHAR(50) NOT NULL, `vendor_id` BIGINT UNSIGNED NOT NULL, `txn_date` DATE NOT NULL, `total_amount` DECIMAL(20,4) NOT NULL, `payment_method` ENUM('CASH','CARD','BANK_TRANSFER','CHEQUE','ONLINE') NOT NULL DEFAULT 'CASH', `ref_number` VARCHAR(100) NULL, `memo` TEXT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`bp_id`), UNIQUE KEY `uq_bp_number_company` (`company_id`, `payment_number`), KEY `idx_bp_txn` (`txn_id`), KEY `idx_bp_vendor` (`vendor_id`), KEY `idx_bp_company` (`company_id`), CONSTRAINT `fk_bp_txn` FOREIGN KEY (`txn_id`) REFERENCES `transactions` (`txn_id`) ON DELETE CASCADE, CONSTRAINT `fk_bp_vendor` FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`vendor_id`) ON DELETE RESTRICT, CONSTRAINT `fk_bp_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 18. bill_payment_allocations -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS `bill_payment_allocations` ( `allocation_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `bp_txn_id` BIGINT UNSIGNED NOT NULL, `bill_txn_id` BIGINT UNSIGNED NOT NULL, `amount_applied` DECIMAL(20,4) NOT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`allocation_id`), KEY `idx_bpa_bp` (`bp_txn_id`), KEY `idx_bpa_bill` (`bill_txn_id`), CONSTRAINT `fk_bpa_bp` FOREIGN KEY (`bp_txn_id`) REFERENCES `transactions` (`txn_id`) ON DELETE CASCADE, CONSTRAINT `fk_bpa_bill` FOREIGN KEY (`bill_txn_id`) REFERENCES `transactions` (`txn_id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 19. journal_entries -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS `journal_entries` ( `je_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `txn_id` BIGINT UNSIGNED NOT NULL, `company_id` BIGINT UNSIGNED NOT NULL, `je_number` VARCHAR(50) NOT NULL, `txn_date` DATE NOT NULL, `reference_txn_id` BIGINT UNSIGNED NULL COMMENT 'Which invoice/bill triggered this JE', `memo` TEXT NULL, `is_auto_generated` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = system, 0 = manual', `created_by_user_id` BIGINT UNSIGNED NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`je_id`), UNIQUE KEY `uq_je_number_company` (`company_id`, `je_number`), KEY `idx_je_txn` (`txn_id`), KEY `idx_je_company` (`company_id`), KEY `idx_je_ref_txn` (`reference_txn_id`), CONSTRAINT `fk_je_txn` FOREIGN KEY (`txn_id`) REFERENCES `transactions` (`txn_id`) ON DELETE CASCADE, CONSTRAINT `fk_je_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE, CONSTRAINT `fk_je_ref_txn` FOREIGN KEY (`reference_txn_id`) REFERENCES `transactions` (`txn_id`) ON DELETE SET NULL, CONSTRAINT `fk_je_created_by` FOREIGN KEY (`created_by_user_id`) REFERENCES `users` (`user_id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ------------------------------------------------------------ -- 20. journal_entry_lines -- ------------------------------------------------------------ CREATE TABLE IF NOT EXISTS `journal_entry_lines` ( `jel_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `je_id` BIGINT UNSIGNED NOT NULL, `txn_id` BIGINT UNSIGNED NOT NULL, `line_number` SMALLINT UNSIGNED NOT NULL, `account_id` BIGINT UNSIGNED NOT NULL, `posting_type` ENUM('DEBIT','CREDIT') NOT NULL, `amount` DECIMAL(20,4) NOT NULL, `entity_type` ENUM('CUSTOMER','VENDOR') NULL COMMENT 'Sub-ledger drill-down', `entity_id` BIGINT UNSIGNED NULL COMMENT 'customer_id or vendor_id', `description` VARCHAR(500) NULL, PRIMARY KEY (`jel_id`), KEY `idx_jel_je` (`je_id`), KEY `idx_jel_account` (`account_id`), CONSTRAINT `fk_jel_je` FOREIGN KEY (`je_id`) REFERENCES `journal_entries` (`je_id`) ON DELETE CASCADE, CONSTRAINT `fk_jel_txn` FOREIGN KEY (`txn_id`) REFERENCES `transactions` (`txn_id`) ON DELETE CASCADE, CONSTRAINT `fk_jel_account` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`account_id`) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; SET FOREIGN_KEY_CHECKS = 1; -- ============================================================ -- CHANGELOG vs MVP base schema -- ============================================================ -- items : REMOVED purchase_cost -- COGS now comes from inventory_cost_layers -- invoice_lines : ADDED cogs_amount -- Stores FIFO cost consumed — needed for -- COGS journal entry and void reversal -- inventory_cost_layers : NEW — one row per purchase batch -- qty_remaining counts down as stock sells -- invoice_line_layers : NEW — maps each invoice line to the -- exact layers it consumed (may be multiple) -- All other tables : UNCHANGED -- ============================================================ -- FIFO flow summary -- ============================================================ -- ON BILL RECEIVE (received_into_inventory flipped to 1): -- INSERT inventory_cost_layers (one row per bill_line) -- UPDATE items.qty_on_hand += qty -- -- ON INVOICE POST: -- For each invoice_line with item_type = INVENTORY: -- SELECT layers WHERE item_id = ? AND qty_remaining > 0 -- ORDER BY date_acquired ASC -- oldest first -- LOCK FOR UPDATE -- prevent race conditions -- Consume layers oldest-first until qty satisfied -- INSERT invoice_line_layers (one row per layer touched) -- UPDATE inventory_cost_layers.qty_remaining -- UPDATE invoice_lines.cogs_amount = sum of layer costs -- UPDATE items.qty_on_hand -= qty -- POST journal entries: -- DR Accounts Receivable invoice total -- CR Sales Revenue invoice total -- DR Cost of Goods Sold cogs_amount -- CR Inventory Asset cogs_amount -- -- ON INVOICE VOID: -- SELECT invoice_line_layers WHERE invoice_line_id IN (...) -- RESTORE inventory_cost_layers.qty_remaining += qty_consumed -- UPDATE items.qty_on_hand += qty -- POST reversing journal entries (swap DR/CR) -- ============================================================ -- Table count : 20 (18 MVP + 2 FIFO) -- ============================================================