> ## Documentation Index
> Fetch the complete documentation index at: https://docs.criar.in/llms.txt
> Use this file to discover all available pages before exploring further.

# Data Schema & Technical Specification

> Comprehensive technical specification of the Skole project database schema, detailing tables, fields, constraints, and relationships.

## 1. Core & Institutional Identity

These tables define the school entities, administrative users, and the initial registration lifecycle.

### `schools`

Stores the metadata for each school instance.

| Column        | Type           | Constraints       | Description                           |
| :------------ | :------------- | :---------------- | :------------------------------------ |
| `id`          | `Int`          | PK, AutoIncrement | Internal database ID.                 |
| `skole_id`    | `VarChar(15)`  | Unique, Not Null  | Publicly visible school identifier.   |
| `user_id`     | `Int`          | FK (users)        | Owner/Primary Admin of the school.    |
| `school_name` | `VarChar(255)` | Not Null          | Official institution name.            |
| `email`       | `String`       | Nullable          | Primary school contact email.         |
| `phone_no`    | `String`       | Nullable          | Primary contact number.               |
| `address_1`   | `VarChar(255)` | Not Null          | Primary address line.                 |
| `address_2`   | `VarChar(255)` | Nullable          | Secondary address line.               |
| `city`        | `VarChar(150)` | Not Null          | Registered city.                      |
| `state`       | `VarChar(100)` | Not Null          | Registered state.                     |
| `pincode`     | `Int`          | Not Null          | Area pin code.                        |
| `country`     | `String`       | Nullable          | Country name.                         |
| `is_active`   | `SmallInt`     | Default 1         | Status flag (1: Active, 0: Inactive). |

### `users`

Global administrative users responsible for managing the platform.

| Column               | Type            | Constraints       | Description               |
| :------------------- | :-------------- | :---------------- | :------------------------ |
| `id`                 | `BigInt`        | PK, AutoIncrement | Unique admin identifier.  |
| `name`               | `VarChar(191)`  | Not Null          | Admin's full name.        |
| `email`              | `VarChar(191)`  | Unique            | Login credential email.   |
| `phone`              | `BigInt`        | Not Null          | Contact phone number.     |
| `password`           | `VarChar(191)`  | Not Null          | Hashed password (Bcrypt). |
| `designation`        | `VarChar(150)`  | Nullable          | Admin role/title.         |
| `privilege`          | `VarChar(50)`   | Nullable          | Access level.             |
| `profile_photo_path` | `VarChar(2048)` | Nullable          | Path to profile image.    |

### `registrations`

Tracks new school registration requests from the public landing page.

| Column              | Type            | Constraints       | Description                                           |
| :------------------ | :-------------- | :---------------- | :---------------------------------------------------- |
| `id`                | `Int`           | PK, AutoIncrement | Registration ID.                                      |
| `full_name`         | `VarChar(255)`  | Not Null          | Registrant's full name.                               |
| `email`             | `VarChar(255)`  | Not Null          | Registrant's email.                                   |
| `mobile_no`         | `Decimal(10,0)` | Not Null          | Contact mobile.                                       |
| `school_name`       | `VarChar(255)`  | Not Null          | Requested school name.                                |
| `school_address`    | `VarChar(255)`  | Not Null          | Physical location.                                    |
| `children_enrolled` | `VarChar(255)`  | Not Null          | Expected scale.                                       |
| `registrant_role`   | `VarChar(255)`  | Not Null          | Relationship to school.                               |
| `school_type`       | `VarChar(255)`  | Not Null          | Primary/Secondary/etc.                                |
| `is_converted`      | `SmallInt`      | Default 0         | 1 if registration turned into a full school instance. |

***

## 2. Staff & Resource Management

Manages school employees, their roles (RBAC), and their assignments to students.

### `staff`

Primary registry for teachers and school staff.

