# TOPIQ Referral System Audit (Phase 3 — Step 1)

**Audit Date**: 2026-10-09  
**Repository**: `topiq-backend`  
**Database**: MySQL 9.7.1 (`mysql2/promise`)  
**Architecture**: Express.js REST API with layered routing, controller, service, and middleware architecture.

---

## 1. Executive Summary

This audit assesses the existing referral infrastructure in the TOPIQ codebase prior to implementing Phase 3. 

While the database foundation (`001_baseline.sql`, `002_add_referral_events_classes_audit.sql`, and `003_enforce_hierarchy_composite_fk.sql`) defines the physical tables `referral_codes` and `referral_events`, **no API routes, controllers, services, or registration integrations currently exist in `src/`**.

The database schema and constraint models are sound and ready to support secure, hierarchical referral attribution.

---

## 2. Database Schema & Constraint Audit

### 2.1 `referral_codes` Table
* **Definition Location**: `migrations/001_baseline.sql` (Lines 65–76)
* **Fields**:
  - `id INT AUTO_INCREMENT PRIMARY KEY`
  - `code VARCHAR(50) UNIQUE NOT NULL`: Unique referral identifier.
  - `owner_user_id INT NOT NULL`: Foreign key referencing `users(id) ON DELETE CASCADE`.
  - `purpose VARCHAR(50) NOT NULL`: Categorizes the referral intent (e.g., `COORDINATOR_ONBOARDING`, `STUDENT_ONBOARDING`).
  - `status ENUM('ACTIVE', 'EXPIRED', 'REVOKED') DEFAULT 'ACTIVE'`: Lifecycle status.
  - `expires_at TIMESTAMP NULL`: Optional expiration timestamp.
  - `max_uses INT NULL`: Optional usage cap.
  - `use_count INT DEFAULT 0`: Counter tracking redemption count.
  - `created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP`
* **Constraints**:
  - `UNIQUE (code)`: Enforces database-level uniqueness across all referral codes.
  - `FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE CASCADE`: Deleting an owner cleans up their referral codes.
* **Assessment**: **Implemented** in database schema; **Missing** in application layer.

### 2.2 `referral_events` Table
* **Definition Location**: `migrations/002_add_referral_events_classes_audit.sql` (Lines 8–19)
* **Fields**:
  - `id INT AUTO_INCREMENT PRIMARY KEY`
  - `referral_code_id INT NULL`: Foreign key referencing `referral_codes(id) ON DELETE SET NULL`.
  - `referrer_user_id INT NULL`: Foreign key referencing `users(id) ON DELETE SET NULL`.
  - `referred_user_id INT NULL`: Foreign key referencing `users(id) ON DELETE SET NULL`.
  - `attribution_type VARCHAR(50) NOT NULL`: Categorization of attribution event (e.g., `HIERARCHY_ONBOARDING`).
  - `created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP`
* **Constraints**:
  - `UNIQUE (referred_user_id)`: **Critical constraint** ensuring a user is referred at most once as their primary referral attribution.
  - `FOREIGN KEY (referral_code_id) REFERENCES referral_codes(id) ON DELETE SET NULL`: Preserves event record if code is deleted.
  - `FOREIGN KEY (referrer_user_id) REFERENCES users(id) ON DELETE SET NULL`: Preserves event record if referrer is deleted.
  - `FOREIGN KEY (referred_user_id) REFERENCES users(id) ON DELETE SET NULL`: Preserves event record if referred user is deleted.
* **Assessment**: **Implemented** in database schema; **Missing** in application layer.

### 2.3 User and Hierarchy Profile Tables
* **`users`** (`migrations/001_baseline.sql`):
  - `id`, `full_name`, `mobile_normalized`, `email_normalized`, `role` (`ASM`, `COORDINATOR`, `STUDENT`, `ADMIN`), `status` (`PENDING`, `ACTIVE`, `SUSPENDED`, `DISABLED`).
* **`asm_profiles`** (`migrations/001_baseline.sql`):
  - `user_id PRIMARY KEY FK users(id)`, `area_id FK areas(id)`.
* **`coordinator_profiles`** (`migrations/001_baseline.sql`, `migrations/003_enforce_hierarchy_composite_fk.sql`):
  - `user_id PRIMARY KEY FK users(id)`, `asm_id FK users(id)`, `area_id FK areas(id)`.
  - `UNIQUE KEY uq_coordinator_asm (user_id, asm_id)`: Added in migration 003.
