# College Website + Management System — Architecture & Database Schema **Stack:** PHP 8.2+ / Laravel 11 / MySQL 8 (or MariaDB 10.6+) / Blade + Alpine.js (or Vue if you prefer a heavier frontend) / Tailwind CSS **Target hosting:** cPanel shared/VPS hosting (Apache/LiteSpeed + PHP-FPM + MySQL) Laravel was chosen over a Node stack because cPanel hosts near-universally support PHP + MySQL out of the box, while Node hosting on cPanel varies host-to-host and often needs extra setup (Passenger, custom Node app manager, etc.). Laravel also gives us, for free, the things this spec needs most: migrations, an ORM with relationships, role/permission packages, queued jobs, and a mature ecosystem — without locking you into any platform-specific service. --- ## 1. High-Level Architecture ``` ┌─────────────────────────────────────────────────────────────┐ │ Public Website │ │ Home / About / Academic / Admission / Faculty / Notices / │ │ Results / Events / Gallery / Downloads / News / Contact │ │ (Bangla + English, driven entirely by CMS data) │ └───────────────────────────┬───────────────────────────────────┘ │ ┌───────────────────────────┴───────────────────────────────────┐ │ Laravel Application │ │ ┌───────────────┐ ┌───────────────┐ ┌─────────────────────┐ │ │ │ Web Routes │ │ API Routes │ │ Console/Jobs │ │ │ │ (session │ │ (Sanctum │ │ (report generation, │ │ │ │ auth, CMS, │ │ token auth, │ │ notifications, │ │ │ │ portals) │ │ optional │ │ backups) │ │ │ └───────┬───────┘ └───────┬───────┘ └──────────┬───────────┘ │ │ │ │ │ │ │ ┌───────┴─────────────────┴─────────────────────┴───────────┐ │ │ │ Middleware: auth, role, permission, throttle │ │ │ └───────────────────────────┬─────────────────────────────────┘ │ │ ┌───────────────────────────┴─────────────────────────────────┐ │ │ │ Controllers → Form Requests (validation) → Policies │ │ │ │ (authorization) → Services → Models (Eloquent) │ │ │ └───────────────────────────┬─────────────────────────────────┘ │ └──────────────────────────────┼───────────────────────────────────┘ │ ┌──────────┴──────────┐ │ MySQL Database │ │ (this document) │ └───────────────────────┘ ``` **Key principle enforced everywhere:** authorization lives in **Policies/Gates + middleware on every route**, never just in the Blade view. Every controller method checks `$this->authorize(...)` before touching data. A Student's JWT/session literally cannot hit a Teacher or Admin route — it's rejected at the middleware layer, not just hidden in the UI. ### Portal separation Rather than one dashboard with conditional rendering, each role gets its own route group and its own set of Blade layouts: ``` / → public site /admin/* → super-admin + admin (permission-gated per route) /teacher/* → teacher portal /student/* → student portal /staff/* → staff portal /api/* → JSON API (used by dashboards' AJAX calls, future mobile app) ``` A single `users` table handles login for all roles; a `role_id` plus a polymorphic profile (`students`, `teachers`, `staff`) determines what a given user actually is and which portal they're redirected to after login. --- ## 2. Bilingual Content Strategy Rather than a separate `translations` table (which adds a join to every query), translatable content tables get **paired columns**: `title_bn` / `title_en`, `body_bn` / `body_en`, etc. This is simpler to query, simpler for admin forms (two tabs: বাংলা / English), and fast on shared hosting since there's no extra join. Non-translatable data (dates, file paths, foreign keys, numbers) has no pairing. Frontend renders `{{ $notice->getTranslated('title', $locale) }}` via a small trait (`HasTranslations`) shared by all content models, falling back to English if a Bangla field is empty (and flagged in admin UI as "missing translation"). --- ## 3. Roles & Permissions Model ``` roles — super_admin, admin, teacher, student, staff, parent (reserved) permissions — atomic actions, e.g. view_students, edit_results, manage_notices role_permissions — pivot: which roles get which permissions by default user_permissions — pivot: per-user overrides (grant/revoke beyond role default) ``` Permission checks use Laravel Gates backed by a `HasPermissions` trait: `$user->can('edit_results')`. Super Admin bypasses all checks (`Gate::before`). This lets Super Admin fine-tune an individual Admin's access (e.g., an Admin who can manage notices but not fees) without creating a new role. --- ## 4. Database Schema Conventions: every table has `id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY`, `created_at`, `updated_at` (Laravel timestamps). Tables that support soft delete also get `deleted_at`. Foreign keys are `UNSIGNED BIGINT` with `ON DELETE` behavior noted. ### 4.1 Identity & Access **users** | Column | Type | Notes | |---|---|---| | id | PK | | | name | varchar(150) | | | email | varchar(150) unique | login | | password | varchar(255) | bcrypt | | role_id | FK → roles.id | | | status | enum(active,disabled) | default active | | last_login_at | timestamp null | | | remember_token | varchar(100) null | | | soft delete | | | **roles** — id, name, slug, description **permissions** — id, name, slug, module (for grouping in UI), description **role_permissions** — role_id, permission_id (composite unique) **user_permissions** — user_id, permission_id, type enum(grant,revoke) **password_resets** — email, token, created_at (Laravel default table) **personal_access_tokens** — Sanctum default, for API auth from JS dashboards **activity_logs** — id, user_id (nullable, FK set null on delete), action, subject_type, subject_id, description, ip_address, user_agent, created_at ### 4.2 Academic Structure **departments** — id, name_bn, name_en, slug, description_bn, description_en, image, status, soft delete **courses** — id, department_id (FK cascade), name_bn, name_en, duration, description_bn, description_en, status, soft delete **subjects** — id, course_id (FK cascade), name_bn, name_en, code, credit_hours, semester, status **classes** — id, course_id (FK cascade), name (e.g. "1st Year"), status **sections** — id, class_id (FK cascade), name (e.g. "A"), capacity ### 4.3 People **teachers** — id, user_id (FK cascade, unique), teacher_code unique, department_id (FK), designation, qualification, photo, phone, joining_date, bio_bn, bio_en, status, soft delete **students** — id, user_id (FK cascade, unique), student_id_no unique, name_bn, photo, dob, gender, phone, address, department_id (FK), course_id (FK), session, current_year_semester, roll, registration_no unique, guardian_name, guardian_phone, guardian_relation, admission_date, status enum(active,graduated,suspended,inactive), soft delete **staff** — id, user_id (FK cascade, unique), staff_code unique, designation, department_id (FK nullable), phone, joining_date, status **teacher_subject** — pivot: teacher_id, subject_id, class_id, section_id (which teacher teaches what, to whom) **enrollments** — id, student_id (FK cascade), class_id (FK), section_id (FK), course_id (FK), session, status enum(active,completed,dropped), enrolled_at ### 4.4 Attendance **attendance_sessions** — id, class_id, section_id, subject_id, teacher_id, date, created_at (one row per taken-attendance event) **attendance_records** — id, attendance_session_id (FK cascade), student_id (FK cascade), status enum(present,absent,late,excused), remarks Indexes: composite index on (class_id, section_id, subject_id, date) in `attendance_sessions` for fast lookups; index on (student_id) in `attendance_records`. ### 4.5 Assignments **assignments** — id, teacher_id (FK), subject_id (FK), class_id (FK), section_id (FK), title_bn, title_en, description_bn, description_en, attachment_path, marks, deadline, status enum(draft,published,closed), soft delete **assignment_submissions** — id, assignment_id (FK cascade), student_id (FK cascade), file_path, submitted_at, marks_obtained nullable, feedback nullable, status enum(submitted,late,graded), unique(assignment_id, student_id) ### 4.6 Exams & Results **exams** — id, name_bn, name_en, exam_type enum(midterm,final,class_test,other), session, status enum(scheduled,ongoing,completed,published), soft delete **exam_subjects** — id, exam_id (FK cascade), subject_id (FK), class_id (FK), date, start_time, end_time, room, full_marks, pass_marks **marks** — id, exam_subject_id (FK cascade), student_id (FK cascade), teacher_id (entered_by FK), marks_obtained, grade nullable, status enum(entered,approved), unique(exam_subject_id, student_id) **results** — id, exam_id (FK cascade), student_id (FK cascade), total_marks, gpa nullable, result_status enum(pass,fail,pending), published_at nullable, finalized_by (FK users, nullable), unique(exam_id, student_id) **Business rule enforced in code:** once `results.published_at` is set, update routes for that row require `manage_results` permission at Admin/Super Admin level only — Teachers lose write access even to their own entered marks for that exam. ### 4.7 Routine **routines** — id, class_id (FK), section_id (FK), subject_id (FK), teacher_id (FK), room, day enum(sat..fri), start_time, end_time, session Index on (class_id, section_id, day) and (teacher_id, day) for fast "my routine" queries. ### 4.8 Fees **fee_types** — id, name_bn, name_en, is_recurring boolean **fee_structures** — id, fee_type_id (FK), course_id (FK), amount, due_date, session **student_fees** — id, student_id (FK cascade), fee_structure_id (FK), amount_due, discount, final_amount, status enum(unpaid,partial,paid,overdue) **payments** — id, student_fee_id (FK cascade), amount, method enum(cash,bank,gateway,other), transaction_ref nullable, paid_at, recorded_by (FK users) Payment gateway integration point: a `PaymentGatewayInterface` service contract with a `ManualPaymentGateway` implementation by default; swapping in bKash/SSLCommerz/Stripe later means implementing the interface, not touching the schema. ### 4.9 Communication **notices** — id, title_bn, title_en, body_bn, body_en, category enum(general,exam,admission,important), attachment_path nullable, is_pinned boolean, publish_at, expire_at nullable, status enum(draft,published), created_by (FK users), soft delete **events** — id, title_bn, title_en, description_bn, description_en, cover_image, location, event_date, start_time, organizer, registration_link nullable, status enum(upcoming,completed,cancelled), soft delete **notifications** — id, user_id nullable (FK, null = broadcast), audience enum(all,students,teachers,department,class,user), type enum(notice,assignment,exam,result,attendance,event,system), title, body, is_read boolean default false, read_at nullable, related_type nullable, related_id nullable **messages** — id, sender_id (FK users), recipient_id (FK users), subject, body, attachment_path nullable, is_read boolean, read_at nullable, soft delete Messaging authorization rule (enforced in a `MessagePolicy`): a Student can only message Teachers who teach at least one of their enrolled subjects; Teachers can only message their own students/Admin; Admin can message anyone. This mapping is derived from `teacher_subject` + `enrollments`, not stored redundantly. ### 4.10 Leave **leave_applications** — id, user_id (FK cascade, teacher/staff), leave_type, start_date, end_date, reason, status enum(pending,approved,rejected), remarks nullable, reviewed_by (FK users nullable) ### 4.11 Study Materials & Downloads **study_materials** — id, teacher_id (FK), subject_id (FK), class_id (FK), title_bn, title_en, file_path, description_bn, description_en, uploaded_at, soft delete **downloads** — id, title_bn, title_en, category enum(prospectus,form,notice,routine,syllabus,document), file_path, description_bn, description_en, access_level enum(public,student,teacher,all_authenticated), uploaded_by (FK users), soft delete ### 4.12 Media & Gallery **media** — id, type enum(image,document,video), path, original_name, mime_type, size, uploaded_by (FK users), created_at — central library referenced by other tables via `media_id` where a single asset is reused **gallery_albums** — id, title_bn, title_en, category enum(campus,academic,cultural,sports,event), cover_image, soft delete **gallery_images** — id, album_id (FK cascade), media_id (FK), caption_bn nullable, caption_en nullable, sort_order ### 4.13 CMS **pages** — id, slug unique, title_bn, title_en, meta_title_bn, meta_title_en, meta_description_bn, meta_description_en, og_image, template enum(default,about,contact,custom), status enum(draft,published), soft delete **page_sections** — id, page_id (FK cascade, nullable for homepage-only sections), section_type enum(hero,welcome,stats,departments,faculty,gallery,cta,custom_html,...), title_bn, title_en, body_bn, body_en, image, button_text_bn, button_text_en, button_link, sort_order, is_visible boolean **menus** — id, label_bn, label_en, url, parent_id nullable (self FK, for dropdowns), sort_order, is_visible **settings** — id, key unique, value (text), type enum(text,image,json) — single source for college name, logo, contact info, social links, theme options, default language, etc. Homepage Builder is simply `page_sections` where `page_id IS NULL`, ordered by `sort_order`, rendered by a section-type → Blade-partial map. Reordering = updating `sort_order` via a drag-and-drop AJAX call. ### 4.14 News **news** — id, title_bn, title_en, body_bn, body_en, cover_image, category, published_at, author_id (FK users), status enum(draft,published), soft delete ### 4.15 Facilities **facilities** — id, name_bn, name_en, description_bn, description_en, icon, image, sort_order ### 4.16 Admissions **admission_sessions** — id, name, course_id (FK), start_date, end_date, requirements_bn, requirements_en, status enum(open,closed) **admission_applications** — id, admission_session_id (FK), applicant_name, email, phone, dob, previous_school, documents (JSON of media_ids or separate table below), status enum(submitted,under_review,approved,rejected,correction_requested), remarks nullable, reviewed_by (FK users nullable), soft delete **admission_documents** — id, admission_application_id (FK cascade), document_type, media_id (FK) ### 4.17 Certificates (architecture-ready) **certificates** — id, student_id (FK), type enum(admission,provisional,transcript,character,other), issued_at, file_path nullable, issued_by (FK users) --- ## 5. Authentication & Authorization Flow 1. Single login form (or role-specific login pages if you prefer separate URLs like `/teacher/login`) → Laravel's standard session-based auth for the web portals, Sanctum tokens for AJAX/API calls made from within those portals. 2. On successful login, redirect based on `role.slug`: `super_admin|admin → /admin`, `teacher → /teacher`, `student → /student`, `staff → /staff`. 3. Every route inside `/admin`, `/teacher`, `/student`, `/staff` is wrapped in: - `auth` middleware (must be logged in) - a custom `role:teacher` (or similar) middleware (must be the right role) - relevant `can:permission_name` middleware or in-controller `$this->authorize()` calls for granular actions (e.g., a Teacher route for entering marks still checks the Teacher is actually assigned to that subject/class via `teacher_subject`, not just "is a teacher"). 4. All data-fetching queries are scoped to the logged-in user's own records at the query level (e.g., `Student::where('user_id', auth()->id())`), not filtered client-side — so even a crafted API request can't pull another student's data. 5. Password reset uses Laravel's built-in signed, expiring token flow. Sessions are invalidated on password change and on explicit logout (`session()->invalidate()` + `regenerateToken()`). --- ## 6. Suggested Build Order (Phases) This matches how we'll actually build it — each phase ends with something real and testable, not a stub: 1. **Foundation** — Laravel install, `.env` setup, migrations for `users/roles/permissions`, auth scaffolding, role-based redirect, seeders for demo roles/admin. 2. **Academic structure + People** — departments, courses, subjects, classes/sections, teacher & student CRUD (Admin creates → real login credentials generated → they can log in). 3. **Attendance** — teacher marks attendance → student sees it → admin reports. 4. **Assignments** — teacher creates/grades → student submits/views. 5. **Exams, marks, results** — including the "finalize locks it" rule. 6. **Routine, Notices, Events** — CMS-editable, bilingual. 7. **CMS/Homepage builder + public site** — sections, pages, menus, settings, SEO fields. 8. **Gallery, Downloads, Study Materials, News** — with access-level restrictions. 9. **Notifications + Messaging** — with the policy-based authorization rules above. 10. **Fees + Admissions + Leave** — including the pluggable payment gateway interface. 11. **Reports, Audit Log viewer, Global Search** — cross-cutting features once the data exists to report on. 12. **Hardening pass** — rate limiting, CSRF/XSS review, file upload validation, production build, deployment guide, backup/restore scripts. Each phase will ship working code (migrations + models + controllers + routes + Blade views) you can actually click through, not placeholders. --- ## 7. What I need from you to start Phase 1 - Preferred name/branding for demo content (or I'll use a realistic placeholder college). - Do you want a single shared `/login` with role auto-detection, or separate login URLs per role? - PHP version and MySQL version available on your cPanel plan (run `php -v` if you have SSH access, or check cPanel's "MultiPHP Manager") — this affects a couple of syntax choices.