| Column        | Type           | Constraints       | Description                          |
| :------------ | :------------- | :---------------- | :----------------------------------- |
| `id`          | `Int`          | PK, AutoIncrement | Unique staff ID.                     |
| `skole_id`    | `VarChar(15)`  | FK                | Multi-tenant link.                   |
| `name`        | `VarChar(100)` | Nullable          | Staff member name.                   |
| `designation` | `VarChar(100)` | Nullable          | Job title.                           |
| `phone_no`    | `VarChar(50)`  | Unique            | Login credential (Phone).            |
| `email`       | `VarChar(255)` | Nullable          | Contact email.                       |
| `pin`         | `Int`          | Nullable          | 4-digit mobile app PIN.              |
| `rbac`        | `Json`         | Nullable          | Fine-grained permission definitions. |
| `status`      | `Int`          | Default 0         | 1: Active, 0: Suspended.             |

### `staff_student`

Mapping table that assigns students to specific teachers.

| Column       | Type          | Constraints       | Description           |
| :----------- | :------------ | :---------------- | :-------------------- |
| `id`         | `Int`         | PK, AutoIncrement | Mapping ID.           |
| `skole_id`   | `VarChar(15)` | FK                | Multi-tenant link.    |
| `staff_id`   | `Int`         | FK (staff)        | The assigned teacher. |
| `student_id` | `Int`         | FK (students)     | The assigned student. |

### `staff_address`

Stores residential information for staff members.

| Column         | Type           | Constraints       | Description               |
| :------------- | :------------- | :---------------- | :------------------------ |
| `id`           | `Int`          | PK, AutoIncrement | Address ID.               |
| `skole_id`     | `VarChar(15)`  | FK                | Multi-tenant link.        |
| `staff_id`     | `Int`          | FK (staff)        | Linked staff member.      |
| `address_type` | `VarChar(100)` | Nullable          | e.g. 'local', 'permanent' |
| `address`      | `VarChar(255)` | Nullable          | Street address.           |
| `city`         | `VarChar(255)` | Nullable          | City name.                |
| `state`        | `VarChar(255)` | Nullable          | State name.               |
| `pincode`      | `Int`          | Nullable          | Area pin code.            |

### `staff_designation`

Pre-defined job roles with template RBAC permissions.

| Column        | Type           | Constraints       | Description             |
| :------------ | :------------- | :---------------- | :---------------------- |
| `id`          | `Int`          | PK, AutoIncrement | Designation ID.         |
| `skole_id`    | `VarChar(15)`  | FK                | Multi-tenant link.      |
| `designation` | `VarChar(100)` | Not Null          | Role name.              |
| `rbac`        | `Json`         | Not Null          | Associated permissions. |

### `staff_leave`

Leave applications and approval status for school employees.

| Column            | Type           | Constraints       | Description                  |
| :---------------- | :------------- | :---------------- | :--------------------------- |
| `id`              | `Int`          | PK, AutoIncrement | Leave ID.                    |
| `skole_id`        | `VarChar(15)`  | FK                | Multi-tenant link.           |
| `staff_id`        | `Int`          | FK (staff)        | Applying staff member.       |
| `leave_date_from` | `Date`         | Nullable          | Start date.                  |
| `leave_date_to`   | `Date`         | Nullable          | End date.                    |
| `leave_reason`    | `VarChar(255)` | Nullable          | Justification.               |
| `leave_status`    | `VarChar(50)`  | Nullable          | e.g., 'Pending', 'Approved'. |

***

## 3. Student & Parent Ecosystem

The core registry relating students, their guardians, addresses, and parent authentication.

### `students`

The central model for all student-related data.

| Column              | Type           | Constraints       | Description                      |
| :------------------ | :------------- | :---------------- | :------------------------------- |
| `id`                | `Int`          | PK, AutoIncrement | Unique student ID.               |
| `skole_id`          | `VarChar(15)`  | FK                | Multi-tenant link.               |
| `roll_no`           | `VarChar(50)`  | Nullable          | School-assigned roll number.     |
| `student_full_name` | `VarChar(255)` | Nullable          | Full legal name.                 |
| `date_of_birth`     | `Date`         | Nullable          | Birthday.                        |
| `gender`            | `VarChar(45)`  | Nullable          | M/F/Other.                       |
| `grade`             | `VarChar(100)` | Nullable          | Current class (e.g., Grade 5-A). |
| `blood_group`       | `VarChar(45)`  | Nullable          | Critical medical info.           |
| `student_status`    | `VarChar(50)`  | Nullable          | active, archived, relieved.      |

