SchoolPro

Technology Stack

Architectural Principles

High-Level Module Structure

Directory Structure

Key Design Decisions

Why NOT Vue/React

• Server-rendered Blade with Alpine.js keeps the stack simple
• No API layer required for basic CRUD operations
• Lower JavaScript bundle size and faster page loads
• Alpine.js provides reactive UI without build complexity

Why NOT Spatie Permissions

• Custom RBAC is simpler for this use case
• Fewer dependencies, more control
• School-scoped permissions are easier to implement custom

Why Service Layer

• Keeps controllers thin and focused on HTTP concerns
• Business logic is reusable across controllers, commands, and jobs
• Easier to test domain logic independently
• Prevents code duplication between web and API controllers

Database Strategy

• MariaDB 10.4.28 on port 3305 (XAMPP)
• No schema changes to existing DB configuration
• Foreign key constraints enforced at database level
• Soft deletes for all domain entities
• Decimal(15,2) for financial amounts

Table Classification

Global Tables (no school_id)

School-Scoped Tables (have school_id)

Core Table Definitions

1. Schools

CREATE TABLE schools (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_code VARCHAR(30) UNIQUE NOT NULL,
    name VARCHAR(200) NOT NULL,
    short_name VARCHAR(50) NULL,
    eiin VARCHAR(20) UNIQUE NULL,          -- Bangladesh EIIN number
    address TEXT NULL,
    phone VARCHAR(30) NULL,
    email VARCHAR(100) NULL,
    website VARCHAR(150) NULL,
    logo VARCHAR(255) NULL,
    established_date DATE NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL
);

2. Academic Years

CREATE TABLE academic_years (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(50) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    is_current BOOLEAN DEFAULT false,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL,
    FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
    UNIQUE KEY (school_id, name),
    INDEX idx_school_status (school_id, status),
    INDEX idx_school_current (school_id, is_current)
);

3. Classes

CREATE TABLE classes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    academic_year_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    short_name VARCHAR(30) NULL,
    numeric_order UNSIGNED INT DEFAULT 0,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL,
    FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
    FOREIGN KEY (academic_year_id) REFERENCES academic_years(id) ON DELETE CASCADE,
    UNIQUE KEY (school_id, academic_year_id, name)
);

4. Students

CREATE TABLE students (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    student_code VARCHAR(50) NOT NULL,
    admission_no VARCHAR(50) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NULL,
    gender ENUM('male','female','other') NULL,
    date_of_birth DATE NULL,
    blood_group VARCHAR(10) NULL,
    religion VARCHAR(50) NULL,
    nationality VARCHAR(50) DEFAULT 'Bangladeshi',
    phone VARCHAR(30) NULL,
    email VARCHAR(100) NULL,
    address TEXT NULL,
    photo VARCHAR(255) NULL,
    status ENUM('active','inactive','transferred','graduated','left') DEFAULT 'active',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL,
    FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
    UNIQUE KEY (school_id, student_code),
    UNIQUE KEY (school_id, admission_no)
);

5. Employees

CREATE TABLE employees (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    employee_code VARCHAR(50) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NULL,
    gender ENUM('male','female','other') NULL,
    department_id BIGINT UNSIGNED NULL,
    designation_id BIGINT UNSIGNED NULL,
    employee_type ENUM('teacher','staff','accountant','admin','support','other') DEFAULT 'staff',
    joining_date DATE NULL,
    status ENUM('active','inactive','resigned','terminated') DEFAULT 'active',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    deleted_at TIMESTAMP NULL,
    FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL,
    FOREIGN KEY (designation_id) REFERENCES designations(id) ON DELETE SET NULL,
    UNIQUE KEY (school_id, employee_code)
);

6. Fee Management Tables

CREATE TABLE fee_categories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(50) NOT NULL,
    is_recurring BOOLEAN DEFAULT true,
    status ENUM('active','inactive') DEFAULT 'active',
    FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
    UNIQUE KEY (school_id, code)
);

CREATE TABLE fee_structures (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    academic_year_id BIGINT UNSIGNED NOT NULL,
    class_id BIGINT UNSIGNED NOT NULL,
    fee_category_id BIGINT UNSIGNED NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    frequency ENUM('one_time','monthly','quarterly','half_yearly','yearly') DEFAULT 'monthly',
    FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
    FOREIGN KEY (academic_year_id) REFERENCES academic_years(id) ON DELETE CASCADE,
    FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
    FOREIGN KEY (fee_category_id) REFERENCES fee_categories(id) ON DELETE CASCADE,
    UNIQUE KEY (school_id, academic_year_id, class_id, fee_category_id, frequency)
);

