# Database Audit Report

## Current State Analysis

### 1. Backend Directory & Dependencies
- **Project Structure**: Follows the recommended architecture (`src/config`, `src/modules`, `src/middleware`, `src/shared`, `migrations`, `seeders`, `tests`, `docs`).
- **Dependencies**: `express`, `mysql2`, `dotenv`, `cors`, `helmet`, `bcryptjs`, `jsonwebtoken`, `joi`, `winston` are installed. Testing frameworks like `jest` need to be added.
- **MySQL Configuration**: Handled through `mysql2/promise` connection pool in `src/config/db.js`. Uses TCP connection instead of local sockets to prevent sandbox/container connection issues.

### 2. Existing Database Schema
The database currently has foundational tables created via ad-hoc setup scripts (`setup-tables.js` and `setup-auth-tables.js`):
- `users`: Tracks base identity, normalized credentials, roles, and status.
- `areas`: Represents the geographic regions.
- `asm_profiles`: Linked to `users` and `areas`.
- `coordinator_profiles`: Linked to `users`, `asm_id`, and `areas`.
- `student_profiles`: Linked to `users`, `coordinator_id`, and `asm_id`.
- `refresh_sessions`: Auth tokens.
- `referral_codes`: Referral mechanism.

### 3. Missing Structures & Conflicts
**Missing Tables**:
- `referral_events`: Required for immutable audit of referral attribution.
- `classes`: Required for student class assignments.
- `audit_logs`: Required for general auditing across operations.
- `otp_challenges`: Needed for OTP login/verification.

**Conflicts/Issues**:
- The current database setup relies on executing arbitrary JavaScript files instead of a version-controlled migration system (like tracking executed migrations in a `migrations` table).
- Missing explicit validation constraints in some areas (e.g., referential integrity checks on deletion).

## Proposed Migration Plan
1. **Implement Migration System**: Create a `migration_history` table and a runner script to handle `.sql` files sequentially.
2. **V1 Migration (Baseline)**: Port the existing schema definitions into `001_baseline.sql` using `CREATE TABLE IF NOT EXISTS`. This preserves existing data while formalizing the schema.
3. **V2 Migration (Extensions)**: Create `002_add_referral_events_classes_audit.sql` to introduce the missing tables (`referral_events`, `classes`, `audit_logs`, `otp_challenges`) with all necessary foreign keys and indexes.
4. **Validation**: Seed test data programmatically and run Jest tests to assert foreign key constraints and hierarchy rules.