### `student_address`

Physical address tracking for students (Permanent/Current).

| Column            | Type           | Constraints       | Description                   |
| :---------------- | :------------- | :---------------- | :---------------------------- |
| `id`              | `Int`          | PK, AutoIncrement | Address ID.                   |
| `skole_id`        | `VarChar(15)`  | FK                | Multi-tenant link.            |
| `student_roll_no` | `VarChar(100)` | Nullable          | Linked roll number.           |
| `address_type`    | `VarChar(100)` | Nullable          | e.g., 'permanent', 'current'. |
| `address`         | `VarChar(255)` | Nullable          | Full address.                 |
| `city`            | `VarChar(255)` | Nullable          | City.                         |
| `state`           | `VarChar(255)` | Nullable          | State.                        |
| `pincode`         | `Int`          | Nullable          | ZIP code.                     |

### `student_guardian`

Auxiliary guardian information (Alternative contacts).

| Column               | Type            | Constraints       | Description                       |
| :------------------- | :-------------- | :---------------- | :-------------------------------- |
| `id`                 | `Int`           | PK, AutoIncrement | Guardian ID.                      |
| `skole_id`           | `VarChar(15)`   | FK                | Multi-tenant link.                |
| `student_roll_no`    | `VarChar(255)`  | Nullable          | Linked student.                   |
| `guardian_name`      | `VarChar(255)`  | Nullable          | Name of guardian.                 |
| `relationship`       | `VarChar(150)`  | Not Null          | e.g., 'Father', 'Mother', 'Aunt'. |
| `address`            | `VarChar(255)`  | Nullable          | Guardian's address.               |
| `phone_no`           | `Decimal(10,0)` | Nullable          | Contact number.                   |
| `alternate_phone_no` | `Decimal(10,0)` | Nullable          | Secondary contact.                |

### `parent_details`

Authentication and profile data for parents.

| Column            | Type            | Constraints       | Description                         |
| :---------------- | :-------------- | :---------------- | :---------------------------------- |
| `id`              | `Int`           | PK, AutoIncrement | Primary ID.                         |
| `skole_id`        | `VarChar(15)`   | FK                | Multi-tenant link.                  |
| `student_roll_no` | `VarChar(255)`  | Nullable          | Linked roll number.                 |
| `father_name`     | `VarChar(255)`  | Nullable          | Father's name.                      |
| `mother_name`     | `VarChar(255)`  | Nullable          | Mother's name.                      |
| `father_phone_no` | `Decimal(10,0)` | Nullable          | Login credential (Primary).         |
| `mother_phone_no` | `Decimal(10,0)` | Nullable          | Secondary contact.                  |
| `pin`             | `Int`           | Nullable          | Parent app access PIN.              |
| `fcm_token`       | `VarChar(255)`  | Nullable          | Latest push token for quick lookup. |

***

## 4. Academic & Daily Operations

Tracks the daily flow of school life: attendance, food, calendar, and notices.

### `academic_events`

School calendar events, holidays, and exams.

| Column            | Type           | Constraints       | Description              |
| :---------------- | :------------- | :---------------- | :----------------------- |
| `id`              | `Int`          | PK, AutoIncrement | Event ID.                |
| `skole_id`        | `VarChar(15)`  | FK                | Multi-tenant link.       |
| `title`           | `VarChar(100)` | Nullable          | Event title.             |
| `description`     | `VarChar(255)` | Nullable          | Event summary.           |
| `event_type`      | `VarChar(50)`  | Nullable          | e.g., 'holiday', 'exam'. |
| `event_from_date` | `Date`         | Nullable          | Start date.              |
| `event_to_date`   | `Date`         | Nullable          | End date.                |

