# Database Schema Design

This document details the exact implementations of the tables constructed for TOPIQ based on Phase 1 instructions.

## Core Entities

### 1. `users`
Supports base identity, normalizations, roles, and status for all account types.
- **`id`**: INT, Primary Key, Auto Increment
- **`full_name`**: VARCHAR(255)
- **`mobile_normalized`**: VARCHAR(20) UNIQUE
- **`email_normalized`**: VARCHAR(255) UNIQUE
- **`password_hash`**: VARCHAR(255)
- **`role`**: ENUM('ASM', 'COORDINATOR', 'STUDENT', 'ADMIN')
- **`status`**: ENUM('PENDING', 'ACTIVE', 'SUSPENDED', 'DISABLED')
- **`mobile_verified_at`**: TIMESTAMP
- **`created_at`**, **`updated_at`**: TIMESTAMP

### 2. `areas`
Geographic hierarchy implementation.
- **`id`**: INT, Primary Key, Auto Increment
- **`name`**: VARCHAR(255) UNIQUE
- **`parent_area_id`**: INT, FK to `areas.id`

### 3. Profiles (Hierarchy Extension)
Each table stores specific metadata and solidifies hierarchy relationships.
- **`asm_profiles`**: Linked to `users.id` and `areas.id`.
- **`coordinator_profiles`**: Linked to `users.id`, `asm_id` (FK to `users.id`), and `areas.id`.
- **`student_profiles`**: Linked to `users.id`, `coordinator_id`, `asm_id`. Contains `class_id` and `registration_status`.

### 4. Referral System
- **`referral_codes`**: Unique unpredictable codes tied to `owner_user_id`. Tracks purpose, max uses, expiry.
- **`referral_events`**: Immutable audit log linking `referral_code_id`, `referrer_user_id`, and `referred_user_id`. UNIQUE constraint on `referred_user_id` ensures only one primary referral per user.

### 5. Utilities
- **`classes`**: Configurable metadata for students.
- **`otp_challenges`**: Short-lived verification records.
- **`refresh_sessions`**: Token rotation tracking.
- **`audit_logs`**: General JSON-based tracking for key user actions.

## Integrity Policies
- **Foreign Keys**: Enabled for all logical links.
- **Cascades**: Used where an identity removal logically mandates profile/session removal (`ON DELETE CASCADE`).
- **Restricts**: Prevents deleting an ASM or Coordinator if they have active students (`ON DELETE RESTRICT` from `student_profiles`).
- **Timezones**: Handled via `TIMESTAMP` default implementations matching UTC conversions if DB initialized correctly.