docs/database latex.md

\documentclass[12pt]{article}
\usepackage[margin=1in]{geometry}
\usepackage{enumitem}
\usepackage{hyperref}
\usepackage{setspace}
\setstretch{1.1}

\begin{document}

\title{Database Design Overview}
\author{Interactive Platform for Learning and Reciting Qur'an}
\date{June 5, 2026}
\maketitle

\section{Database Creation}

The schema was designed for a PostgreSQL backend. It uses native PostgreSQL types, enums, identity columns, and referential integrity rules to model users, sessions, payments, reviews, wallets, and recitations.

Key creation steps include:
\begin{itemize}[leftmargin=*]
  \item Defining enum types for user roles, genders, Quran levels, specializations, live status, day of week, session status, payment methods, payment status, and transaction type.
  \item Creating a reusable trigger function to keep updated timestamps current.
  \item Using identity columns for all primary keys to provide auto-generated numeric IDs.
  \item Establishing separate profile tables for students and sheikhs with one-to-one mandatory relationships to the users table.
\end{itemize}

\section{Schema Implementation}

The implementation includes the following core entities:
\begin{itemize}[leftmargin=*]
  \item \textbf{users}: stores account credentials, contact details, role, activation state, and creation time.
  \item \textbf{student\_profile}: captures student-specific details such as gender, birthdate, and Quran proficiency level.
  \item \textbf{sheikh\_profile}: captures instructor-specific details such as biography, specialization, experience, certificates, hourly rate, rating, live availability status, and last active timestamp.
  \item \textbf{availability}: records weekly availability slots for sheikhs, including day of week and time ranges.
  \item \textbf{recitations}: stores recitation practice details, including Quran passage boundaries, mistake reports, evaluation scores, and student association.
  \item \textbf{session}: tracks scheduled live sessions, actual start/end times, meeting link, status, session notes, and links to the student and sheikh users.
  \item \textbf{payment}: holds payment records for sessions, including amount, currency, method, transaction status, and associations to student, sheikh, and session.
  \item \textbf{review}: stores a single review per session with rating and comment.
  \item \textbf{wallet}: provides an optional wallet balance for a user.
  \item \textbf{wallet\_transaction}: records wallet transactions, linking them to payments, sessions, and wallets.
\end{itemize}

The schema uses referential constraints to ensure consistency across related entities. For example, `student\_profile.studentId` and `sheikh\_profile.sheikhId` reference `users.userId` with cascade delete and update, guaranteeing that profile records are removed when a user is deleted.

\section{Constraints}

The database uses several types of constraints to enforce data integrity:
\begin{itemize}[leftmargin=*]
  \item \textbf{Primary keys}: each table has a primary key defined with `INT GENERATED ALWAYS AS IDENTITY`.
  \item \textbf{Unique constraints}: `users.email`, `users.phone`, `payment.transaction\_id`, `payment.sessionId`, `review.sessionId`, and `wallet.userId` are unique to prevent duplication.
  \item \textbf{Foreign keys}: session, payment, review, wallet, and availability tables all reference the users table or related tables, enforcing valid relationships.
  \item \textbf{Check constraints}: the `review.rate` column restricts rating values to the range 1--5.
  \item \textbf{NOT NULL constraints}: required attributes such as `email`, `passHash`, `fullName`, `role`, `gender`, `birthdate`, `surahStart`, `ayahtStart`, and `scheduledStart` are non-nullable.
  \item \textbf{ENUM constraints}: domain-specific values are restricted using PostgreSQL enum types rather than free-text values.
\end{itemize}

Referential actions are chosen to reflect business rules:
\begin{itemize}[leftmargin=*]
  \item `ON DELETE CASCADE` for profiles, availability, recitations, payment, review, and wallet transactions when parent records are deleted.
  \item `ON DELETE RESTRICT` for session references inside payment and review to preserve historical payment and review data when sessions still exist.
  \item `ON DELETE SET NULL` for `wallet.userId` in the wallet table to allow soft removal of the user reference.
\end{itemize}

\section{Indexing}

The schema relies primarily on implicit indexes created by primary and unique constraints.

Implicit indexes include:
\begin{itemize}[leftmargin=*]
  \item Primary key indexes on all ID columns.
  \item Unique indexes on `users.email`, `users.phone`, `payment.transaction\_id`, `payment.sessionId`, `review.sessionId`, and `wallet.userId`.
\end{itemize}

Additional indexing opportunities based on the schema:
\begin{itemize}[leftmargin=*]
  \item Indexing foreign key columns such as `session.studentId`, `session.sheikhId`, `payment.studentId`, and `payment.sheikhId` would improve join performance.
  \item Indexing `availability.sheikhId` and `recitations.studentId` can speed up lookups for teacher availability and student history.
  \item Indexing `session.status` or `session.scheduledStart` may improve queries that retrieve upcoming sessions or filter by session state.
\end{itemize}

\section{Triggers and Audit Columns}

To maintain `updatedAt` values automatically, the schema defines a trigger function `update\_updated\_at\_column()` and attaches it to the `session` and `wallet` tables. This ensures that every update to these rows refreshes the timestamp without requiring manual application logic.

\end{document}