fchinembiri
/
geocrop-platform.
1
1
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
geocrop-platform./apps/nextgen/datacontrol.md

677 lines
44 KiB
Markdown
Raw Permalink Blame History

# Database Control Matrix (`datacontrol.md`)
This document maps all available backend controllers to the SQLite database tables they interact with, detailing the operations performed (reads, writes, updates, soft-deletes) and the transaction patterns they use.
---
## Design Principles & Patterns
* **Write Operations (`INSERT` / `UPDATE`)**: Under nextgen server conventions, all direct write operations set `sync_status = 'pending'`, generate a new UUIDv4 `uid` on creation, and stamp the local timestamp `updated_at`.
* **Soft Deletes**: Deletion operations do not drop DB table records. Instead, they run an `UPDATE` command setting `is_deleted = 1` (and often set `is_active = 0` or similar lifecycle flags when relevant).
* **Transactional Integrity**: Multi-step operations (e.g. bulk CSV marks upload in `marks.controller.js`, bulk attendance marks, or invoice calculations) use explicit database transactions (via `better-sqlite3`'s `db.transaction(...)`) to guarantee all-or-nothing execution.
* **Read Scoping**: Read operations are filtered globally on `is_deleted = 0` and scoped appropriately (e.g., student/parent portals only view records matched to their specific user or linked dependent IDs).
* **Grading Scale Resolutions**: Complex entities like `grades.controller.js` and `exams.controller.js` dynamically resolve percentages against a cascading grading-scale chain (specific row scale override -> default school configuration scale -> ZIMSEC standard fallback bands).
---
## Controllers & Database Interactions
### 1. [academic-rollover.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/academic-rollover.controller.js)
* **`academic_years`**:
* `SELECT` (retrieve status, lists)
* `INSERT` (create new year: `uid`, `name`, `status = 'draft'`, `start_date`, `end_date`)
* `UPDATE` (change status to `'active'`, `'completed'`; soft-delete with `is_deleted = 1`)
* **`classes`**:
* `SELECT` (fetch classes to rollover)
* `INSERT` (clone/rollover classes into new academic year)
* **`subjects`**:
* `SELECT` (fetch subjects to rollover)
* `INSERT` (clone/rollover subjects into new academic year)
* **`enrollments`**:
* `SELECT` (active enrollment state checks)
* `INSERT` (auto-promote students to new classes in the rolling year)
* **`users`**:
* `SELECT` (link student roles)
### 2. [assignments.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/assignments.controller.js)
* **`assignments`**:
* `SELECT` (list active assignments with class/subject boundaries)
* `INSERT` (create assignment: `uid`, `title`, `description`, `subject_id`, `due_date`, `max_score`, `created_by`)
* `UPDATE` (edit fields; soft-delete with `is_deleted = 1`)
* **`submissions`**:
* `SELECT` (check user submissions history)
* `INSERT` (create draft/initial submission: `uid`, `assignment_id`, `student_id`, `status = 'submitted'`, `submitted_at`)
* `UPDATE` (grade submission: `grade`, `feedback`, `graded_by`, `graded_at`, `status = 'graded'`; soft-delete with `is_deleted = 1`)
* **`users`**:
* `SELECT` (joining names for graders/students)
* **`enrollments`**:
* `SELECT` (verify active student status in target class)
### 3. [attendance.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/attendance.controller.js)
* **`attendance_new`**:
* `SELECT` (retrieve rosters, historical summaries)
* `INSERT` (bulk insert roll-call records: `uid`, `student_id`, `class_id`, `date`, `status`, `remarks`, `marked_by`)
* `UPDATE` (modify status, remarks, marked_by, or soft-delete with `is_deleted = 1`)
* **`users`**:
* `SELECT` (verify students name)
* **`enrollments`**:
* `SELECT` (active student list for class roster)
* **`classes`**:
* `SELECT` (validate target class)
* **`parent_students`**:
* `SELECT` (scoped lookup for parent portal self-read)
### 4. [audit.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/audit.controller.js)
* **`audit_logs`**:
* `SELECT` (list recent events filtered by email, action, date)
* `INSERT` (log technical actions/events: `uid`, `user_id`, `user_email`, `action`, `entity_type`, `entity_id`, `old_data`, `new_data`, `ip_address`, `user_agent`)
* **`users`**:
* `SELECT` (look up user emails)
### 5. [calendar.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/calendar.controller.js)
* **`events`**:
* `SELECT` (list active events filterable by category, date range)
* `INSERT` (create calendar event: `uid`, `title`, `description`, `start_time`, `end_time`, `category`, `is_all_day`, `location`, `created_by`)
* `UPDATE` (modify properties; soft-delete with `is_deleted = 1`)
* **`users`**:
* `SELECT` (join creator profile)
### 6. [classes.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/classes.controller.js)
* **`classes`**:
* `SELECT` (list active classes with counts)
* `INSERT` (create class: `uid`, `name`, `section`, `grade_level`, `room_number`, `class_teacher_id`)
* `UPDATE` (modify properties; soft-delete with `is_deleted = 1`)
* **`users`**:
* `SELECT` (join teacher names)
* **`enrollments`**:
* `SELECT` (calculate active student roll counts)
### 7. [club-attendance.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/club-attendance.controller.js)
* **`club_attendance`**:
* `SELECT` (list roll-call entries)
* `INSERT` (bulk insert club logs: `uid`, `club_id`, `student_id`, `date`, `status`, `remarks`, `marked_by`)
* `UPDATE` (modify attendance details; soft-delete with `is_deleted = 1`)
* **`clubs`**:
* `SELECT` (verify active status)
* **`users`**:
* `SELECT` (verify students)
### 8. [clubs.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/clubs.controller.js)
* **`clubs`**:
* `SELECT` (active clubs catalog list)
* `INSERT` (create club: `uid`, `name`, `type`, `description`, `teacher_in_charge_id`, `schedule`, `max_members`)
* `UPDATE` (modify properties, status; soft-delete with `is_deleted = 1`)
* **`club_memberships`**:
* `SELECT` (check active student enrollments)
* `INSERT` (join a club: `uid`, `club_id`, `student_id`, `status = 'active'`, `join_date = CURRENT_DATE`)
* `UPDATE` (re-enroll/join; leave a club: set `status = 'inactive'`)
* **`club_announcements`**:
* `SELECT` (list recent club notices)
* `INSERT` (post notice: `uid`, `club_id`, `title`, `content`, `created_by`)
* **`club_attendance`**:
* `SELECT` (active roll-call records)
* `INSERT` / `UPDATE` (bulk upsert marks matching `(club_id, student_id, date)`)
* **`club_chats`**:
* `SELECT` (list messages)
* `INSERT` (post message: `uid`, `club_id`, `sender_id`, `message`, `image_url`)
* **`users`**:
* `SELECT` (joining names for dashboard/contacts)
### 9. [courseGrades.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/courseGrades.controller.js)
* **`course_grades`**:
* `SELECT` (list student performance scores)
* `INSERT` (record grade: `uid`, `student_id`, `course_id`, `grade_letter`, `grade_points`, `percentage`, `remarks`, `graded_by`)
* `UPDATE` (modify grade letter, percentage, remarks, graded_by; soft-delete with `is_deleted = 1`)
* **`course_enrolments`**:
* `SELECT` (verify active status in courses)
* **`courses`**:
* `SELECT` (verify existence)
* **`users`**:
* `SELECT` (verify roles)
### 10. [course_enrolments.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/course_enrolments.controller.js)
* **`course_enrolments`**:
* `SELECT` (active, pending, and filterable enrolment requests lists)
* `INSERT` (create enrolment: `uid`, `student_id`, `course_id`, `status`, `request_status`, `requested_at`, `approved_by`, `approved_at`, `progress = 0`, `sync_status = 'pending'`)
* `UPDATE` (modify `request_status` to `'approved'`, `'rejected'`, or `'withdrawn'`; modify `status` to `'active'` or `'dropped'`; modify `approved_by`, `approved_at`, `rejection_reason`, `updated_at`, `sync_status = 'pending'`, soft-delete `is_deleted = 1`)
* **`courses`**:
* `SELECT` (existence checks)
* **`users`**:
* `SELECT` (joining student profiles)
### 11. [courses.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/courses.controller.js)
* **`subjects`**: (Note: The routes query/modify `subjects` table name, not a `courses` table name directly, although the controller is called `courses.controller.js`).
* `SELECT` (catalog list with filters)
* `INSERT` (create course: `uid`, `name`, `code`, `class_id`, `teacher_id`, `description`, `credit_hours`)
* `UPDATE` (modify attributes, or soft-delete with `is_deleted = 1`)
* **`classes`**:
* `SELECT` (class details)
* **`users`**:
* `SELECT` (teacher and student directories)
* **`enrollments`**:
* `SELECT` (verify class list)
* **`grades`**:
* `SELECT` (calculate percentage average and recent history list)
### 12. [crossword.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/crossword.controller.js)
* **`crossword_games`**:
* `SELECT` (active games lists with role/status filters, metadata detail fetch)
* `INSERT` (create game layout details: `uid`, `title`, `description`, `subject_id`, `class_id`, `created_by`, `status = 'ready'`, `grid_layout` JSON, `word_count`, `difficulty`, `reward_points`, `time_limit_seconds`, `is_published = 0`, `sync_status = 'pending'`, `created_at`, `updated_at`)
* `UPDATE` (modify game attributes: `title`, `description`, `reward_points`, `time_limit_seconds`, `is_published`, `updated_at`, soft-delete with `is_deleted = 1`)
* **`crossword_clues`**:
* `SELECT` (clues details for active games)
* `INSERT` (create clues per placement: `uid`, `game_id`, `clue_text`, `answer`, `direction`, `start_row`, `start_col`, `clue_number`, `hint`, `sync_status = 'pending'`, `created_at`, `updated_at`)
* `UPDATE` (soft-delete with `is_deleted = 1`)
* **`crossword_sessions`**:
* `SELECT` (active/completed history matching student user, leaderboard statistics)
* `INSERT` (start new session: `uid`, `game_id`, `student_id`, `current_state = '{}'`, `correct_count = 0`, `total_count`, `points_earned = 0`, `sync_status = 'pending'`, `created_at`, `updated_at`)
* `UPDATE` (save debounced state `current_state`; submit completion updates: `correct_count`, `total_count`, `points_earned`, `time_taken_seconds`, `completed_at = CURRENT_TIMESTAMP`, `updated_at = CURRENT_TIMESTAMP`, `sync_status = 'pending'`)
* **`users`**:
* `SELECT` (joining student profiles for dashboard ranking leaderboard counts)
### 13. [dashboard.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/dashboard.controller.js)
*(Note: Completely READ-ONLY SELECT operations)*
* **`enrollments`**, **`attendance`** / **`attendance_new`**, **`grades`**, **`subjects_new`** / **`subjects`**, **`classes`**, **`users`**, **`payments`**, **`student_fees`**, **`audit_logs`**, **`courses`**, **`course_enrolments`**, **`course_grades`**, **`submissions`**, **`assignments`**, **`crossword_sessions`**, **`parent_students`**, **`messages`**, **`events`**:
* `SELECT` (aggregate dashboard stats, unread announcements, schedule items)
### 14. [departments.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/departments.controller.js)
* **`departments`**:
* `SELECT` (flat lists and tree detail views)
* `INSERT` (create department: `uid`, `name`, `code`, `head_teacher_id`, `description`)
* `UPDATE` (modify attributes, or soft-delete with `is_deleted = 1` if no active subjects link)
* **`subjects_new`**:
* `SELECT` (active subject counts checks)
* **`staff_records`**:
* `SELECT` (fetch head teachers), `UPDATE` (modify `department` name reference if changed)
* **`users`**:
* `SELECT` (teacher links), `UPDATE` (modify `department_id` assignment)
* **`enrollments`**:
* `SELECT` (count active students)
### 15. [edutainment.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/edutainment.controller.js)
* **`edutainment_content`**:
* `SELECT` (scramble and trivia pool lists)
* `INSERT` (create catalog content: `uid`, `game_type`, `subject_id`, `created_by`, `question_text`, `hint_text`, `options` JSON, `correct_answer`)
* `UPDATE` (soft-delete with `is_deleted = 1`)
* **`edutainment_games`**:
* `SELECT` (list custom puzzles)
* `INSERT` (create custom game template: `uid`, `game_type`, `title`, `description`, `subject_id`, `created_by`, `difficulty`, `reward_points`, `time_limit_seconds`, `game_config` JSON, `is_published = 1`)
* `UPDATE` (soft-delete with `is_deleted = 1`)
* **`edutainment_sessions`**:
* `SELECT` (attempt count summaries)
* `INSERT` (record student game attempt session: `uid`, `game_id`, `student_id`, `score`, `completed_at = CURRENT_TIMESTAMP`, `points_earned`)
* **`subjects_new`**:
* `SELECT` (joining subject names)
* **`users`**:
* `SELECT` (joining teacher names)
### 16. [enrollments.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/enrollments.controller.js)
* **`enrollments`**:
* `SELECT` (enrolment request lists, active/pending profiles)
* `INSERT` (create request: `uid`, `student_id`, `class_id`, `roll_number`, `academic_year`, `section`, `status`, `request_status`, `requested_at`, `approved_by`, `approved_at`, `sync_status = 'pending'`)
* `UPDATE` (approve request: status `'active'`, request_status `'approved'`; reject request: status `'inactive'`, request_status `'rejected'`, `rejection_reason`; withdraw request: status `'inactive'`, request_status `'withdrawn'`; soft-delete with `is_deleted = 1` and status `'inactive'`)
* **`classes`**:
* `SELECT` (checking class properties and sections)
* **`users`**:
* `SELECT` (verifying active student roles)
### 17. [exams.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/exams.controller.js)
* **`exam_groups`**:
* `SELECT` (list templates)
* `INSERT` (create exam group: `uid`, `name`, `description`, `exam_type`, `duration_minutes`, `total_marks`, `passing_marks`, `is_random_order`, `show_results`, `allow_review`, `max_attempts`, `academic_year`, `term`, `grading_scale`)
* `UPDATE` (modify mutable fields, grading_scale; soft-delete with `is_deleted = 1`)
* **`question_banks`**:
* `SELECT` (questions with difficulty lists)
* `INSERT` (single and bulk insert: `uid`, `exam_group_id`, `subject_id`, `question_type`, `question`, `options`, `correct_answer`, `marks`, `difficulty`, `tags`)
* `UPDATE` (modify question attributes; soft-delete with `is_deleted = 1`)
* **`exam_schedules`**:
* `SELECT` (scheduled exams index)
* `INSERT` (create schedule: `uid`, `exam_group_id`, `class_id`, `subject_id`, `start_time`, `end_time`, `duration_minutes`, `instructions`)
* **`exam_attempts`**:
* `SELECT` (attempt counts and performance averages)
* `INSERT` (start new attempt: `uid`, `student_id`, `exam_group_id`, `schedule_id`, `started_at`)
* `UPDATE` (submit attempt: `submitted_at`, `time_spent_seconds`, `total_marks`, `obtained_marks`, `percentage`, `status = 'submitted'`, `ip_address`, `user_agent`)
* **`exam_answers`**:
* `SELECT` (fetch student answers)
* `INSERT` / `UPDATE` (upsert answer: `answer`, `answered_at` / write grade results: `is_correct`, `marks_obtained`)
* **`enrollments`**, **`subjects`**, **`classes`**, **`users`**:
* `SELECT` (verify relationship linkages)
### 18. [fees.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/fees.controller.js)
* **`fee_groups`**:
* `SELECT` (list definitions)
* `INSERT` (create fee type: `uid`, `name`, `description`, `amount`, `type`, `frequency`, `academic_year`, `due_date`)
* `UPDATE` (modify attributes; soft-delete with `is_deleted = 1`)
* **`student_fees`**:
* `SELECT` (student ledger profiles list)
* `INSERT` (assign fee type: `uid`, `student_id`, `fee_group_id`, `amount`, `due_date`)
* **`fee_plans`**:
* `SELECT` (list schedules)
* `INSERT` (create plan: `uid`, `name`, `fee_group_id`, `total_amount`, `installments_count`, `start_date`, `end_date`, `academic_year`, `auto_apply_to_class_id`, `notes`, `created_by`)
* `UPDATE` (modify mutable fields; soft-delete with `is_deleted = 1`)
* **`fee_plan_installments`**:
* `SELECT` (fetch split amounts)
* `INSERT` (create installment rows: `uid`, `fee_plan_id`, `sequence`, `label`, `amount`, `due_date`)
* `UPDATE` (soft-delete with `is_deleted = 1` on plan delete/reset)
* **`discounts`**:
* `SELECT` (list discounts)
* `INSERT` (create discount rate: `uid`, `name`, `kind`, `value`, `scope`, `scope_id`, `reason`, `valid_from`, `valid_to`, `academic_year`, `created_by`)
* `UPDATE` (modify details; soft-delete with `is_deleted = 1`)
* **`student_fee_discounts`**:
* `UPDATE` (soft-delete with `is_deleted = 1` on discount delete)
* **`users`**, **`enrollments`**, **`classes`**:
* `SELECT` (validate student scopes)
### 19. [finance-banking.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/finance-banking.controller.js)
* **`bank_accounts`**:
* `SELECT` (list cash/assets balances)
* `INSERT` (create account: `uid`, `name`, `bank_name`, `account_number`, `account_type`, `currency`, `opening_balance`, `current_balance`, `chart_account_id`, `is_active`)
* `UPDATE` (modify account settings; soft-delete with `is_deleted = 1`, `is_active = 0`)
* **`bank_reconciliations`**:
* `SELECT` (list bank recon history)
* `INSERT` (create draft recon: `uid`, `bank_account_id`, `period_start`, `period_end`, `statement_balance`, `status = 'draft'`, `created_by`)
* `UPDATE` (modify status: `'balanced'` or `'discrepancy'`, setting `book_balance`)
* **`bank_reconciliation_lines`**:
* `SELECT` (fetch lines list)
* `INSERT` (auto-match statement lines: `uid`, `reconciliation_id`, `payment_id`, `statement_date`, `statement_amount`, `statement_reference`, `match_status`)
* `UPDATE` (resolve line match: update `match_status` and `payment_id`)
* **`chart_of_accounts`**, **`payments`**:
* `SELECT` (verify transaction linkages)
### 20. [finance-coa.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/finance-coa.controller.js)
* **`chart_of_accounts`**:
* `SELECT` (ledger codes flat lists and tree hierarchies)
* `INSERT` (create account: `uid`, `code`, `name`, `type`, `parent_id`, `color`, `is_active`)
* `UPDATE` (modify codes attributes; soft-delete with `is_deleted = 1`, `is_active = 0`)
* **`bank_accounts`**:
* `SELECT` (constraint validation checks)
### 21. [finance-engine.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/finance-engine.controller.js)
* **`student_groups`**:
* `SELECT` (groups search and detail metrics)
* `INSERT` (create student cohort: `uid`, `name`, `description`, `type`, `color`, `class_id`, `capacity`, `auto_track_class`, `created_by`)
* `UPDATE` (modify properties; soft-delete with `is_deleted = 1`)
* **`student_group_members`**:
* `SELECT` (member details and student count calculations)
* `INSERT` (add member mapping: `uid`, `group_id`, `student_id`)
* `UPDATE` (soft-delete with `is_deleted = 1`)
* **`student_fees`**:
* `SELECT` (check applied plans)
* `INSERT` (apply plan installment: `uid`, `student_id`, `fee_group_id`, `plan_id`, `amount`, `due_date`, `status = 'pending'`, `sync_status = 'pending'`)
* `UPDATE` (re-aggregate discount_amount; soft-delete with `is_deleted = 1` and `sync_status = 'pending'` when revoking unpaid plans)
* **`student_fee_discounts`**:
* `SELECT` (applied discount lines checks)
* `INSERT` (apply discount: `uid`, `student_fee_id`, `discount_id`, `amount_applied`, `applied_by`)
* `UPDATE` (modify amount_applied, or soft-delete with `is_deleted = 1`)
* **`classes`**, **`users`**, **`enrollments`**, **`fee_plans`**, **`fee_plan_installments`**, **`discounts`**, **`fee_groups`**:
* `SELECT` (scope verification checks)
### 22. [finance-invoices.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/finance-invoices.controller.js)
* **`invoices`**:
* `SELECT` (invoice search pages index)
* `INSERT` (create invoice: `uid`, `invoice_number`, `student_id`, `fee_group_id`, `plan_id`, `period_label`, `subtotal`, `discount_amount`, `total`, `paid_amount = 0`, `currency`, `status = 'issued'`, `issued_at`, `due_date`, `notes`, `created_by`)
* `UPDATE` (modify status, notes, subtotal; soft-delete with `is_deleted = 1` and status `'cancelled'`)
* **`users`**, **`fee_groups`**, **`student_fees`**, **`fee_plans`**:
* `SELECT` (verify billing components)
### 23. [finance-suppliers.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/finance-suppliers.controller.js)
* **`suppliers`**:
* `SELECT` (supplier details search catalog list)
* `INSERT` (create supplier: `uid`, `name`, `code`, `contact_person`, `email`, `phone`, `address`, `city`, `country`, `tax_id`, `bank_details`, `notes`, `is_active`, `sync_status = 'pending'`)
* `UPDATE` (modify supplier details; soft-delete with `is_deleted = 1`, `is_active = 0`)
### 24. [finance-trips.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/finance-trips.controller.js)
* **`trips`**:
* `SELECT` (bookings catalog)
* `INSERT` (create field trip: `uid`, `name`, `destination`, `departure_date`, `return_date`, `cost_per_student`, `total_capacity`, `status = 'draft'`, `description`, `created_by`)
* `UPDATE` (modify trip details; soft-delete with `is_deleted = 1`, `status = 'cancelled'`)
* **`trip_payments`**:
* `SELECT` (passenger booking accounts checks)
* `INSERT` (assign student passenger: `uid`, `trip_id`, `student_id`, `invoiced_amount`, `status = 'pending'`, `created_by`)
* `UPDATE` (pay/refund: `paid_amount`, `status`, `paid_at`, `updated_at`; soft-delete with `is_deleted = 1` and `status = 'cancelled'`)
* **`users`**, **`enrollments`**:
* `SELECT` (look up student eligibility)
### 25. [finance.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/finance.controller.js)
*(Note: Proxies payroll routes to `hr.controller.js`)*
* **`payments`**:
* `SELECT` (sum payment amounts this month)
* **`student_fees`**:
* `SELECT` (sum outstanding amount: `amount - paid_amount`)
* **`payroll_runs`**:
* `SELECT` (details of latest run)
* **`expenses`**:
* `SELECT` (list with filters/pagination)
* `INSERT` (create expense: `uid`, `category`, `subcategory`, `description`, `amount`, `expense_date`, `vendor`, `invoice_number`, `payment_method`, `status`, `receipt_image`, `created_by`)
* `UPDATE` (modify fields, change status to `'approved'`/`'rejected'`, or soft-delete with `is_deleted = 1`)
* **`invoices`**, **`trips`**, **`bank_reconciliations`**, **`student_groups`**:
* `SELECT` (additional tile aggregations)
### 26. [front-office.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/front-office.controller.js)
* **`visitor_logs`**:
* `SELECT` (visitor log registry)
* `INSERT` (log visitor check-in: `uid`, `visitor_name`, `visitor_type`, `phone`, `email`, `id_number`, `purpose`, `person_to_visit`, `badge_number`, `remarks`, `created_by`)
* `UPDATE` (check out: set `check_out` time; soft-delete with `is_deleted = 1`)
* **`admission_enquiries`**:
* `SELECT` (enquiry follow-up checklist)
* `INSERT` (create enquiry: `uid`, `full_name`, `email`, `phone`, `address`, `class_interested`, `source`, `follow_up_date`, `notes`, `next_action`, `assigned_to`)
* `UPDATE` (modify enquiry details; soft-delete with `is_deleted = 1`)
* **`complaints`**:
* `SELECT` (complaint tracking files)
* `INSERT` (file complaint: `uid`, `complainant_name`, `complainant_type`, `contact_phone`, `contact_email`, `category`, `priority`, `subject`, `description`, `assigned_to`, `status = 'open'`)
* `UPDATE` (resolve complaint: set `status = 'resolved'`, `resolution`, `resolved_by`, `resolved_at`)
* **`phone_call_logs`**:
* `SELECT` (phone checklists logs)
* `INSERT` (single / bulk record phone logs: `uid`, `caller_name`, `caller_phone`, `caller_type`, `direction`, `call_time`, `duration_seconds`, `purpose`, `response`, `follow_up_required`, `follow_up_date`, `handled_by`, `notes`)
* `UPDATE` (modify details; clear follow-up: set `follow_up_required = 0`; soft-delete with `is_deleted = 1`)
* **`postal_dispatch`**:
* `SELECT` (mailroom delivery registries)
* `INSERT` (single / bulk log mail items: `uid`, `type`, `reference_number`, `sender`, `receiver`, `address`, `courier`, `tracking_number`, `dispatch_date`, `description`, `status = 'pending'`, `created_by`)
* `UPDATE` (modify details; mark sent: set `status = 'sent'`; mark delivered: set `status = 'delivered'`, `received_date`; soft-delete with `is_deleted = 1`)
* **`users`**:
* `SELECT` (consultant names check)
### 27. [grades.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/grades.controller.js)
* **`grades`**:
* `SELECT` (list results with filters, calculate average summaries)
* `INSERT` (single and bulk: `uid`, `student_id`, `subject_id`, `exam_type`, `marks`, `total_marks`, `grade`, `grading_scale`, `remarks`, `graded_by`, `academic_year`, `term`)
* `UPDATE` (modify marks, grade letter, remarks, grading_scale, graded_by; soft-delete with `is_deleted = 1`)
* **`users`**, **`subjects`**, **`classes`**, **`enrollments`**:
* `SELECT` (verify relationships)
### 28. [hostel-attendance.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/hostel-attendance.controller.js)
* **`hostel_attendance`**:
* `SELECT` (hostel attendance counts checklist)
* `INSERT` (record check-in: `uid`, `hostel_id`, `room_id`, `student_id`, `date`, `status`, `marked_by`)
* `UPDATE` (modify status, marked_by, updated_at)
* **`hostels`**, **`rooms`**, **`room_assignments`**:
* `SELECT` (verify boarding occupancy)
* **`users`**, **`parent_students`**:
* `SELECT` (verify child boundaries)
### 29. [hostel.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/hostel.controller.js)
* **`hostels`**:
* `SELECT` (active hostels catalog list)
* `INSERT` (create hostel: `uid`, `name`, `code`, `type`, `address`, `warden_id`, `phone`, `description`)
* **`rooms`**:
* `SELECT` (rooms in hostel catalog)
* `INSERT` (create room: `uid`, `hostel_id`, `room_type_id`, `room_number`, `floor`, `bed_count`, `status = 'available'`, `description`)
* `UPDATE` (change status to `'available'`/`'occupied'`)
* **`room_assignments`**:
* `SELECT` (list assignments)
* `INSERT` (assign student to bed: `uid`, `student_id`, `room_id`, `bed_number`, `start_date`, `end_date`, `status = 'active'`, `remarks`, `assigned_by`)
* `UPDATE` (check out assignment: set `status = 'inactive'`, `end_date = today`)
* **`room_types`**:
* `SELECT` (list room configurations)
* **`hostel_attendance`**:
* `SELECT` (attendance logs index)
* `INSERT` / `UPDATE` (bulk upsert hostel attendance matching `(hostel_id, student_id, date)`)
* **`users`**:
* `SELECT` (warden/student validation checks)
### 30. [hr.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/hr.controller.js)
* **`staff_roles`**:
* `SELECT` (list active roles)
* `INSERT` (create role: `uid`, `name`, `description`, `is_teaching_role`, `permissions`)
* **`salary_grades`**:
* `SELECT` (list pay grades)
* `INSERT` (create salary grade: `uid`, `name`, `grade_level`, `basic_salary`, `housing_allowance`, `transport_allowance`, `medical_allowance`, `other_allowances`, `description`, `effective_date`)
* **`staff_records`**:
* `SELECT` (list employment cards details)
* `INSERT` (create record: `uid`, `user_id`, `staff_number`, `role_id`, `salary_grade_id`, `employment_type`, `appointment_date`, `bank_name`, `account_number`, `department`, `designation`)
* `UPDATE` (modify role, pay scale, active status; soft-delete with `is_deleted = 1`)
* **`leave_types`**:
* `SELECT` (list categories)
* **`leave_requests`**:
* `SELECT` (requests history lists)
* `INSERT` (submit request: `uid`, `staff_id`, `leave_type_id`, `start_date`, `end_date`, `days_count`, `reason`)
* `UPDATE` (approve/reject: set `status`, `rejection_reason`, `approved_by`, `approved_at`)
* **`staff_attendance`**:
* `SELECT` (roster index)
* `INSERT` (log day check: `uid`, `staff_id`, `date`, `status`, `remarks`, `marked_by`)
* **`payroll_runs`**:
* `SELECT` (list runs)
* `INSERT` (create run: `uid`, `period_month`, `period_year`, `description`, `status = 'draft'`, `created_by`)
* `UPDATE` (calculate totals: `total_gross`, `total_deductions`, `total_net`, `status = 'calculated'`; mark paid: set `status = 'paid'`, `paid_at`; cancel run: reset status to `'draft'`, zero out totals)
* **`payslips`**:
* `SELECT` (list slips)
* `INSERT` (generate payslips for run)
* `UPDATE` (cascading update to status `'paid'` and `paid_at`)
* `DELETE` (recalculating run deletes old slips)
* **`vacancies`**:
* `SELECT` (list vacancies)
* `INSERT` (create job posting: `uid`, `title`, `description`, `requirements`, `department`, `status = 'open'`, `closing_date`)
* `UPDATE` (modify details, status; soft-delete with `is_deleted = 1`)
* **`applicants`**:
* `SELECT` (list candidates)
* `INSERT` (add applicant: `uid`, `vacancy_id`, `first_name`, `last_name`, `email`, `phone`, `resume_url`, `status = 'pending'`, `notes`)
* `UPDATE` (modify status, interview_date, notes)
* **`users`**:
* `SELECT` (validate identities)
### 31. [inventory.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/inventory.controller.js)
* **`item_categories`**:
* `SELECT` (list categories)
* `INSERT` (create category: `uid`, `name`, `code`, `description`, `parent_id`)
* **`suppliers`**:
* `SELECT` (list vendors)
* `INSERT` (create vendor: `uid`, `name`, `code`, `contact_person`, `email`, `phone`)
* **`store_locations`**:
* `SELECT` (list store spaces)
* **`items`**:
* `SELECT` (list items)
* `INSERT` (create item card: `uid`, `name`, `code`, `barcode`, `description`, `category_id`, `unit`, `purchase_price`, `selling_price`, `reorder_level`, `supplier_id`)
* **`item_stock`**:
* `SELECT` (current inventory balances list)
* `INSERT` (create stock row if missing)
* `UPDATE` (increment/decrement quantity)
* **`stock_transactions`**:
* `INSERT` (record ledger transaction: `uid`, `item_id`, `store_id`, `type`, `quantity`, `unit_price`, `total_amount`, `reference_number`, `supplier_id`, `notes`, `created_by`)
* **`item_issues`**:
* `SELECT` (issued items tracking list)
* `INSERT` (issue item: `uid`, `item_id`, `issued_to_type`, `issued_to_id`, `quantity`, `purpose`, `return_due_date`, `notes`, `issued_by`)
* `UPDATE` (return items: update `returned_quantity`, `returned_date`, `status = 'returned'|'partially_returned'`, `condition_on_return`)
* **`users`**:
* `SELECT` (join issuer profile)
### 32. [library.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/library.controller.js)
* **`library_books`**:
* `SELECT` (catalog lists)
* `INSERT` (single and bulk book import: `uid`, `isbn`, `title`, `author`, `category`, `publisher`, `year_published`, `total_copies`, `available_copies`, `shelf_location`)
* `UPDATE` (modify book attributes, increase/decrease total/available copies; soft-delete with `is_deleted = 1`)
* **`library_issues`**:
* `SELECT` (borrow logs history)
* `INSERT` (borrow book copy: `uid`, `book_id`, `user_id`, `issue_date`, `due_date`, `status = 'issued'`, `issued_by`)
* `UPDATE` (return book: set `return_date`, `status = 'returned'|'lost'`)
* **`library_reservations`**:
* `SELECT` (booking reserves lists)
* `INSERT` (reserve copies: `uid`, `book_id`, `user_id`, `notes`)
* `UPDATE` (auto-fulfill when book is issued: set `status = 'fulfilled'`; cancel reservation: set `status = 'cancelled'`)
* **`library_fines`**:
* `SELECT` (fine accounts details)
* `INSERT` (fine citation: `uid`, `issue_id`, `user_id`, `amount`, `reason`, `status = 'unpaid'`)
* `UPDATE` (pay/waive: set `status = 'paid'|'waived'`)
* **`users`**:
* `SELECT` (verify member roles)
### 33. [marks.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/marks.controller.js)
* **`assignments`**:
* `SELECT` (caching max score scales verification checks)
* **`users`**:
* `SELECT` (caching student roles checks)
* **`submissions`**:
* `SELECT` (find existing submission matching student/assignment)
* `INSERT` (create submission: `uid`, `assignment_id`, `student_id`, `status = 'graded'`, `grade`, `feedback`, `graded_by`, `graded_at`)
* `UPDATE` (grade modification: `grade`, `feedback`, `graded_by`, `graded_at`, `status = 'graded'`)
### 34. [medical.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/medical.controller.js)
* **`student_medical_profiles`**:
* `SELECT` (retrieve student health profile cards)
* `INSERT` (create profile: `uid`, `student_id`, `blood_group`, `allergies`, `dietary_restrictions`, `contraindications`, `chronic_conditions`, `emergency_instructions`, `updated_by`)
* `UPDATE` (modify profile settings, emergency contacts; update last modifier `updated_by`)
* **`student_medication_logs`**:
* `SELECT` (list administrations history)
* `INSERT` (record dispensation: `uid`, `student_id`, `medication_name`, `dosage`, `administered_at`, `administered_by`, `reason`, `notes`)
* **`student_medical_history`**:
* `SELECT` (illness/injury history)
* `INSERT` (record diagnostic event: `uid`, `student_id`, `event_type`, `description`, `severity`, `onset_date`, `resolution_date`, `treatment_details`, `doctor_notes`, `is_visible_to_teachers`, `reported_by`)
* **`users`**, **`parent_students`**, **`enrollments`**, **`classes`**:
* `SELECT` (verify student access scope boundaries)
### 35. [messages.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/messages.controller.js)
* **`messages`**:
* `SELECT` (inbox/sent directories index, conversations threads)
* `INSERT` (send direct/group/announcement message: `uid`, `sender_id`, `recipient_id`, `body`, `subject`, `is_announcement`, `attachment_url`, `attachment_name`, `attachment_type`)
* `UPDATE` (mark read: `is_read = 1`; mark delivered: `is_delivered = 1`; soft-delete with `is_deleted = 1`)
* **`chat_groups`**:
* `SELECT` (conversations lists)
* **`chat_group_members`**:
* `SELECT` (membership access checks)
* **`clubs`**, **`club_chats`**, **`club_memberships`**:
* `SELECT` (extracurricular messages thread index)
* **`users`**:
* `SELECT` (recipient search directories)
### 36. [notices.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/notices.controller.js)
* **`notices`**:
* `SELECT` (pinboard index, detail fetch with audience filters)
* `INSERT` (create notice: `uid`, `title`, `content`, `category`, `priority`, `audience`, `is_pinned`, `created_by`, `expiry_date`)
* `UPDATE` (modify notice attributes; soft-delete with `is_deleted = 1`)
* **`users`**:
* `SELECT` (join creator names)
### 37. [payments.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/payments.controller.js)
* **`payments`**:
* `SELECT` (lists of invoices paid)
* `INSERT` (record transaction payment: `uid`, `student_fee_id`, `amount`, `payment_method`, `transaction_reference`, `payment_date`, `created_by`, `sync_status = 'pending'`)
* `UPDATE` (modify notes details; soft-delete with `is_deleted = 1`)
* **`student_fees`**:
* `SELECT` (check fee balances), `UPDATE` (increment `paid_amount`, modify status to `'paid'` / `'partial'`)
* **`users`**:
* `SELECT` (validate student name)
### 38. [reports.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/reports.controller.js)
*(Note: Completely READ-ONLY SELECT operations)*
* **`grades`**, **`attendance_new`**, **`student_fees`**, **`enrollments`**, **`users`**, **`classes`**, **`subjects`**:
* `SELECT` (aggregate term averages, attendance percentages, unpaid balances summaries)
### 39. [schoolSettings.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/schoolSettings.controller.js)
* **`school_settings`**:
* `SELECT` (get system details)
* `INSERT` / `UPDATE` (upsert global key-value preferences: `setting_key`, `setting_value`, `category`, `updated_by`)
### 40. [settings.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/settings.controller.js)
* **`settings`**:
* `SELECT` (list system configuration keys)
* `INSERT` / `UPDATE` (upsert preferences: `key`, `value`, `category`, `description`)
### 41. [social.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/social.controller.js)
* **`posts`**:
* `SELECT` (feed index)
* `INSERT` (create social post: `uid`, `author_id`, `content`, `image_url`)
* `UPDATE` (modify post; soft-delete with `is_deleted = 1`)
* **`post_comments`**:
* `SELECT` (comments for post)
* `INSERT` (post comment: `uid`, `post_id`, `author_id`, `content`)
* `UPDATE` (soft-delete with `is_deleted = 1`)
* **`post_likes`**:
* `SELECT` (likes count checks)
* `INSERT` (like a post: `uid`, `post_id`, `user_id`)
* `DELETE` (unlike a post: remove row matching user/post)
* **`users`**:
* `SELECT` (join author names)
### 42. [staff-attendance.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/staff-attendance.controller.js)
* **`staff_attendance`**:
* `SELECT` (attendance status details roster checklist)
* `INSERT` / `UPDATE` (bulk upsert checklist logs: `uid`, `staff_id`, `date`, `status`, `remarks`, `marked_by`, `sync_status = 'pending'`)
* **`users`**:
* `SELECT` (joining active staff names)
### 43. [students-fees.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/students-fees.controller.js)
*(Note: Completely READ-ONLY SELECT operations)*
* **`student_fees`**, **`fee_groups`**, **`fee_plans`**, **`student_fee_discounts`**, **`discounts`**, **`trip_payments`**, **`trips`**, **`users`**, **`parent_students`**:
* `SELECT` (scoped lookup of child balances, travel bookings, installment plans)
### 44. [students.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/students.controller.js)
*(Note: Completely READ-ONLY SELECT operations)*
* **`users`**, **`parent_students`**, **`enrollments`**, **`subjects`**, **`grades`**, **`submissions`**, **`assignments`**, **`exam_attempts`**, **`exam_schedules`**, **`exam_groups`**, **`attendance_new`**:
* `SELECT` (academic dashboard grade averages, term attendance percentages, exam schedule bounds)
### 45. [subjects.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/subjects.controller.js)
* **`subjects`**:
* `SELECT` (list classes courses)
* `INSERT` (create subject: `uid`, `name`, `code`, `class_id`, `teacher_id`, `description`, `credit_hours`)
* `UPDATE` (modify subject details; soft-delete with `is_deleted = 1`)
* **`classes`**, **`users`**:
* `SELECT` (validate bounds)
### 46. [sync.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/sync.controller.js)
* **SyncEngine Tables**:
* This controller initiates standard manual cloud sync cycles. Under the hood, the transaction engine targets **all sync-aware tables** having the `sync_status` column (including `users`, `grades`, `attendance_new`, `student_fees`, `expenses`, `visitor_logs`, `notices`, `assignments`, `classes`, etc.).
* `SELECT` (fetch rows where `sync_status = 'pending'`)
* `UPDATE` (update target rows to `sync_status = 'synced'` or `sync_status = 'conflict'` upon completion)
### 47. [sysadmin.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/sysadmin.controller.js)
*(Note: Technical infrastructure telemetry and diagnostics)*
* **`audit_logs`**:
* `SELECT` (count active logins last 24h, list recent technical audit lines)
* **`sqlite_master`**:
* `SELECT` (find tables containing `sync_status` column to verify conflicts)
### 48. [teacher.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/teacher.controller.js)
* **`syllabuses`**:
* `SELECT` (list syllabus catalog filterable by subject)
* `INSERT` (create syllabus: `uid`, `subject_id`, `title`, `description`, `file_path`, `version`, `created_by`)
* **`schemes_of_work`**:
* `SELECT` (list schemes)
* `INSERT` (create scheme: `uid`, `subject_id`, `teacher_id`, `term`, `academic_year`, `content`)
* **`focus_points`**:
* `SELECT` (topic items catalog)
* `INSERT` (create focus point: `uid`, `scheme_id`, `topic`, `objectives`, `key_concepts`, `resources_needed`)
* **`lesson_plans`**:
* `SELECT` (lesson plans details lists)
* `INSERT` (create plan: `uid`, `teacher_id`, `subject_id`, `scheme_id`, `focus_point_id`, `date`, `topic`, `objectives`, `introduction`, `development`, `conclusion`, `assessment_method`, `homework`, `reflections`)
* **`departments`**, **`subjects_new`**, **`users`**:
* `SELECT` (verify teacher departments constraints)
### 49. [transport-attendance.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/transport-attendance.controller.js)
* **`transport_attendance`**:
* `SELECT` (attendance logs lists)
* `INSERT` / `UPDATE` (bulk upsert roll-call logs matching `(route_id, student_id, date)`)
* **`routes`**, **`users`**, **`parent_students`**, **`transport_allocations`**:
* `SELECT` (check active student allocations on route)
### 50. [transport.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/transport.controller.js)
* **`vehicles`**:
* `SELECT` (list vehicles catalog)
* `INSERT` (create vehicle: `uid`, `registration_number`, `model`, `capacity`, `vehicle_type`, `fuel_type`, `status = 'active'`, `description`)
* **`routes`**:
* `SELECT` (list routes catalog)
* `INSERT` (create route: `uid`, `name`, `code`, `start_point`, `end_point`, `distance_km`, `estimated_time_minutes`, `status = 'active'`, `description`)
* **`vehicle_routes`**:
* `SELECT` (active assignments catalog)
* `INSERT` (create assignment: `uid`, `vehicle_id`, `route_id`, `driver_id`, `start_time = '07:00'`, `end_time = '16:00'`, `is_active = 1`)
* `UPDATE` (modify vehicle/driver details)
* **`transport_allocations`**:
* `SELECT` (active student passenger rolls)
* `INSERT` (allocate student passenger: `uid`, `student_id`, `route_id`, `pickup_point_id`, `vehicle_route_id`, `fee_amount`, `effective_from`, `effective_to`, `is_active = 1`)
* `UPDATE` (deallocate passenger: set `is_active = 0`, `is_deleted = 1`)
* **`pickup_points`**:
* `SELECT` (list pickup locations)
* **`transport_attendance`**:
* `SELECT` (list attendance)
* `INSERT` / `UPDATE` (bulk upsert attendance matching `(route_id, student_id, date)`)
* **`users`**:
* `SELECT` (verify drivers/students)
### 51. [users.controller.js](file:///E:/nextgen/next-gen/server/src/controllers/users.controller.js)
* **`users`**:
* `SELECT` (list directory, authentication detail fetch)
* `INSERT` (register user: `uid`, `email`, `password`, `role`, `first_name`, `last_name`, `phone`, `is_active = 1`, `created_at`)
* `UPDATE` (modify user details, profile_image, active status, reset password; soft-delete with `is_deleted = 1`)
* **`parent_students`**:
* `SELECT` (check linkages)
* `INSERT` (link child student to parent: `uid`, `parent_id`, `student_id`)
* `UPDATE` (unlink student: soft-delete with `is_deleted = 1`)
Reference in New Issue View Git Blame Copy Permalink