### `student_attendance`

Daily presence tracking.

| Column                | Type           | Constraints       | Description                |
| :-------------------- | :------------- | :---------------- | :------------------------- |
| `id`                  | `Int`          | PK, AutoIncrement | Attendance record ID.      |
| `skole_id`            | `VarChar(15)`  | FK                | Multi-tenant link.         |
| `student_id`          | `Int`          | FK (students)     | Targeted student.          |
| `date`                | `Date`         | Nullable          | Attendance date.           |
| `checkedIn_at`        | `Timestamp`    | Nullable          | Time of arrival.           |
| `CheckedOut_at`       | `Timestamp`    | Nullable          | Time of departure.         |
| `attendance_status`   | `VarChar(20)`  | Enum              | Present, Absent, Late.     |
| `availability_status` | `VarChar(100)` | Enum              | IN, OUT (Premises status). |

### `noticeboards`

Official announcements broadcasted to the school or specific segments.

| Column         | Type           | Constraints       | Description                |
| :------------- | :------------- | :---------------- | :------------------------- |
| `id`           | `Int`          | PK, AutoIncrement | Notice ID.                 |
| `skole_id`     | `VarChar(15)`  | FK                | Multi-tenant link.         |
| `notice_title` | `VarChar(255)` | Nullable          | Heading.                   |
| `notice_desc`  | `VarChar(255)` | Nullable          | Content summary.           |
| `notice_type`  | `VarChar(255)` | Nullable          | e.g., 'urgent', 'general'. |

### `food_menus`

Defines available food items and nutritional information.

| Column            | Type           | Constraints       | Description        |
| :---------------- | :------------- | :---------------- | :----------------- |
| `id`              | `Int`          | PK, AutoIncrement | Menu ID.           |
| `skole_id`        | `VarChar(15)`  | FK                | Multi-tenant link. |
| `food_menu_title` | `VarChar(255)` | Nullable          | Menu name.         |
| `food_menu_items` | `VarChar(255)` | Nullable          | List of items.     |
| `menu_nutrients`  | `VarChar(255)` | Nullable          | Healthy info.      |
| `status`          | `VarChar(25)`  | Nullable          | active/inactive.   |

### `food_menu_schedule`

Scheduling for menus (which menu on which day).

| Column         | Type           | Constraints       | Description          |
| :------------- | :------------- | :---------------- | :------------------- |
| `id`           | `Int`          | PK, AutoIncrement | Schedule ID.         |
| `skole_id`     | `VarChar(15)`  | FK                | Multi-tenant link.   |
| `food_menu_id` | `Int`          | FK (food\_menus)  | Linked menu.         |
| `menu_day`     | `VarChar(255)` | Nullable          | Weekday.             |
| `menu_period`  | `VarChar(255)` | Nullable          | Breakfast/Lunch/etc. |

***

## 5. Communication & Engagement

Interactive modules for homework, activities, and ticketing.

### `digital_diary`

Assignments and logs posted to specific grades or students.

| Column           | Type           | Constraints       | Description                 |
| :--------------- | :------------- | :---------------- | :-------------------------- |
| `id`             | `Int`          | PK, AutoIncrement | Entry ID.                   |
| `skole_id`       | `VarChar(15)`  | FK                | Multi-tenant link.          |
| `staff_id`       | `Int`          | FK (staff)        | Authoring teacher.          |
| `grade`          | `VarChar(50)`  | Nullable          | Targeted class.             |
| `diary_type`     | `VarChar(50)`  | Nullable          | e.g., 'homework', 'notice'. |
| `title`          | `VarChar(255)` | Nullable          | Heading.                    |
| `description`    | `Text`         | Nullable          | Full details.               |
| `due_date`       | `Date`         | Nullable          | Submission deadline.        |
| `attachment_url` | `VarChar(500)` | Nullable          | Linked file.                |
| `status`         | `VarChar(25)`  | Nullable          | active/inactive.            |