* **`student_profiles`** (`migrations/001_baseline.sql`, `migrations/003_enforce_hierarchy_composite_fk.sql`):
  - `user_id PRIMARY KEY FK users(id)`.
  - `coordinator_id INT NOT NULL`, `asm_id INT NOT NULL`.
  - `fk_student_coordinator_asm FOREIGN KEY (coordinator_id, asm_id) REFERENCES coordinator_profiles (user_id, asm_id) ON DELETE RESTRICT`: Strict hierarchy enforcement ensuring student's assigned Coordinator belongs to the same ASM.
  - `fk_student_class FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE SET NULL`.
* **Assessment**: **Implemented** and fully verified in database migrations.

---

## 3. Referral Capabilities Classification Matrix

| Capability | Classification | Location in Codebase | Description & Gaps |
|---|---|---|---|
| **Referral Code Generation Utility** | **Missing** | N/A | No utility exists to generate unique, cryptographically secure codes with collision retries. |
| **Referral Code Model/Service** | **Missing** | N/A | No queries or services exist to create, fetch, rotate, or validate referral codes against MySQL. |
| **Referral Code API Endpoints** | **Missing** | N/A (`src/app.js`, `src/modules/`) | No routes exist for `GET /api/v1/referrals/my-code`, `POST /api/v1/referrals/validate`, etc. |
| **Registration Flow Integration** | **Missing** | `src/modules/auth/auth.service.js` | `AuthService.register()` does not accept or validate `referralCode`. It creates a standalone user without profile or referral attribution. |
| **Referral Event Recording** | **Missing** | N/A | No service writes attribution rows to `referral_events`. |
| **Hierarchy Resolution on Referral** | **Missing** | N/A | Referral logic must bind Coordinator to the referring ASM, and Student to the referring Coordinator + ASM. |
| **Duplicate Attribution Prevention** | **Partially Implemented** | `migrations/002_add_referral_events_classes_audit.sql` (L18) | DB enforces `UNIQUE(referred_user_id)`. Application-level transaction handling and user-friendly error codes are missing. |
| **Self-Referral Prevention** | **Missing** | N/A | No check to prevent a user from applying their own code. |
| **Authorization & Scoped Access** | **Missing** | N/A | While `authenticate` and `authorize` middlewares exist, referral data scoping (ASM cannot view other ASM trees) is not yet implemented. |
| **Referral Audit History** | **Implemented** (Schema) | `migrations/002_add_referral_events_classes_audit.sql` | `referral_events` schema is ready for immutable event insertion. |

---

## 4. Authentication Middleware & Conventions Audit

### 4.1 Authentication Middleware (`src/middleware/authenticate.js`)
* Verifies Bearer JWT access token using `config.jwt.secret`.
* Validates active account status directly against MySQL `users` table (`status === 'ACTIVE'`).
* Sets `req.user = { id, fullName, mobile, email, role, status }`.
* Rejects missing/expired tokens with `401 UNAUTHORIZED`.
* Rejects non-active accounts with `403 FORBIDDEN`.

### 4.2 Authorization Middleware (`src/middleware/authorize.js`)
* Validates `req.user.role` against permitted roles list (`authorize('ASM', 'ADMIN')`).
* Returns `403 FORBIDDEN` on role mismatch.

### 4.3 API Response Conventions
* **Success**:
  ```json
  {
    "success": true,
    "data": { ... }
  }
  ```
* **Error**:
  ```json
  {
    "success": false,
    "error": {
      "code": "ERROR_CODE",
      "message": "Human readable message",
      "details": []
    }
  }
  ```

---

## 5. Migration Status & Next Steps

* **Applied Migrations**:
  1. `001_baseline.sql` (checksum `3d3393ec9715...`)
  2. `002_add_referral_events_classes_audit.sql` (checksum `8311533d6dab...`)
  3. `003_enforce_hierarchy_composite_fk.sql` (checksum `63bebe0aaeb0...`)
* **Schema Integrity**: All required tables, primary keys, unique keys, and composite foreign keys are already present in MySQL. **No new migrations are required** for Phase 3.
* **Implementation Plan**:
  1. Create `src/shared/utils/referralCode.js`: Cryptographically secure code generator with format `TPQ-<ROLE_PREFIX>-<HEX/ALPHANUM>`.
  2. Create `src/modules/referral/referral.service.js`: Code retrieval/generation with collision retry, validation, event recording, and hierarchy-scoped listing.
  3. Create `src/modules/referral/referral.controller.js` and `referral.routes.js`.
  4. Integrate referral resolution into `src/modules/auth/auth.service.js` and `auth.validation.js` inside an atomic MySQL transaction.
  5. Mount routes under `/api/v1/referrals` in `src/app.js`.
  6. Build automated tests in `tests/referral.test.js`.