CREATE TABLE student_fee_invoices (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    student_id BIGINT UNSIGNED NOT NULL,
    enrollment_id BIGINT UNSIGNED NOT NULL,
    invoice_no VARCHAR(50) NOT NULL,
    invoice_date DATE NOT NULL,
    due_date DATE NULL,
    subtotal DECIMAL(12,2) DEFAULT 0,
    discount DECIMAL(12,2) DEFAULT 0,
    fine DECIMAL(12,2) DEFAULT 0,
    total_amount DECIMAL(12,2) DEFAULT 0,
    paid_amount DECIMAL(12,2) DEFAULT 0,
    due_amount DECIMAL(12,2) DEFAULT 0,
    status ENUM('unpaid','partial','paid','cancelled') DEFAULT 'unpaid',
    FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (enrollment_id) REFERENCES student_enrollments(id) ON DELETE CASCADE,
    UNIQUE KEY (school_id, invoice_no)
);

CREATE TABLE student_fee_payments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    student_id BIGINT UNSIGNED NOT NULL,
    receipt_no VARCHAR(50) NOT NULL,
    payment_date DATE NOT NULL,
    payment_method_id BIGINT UNSIGNED NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    reference_no VARCHAR(100) NULL,
    status ENUM('completed','cancelled') DEFAULT 'completed',
    FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (payment_method_id) REFERENCES payment_methods(id) ON DELETE RESTRICT,
    UNIQUE KEY (school_id, receipt_no)
);

7. Accounting Tables

CREATE TABLE accounts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    account_type_id BIGINT UNSIGNED NOT NULL,
    parent_id BIGINT UNSIGNED NULL,
    account_code VARCHAR(50) NOT NULL,
    name VARCHAR(150) NOT NULL,
    is_group BOOLEAN DEFAULT false,
    status ENUM('active','inactive') DEFAULT 'active',
    FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
    FOREIGN KEY (account_type_id) REFERENCES account_types(id) ON DELETE RESTRICT,
    FOREIGN KEY (parent_id) REFERENCES accounts(id) ON DELETE SET NULL,
    UNIQUE KEY (school_id, account_code)
);

CREATE TABLE journal_entries (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    journal_no VARCHAR(50) NOT NULL,
    journal_date DATE NOT NULL,
    reference_type VARCHAR(100) NULL,
    reference_id BIGINT UNSIGNED NULL,
    description VARCHAR(500) NULL,
    status ENUM('draft','posted','cancelled') DEFAULT 'posted',
    FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
    UNIQUE KEY (school_id, journal_no)
);

CREATE TABLE journal_entry_lines (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    journal_entry_id BIGINT UNSIGNED NOT NULL,
    account_id BIGINT UNSIGNED NOT NULL,
    debit DECIMAL(15,2) DEFAULT 0,
    credit DECIMAL(15,2) DEFAULT 0,
    description VARCHAR(500) NULL,
    FOREIGN KEY (journal_entry_id) REFERENCES journal_entries(id) ON DELETE CASCADE,
    FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE RESTRICT
);

CREATE TABLE vouchers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    voucher_type_id BIGINT UNSIGNED NOT NULL,
    voucher_no VARCHAR(50) NOT NULL,
    voucher_date DATE NOT NULL,
    payee_payer VARCHAR(200) NULL,
    description TEXT NULL,
    status ENUM('draft','posted','cancelled') DEFAULT 'draft',
    FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
    FOREIGN KEY (voucher_type_id) REFERENCES voucher_types(id) ON DELETE RESTRICT,
    UNIQUE KEY (school_id, voucher_no)
);

8. Examination & Results

CREATE TABLE exams (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    academic_year_id BIGINT UNSIGNED NOT NULL,
    exam_type_id BIGINT UNSIGNED NOT NULL,
    class_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    code VARCHAR(50) NOT NULL,
    start_date DATE NULL,
    end_date DATE NULL,
    status ENUM('draft','active','completed','published','cancelled') DEFAULT 'draft',
    FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
    FOREIGN KEY (academic_year_id) REFERENCES academic_years(id) ON DELETE CASCADE,
    FOREIGN KEY (exam_type_id) REFERENCES exam_types(id) ON DELETE RESTRICT,
    FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
    UNIQUE KEY (school_id, academic_year_id, code)
);

CREATE TABLE result_entries (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    exam_id BIGINT UNSIGNED NOT NULL,
    student_id BIGINT UNSIGNED NOT NULL,
    total_marks DECIMAL(10,2) DEFAULT 0,
    percentage DECIMAL(5,2) DEFAULT 0,
    gpa DECIMAL(5,2) NULL,
    grade VARCHAR(20) NULL,
    merit_position UNSIGNED INT NULL,
    FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    UNIQUE KEY (exam_id, student_id)
);

9. Payroll Tables

CREATE TABLE salary_components (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(50) NOT NULL,
    component_type ENUM('earning','deduction') NOT NULL,
    calculation_type ENUM('fixed','percentage') DEFAULT 'fixed',
    default_amount DECIMAL(12,2) DEFAULT 0,
    FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
    UNIQUE KEY (school_id, code)
);

