108 lines
4.9 KiB
SQL
108 lines
4.9 KiB
SQL
-- MySQL-compatible Schema for Ghana School ERP Accounting Module
|
|
-- Translated from user-provided PostgreSQL schema.
|
|
|
|
CREATE TABLE IF NOT EXISTS `accounts` (
|
|
`id` CHAR(36) PRIMARY KEY,
|
|
`account_code` VARCHAR(50) UNIQUE NOT NULL,
|
|
`account_name` VARCHAR(255) NOT NULL,
|
|
`account_type` VARCHAR(20) NOT NULL CHECK (`account_type` IN ('Asset', 'Liability', 'Equity', 'Revenue', 'ContraRevenue', 'Expense')),
|
|
`normal_balance` VARCHAR(6) NOT NULL CHECK (`normal_balance` IN ('Debit', 'Credit')),
|
|
`parent_account_id` CHAR(36),
|
|
`is_active` BOOLEAN NOT NULL DEFAULT TRUE,
|
|
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`parent_account_id`) REFERENCES `accounts`(`id`) ON DELETE RESTRICT
|
|
);
|
|
|
|
CREATE INDEX `idx_accounts_code` ON `accounts`(`account_code`);
|
|
CREATE INDEX `idx_accounts_parent` ON `accounts`(`parent_account_id`);
|
|
CREATE INDEX `idx_accounts_type` ON `accounts`(`account_type`);
|
|
|
|
CREATE TABLE IF NOT EXISTS `accounting_periods` (
|
|
`id` CHAR(36) PRIMARY KEY,
|
|
`name` VARCHAR(100) NOT NULL,
|
|
`start_date` DATE NOT NULL,
|
|
`end_date` DATE NOT NULL,
|
|
`is_closed` BOOLEAN NOT NULL DEFAULT FALSE,
|
|
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT `chk_period_dates` CHECK (`end_date` > `start_date`)
|
|
);
|
|
|
|
CREATE INDEX `idx_periods_dates` ON `accounting_periods`(`start_date`, `end_date`);
|
|
|
|
CREATE TABLE IF NOT EXISTS `journal_entries` (
|
|
`id` CHAR(36) PRIMARY KEY,
|
|
`entry_number` VARCHAR(50) UNIQUE NOT NULL,
|
|
`entry_date` DATE NOT NULL,
|
|
`description` TEXT,
|
|
`period_id` CHAR(36) NOT NULL,
|
|
`source_module` VARCHAR(20) NOT NULL CHECK (`source_module` IN ('Manual', 'Fees', 'Payments', 'Payroll', 'Expenses')),
|
|
`source_reference_id` CHAR(36),
|
|
`status` VARCHAR(10) NOT NULL DEFAULT 'Draft' CHECK (`status` IN ('Draft', 'Posted', 'Reversed')),
|
|
`reversed_by` CHAR(36),
|
|
`reverses_journal_id` CHAR(36),
|
|
`created_by` CHAR(36) NOT NULL,
|
|
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`posted_at` TIMESTAMP NULL,
|
|
FOREIGN KEY (`period_id`) REFERENCES `accounting_periods`(`id`) ON DELETE RESTRICT,
|
|
FOREIGN KEY (`reversed_by`) REFERENCES `journal_entries`(`id`),
|
|
FOREIGN KEY (`reverses_journal_id`) REFERENCES `journal_entries`(`id`)
|
|
);
|
|
|
|
CREATE INDEX `idx_journal_period` ON `journal_entries`(`period_id`);
|
|
CREATE INDEX `idx_journal_date` ON `journal_entries`(`entry_date`);
|
|
CREATE INDEX `idx_journal_status` ON `journal_entries`(`status`);
|
|
CREATE INDEX `idx_journal_source` ON `journal_entries`(`source_module`, `source_reference_id`);
|
|
|
|
CREATE TABLE IF NOT EXISTS `journal_lines` (
|
|
`id` CHAR(36) PRIMARY KEY,
|
|
`journal_entry_id` CHAR(36) NOT NULL,
|
|
`account_id` CHAR(36) NOT NULL,
|
|
`debit_amount` DECIMAL(14,2) NOT NULL DEFAULT 0,
|
|
`credit_amount` DECIMAL(14,2) NOT NULL DEFAULT 0,
|
|
`line_description` TEXT,
|
|
CONSTRAINT `chk_debit_or_credit` CHECK ((`debit_amount` > 0 AND `credit_amount` = 0) OR (`credit_amount` > 0 AND `debit_amount` = 0)),
|
|
CHECK (`debit_amount` >= 0),
|
|
CHECK (`credit_amount` >= 0),
|
|
FOREIGN KEY (`journal_entry_id`) REFERENCES `journal_entries`(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`account_id`) REFERENCES `accounts`(`id`) ON DELETE RESTRICT
|
|
);
|
|
|
|
CREATE INDEX `idx_lines_journal` ON `journal_lines`(`journal_entry_id`);
|
|
CREATE INDEX `idx_lines_account` ON `journal_lines`(`account_id`);
|
|
|
|
CREATE TABLE IF NOT EXISTS `subledgers` (
|
|
`id` CHAR(36) PRIMARY KEY,
|
|
`subledger_type` VARCHAR(20) NOT NULL CHECK (`subledger_type` IN ('Student', 'Vendor', 'Staff')),
|
|
`reference_id` CHAR(36) NOT NULL,
|
|
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT `unq_subledger` UNIQUE (`subledger_type`, `reference_id`)
|
|
);
|
|
|
|
CREATE INDEX `idx_subledger_ref` ON `subledgers`(`subledger_type`, `reference_id`);
|
|
|
|
CREATE TABLE IF NOT EXISTS `journal_line_subledgers` (
|
|
`id` CHAR(36) PRIMARY KEY,
|
|
`journal_line_id` CHAR(36) NOT NULL,
|
|
`subledger_id` CHAR(36) NOT NULL,
|
|
CONSTRAINT `unq_line_subledger` UNIQUE (`journal_line_id`, `subledger_id`),
|
|
FOREIGN KEY (`journal_line_id`) REFERENCES `journal_lines`(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`subledger_id`) REFERENCES `subledgers`(`id`) ON DELETE RESTRICT
|
|
);
|
|
|
|
CREATE INDEX `idx_jls_line` ON `journal_line_subledgers`(`journal_line_id`);
|
|
CREATE INDEX `idx_jls_subledger` ON `journal_line_subledgers`(`subledger_id`);
|
|
|
|
CREATE TABLE IF NOT EXISTS `audit_logs` (
|
|
`id` CHAR(36) PRIMARY KEY,
|
|
`entity_type` VARCHAR(50) NOT NULL,
|
|
`entity_id` CHAR(36) NOT NULL,
|
|
`action` VARCHAR(20) NOT NULL CHECK (`action` IN ('CREATE', 'UPDATE', 'POST', 'REVERSE', 'DELETE')),
|
|
`performed_by` CHAR(36) NOT NULL,
|
|
`performed_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`details` JSON
|
|
);
|
|
|
|
CREATE INDEX `idx_audit_entity` ON `audit_logs`(`entity_type`, `entity_id`);
|
|
CREATE INDEX `idx_audit_performed` ON `audit_logs`(`performed_at`);
|