# Authentication Database Design

---

**Document Version:** 1.0  
**Project:** Student Bazaar  
**Document Type:** Authentication Database Schema  
**Status:** Approved  
**Last Updated:** 28 June 2026  
**Author:** Nemmi Tangha

---

# 1. Overview

The Authentication module manages user registration, login, account security, email verification, password recovery, and user profile information.

The module consists of four tables:

1. users
2. user_profiles
3. email_verifications
4. password_reset_tokens

---

# 2. Table: users

## Purpose

Stores authentication and account-related information for every registered user.

This table contains only security and account management data.

Personal profile information is stored separately in the `user_profiles` table.

---

## Columns

| Column | Data Type | Nullable | Default | Key | Description |
|---------|-----------|----------|---------|-----|-------------|
| id | BIGINT | No | AUTO_INCREMENT | PK | Internal primary key |
| public_id | CHAR(36) | No | UUID | UNIQUE | Public identifier used in URLs and APIs |
| email | VARCHAR(255) | No | — | UNIQUE | User email address |
| password_hash | VARCHAR(255) | No | — | — | Secure hashed password |
| role | ENUM('student','super_admin') | No | student | INDEX | User role |
| account_status | ENUM('active','inactive','suspended','banned') | No | active | INDEX | Current account status |
| is_verified | BOOLEAN | No | FALSE | — | Email verification status |
| last_login | DATETIME | Yes | NULL | — | Last successful login |
| failed_login_attempts | INT | No | 0 | — | Number of consecutive failed login attempts |
| locked_until | DATETIME | Yes | NULL | INDEX | Account lock expiration time |
| last_password_change | DATETIME | Yes | NULL | — | Date and time of the most recent password change |
| created_at | DATETIME | No | CURRENT_TIMESTAMP | INDEX | Record creation time |
| updated_at | DATETIME | No | CURRENT_TIMESTAMP | — | Last update time (automatically updated on modification) |

---

## Relationships

- One User → One User Profile
- One User → Many Listings
- One User → Many Offers
- One User → Many Conversations
- One User → Many Messages
- One User → Many Notifications
- One User → Many Reports
- One User → One Premium Subscription (optional)

---

## Indexes

- PRIMARY KEY (`id`)
- UNIQUE (`public_id`)
- UNIQUE (`email`)
- INDEX (`role`)
- INDEX (`account_status`)
- INDEX (`locked_until`)
- INDEX (`created_at`)

---

## Business Rules

- Every user must have a unique email address.
- Passwords must always be securely hashed.
- A single account can both buy and sell products.
- Users cannot log in until their email has been verified.
- Public identifiers must never change after creation.
- Failed login attempts are tracked for every user.
- Successful login resets the failed login attempt counter.
- Accounts may be temporarily locked after exceeding the maximum allowed failed login attempts.
- Locked accounts cannot authenticate until the lock period expires.
- Password changes should update the `last_password_change` field.

---

# 3. Table: user_profiles

## Purpose

Stores personal profile information separately from authentication and security data.

---

## Columns

| Column | Data Type | Nullable | Default | Key | Description |
|---------|-----------|----------|---------|-----|-------------|
| id | BIGINT | No | AUTO_INCREMENT | PK | Internal primary key |
| public_id | CHAR(36) | No | UUID | UNIQUE | Public identifier |
| user_id | BIGINT | No | — | FK | References users.id |
| full_name | VARCHAR(150) | No | — | — | Student's full name |
| profile_picture | VARCHAR(255) | Yes | NULL | — | Profile image path |
| phone_number | VARCHAR(20) | Yes | NULL | — | Contact number |
| bio | TEXT | Yes | NULL | — | Short user biography |
| created_at | DATETIME | No | CURRENT_TIMESTAMP | — | Record creation time |
| updated_at | DATETIME | No | CURRENT_TIMESTAMP | — | Last update time (automatically updated on modification) |

---

## Relationships

- Belongs to one User.

---

## Constraints

- One profile per user.
- `user_id` must be unique.

---

## Business Rules

- Every registered user must have exactly one profile.
- Profile pictures are optional.
- Uploaded profile pictures must be validated before storage.

---

# 4. Table: email_verifications

## Purpose

Stores email verification requests and tokens.

---

## Columns

| Column | Data Type | Nullable | Default | Key | Description |
|---------|-----------|----------|---------|-----|-------------|
| id | BIGINT | No | AUTO_INCREMENT | PK | Internal primary key |
| user_id | BIGINT | No | — | FK | References users.id |
| verification_token | CHAR(64) | No | — | UNIQUE | Secure verification token |
| expires_at | DATETIME | No | — | — | Token expiration time |
| verified_at | DATETIME | Yes | NULL | — | Verification completion timestamp |
| created_at | DATETIME | No | CURRENT_TIMESTAMP | — | Record creation time |

---

## Relationships

- Belongs to one User.

---

## Business Rules

- Verification tokens expire after a configurable period.
- Tokens become invalid after successful verification.
- Tokens are single-use only.

---

# 5. Table: password_reset_tokens

## Purpose

Stores secure password reset requests.

---

## Columns

| Column | Data Type | Nullable | Default | Key | Description |
|---------|-----------|----------|---------|-----|-------------|
| id | BIGINT | No | AUTO_INCREMENT | PK | Internal primary key |
| user_id | BIGINT | No | — | FK | References users.id |
| reset_token | CHAR(64) | No | — | UNIQUE | Secure password reset token |
| expires_at | DATETIME | No | — | — | Token expiration time |
| used_at | DATETIME | Yes | NULL | — | Time when the token was used |
| created_at | DATETIME | No | CURRENT_TIMESTAMP | — | Record creation time |

---

## Relationships

- Belongs to one User.

---

## Business Rules

- Password reset tokens expire after a configurable period.
- Password reset tokens may only be used once.
- Expired or used tokens cannot be reused.

---

# 6. Authentication Module Relationships

```text
users
│
├── user_profiles (1:1)
├── email_verifications (1:M)
└── password_reset_tokens (1:M)
```

---

# 7. Security Standards

The Authentication module follows these standards:

- Passwords must be hashed using Werkzeug's secure password hashing utilities.
- Public identifiers use UUID format.
- Email addresses must be unique.
- Sensitive tokens must be generated using a cryptographically secure random generator.
- Email verification tokens expire automatically.
- Password reset tokens expire automatically.
- Authentication data is separated from personal profile information.
- Failed login attempts are tracked.
- Temporary account lockout protects against brute-force attacks.
- Password changes are auditable using the `last_password_change` field.

---

# 8. Summary

The Authentication database module provides a secure and scalable foundation for account management while maintaining a clear separation between authentication, user profile information, email verification, and password recovery. The design follows modern security practices and is structured to support future enhancements without major database redesign.