# Database Migrations

TOPIQ uses a custom, version-controlled migration runner based on `mysql2` and Node.js.

## Overview

- **Location**: All migrations are stored in the `migrations/` directory.
- **Format**: SQL files prefixed with numbers (e.g., `001_baseline.sql`, `002_...`, `003_...`).
- **Runner**: `scripts/migrate.js`.
- **Status Script**: `scripts/migration-status.js`.
- **History**: Tracked in the `migration_history` table in the database.

## Principles

1. **Deterministic Order**: Migrations are executed alphabetically (`001_...`, `002_...`, `003_...`).
2. **Immutability**: Once a migration is committed and applied, do not modify its SQL. The runner validates each file against stored SHA-256 checksums and aborts with non-zero exit code if modified.
3. **Safety & Advisory Locks**: 
    - Advisory locks (`GET_LOCK('topiq_migration_lock', 10)`) prevent concurrent execution.
    - Preflight checks prevent running migrations on untracked existing databases.
    - Migration-specific preflight hooks validate existing data before applying relational constraints.
4. **Idempotent / Forward-only**: Fixes are implemented via new forward migrations.
5. **Clean Process Lifecycle**: Explicit exit codes (0 for success, 1 for failure), guaranteed lock release, and database pool closure without hardcoded success exits in `finally` blocks.

## Applied Migrations

1. `001_baseline.sql`: Foundational user identities, areas, and role profile hierarchy.
2. `002_add_referral_events_classes_audit.sql`: Support tables (`classes`, `referral_events`, `otp_challenges`, `audit_logs`).
3. `003_enforce_hierarchy_composite_fk.sql`:
   - Adds composite unique index `uq_coordinator_asm` on `coordinator_profiles (user_id, asm_id)`.
   - Adds composite foreign key `fk_student_coordinator_asm` on `student_profiles (coordinator_id, asm_id)` referencing `coordinator_profiles (user_id, asm_id)` (`ON DELETE RESTRICT`).
   - Adds foreign key `fk_student_class` on `student_profiles (class_id)` referencing `classes (id)` (`ON DELETE SET NULL`).

## Commands

### Check Status
Check which migrations have been applied:
```bash
npm run migration:status
```

### Run Migrations
Run pending migrations:
```bash
npm run migrate
```

### Run Integrity Tests
Run automated test suite:
```bash
npm run test
```

## Fresh Installation (Development)

To set up a fresh database in development:
1. Recreate the database (Warning: Destructive!)
   ```bash
   mysql -u root -e "DROP DATABASE IF EXISTS topiq; CREATE DATABASE topiq;"
   ```
2. Run the migration script:
   ```bash
   npm run migrate
   ```

## Adopting an Existing Database

If the database already has the `users` table but no `migration_history`, the runner aborts to prevent unintended schema modifications or data loss.
To adopt:
1. Verify the schema matches existing migration definitions.
2. Manually populate `migration_history` with the applied files and their current SHA-256 checksums.

## Recovery from Partial DDL Failures

MySQL DDL statements (`CREATE TABLE`, `ALTER TABLE`, etc.) trigger implicit commits. If a migration fails mid-script:
1. The migration runner will halt with a non-zero exit code and will NOT record the file in `migration_history`.
2. Inspect the failure output to identify which statement failed.
3. Connect to the database and manually roll back any partially created tables or constraints.
4. Correct the migration SQL file.
5. Re-run `npm run migrate`.

## Backups

**Always** back up the database before running migrations in production.
```bash
mysqldump -u root -p topiq > topiq_backup_$(date +%F).sql
```