### `activities`

Visual logs of student participation and extracurricular updates.

| Column          | Type           | Constraints       | Description             |
| :-------------- | :------------- | :---------------- | :---------------------- |
| `id`            | `Int`          | PK, AutoIncrement | Activity ID.            |
| `skole_id`      | `VarChar(15)`  | FK                | Multi-tenant link.      |
| `title`         | `VarChar(255)` | Nullable          | Activity name.          |
| `description`   | `Text`         | Nullable          | Details.                |
| `activity_type` | `VarChar(50)`  | Nullable          | e.g., 'sports', 'arts'. |
| `posted_by`     | `VarChar(50)`  | Nullable          | Author role.            |
| `posted_by_id`  | `Int`          | Nullable          | Author ID.              |

### `write_to`

Direct communication/ticketing from parents to staff.

| Column         | Type           | Constraints       | Description           |
| :------------- | :------------- | :---------------- | :-------------------- |
| `id`           | `Int`          | PK, AutoIncrement | Ticket ID.            |
| `skole_id`     | `VarChar(15)`  | FK                | Multi-tenant link.    |
| `parent_id`    | `Int`          | FK (parents)      | Initiating parent.    |
| `student_id`   | `Int`          | FK (students)     | Subject student.      |
| `request_type` | `VarChar(50)`  | Nullable          | e.g. 'Leave', 'Query' |
| `subject`      | `VarChar(255)` | Nullable          | Summary.              |
| `message`      | `Text`         | Nullable          | Full message.         |
| `status`       | `VarChar(25)`  | Nullable          | Open/Resolved/etc.    |
| `priority`     | `VarChar(25)`  | Nullable          | urgent/normal.        |

### `write_to_replies`

Messages in a ticket conversation threads.

| Column        | Type          | Constraints       | Description        |
| :------------ | :------------ | :---------------- | :----------------- |
| `id`          | `Int`         | PK, AutoIncrement | Reply ID.          |
| `skole_id`    | `VarChar(15)` | FK                | Multi-tenant link. |
| `write_to_id` | `Int`         | FK (write\_to)    | Parent ticket.     |
| `replied_by`  | `VarChar(50)` | Nullable          | staff/parent.      |
| `message`     | `Text`        | Nullable          | Reply content.     |
| `is_read`     | `SmallInt`    | Default 0         | Read status.       |

### `comments`

Social layer for comments on diary, activities, etc.

| Column        | Type          | Constraints       | Description              |
| :------------ | :------------ | :---------------- | :----------------------- |
| `id`          | `Int`         | PK, AutoIncrement | Comment ID.              |
| `skole_id`    | `VarChar(15)` | FK                | Multi-tenant link.       |
| `entity_type` | `VarChar(50)` | Not Null          | e.g. 'activity', 'diary' |
| `entity_id`   | `Int`         | Not Null          | Linked object ID.        |
| `content`     | `Text`        | Not Null          | Text content.            |

### `reactions`

Like/Love reactions on various entities.

| Column          | Type          | Constraints       | Description              |
| :-------------- | :------------ | :---------------- | :----------------------- |
| `id`            | `Int`         | PK, AutoIncrement | Reaction ID.             |
| `skole_id`      | `VarChar(15)` | FK                | Multi-tenant link.       |
| `entity_type`   | `VarChar(50)` | Not Null          | e.g. 'activity', 'diary' |
| `entity_id`     | `Int`         | Not Null          | Linked object ID.        |
| `reaction_type` | `VarChar(20)` | Not Null          | like, love, etc.         |

***

## 6. Infrastructure & Security

Tables managing background tasks, sessions, and database state.

### `parent_devices`

Registers parent devices for push notifications and mobile sessions.

