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}