Data Schema & Technical Specification

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).

​1. Core & Institutional Identity

​schools

​users

​registrations

​2. Staff & Resource Management

​staff

​staff_student

​staff_address

​staff_designation

​staff_leave

​3. Student & Parent Ecosystem

​students

​student_address

​student_guardian

​parent_details

​4. Academic & Daily Operations

​academic_events

​student_attendance

​noticeboards

​food_menus

​food_menu_schedule

​5. Communication & Engagement

​digital_diary

​activities

​write_to

​write_to_replies

​comments

​reactions

​6. Infrastructure & Security

​parent_devices

​staff_sessions

​user_sessions

​failed_jobs

​personal_access_tokens

​Pulse Monitoring

​Relational Architecture

​1. Multi-Tenancy Logic

​2. Parent-Student Linkage

​3. Soft Deletion Policy