CREATE TABLE employee_salary_structures (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_id BIGINT UNSIGNED NOT NULL,
    employee_id BIGINT UNSIGNED NOT NULL,
    basic_salary DECIMAL(12,2) DEFAULT 0,
    house_rent DECIMAL(12,2) DEFAULT 0,
    medical_allowance DECIMAL(12,2) DEFAULT 0,
    gross_salary DECIMAL(12,2) DEFAULT 0,
    effective_from DATE NOT NULL,
    FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
);

Relationship Summary

Module Dependency Graph

Module Status

Phase 1: Foundation Complete

Phase 2: Fee & Accounting Partially Scaffolded

Phase 3: Employee & HR Scaffolded

Phase 4: Advanced Features Not Started

Key Integration Pipelines

Fee → Accounting

Payroll → Accounting

Exam → Result → Final Result

Student Enrollment → Fee Invoice

Role Definitions

Permission Naming Convention

Module: Schools

Module: Academic

Module: Students

Module: Employees

Module: Attendance

Module: Examinations

Module: Fees

Module: Accounting

Module: Payroll

Module: System

Module: Self (Own Data)

Authorization Implementation

Current (Manual)

// In controllers - custom authorizeSchool() method
private function authorizeSchool(int $schoolId): void
{
    if (auth()->user()->hasRole('Super Admin')) {
        return;
    }

    $hasSchool = auth()->user()->schools()
        ->where('school_id', $schoolId)
        ->wherePivot('status', 'active')
        ->exists();

    if (!$hasSchool) {
        abort(403, 'Unauthorized access to this school.');
    }
}

Target (Policy-Based)

// app/Policies/SchoolPolicy.php
class SchoolPolicy
{
    public function viewAny(User $user): bool
    {
        return $user->hasPermission('schools.view');
    }

    public function view(User $user, School $school): bool
    {
        if ($user->hasRole('super-admin')) return true;
        return $user->schools()->where('schools.id', $school->id)
            ->wherePivot('status', 'active')->exists()
            && $user->hasPermission('schools.view');
    }
}

Account Types (Global - seeded once)

Chart of Accounts Hierarchy (Per School)

Voucher Types

Transaction Flows

1. Fee Collection (Receipt Voucher)

-- Journal Entry for fee collection:
DEBIT   Cash in Hand (111)              ৳5,000
CREDIT  Tuition Fee Income (410)        ৳5,000

-- Or if deposited to bank:
DEBIT   Cash at Bank (121)              ৳5,000
CREDIT  Tuition Fee Income (410)        ৳5,000

2. Salary Payment (Payment Voucher)

-- Journal Entry for salary payment:
DEBIT   Teacher Salary (511)            ৳50,000
DEBIT   Staff Salary (512)              ৳30,000
CREDIT  Cash at Bank (121)              ৳80,000

3. Bank Transfer (Contra Voucher)

DEBIT   Savings Account (122)           ৳100,000
CREDIT  Primary Bank Account (121)      ৳100,000

4. General Adjustment (Journal Voucher)

-- Depreciation entry:
DEBIT   Depreciation (563)              ৳10,000
CREDIT  Computer Equipment (153)        ৳10,000

Accounting Rules

VoucherPostingService

// app/Services/Accounting/VoucherPostingService.php
public function post(Voucher $voucher): JournalEntry
{
    return DB::transaction(function () use ($voucher) {
        // 1. Validate voucher is in 'posted' status
        // 2. Validate at least 2 lines
        // 3. Validate SUM(debit) == SUM(credit)
        // 4. Create journal entry from voucher
        // 5. Copy voucher lines to journal entry lines
        // 6. Return the created journal entry
    });
}

Financial Reports

1. Trial Balance

2. Profit & Loss Statement

3. Balance Sheet

4. General Ledger

Data Integrity

Integration Diagram

Phase 1: Foundation Complete

Core Infrastructure

Academic Module

Student & Examination Module

Phase 2: Fee Management & Accounting Current Phase

Fee Management

Chart of Accounts & Vouchers

Fee-Accounting Integration

Phase 3: Employee & HR Planned

Phase 4: Advanced Features Planned

Phase 5: Reporting & Analytics Planned

Development Sequence

Immediate (Next Sprint)

1. Fee Categories CRUD (Controller + Views)
2. Fee Structures CRUD (Controller + Views)
3. Account Types seeder
4. Accounts CRUD (Controller + Views)
5. Voucher Types seeder

Short-term (2-3 Sprints)

1. Fee Invoices generation logic
2. Payment Methods CRUD
3. Fee Payments processing
4. Payment Allocation logic
5. Account Head Mappings CRUD

Medium-term (1-2 Months)

1. Voucher CRUD with posting
2. Journal Entry management
3. Reversal entry system
4. Fee → Accounting integration
5. Dashboard financial widgets

Long-term (2-3 Months)

1. Employee module (full)
2. Attendance module
3. Payroll module
4. Payroll → Accounting integration
5. Financial reports

Technical Debt

High Priority

Medium Priority

Low Priority

Risk Assessment