| Column          | Type           | Constraints       | Description            |
| :-------------- | :------------- | :---------------- | :--------------------- |
| `id`            | `Int`          | PK, AutoIncrement | Device Record ID.      |
| `skole_id`      | `VarChar(15)`  | FK                | Multi-tenant link.     |
| `parent_id`     | `Int`          | FK                | Owner parent ID.       |
| `session_token` | `VarChar(255)` | Unique            | Auth token.            |
| `fcm_token`     | `String`       | Not Null          | Push token.            |
| `platform`      | `String`       | Not Null          | iOS/Android.           |
| `device_name`   | `String`       | Not Null          | e.g. 'iPhone 13'.      |
| `brand`         | `String`       | Not Null          | e.g. 'Apple'.          |
| `is_active`     | `SmallInt`     | Not Null          | 1: Active, 0: Expired. |

### `staff_sessions`

Active sessions for staff members on the mobile app.

| Column          | Type           | Constraints       | Description        |
| :-------------- | :------------- | :---------------- | :----------------- |
| `id`            | `Int`          | PK, AutoIncrement | Session ID.        |
| `skole_id`      | `VarChar(15)`  | FK                | Multi-tenant link. |
| `staff_id`      | `Int`          | FK (staff)        | Logged in staff.   |
| `session_token` | `VarChar(255)` | Unique            | Auth token.        |
| `platform`      | `VarChar(50)`  | Nullable          | OS.                |
| `is_active`     | `SmallInt`     | Default 1         | Status.            |

### `user_sessions`

Web sessions for administrative users.

| Column          | Type           | Constraints       | Description          |
| :-------------- | :------------- | :---------------- | :------------------- |
| `id`            | `Int`          | PK, AutoIncrement | Session ID.          |
| `user_id`       | `BigInt`       | FK (users)        | Admin user.          |
| `session_token` | `VarChar(255)` | Unique            | Web token.           |
| `device_name`   | `VarChar(255)` | Nullable          | Browser/Device info. |
| `is_active`     | `SmallInt`     | Default 1         | Status.              |

### `failed_jobs`

Logs for background worker failures.

| Column      | Type           | Constraints       | Description      |
| :---------- | :------------- | :---------------- | :--------------- |
| `id`        | `BigInt`       | PK, AutoIncrement | Error ID.        |
| `uuid`      | `VarChar(191)` | Not Null          | Unique trace ID. |
| `queue`     | `String`       | Not Null          | Target queue.    |
| `payload`   | `String`       | Not Null          | Raw data.        |
| `exception` | `String`       | Not Null          | Error message.   |
| `failed_at` | `Timestamp`    | Default Now       | Error time.      |

### `personal_access_tokens`

API tokens for different entities (admins, external integrations).

| Column           | Type           | Constraints       | Description        |
| :--------------- | :------------- | :---------------- | :----------------- |
| `id`             | `BigInt`       | PK, AutoIncrement | Token ID.          |
| `tokenable_type` | `VarChar(191)` | Not Null          | Model class name.  |
| `tokenable_id`   | `BigInt`       | Not Null          | Model instance ID. |
| `name`           | `VarChar(191)` | Not Null          | Token label.       |
| `token`          | `VarChar(64)`  | Not Null          | Hashed token.      |

### Pulse Monitoring

Tables used for performance tracking and resource usage.

* **`pulse_aggregates`**: Stores calculated metrics (sum, avg) over buckets of time.
* **`pulse_entries`**: Real-time snapshots of system metrics.
* **`pulse_values`**: Key-value pairs of system state at specific timestamps.

***

## Relational Architecture

### 1. Multi-Tenancy Logic

The `skole_id` is the **Universal Partition Key**. Every query in the application must include this filter to ensure that Data from School A is never visible to School B.

### 2. Parent-Student Linkage

Unlike a direct Foreign Key, parents are linked to students via:

* `parent_details` and `student_guardian` matching via `student_roll_no`.
* `staff_student` mapping for teacher access control.

### 3. Soft Deletion Policy

Content tables (`activities`, `digital_diary`, `noticeboards`, `write_to`) utilize an integer `deleted_status`.

* `0`: Visible/Active.
* `1`: Soft-deleted (Excluded from most queries).